plsql users guide and reference

PL/SQL
User's Guide and Reference
Release 2 (9.2)
March 2002 Part No. A96624-01
PL/SQL User's Guide and Reference, Release 2 (9.2) Part No. A96624-01 Copyright 1996, 2002 Oracle Corporation. All rights reserved. Primary Author: John Russell Contributing Author: Tom Portfolio 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, except to the extent required to obtain interoperability with other independently created software or as specied by law, is prohibited. The information contained in this document is subject to change without notice. If you nd any problems 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 is a registered trademark, and Oracle Store, Oracle9i, PL/SQL, Pro*C, and SQL*Plus 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/SQLUnderstanding the Main Features of PL/SQL............................................................................... Block Structure .............................................................................................................................. Variables and Constants .............................................................................................................. Cursors ........................................................................................................................................... Cursor FOR Loops........................................................................................................................ Cursor Variables ........................................................................................................................... Attributes ....................................................................................................................................... Control Structures ........................................................................................................................ Modularity................................................................................................................................... Data Abstraction ......................................................................................................................... Information Hiding .................................................................................................................... Error Handling............................................................................................................................ PL/SQL Architecture ........................................................................................................................ In the Oracle Database Server................................................................................................... In Oracle Tools ............................................................................................................................ Advantages of PL/SQL..................................................................................................................... Support for SQL .......................................................................................................................... 1-2 1-2 1-3 1-5 1-6 1-6 1-7 1-9 1-12 1-14 1-16 1-16 1-17 1-18 1-20 1-20 1-20
iii
Support for Object-Oriented Programming...................................................

......................... Better Performance ..................................................................................................................... Higher Productivity.................................................................................................................... Full Portability............................................................................................................................. Tight Integration with SQL ....................................................................................................... Tight Security ..............................................................................................................................
1-21 1-21 1-22 1-23 1-23 1-23
2
Fundamentals of PL/SQLCharacter Set ........................................................................................................................................ Lexical Units......................................................................................................................................... Delimiters....................................................................................................................................... Identifiers ....................................................................................................................................... Literals ............................................................................................................................................ Comments .................................................................................................................................... Declarations ....................................................................................................................................... Using DEFAULT......................................................................................................................... Using NOT NULL....................................................................................................................... Using %TYPE .............................................................................................................................. Using %ROWTYPE..................................................................................................................... Restrictions on Declarations...................................................................................................... PL/SQL Naming Conventions ........................................................................................................ Scope and Visibility of PL/SQL Identiers ................................................................................. Variable Assignment ........................................................................................................................ Assigning Boolean Values ....................................................................................................

..... Assigning a SQL Query Result to a PL/SQL Variable.......................................................... PL/SQL Expressions and Comparisons ........................................................................................ Logical Operators........................................................................................................................ Boolean Expressions ................................................................................................................... CASE Expressions....................................................................................................................... Handling Null Values in Comparisons and Conditional Statements................................. Built-In Functions ............................................................................................................................. 2-2 2-2 2-3 2-4 2-7 2-10 2-11 2-12 2-13 2-13 2-14 2-17 2-17 2-19 2-22 2-22 2-23 2-23 2-25 2-28 2-31 2-33 2-35
3
PL/SQL DatatypesPredened Datatypes ......................................................................................................................... 3-2 Number Types............................................................................................................................... 3-3
iv
Character Types ............................................................................................................................ National Character Types.......................................................................................................... LOB Types ................................................................................................................................... Boolean Type............................................................................................................................... Datetime and Interval Types..................................................................................................... Datetime and Interval Arithmetic ............................................................................................ Avoiding Truncation Problems Using Date and Time Subtypes ........................................ User-Dened Subtypes.................................................................................................................... Defining Subtypes ...................................................................................................................... Using Subtypes ........................................................................................................................... Datatype Conversion........................................................................................................................ Explicit Conversion .................................................................................................................... Implicit Conversion.....................................................................

