Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

How This Book Is Organized

Related Documentation

Sample Database Tables

Documentation Accessibility

Reading the Syntax Diagrams

What's New in PL/SQL?

New Features in PL/SQL for Oracle Database 10g

New Features in PL/SQL for Oracle9i

1 Overview of PL/SQL

Advantages of PL/SQL

Tight Integration with SQL

Support for SQL

Better Performance

Higher Productivity

Full Portability

Support for Object-Oriented Programming

Understanding the Main Features of PL/SQL

Block Structure

Variables and Constants

Declaring Variables

Assigning Values to a Variable

Declaring Constants

Processing Queries with PL/SQL

Declaring PL/SQL Variables

Control Structures

Conditional Control

Iterative Control

Sequential Control

Writing Reusable PL/SQL Code

Data Abstraction

PL/SQL Architecture

In the Oracle Database Server

Anonymous Blocks

Stored Subprograms

Database Triggers

In Oracle Tools

2 Fundamentals of the PL/SQL Language

Predefined Identifiers

Quoted Identifiers

Numeric Literals

Character Literals

String Literals

Boolean Literals

Datetime Literals

Single-Line Comments

Multi-line Comments

Restrictions on Comments

Using the %TYPE Attribute

Using the %ROWTYPE Attribute

Aggregate Assignment

Restrictions on Declarations

PL/SQL Naming Conventions

Scope and Visibility of PL/SQL Identifiers

Assigning Values to Variables

Assigning Boolean Values

Assigning a SQL Query Result to a PL/SQL Variable

PL/SQL Expressions and Comparisons

Logical Operators

Boolean Expressions

CASE Expressions

Searched CASE Expression

Handling Null Values in Comparisons and Conditional Statements

NULLs and the NOT Operator

Summary of PL/SQL Built-In Functions

3 PL/SQL Datatypes

Overview of Predefined PL/SQL Datatypes

PL/SQL Number Types

BINARY_FLOAT and BINARY_DOUBLE

PL/SQL Character and String Types

LONG and LONG RAW

ROWID and UROWID

PL/SQL National Character Types

Comparing UTF8 and AL16UTF16 Encodings

PL/SQL LOB Types

PL/SQL Boolean Types

PL/SQL Date, Time, and Interval Types

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Datetime and Interval Arithmetic

Avoiding Truncation Problems Using Date and Time Subtypes

Overview of PL/SQL Subtypes

Defining Subtypes

Type Compatibility

Converting PL/SQL Datatypes

Explicit Conversion

Implicit Conversion

Choosing Between Implicit and Explicit Conversion

RAW and LONG RAW Values

4 Using PL/SQL Control Structures

Overview of PL/SQL Control Structures

Testing Conditions: IF and CASE Statements

Using the IF-THEN Statement

Using the IF-THEN-ELSE Statement

Using the IF-THEN-ELSIF Statement

Using the CASE Statement

Searched CASE Statement

Guidelines for PL/SQL Conditional Statements

Controlling Loop Iterations: LOOP and EXIT Statements

Using the LOOP Statement

Using the EXIT Statement

Using the EXIT-WHEN Statement

Labeling a PL/SQL Loop

Using the WHILE-LOOP Statement

Using the FOR-LOOP Statement

How PL/SQL Loops Iterate

Dynamic Ranges for Loop Bounds

Scope of the Loop Counter Variable

Using the EXIT Statement in a FOR Loop

Sequential Control: GOTO and NULL Statements

Using the GOTO Statement

Restrictions on the GOTO Statement

Using the NULL Statement

5 Using PL/SQL Collections and Records

What Is a Collection?

Understanding Nested Tables

Understanding Varrays

Understanding Associative Arrays (Index-By Tables)

How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays

Choosing Which PL/SQL Collection Types to Use

Choosing Between Nested Tables and Associative Arrays

Choosing Between Nested Tables and Varrays

Defining Collection Types

Defining SQL Types Equivalent to PL/SQL Collection Types

Declaring PL/SQL Collection Variables

Initializing and Referencing Collections

Referencing Collection Elements

Assigning Collections

Comparing Collections

Using PL/SQL Collections with SQL Statements

Using PL/SQL Varrays with INSERT, UPDATE, and SELECT Statements

Manipulating Individual Collection Elements with SQL

Using Multilevel Collections

Using Collection Methods

Checking If a Collection Element Exists (EXISTS Method)

Counting the Elements in a Collection (COUNT Method)

Checking the Maximum Size of a Collection (LIMIT Method)

Finding the First or Last Collection Element (FIRST and LAST Methods)

