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 Features for 11g Release 2 (11.2)
• PL/SQL Features for 11g Release 1 (11.1)

1 Overview of PL/SQL

• Advantages of PL/SQL
  • Tight Integration with SQL
  • High Performance
  • High Productivity
  • Portability
  • Scalability
  • Manageability
  • Tight Security
  • Access to Predefined Packages
  • Support for Object-Oriented Programming
  • Support for Developing Web Applications
  • Support for Developing Server Pages
• Main Features of PL/SQL
  • Blocks
  • Error Handling
  • Input and Output
  • Variables and Constants
  • Data Abstraction
    • Cursors
    • Composite Variables
    • %ROWTYPE Attribute
    • %TYPE Attribute
    • Abstract Data Types
  • Control Statements
  • Subprograms
  • Triggers
  • Packages (APIs Written in PL/SQL)
  • 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
    • Quoted Identifiers
  • Literals
    • Numeric Literals
    • Character Literals
    • String Literals
    • BOOLEAN Literals
    • Datetime and Interval Literals
  • Comments
    • Single-Line Comments
    • Multiline Comments
• Declarations
  • Variable Declarations
  • Constant Declarations
  • Initial Values of Variables and Constants
  • NOT NULL Constraint
  • %TYPE Attribute
  • %ROWTYPE 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
    • Relational Operators
    • IS [NOT] NULL Operator
    • 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

• Subtypes
• Predefined PL/SQL Scalar Data Types and Subtypes
  • Predefined PL/SQL Numeric Data Types and Subtypes
    • PLS_INTEGER and BINARY_INTEGER Data Types
    • SIMPLE_INTEGER Subtype of PLS_INTEGER
    • BINARY_FLOAT and BINARY_DOUBLE Data Types
    • NUMBER Data Type
  • Predefined PL/SQL Character Data Types and Subtypes
    • CHAR and VARCHAR2 Data Types
    • RAW Data Type
    • NCHAR and NVARCHAR2 Data Types
    • LONG and LONG RAW Data Types
    • ROWID and UROWID Data Types
  • Predefined PL/SQL BOOLEAN Data Type
  • Predefined PL/SQL Datetime and Interval Data Types
    • DATE Data Type
    • TIMESTAMP Data Type
    • TIMESTAMP WITH TIME ZONE Data Type
    • TIMESTAMP WITH LOCAL TIME ZONE Data Type
    • INTERVAL YEAR TO MONTH Data Type
    • INTERVAL DAY TO SECOND Data Type
    • Datetime and Interval Arithmetic
    • Avoiding Truncation Problems with Date and Time Subtypes
• Predefined PL/SQL Large Object (LOB) Data Types
  • BFILE Data Type
  • BLOB Data Type
  • CLOB Data Type
  • NCLOB Data Type
• User-Defined PL/SQL Subtypes
• PL/SQL Data Type Conversion
  • Explicit Conversion
  • Implicit Conversion

4 PL/SQL Control Statements

• Overview of 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
  • WHILE LOOP Statement
  • FOR LOOP Statement
    • FOR LOOP Index
    • Lower Bound and Upper Bound
    • EXIT Statement in FOR LOOP Statement
• Sequential Control Statements
  • GOTO Statement
  • NULL Statement

5 PL/SQL Collections and Records

• PL/SQL Collection Type Characteristics
  • Associative Arrays
  • Nested Tables
  • Variable-Size Arrays (Varrays)
• Collection Type Definitions
• Collection Variable Declarations
• Collection Initialization
• Collection Element References
• Assignments to Collections
• Collection Comparisons
• Multidimensional Collections
• Collection Methods
  • EXISTS Method
  • COUNT Method
  • LIMIT Method
  • FIRST and LAST Methods
  • PRIOR and NEXT Methods
  • EXTEND Method
  • TRIM Method
  • DELETE Method
• Collection Exceptions
• Record Definitions and Declarations
• Records as Subprogram Parameters and Function Return Values
• Assignments to Records
• Record Comparisons
• Inserting Records into Tables
• Updating Rows with Records
• Returning Rows into Records
• Restrictions on Record Inserts and Updates
• Assignments to Collections of Records

6 PL/SQL Static SQL

• Description of Static SQL
• Resolution of Names in Static SQL Statements
• SQL Pseudocolumns
  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM
• 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 Explicit Cursors
    • Opening Explicit Cursors
    • Fetching Data with Explicit Cursors
    • Fetching Bulk Data with Explicit Cursors
    • Closing Explicit Cursors
    • Explicit Cursor Attributes
    • When Explicit Cursors Need Column Aliases
    • Explicit Cursors that Accept Parameters
• Queries
  • Selecting At Most One Row (SELECT INTO Statement)
  • Selecting Multiple Rows (BULK COLLECT Clause)
  • Looping Through Multiple Rows (Cursor FOR LOOP Statement)
    • Implicit Cursor FOR LOOP
    • Explicit Cursor FOR LOOP
    • Column Aliases for Expression Values in Cursor FOR LOOP
  • Processing Complicated Queries with Explicit Cursors
