[PDF] [PDF] Oracle Database PL/SQL Language Reference - Oracle Help Center

[PDF] Mastering Oracle PL/SQL: Practical Solutions

developers to present dynamic Web pages from directly within the data- base SQL*Plus provides many useful options and commands that we'll make frequent In fact, many a training manual, book, and even the standard Oracle documen-

[PDF] Oracle Database PL/SQL Users Guide and Reference - Oracle Help

Records 5 - 35 · Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings Shows how PL/SQL supports the SQL commands, functions, and operators for PL/SQL's exception handling is different from the manual checking 

[PDF] Oracle Database PL/SQL Language Reference - Oracle Help Center

29 mar 2010 · When Explicit Cursor Queries Need Column Aliases Dynamic SQL Statement is Not Anonymous Block or CALL Statement 7- 

[PDF] Oracle10g Advanced PL/SQL Programming - ITCourseware

14 août 2006 · Oracle 10g Advanced PL/SQL Programming Using DBMS_SQL for Queries that has either a hard or soft shell top, and manual or power

[PDF] Pl/sql tutorial w3schools pdf - Squarespace

The HTML5 certificate documents your knowledge of advanced HTML5 Tutorial Oracle PL/SQL Variable Identifiers Tutorial with Examples Tutorial Oracle  

[PDF] Preview PL/SQL Tutorial (PDF Version) - Tutorialspoint

This tutorial will give you great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts Audience This tutorial is 

[PDF] TOP TWENTY PL/SQL TIPS AND TECHNIQUES

Since PL/SQL Version 2 was first released, for example, Oracle Corporation has added dynamic SQL, RDBMS-based job scheduling, file I/O, and cursor variables  

[PDF] Easy Oracle PL/SQL Programming - Caribbean Environment

Right here, we have countless books Easy Oracle PL/SQL Programming: Get Started Fast with Working PL/SQL Code Examples and collections to check The basics of PL/SQL are introduced using multiple triggers Extend functionality using dynamic SQL statements Understand Working PL/SQL Code Examples pdf

[PDF] An Training Guide PL/SQL for Beginners - Toolboxcom

Exceptions for an Oracle Error Code 100 There are some more advanced operators available SQL*Plus is by entering PL/SQL commands directly into the 

[PDF] oracle advanced support gateway documentation

[PDF] oracle amr support

[PDF] oracle apex 19 manual

[PDF] oracle apex 19.1 architecture

[PDF] oracle apex 19.1 tutorial

[PDF] oracle apex 19.1 tutorial for beginners

[PDF] oracle apex 19.1 tutorial pdf

[PDF] oracle apex 19.2 download

[PDF] oracle apex 19.2 installation

[PDF] oracle apex 19.2 new features

[PDF] oracle apex 19.2 popup lov

[PDF] oracle apex 5

[PDF] oracle apex components

[PDF] oracle apex documentation

[PDF] oracle apex download file button

[1] Oracle® 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.

v

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

vi

%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

vii

IN Operator............................................................................................................................... 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 Functions................................................................................................................... 2-42

Pragmas................................................................................................................................................... 2-42

Conditional Compilation..................................................................................................................... 2-42

How Conditional Compilation Works......................................................................................... 2-43

Preprocessor Control Tokens................................................................................................. 2-43

Selection Directives.................................................................................................................. 2-44

Error Directives........................................................................................................................ 2-44

Inquiry Directives.................................................................................................................... 2-44

Static Expressions..................................................................................................................... 2-47

Conditional Compilation Examples............................................................................................. 2-51

Retrieving and Printing Post-Processed Source Text................................................................. 2-52

Conditional Compilation Directive Restrictions ........................................................................ 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

viii

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

ix

EXISTS Collection Method............................................................................................................. 5-28

FIRST and LAST Collection Methods .......................................................................................... 5-29

FIRST and LAST Methods for Associative Array............................................................... 5-29

FIRST and LAST Methods for Varray................................................................................... 5-31

FIRST and LAST Methods for Nested Table........................................................................ 5-32

COUNT Collection Method........................................................................................................... 5-33

COUNT Method for Varray................................................................................................... 5-33

COUNT Method for Nested Table........................................................................................ 5-34

LIMIT Collection Method .............................................................................................................. 5-35

PRIOR and NEXT Collection Methods........................................................................................ 5-36

Collection Types Defined in Package Specifications.................................................................... 5-38

Record Variables................................................................................................................................... 5-39

Initial Values of Record Variables ................................................................................................ 5-40

Declaring Record Constants.......................................................................................................... 5-40

RECORD Types............................................................................................................................... 5-41

%ROWTYPE Attribute................................................................................................................... 5-44

Record Variable that Always Represents Full Row............................................................ 5-44

Record Variable that Can Represent Partial Row ............................................................... 5-45

%ROWTYPE Attribute and Virtual Columns...................................................................... 5-47

Assigning Values to Record Variables.............................................................................................. 5-48

Assigning One Record Variable to Another................................................................................ 5-48

quotesdbs_dbs17.pdfusesText_23