Looping Through Collection Elements (PRIOR and NEXT Methods)

Increasing the Size of a Collection (EXTEND Method)

Decreasing the Size of a Collection (TRIM Method)

Deleting Collection Elements (DELETE Method)

Applying Methods to Collection Parameters

Avoiding Collection Exceptions

What Is a PL/SQL Record?

Defining and Declaring Records

Using Records as Procedure Parameters and Function Return Values

Assigning Values to Records

Comparing Records

Inserting PL/SQL Records into the Database

Updating the Database with PL/SQL Record Values

Restrictions on Record Inserts/Updates

Querying Data into Collections of Records

6 Performing SQL Operations from PL/SQL

Overview of SQL Support in PL/SQL

Data Manipulation

Transaction Control

SQL Pseudocolumns

Performing DML Operations from PL/SQL (INSERT, UPDATE, and DELETE)

Overview of Implicit Cursor Attributes

%FOUND Attribute: Has a DML Statement Changed Rows?

%ISOPEN Attribute: Always FALSE for Implicit Cursors

%NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?

%ROWCOUNT Attribute: How Many Rows Affected So Far?

Guidelines for Using Implicit Cursor Attributes

Using PL/SQL Records in SQL INSERT and UPDATE Statements

Issuing Queries from PL/SQL

Selecting At Most One Row: SELECT INTO Statement

Selecting Multiple Rows: BULK COLLECT Clause

Looping Through Multiple Rows: Cursor FOR Loop

Performing Complicated Query Processing: Explicit Cursors

Querying Data with PL/SQL

Querying Data with PL/SQL: Implicit Cursor FOR Loop

Querying Data with PL/SQL: Explicit Cursor FOR Loops

Defining Aliases for Expression Values in a Cursor FOR Loop

Overview of Explicit Cursors

Declaring a Cursor

Opening a Cursor

Fetching with a Cursor

Fetching Bulk Data with a Cursor

Closing a Cursor

Using Subqueries

Using Correlated Subqueries

Writing Maintainable PL/SQL Queries

Using Cursor Attributes

Overview of Explicit Cursor Attributes

%FOUND Attribute: Has a Row Been Fetched?

%ISOPEN Attribute: Is the Cursor Open?

%NOTFOUND Attribute: Has a Fetch Failed?

%ROWCOUNT Attribute: How Many Rows Fetched So Far?

Using Cursor Variables (REF CURSORs)

What Are Cursor Variables (REF CURSORs)?

Why Use Cursor Variables?

Declaring REF CURSOR Types and Cursor Variables

Passing Cursor Variables As Parameters

Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE

Opening a Cursor Variable

Using a Cursor Variable as a Host Variable

Fetching from a Cursor Variable

Closing a Cursor Variable

Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL

Avoiding Errors with Cursor Variables

Restrictions on Cursor Variables

Using Cursor Expressions

Restrictions on Cursor Expressions

Example of Cursor Expressions

Constructing REF CURSORs with Cursor Subqueries

Overview of Transaction Processing in PL/SQL

Using COMMIT, SAVEPOINT, and ROLLBACK in PL/SQL

Statement-Level Rollbacks

How Oracle Does Implicit Rollbacks

Ending Transactions

Setting Transaction Properties with SET TRANSACTION

Restrictions on SET TRANSACTION

Overriding Default Locking

Doing Independent Units of Work with Autonomous Transactions

Advantages of Autonomous Transactions

Defining Autonomous Transactions

Comparison of Autonomous Transactions and Nested Transactions

Transaction Context

Transaction Visibility

Controlling Autonomous Transactions

Using Autonomous Triggers

Calling Autonomous Functions from SQL

7 Performing SQL Operations with Native Dynamic SQL

What Is Dynamic SQL?

Why Use Dynamic SQL?

Using the EXECUTE IMMEDIATE Statement

Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings

Building a Dynamic Query with Dynamic SQL

Examples of Dynamic SQL for Records, Objects, and Collections

Using Bulk Dynamic SQL

Using Dynamic SQL with Bulk SQL

Examples of Dynamic Bulk Binds

Guidelines for Dynamic SQL

When to Use or Omit the Semicolon with Dynamic SQL

Improving Performance of Dynamic SQL with Bind Variables

Passing Schema Object Names As Parameters

Using Duplicate Placeholders with Dynamic SQL

Using Cursor Attributes with Dynamic SQL

Passing Nulls to Dynamic SQL

Using Database Links with Dynamic SQL

Using Invoker Rights with Dynamic SQL

