PL/SQL User's Guide and Reference
Release 8.1.5

A67842-01

Library

Product

Index

Next

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Overview

Main Features
Block Structure
Variables and Constants
Cursors
Cursor FOR Loops
Cursor Variables
Attributes
Control Structures
Modularity
Data Abstraction
Information Hiding
Error Handling
Architecture
In the Oracle Server
In Oracle Tools
Advantages of PL/SQL
Support for SQL
Support for Object-Oriented Programming
Better Performance
Higher Productivity
Full Portability
Tight Integration with SQL
Security

2 Fundamentals

Character Set
Lexical Units
Delimiters
Identifiers
Literals
Comments
Datatypes
Number Types
Character Types
NLS Character Types
LOB Types
Other Types
User-Defined Subtypes
Defining Subtypes
Using Subtypes
Datatype Conversion
Explicit Conversion
Implicit Conversion
Implicit versus Explicit Conversion
DATE Values
RAW and LONG RAW Values
NLS Values
Declarations
Using DEFAULT
Using NOT NULL
Using %TYPE
Using %ROWTYPE
Restrictions
Naming Conventions
Synonyms
Scoping
Case Sensitivity
Name Resolution
Scope and Visibility
Assignments
Boolean Values
Database Values
Expressions and Comparisons
Operator Precedence
Logical Operators
Comparison Operators
Concatenation Operator
Boolean Expressions
Handling Nulls
Built-In Functions

3 Control Structures

Overview
Conditional Control: IF Statements
IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
Guidelines
Iterative Control: LOOP and EXIT Statements
LOOP
WHILE-LOOP
FOR-LOOP
Sequential Control: GOTO and NULL Statements
GOTO Statement
NULL Statement

4 Collections and Records

What Is a Collection?
Understanding Nested Tables
Nested Tables versus Index-by Tables
Understanding Varrays
Varrays versus Nested Tables
Defining and Declaring Collections
Declaring Collections
Initializing and Referencing Collections
Referencing Collection Elements
Assigning and Comparing Collections
Comparing Whole Collections
Manipulating Collections
Some Nested Table Examples
Some Varray Examples
Manipulating Individual Elements
Manipulating Local Collections
Using Collection Methods
Using EXISTS
Using COUNT
Using LIMIT
Using FIRST and LAST
Using PRIOR and NEXT
Using EXTEND
Using TRIM
Using DELETE
Applying Methods to Collection Parameters
Avoiding Collection Exceptions
Taking Advantage of Bulk Binds
How Do Bulk Binds Improve Performance?
Using the FORALL Statement
Using the BULK COLLECT Clause
Using FORALL and BULK COLLECT Together
Using Host Arrays
Using Cursor Attributes
What Is a Record?
Defining and Declaring Records
Declaring Records
Initializing and Referencing Records
Referencing Records
Assigning and Comparing Records
Comparing Records
Manipulating Records

5 Interaction with Oracle

SQL Support
Data Manipulation
Transaction Control
SQL Functions
SQL Pseudocolumns
SQL Operators
Managing Cursors
Explicit Cursors
Implicit Cursors
Packaging Cursors
Using Cursor FOR Loops
Using Subqueries
Using Aliases
Passing Parameters
Using Cursor Variables
What Are Cursor Variables?
Why Use Cursor Variables?
Defining REF CURSOR Types
Declaring Cursor Variables
Controlling Cursor Variables
Example 1
Example 2
Example 3
Example 4
Reducing Network Traffic
Avoiding Errors
Restrictions on Cursor Variables
Using Cursor Attributes
Explicit Cursor Attributes
Implicit Cursor Attributes
Processing Transactions
How Transactions Guard Your Database
Using COMMIT
Using ROLLBACK
Using SAVEPOINT
Implicit Rollbacks
Ending Transactions
Using SET TRANSACTION
Overriding Default Locking
Dealing with Size Limitations
Using Autonomous Transactions
Advantages of Autonomous Transactions
Defining Autonomous Transactions
Controlling Autonomous Transactions
Example 1: Using an Autonomous Trigger
Example 2: Calling an Autonomous Function from SQL
Improving Performance
Use Object Types and Collections
Use Bulk Binds
Use Native Dynamic SQL
Use External Routines
Use the NOCOPY Compiler Hint
Use the RETURNING Clause
Use Serially Reusable Packages
Use the PLS_INTEGER Datatype
Avoid the NOT NULL Constraint
Rephrase Conditional Control Statements
Avoid Implicit Datatype Conversions
Ensuring Backward Compatibility

6 Error Handling

