PL/SQL User's Guide and Reference

Audience

What's New in This Edition?

How This Guide Is Organized

Notational Conventions

Sample Database Tables

Sample Data

Your Comments Are Welcome

Main Features

Block Structure

Variables and Constants

Support for Object-Oriented Programming

Better Performance

Portability

Higher Productivity

Integration with Oracle

User-Defined Subtypes

Implicit versus Explicit Conversion

DATE Values

RAW and LONG RAW Values

Expressions and Comparisons

Operator Precedence

Logical Operators

Comparison Operators

Concatenation Operator

Conditional Control: IF Statements

Iterative Control: LOOP and EXIT Statements

LOOP

WHILE-LOOP

FOR-LOOP

Sequential Control: GOTO and NULL Statements

GOTO Statement

NULL Statement

What Is a Collection?

Understanding Nested Tables

Understanding Varrays

Differences Between Nested Tables and Varrays

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

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

Using DDL and Dynamic SQL

Efficiency versus Flexibility

Some Limitations

Overcoming the Limitations

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

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

Overriding Default Locking

Dealing with Size Limitations

Ensuring Backward Compatibility

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

What Are Subprograms?

Advantages of Subprograms

Declaring Subprograms

Forward Declarations

Stored Subprograms

Actual versus Formal Parameters

Positional and Named Notation

Parameter Default Values

Parameter Aliasing

Overloading

Restrictions

How Calls Are Resolved

Recursion

Recursive Subprograms

Mutual Recursion

Recursion versus Iteration

What Is a Package?

Advantages of Packages

The Package Specification

Referencing Package Contents

The Package Body

Some Examples

Private versus Public Items

Overloading

Serially Reusable Packages

Package STANDARD

Product-specific Packages

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

What Is an External Procedure?

Using DLLs

Registering an External Procedure

Understanding the EXTERNAL Clause

An Example

Calling an External Procedure

An Example

How PL/SQL Calls an External Procedure

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

Reading the Syntax Diagrams

Constants and Variables

EXCEPTION_INIT Pragma

SELECT INTO Statement

SET TRANSACTION Statement

Sample 4. Batch Transaction Processing

Sample 5. Embedded PL/SQL

Sample 6. Calling a Stored Procedure

Assigning Character Values

Comparing Character Values

Inserting Character Values

Selecting Character Values

Advantages of Wrapping

Running the PL/SQL Wrapper

What Is Name Resolution?

Various Forms of References

Name-Resolution Algorithm

Understanding Capture

Accessing Attributes and Methods

Avoiding Capture

Calling Subprograms and Methods

SQL versus PL/SQL

Contents

Title and Copyright Information

Preface

Chapter 1 Overview

Chapter 2 Fundamentals

Chapter 3 Control Structures

Chapter 4 Collections and Records

Chapter 5 Interaction with Oracle

Chapter 6 Error Handling

Chapter 7 Subprograms

Chapter 8 Packages

Chapter 9 Object Types

Chapter 10 External Procedures

Chapter 11 Language Elements

Appendix A New Features

Appendix B Sample Programs

Appendix C CHAR versus VARCHAR2 Semantics

Appendix D PL/SQL Wrapper

Appendix E Name Resolution

Appendix F Reserved Words