............................................... Implicit versus Explicit Conversion......................................................................................... DATE Values ............................................................................................................................... RAW and LONG RAW Values.................................................................................................
3-5 3-10 3-13 3-15 3-15 3-20 3-21 3-21 3-22 3-22 3-24 3-24 3-24 3-26 3-26 3-27
4
PL/SQL Control StructuresOverview of PL/SQL Control Structures........................................................................................ Conditional Control: IF and CASE Statements............................................................................. IF-THEN Statement ...................................................................................................................... IF-THEN-ELSE Statement ........................................................................................................... IF-THEN-ELSIF Statement .......................................................................................................... CASE Statement ............................................................................................................................ Guidelines for PL/SQL Conditional Statements ..................................................................... Iterative Control: LOOP and EXIT Statements ............................................................................. LOOP .............................................................................................................................................. WHILE-LOOP ............................................................................................................................. FOR-LOOP................................................................................................................................... Sequential Control: GOTO and NULL Statements ................................................................... GOTO Statement......................................................................................................................... NULL Statement ......................................................................................................................... 4-2 4-2 4-3 4-3 4-4 4-5 4-8 4-9 4-9 4-11 4-13 4-17 4-17 4-21
5
PL/SQL Collections and RecordsWhat Is a Collection?.......................................................................................................................... 5-2
v
Understanding Nested Tables .................................................................................................... 5-2 Understanding Varrays ............................................................................................................... 5-3 Understanding Associative Arrays (Index-By Tables)....................................................

........ 5-4 How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays................... 5-5 Choosing Which PL/SQL Collection Types to Use....................................................................... 5-6 Choosing Between Nested Tables and Associative Arrays .................................................... 5-6 Choosing Between Nested Tables and Varrays ....................................................................... 5-6 Dening Collection Types................................................................................................................. 5-7 Defining SQL Types Equivalent to PL/SQL Collection Types .............................................. 5-9 Declaring PL/SQL Collection Variables ....................................................................................... 5-10 Initializing and Referencing Collections ..................................................................................... 5-12 Referencing Collection Elements.............................................................................................. 5-14 Assigning Collections ...................................................................................................................... 5-15 Comparing Collections .................................................................................................................... 5-17 Using PL/SQL Collections with SQL Statements....................................................................... 5-18 Some Varray Examples .............................................................................................................. 5-20 Manipulating Inpidual Collection Elements with SQL ..................................................... 5-22 Using Multilevel Collections.......................................................................................................... 5-26 Using Collection Methods............................................................................................................... 5-28 Checking If a Collection Element Exists (EXISTS Method) .................................................. 5-29 Counting the Elements in a Collection (COUNT Method)................................................... 5-29 Checking the Maximum Size of a Collection (LIMIT Method)............................................ 5-30 Finding the First or Last Collection Element (FIRST and LAST Methods) ........................ 5-30 Looping Through Collection Elements (PRIOR and NEXT Methods) ............................... 5-31 Increasing the Size of a Collection (EXTEND Method)......................................................... 5-32 Decreasing the Size of a Collection (TRIM Method) ............................................................. 5-33 Deleting Collection Elements (DELETE Method) .................................................................. 5-34 Applying Methods to Collection Parameters ...........................................

.............................. 5-35 Avoiding Collection Exceptions..................................................................................................... 5-35 Reducing Loop Overhead for Collections with Bulk Binds..................................................... 5-37 How Do Bulk Binds Improve Performance? .......................................................................... 5-38 Using the FORALL Statement ........................................................................................................ 5-41 How FORALL Affects Rollbacks.............................................................................................. 5-42 Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute ... 5-43 Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute...................... 5-44
vi
Retrieving Query Results into Collections with the BULK COLLECT Clause.................... Examples of Bulk Fetching from a Cursor .............................................................................. Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause.......................... Retrieving DML Results into a Collection with the RETURNING INTO Clause ............. Restrictions on BULK COLLECT ............................................................................................. Using FORALL and BULK COLLECT Together ................................................................... Using Host Arrays with Bulk Binds ........................................................................................ What Is a Record?.............................................................................................................................. Dening and Declaring Records.................................................................................................... Declaring Records....................................................................................................................... Initializing Records .................................................................................................................... Referencing Records................................................................................................................... Assigning Null Values to Records ........................................................................................... Assigning Records...................................................................................................................... Comparing Records.................................................................................................................... Manipulating Records ..................................................................................................................... Inserting PL/SQL Records into the Database ........................................................................ Updating the Database with PL/SQL Record Values.........................

