1/99

Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions
• Syntax Descriptions

What's New in PL/SQL?

• PL/SQL Feature for Oracle Database 11g Release 2 (11.2.0.2)
• PL/SQL Features for Oracle Database 11g Release 2
• PL/SQL Features for Oracle Database 11g Release 1

1 Overview of PL/SQL

• Advantages of PL/SQL
  • Tight Integration with SQL
  • High Performance
  • High Productivity
  • Portability
  • Scalability
  • Manageability
  • Support for Object-Oriented Programming
  • Support for Developing Web Applications
  • Support for Developing Server Pages
• Main Features of PL/SQL
  • Error Handling
  • Blocks
  • Variables and Constants
  • Subprograms
  • Packages
  • Triggers
  • Input and Output
  • Data Abstraction
    • Cursors
    • Composite Variables
    • %ROWTYPE Attribute
    • %TYPE Attribute
    • Abstract Data Types
  • Control Statements
  • Conditional Compilation
  • Processing a Query Result Set One Row at a Time
• Architecture of PL/SQL
  • PL/SQL Engine
  • PL/SQL Units and Compilation Parameters

2 PL/SQL Language Fundamentals

• Character Sets
  • Database Character Set
  • National Character Set
• Lexical Units
  • Delimiters
  • Identifiers
    • Reserved Words and Keywords
    • Predefined Identifiers
    • User-Defined Identifiers
  • Literals
  • Comments
    • Single-Line Comments
    • Multiline Comments
  • Whitespace Characters Between Lexical Units
• Declarations
  • Variable Declarations
  • Constant Declarations
  • Initial Values of Variables and Constants
  • NOT NULL Constraint
  • %TYPE Attribute
• References to Identifiers
• Scope and Visibility of Identifiers
• Assigning Values to Variables
  • Assigning Values to Variables with the Assignment Statement
  • Assigning Values to Variables with the SELECT INTO Statement
  • Assigning Values to Variables as Parameters of a Subprogram
  • Assigning Values to BOOLEAN Variables
• Expressions
  • Concatenation Operator
  • Operator Precedence
  • Logical Operators
  • Short-Circuit Evaluation
  • Comparison Operators
    • IS [NOT] NULL Operator
    • Relational Operators
    • LIKE Operator
    • BETWEEN Operator
    • IN Operator
  • BOOLEAN Expressions
  • CASE Expressions
    • Simple CASE Expression
    • Searched CASE Expression
  • SQL Functions in PL/SQL Expressions
• Error-Reporting Functions
• Pragmas
• Conditional Compilation
  • How Conditional Compilation Works
    • Preprocessor Control Tokens
    • Selection Directives
    • Error Directives
    • Inquiry Directives
    • Static Expressions
  • Conditional Compilation Examples
  • Retrieving and Printing Post-Processed Source Text
  • Conditional Compilation Directive Restrictions

3 PL/SQL Data Types

• SQL Data Types
  • Different Maximum Sizes
  • Additional PL/SQL Constants for BINARY_FLOAT and BINARY_DOUBLE
  • Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE
  • CHAR and VARCHAR2 Variables
    • Assigning or Inserting Too-Long Values
    • Declaring Variables for Multibyte Characters
    • Differences Between CHAR and VARCHAR2 Data Types
  • LONG and LONG RAW Variables
  • ROWID and UROWID Variables
• BOOLEAN Data Type
• PLS_INTEGER and BINARY_INTEGER Data Types
  • Preventing PLS_INTEGER Overflow
  • Predefined PLS_INTEGER Subtypes
  • SIMPLE_INTEGER Subtype of PLS_INTEGER
    • SIMPLE_INTEGER Overflow Semantics
    • Expressions with Both SIMPLE_INTEGER and Other Operands
    • Integer Literals in SIMPLE_INTEGER Range
• User-Defined PL/SQL Subtypes
  • Unconstrained Subtypes
  • Constrained Subtypes
  • Subtypes with Base Types in Same Data Type Family

4 PL/SQL Control Statements

• Conditional Selection Statements
  • IF THEN Statement
  • IF THEN ELSE Statement
  • IF THEN ELSIF Statement
  • Simple CASE Statement
  • Searched CASE Statement
