PL/SQL User's Guide and Reference 10g Release 2 (10.2)

1 oracle DatabasePL/SQL User's Guide and Reference 10g Release 2 ( ) B14261-01 June 2005 oracle Database PL/SQL User s Guide and Reference 10g Release 2 ( ) B14261-01 Copyright 1996, 2005, oracle . All rights : Shashaanka Agrawal, Cailein Barclay, Eric Belden, Dmitri Bronnikov, Sharon Castledine, Thomas Chang, Ravindra Dani, Chandrasekharan Iyer, Susan Kotsovolos, Neil Le, Warren Li, Bryn Llewellyn, Valarie Moore, Chris Racicot, Murali Vemulapati, John Russell, Guhan Viswanathan, Minghui Ya n gThe Programs (which include both the software and documentation) contain proprietary information; 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 specified by law, is information contained in this document is subject to change without notice.

2 If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be 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 the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR , Commercial Computer Software Restricted Rights (June 1987).

3 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 we disclaim liability for any damages caused by such use of the Programs. oracle , JD Edwards, PeopleSoft, and Retek are registered trademarks of oracle Corporation and/or its affiliates. Other names may be trademarks of their respective Programs may provide links to Web sites and access to content, products, and services from third parties. oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party.

4 oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party. iiiContentsSend Us Your Comments .. xixDocumentation Accessibility .. xixStructure .. xxPL/SQL Sample xxiRelated Documents .. xxiiConventions .. xxiiWhat's New in PL/SQL ?.. xxvNew Features in PL/SQL for oracle Database 10g Release 2 ( ).. xxvNew Features in PL/SQL for oracle Database 10g Release 1 ( ).. xxvi1 Overview of PL/SQLA dvantages of 1-1 Tight Integration with 1-2 Better 1-2 Higher Productivity .. 1-3 Full Portability .. 1-3 Tight 1-3 Access to Pre-defined Packages .. 1-4 Support for Object-Oriented Programming.

5 1-4 Support for Developing Web Applications and 1-4 Understanding the Main Features of 1-4 Understanding PL/SQL Block 1-4 Understanding PL/SQL Variables and 1-5 Declaring 1-5 Assigning Values to a Variable .. 1-6 Bind Variables .. 1-7 Declaring 1-7 Processing Queries with 1-8 Declaring PL/SQL 1-8 Declaring Datatypes for PL/SQL 1-8%TYPE .. 1-8iv%ROWTYPE .. 1-9 Understanding PL/SQL Control Structures .. 1-9 Conditional 1-10 Iterative Control .. 1-10 Sequential Control .. 1-12 Understanding Conditional Compilation .. 1-12 Writing Reusable PL/SQL 1-13 Subprograms: Procedures and Functions .. 1-13 Packages: APIs Written in 1-13 Inputting and Outputting Data with 1-15 Understanding PL/SQL Data 1-16 Cursors .. 1-16 Records .. 1-16 Object Types .. 1-17 Understanding PL/SQL Error Handling .. 1-18PL/SQL 1-18In the oracle Database Server.

6 1-19 Anonymous Blocks .. 1-19 Stored Subprograms .. 1-19 Database Triggers .. 1-20In oracle 1-212 Fundamentals of the PL/SQL LanguageCharacter Sets and Lexical 2-1 Delimiters .. 2-2 Identifiers .. 2-3 Reserved Words .. 2-4 Predefined Identifiers .. 2-4 Quoted Identifiers .. 2-4 Literals .. 2-4 Numeric 2-5 Character Literals .. 2-6 String 2-6 BOOLEAN Literals .. 2-7 Datetime Literals .. 2-7 Comments .. 2-7 Single-Line Comments .. 2-7 Multi-line Comments .. 2-8 Restrictions on Comments .. 2-8 Constants .. 2-9 Using DEFAULT .. 2-9 Using NOT NULL .. 2-9 Using the %TYPE Attribute .. 2-10 Using the %ROWTYPE Attribute .. 2-11 Aggregate 2-12vUsing Aliases .. 2-12 Restrictions on Declarations .. 2-13PL/SQL Naming 2-13 Scope and Visibility of PL/SQL 2-15 Assigning Values to 2-18 Assigning BOOLEAN 2-19 Assigning a SQL Query Result to a PL/SQL Variable.