.................................. Restrictions on Record Inserts/Updates ................................................................................. Querying Data into Collections of Records ............................................................................
5-46 5-47 5-48 5-49 5-49 5-50 5-50 5-51 5-51 5-53 5-53 5-54 5-56 5-56 5-58 5-58 5-61 5-61 5-64 5-64
6
Interaction Between PL/SQL and OracleOverview of SQL Support in PL/SQL ............................................................................................ Data Manipulation ....................................................................................................................... Transaction Control...................................................................................................................... SQL Functions ............................................................................................................................... SQL Pseudocolumns .................................................................................................................... SQL Operators............................................................................................................................... Managing Cursors .............................................................................................................................. Overview of Explicit Cursors...................................................................................................... Overview of Implicit Cursors ................................................................................................... Separating Cursor Specs and Bodies with Packages ................................................................. Using Cursor FOR Loops ................................................................................................................ Using Subqueries Instead of Explicit Cursors ........................................................................ Using Cursor Subqueries........................................................................................................... 6-2 6-2 6-2 6-2 6-3 6-5 6-6 6-6 6-11 6-12 6-13 6-14 6-14
vii
Defining Aliases for Expression Values in a Cursor FOR Loop .......................................... Passing Parameters to a Cursor FOR Loop............................................................................. Using Cursor Variables .................................................................................................................... What Are Cursor Variables? ..................................................................................................... Why Use Cursor Variables? ...................................................................................................... Defining REF CURSOR Types .................................................................................................. Declaring Cursor Variables .......................

................................................................................ Controlling Cursor Variables .................................................................................................... Cursor Variable Example: Master Table and Details Tables ................................................ Cursor Variable Example: Client-Side PL/SQL Block .......................................................... Cursor Variable Example: Pro*C Program ............................................................................. Cursor Variable Example: Manipulating Host Variables in SQL*Plus............................... Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL.................. Avoiding Errors with Cursor Variables .................................................................................. Restrictions on Cursor Variables .............................................................................................. Using Cursor Attributes................................................................................................................... Overview of Explicit Cursor Attributes .................................................................................. Overview of Implicit Cursor Attributes .................................................................................. Using Cursor Expressions ............................................................................................................... Restrictions on Cursor Expressions.......................................................................................... Example of Cursor Expressions................................................................................................ Overview of Transaction Processing in PL/SQL ......................................................................... How Transactions Guard Your Database ............................................................................... Making Changes Permanent with COMMIT ......................................................................... Undoing Changes with ROLLBACK ....................................................................................... Undoing Partial Changes with SAVEPOINT ......................................................................... How Oracle Does Implicit Rollbacks ....................................................................................... Ending Transactions................................................................................................................... Setting Transaction Properties with SET TRANSACTION .................................................. Overriding Default Locking ...................................................................................................... Doing Independent Units of Work with Autonomous Transactions...................................... Advantages of Autonomous Transactions................................................

.............................. Defining Autonomous Transactions........................................................................................ Controlling Autonomous Transactions ................................................................................... Using Autonomous Triggers.....................................................................................................
6-14 6-15 6-16 6-16 6-16 6-17 6-18 6-19 6-25 6-26 6-26 6-29 6-30 6-31 6-33 6-34 6-34 6-38 6-41 6-41 6-42 6-43 6-44 6-44 6-45 6-46 6-47 6-48 6-48 6-49 6-53 6-53 6-54 6-57 6-59
viii
Calling Autonomous Functions from SQL ............................................................................. 6-61 Ensuring Backward Compatibility of PL/SQL Programs ......................................................... 6-62
7
Handling PL/SQL ErrorsOverview of PL/SQL Error Handling ............................................................................................. Advantages of PL/SQL Exceptions.................................................................................................. Predened PL/SQL Exceptions ........................................................................................................ Dening Your Own PL/SQL Exceptions ........................................................................................ Declaring PL/SQL Exceptions.................................................................................................... Scope Rules for PL/SQL Exceptions.......................................................................................... Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT ................ Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR ........... Redeclaring Predefined Exceptions ......................................................................................... How PL/SQL Exceptions Are Raised ............................................................................................ Raising Exceptions with the RAISE Statement....................................................................... How PL/SQL Exceptions Propagate .............................................................................................. Reraising a PL/SQL Exception ....................................................................................................... Handling Raised PL/SQL Exceptions ........................................................................................... Handling Exceptions Raised in Declarations ......................................................................... Handling Exceptions Raised in Handlers ............................................................................... Branching to or from an Exception Handler .......................................................................... Retrieving the Error Code and Error Message: SQLCODE and SQLERRM...................... Catching Unhandled Exceptions.....

