1/100
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Syntax Descriptions
Changes in This Release for Oracle Database PL/SQL Language Reference
Changes in Oracle Database 12
c
Release 1 (12.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
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
NOT NULL Constraint
Variable Declarations
Constant Declarations
Initial Values of Variables and Constants
%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
%ROWTYPE Attribute and Invisible 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
Querying a Collection
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 Routines
Controlling Autonomous Transactions
Entering and Exiting Autonomous Routines
Committing and Rolling Back Autonomous Transactions
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.RETURN_RESULT Procedure
DBMS_SQL.GET_NEXT_RESULT Procedure
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
Formal Parameters of Constrained Subtypes
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)
Granting Roles to PL/SQL Packages and Standalone Subprograms
IR Units Need Template Objects
External Subprograms
9
PL/SQL Triggers
Overview of Triggers
Reasons to Use Triggers
DML Triggers
Conditional Predicates for Detecting Triggering DML Statement
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
Correlation Names and Pseudorecords
OBJECT_VALUE Pseudocolumn
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
Handling Errors in Distributed Queries
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 Method Invocation
Collection Variable Declaration
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
UDF Pragma
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
Scripting on this page enhances content navigation, but does not change the content in any way.