Oracle Advanced PL/SQL

PL/SQL 11

What you will learn

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create procedures, functions, packages, and database triggers. Students use iSQL*Plus to develop these program units. Students also learn to manage PL/SQL program units and database triggers, to manage dependencies, to manipulate large objects, and to use some of the Oracle-supplied packages

Prerequisites

Working with iSQL*Plus

Course Topics

Introduction

  • Describing PL/SQL
  • Describing the Use of PL/SQL for the Developer and the Database Administrator
  • Explaining the Benefits of PL/SQL
  • PL/SQL program constructs
  • PL/SQL anonymous block structure
  • Subprogram block structure
  • Course objectives and overview

Declaring Variables

  • Recognizing the Basic PL /SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block

Writing Executable Statements

  • Recognizing the Significance of the Executable Section
  • Writing Statements Within the Executable Section
  • Describing the Rules of Nested Blocks
  • Executing and Testing a PL/SQL Block
  • Using Coding Conventions

Interacting with the Oracle Server

  • Writing a Successful SELECT Statement in PL/SQL
  • Declaring the Data type and Size of a PL/SQL Variable Dynamically
  • Writing Data Manipulation Language (DML) Statements in PL/SQL
  • Controlling Transactions in PL/SQL
  • Determining the Outcome of SQL DML Statements

Writing Control Structures

  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables

Working with Composite Data types

  • Creating User-Defined PL/SQL Records
  • Creating a PL/SQL Table
  • Creating a PL/SQL Table of Records
  • Differentiating Among Records, Tables, and Tables of Records

Writing Explicit Cursors

  • Using a PL/SQL Record Variable
  • Distinguishing Between the Implicit and Explicit Cursor
  • Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts

  • Writing a Cursor that Uses Parameters
  • Determining When a FOR UPDATE Clause in a Cursor Is Required
  • Using a PL/SQL Table Variable
  • Using a PL/SQL Table of Records

Handling Exceptions

  • Defining PL/SQL Exceptions
  • Recognizing Unhandled Exceptions
  • Listing and Using Different Types of PL/SQL Exception Handlers
  • Trapping Unanticipated Errors
  • Describing the Effect of Exception Propagation in Nested Blocks
  • Customizing PL/SQL Exception Messages

Creating Procedures

  • Describe the uses of procedures
  • Create procedures
  • Create procedures with arguments
  • Invoke a procedure
  • Remove a procedure

Creating Functions

  • Describe the uses of functions
  • Create a function
  • Invoke a function
  • Remove a function
  • Differentiate between a procedure and a function

Managing Subprograms

  • Describe system privilege requirements
  • Describe object privilege requirements
  • Query the relevant data dictionary views
  • Debug subprograms

Creating Packages

  • Describe packages and list their possible components
  • Create packages that include public and private subprograms, as well as global and local variables
  • Invoke objects in a package
  • Remove packages

More Package Concepts

  • Write packages that use the overloading feature of PL/SQL
  • Avoid errors with mutually referential subprograms
  • Initialize variables with a one-time-only procedure
  • Specify the purity level of packaged functions
  • Describe the persistent state of packaged variables, cursors, tables, and records
  • Query the relevant data dictionary views

Using Oracle-Supplied Packages

  • Overview of Oracle-supplied packages
  • View examples of some supplied packages
  • Write dynamic SQL

Creating Database Triggers

  • Describe different types of triggers
  • Describe database triggers and their use
  • Create database triggers
  • Describe database trigger firing rules
  • Drop database triggers

More Trigger Concepts

  • Create triggers that fire when certain database actions occur
  • List some of the limitations of database triggers
  • Determine when to use database triggers or Oracle Server features
  • Create triggers by using alternative events (notINSERT/UPDATE/DELETE)
  • Create triggers by using alternative levels (not STATEMENT/ROW)
  • Query the relevant data dictionary views

Managing Dependencies

  • Overview of object dependencies
  • Manage PL/SQL objects for recompilation

Manipulating Large Objects

  • Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
  • Understand LOBs
  • Manage binary large file objects (BFILEs)
  • Use PL/SQL with an LOB
  • Create a table with LOB columns
  • Manipulate LOBs
  • Use DBMS_LOB Oracle-supplied packages
  • Create a temporary LOB