Using Pragma RESTRICT_REFERENCES with Dynamic SQL

Avoiding Deadlocks with Dynamic SQL

Backward Compatibility of the USING Clause

8 Using PL/SQL Subprograms

What Are Subprograms?

Advantages of PL/SQL Subprograms

Understanding PL/SQL Procedures

Understanding PL/SQL Functions

Using the RETURN Statement

Declaring Nested PL/SQL Subprograms

Passing Parameters to PL/SQL Subprograms

Actual Versus Formal Subprogram Parameters

Using Positional, Named, or Mixed Notation for Subprogram Parameters

Specifying Subprogram Parameter Modes

Using the IN Mode

Using the OUT Mode

Using the IN OUT Mode

Summary of Subprogram Parameter Modes

Using Default Values for Subprogram Parameters

Overloading Subprogram Names

Guidelines for Overloading with Numeric Types

Restrictions on Overloading

How Subprogram Calls Are Resolved

How Overloading Works with Inheritance

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

Advantages of Invoker's Rights

Specifying the Privileges for a Subprogram with the AUTHID Clause

Who Is the Current User During Subprogram Execution?

How External References Are Resolved in Invoker's Rights Subprograms

The Need for Template Objects in Invoker's Rights Subprograms

Overriding Default Name Resolution in Invoker's Rights Subprograms

Granting Privileges on Invoker's Rights Subprograms

Granting Privileges on an Invoker's Rights Subprogram: Example

Using Roles with Invoker's Rights Subprograms

Using Views and Database Triggers with Invoker's Rights Subprograms

Using Database Links with Invoker's Rights Subprograms

Using Object Types with Invoker's Rights Subprograms

Calling Invoker's Rights Instance Methods

Using Recursion with PL/SQL

What Is a Recursive Subprogram?

Calling External Subprograms

Creating Dynamic Web Pages with PL/SQL Server Pages

Controlling Side Effects of PL/SQL Subprograms

Understanding Subprogram Parameter Aliasing

9 Using PL/SQL Packages

What Is a PL/SQL Package?

What Goes In a PL/SQL Package?

Example of a PL/SQL Package

Advantages of PL/SQL Packages

Understanding The Package Specification

Referencing Package Contents

Understanding The Package Body

Some Examples of Package Features

Private Versus Public Items in Packages

Overloading Packaged Subprograms

How Package STANDARD Defines the PL/SQL Environment

Overview of Product-Specific Packages

About the DBMS_ALERT Package

About the DBMS_OUTPUT Package

About the DBMS_PIPE Package

About the UTL_FILE Package

About the UTL_HTTP Package

Guidelines for Writing Packages

Separating Cursor Specs and Bodies with Packages

10 Handling PL/SQL Errors

Overview of PL/SQL Runtime Error Handling

Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions

Advantages of PL/SQL Exceptions

Summary of Predefined PL/SQL Exceptions

Defining Your Own PL/SQL Exceptions

Declaring PL/SQL Exceptions

Scope Rules for PL/SQL Exceptions

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT

Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR

Redeclaring Predefined Exceptions

How PL/SQL Exceptions Are Raised

Raising Exceptions with the RAISE Statement

How PL/SQL Exceptions Propagate

Reraising a PL/SQL Exception

Handling Raised PL/SQL Exceptions

Handling Exceptions Raised in Declarations

Handling Exceptions Raised in Handlers

Branching to or from an Exception Handler

Retrieving the Error Code and Error Message: SQLCODE and SQLERRM

Catching Unhandled Exceptions

Tips for Handling PL/SQL Errors

Continuing after an Exception Is Raised

Retrying a Transaction

Using Locator Variables to Identify Exception Locations

Overview of PL/SQL Compile-Time Warnings

PL/SQL Warning Categories

Controlling PL/SQL Warning Messages

Using the DBMS_WARNING Package

11 Tuning PL/SQL Applications for Performance

How PL/SQL Optimizes Your Programs

When to Tune PL/SQL Code

Guidelines for Avoiding PL/SQL Performance Problems

Avoiding CPU Overhead in PL/SQL Code

Avoiding Memory Overhead in PL/SQL Code

Profiling and Tracing PL/SQL Programs

Using The Profiler API: Package DBMS_PROFILER

Using The Trace API: Package DBMS_TRACE

Reducing Loop Overhead for DML Statements and Queries (FORALL, BULK COLLECT)

Using the FORALL Statement

Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute

Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute

Retrieving Query Results into Collections with the BULK COLLECT Clause

Examples of Bulk-Fetching from a Cursor

Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause

