Title and Copyright Information

Send Us Your Comments

Preface

What's New in PL/SQL?

1 Overview of PL/SQL

Understanding the Main Features of PL/SQL

Block Structure

Variables and Constants

Cursors

Cursor FOR Loops

Cursor Variables

Attributes

Control Structures

Modularity

Data Abstraction

Information Hiding

Error Handling

PL/SQL Architecture

In the Oracle Database Server

In Oracle Tools

Advantages of PL/SQL

Support for SQL

Support for Object-Oriented Programming

Better Performance

Higher Productivity

Full Portability

Tight Integration with SQL

Tight Security

2 Fundamentals of PL/SQL

Character Set

Lexical Units

Delimiters

Identifiers

Literals

Comments

Declarations

Using DEFAULT

Using NOT NULL

Using %TYPE

Using %ROWTYPE

Restrictions on Declarations

PL/SQL Naming Conventions

Scope and Visibility of PL/SQL Identifiers

Variable Assignment

Assigning Boolean Values

Assigning a SQL Query Result to a PL/SQL Variable

PL/SQL Expressions and Comparisons

Logical Operators

Boolean Expressions

CASE Expressions

Handling Null Values in Comparisons and Conditional Statements

Built-In Functions

3 PL/SQL Datatypes

Predefined Datatypes

Number Types

Character Types

National Character Types

LOB Types

Boolean Type

Datetime and Interval Types

Datetime and Interval Arithmetic

Avoiding Truncation Problems Using Date and Time Subtypes

User-Defined Subtypes

Defining Subtypes

Using Subtypes

Datatype Conversion

Explicit Conversion

Implicit Conversion

Implicit versus Explicit Conversion

DATE Values

RAW and LONG RAW Values

4 PL/SQL Control Structures

Overview of PL/SQL Control Structures

Conditional Control: IF and CASE Statements

IF-THEN Statement

IF-THEN-ELSE Statement

IF-THEN-ELSIF Statement

CASE Statement

Guidelines for PL/SQL Conditional Statements

Iterative Control: LOOP and EXIT Statements

LOOP

WHILE-LOOP

FOR-LOOP

Sequential Control: GOTO and NULL Statements

GOTO Statement

NULL Statement

5 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

Some Varray Examples

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

Reducing Loop Overhead for Collections with Bulk Binds

How Do Bulk Binds Improve Performance?

Using the FORALL Statement

How FORALL Affects Rollbacks

Counting Rows Affected by FORALL Iterations 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

Restrictions on BULK COLLECT

Using FORALL and BULK COLLECT Together

Using Host Arrays with Bulk Binds

What Is a Record?

Defining and Declaring Records

Declaring Records

Initializing Records

Referencing Records

Assigning Null Values to Records

Assigning Records

Comparing Records

Manipulating 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 Interaction Between PL/SQL and Oracle

Overview of SQL Support in PL/SQL

Data Manipulation

Transaction Control

SQL Functions

SQL Pseudocolumns

SQL Operators

Managing Cursors

Overview of Explicit Cursors

Overview of Implicit Cursors

Separating Cursor Specs and Bodies with Packages

Using Cursor FOR Loops

Using Subqueries Instead of Explicit Cursors

Using Cursor Subqueries

Defining Aliases for Expression Values in a Cursor FOR Loop

Passing Parameters to a Cursor FOR Loop

Using Cursor Variables

What Are Cursor Variables?

Why Use Cursor Variables?

Defining REF CURSOR Types

Declaring Cursor Variables

Controlling Cursor Variables

Cursor Variable Example: Master Table and Details Tables

Cursor Variable Example: Client-Side PL/SQL Block

Cursor Variable Example: Pro*C Program

Cursor Variable Example: Manipulating Host Variables in SQL*Plus

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

Avoiding Errors with Cursor Variables

Restrictions on Cursor Variables

Using Cursor Attributes

Overview of Explicit Cursor Attributes

Overview of Implicit Cursor Attributes

Using Cursor Expressions

Restrictions on Cursor Expressions

Example of Cursor Expressions

Overview of Transaction Processing in PL/SQL