• LOOP Statements
  • Basic LOOP Statement
  • EXIT Statement
  • EXIT WHEN Statement
  • CONTINUE Statement
  • CONTINUE WHEN Statement
  • FOR LOOP Statement
    • FOR LOOP Index
    • Lower Bound and Upper Bound
    • EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement
  • WHILE LOOP Statement
• Sequential Control Statements
  • GOTO Statement
  • NULL Statement

5 PL/SQL Collections and Records

• Collection Types
• Associative Arrays
  • Declaring Associative Array Constants
  • NLS Parameter Values Affect Associative Arrays Indexed by String
    • Changing NLS Parameter Values After Populating Associative Arrays
    • Indexes of Data Types Other Than VARCHAR2
    • Passing Associative Arrays to Remote Databases
  • Appropriate Uses for Associative Arrays
• Varrays (Variable-Size Arrays)
  • Appropriate Uses for Varrays
• Nested Tables
  • Important Differences Between Nested Tables and Arrays
  • Appropriate Uses for Nested Tables
• Collection Constructors
• Assigning Values to Collection Variables
  • Data Type Compatibility
  • Assigning Null Values to Varray or Nested Table Variables
  • Assigning Set Operation Results to Nested Table Variables
• Multidimensional Collections
• Collection Comparisons
  • Comparing Varray and Nested Table Variables to NULL
  • Comparing Nested Tables for Equality and Inequality
  • Comparing Nested Tables with SQL Multiset Conditions
• Collection Methods
  • DELETE Collection Method
  • TRIM Collection Method
  • EXTEND Collection Method
  • EXISTS Collection Method
  • FIRST and LAST Collection Methods
    • FIRST and LAST Methods for Associative Array
    • FIRST and LAST Methods for Varray
    • FIRST and LAST Methods for Nested Table
  • COUNT Collection Method
    • COUNT Method for Varray
    • COUNT Method for Nested Table
  • LIMIT Collection Method
  • PRIOR and NEXT Collection Methods
• Collection Types Defined in Package Specifications
• Record Variables
  • Initial Values of Record Variables
  • Declaring Record Constants
  • RECORD Types
  • %ROWTYPE Attribute
    • Record Variable that Always Represents Full Row
    • Record Variable that Can Represent Partial Row
    • %ROWTYPE Attribute and Virtual Columns
• Assigning Values to Record Variables
  • Assigning One Record Variable to Another
  • Assigning Full or Partial Rows to Record Variables
    • SELECT INTO Statement for Assigning Row to Record Variable
    • FETCH Statement for Assigning Row to Record Variable
    • SQL Statements that Return Rows in PL/SQL Record Variables
  • Assigning NULL to Record Variable
• Record Comparisons
• Inserting Records into Tables
• Updating Rows with Records
• Restrictions on Record Inserts and Updates

6 PL/SQL Static SQL

• Description of Static SQL
  • Statements
  • Pseudocolumns
    • CURRVAL and NEXTVAL in PL/SQL
• Cursors
  • Implicit Cursors
    • SQL%ISOPEN Attribute: Is the Cursor Open?
    • SQL%FOUND Attribute: Were Any Rows Affected?
    • SQL%NOTFOUND Attribute: Were No Rows Affected?
    • SQL%ROWCOUNT Attribute: How Many Rows Were Affected?
  • Explicit Cursors
    • Declaring and Defining Explicit Cursors
    • Opening and Closing Explicit Cursors
    • Fetching Data with Explicit Cursors
    • Variables in Explicit Cursor Queries
    • When Explicit Cursor Queries Need Column Aliases
    • Explicit Cursors that Accept Parameters
    • Explicit Cursor Attributes
• Query Result Set Processing
  • Query Result Set Processing With SELECT INTO Statements
    • Single-Row Result Sets
    • Large Multiple-Row Result Sets
  • Query Result Set Processing With Cursor FOR LOOP Statements
  • Query Result Set Processing With Explicit Cursors, OPEN, FETCH, and CLOSE
  • Query Result Set Processing with Subqueries
• Cursor Variables
  • Creating Cursor Variables
  • Opening and Closing Cursor Variables
  • Fetching Data with Cursor Variables
  • Assigning Values to Cursor Variables
  • Variables in Cursor Variable Queries
  • Cursor Variable Attributes
  • Cursor Variables as Subprogram Parameters
  • Cursor Variables as Host Variables
