PL/SQL User's Guide and Reference
Release 8.1.5
A67842-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Overview

2 Fundamentals

Character Set

Lexical Units

Character Types

NLS Character Types

User-Defined Subtypes

Defining Subtypes

Datatype Conversion

Explicit Conversion

Implicit Conversion

Implicit versus Explicit Conversion

RAW and LONG RAW Values

Declarations

Naming Conventions

Case Sensitivity

Name Resolution

Scope and Visibility

Assignments

Database Values

Expressions and Comparisons

Operator Precedence

Logical Operators

Comparison Operators

Concatenation Operator

Boolean Expressions

Built-In Functions

3 Control Structures

Conditional Control: IF Statements

Iterative Control: LOOP and EXIT Statements

Sequential Control: GOTO and NULL Statements

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 FIRST and LAST

Using PRIOR and NEXT

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 Pseudocolumns

Managing Cursors

Explicit Cursors

Implicit Cursors

Packaging Cursors

Using Cursor FOR Loops

Using Subqueries

Passing Parameters

Using Cursor Variables

What Are Cursor Variables?

Why Use Cursor Variables?

Defining REF CURSOR Types

Declaring Cursor Variables

Controlling Cursor Variables

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 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 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

7 Subprograms

What Are Subprograms?

Advantages of Subprograms

Controlling Sides Effects

RETURN Statement

Declaring Subprograms

Forward Declarations

Stored Subprograms

Actual versus Formal Parameters

Positional and Named Notation

Positional Notation

Parameter Modes

NOCOPY Compiler Hint

The Trade-Off for Better Performance

Restrictions on NOCOPY

Parameter Default Values

Parameter Aliasing

Overloading

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

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

9 Object Types

10 Native Dynamic SQL

What Is Dynamic SQL?

The Need for Dynamic SQL

Using the EXECUTE IMMEDIATE Statement

Using the OPEN-FOR, FETCH, and CLOSE Statements

Opening the Cursor Variable

Fetching from the Cursor Variable

Closing the Cursor Variable

Specifying Parameter Modes

Tips and Traps

Passing the Names of Schema Objects

Using Duplicate Placeholders

Using Cursor Attributes

Doing Remote Operations

Using Invoker Rights

Using Pragma RESTRICT_REFERENCES

Avoiding Deadlocks

11 Language Elements

Assignment Statement

CLOSE Statement

Collection Methods

Collections

COMMIT Statement

Constants and Variables

Cursor Attributes

Cursor Variables

DELETE Statement

EXCEPTION_INIT Pragma

EXECUTE IMMEDIATE Statement

EXIT Statement

Expressions

FETCH Statement

FORALL Statement

GOTO Statement

IF Statement

INSERT Statement

LOCK TABLE Statement

LOOP Statements

NULL Statement

Object Types

OPEN Statement

OPEN-FOR Statement

OPEN-FOR-USING Statement

RAISE Statement

RETURN Statement

ROLLBACK Statement

%ROWTYPE Attribute

SAVEPOINT Statement

SELECT INTO Statement

SET TRANSACTION Statement

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

Copyright © 1999 Oracle Corporation.
All Rights Reserved.