Contents

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

PL/SQL Sample Programs

Related Documents

What's New in PL/SQL?

New Features in PL/SQL for Oracle Database 10g Release 2 (10.2)

New Features in PL/SQL for Oracle Database 10g Release 1 (10.1)

1 Overview of PL/SQL

Advantages of PL/SQL

Tight Integration with SQL

Better Performance

Higher Productivity

Full Portability

Access to Pre-defined Packages

Support for Object-Oriented Programming

Support for Developing Web Applications and Pages

Understanding the Main Features of PL/SQL

Understanding PL/SQL Block Structure

Understanding PL/SQL Variables and Constants

Declaring Variables

Assigning Values to a Variable

Declaring Constants

Processing Queries with PL/SQL

Declaring PL/SQL Subprograms

Declaring Datatypes for PL/SQL Variables

Understanding PL/SQL Control Structures

Conditional Control

Iterative Control

Sequential Control

Understanding Conditional Compilation

Writing Reusable PL/SQL Code

Subprograms: Procedures and Functions

Packages: APIs Written in PL/SQL

Inputting and Outputting Data with PL/SQL

Understanding PL/SQL Data Abstraction

Understanding PL/SQL Error Handling

PL/SQL Architecture

In the Oracle Database Server

Anonymous Blocks

Stored Subprograms

Database Triggers

In Oracle Tools

2 Fundamentals of the PL/SQL Language

Character Sets and Lexical Units

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

Order of Evaluation

Short-Circuit Evaluation

Comparison Operators

Relational Operators

IS NULL Operator

BETWEEN Operator

Concatenation Operator

BOOLEAN Expressions

BOOLEAN Arithmetic Expressions

BOOLEAN Character Expressions

BOOLEAN Date Expressions

Guidelines for PL/SQL BOOLEAN Expressions

CASE Expressions

Simple CASE expression

Searched CASE Expression

Handling Null Values in Comparisons and Conditional Statements

NULLs and the NOT Operator

Conditional Compilation

How Does Conditional Compilation Work?

Conditional Compilation Control Tokens

Using Conditional Compilation Selection Directives

Using Conditional Compilation Error Directives

Using Conditional Compilation Inquiry Directives

Using Predefined Inquiry Directives With Conditional Compilation

Using Static Expressions with Conditional Compilation

Setting the PLSQL_CCFLAGS Initialization Parameter

Using DBMS_DB_VERSION Package Constants

Conditional Compilation Examples

Using Conditional Compilation to Specify Code for Database Versions

Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text

Conditional Compilation Restrictions

Using PL/SQL to Create Web Applications and Server Pages

PL/SQL Web Applications

PL/SQL Server Pages

Summary of PL/SQL Built-In Functions

3 PL/SQL Datatypes

Overview of Predefined PL/SQL Datatypes

PL/SQL Number Types

BINARY_INTEGER Datatype

BINARY_FLOAT and BINARY_DOUBLE Datatypes

NUMBER Datatype

PLS_INTEGER Datatype

PL/SQL Character and String Types

LONG and LONG RAW Datatypes

ROWID and UROWID Datatype

VARCHAR2 Datatype

PL/SQL National Character Types

Comparing UTF8 and AL16UTF16 Encodings

NVARCHAR2 Datatype

PL/SQL LOB Types

PL/SQL Boolean Types

BOOLEAN Datatype

PL/SQL Date, Time, and Interval Types

TIMESTAMP Datatype

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE Datatype

INTERVAL YEAR TO MONTH Datatype

INTERVAL DAY TO SECOND Datatype

Datetime and Interval Arithmetic

Avoiding Truncation Problems Using Date and Time Subtypes

Overview of PL/SQL Subtypes

Defining Subtypes

Type Compatibility With Subtypes

Constraints and Default Values With Subtypes

Converting PL/SQL Datatypes

Explicit Conversion

Implicit Conversion

Choosing Between Implicit and Explicit Conversion

RAW and LONG RAW Values

Differences between the CHAR and VARCHAR2 Datatypes

Assigning Character Values

Comparing Character Values

Inserting Character Values

Selecting Character 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 CASE Statements

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 are PL/SQL Collections and Records?

Understanding PL/SQL Collections

Understanding Nested Tables

Understanding Varrays

Understanding Associative Arrays (Index-By Tables)

How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays

Understanding PL/SQL Records

Choosing Which PL/SQL Collection Types to Use

Choosing Between Nested Tables and Associative Arrays

Choosing Between Nested Tables and Varrays

Defining Collection Types and Declaring Collection Variables

Declaring PL/SQL Collection Variables

Initializing and Referencing Collections

Referencing Collection Elements

Assigning Collections

Comparing Collections

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

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 and 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

Managing Cursors in PL/SQL

Implicit Cursors

Attributes of Implicit Cursors

Guidelines for Using Attributes of Implicit Cursors

Explicit Cursors

Declaring a Cursor

Opening a Cursor

Fetching with a Cursor

Fetching Bulk Data with a Cursor

Closing a Cursor

Attributes of Explicit Cursors

Querying Data with 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: Implicit Cursor FOR Loop

Querying Data with PL/SQL: Explicit Cursor FOR Loops

Defining Aliases for Expression Values in a Cursor FOR Loop

Using Subqueries

Using Correlated Subqueries

Writing Maintainable PL/SQL Queries

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 in PL/SQL

Using ROLLBACK in PL/SQL

Using SAVEPOINT in PL/SQL

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