7 2-19PL/SQL Expressions and 2-19 Logical Operators .. 2-20 Order of Evaluation .. 2-21 Short-Circuit Evaluation .. 2-21 Comparison 2-22 Relational 2-22IS NULL 2-23 LIKE Operator .. 2-23 BETWEEN 2-23IN 2-23 Concatenation 2-23 BOOLEAN Expressions .. 2-24 BOOLEAN Arithmetic Expressions .. 2-24 BOOLEAN Character Expressions .. 2-24 BOOLEAN Date Expressions .. 2-25 Guidelines for PL/SQL BOOLEAN Expressions .. 2-25 CASE Expressions .. 2-26 Simple CASE 2-26 Searched CASE Expression .. 2-27 Handling Null Values in Comparisons and Conditional Statements .. 2-27 NULLs and the NOT Operator .. 2-28 Conditional 2-30 How Does Conditional Compilation Work? .. 2-31 Conditional Compilation Control 2-31 Using Conditional Compilation Selection 2-31 Using Conditional Compilation Error Directives .. 2-31 Using Conditional Compilation Inquiry Directives.

8 2-31 Using Predefined Inquiry Directives With Conditional Compilation .. 2-32 Using Static Expressions with Conditional 2-33 Setting the PLSQL_CCFLAGS Initialization 2-34 Using DBMS_DB_VERSION Package Constants .. 2-35 Conditional Compilation Examples .. 2-35 Using Conditional Compilation to Specify Code for Database Versions .. 2-35 Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text .. 2-36 Conditional Compilation 2-37 Using PL/SQL to Create Web Applications and Server 2-38PL/SQL Web Applications .. 2-38PL/SQL Server Pages .. 2-38 Summary of PL/SQL Built-In 2-38vi3 PL/SQL DatatypesOverview of Predefined PL/SQL 3-1PL/SQL Number Types .. 3-2 BINARY_INTEGER Datatype .. 3-2 BINARY_FLOAT and BINARY_DOUBLE 3-2 NUMBER Datatype .. 3-3 PLS_INTEGER 3-4PL/SQL Character and String Types .. 3-4 CHAR Datatype .. 3-4 LONG and LONG RAW Datatypes .. 3-5 RAW Datatype.

9 3-6 ROWID and UROWID Datatype .. 3-6 VARCHAR2 3-8PL/SQL National Character Types .. 3-9 Comparing UTF8 and AL16 UTF16 3-9 NCHAR Datatype .. 3-9 NVARCHAR2 Datatype .. 3-10PL/SQL LOB Types .. 3-10 BFILE Datatype .. 3-11 BLOB 3-11 CLOB Datatype .. 3-11 NCLOB Datatype .. 3-12PL/SQL Boolean 3-12 BOOLEAN Datatype .. 3-12PL/SQL Date, Time, and Interval Types .. 3-12 DATE Datatype .. 3-13 TIMESTAMP Datatype .. 3-13 TIMESTAMP WITH TIME ZONE 3-14 TIMESTAMP WITH LOCAL TIME ZONE 3-15 INTERVAL YEAR TO MONTH Datatype .. 3-16 INTERVAL DAY TO SECOND Datatype .. 3-16 Datetime and Interval 3-17 Avoiding Truncation Problems Using Date and Time 3-17 Overview of PL/SQL 3-17 Defining Subtypes .. 3-18 Using Subtypes .. 3-18 Type Compatibility With 3-19 Constraints and Default Values With Subtypes .. 3-20 Converting PL/SQL 3-21 Explicit 3-21 Implicit Conversion.

10 3-21 Choosing Between Implicit and Explicit Conversion .. 3-23 DATE Values .. 3-23 RAW and LONG RAW Values .. 3-23 Differences between the CHAR and VARCHAR2 3-23 Assigning Character Values .. 3-23 Comparing Character Values .. 3-24viiInserting Character Values .. 3-25 Selecting Character Values .. 3-264 Using PL/SQL Control StructuresOverview of PL/SQL Control 4-1 Testing Conditions: IF and CASE 4-2 Using the IF-THEN Statement .. 4-2 Using the IF-THEN-ELSE 4-2 Using the IF-THEN-ELSIF 4-3 Using CASE Statements .. 4-4 Searched CASE Statement .. 4-5 Guidelines for PL/SQL Conditional Statements .. 4-6 Controlling Loop Iterations: LOOP and EXIT 4-7 Using the LOOP 4-7 Using the EXIT Statement .. 4-7 Using the EXIT-WHEN 4-8 Labeling a PL/SQL Loop .. 4-8 Using the WHILE-LOOP Statement .. 4-9 Using the FOR-LOOP Statement .. 4-9 How PL/SQL Loops Iterate .. 4-10 Dynamic Ranges for Loop 4-11 Scope of the Loop Counter Variable.