Oracle Database PL/SQL Language Reference

1 [1] Oracle DatabasePL/SQL Language Reference 11g Release 2 ( ) E25519-13 December 2014 Oracle Database PL/SQL Language Reference , 11g Release 2 ( ) E25519-13 Copyright 1996, 2014, Oracle and/or its affiliates. All rights Author: Sheila MooreContributing Author: Eric BeldenContributors: D. Alpern, E. Belden, S. Agrawal, H. Baer, S. Castledine, T. Chang, B. Cheng, R. Dani, R. Decker, C. Iyer, A. Kruglikov, S. Kotsovolos, N. Le, W. Li, B. Llewellyn, V. Moore, T. Raney, K. Rich, C. Wetherell, G. Viswanathan, M. YangThis software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means.

2 Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in this is software or related documentation that is delivered to the Government or anyone licensing it on behalf of the Government, then the following notice is GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs.

3 No other rights are granted to the software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc.

4 AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle . Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and xxviiDocumentation Accessibility .. xxviiRelated Documents .. xxviiiConventions.

5 XxviiiSyntax xxviiiWhat's New in PL/SQL ?.. xxixPL/SQL Feature for Oracle Database 11g Release 2 ( ).. xxixPL/SQL Features for Oracle Database 11g Release xxixPL/SQL Features for Oracle Database 11g Release xxxi1 Overview of PL/SQLA dvantages of 1-1 Tight Integration with 1-1 High Performance .. 1-2 High Productivity .. 1-2 Portability .. 1-3 Scalability .. 1-3 Manageability .. 1-3 Support for Object-Oriented Programming .. 1-3 Support for Developing Web 1-3 Support for Developing Server Pages .. 1-4 Main Features of 1-4 Error Handling .. 1-4 Blocks .. 1-5 Variables and 1-6 Packages .. 1-6 Triggers .. 1-6 Input and Output .. 1-6 Data 1-7 Cursors .. 1-7 Composite 1-7%ROWTYPE Attribute .. 1-8vi%TYPE Attribute .. 1-8 Abstract Data Types .. 1-8 Control 1-8 Conditional Compilation.

6 1-9 Processing a Query Result Set One Row at a Time .. 1-9 Architecture of 1-10PL/SQL Engine .. 1-10PL/SQL Units and Compilation Parameters .. 1-102 PL/SQL Language FundamentalsCharacter 2-1 Database Character Set .. 2-1 National Character 2-3 Lexical 2-3 Delimiters .. 2-3 Identifiers .. 2-4 Reserved Words and Keywords .. 2-5 Predefined Identifiers .. 2-5 User-Defined Identifiers .. 2-5 Literals .. 2-8 Comments .. 2-10 Single-Line Comments .. 2-10 Multiline 2-10 Whitespace Characters Between Lexical Units .. 2-12 Variable 2-12 Constant Declarations .. 2-13 Initial Values of Variables and 2-13 NOT NULL 2-14%TYPE Attribute .. 2-15 References to 2-16 Scope and Visibility of 2-17 Assigning Values to 2-21 Assigning Values to Variables with the Assignment Statement.

7 2-21 Assigning Values to Variables with the SELECT INTO Statement .. 2-22 Assigning Values to Variables as Parameters of a Subprogram .. 2-23 Assigning Values to BOOLEAN 2-24 Concatenation Operator .. 2-24 Operator 2-25 Logical Operators .. 2-27 Short-Circuit Evaluation .. 2-32 Comparison Operators .. 2-32IS [NOT] NULL Operator .. 2-33 Relational 2-33 LIKE Operator .. 2-35 BETWEEN Operator .. 2-36viiIN 2-37 BOOLEAN Expressions .. 2-38 CASE Expressions .. 2-39 Simple CASE Expression .. 2-39 Searched CASE Expression .. 2-40 SQL Functions in PL/SQL Expressions .. 2-41 Error-Reporting 2-42 Conditional 2-42 How Conditional Compilation 2-43 Preprocessor Control Tokens .. 2-43 Selection 2-44 Error Directives .. 2-44 Inquiry Directives .. 2-44 Static 2-47 Conditional Compilation Examples.

8 2-51 Retrieving and Printing Post-Processed Source 2-52 Conditional Compilation Directive Restrictions .. 2-533 PL/SQL Data TypesSQL Data 3-2 Different Maximum Sizes .. 3-2 Additional PL/SQL Constants for BINARY_FLOAT and 3-2 Additional PL/SQL Subtypes of BINARY_FLOAT and 3-3 CHAR and VARCHAR2 3-3 Assigning or Inserting Too-Long Values .. 3-3 Declaring Variables for Multibyte Characters .. 3-4 Differences Between CHAR and VARCHAR2 Data Types .. 3-5 LONG and LONG RAW 3-6 ROWID and UROWID 3-6 BOOLEAN Data 3-7 PLS_INTEGER and BINARY_INTEGER Data 3-8 Preventing PLS_INTEGER 3-8 Predefined PLS_INTEGER Subtypes .. 3-9 SIMPLE_INTEGER Subtype of 3-10 SIMPLE_INTEGER Overflow Semantics .. 3-10 Expressions with Both SIMPLE_INTEGER and Other Operands .. 3-11 Integer Literals in SIMPLE_INTEGER 3-11 User-Defined PL/SQL 3-11 Unconstrained Subtypes.

9 3-12 Constrained 3-12 Subtypes with Base Types in Same Data Type 3-144 PL/SQL Control StatementsConditional Selection 4-1IF THEN Statement .. 4-2viiiIF THEN ELSE Statement .. 4-3IF THEN ELSIF Statement .. 4-5 Simple CASE Statement .. 4-6 Searched CASE Statement .. 4-7 LOOP 4-9 Basic LOOP 4-9 EXIT Statement .. 4-10 EXIT WHEN Statement .. 4-10 CONTINUE Statement .. 4-12 CONTINUE WHEN Statement .. 4-13 FOR LOOP 4-14 FOR LOOP Index .. 4-16 Lower Bound and Upper Bound .. 4-18 EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP 4-19 WHILE LOOP Statement .. 4-20 Sequential Control 4-21 GOTO Statement .. 4-21 NULL 4-245 PL/SQL Collections and RecordsCollection 5-2 Associative 5-4 Declaring Associative Array Constants .. 5-6 NLS Parameter Values Affect Associative Arrays Indexed by 5-7 Changing NLS Parameter Values After Populating Associative 5-7 Indexes of Data Types Other Than VARCHAR2.

10 5-7 Passing Associative Arrays to Remote 5-8 Appropriate Uses for Associative 5-8 Varrays (Variable-Size Arrays).. 5-8 Appropriate Uses for Varrays .. 5-10 Nested 5-10 Important Differences Between Nested Tables and Arrays .. 5-13 Appropriate Uses for Nested Tables .. 5-13 Collection 5-14 Assigning Values to Collection 5-15 Data Type 5-15 Assigning Null Values to Varray or Nested Table Variables .. 5-16 Assigning Set Operation Results to Nested Table Variables .. 5-16 Multidimensional 5-18 Collection 5-19 Comparing Varray and Nested Table Variables to 5-20 Comparing Nested Tables for Equality and Inequality .. 5-20 Comparing Nested Tables with SQL Multiset Conditions .. 5-21 Collection 5-22 DELETE Collection Method .. 5-23 TRIM Collection Method .. 5-26 EXTEND Collection Method.