Table of Contents
- List of Examples
- List of Figures
- List of Tables
- Title and Copyright Information
- Preface
- 1 Changes in This Release for Oracle Database PL/SQL Language Reference
-
2
Overview of PL/SQL
- 2.1 Advantages of PL/SQL
- 2.2 Main Features of PL/SQL
- 2.3 Architecture of PL/SQL
-
3
PL/SQL Language Fundamentals
- 3.1 Character Sets
- 3.2 Lexical Units
- 3.3 Declarations
- 3.4 References to Identifiers
- 3.5 Scope and Visibility of Identifiers
- 3.6 Assigning Values to Variables
- 3.7 Expressions
- 3.8 Error-Reporting Functions
- 3.9 Conditional Compilation
-
4
PL/SQL Data Types
- 4.1 SQL Data Types
- 4.2 BOOLEAN Data Type
- 4.3 PLS_INTEGER and BINARY_INTEGER Data Types
- 4.4 User-Defined PL/SQL Subtypes
-
5
PL/SQL Control Statements
- 5.1 Conditional Selection Statements
-
5.2
LOOP Statements
- 5.2.1 Basic LOOP Statement
-
5.2.2
FOR LOOP Statement Overview
- 5.2.2.1 FOR LOOP Iterand
- 5.2.2.2 Iterand Mutability
- 5.2.2.3 Multiple Iteration Controls
- 5.2.2.4 Stepped Range Iteration Controls
- 5.2.2.5 Single Expression Iteration Controls
- 5.2.2.6 Collection Iteration Controls
- 5.2.2.7 Cursor Iteration Controls
- 5.2.2.8 Using Dynamic SQL in Iteration Controls
- 5.2.2.9 Stopping and Skipping Predicate Clauses
- 5.2.3 WHILE LOOP Statement
- 5.3 Sequential Control Statements
-
6
PL/SQL Collections and Records
- 6.1 Collection Types
- 6.2 Associative Arrays
- 6.3 Varrays (Variable-Size Arrays)
- 6.4 Nested Tables
- 6.5 Collection Constructors
- 6.6 Qualified Expressions Overview
- 6.7 Assigning Values to Collection Variables
- 6.8 Multidimensional Collections
- 6.9 Collection Comparisons
- 6.10 Collection Methods
- 6.11 Collection Types Defined in Package Specifications
- 6.12 Record Variables
- 6.13 Assigning Values to Record Variables
- 6.14 Record Comparisons
- 6.15 Inserting Records into Tables
- 6.16 Updating Rows with Records
- 6.17 Restrictions on Record Inserts and Updates
-
7
PL/SQL Static SQL
- 7.1 Description of Static SQL
-
7.2
Cursors Overview
- 7.2.1 Implicit Cursors
-
7.2.2
Explicit Cursors
- 7.2.2.1 Declaring and Defining Explicit Cursors
- 7.2.2.2 Opening and Closing Explicit Cursors
- 7.2.2.3 Fetching Data with Explicit Cursors
- 7.2.2.4 Variables in Explicit Cursor Queries
- 7.2.2.5 When Explicit Cursor Queries Need Column Aliases
- 7.2.2.6 Explicit Cursors that Accept Parameters
- 7.2.2.7 Explicit Cursor Attributes
- 7.3 Processing Query Result Sets
-
7.4
Cursor Variables
- 7.4.1 Creating Cursor Variables
- 7.4.2 Opening and Closing Cursor Variables
- 7.4.3 Fetching Data with Cursor Variables
- 7.4.4 Assigning Values to Cursor Variables
- 7.4.5 Variables in Cursor Variable Queries
- 7.4.6 Querying a Collection
- 7.4.7 Cursor Variable Attributes
- 7.4.8 Cursor Variables as Subprogram Parameters
- 7.4.9 Cursor Variables as Host Variables
- 7.5 CURSOR Expressions
- 7.6 Transaction Processing and Control
- 7.7 Autonomous Transactions
- 8 PL/SQL Dynamic SQL
-
9
PL/SQL Subprograms
- 9.1 Reasons to Use Subprograms
- 9.2 Nested, Package, and Standalone Subprograms
- 9.3 Subprogram Invocations
- 9.4 Subprogram Properties
- 9.5 Subprogram Parts
- 9.6 Forward Declaration
- 9.7 Subprogram Parameters
- 9.8 Subprogram Invocation Resolution
- 9.9 Overloaded Subprograms
- 9.10 Recursive Subprograms
- 9.11 Subprogram Side Effects
-
9.12
PL/SQL Function Result Cache
- 9.12.1 Enabling Result-Caching for a Function
- 9.12.2 Developing Applications with Result-Cached Functions
- 9.12.3 Requirements for Result-Cached Functions
- 9.12.4 Examples of Result-Cached Functions
-
9.12.5
Advanced Result-Cached Function Topics
- 9.12.5.1 Rules for a Cache Hit
- 9.12.5.2 Result Cache Bypass
- 9.12.5.3 Making Result-Cached Functions Handle Session-Specific Settings
- 9.12.5.4 Making Result-Cached Functions Handle Session-Specific Application Contexts
- 9.12.5.5 Choosing Result-Caching Granularity
- 9.12.5.6 Result Caches in Oracle RAC Environment
- 9.12.5.7 Result Cache Management
- 9.12.5.8 Hot-Patching PL/SQL Units on Which Result-Cached Functions Depend
- 9.13 PL/SQL Functions that SQL Statements Can Invoke
- 9.14 Invoker's Rights and Definer's Rights (AUTHID Property)
- 9.15 External Subprograms
-
10
PL/SQL Triggers
- 10.1 Overview of Triggers
- 10.2 Reasons to Use Triggers
-
10.3
DML Triggers
- 10.3.1 Conditional Predicates for Detecting Triggering DML Statement
- 10.3.2 INSTEAD OF DML Triggers
- 10.3.3 Compound DML Triggers
-
10.3.4
Triggers for Ensuring Referential Integrity
- 10.3.4.1 Foreign Key Trigger for Child Table
- 10.3.4.2 UPDATE and DELETE RESTRICT Trigger for Parent Table
- 10.3.4.3 UPDATE and DELETE SET NULL Trigger for Parent Table
- 10.3.4.4 DELETE CASCADE Trigger for Parent Table
- 10.3.4.5 UPDATE CASCADE Trigger for Parent Table
- 10.3.4.6 Triggers for Complex Constraint Checking
- 10.3.4.7 Triggers for Complex Security Authorizations
- 10.3.4.8 Triggers for Transparent Event Logging
- 10.3.4.9 Triggers for Deriving Column Values
- 10.3.4.10 Triggers for Building Complex Updatable Views
- 10.3.4.11 Triggers for Fine-Grained Access Control
- 10.4 Correlation Names and Pseudorecords
- 10.5 System Triggers
- 10.6 Subprograms Invoked by Triggers
- 10.7 Trigger Compilation, Invalidation, and Recompilation
- 10.8 Exception Handling in Triggers
- 10.9 Trigger Design Guidelines
- 10.10 Trigger Restrictions
- 10.11 Order in Which Triggers Fire
- 10.12 Trigger Enabling and Disabling
- 10.13 Trigger Changing and Debugging
- 10.14 Triggers and Oracle Database Data Transfer Utilities
- 10.15 Triggers for Publishing Events
- 10.16 Views for Information About Triggers
-
11
PL/SQL Packages
- 11.1 What is a Package?
- 11.2 Reasons to Use Packages
- 11.3 Package Specification
- 11.4 Package Body
- 11.5 Package Instantiation and Initialization
- 11.6 Package State
- 11.7 SERIALLY_REUSABLE Packages
- 11.8 Package Writing Guidelines
- 11.9 Package Example
- 11.10 How STANDARD Package Defines the PL/SQL Environment
-
12
PL/SQL Error Handling
- 12.1 Compile-Time Warnings
- 12.2 Overview of Exception Handling
- 12.3 Internally Defined Exceptions
- 12.4 Predefined Exceptions
- 12.5 User-Defined Exceptions
- 12.6 Redeclared Predefined Exceptions
- 12.7 Raising Exceptions Explicitly
- 12.8 Exception Propagation
- 12.9 Unhandled Exceptions
- 12.10 Retrieving Error Code and Error Message
- 12.11 Continuing Execution After Handling Exceptions
- 12.12 Retrying Transactions After Handling Exceptions
- 12.13 Handling Errors in Distributed Queries
-
13
PL/SQL Optimization and Tuning
- 13.1 PL/SQL Optimizer
- 13.2 Candidates for Tuning
- 13.3 Minimizing CPU Overhead
-
13.4
Bulk SQL and Bulk Binding
- 13.4.1 FORALL Statement
- 13.4.2 BULK COLLECT Clause
- 13.4.3 Using FORALL Statement and BULK COLLECT Clause Together
- 13.4.4 Client Bulk-Binding of Host Arrays
-
13.5
Chaining Pipelined Table Functions for Multiple Transformations
- 13.5.1 Overview of Table Functions
- 13.5.2 Creating Pipelined Table Functions
- 13.5.3 Pipelined Table Functions as Transformation Functions
- 13.5.4 Chaining Pipelined Table Functions
- 13.5.5 Fetching from Results of Pipelined Table Functions
- 13.5.6 Passing CURSOR Expressions to Pipelined Table Functions
- 13.5.7 DML Statements on Pipelined Table Function Results
- 13.5.8 NO_DATA_NEEDED Exception
-
13.6
Overview of Polymorphic Table Functions
- 13.6.1 Polymorphic Table Function Definition
- 13.6.2 Polymorphic Table Function Implementation
- 13.6.3 Polymorphic Table Function Invocation
- 13.6.4 Polymorphic Table Function Compilation and Execution
- 13.6.5 Polymorphic Table Function Optimization
- 13.6.6 Skip_col Polymorphic Table Function Example
- 13.6.7 To_doc Polymorphic Table Function Example
- 13.6.8 Implicit_echo Polymorphic Table Function Example
- 13.7 Updating Large Tables in Parallel
- 13.8 Collecting Data About User-Defined Identifiers
- 13.9 Profiling and Tracing PL/SQL Programs
-
13.10
Compiling PL/SQL Units for Native Execution
- 13.10.1 Determining Whether to Use PL/SQL Native Compilation
- 13.10.2 How PL/SQL Native Compilation Works
- 13.10.3 Dependencies, Invalidation, and Revalidation
- 13.10.4 Setting Up a New Database for PL/SQL Native Compilation
- 13.10.5 Compiling the Entire Database for PL/SQL Native or Interpreted Compilation
-
14
PL/SQL Language Elements
- 14.1 ACCESSIBLE BY Clause
- 14.2 AGGREGATE Clause
- 14.3 Assignment Statement
- 14.4 AUTONOMOUS_TRANSACTION Pragma
- 14.5 Basic LOOP Statement
- 14.6 Block
- 14.7 Call Specification
- 14.8 CASE Statement
- 14.9 CLOSE Statement
- 14.10 Collection Method Invocation
- 14.11 Collection Variable Declaration
- 14.12 Comment
- 14.13 COMPILE Clause
- 14.14 Constant Declaration
- 14.15 CONTINUE Statement
- 14.16 COVERAGE Pragma
- 14.17 Cursor FOR LOOP Statement
- 14.18 Cursor Variable Declaration
- 14.19 Datatype Attribute
- 14.20 DEFAULT COLLATION Clause
- 14.21 DELETE Statement Extension
- 14.22 DEPRECATE Pragma
- 14.23 DETERMINISTIC Clause
- 14.24 Element Specification
- 14.25 EXCEPTION_INIT Pragma
- 14.26 Exception Declaration
- 14.27 Exception Handler
- 14.28 EXECUTE IMMEDIATE Statement
- 14.29 EXIT Statement
- 14.30 Explicit Cursor Declaration and Definition
- 14.31 Expression
- 14.32 FETCH Statement
- 14.33 FOR LOOP Statement
- 14.34 FORALL Statement
- 14.35 Formal Parameter Declaration
- 14.36 Function Declaration and Definition
- 14.37 GOTO Statement
- 14.38 IF Statement
- 14.39 Implicit Cursor Attribute
- 14.40 INLINE Pragma
- 14.41 Invoker’s Rights and Definer’s Rights Clause
- 14.42 INSERT Statement Extension
- 14.43 Iterator
- 14.44 Named Cursor Attribute
- 14.45 NULL Statement
- 14.46 OPEN Statement
- 14.47 OPEN FOR Statement
- 14.48 PARALLEL_ENABLE Clause
- 14.49 PIPE ROW Statement
- 14.50 PIPELINED Clause
- 14.51 Procedure Declaration and Definition
- 14.52 Qualified Expression
- 14.53 RAISE Statement
- 14.54 Record Variable Declaration
- 14.55 RESTRICT_REFERENCES Pragma
- 14.56 RETURN Statement
- 14.57 RETURNING INTO Clause
- 14.58 RESULT_CACHE Clause
- 14.59 %ROWTYPE Attribute
- 14.60 Scalar Variable Declaration
- 14.61 SELECT INTO Statement
- 14.62 SERIALLY_REUSABLE Pragma
- 14.63 SHARING Clause
- 14.64 SQL_MACRO Clause
- 14.65 SQLCODE Function
- 14.66 SQLERRM Function
- 14.67 SUPPRESSES_WARNING_6009 Pragma
- 14.68 %TYPE Attribute
- 14.69 UDF Pragma
- 14.70 UPDATE Statement Extensions
- 14.71 WHILE LOOP Statement
-
15
SQL Statements for Stored PL/SQL Units
- 15.1 ALTER FUNCTION Statement
- 15.2 ALTER LIBRARY Statement
- 15.3 ALTER PACKAGE Statement
- 15.4 ALTER PROCEDURE Statement
- 15.5 ALTER TRIGGER Statement
- 15.6 ALTER TYPE Statement
- 15.7 CREATE FUNCTION Statement
- 15.8 CREATE LIBRARY Statement
- 15.9 CREATE PACKAGE Statement
- 15.10 CREATE PACKAGE BODY Statement
- 15.11 CREATE PROCEDURE Statement
- 15.12 CREATE TRIGGER Statement
- 15.13 CREATE TYPE Statement
- 15.14 CREATE TYPE BODY Statement
- 15.15 DROP FUNCTION Statement
- 15.16 DROP LIBRARY Statement
- 15.17 DROP PACKAGE Statement
- 15.18 DROP PROCEDURE Statement
- 15.19 DROP TRIGGER Statement
- 15.20 DROP TYPE Statement
- 15.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