How Transactions Guard Your Database

Making Changes Permanent with COMMIT

Undoing Changes with ROLLBACK

Undoing Partial Changes with SAVEPOINT

How Oracle Does Implicit Rollbacks

Ending Transactions

Setting Transaction Properties with SET TRANSACTION

Overriding Default Locking

Doing Independent Units of Work with Autonomous Transactions

Advantages of Autonomous Transactions

Defining Autonomous Transactions

Controlling Autonomous Transactions

Using Autonomous Triggers

Calling Autonomous Functions from SQL

Ensuring Backward Compatibility of PL/SQL Programs

7 Handling PL/SQL Errors

Overview of PL/SQL Error Handling

Advantages of PL/SQL Exceptions

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

8 PL/SQL Subprograms

What Are Subprograms?

Advantages of Subprograms

Understanding PL/SQL Procedures

Understanding PL/SQL Functions

Using the RETURN Statement

Controlling Side Effects of PL/SQL Subprograms

Declaring PL/SQL Subprograms

Packaging PL/SQL Subprograms Together

Actual Versus Formal Subprogram Parameters

Positional Versus Named Notation for Subprogram Parameters

Using Positional Notation

Using Named Notation

Using Mixed Notation

Specifying Subprogram Parameter Modes

Using the IN Mode

Using the OUT Mode

Using the IN OUT Mode

Summary of Subprogram Parameter Modes

Passing Large Data Structures with the NOCOPY Compiler Hint

The Trade-Off for Better Performance with NOCOPY

Restrictions on NOCOPY

Using Default Values for Subprogram Parameters

Understanding Subprogram Parameter Aliasing

Overloading Subprogram Names

Restrictions on Overloading

How Subprogram Calls Are Resolved

How Overloading Works with Inheritance

Accepting and Returning Multiple Rows with Table Functions

Overview of Table Functions

What Are Table Functions?

What Are Pipelined 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

Passing Data with Cursor Variables

Performing DML Operations Inside Table Functions

Performing DML Operations on Table Functions

Handling Exceptions in Table Functions

Parallelizing Table Functions

Parallel Execution of Table Functions

Input Data Partitioning

Parallel Execution of Leaf-level Table Functions

How Table Functions Stream their Input Data

Choosing Between Partitioning and Clustering for Parallel Execution

Invoker Rights Versus Definer Rights

Advantages of Invoker 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-Rights Subprograms

Overriding Default Name Resolution in Invoker-Rights Subprograms

Granting Privileges on Invoker-Rights Subprograms

Using Roles with Invoker-Rights Subprograms

Using Views and Database Triggers with Invoker-Rights Subprograms

Using Database Links with Invoker-Rights Subprograms

Using Object Types with Invoker-Rights Subprograms

Understanding and Using Recursion

What Is a Recursive Subprogram?

Using Mutual Recursion

Recursion Versus Iteration

Calling External Subprograms

Creating Dynamic Web Pages with PL/SQL Server Pages

9 PL/SQL Packages

What Is a PL/SQL Package?

Example of a PL/SQL Package

Advantages of PL/SQL Packages

Understanding The Package Spec

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

10 PL/SQL Object Types

The Role of Abstraction

What Is an Object Type?

Why Use Object Types?

Structure of an Object Type

Components of an Object Type

Attributes

Methods

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

Defining Object Types

Overview of PL/SQL Type Inheritance

Object Type Example: Stack

Object Type Example: Ticket_Booth

Object Type Example: Bank_Account

Object Type Example: Rational Numbers

Declaring and Initializing Objects

Declaring Objects

Initializing Objects

How PL/SQL Treats Uninitialized Objects

Accessing Attributes

Defining Constructors

Calling Constructors

Calling Methods

Sharing Objects through the REF Modifier

Forward Type Definitions

Manipulating Objects

Selecting Objects

Inserting Objects

Updating Objects

Deleting Objects

11 Native Dynamic SQL

What Is Dynamic SQL?

The Need for Dynamic SQL

Using the EXECUTE IMMEDIATE Statement

Some Examples of Dynamic SQL