......................................................................................... Tips for Handling PL/SQL Errors .................................................................................................. Continuing after an Exception Is Raised................................................................................. Retrying a Transaction............................................................................................................... Using Locator Variables to Identify Exception Locations .................................................... 7-2 7-3 7-4 7-7 7-7 7-7 7-8 7-9 7-10 7-10 7-11 7-11 7-14 7-15 7-16 7-17 7-17 7-18 7-19 7-20 7-20 7-21 7-22
8
PL/SQL SubprogramsWhat Are Subprograms? ................................................................................................................... Advantages of Subprograms ............................................................................................................ Understanding PL/SQL Procedures ................................................................................................ Understanding PL/SQL Functions .................................................................................................. Using the RETURN Statement.................................................................................................... Controlling Side Effects of PL/SQL Subprograms.................................................................. 8-2 8-3 8-3 8-6 8-8 8-9
ix
Declaring PL/SQL Subprograms.................................................................................................... Packaging PL/SQL Subprograms Together.................................................................................. Actual Versus Formal Subprogram Parameters .......................................................................... Positional Versus Named Notation for Subprogram Parameters............................................ Using Positional Notation ......................................................................................................... Using Named Notation.............................................................................................................. Using Mixed Notation................................................................................................................ Specifying Subprogram Parameter Modes .................................................................................. Using the IN Mode ..................................................................................................................... Using the OUT Mode ................................................................................................................. Using the IN OUT Mode............................................................................................................ Summary of Subprogram Parameter Modes ...........................................................

............... Passing Large Data Structures with the NOCOPY Compiler Hint ......................................... The Trade-Off for Better Performance with NOCOPY ......................................................... Restrictions on NOCOPY .......................................................................................................... Using Default Values for Subprogram Parameters .................................................................... Understanding Subprogram Parameter Aliasing ....................................................................... Overloading Subprogram Names.................................................................................................. Restrictions on Overloading...................................................................................................... How Subprogram Calls Are Resolved.......................................................................................... How Overloading Works with Inheritance ............................................................................ Accepting and Returning Multiple Rows with Table Functions ........................................... Overview of Table Functions .................................................................................................... What Are Table Functions? ....................................................................................................... What Are Pipelined Table Functions? ..................................................................................... Using Pipelined Table Functions for Transformations ......................................................... Writing a Pipelined Table Function ......................................................................................... Returning Results from Table Functions................................................................................. Pipelining Data Between PL/SQL Table Functions .............................................................. Querying Table Functions ......................................................................................................... Optimizing Multiple Calls to Table Functions ....................................................................... Passing Data with Cursor Variables ........................................................................................ Performing DML Operations Inside Table Functions ........................................................... Performing DML Operations on Table Functions ................................................................. Handling Exceptions in Table Functions ................................................................................
8-10 8-11 8-12 8-13 8-13 8-13 8-13 8-14 8-14 8-14 8-16 8-16 8-17 8-18 8-19 8-19 8-21 8-23 8-24 8-26 8-28 8-30 8-30 8-31 8-33 8-34 8-36 8-36 8-37 8-37 8-38 8-39 8-42 8-42 8-43
x
Parallelizing Table Functions ............................................