• CURSOR Expressions
• Transaction Processing and Control
  • COMMIT Statement
  • ROLLBACK Statement
  • SAVEPOINT Statement
  • Implicit Rollbacks
  • SET TRANSACTION Statement
  • Overriding Default Locking
    • LOCK TABLE Statement
    • SELECT FOR UPDATE and FOR UPDATE Cursors
    • Simulating CURRENT OF Clause with ROWID Pseudocolumn
• Autonomous Transactions
  • Advantages of Autonomous Transactions
  • Transaction Context
  • Transaction Visibility
  • Declaring Autonomous Transactions
  • Controlling Autonomous Transactions
    • Entering and Exiting
    • Committing and Rolling Back
    • Savepoints
    • Avoiding Errors with Autonomous Transactions
  • Autonomous Triggers
  • Invoking Autonomous Functions from SQL

7 PL/SQL Dynamic SQL

• When You Need Dynamic SQL
• Native Dynamic SQL
  • EXECUTE IMMEDIATE Statement
  • OPEN FOR, FETCH, and CLOSE Statements
  • Repeated Placeholder Names in Dynamic SQL Statements
    • Dynamic SQL Statement is Not Anonymous Block or CALL Statement
    • Dynamic SQL Statement is Anonymous Block or CALL Statement
• DBMS_SQL Package
  • DBMS_SQL.TO_REFCURSOR Function
  • DBMS_SQL.TO_CURSOR_NUMBER Function
• SQL Injection
  • SQL Injection Techniques
    • Statement Modification
    • Statement Injection
    • Data Type Conversion
  • Guarding Against SQL Injection
    • Bind Variables
    • Validation Checks
    • Explicit Format Models

8 PL/SQL Subprograms

• Reasons to Use Subprograms
• Nested, Package, and Standalone Subprograms
• Subprogram Invocations
• Subprogram Parts
  • Additional Parts for Functions
  • RETURN Statement
    • RETURN Statement in Function
    • RETURN Statement in Procedure
    • RETURN Statement in Anonymous Block
• Forward Declaration
• Subprogram Parameters
  • Formal and Actual Subprogram Parameters
  • Subprogram Parameter Passing Methods
  • Subprogram Parameter Modes
  • Subprogram Parameter Aliasing
    • Subprogram Parameter Aliasing with Parameters Passed by Reference
    • Subprogram Parameter Aliasing with Cursor Variable Parameters
  • Default Values for IN Subprogram Parameters
  • Positional, Named, and Mixed Notation for Actual Parameters
• Subprogram Invocation Resolution
• Overloaded Subprograms
  • Formal Parameters that Differ Only in Numeric Data Type
  • Subprograms that You Cannot Overload
  • Subprogram Overload Errors
• Recursive Subprograms
• Subprogram Side Effects
• PL/SQL Function Result Cache
  • Enabling Result-Caching for a Function
  • Developing Applications with Result-Cached Functions
  • Restrictions on Result-Cached Functions
  • Examples of Result-Cached Functions
    • Result-Cached Application Configuration Parameters
    • Result-Cached Recursive Function
  • Advanced Result-Cached Function Topics
    • Rules for a Cache Hit
    • Result Cache Bypass
    • Making Result-Cached Functions Handle Session-Specific Settings
    • Making Result-Cached Functions Handle Session-Specific Application Contexts
    • Choosing Result-Caching Granularity
    • Result Caches in Oracle RAC Environment
    • Result Cache Management
    • Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend
• PL/SQL Functions that SQL Statements Can Invoke
• Invoker's Rights and Definer's Rights (AUTHID Property)
  • Choosing AUTHID CURRENT_USER or AUTHID DEFINER
  • AUTHID and SQL Command SET ROLE
  • Need for Template Objects in IR Units
  • Overriding Default Name Resolution in IR Units
  • IR Subprograms, Views, and Database Triggers
  • IR Database Links
  • IR ADTs
  • IR Instance Methods
• External Subprograms

9 PL/SQL Triggers

