Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Go to Book List
Book List
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to next page
View PDF


List of Examples

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments


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
Tight Security
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
Object Types
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 Set
Lexical Units
Reserved Words
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
Using Aliases
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
PL/SQL Character and String Types
PL/SQL National Character Types
Comparing UTF8 and AL16UTF16 Encodings
PL/SQL Boolean Types
PL/SQL Date, Time, and Interval Types
Datetime and Interval Arithmetic
Avoiding Truncation Problems Using Date and Time Subtypes
Overview of PL/SQL Subtypes
Defining Subtypes
Using Subtypes
Type Compatibility
Converting PL/SQL Datatypes
Explicit Conversion
Implicit Conversion
Choosing Between Implicit and Explicit Conversion
DATE Values
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 Functions
SQL Pseudocolumns
SQL Operators
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
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
CASE Statement
CLOSE Statement
Collection Methods
COMMIT Statement
Constants and Variables
Cursor Attributes
Cursor Variables
DELETE Statement
EXIT Statement
FETCH Statement
Restrictions on BULK COLLECT
FORALL Statement
GOTO Statement
IF Statement
INSERT Statement
LOCK TABLE Statement
LOOP Statements
MERGE Statement
NULL Statement
Object Types
OPEN Statement
OPEN-FOR Statement
RAISE Statement
RETURN Statement
ROLLBACK Statement
%ROWTYPE Attribute
SQL Cursor
SQLCODE Function
SQLERRM 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
Inner Capture
Same-Scope Capture
Outer 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?