Retrieving DML Results into a Collection with the RETURNING INTO Clause

Using FORALL and BULK COLLECT Together

Using Host Arrays with Bulk Binds

Writing Computation-Intensive Programs in PL/SQL

Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables

Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint

Restrictions on NOCOPY

Compiling PL/SQL Code for Native Execution

Setting Up Transformation Pipelines with Table Functions

Overview of Table Functions

Using Pipelined Table Functions for Transformations

Writing a Pipelined Table Function

Returning Results from Table Functions

Pipelining Data Between PL/SQL Table Functions

Querying Table Functions

Optimizing Multiple Calls to Table Functions

Fetching from the Results of Table Functions

Passing Data with Cursor Variables

Performing DML Operations Inside Table Functions

Performing DML Operations on Table Functions

Handling Exceptions in Table Functions

12 Using PL/SQL Object Types

Overview of PL/SQL Object Types

What Is an Object Type?

Why Use Object Types?

Structure of an Object Type

Components of an Object Type

What Languages can I Use for Methods of Object Types?

How Object Types Handle the SELF Parameter

Changing Attributes and Methods of an Existing Object Type (Type Evolution)

Defining Object Types

Overview of PL/SQL Type Inheritance

Examples of PL/SQL Type Inheritance

Declaring and Initializing Objects

Declaring Objects

Initializing Objects

How PL/SQL Treats Uninitialized Objects

Accessing Object Attributes

Defining Object Constructors

Calling Object Constructors

Calling Object Methods

Sharing Objects through the REF Modifier

Forward Type Definitions

Manipulating Objects through SQL

Selecting Objects

Using Function VALUE

Using Function REF

Testing for Dangling Refs

Using Function DEREF

Inserting Objects

Updating Objects

Deleting Objects

13 PL/SQL Language Elements

Assignment Statement

AUTONOMOUS_TRANSACTION Pragma

CLOSE Statement

Collection Methods

COMMIT Statement

Constants and Variables

Cursor Attributes

Cursor Variables

DELETE Statement

EXCEPTION_INIT Pragma

EXECUTE IMMEDIATE Statement

FETCH Statement

Restrictions on BULK COLLECT

FORALL Statement

INSERT Statement

LOCK TABLE Statement

LOOP Statements

MERGE Statement

OPEN-FOR Statement

OPEN-FOR-USING Statement

RAISE Statement

RESTRICT_REFERENCES Pragma

RETURN Statement

ROLLBACK Statement

%ROWTYPE Attribute

SAVEPOINT Statement

SCN_TO_TIMESTAMP Function

SELECT INTO Statement

SERIALLY_REUSABLE Pragma

SET TRANSACTION Statement

SQLCODE Function

SQLERRM Function

TIMESTAMP_TO_SCN Function

%TYPE Attribute

UPDATE Statement

A Sample PL/SQL Programs

Where to Find PL/SQL Sample Programs

Exercises for the Reader

B Understanding CHAR and VARCHAR2 Semantics in PL/SQL

Assigning Character Values

Comparing Character Values

Inserting Character Values

Selecting Character Values

C Obfuscating Source Code with the PL/SQL Wrap Utility

Advantages of Wrapping PL/SQL Procedures

Running the PL/SQL Wrap Utility

Input and Output Files for the PL/SQL Wrap Utility

Limitations of the PL/SQL Wrap Utility

D How PL/SQL Resolves Identifier Names

What Is Name Resolution?

Examples of Qualified Names and Dot Notation

Differences in Name Resolution Between SQL and PL/SQL

Understanding Capture

Same-Scope Capture

Avoiding Inner Capture in DML Statements

Qualifying References to Object Attributes and Methods

Calling Parameterless Subprograms and Methods

Name Resolution for SQL Versus PL/SQL

E PL/SQL Program Limits

F List of PL/SQL Reserved Words

G Frequently Asked Questions About PL/SQL

When Should I Use Bind Variables with PL/SQL?

When Do I Use or Omit the Semicolon with Dynamic SQL?

How Can I Use Regular Expressions with PL/SQL?

How Do I Continue After a PL/SQL Exception?

Does PL/SQL Have User-Defined Types or Abstract Data Types?

How Do I Pass a Result Set from PL/SQL to Java or Visual Basic (VB)?

How Do I Specify Different Kinds of Names with PL/SQL's Dot Notation?

What Can I Do with Objects and Object Types in PL/SQL?

How Do I Create a PL/SQL Procedure?

How Do I Input or Output Data with PL/SQL?

How Do I Perform a Case-Insensitive Query?

Index