• Overview of Triggers
• Reasons to Use Triggers
• DML Triggers
  • Conditional Predicates for Detecting Triggering DML Statement
  • Correlation Names and Pseudorecords
  • OBJECT_VALUE Pseudocolumn
  • INSTEAD OF DML Triggers
  • Compound DML Triggers
    • Compound DML Trigger Structure
    • Compound DML Trigger Restrictions
    • Performance Benefit of Compound DML Triggers
    • Using Compound DML Triggers with Bulk Insertion
    • Using Compound DML Triggers to Avoid Mutating-Table Error
  • Triggers for Ensuring Referential Integrity
    • Foreign Key Trigger for Child Table
    • UPDATE and DELETE RESTRICT Trigger for Parent Table
    • UPDATE and DELETE SET NULL Trigger for Parent Table
    • DELETE CASCADE Trigger for Parent Table
    • UPDATE CASCADE Trigger for Parent Table
    • Triggers for Complex Constraint Checking
    • Triggers for Complex Security Authorizations
    • Triggers for Transparent Event Logging
    • Triggers for Deriving Column Values
    • Triggers for Building Complex Updatable Views
    • Triggers for Fine-Grained Access Control
• System Triggers
  • SCHEMA Triggers
  • DATABASE Triggers
  • INSTEAD OF CREATE Triggers
• Subprograms Invoked by Triggers
• Trigger Compilation, Invalidation, and Recompilation
• Exception Handling in Triggers
• Trigger Design Guidelines
• Trigger Restrictions
  • Trigger Size Restriction
  • Trigger LONG and LONG RAW Data Type Restrictions
  • Mutating-Table Restriction
• Order in Which Triggers Fire
• Trigger Enabling and Disabling
• Trigger Changing and Debugging
• Triggers and Oracle Database Data Transfer Utilities
• Triggers for Publishing Events
  • Event Attribute Functions
  • Event Attribute Functions for Database Event Triggers
  • Event Attribute Functions for Client Event Triggers
• Views for Information About Triggers

10 PL/SQL Packages

• What is a Package?
• Reasons to Use Packages
• Package Specification
  • Appropriate Public Items
  • Creating Package Specifications
• Package Body
• Package Instantiation and Initialization
• Package State
• SERIALLY_REUSABLE Packages
  • Creating SERIALLY_REUSABLE Packages
  • SERIALLY_REUSABLE Package Work Unit
  • Explicit Cursors in SERIALLY_REUSABLE Packages
• Package Writing Guidelines
• Package Example
• How STANDARD Package Defines the PL/SQL Environment

11 PL/SQL Error Handling

• Compile-Time Warnings
  • DBMS_WARNING Package
• Overview of Exception Handling
  • Exception Categories
  • Advantages of Exception Handlers
  • Guidelines for Avoiding and Handling Exceptions
• Internally Defined Exceptions
• Predefined Exceptions
• User-Defined Exceptions
• Redeclared Predefined Exceptions
• Raising Exceptions Explicitly
  • RAISE Statement
    • Raising User-Defined Exception with RAISE Statement
    • Raising Internally Defined Exception with RAISE Statement
    • Reraising Current Exception with RAISE Statement
  • RAISE_APPLICATION_ERROR Procedure
• Exception Propagation
  • Propagation of Exceptions Raised in Declarations
  • Propagation of Exceptions Raised in Exception Handlers
• Unhandled Exceptions
• Error Code and Error Message Retrieval
• Continuing Execution After Handling Exceptions
• Retrying Transactions After Handling Exceptions

12 PL/SQL Optimization and Tuning

• PL/SQL Optimizer
  • Subprogram Inlining
• Candidates for Tuning
• Minimizing CPU Overhead
  • Tune SQL Statements
  • Tune Function Invocations in Queries
  • Tune Subprogram Invocations
  • Tune Loops
  • Tune Computation-Intensive PL/SQL Code
    • Use Data Types that Use Hardware Arithmetic
    • Avoid Constrained Subtypes in Performance-Critical Code
    • Minimize Implicit Data Type Conversion
  • Use SQL Character Functions
  • Put Least Expensive Conditional Tests First
• Bulk SQL and Bulk Binding
  • FORALL Statement
    • FORALL Statements for Sparse Collections
    • Unhandled Exceptions in FORALL Statements
    • Handling FORALL Exceptions Immediately
    • Handling FORALL Exceptions After FORALL Statement Completes
    • Getting Number of Rows Affected by FORALL Statement
  • BULK COLLECT Clause
    • SELECT INTO Statement with BULK COLLECT Clause
    • FETCH Statement with BULK COLLECT Clause
    • RETURNING INTO Clause with BULK COLLECT Clause
  • Using FORALL Statement and BULK COLLECT Clause Together
  • Client Bulk-Binding of Host Arrays
