Skip Headers

Table of Contents Image PL/SQL User's Guide and Reference
Release 2 (9.2)

Part Number A96624-01
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index


Go to next page


Title and Copyright Information

Send Us Your Comments


What's New in PL/SQL?

1 Overview of PL/SQL

Understanding the Main Features of PL/SQL
Block Structure
Variables and Constants
Cursor FOR Loops
Cursor Variables
Control Structures
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
Using %TYPE
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
CASE Statement
Guidelines for PL/SQL Conditional Statements
Iterative Control: LOOP and EXIT Statements
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
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
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
CASE Statement
CLOSE Statement
Collection Methods
COMMIT Statement
Constants and Variables
Cursor Attributes
Cursor Variables
DELETE Statement
EXIT Statement
FETCH Statement
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

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

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


Go to next page
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index