............................................................. Parallel Execution of Table Functions ..................................................................................... Input Data Partitioning.............................................................................................................. Parallel Execution of Leaf-level Table Functions ................................................................... How Table Functions Stream their Input Data ........................................................................... Choosing Between Partitioning and Clustering for Parallel Execution ............................. Invoker Rights Versus Dener Rights.......................................................................................... Advantages of Invoker Rights .................................................................................................. Specifying the Privileges for a Subprogram with the AUTHID Clause ............................. Who Is the Current User During Subprogram Execution? .................................................. How External References Are Resolved in Invoker-Rights Subprograms......................... Overriding Default Name Resolution in Invoker-Rights Subprograms ............................ Granting Privileges on Invoker-Rights Subprograms........................................................... Using Roles with Invoker-Rights Subprograms .................................................................... Using Views and Database Triggers with Invoker-Rights Subprograms .......................... Using Database Links with Invoker-Rights Subprograms................................................... Using Object Types with Invoker-Rights Subprograms ....................................................... Understanding and Using Recursion............................................................................................ What Is a Recursive Subprogram? ........................................................................................... Using Mutual Recursion............................................................................................................ Recursion Versus Iteration ........................................................................................................ Calling External Subprograms ....................................................................................................... Creating Dynamic Web Pages with PL/SQL Server Pages .......................................................
8-43 8-43 8-44 8-46 8-47 8-48 8-49 8-50 8-52 8-53 8-53 8-55 8-55 8-57 8-57 8-58 8-58 8-60 8-60 8-63 8-64 8-65 8-66
9
PL/SQL PackagesWhat Is a PL/SQL Package? .............................................................................................................. Example of a PL/SQL Package................................................................................................... Advantages of PL/SQL Packa

ges ..................................................................................................... Understanding The Package Spec ................................................................................................... Referencing Package Contents ................................................................................................... Understanding The Package Body .................................................................................................. Some Examples of Package Features............................................................................................... Private Versus Public Items in Packages...................................................................................... Overloading Packaged Subprograms ........................................................................................... How Package STANDARD Denes the PL/SQL Environment............................................... 9-2 9-4 9-5 9-6 9-7 9-8 9-9 9-14 9-15 9-15
xi
Overview of Product-Specic Packages ....................................................................................... About the DBMS_ALERT Package .......................................................................................... About the DBMS_OUTPUT Package....................................................................................... About the DBMS_PIPE Package ............................................................................................... About the UTL_FILE Package................................................................................................... About the UTL_HTTP Package ................................................................................................ Guidelines for Writing Packages ...................................................................................................
9-16 9-16 9-16 9-17 9-17 9-18 9-18
10
PL/SQL Object TypesThe Role of Abstraction................................................................................................................... 10-2 What Is an Object Type? .................................................................................................................. 10-3 Why Use Object Types? ................................................................................................................... 10-5 Structure of an Object Type ............................................................................................................ 10-5 Components of an Object Type...................................................................................................... 10-7 Attributes ..................................................................................................................................... 10-7 Methods........................................................................................................................................ 10-7 Changing Attributes and Methods of an Exis

ting Object Type (Type Evolution).......... 10-12 Dening Object Types ................................................................................................................... 10-13 Overview of PL/SQL Type Inheritance ................................................................................ 10-14 Object Type Example: Stack .................................................................................................... 10-16 Object Type Example: Ticket_Booth ...................................................................................... 10-18 Object Type Example: Bank_Account.................................................................................... 10-20 Object Type Example: Rational Numbers ............................................................................. 10-22 Declaring and Initializing Objects .............................................................................................. 10-24 Declaring Objects ...................................................................................................................... 10-25 Initializing Objects .................................................................................................................... 10-25 How PL/SQL Treats Uninitialized Objects .......................................................................... 10-26 Accessing Attributes....................................................................................................................... 10-27 Dening Constructors .................................................................................................................... 10-28 Calling Constructors....................................................................................................................... 10-29 Calling Methods.............................................................................................................................. 10-30 Sharing Objects through the REF Modier ............................................................................... 10-31 Forward Type Definitions ....................................................................................................... 10-32 Manipulating Objects .................................................................................................................... 10-33 Selecting Objects ....................................................................................................................... 10-34
xii
Inserting Objects ....................................................................................................................... 10-39 Updating Objects ...................................................................................................................... 10-40 Deleting Objects ........................................................................................................................ 10-40
11
Native Dynamic SQLWhat Is Dynamic SQL?......................