• Chaining Pipelined Table Functions for Multiple Transformations
  • Overview of Table Functions
  • Creating Pipelined Table Functions
  • Pipelined Table Functions as Transformation Functions
  • Chaining Pipelined Table Functions
  • Fetching from Results of Pipelined Table Functions
  • Passing CURSOR Expressions to Pipelined Table Functions
  • DML Statements on Pipelined Table Function Results
  • NO_DATA_NEEDED Exception
• Updating Large Tables in Parallel
• Collecting Data About User-Defined Identifiers
• Profiling and Tracing PL/SQL Programs
  • Profiler API: Package DBMS_PROFILER
  • Trace API: Package DBMS_TRACE
• Compiling PL/SQL Units for Native Execution
  • Determining Whether to Use PL/SQL Native Compilation
  • How PL/SQL Native Compilation Works
  • Dependencies, Invalidation, and Revalidation
  • Setting Up a New Database for PL/SQL Native Compilation
  • Compiling the Entire Database for PL/SQL Native or Interpreted Compilation

13 PL/SQL Language Elements

• Assignment Statement
• AUTONOMOUS_TRANSACTION Pragma
• Basic LOOP Statement
• Block
• CASE Statement
• CLOSE Statement
• Collection Variable Declaration
• Collection Method Invocation
• Comment
• Constant Declaration
• CONTINUE Statement
• Cursor FOR LOOP Statement
• Cursor Variable Declaration
• DELETE Statement Extension
• EXCEPTION_INIT Pragma
• Exception Declaration
• Exception Handler
• EXECUTE IMMEDIATE Statement
• EXIT Statement
• Explicit Cursor Declaration and Definition
• Expression
• FETCH Statement
• FOR LOOP Statement
• FORALL Statement
• Formal Parameter Declaration
• Function Declaration and Definition
• GOTO Statement
• IF Statement
• Implicit Cursor Attribute
• INLINE Pragma
• INSERT Statement Extension
• Named Cursor Attribute
• NULL Statement
• OPEN Statement
• OPEN FOR Statement
• PIPE ROW Statement
• Procedure Declaration and Definition
• RAISE Statement
• Record Variable Declaration
• RESTRICT_REFERENCES Pragma
• RETURN Statement
• RETURNING INTO Clause
• %ROWTYPE Attribute
• Scalar Variable Declaration
• SELECT INTO Statement
• SERIALLY_REUSABLE Pragma
• SQLCODE Function
• SQLERRM Function
• %TYPE Attribute
• UPDATE Statement Extensions
• WHILE LOOP Statement

14 SQL Statements for Stored PL/SQL Units

• ALTER FUNCTION Statement
• ALTER LIBRARY Statement
• ALTER PACKAGE Statement
• ALTER PROCEDURE Statement
• ALTER TRIGGER Statement
• ALTER TYPE Statement
• CREATE FUNCTION Statement
• CREATE LIBRARY Statement
• CREATE PACKAGE Statement
• CREATE PACKAGE BODY Statement
• CREATE PROCEDURE Statement
• CREATE TRIGGER Statement
• CREATE TYPE Statement
• CREATE TYPE BODY Statement
• DROP FUNCTION Statement
• DROP LIBRARY Statement
• DROP PACKAGE Statement
• DROP PROCEDURE Statement
• DROP TRIGGER Statement
• DROP TYPE Statement
• DROP TYPE BODY Statement

A PL/SQL Source Text Wrapping

• PL/SQL Source Text Wrapping Limitations
• PL/SQL Source Text Wrapping Guidelines
• PL/SQL Source Text Wrapping with PL/SQL Wrapper Utility
• PL/SQL Source Text Wrapping with DBMS_DDL Subprograms

B PL/SQL Name Resolution

• Qualified Names and Dot Notation
• Column Name Precedence
• Differences Between PL/SQL and SQL Name Resolution Rules
• Resolution of Names in Static SQL Statements
• What is Capture?
  • Outer Capture
  • Same-Scope Capture
  • Inner Capture
• Avoiding Inner Capture in SELECT and DML Statements
  • Qualifying References to Attributes and Methods
  • Qualifying References to Row Expressions

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

E PL/SQL Predefined Data Types

Index