PL/SQL User's Guide and Reference

694 Pages·2001·3.703 MB·English

by  Portfolio T., Russell J.

PL/SQL
User’s Guide and Reference
Release 9.0.1
June 2001
Part No. A89856-01
PL/SQL User’s Guide and Reference, Release 9.0.1
Part No. A89856-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Authors: Tom Portfolio, John Russell
Contributors: Shashaanka Agrawal, Cailein Barclay,Dmitri Bronnikov, Sharon Castledine, Thomas
Chang, Ravindra Dani, Chandrasekharan Iyer, Susan Kotsovolos, Neil Le, Warren Li, Chris Racicot,
Murali Vemulapati, Guhan Viswanathan, Minghui Yang
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle, Oracle Store, and SQL*Plus are registered trademarks, and Oracle Call Interface, Oracle
Developer, Oracle Forms, Oracle Reports, Oracle8i, Oracle9i, PL/SQL, Pro*C, and Pro*C/C++are
trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their
respective owners.
Contents
Send Us Your Comments................................................................................................................. xix
Preface.......................................................................................................................................................... xxi
What’s New in PL/SQL?................................................................................................................... xxxi
1 Overview of PL/SQL
Understanding the Main Features of PL/SQL............................................................................... 1-2
Block Structure.............................................................................................................................. 1-2
Variables and Constants.............................................................................................................. 1-3
Cursors........................................................................................................................................... 1-5
Cursor FOR Loops........................................................................................................................ 1-6
Cursor Variables........................................................................................................................... 1-6
Attributes....................................................................................................................................... 1-7
Control Structures........................................................................................................................ 1-9
Modularity................................................................................................................................... 1-12
Data Abstraction......................................................................................................................... 1-14
Information Hiding.................................................................................................................... 1-16
Error Handling............................................................................................................................ 1-16
PL/SQL Architecture........................................................................................................................ 1-17
In the Oracle Database Server................................................................................................... 1-18
In Oracle Tools............................................................................................................................ 1-20
Advantages of PL/SQL..................................................................................................................... 1-20
Support for SQL.......................................................................................................................... 1-20
iii
Support for Object-Oriented Programming............................................................................ 1-21
Better Performance..................................................................................................................... 1-21
Higher Productivity.................................................................................................................... 1-22
Full Portability............................................................................................................................. 1-23
Tight Integration with SQL....................................................................................................... 1-23
Tight Security.............................................................................................................................. 1-23
2 Fundamentals of PL/SQL
Character Set........................................................................................................................................ 2-2
Lexical Units......................................................................................................................................... 2-2
Delimiters....................................................................................................................................... 2-3
Identifiers....................................................................................................................................... 2-4
Literals............................................................................................................................................ 2-7
Comments.................................................................................................................................... 2-10
Declarations....................................................................................................................................... 2-11
Using DEFAULT......................................................................................................................... 2-12
Using NOT NULL....................................................................................................................... 2-13
Using %TYPE.............................................................................................................................. 2-13
Using %ROWTYPE..................................................................................................................... 2-14
Restrictions on Declarations...................................................................................................... 2-17
Naming Conventions....................................................................................................................... 2-17
Synonyms..................................................................................................................................... 2-18
Scoping......................................................................................................................................... 2-18
Case Sensitivity........................................................................................................................... 2-18
Name Resolution......................................................................................................................... 2-18
Scope and Visibility......................................................................................................................... 2-19
Assignments....................................................................................................................................... 2-22
Boolean Values............................................................................................................................ 2-22
Database Values.......................................................................................................................... 2-23
Expressions and Comparisons........................................................................................................ 2-23
Operator Precedence.................................................................................................................. 2-24
Logical Operators........................................................................................................................ 2-25
Comparison Operators............................................................................................................... 2-26
Concatenation Operator............................................................................................................. 2-28
Boolean Expressions................................................................................................................... 2-28
iv
CASE Expressions...................................................................................................................... 2-31
Handling Null Values in Comparisons and Conditional Statements................................. 2-33
Built-In Functions............................................................................................................................. 2-35
3 PL/SQL Datatypes
Predefined Datatypes......................................................................................................................... 3-2
Number Types.............................................................................................................................. 3-3
Character Types............................................................................................................................ 3-5
National Character Types.......................................................................................................... 3-10
LOB Types................................................................................................................................... 3-13
Boolean Type............................................................................................................................... 3-15
Datetime and Interval Types..................................................................................................... 3-15
Datetime and Interval Arithmetic............................................................................................ 3-20
Avoiding Truncation Problems Using Date and Time Subtypes........................................ 3-21
User-Defined Subtypes.................................................................................................................... 3-21
Defining Subtypes...................................................................................................................... 3-22
Using Subtypes........................................................................................................................... 3-22
Datatype Conversion........................................................................................................................ 3-24
Explicit Conversion.................................................................................................................... 3-24
Implicit Conversion.................................................................................................................... 3-24
Implicit versus Explicit Conversion......................................................................................... 3-26
DATE Values............................................................................................................................... 3-26
RAW and LONG RAW Values................................................................................................. 3-27
4 PL/SQL Control Structures
Overview of PL/SQL Control Structures........................................................................................ 4-2
Conditional Control: IF and CASE Statements............................................................................. 4-2
IF-THEN Statement...................................................................................................................... 4-3
IF-THEN-ELSE Statement........................................................................................................... 4-3
IF-THEN-ELSIF Statement.......................................................................................................... 4-4
CASE Statement............................................................................................................................ 4-5
Guidelines for PL/SQL Conditional Statements..................................................................... 4-8
Iterative Control: LOOP and EXIT Statements............................................................................. 4-9
LOOP.............................................................................................................................................. 4-9
WHILE-LOOP............................................................................................................................. 4-11
v
FOR-LOOP................................................................................................................................... 4-13
Sequential Control: GOTO and NULL Statements.................................................................... 4-17
GOTO Statement......................................................................................................................... 4-17
NULL Statement......................................................................................................................... 4-21
5 PL/SQL Collections and Records
What Is a Collection?.......................................................................................................................... 5-2
Understanding Nested Tables.................................................................................................... 5-2
Nested Tables Versus Index-By Tables...................................................................................... 5-3
Understanding Varrays............................................................................................................... 5-4
Varrays Versus Nested Tables.................................................................................................... 5-4
Defining and Declaring Collections................................................................................................ 5-5
Declaring Collections................................................................................................................... 5-7
Initializing and Referencing Collections....................................................................................... 5-8
Referencing Collection Elements.............................................................................................. 5-10
Assigning and Comparing Collections......................................................................................... 5-11
Comparing Whole Collections.................................................................................................. 5-13
Manipulating Collections................................................................................................................ 5-13
Some Nested Table Examples................................................................................................... 5-13
Some Varray Examples.............................................................................................................. 5-16
Manipulating Individual Collection Elements....................................................................... 5-18
Manipulating Local Collections................................................................................................ 5-20
Using Multilevel Collections.......................................................................................................... 5-21
Using Collection Methods............................................................................................................... 5-23
Using the EXISTS Collection Method...................................................................................... 5-24
Using the COUNT Collection Method.................................................................................... 5-24
Using the LIMIT Collection Method........................................................................................ 5-25
Using the FIRST and LAST Collection Methods.................................................................... 5-25
Using the PRIOR and NEXT Collection Methods.................................................................. 5-26
Using the EXTEND Collection Method................................................................................... 5-27
Using the TRIM Collection Method......................................................................................... 5-27
Using the DELETE Collection Method.................................................................................... 5-28
Applying Methods to Collection Parameters......................................................................... 5-29
Avoiding Collection Exceptions..................................................................................................... 5-30
Reducing Loop Overhead for Collections with Bulk Binds..................................................... 5-32
vi
How Do Bulk Binds Improve Performance?.......................................................................... 5-33
Using the FORALL Statement........................................................................................................ 5-34
How FORALL Affects Rollbacks.............................................................................................. 5-35
Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute...
5-36
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute..................... 5-38
Restrictions on FORALL............................................................................................................ 5-39
Retrieving Query Results into Collections with the BULK COLLECT Clause.................... 5-41
Example of a Bulk Fetch from a Cursor.................................................................................. 5-42
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause.......................... 5-43
Retrieving DML Results into a Collection with the RETURNING INTO Clause............. 5-43
Restrictions on BULK COLLECT............................................................................................. 5-44
Using FORALL and BULK COLLECT Together................................................................... 5-45
Using Host Arrays with Bulk Binds........................................................................................ 5-45
What Is a Record?.............................................................................................................................. 5-46
Defining and Declaring Records.................................................................................................... 5-46
Declaring Records....................................................................................................................... 5-48
Initializing Records.................................................................................................................... 5-48
Referencing Records................................................................................................................... 5-49
Nulling Records.......................................................................................................................... 5-51
Assigning Records...................................................................................................................... 5-51
Comparing Records.................................................................................................................... 5-53
Manipulating Records..................................................................................................................... 5-53
6 Interaction Between PL/SQL and Oracle
Overview of SQL Support in PL/SQL............................................................................................ 6-2
Data Manipulation....................................................................................................................... 6-2
Transaction Control...................................................................................................................... 6-2
SQL Functions............................................................................................................................... 6-2
SQL Pseudocolumns.................................................................................................................... 6-3
SQL Operators............................................................................................................................... 6-5
Managing Cursors.............................................................................................................................. 6-6
Overview of Explicit Cursors...................................................................................................... 6-6
Overview of Implicit Cursors................................................................................................... 6-11
Separating Cursor Specs and Bodies with Packages................................................................. 6-12
vii
Using Cursor FOR Loops................................................................................................................. 6-13
Using Subqueries Instead of Explicit Cursors........................................................................ 6-14
Using Cursor Subqueries........................................................................................................... 6-14
Defining Aliases for Expression Values in a Cursor FOR Loop.......................................... 6-14
Passing Parameters to a Cursor FOR Loop............................................................................. 6-15
Using Cursor Variables.................................................................................................................... 6-16
What Are Cursor Variables?..................................................................................................... 6-16
Why Use Cursor Variables?...................................................................................................... 6-16
Defining REF CURSOR Types.................................................................................................. 6-17
Declaring Cursor Variables....................................................................................................... 6-18
Controlling Cursor Variables.................................................................................................... 6-19
Cursor Variable Example: Master Table and Details Tables................................................ 6-25
Cursor Variable Example: Client-Side PL/SQL Block.......................................................... 6-26
Cursor Variable Example: Pro*C Program............................................................................. 6-26
Cursor Variable Example: Manipulating Host Variables in SQL*Plus............................... 6-29
Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL.................. 6-30
Avoiding Errors with Cursor Variables.................................................................................. 6-31
Restrictions on Cursor Variables.............................................................................................. 6-33
Using Cursor Attributes................................................................................................................... 6-34
Overview of Explicit Cursor Attributes.................................................................................. 6-34
Overview of Implicit Cursor Attributes.................................................................................. 6-38
Using Cursor Expressions............................................................................................................... 6-41
Restrictions on Cursor Expressions.......................................................................................... 6-41
Example of Cursor Expressions................................................................................................ 6-42
Overview of Transaction Processing in PL/SQL......................................................................... 6-43
How Transactions Guard Your Database............................................................................... 6-44
Making Changes Permanent with COMMIT......................................................................... 6-44
Undoing Changes with ROLLBACK....................................................................................... 6-45
Undoing Partial Changes with SAVEPOINT......................................................................... 6-46
How Oracle Does Implicit Rollbacks....................................................................................... 6-47
Ending Transactions................................................................................................................... 6-48
Setting Transaction Properties with SET TRANSACTION.................................................. 6-48
Overriding Default Locking...................................................................................................... 6-49
Doing Independent Units of Work with Autonomous Transactions...................................... 6-53
Advantages of Autonomous Transactions.............................................................................. 6-53
viii
Defining Autonomous Transactions........................................................................................ 6-54
Controlling Autonomous Transactions................................................................................... 6-57
Using Autonomous Triggers.................................................................................................... 6-59
Calling Autonomous Functions from SQL............................................................................. 6-61
Ensuring Backward Compatibility of PL/SQL Programs......................................................... 6-62
7 Handling PL/SQL Errors
Overview of PL/SQL Error Handling............................................................................................. 7-2
Advantages of PL/SQL Exceptions.................................................................................................. 7-3
Predefined PL/SQL Exceptions........................................................................................................ 7-4
Defining Your Own PL/SQL Exceptions........................................................................................ 7-7
Declaring PL/SQL Exceptions.................................................................................................... 7-7
Scope Rules for PL/SQL Exceptions.......................................................................................... 7-7
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT................ 7-8
Defining Your Own PL/SQL Error Messages: Procedure RAISE_APPLICATION_ERROR....
7-9
Redeclaring Predefined Exceptions......................................................................................... 7-10
How PL/SQL Exceptions Are Raised............................................................................................ 7-11
Raising Exceptions with the RAISE Statement....................................................................... 7-11
How PL/SQL Exceptions Propagate.............................................................................................. 7-12
Reraising a PL/SQL Exception....................................................................................................... 7-14
Handling Raised PL/SQL Exceptions........................................................................................... 7-15
Handling Exceptions Raised in Declarations......................................................................... 7-16
Handling Exceptions Raised in Handlers............................................................................... 7-17
Branching to or from an Exception Handler.......................................................................... 7-17
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM...................... 7-18
Catching Unhandled Exceptions.............................................................................................. 7-19
Tips for Handling PL/SQL Errors.................................................................................................. 7-20
Continuing after an Exception Is Raised................................................................................. 7-20
Retrying a Transaction............................................................................................................... 7-21
Using Locator Variables to Identify Exception Locations.................................................... 7-22
8 PL/SQL Subprograms
What Are Subprograms?................................................................................................................... 8-2
Advantages of Subprograms............................................................................................................ 8-3
ix
Understanding PL/SQL Procedures................................................................................................ 8-3
Understanding PL/SQL Functions................................................................................................... 8-6
Using the RETURN Statement.................................................................................................... 8-8
Controlling Side Effects of PL/SQL Subprograms.................................................................. 8-9
Declaring PL/SQL Subprograms.................................................................................................... 8-10
Packaging PL/SQL Subprograms Together.................................................................................. 8-11
Actual Versus Formal Subprogram Parameters.......................................................................... 8-12
Positional Versus Named Notation for Subprogram Parameters............................................ 8-13
Using Positional Notation......................................................................................................... 8-13
Using Named Notation.............................................................................................................. 8-13
Using Mixed Notation................................................................................................................ 8-13
Specifying Subprogram Parameter Modes.................................................................................. 8-14
Using the IN Mode..................................................................................................................... 8-14
Using the OUT Mode................................................................................................................. 8-14
Using the IN OUT Mode............................................................................................................ 8-16
Summary of Subprogram Parameter Modes.......................................................................... 8-16
Passing Large Data Structures with the NOCOPY Compiler Hint......................................... 8-17
The Trade-Off for Better Performance with NOCOPY......................................................... 8-18
Restrictions on NOCOPY.......................................................................................................... 8-19
Using Default Values for Subprogram Parameters.................................................................... 8-19
Understanding Subprogram Parameter Aliasing....................................................................... 8-21
Overloading Subprogram Names.................................................................................................. 8-23
Restrictions on Overloading...................................................................................................... 8-24
How Subprogram Calls Are Resolved.......................................................................................... 8-26
How Overloading Works with Inheritance............................................................................ 8-28
Accepting and Returning Multiple Rows with Table Functions........................................... 8-30
Overview of Table Functions.................................................................................................... 8-30
What Are Table Functions?....................................................................................................... 8-31
What Are Pipelined Table Functions?..................................................................................... 8-32
Using Pipelined Table Functions for Transformations......................................................... 8-33
Writing a Pipelined Table Function......................................................................................... 8-34
Returning Results from Table Functions................................................................................. 8-35
Pipelining Data Between PL/SQL Table Functions.............................................................. 8-36
Querying Table Functions......................................................................................................... 8-36
Optimizing Multiple Calls to Table Functions....................................................................... 8-37
x