• Subqueries
• Cursor Variables
  • Purpose of Cursor Variables
  • Cursor Variable Creation
  • Cursor Variables as Subprogram Parameters
  • Cursor Variable Control (OPEN FOR, FETCH, and CLOSE Statements)
    • Opening Cursor Variables
    • Fetching from Cursor Variables
    • Closing Cursor Variables
    • INVALID_CURSOR Exception
  • Cursor Variables as Host Variables
  • Cursor Variable Restrictions
• 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 Arguments
    • Validation Checks
    • Explicit Format Models

8 PL/SQL Subprograms

• Overview of PL/SQL Subprograms
• Subprogram Parts
• Nested Subprograms that Invoke Each Other
• Subprogram Parameters
  • Formal and Actual Subprogram Parameters
  • Subprogram Parameter Passing Methods
  • Subprogram Parameter Modes
    • IN Mode
    • OUT Mode
    • IN OUT Mode
    • Summary of Subprogram Parameter Modes
  • Initial Values for Subprogram Parameters
  • Positional, Named, and Mixed Notation for Actual Parameters
• Overloaded Subprograms
  • Subprograms that You Can Overload
  • Subprograms that You Cannot Overload
  • Subprogram Overload Errors
• Subprogram Call Resolution
• 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
• Recursive Subprograms
• External Subprograms
• Subprogram Side Effects
• Subprogram Parameter Aliasing
• 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

9 PL/SQL Triggers

• Overview of Triggers
  • Trigger Types
  • OLD and NEW Pseudorecords
  • Trigger States
  • Data Access for Triggers
  • Trigger Uses
• Trigger Design Guidelines
• Trigger Creation
  • Trigger Names
  • Triggering Statements
    • SQL*Loader and Import
    • How Column Lists Affect UPDATE Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD OF Triggers
    • Views that Require INSTEAD OF Triggers
    • Triggers on Nested Table View Columns
    • INSTEAD OF Trigger Example
  • FOR EACH ROW Triggers
  • Conditional Triggers
  • Compound Triggers
    • Compound Trigger Uses
    • Compound Trigger Sections
    • Triggering Statements of Compound Triggers
    • Compound Trigger Restrictions
    • Compound Trigger Example
    • Using Compound Triggers to Avoid Mutating-Table Error
  • Order in Which Triggers Fire
• Trigger Body
  • Column Values Accessible to Row Triggers
    • Example: Trigger That Modifies LOB Columns
    • INSTEAD OF Triggers on Nested Table View Columns
    • Trigger Name Conflicts
    • Conditional Predicates for Detecting the DML Operation that Fired a Trigger
    • Exceptions Raised in the Trigger Body
  • OBJECT_VALUE Pseudocolumn
  • Remote Exception Handling
  • Trigger Body Restrictions
    • Size Restriction
    • SQL Statement Restrictions
    • LONG and LONG RAW Data Type Restrictions
    • Mutating Table Restriction
    • Restrictions on Mutating Tables Relaxed
    • System Trigger Restrictions
• Trigger Compilation
  • Trigger Dependencies
  • Trigger Recompilation
• Trigger Replacement
• Trigger Debugging
• Trigger Enabling
• Trigger Disabling
• Views for Information About Triggers
• Trigger Application Examples
  • Triggers for Auditing
  • Triggers and Constraints
  • Triggers for Ensuring Referential Integrity
    • Foreign Key Trigger for Child Table
    • UPDATE and DELETE RESTRICT Triggers for Parent Table
    • UPDATE and DELETE SET NULL Triggers for Parent Table
    • DELETE Cascade Trigger for Parent Table
    • UPDATE Cascade Trigger for Parent Table
    • Triggers for Complex Check Constraints
    • 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
• Triggers that Publish Database Events
  • How Triggers Publish Events
  • Publication Context
  • Error Handling
  • Execution Model
  • Event Attribute Functions
  • Database Events
  • Client Events

10 PL/SQL Packages

• What is a Package?
• What Goes in a Package?
• Advantages of Packages
• Serially Reusable Packages
  • Package States
  • Why Serially Reusable Packages?
  • Syntax of Serially Reusable Packages
  • Semantics of Serially Reusable Packages
  • Examples of Serially Reusable Packages
• Package Specification
• Referencing Package Contents
• Package Body
• Examples of Package Features
• Private and Public Items in Packages
• How STANDARD Package Defines the PL/SQL Environment
• Overview of Product-Specific Packages
  • DBMS_ALERT Package
  • DBMS_OUTPUT Package
  • DBMS_PIPE Package
  • DBMS_CONNECTION_POOL Package
  • HTF and HTP Packages
  • UTL_FILE Package
  • UTL_HTTP Package
  • UTL_SMTP Package
• Packing Writing Guidelines
• Separating Cursor Specifications and Bodies with Packages

11 PL/SQL Error Handling

• Compile-Time Warnings
• DBMS_WARNING Package
• Overview of Exception Handling
  • Advantages of Exceptions
  • Guidelines for Avoiding and Handling Exceptions
