Oracle Database PL/SQL Language Reference

[Pages:768][1O] racle? Database

PL/SQL Language Reference 11g Release 2 (11.2)

E25519-13

December 2014

Oracle Database PL/SQL Language Reference, 11g Release 2 (11.2)

E25519-13

Copyright ? 1996, 2014, Oracle and/or its affiliates. All rights reserved.

Primary Author: Sheila Moore

Contributing Author: Eric Belden

Contributors: 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. Yang

This 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. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The 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 writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. 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. No other rights are granted to the U.S. Government.

This 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 applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel 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. 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 Group.

This 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 Oracle.

Contents

Preface ............................................................................................................................................................ xxvii

Audience.................................................................................................................................................. xxvii Documentation Accessibility ................................................................................................................ xxvii Related Documents ............................................................................................................................... xxviii Conventions ........................................................................................................................................... xxviii Syntax Descriptions............................................................................................................................... xxviii

What's New in PL/SQL?......................................................................................................................... xxix

PL/SQL Feature for Oracle Database 11g Release 2 (11.2.0.2).......................................................... xxix PL/SQL Features for Oracle Database 11g Release 2......................................................................... xxix PL/SQL Features for Oracle Database 11g Release 1......................................................................... xxxi

1 Overview of PL/SQL

Advantages of PL/SQL ............................................................................................................................ 1-1 Tight Integration with SQL............................................................................................................... 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 Applications.................................................................................... 1-3 Support for Developing Server Pages ............................................................................................. 1-4

Main Features of PL/SQL........................................................................................................................ 1-4 Error Handling ................................................................................................................................... 1-4 Blocks ................................................................................................................................................... 1-5 Variables and Constants.................................................................................................................... 1-5 Subprograms....................................................................................................................................... 1-6 Packages .............................................................................................................................................. 1-6 Triggers................................................................................................................................................ 1-6 Input and Output ............................................................................................................................... 1-6 Data Abstraction................................................................................................................................. 1-7 Cursors ......................................................................................................................................... 1-7 Composite Variables................................................................................................................... 1-7 %ROWTYPE Attribute ............................................................................................................... 1-8

v

%TYPE Attribute......................................................................................................................... 1-8 Abstract Data Types ................................................................................................................... 1-8 Control Statements............................................................................................................................. 1-8 Conditional Compilation .................................................................................................................. 1-9 Processing a Query Result Set One Row at a Time ....................................................................... 1-9 Architecture of PL/SQL ........................................................................................................................ 1-10 PL/SQL Engine ............................................................................................................................... 1-10 PL/SQL Units and Compilation Parameters .............................................................................. 1-10

2 PL/SQL Language Fundamentals

Character Sets............................................................................................................................................ 2-1 Database Character Set...................................................................................................................... 2-1 National Character Set....................................................................................................................... 2-3

Lexical Units .............................................................................................................................................. 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 Comments................................................................................................................ 2-10 Whitespace Characters Between Lexical Units........................................................................... 2-11

Declarations............................................................................................................................................ 2-12 Variable Declarations...................................................................................................................... 2-12 Constant Declarations .................................................................................................................... 2-13 Initial Values of Variables and Constants.................................................................................... 2-13 NOT NULL Constraint................................................................................................................... 2-14 %TYPE Attribute ............................................................................................................................. 2-15

References to Identifiers ...................................................................................................................... 2-16 Scope and Visibility of Identifiers .................................................................................................... 2-17 Assigning Values to Variables ........................................................................................................... 2-21

Assigning Values to Variables with the Assignment Statement.............................................. 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 Variables.................................................................................. 2-23 Expressions ............................................................................................................................................. 2-24 Concatenation Operator ................................................................................................................. 2-24 Operator Precedence....................................................................................................................... 2-25 Logical Operators ............................................................................................................................ 2-27 Short-Circuit Evaluation ................................................................................................................ 2-32 Comparison Operators ................................................................................................................... 2-32

IS [NOT] NULL Operator ....................................................................................................... 2-33 Relational Operators................................................................................................................ 2-33 LIKE Operator .......................................................................................................................... 2-35 BETWEEN Operator................................................................................................................ 2-36

vi

IN Operator............................................................................................................................... BOOLEAN Expressions ................................................................................................................. CASE Expressions ...........................................................................................................................

Simple CASE Expression ........................................................................................................ Searched CASE Expression .................................................................................................... SQL Functions in PL/SQL Expressions ....................................................................................... Error-Reporting Functions................................................................................................................... Pragmas ................................................................................................................................................... Conditional Compilation..................................................................................................................... How Conditional Compilation Works......................................................................................... Preprocessor Control Tokens ................................................................................................. Selection Directives.................................................................................................................. Error Directives ........................................................................................................................ Inquiry Directives .................................................................................................................... Static Expressions..................................................................................................................... Conditional Compilation Examples ............................................................................................. Retrieving and Printing Post-Processed Source Text................................................................. Conditional Compilation Directive Restrictions ........................................................................

