Table of Contents
- List of Examples
- List of Figures
- List of Tables
- Title and Copyright Information
- Preface
- Changes in This Release for Oracle Database PL/SQL Language Reference
-
1
Overview of PL/SQL
- 1.1 Advantages of PL/SQL
- 1.2 Main Features of PL/SQL
- 1.3 Architecture of PL/SQL
-
2
PL/SQL Language Fundamentals
- 2.1 Character Sets
- 2.2 Lexical Units
- 2.3 Declarations
- 2.4 References to Identifiers
- 2.5 Scope and Visibility of Identifiers
- 2.6 Assigning Values to Variables
- 2.7 Expressions
- 2.8 Error-Reporting Functions
- 2.9 Conditional Compilation
-
3
PL/SQL Data Types
- 3.1 SQL Data Types
- 3.2 BOOLEAN Data Type
- 3.3 PLS_INTEGER and BINARY_INTEGER Data Types
- 3.4 User-Defined PL/SQL Subtypes
- 4 PL/SQL Control Statements
-
5
PL/SQL Collections and Records
- 5.1 Collection Types
- 5.2 Associative Arrays
- 5.3 Varrays (Variable-Size Arrays)
- 5.4 Nested Tables
- 5.5 Collection Constructors
- 5.6 Qualified Expressions Overview
- 5.7 Assigning Values to Collection Variables
- 5.8 Multidimensional Collections
- 5.9 Collection Comparisons
- 5.10 Collection Methods
- 5.11 Collection Types Defined in Package Specifications
- 5.12 Record Variables
- 5.13 Assigning Values to Record Variables
- 5.14 Record Comparisons
- 5.15 Inserting Records into Tables
- 5.16 Updating Rows with Records
- 5.17 Restrictions on Record Inserts and Updates
-
6
PL/SQL Static SQL
- 6.1 Description of Static SQL
-
6.2
Cursors Overview
- 6.2.1 Implicit Cursors
-
6.2.2
Explicit Cursors
- 6.2.2.1 Declaring and Defining Explicit Cursors
- 6.2.2.2 Opening and Closing Explicit Cursors
- 6.2.2.3 Fetching Data with Explicit Cursors
- 6.2.2.4 Variables in Explicit Cursor Queries
- 6.2.2.5 When Explicit Cursor Queries Need Column Aliases
- 6.2.2.6 Explicit Cursors that Accept Parameters
- 6.2.2.7 Explicit Cursor Attributes
- 6.3 Processing Query Result Sets
-
6.4
Cursor Variables
- 6.4.1 Creating Cursor Variables
- 6.4.2 Opening and Closing Cursor Variables
- 6.4.3 Fetching Data with Cursor Variables
- 6.4.4 Assigning Values to Cursor Variables
- 6.4.5 Variables in Cursor Variable Queries
- 6.4.6 Querying a Collection
- 6.4.7 Cursor Variable Attributes
- 6.4.8 Cursor Variables as Subprogram Parameters
- 6.4.9 Cursor Variables as Host Variables
- 6.5 CURSOR Expressions
- 6.6 Transaction Processing and Control
- 6.7 Autonomous Transactions
- 7 PL/SQL Dynamic SQL
-
8
PL/SQL Subprograms
- 8.1 Reasons to Use Subprograms
- 8.2 Nested, Package, and Standalone Subprograms
- 8.3 Subprogram Invocations
- 8.4 Subprogram Properties
- 8.5 Subprogram Parts
- 8.6 Forward Declaration
- 8.7 Subprogram Parameters
- 8.8 Subprogram Invocation Resolution
- 8.9 Overloaded Subprograms
- 8.10 Recursive Subprograms
- 8.11 Subprogram Side Effects
-
8.12
PL/SQL Function Result Cache
- 8.12.1 Enabling Result-Caching for a Function
- 8.12.2 Developing Applications with Result-Cached Functions
- 8.12.3 Requirements for Result-Cached Functions
- 8.12.4 Examples of Result-Cached Functions
-
8.12.5
Advanced Result-Cached Function Topics
- 8.12.5.1 Rules for a Cache Hit
- 8.12.5.2 Result Cache Bypass
- 8.12.5.3 Making Result-Cached Functions Handle Session-Specific Settings
- 8.12.5.4 Making Result-Cached Functions Handle Session-Specific Application Contexts
- 8.12.5.5 Choosing Result-Caching Granularity
- 8.12.5.6 Result Caches in Oracle RAC Environment
- 8.12.5.7 Result Cache Management
- 8.12.5.8 Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend
- 8.13 PL/SQL Functions that SQL Statements Can Invoke
- 8.14 Invoker's Rights and Definer's Rights (AUTHID Property)
- 8.15 External Subprograms
-
9
PL/SQL Triggers
- 9.1 Overview of Triggers
- 9.2 Reasons to Use Triggers
-
9.3
DML Triggers
- 9.3.1 Conditional Predicates for Detecting Triggering DML Statement
- 9.3.2 INSTEAD OF DML Triggers
- 9.3.3 Compound DML Triggers
-
9.3.4
Triggers for Ensuring Referential Integrity
- 9.3.4.1 Foreign Key Trigger for Child Table
- 9.3.4.2 UPDATE and DELETE RESTRICT Trigger for Parent Table
- 9.3.4.3 UPDATE and DELETE SET NULL Trigger for Parent Table
- 9.3.4.4 DELETE CASCADE Trigger for Parent Table
- 9.3.4.5 UPDATE CASCADE Trigger for Parent Table
- 9.3.4.6 Triggers for Complex Constraint Checking
- 9.3.4.7 Triggers for Complex Security Authorizations
- 9.3.4.8 Triggers for Transparent Event Logging
- 9.3.4.9 Triggers for Deriving Column Values
- 9.3.4.10 Triggers for Building Complex Updatable Views
- 9.3.4.11 Triggers for Fine-Grained Access Control
- 9.4 Correlation Names and Pseudorecords
- 9.5 System Triggers
- 9.6 Subprograms Invoked by Triggers
- 9.7 Trigger Compilation, Invalidation, and Recompilation
- 9.8 Exception Handling in Triggers
- 9.9 Trigger Design Guidelines
- 9.10 Trigger Restrictions
- 9.11 Order in Which Triggers Fire
- 9.12 Trigger Enabling and Disabling
- 9.13 Trigger Changing and Debugging
- 9.14 Triggers and Oracle Database Data Transfer Utilities
- 9.15 Triggers for Publishing Events
- 9.16 Views for Information About Triggers
-
10
PL/SQL Packages
- 10.1 What is a Package?
- 10.2 Reasons to Use Packages
- 10.3 Package Specification
- 10.4 Package Body
- 10.5 Package Instantiation and Initialization
- 10.6 Package State
- 10.7 SERIALLY_REUSABLE Packages
- 10.8 Package Writing Guidelines
- 10.9 Package Example
- 10.10 How STANDARD Package Defines the PL/SQL Environment
-
11
PL/SQL Error Handling
- 11.1 Compile-Time Warnings
- 11.2 Overview of Exception Handling
- 11.3 Internally Defined Exceptions
- 11.4 Predefined Exceptions
- 11.5 User-Defined Exceptions
- 11.6 Redeclared Predefined Exceptions
- 11.7 Raising Exceptions Explicitly
- 11.8 Exception Propagation
- 11.9 Unhandled Exceptions
- 11.10 Retrieving Error Code and Error Message
- 11.11 Continuing Execution After Handling Exceptions
- 11.12 Retrying Transactions After Handling Exceptions
- 11.13 Handling Errors in Distributed Queries
-
12
PL/SQL Optimization and Tuning
- 12.1 PL/SQL Optimizer
- 12.2 Candidates for Tuning
- 12.3 Minimizing CPU Overhead
-
12.4
Bulk SQL and Bulk Binding
- 12.4.1 FORALL Statement
- 12.4.2 BULK COLLECT Clause
- 12.4.3 Using FORALL Statement and BULK COLLECT Clause Together
- 12.4.4 Client Bulk-Binding of Host Arrays
-
12.5
Chaining Pipelined Table Functions for Multiple Transformations
- 12.5.1 Overview of Table Functions
- 12.5.2 Creating Pipelined Table Functions
- 12.5.3 Pipelined Table Functions as Transformation Functions
- 12.5.4 Chaining Pipelined Table Functions
- 12.5.5 Fetching from Results of Pipelined Table Functions
- 12.5.6 Passing CURSOR Expressions to Pipelined Table Functions
- 12.5.7 DML Statements on Pipelined Table Function Results
- 12.5.8 NO_DATA_NEEDED Exception
-
12.6
Overview of Polymorphic Table Functions
- 12.6.1 Polymorphic Table Function Definition
- 12.6.2 Polymorphic Table Function Implementation
- 12.6.3 Polymorphic Table Function Invocation
- 12.6.4 Polymorphic Table Function Compilation and Execution
- 12.6.5 Polymorphic Table Function Optimization
- 12.6.6 Skip_col Polymorphic Table Function Example
- 12.6.7 To_doc Polymorphic Table Function Example
- 12.6.8 Implicit_echo Polymorphic Table Function Example
- 12.7 Updating Large Tables in Parallel
- 12.8 Collecting Data About User-Defined Identifiers
- 12.9 Profiling and Tracing PL/SQL Programs
-
12.10
Compiling PL/SQL Units for Native Execution
- 12.10.1 Determining Whether to Use PL/SQL Native Compilation
- 12.10.2 How PL/SQL Native Compilation Works
- 12.10.3 Dependencies, Invalidation, and Revalidation
- 12.10.4 Setting Up a New Database for PL/SQL Native Compilation
- 12.10.5 Compiling the Entire Database for PL/SQL Native or Interpreted Compilation
-
13
PL/SQL Language Elements
- 13.1 ACCESSIBLE BY Clause
- 13.2 AGGREGATE Clause
- 13.3 Assignment Statement
- 13.4 AUTONOMOUS_TRANSACTION Pragma
- 13.5 Basic LOOP Statement
- 13.6 Block
- 13.7 Call Specification
- 13.8 CASE Statement
- 13.9 CLOSE Statement
- 13.10 Collection Method Invocation
- 13.11 Collection Variable Declaration
- 13.12 Comment
- 13.13 COMPILE Clause
- 13.14 Constant Declaration
- 13.15 CONTINUE Statement
- 13.16 COVERAGE Pragma
- 13.17 Cursor FOR LOOP Statement
- 13.18 Cursor Variable Declaration
- 13.19 Datatype Attribute
- 13.20 DEFAULT COLLATION Clause
- 13.21 DELETE Statement Extension
- 13.22 DEPRECATE Pragma
- 13.23 DETERMINISTIC Clause
- 13.24 Element Specification
- 13.25 EXCEPTION_INIT Pragma
- 13.26 Exception Declaration
- 13.27 Exception Handler
- 13.28 EXECUTE IMMEDIATE Statement
- 13.29 EXIT Statement
- 13.30 Explicit Cursor Declaration and Definition
- 13.31 Expression
- 13.32 FETCH Statement
- 13.33 FOR LOOP Statement
- 13.34 FORALL Statement
- 13.35 Formal Parameter Declaration
- 13.36 Function Declaration and Definition
- 13.37 GOTO Statement
- 13.38 IF Statement
- 13.39 Implicit Cursor Attribute
- 13.40 INLINE Pragma
- 13.41 Invoker’s Rights and Definer’s Rights Clause
- 13.42 INSERT Statement Extension
- 13.43 Named Cursor Attribute
- 13.44 NULL Statement
- 13.45 OPEN Statement
- 13.46 OPEN FOR Statement
- 13.47 PARALLEL_ENABLE Clause
- 13.48 PIPE ROW Statement
- 13.49 PIPELINED Clause
- 13.50 Procedure Declaration and Definition
- 13.51 RAISE Statement
- 13.52 Record Variable Declaration
- 13.53 RESTRICT_REFERENCES Pragma
- 13.54 RETURN Statement
- 13.55 RETURNING INTO Clause
- 13.56 RESULT_CACHE Clause
- 13.57 %ROWTYPE Attribute
- 13.58 Scalar Variable Declaration
- 13.59 SELECT INTO Statement
- 13.60 SERIALLY_REUSABLE Pragma
- 13.61 SHARING Clause
- 13.62 SQLCODE Function
- 13.63 SQLERRM Function
- 13.64 %TYPE Attribute
- 13.65 UDF Pragma
- 13.66 UPDATE Statement Extensions
- 13.67 WHILE LOOP Statement
-
14
SQL Statements for Stored PL/SQL Units
- 14.1 ALTER FUNCTION Statement
- 14.2 ALTER LIBRARY Statement
- 14.3 ALTER PACKAGE Statement
- 14.4 ALTER PROCEDURE Statement
- 14.5 ALTER TRIGGER Statement
- 14.6 ALTER TYPE Statement
- 14.7 CREATE FUNCTION Statement
- 14.8 CREATE LIBRARY Statement
- 14.9 CREATE PACKAGE Statement
- 14.10 CREATE PACKAGE BODY Statement
- 14.11 CREATE PROCEDURE Statement
- 14.12 CREATE TRIGGER Statement
- 14.13 CREATE TYPE Statement
- 14.14 CREATE TYPE BODY Statement
- 14.15 DROP FUNCTION Statement
- 14.16 DROP LIBRARY Statement
- 14.17 DROP PACKAGE Statement
- 14.18 DROP PROCEDURE Statement
- 14.19 DROP TRIGGER Statement
- 14.20 DROP TYPE Statement
- 14.21 DROP TYPE BODY Statement
- A PL/SQL Source Text Wrapping
- B PL/SQL Name Resolution
- C PL/SQL Program Limits
- D PL/SQL Reserved Words and Keywords
- E PL/SQL Predefined Data Types
- Index