Overview
Advantages of Exceptions
Predefined Exceptions
User-Defined Exceptions
Declaring Exceptions
Scope Rules
Using EXCEPTION_INIT
Using raise_application_error
Redeclaring Predefined Exceptions
How Exceptions Are Raised
Using the RAISE Statement
How Exceptions Propagate
Reraising an Exception
Handling Raised Exceptions
Exceptions Raised in Declarations
Exceptions Raised in Handlers
Branching to or from an Exception Handler
Using SQLCODE and SQLERRM
Unhandled Exceptions
Useful Techniques
Continuing after an Exception Is Raised
Retrying a Transaction
Using Locator Variables

7 Subprograms

What Are Subprograms?
Advantages of Subprograms
Procedures
Functions
Controlling Sides Effects
RETURN Statement
Declaring Subprograms
Forward Declarations
Stored Subprograms
Actual versus Formal Parameters
Positional and Named Notation
Positional Notation
Named Notation
Mixed Notation
Parameter Modes
IN Mode
OUT Mode
IN OUT Mode
Summary
NOCOPY Compiler Hint
The Trade-Off for Better Performance
Restrictions on NOCOPY
Parameter Default Values
Parameter Aliasing
Overloading
Restrictions
How Calls Are Resolved
Avoiding Call Resolution Errors
Calling External Routines
Invoker Rights versus Definer Rights
Advantages of Invoker Rights
Using the AUTHID Clause
How External References Are Resolved
Granting the EXECUTE Privilege
Using Views and Database Triggers
Using Database Links
Invoking Instance Methods
Recursion
Recursive Subprograms
Mutual Recursion
Recursion versus Iteration

8 Packages

What Is a Package?
Advantages of Packages
The Package Spec
Referencing Package Contents
The Package Body
Some Examples
Private versus Public Items
Overloading
Package STANDARD
Product-specific Packages
DBMS_STANDARD
DBMS_ALERT
DBMS_OUTPUT
DBMS_PIPE
UTL_FILE
UTL_HTTP
Guidelines

9 Object Types

The Role of Abstraction
What Is an Object Type?
Why Use Object Types?
Structure of an Object Type
Components of an Object Type
Attributes
Methods
Defining Object Types
Object Type Stack
Object Type Ticket_Booth
Object Type Bank_Account
Object Type Rational
Declaring and Initializing Objects
Declaring Objects
Initializing Objects
How PL/SQL Treats Uninitialized Objects
Accessing Attributes
Calling Constructors
Calling Methods
Sharing Objects
Using Refs
Forward Type Definitions
Manipulating Objects
Selecting Objects
Inserting Objects
Updating Objects
Deleting Objects

10 Native Dynamic SQL

What Is Dynamic SQL?
The Need for Dynamic SQL
Using the EXECUTE IMMEDIATE Statement
Some Examples
Using the OPEN-FOR, FETCH, and CLOSE Statements
Opening the Cursor Variable
Fetching from the Cursor Variable
Closing the Cursor Variable
Some Examples
Specifying Parameter Modes
Tips and Traps
Passing the Names of Schema Objects
Using Duplicate Placeholders
Using Cursor Attributes
Passing Nulls
Doing Remote Operations
Using Invoker Rights
Using Pragma RESTRICT_REFERENCES
Avoiding Deadlocks

11 Language Elements

Assignment Statement
Blocks
CLOSE Statement
Collection Methods
Collections
Comments
COMMIT Statement
Constants and Variables
Cursor Attributes
Cursor Variables
Cursors
DELETE Statement
EXCEPTION_INIT Pragma
Exceptions
EXECUTE IMMEDIATE Statement
EXIT Statement
Expressions
FETCH Statement
FORALL Statement
Functions
GOTO Statement
IF Statement
INSERT Statement
Literals
LOCK TABLE Statement
LOOP Statements
NULL Statement
Object Types
OPEN Statement
OPEN-FOR Statement
OPEN-FOR-USING Statement
Packages
Procedures
RAISE Statement
Records
RETURN Statement
ROLLBACK Statement
%ROWTYPE Attribute
SAVEPOINT Statement
SELECT INTO Statement
SET TRANSACTION Statement
SQL Cursor
SQLCODE Function
SQLERRM Function
%TYPE Attribute
UPDATE Statement

A Sample Programs

Running the Programs
Sample 1. FOR Loop
Sample 2. Cursors
Sample 3. Scoping
Sample 4. Batch Transaction Processing
Sample 5. Embedded PL/SQL
Sample 6. Calling a Stored Procedure

B CHAR versus VARCHAR2 Semantics

Assigning Character Values
Comparing Character Values
Inserting Character Values
Selecting Character Values

C PL/SQL Wrapper

Advantages of Wrapping
Running the PL/SQL Wrapper

D Name Resolution

Name-Resolution Algorithm
Understanding Capture
Avoiding Capture
Accessing Attributes and Methods
Calling Subprograms and Methods
SQL versus PL/SQL

E Reserved Words

Index



Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Index