PL/SQL User's Guide and Reference
Release 8.0
A58236-01
Library
Product
Index
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
Portability
Higher Productivity
Integration with Oracle
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
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
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
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
SQL92 Conformance
Using DDL and Dynamic SQL
Efficiency versus Flexibility
Some Limitations
Overcoming the Limitations
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 Exceptions
Guarding Against Aliasing
Restrictions
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
Improving Performance
Dealing with Size Limitations
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
Restriction
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
Parameter Default Values
Parameter Aliasing
Overloading
Restrictions
How Calls Are Resolved
Recursion
Recursive Subprograms
Mutual Recursion
Recursion versus Iteration
8 Packages
What Is a Package?
Advantages of Packages
The Package Specification
Referencing Package Contents
The Package Body
Some Examples
Private versus Public Items
Overloading
Package STANDARD
Product-specific Packages
DBMS_STANDARD
DBMS_OUTPUT
DBMS_PIPE
UTL_FILE
UTL_HTTP
DBMS_SQL
DBMS_ALERT
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
Pragma RESTRICT_REFERENCES
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 and Methods
Passing Parameters to a Constructor
Calling Methods
Sharing Objects
Using Refs
Forward Type Definitions
Manipulating Objects
Selecting Objects
Inserting Objects
Updating Objects
Deleting Objects
10 External Procedures
What Is an External Procedure?
Creating an External Procedure
Registering an External Procedure
Understanding the EXTERNAL Clause
An Example
Calling an External Procedure
An Example
How PL/SQL Calls an External Procedure
Environment Variables
Passing Parameters to an External Procedure
Specifying Datatypes
Using the PARAMETERS Clause
Using the WITH CONTEXT Clause
Using Service Routines
OCIExtProcAllocCallMemory
OCIExtProcRaiseExcp
OCIExtProcRaiseExcpWithMsg
OCIExtProcGetEnv
Doing Callbacks
Restrictions on Callbacks
Debugging External Procedures
Using Package DEBUG_EXTPROC
Demo Program
Guidelines for External Procedures
Restrictions on External Procedures
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
EXIT Statement
Expressions
External Procedures
FETCH Statement
Functions
GOTO Statement
IF Statement
INSERT Statement
Literals
LOCK TABLE Statement
LOOP Statements
NULL Statement
Object Types
OPEN Statement
OPEN-FOR 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 New Features
External Procedures
Object Types
Collections
LOB Types
NLS Types
B Sample Programs
Running the Programs
Creating the Tables
Loading the Data
Sample 1. FOR Loop
Input Table
PL/SQL Block
Output Table
Sample 2. Cursors
Input Table
PL/SQL Block
Output Table
Sample 3. Scoping
Input Table
PL/SQL Block
Output Table
Sample 4. Batch Transaction Processing
Input Tables
PL/SQL Block
Output Tables
Sample 5. Embedded PL/SQL
Input Table
PL/SQL Block in a C Program
Interactive Session
Output Tables
Sample 6. Calling a Stored Procedure
Input Table
Stored Procedure
Interactive Session
C CHAR versus VARCHAR2 Semantics
Assigning Character Values
Comparing Character Values
Inserting Character Values
Selecting Character Values
D PL/SQL Wrapper
Advantages of Wrapping
Running the PL/SQL Wrapper
Input and Output Files
Error Detection
E Name Resolution
What Is Name Resolution?
Various Forms of References
Name-Resolution Algorithm
Finding the Basis
Understanding Capture
Inner Capture
Same-Scope Capture
Outer Capture
Avoiding Capture
Accessing Attributes and Methods
Calling Subprograms and Methods
Example 1
Example 2
SQL versus PL/SQL
F Reserved Words
Next
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Library
Product
Index