.............................................................................................. The Need for Dynamic SQL ........................................................................................................... Using the EXECUTE IMMEDIATE Statement............................................................................ Some Examples of Dynamic SQL............................................................................................. Backward Compatibility of the USING Clause...................................................................... Specifying Parameter Modes .................................................................................................... Using the OPEN-FOR, FETCH, and CLOSE Statements .......................................................... Opening the Cursor Variable.................................................................................................... Fetching from the Cursor Variable .......................................................................................... Closing the Cursor Variable...................................................................................................... Examples of Dynamic SQL for Records, Objects, and Collections...................................... Using Bulk Dynamic SQL............................................................................................................. Syntax for Dynamic Bulk Binds.............................................................................................. Examples of Dynamic Bulk Binds .......................................................................................... Tips and Traps for Dynamic SQL ................................................................................................ Improving Performance........................................................................................................... Making Procedures Work on Arbitrarily Named Schema Objects ................................... Using Duplicate Placeholders................................................................................................. Using Cursor Attributes .......................................................................................................... Passing Nulls............................................................................................................................. Doing Remote Operations....................................................................................................... Using Invoker Rights ............................................................................................................... Using Pragma RESTRICT_REFERENCES ............................................................................ Avoiding Deadlocks................................................................................................................. 11-2 11-2 11-3 11-4 11-5 11-6 11-7 11-7 11-8 11-8 11-9 11-11 11-11 11-12 11-1

4 11-14 11-14 11-15 11-16 11-16 11-17 11-17 11-18 11-18
12
Tuning PL/SQL ApplicationsReasons for PL/SQL Performance Problems ............................................................................... Identifying PL/SQL Performance Problems................................................................................ The Profiler API: Package DBMS_PROFILER........................................................................ The Trace API: Package DBMS_TRACE ................................................................................. 12-2 12-8 12-8 12-9
xiii
PL/SQL Features for Performance Tuning ................................................................................. Tuning PL/SQL Performance with Native Dynamic SQL ................................................. Tuning PL/SQL Performance with Bulk Binds ................................................................... Tuning PL/SQL Performance with the NOCOPY Compiler Hint.................................... Tuning PL/SQL Performance with the RETURNING Clause........................................... Tuning PL/SQL Performance with External Routines ....................................................... Improving PL/SQL Performance with Object Types and Collections ............................. Compiling PL/SQL Code for Native Execution ..................................................................
12-10 12-10 12-11 12-12 12-12 12-13 12-13 12-14
13
PL/SQL Language ElementsAssignment Statement ..................................................................................................................... 13-4 AUTONOMOUS_TRANSACTION Pragma............................................................................... 13-8 Blocks ................................................................................................................................................ 13-11 CASE Statement .............................................................................................................................. 13-19 CLOSE Statement............................................................................................................................ 13-22 Collection Methods ........................................................................................................................ 13-24 Collections........................................................................................................................................ 13-29 Comments......................................................................................................................................... 13-36 COMMIT Statement....................................................................................................................... 13-37 Constants and Variables ................................................................................................................ 13-39 Cursor Attributes ..............................................

.............................................................................. 13-43 Cursor Variables .............................................................................................................................. 13-48 Cursors .............................................................................................................................................. 13-54 DELETE Statement ......................................................................................................................... 13-58 EXCEPTION_INIT Pragma........................................................................................................... 13-62 Exceptions......................................................................................................................................... 13-64 EXECUTE IMMEDIATE Statement............................................................................................. 13-67 EXIT Statement................................................................................................................................ 13-71 Expressions....................................................................................................................................... 13-73 FETCH Statement............................................................................................................................ 13-83 FORALL Statement......................................................................................................................... 13-88 Functions........................................................................................................................................... 13-92 GOTO Statement ............................................................................................................................ 13-99 IF Statement .................................................................................................................................. 13-101 INSERT Statement ....................................................................................................................... 13-104
xiv
Literals............................................................................................................................................. LOCK TABLE Statement ............................................................................................................. LOOP Statements.......................................................................................................................... MERGE Statement........................................................................................................................ NULL Statement............................................................................................................................ Object Types .................................................................................................................................. OPEN Statement