Why Use Dynamic SQL with PL/SQL?

Using the EXECUTE IMMEDIATE Statement in PL/SQL

Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings

Using Bulk Dynamic SQL in PL/SQL

Using Dynamic SQL with Bulk SQL

Examples of Dynamic Bulk Binds

Guidelines for Using Dynamic SQL with PL/SQL

Building a Dynamic Query with 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

Using Dynamic SQL With PL/SQL Records and Collections

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

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?

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

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 HTF and HTP Packages

About the UTL_FILE Package

About the UTL_HTTP Package

About the UTL_SMTP 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

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

Initialization Parameters for PL/SQL Compilation

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

Make SQL Statements as Efficient as Possible

Make Function Calls as Efficient as Possible

Make Loops as Efficient as Possible

Do Not Duplicate Built-in String Functions

Reorder Conditional Tests to Put the Least Expensive First

Minimize Datatype Conversions

Use PLS_INTEGER for Integer Arithmetic

Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic

Avoiding Memory Overhead in PL/SQL Code

Be Generous When Declaring Sizes for VARCHAR2 Variables

Group Related Subprograms into Packages

Pin Packages in the Shared Memory Pool

Improve Your Code to Avoid Compiler Warnings

Profiling and Tracing PL/SQL Programs

Using The Profiler API: Package DBMS_PROFILER

Using The Trace API: Package DBMS_TRACE

Controlling the Trace

Reducing Loop Overhead for DML Statements and Queries with Bulk SQL

Using the FORALL Statement

How FORALL Affects Rollbacks

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

Before You Begin

Determining Whether to Use PL/SQL Native Compilation

How PL/SQL Native Compilation Works

Dependencies, Invalidation and Revalidation

Real Application Clusters and PL/SQL Native Compilation

Limitations of Native Compilation

The spnc_commands File

Setting up Initialization Parameters for PL/SQL Native Compilation

PLSQL_NATIVE_LIBRARY_DIR Initialization Parameter

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT Initialization Parameter

PLSQL_CODE_TYPE Initialization Parameter

Setting Up PL/SQL Native Library Subdirectories

Setting Up and Testing PL/SQL Native Compilation

Setting Up a New Database for PL/SQL Native Compilation

Modifying the Entire Database for PL/SQL Native or Interpreted Compilation

Setting Up Transformations with Pipelined Functions

Overview of Pipelined Table Functions

Writing a Pipelined Table Function

Using Pipelined Table Functions for Transformations

Returning Results from Pipelined Table Functions

Pipelining Data Between PL/SQL Table Functions

Optimizing Multiple Calls to Pipelined Table Functions

Fetching from the Results of Pipelined Table Functions

Passing Data with Cursor Variables

Performing DML Operations Inside Pipelined Table Functions

Performing DML Operations on Pipelined Table Functions

Handling Exceptions in Pipelined Table Functions

12 Using PL/SQL With Object Types

Declaring and Initializing Objects in PL/SQL

Declaring Objects in a PL/SQL Block

How PL/SQL Treats Uninitialized Objects

Manipulating Objects in PL/SQL

Accessing Object Attributes With Dot Notation

Calling Object Constructors and Methods

Updating and Deleting Objects

Manipulating Objects Through Ref Modifiers

Defining SQL Types Equivalent to PL/SQL Collection Types

Manipulating Individual Collection Elements with SQL

Using PL/SQL Collections with SQL Object Types

Using Dynamic SQL With Objects

13 PL/SQL Language Elements

Assignment Statement

AUTONOMOUS_TRANSACTION Pragma

Block Declaration

CLOSE Statement

Collection Definition

Collection Methods

COMMIT Statement

Constant and Variable Declaration

Cursor Attributes

Cursor Variables

Cursor Declaration

DELETE Statement

EXCEPTION_INIT Pragma

Exception Definition

EXECUTE IMMEDIATE Statement

Expression Definition

FETCH Statement

FORALL Statement

Function Declaration

INSERT Statement

Literal Declaration

LOCK TABLE Statement

LOOP Statements

MERGE Statement

Object Type Declaration

OPEN-FOR Statement

Package Declaration

Procedure Declaration

RAISE Statement

Record Definition

RESTRICT_REFERENCES Pragma

RETURN Statement

RETURNING INTO Clause

ROLLBACK Statement

%ROWTYPE Attribute

SAVEPOINT Statement

SELECT INTO Statement

SERIALLY_REUSABLE Pragma

SET TRANSACTION Statement

SQLCODE Function

SQLERRM Function

%TYPE Attribute

UPDATE Statement

A Obfuscating PL/SQL Source Code

What is Obfuscation?

Tips When Obfuscating PL/SQL Units

Limitations of Obfuscation

Limitations of the wrap Utility

Limitations of the DBMS_DDL wrap Function

Obfuscating PL/SQL Code With the wrap Utility

Input and Output Files for the PL/SQL wrap Utility

Running the wrap Utility

Obfuscating PL/QL Code With DBMS_DDL Subprograms

Using the DBMS_DDL create_wrapped Procedure

B How PL/SQL Resolves Identifier Names

What Is Name Resolution?

Examples of Qualified Names and Dot Notation

Additional Examples of How to Specify Names With the Dot Notation

Differences in Name Resolution Between PL/SQL and SQL

Understanding Capture

Same-Scope Capture

Avoiding Inner Capture in DML Statements

Qualifying References to Object Attributes and Methods

References to Attributes and Methods

References to Row Expressions

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

Index