2-37 2-38 2-39 2-39 2-40 2-41 2-42 2-42 2-42 2-43 2-43 2-44 2-44 2-44 2-47 2-51 2-52 2-53

3 PL/SQL Data Types

SQL Data Types........................................................................................................................................ 3-2 Different Maximum Sizes ................................................................................................................. 3-2 Additional PL/SQL Constants for BINARY_FLOAT and BINARY_DOUBLE........................ 3-2 Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE........................... 3-3 CHAR and VARCHAR2 Variables.................................................................................................. 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 Variables................................................................................................. 3-6 ROWID and UROWID Variables.................................................................................................... 3-6

BOOLEAN Data Type ............................................................................................................................. 3-7 PLS_INTEGER and BINARY_INTEGER Data Types ...................................................................... 3-8

Preventing PLS_INTEGER Overflow.............................................................................................. 3-8 Predefined PLS_INTEGER Subtypes .............................................................................................. 3-9 SIMPLE_INTEGER Subtype of PLS_INTEGER.......................................................................... 3-10

SIMPLE_INTEGER Overflow Semantics ............................................................................. 3-10 Expressions with Both SIMPLE_INTEGER and Other Operands .................................... 3-11 Integer Literals in SIMPLE_INTEGER Range...................................................................... 3-11 User-Defined PL/SQL Subtypes......................................................................................................... 3-11 Unconstrained Subtypes ................................................................................................................ 3-12 Constrained Subtypes..................................................................................................................... 3-12 Subtypes with Base Types in Same Data Type Family.............................................................. 3-14

4 PL/SQL Control Statements

Conditional Selection Statements......................................................................................................... 4-1 IF THEN Statement............................................................................................................................ 4-2

vii

IF THEN ELSE Statement ................................................................................................................. 4-3 IF THEN ELSIF Statement ................................................................................................................ 4-5 Simple CASE Statement .................................................................................................................... 4-6 Searched CASE Statement ................................................................................................................ 4-7 LOOP Statements ..................................................................................................................................... 4-9 Basic LOOP Statement....................................................................................................................... 4-9 EXIT Statement ................................................................................................................................ 4-10 EXIT WHEN Statement .................................................................................................................. 4-10 CONTINUE Statement ................................................................................................................... 4-12 CONTINUE WHEN Statement ..................................................................................................... 4-13 FOR LOOP Statement..................................................................................................................... 4-14

FOR LOOP Index ..................................................................................................................... 4-16 Lower Bound and Upper Bound ........................................................................................... 4-18 EXIT WHEN or CONTINUE WHEN Statement in FOR LOOP Statement..................... 4-19 WHILE LOOP Statement ............................................................................................................... 4-20 Sequential Control Statements ........................................................................................................... 4-21 GOTO Statement ............................................................................................................................. 4-21 NULL Statement.............................................................................................................................. 4-24

5 PL/SQL Collections and Records

Collection Types....................................................................................................................................... 5-2 Associative Arrays.................................................................................................................................... 5-4

Declaring Associative Array Constants .......................................................................................... 5-6 NLS Parameter Values Affect Associative Arrays Indexed by String........................................ 5-7

Changing NLS Parameter Values After Populating Associative Arrays............................ 5-7 Indexes of Data Types Other Than VARCHAR2 ................................................................... 5-7 Passing Associative Arrays to Remote Databases.................................................................. 5-8 Appropriate Uses for Associative Arrays....................................................................................... 5-8 Varrays (Variable-Size Arrays) .............................................................................................................. 5-8 Appropriate Uses for Varrays ....................................................................................................... 5-10 Nested Tables......................................................................................................................................... 5-10 Important Differences Between Nested Tables and Arrays ..................................................... 5-13 Appropriate Uses for Nested Tables ............................................................................................ 5-13 Collection Constructors........................................................................................................................ 5-14 Assigning Values to Collection Variables........................................................................................ 5-15 Data Type Compatibility................................................................................................................ 5-15 Assigning Null Values to Varray or Nested Table Variables ................................................... 5-16 Assigning Set Operation Results to Nested Table Variables .................................................... 5-16 Multidimensional Collections ............................................................................................................ 5-18 Collection Comparisons....................................................................................................................... 5-19 Comparing Varray and Nested Table Variables to NULL........................................................ 5-20 Comparing Nested Tables for Equality and Inequality ............................................................ 5-20 Comparing Nested Tables with SQL Multiset Conditions ....................................................... 5-21 Collection Methods............................................................................................................................... 5-22 DELETE Collection Method .......................................................................................................... 5-23 TRIM Collection Method ............................................................................................................... 5-26 EXTEND Collection Method ......................................................................................................... 5-27

viii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download