PL/SQL User’s Guide and Reference phần 1 pdf

PL/SQL

User’s Guide and Reference

Release 8.1.6

December 1999

Part No. A77069-01

PL/SQL User’s Guide and Reference, Release 8.1.6

Part No. A77069-01

Copyright © 1999, Oracle Corporation. All rights reserved.

Author: Tom Portfolio

Graphics Artist: Valarie Moore

Contributors: Dave Alpern, Chandrasekharan Iyer, Ervan Darnell, Ken Jacobs, Sanjay

Kaluskar, Sanjay Krishnamurthy, Janaki Krishnaswamy, Neil Le, Kannan Muthukkaruppan,

Shirish Puranik, Chris Racicot, Ken Rudin, Usha Sangam, Ajay Sethi, Guhan Viswanathan

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 is prohibited.

The information contained in this document is subject to change without notice. If you find 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, Oracle Call Interface, Oracle Developer, Oracle Forms, Oracle Reports, and SQL*Plus are

registered trademarks of Oracle Corporation. Net8, Oracle8i, PL/SQL, Pro*C, and Pro*C/C++ are

trademarks of Oracle Corporation. All other company or product names mentioned are used for

identification purposes only and may be trademarks of their respective owners.

i

Contents

Send Us Your Comments .................................................................................................................. xv

Preface......................................................................................................................................................... xvii

1 Overview

Main Features ...................................................................................................................................... 1-2

Block Structure.............................................................................................................................. 1-2

Variables and Constants .............................................................................................................. 1-3

Cursors ........................................................................................................................................... 1-5

Cursor FOR Loops........................................................................................................................ 1-6

Cursor Variables ........................................................................................................................... 1-6

Attributes ....................................................................................................................................... 1-7

Control Structures ........................................................................................................................ 1-9

Modularity................................................................................................................................... 1-11

Data Abstraction ......................................................................................................................... 1-13

Information Hiding .................................................................................................................... 1-15

Error Handling............................................................................................................................ 1-16

Architecture........................................................................................................................................ 1-17

In the Oracle Server.................................................................................................................... 1-18

In Oracle Tools ............................................................................................................................ 1-19

Advantages of PL/SQL..................................................................................................................... 1-20

Support for SQL.......................................................................................................................... 1-20

Support for Object-Oriented Programming ........................................................................... 1-21

Better Performance ..................................................................................................................... 1-21

ii

Higher Productivity.................................................................................................................... 1-22

Full Portability............................................................................................................................. 1-23

Tight Integration with SQL ....................................................................................................... 1-23

Tight Security .............................................................................................................................. 1-23

2 Fundamentals

Character Set ........................................................................................................................................ 2-2

Lexical Units......................................................................................................................................... 2-2

Delimiters....................................................................................................................................... 2-3

Identifiers ....................................................................................................................................... 2-4

Literals ............................................................................................................................................ 2-7

Comments.................................................................................................................................... 2-10

Datatypes ............................................................................................................................................ 2-11

Number Types............................................................................................................................. 2-12

Character Types .......................................................................................................................... 2-15

National Character Types.......................................................................................................... 2-20

LOB Types.................................................................................................................................... 2-22

Other Types ................................................................................................................................. 2-23

User-Defined Subtypes .................................................................................................................... 2-25

Defining Subtypes ...................................................................................................................... 2-25

Using Subtypes............................................................................................................................ 2-26

Datatype Conversion........................................................................................................................ 2-28

Explicit Conversion .................................................................................................................... 2-28

Implicit Conversion.................................................................................................................... 2-28

Implicit versus Explicit Conversion......................................................................................... 2-29

DATE Values ............................................................................................................................... 2-29

RAW and LONG RAW Values................................................................................................. 2-30

Declarations ....................................................................................................................................... 2-30

Using DEFAULT......................................................................................................................... 2-31

Using NOT NULL....................................................................................................................... 2-32

Using %TYPE .............................................................................................................................. 2-32

Using %ROWTYPE..................................................................................................................... 2-33

Restrictions................................................................................................................................... 2-36

iii

Naming Conventions ....................................................................................................................... 2-36

Synonyms .................................................................................................................................... 2-37

Scoping......................................................................................................................................... 2-37

Case Sensitivity ........................................................................................................................... 2-37

Name Resolution ........................................................................................................................ 2-37

Scope and Visibility ......................................................................................................................... 2-38

Assignments....................................................................................................................................... 2-41

Boolean Values............................................................................................................................ 2-41

Database Values.......................................................................................................................... 2-42

Expressions and Comparisons ....................................................................................................... 2-42

Operator Precedence .................................................................................................................. 2-43

Logical Operators ....................................................................................................................... 2-44

Comparison Operators .............................................................................................................. 2-45

Concatenation Operator ............................................................................................................ 2-47

Boolean Expressions................................................................................................................... 2-47

Handling Nulls ........................................................................................................................... 2-49

Built-In Functions............................................................................................................................. 2-52

3 Control Structures

Overview .............................................................................................................................................. 3-2

Conditional Control: IF Statements ................................................................................................ 3-2

IF-THEN......................................................................................................................................... 3-3

IF-THEN-ELSE.............................................................................................................................. 3-3

IF-THEN-ELSIF............................................................................................................................. 3-4

Guidelines...................................................................................................................................... 3-5

Iterative Control: LOOP and EXIT Statements ............................................................................. 3-6

LOOP.............................................................................................................................................. 3-6

WHILE-LOOP ............................................................................................................................... 3-9

FOR-LOOP................................................................................................................................... 3-10

Sequential Control: GOTO and NULL Statements ................................................................... 3-15

GOTO Statement......................................................................................................................... 3-15

NULL Statement ......................................................................................................................... 3-19

iv

4 Collections and Records

What Is a Collection?.......................................................................................................................... 4-2

Understanding Nested Tables .................................................................................................... 4-2

Nested Tables versus Index-by Tables ...................................................................................... 4-3

Understanding Varrays ............................................................................................................... 4-4

Varrays versus Nested Tables..................................................................................................... 4-4

Defining and Declaring Collections................................................................................................ 4-5

Declaring Collections ................................................................................................................... 4-7

Initializing and Referencing Collections ....................................................................................... 4-8

Referencing Collection Elements.............................................................................................. 4-10

Assigning and Comparing Collections......................................................................................... 4-11

Comparing Whole Collections.................................................................................................. 4-13

Manipulating Collections................................................................................................................ 4-13

Some Nested Table Examples ................................................................................................... 4-13

Some Varray Examples .............................................................................................................. 4-16

Manipulating Individual Elements .......................................................................................... 4-18

Manipulating Local Collections................................................................................................ 4-20

Using Collection Methods............................................................................................................... 4-21

Using EXISTS............................................................................................................................... 4-22

Using COUNT............................................................................................................................. 4-22

Using LIMIT ................................................................................................................................ 4-22

Using FIRST and LAST .............................................................................................................. 4-23

Using PRIOR and NEXT............................................................................................................ 4-23

Using EXTEND ........................................................................................................................... 4-24

Using TRIM.................................................................................................................................. 4-25

Using DELETE ............................................................................................................................ 4-26

Applying Methods to Collection Parameters ......................................................................... 4-27

Avoiding Collection Exceptions..................................................................................................... 4-27

Taking Advantage of Bulk Binds................................................................................................... 4-29

How Do Bulk Binds Improve Performance? .......................................................................... 4-30

Using the FORALL Statement........................................................................................................ 4-32

Rollback Behavior of FORALL ................................................................................................. 4-33

Using %BULK_ROWCOUNT................................................................................................... 4-34

Restrictions on FORALL............................................................................................................ 4-35