1.  Triggers

Beyond Declarative Integrity

Triggers

Types of Triggers

Row-Level Triggers

Trigger Predicates

Trigger Conditions

Using SEQUENCEs

Cascading Triggers and Mutating Tables

Generating an Error

Maintaining Triggers

2.  PL/SQL Variables and Datatypes

Anonymous Blocks

Declaring Variables

Datatypes

Subtypes

Character Data

Dates and Timestamps

Date Intervals

Anchored Types

Assignment and Conversions

Selecting into a Variable

Returning into a Variable

3.  PL/SQL Syntax and Logic

Conditional Statements – IF/THEN

Conditional Statements – CASE

Comments and Labels

Loops

WHILE and FOR Loops

SQL in PL/SQL

Local Procedures and Functions

4.  Stored Procedures and Functions

Stored Subprograms

Creating a Stored Procedure

Procedure Calls and Parameters

Parameter Modes

Creating a Stored Function

Stored Functions and SQL

Invoker’s Rights

5.  Exception Handling

SQLCODE and SQLERRM

Exception Handlers

Nesting Blocks

Scope and Name Resolution

Declaring and Raising Named

Exceptions

User-Defined Exceptions

6.  Records, Collections, and User-Defined Types

Record Variables

Using the %ROWTYPE

Attribute

VARRAY and Nested TABLE

Collections

Using Nested TABLEs

Using VARRAYs

Collection in Database Tables

Associative Array Collections

Collection Methods

Iterating Through Collections

7.  Cursors

Multi-Row Queries

Declaring and Opening Cursors

Fetching Rows

Closing Cursors

The Cursor FOR Loop

FOR UPDATE Cursors

Cursor Parameters

The Implicit (SQL) Cursor

8.  Bulk Operations

Bulk Binding

BULK COLLECT Clause

FORALL Statement

FORALL Variations

Bulk Returns

Bulk Fetching with Cursors

9.  Using Packages

Packages

Oracle-Supplied Packages

The DBMS_OUTPUT Package

The DBMS_UTILITY Package

The UTL_FILE Package

Creating Pipes with DBMS_PIPE

Writing to and Reading from a Pipe

The DBMS_METADATA Package

XML Packages

Networking Packages

Other Supplied Packages

10.  Creating Packages

Structure of a Package

The Package Interface and

Implementation

Package Variables and Package State

Overloading Package Functions and

Procedures

Forward Declarations

Strong REF CURSOR Variables

Weak REF CURSOR Variables

11.  Working with LOBs

Large Object Types

Oracle Directories

LOB Locators

Internal LOBs

External LOBs

Temporary LOBs

The DBMS_LOB Package

12.  Maintaining PL/SQL Code

Privileges for Stored Programs

Data Dictionary

PL/SQL Stored Program

Compilation

Conditional Compilation

Compile-Time Warnings

The PL/SQL Execution

Environment

Dependencies and Validation

Maintaining Stored Programs

13.  Appendix A - Dynamic SQL

Generating SQL at Runtime

Native Dynamic SQL vs.

DBMS_SQL Package

The EXECUTE IMMEDIATE

Statement

Using Bind Variables

Multi-row Dynamic Queries

Bulk Operations with Dynamic

SQL

Using DBMS_SQL

DBMS_SQL Subprograms

14.  Appendix B - PL/SQL Versions, Datatypes, and Language Limits

15.  Appendix C - Oracle10g Supplied Packages