........................................................................................................................... OPEN-FOR Statement.................................................................................................................. OPEN-FOR-USING Statement .................................................................................................. Packages.......................................................................................................................................... Procedures ...................................................................................................................................... RAISE Statement .......................................................................................................................... Records............................................................................................................................................ RESTRICT_REFERENCES Pragma........................................................................................... RETURN Statement...................................................................................................................... ROLLBACK Statement ................................................................................................................ %ROWTYPE Attribute ................................................................................................................ SAVEPOINT Statement ............................................................................................................... SELECT INTO Statement............................................................................................................ SERIALLY_REUSABLE Pragma ................................................................................................ SET TRANSACTION Statement ............................................................................................... SQL Cursor..................................................................................................................................... SQLCODE Function..................................................................................................................... SQLERRM Function..................................................................................................................... %TYPE Attribute........................................................................................................................... UPDATE Statement ......................................................................................................................
13-107 13-110 13-112 13-119 13-121 13-122 13-131 13-133 13-136 13-139 13-145 13-150 13-152 13-156 13-159 13-161 13-163 13-165 13-166 13-171 13-173 13-175 13-178 13-180 13-182 13-184
A
Sample PL/SQL ProgramsRunning the Programs .................................................................................................

...................... Sample 1. FOR Loop........................................................................................................................... Input Table..................................................................................................................................... PL/SQL Block ............................................................................................................................... Output Table ................................................................................................................................. Sample 2. Cursors ............................................................................................................................... Input Table..................................................................................................................................... A-2 A-3 A-3 A-3 A-4 A-4 A-4
xv
PL/SQL Block................................................................................................................................ A-5 Output Table.................................................................................................................................. A-5 Sample 3. Scoping............................................................................................................................... A-6 Input Table..................................................................................................................................... A-6 PL/SQL Block................................................................................................................................ A-6 Output Table.................................................................................................................................. A-7 Sample 4. Batch Transaction Processing......................................................................................... A-7 Input Tables ................................................................................................................................... A-8 PL/SQL Block................................................................................................................................ A-8 Output Tables............................................................................................................................. A-10 Sample 5. Embedded PL/SQL........................................................................................................ A-11 Input Table.................................................................................................................................. A-11 PL/SQL Block in a C Program................................................................................................. A-11 Interactive Session ..................................................................................................................... A-13 Output Tables.............................................................

................................................................ A-14 Sample 6. Calling a Stored Procedure.......................................................................................... A-15 Input Table.................................................................................................................................. A-15 Stored Procedure........................................................................................................................ A-15 Interactive Session ..................................................................................................................... A-18
B
CHAR versus VARCHAR2 SemanticsAssigning Character Values .............................................................................................................. Comparing Character Values ............................................................................................................ Inserting Character Values ................................................................................................................ Selecting Character Values................................................................................................................ B-2 B-2 B-4 B-4
C
PL/SQL Wrap UtilityAdvantages of Wrapping PL/SQL Procedures.............................................................................. Limitations of the Wrap Utility.................................................................................................. Running the Wrap Utility................................................................................................................. Input and Output Files for the Wrap Utility............................................................................ Error Handling in the Wrap Utility .......................................................................................... Version Compatibility................................................................................................................. Guidelines ........................................................................................................................................... C-1 C-2 C-2 C-3 C-4 C-4 C-4
xvi
D
PL/SQL Name ResolutionWhat Is Name Resolution?................................................................................................................ D-2 Various Forms of References ............................................................................................................ D-3 Name-Resolution Algorithm ............................................................................................................ D-4 Finding the Basis........................................................................................................................... D-5 Understanding Capture ..................................................................................................................... D-7 Inner Capture ..................................

本文来源：https://www.bwwdw.com/article/zunq.html