Backward Compatibility of the USING Clause

Specifying Parameter Modes

Using the OPEN-FOR, FETCH, and CLOSE Statements

Opening the Cursor Variable

Fetching from the Cursor Variable

Closing the Cursor Variable

Examples of Dynamic SQL for Records, Objects, and Collections

Using Bulk Dynamic SQL

Syntax for Dynamic Bulk Binds

Examples of Dynamic Bulk Binds

Tips and Traps for Dynamic SQL

Improving Performance

Making Procedures Work on Arbitrarily Named Schema Objects

Using Duplicate Placeholders

Using Cursor Attributes

Passing Nulls

Doing Remote Operations

Using Invoker Rights

Using Pragma RESTRICT_REFERENCES

Avoiding Deadlocks

12 Tuning PL/SQL Applications

Reasons for PL/SQL Performance Problems

Identifying PL/SQL Performance Problems

The Profiler API: Package DBMS_PROFILER

The Trace API: Package DBMS_TRACE

PL/SQL Features for Performance Tuning

Tuning PL/SQL Performance with Native Dynamic SQL

Tuning PL/SQL Performance with Bulk Binds

Tuning PL/SQL Performance with the NOCOPY Compiler Hint

Tuning PL/SQL Performance with the RETURNING Clause

Tuning PL/SQL Performance with External Routines

Improving PL/SQL Performance with Object Types and Collections

Compiling PL/SQL Code for Native Execution

13 PL/SQL Language Elements

Assignment Statement

AUTONOMOUS_TRANSACTION Pragma

Blocks

CASE Statement

CLOSE Statement

Collection Methods

Collections

Comments

COMMIT Statement

Constants and Variables

Cursor Attributes

Cursor Variables

Cursors

DELETE Statement

EXCEPTION_INIT Pragma

Exceptions

EXECUTE IMMEDIATE Statement

EXIT Statement

Expressions

FETCH Statement

FORALL Statement

Functions

GOTO Statement

IF Statement

INSERT Statement

Literals

LOCK TABLE Statement

LOOP Statements

MERGE Statement

NULL Statement

Object Types

OPEN Statement

OPEN-FOR Statement

OPEN-FOR-USING Statement

Packages

Procedures

RAISE Statement

Records

RESTRICT_REFERENCES Pragma

RETURN Statement

ROLLBACK Statement

%ROWTYPE Attribute

SAVEPOINT Statement

SELECT INTO Statement

SERIALLY_REUSABLE Pragma

SET TRANSACTION Statement

SQL Cursor

SQLCODE Function

SQLERRM Function

%TYPE Attribute

UPDATE Statement

A Sample PL/SQL Programs

Running the Programs

Sample 1. FOR Loop

Input Table

PL/SQL Block

Output Table

Sample 2. Cursors

Input Table

PL/SQL Block

Output Table

Sample 3. Scoping

Input Table

PL/SQL Block

Output Table

Sample 4. Batch Transaction Processing

Input Tables

PL/SQL Block

Output Tables

Sample 5. Embedded PL/SQL

Input Table

PL/SQL Block in a C Program

Interactive Session

Output Tables

Sample 6. Calling a Stored Procedure

Input Table

Stored Procedure

Interactive Session

B CHAR versus VARCHAR2 Semantics

Assigning Character Values

Comparing Character Values

Inserting Character Values

Selecting Character Values

C PL/SQL Wrap Utility

Advantages of Wrapping PL/SQL Procedures

Limitations of the Wrap Utility

Running the Wrap Utility

Input and Output Files for the Wrap Utility

Error Handling in the Wrap Utility

Version Compatibility

Guidelines

D PL/SQL Name Resolution

What Is Name Resolution?

Various Forms of References

Name-Resolution Algorithm

Finding the Basis

Understanding Capture

Inner Capture

Same-Scope Capture

Outer Capture

Avoiding Capture

Accessing Attributes and Methods

Calling Subprograms and Methods

Name Resolution for SQL Versus PL/SQL

E PL/SQL Program Limits

F List of PL/SQL Reserved Words

Index