• Predefined Exceptions
• User-Defined Exceptions
  • Exception Declarations
  • Exception Scope
  • Associating Exceptions with Numbers
  • User-Defined Error Messages
  • Redeclaring Predefined Exceptions
• How Exceptions Are Raised
• How Exceptions Propagate
• Reraising an Exception
• Exception Handlers
  • Exceptions Raised in Declarations
  • Exceptions Raised in Exception Handlers
  • Transferring Control to or from Exception Handlers
  • Error Code and Error Message Retrieval
  • Continuing Execution After an Exception Is Raised
  • Retrying a Transaction
  • Identifying Exception Locations With Locator Variables
• Unhandled Exceptions

12 PL/SQL Optimization and Tuning

• PL/SQL Optimizer
• Subprogram Inlining
• PL/SQL Code to Consider Tuning
• 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
  • Use SQL String Functions
  • Put Least Expensive Conditional Tests First
  • Minimize Implicit Data Type Conversion
  • Avoid NUMBER Data Type and Constrained Subtypes
    • Recommended Data Types for Integer Arithmetic
    • Recommended Data Types for Floating-Point Arithmetic
• Avoiding Memory Overhead in PL/SQL Code
  • Declare VARCHAR2 Variables of 4000 or More Characters
  • Group Related Subprograms into Packages
  • Pin Packages in the Shared Memory Pool
  • Apply Advice of Compiler Warnings
• Collecting Data About User-Defined Identifiers
• Profiling and Tracing PL/SQL Programs
  • Profiler API: Package DBMS_PROFILER
  • Trace API: Package DBMS_TRACE
• Reducing Loop Overhead with Bulk SQL
  • Running One DML Statement Multiple Times (FORALL Statement)
    • Effect of FORALL Exceptions on Rollbacks
    • Handling FORALL Exceptions
    • Counting Rows Affected by FORALL
  • Retrieving Query Results into Collections
    • Examples of Bulk Fetching from a Cursor
    • Limiting Rows for a Bulk FETCH Operation (LIMIT Clause)
    • Retrieving DML Results Into a Collection (RETURNING INTO Clause)
    • Using FORALL and BULK COLLECT Together
    • Host Arrays with Bulk Binds
    • SELECT BULK COLLECT INTO Statements and Aliasing
• Computation-Intensive PL/SQL Programs
• Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables
• Tuning PL/SQL Subprogram Calls with NOCOPY Hint
• 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
• Performing Multiple Transformations with Pipelined Table Functions
  • Overview of Pipelined Table Functions
  • Writing a Pipelined Table Function
  • 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 Results of Pipelined Table Functions
  • Passing Data with Cursor Variables
  • Performing DML Statements Inside Pipelined Table Functions
  • Performing DML Statements on Pipelined Table Functions
  • Exception Handlers in Pipelined Table Functions
• Updating Large Tables in Parallel

13 PL/SQL Language Elements

• Assignment Statement
• AUTONOMOUS_TRANSACTION Pragma
• Block
• Basic LOOP Statement
• CASE Statement
• CLOSE Statement
• Collection
• Collection Method Call
• Comment
• Constant
• CONTINUE Statement
• Cursor FOR LOOP Statement
• Cursor Variable
• DELETE Statement Extension
• EXCEPTION_INIT Pragma
• Exception
• Exception Handler
• EXECUTE IMMEDIATE Statement
• EXIT Statement
• Explicit Cursor
• Expression
• FETCH Statement
• FOR LOOP Statement
• FORALL Statement
• Function
• GOTO Statement
• IF Statement
• Implicit Cursor Attribute
• INLINE Pragma
• INSERT Statement Extension
• Literal
• Named Cursor Attribute
• NULL Statement
• OPEN Statement
• OPEN FOR Statement
• Parameter
• Procedure
• RAISE Statement
• Record
• RESTRICT_REFERENCES Pragma
• RETURN Statement
• RETURNING INTO Clause
• %ROWTYPE Attribute
• SELECT INTO Statement
• SERIALLY_REUSABLE Pragma
• SQLCODE Function
• SQLERRM Function
• %TYPE Attribute
• UPDATE Statement Extensions
• Variable
• 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 Code Wrapping

• Overview of Wrapping
• Guidelines for Wrapping
• Limitations of Wrapping
• Wrapping PL/SQL Code with wrap Utility
  • Input and Output Files for the PL/SQL wrap Utility
  • Running the wrap Utility
  • Limitations of the wrap Utility
• Wrapping PL/QL Code with DBMS_DDL Subprograms
  • DBMS_DDL.CREATE_WRAPPED Procedure
  • Limitation of the DBMS_DDL.WRAP Function

B PL/SQL Name Resolution

• What is Name Resolution?
  • Name Resolution
• Examples of Qualified Names and Dot Notation
• How Name Resolution Differs in PL/SQL and SQL
• What is Capture?
  • Inner Capture
  • Same-Scope Capture
  • Outer Capture
• Avoiding Inner Capture in 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

Index