Oracle数据库PL/SQL语言参考指南

需积分: 13 93 浏览量更新于2024-07-23 收藏 15.17MB PDF 举报

"Oracle® Database PLSQL Language Reference_e25519.pdf 是一本关于11g Release 2 (11.2)版本的Oracle数据库PL/SQL语言参考指南，由Oracle公司及其关联公司于2013年出版。本书的主要作者是Sheila Moore，还有其他多位贡献者共同编撰，内容涵盖了PL/SQL语言的各种方面，包括但不限于语法、编程结构、异常处理等。" Oracle PL/SQL是Oracle数据库中用于存储过程、函数和触发器等数据库编程的重要组件，它结合了SQL的查询功能与一种类似Ada或PL/I的程序设计语言。在11g Release 2这个版本中，PL/SQL提供了一系列增强的功能和优化，使得数据库开发者能够更高效地管理和操作数据。 1. **PL/SQL基础**：PL/SQL由声明部分（Declaring）、执行部分（Executable）和异常处理部分（Exception Handling）组成。声明部分定义变量、常量、游标、记录类型等；执行部分包含流程控制语句（如IF-THEN-ELSE, FOR循环，WHILE循环）和SQL语句；异常处理部分则用于捕获和处理运行时错误。 2. **PL/SQL变量与数据类型**：PL/SQL支持多种内置数据类型，如NUMBER、VARCHAR2、DATE、BOOLEAN等，以及PL/SQL特有的复合数据类型如Record和Table。变量可以在声明部分定义，可以进行赋值、比较和运算。 3. **PL/SQL块结构**：PL/SQL程序通常以块的形式存在，包括匿名块（Anonymous Blocks）和命名块（Procedures, Functions, Packages）。匿名块通常用于临时测试和调试，而命名块则用于组织和复用代码。 4. **控制流语句**：包括IF-THEN-ELSIF-ELSE, CASE, LOOP, EXIT, CONTINUE等，用于控制程序的执行流程。 5. **PL/SQL中的SQL操作**：PL/SQL可以直接嵌入SQL查询，进行数据的增删改查操作。通过游标（Cursor）可以逐行处理查询结果。 6. **异常处理**：PL/SQL提供了一个强大的异常处理机制，允许程序员预定义异常或者捕获运行时出现的错误。如EXCEPTION WHEN，RAISE语句等。 7. **游标和记录**：游标是用于检索结果集的指针，而记录类型则允许创建自定义的数据结构来存储多列数据。 8. **包（Packages）**：包是将相关的过程、函数、变量和类型组合在一起的高级组织形式，提供了模块化和封装的能力，提高了代码的可重用性。 9. **索引与性能优化**：在PL/SQL中，可以使用索引来加速查询，通过绑定变量（Bind Variables）来避免SQL语句的硬解析，提升数据库性能。 10. **并发与事务管理**：PL/SQL支持事务控制语句如COMMIT, ROLLBACK, SAVEPOINT，确保数据的一致性和完整性。同时，PL/SQL也处理并发问题，如锁定机制和死锁检测。这本参考指南详细介绍了以上这些概念，并提供了大量的示例和最佳实践，是学习和使用Oracle PL/SQL语言不可或缺的参考资料。通过深入学习和实践，开发者可以更好地掌握PL/SQL，从而在Oracle数据库环境中编写高效、可靠的数据库应用程序。

xvi

FOR LOOP Statement ........................................................................................................................ 13-74

FORALL Statement............................................................................................................................. 13-77

Formal Parameter Declaration .......................................................................................................... 13-80

Function Declaration and Definition .............................................................................................. 13-83

GOTO Statement................................................................................................................................. 13-88

IF Statement.......................................................................................................................................... 13-90

Implicit Cursor Attribute................................................................................................................... 13-92

INLINE Pragma ................................................................................................................................... 13-95

INSERT Statement Extension ........................................................................................................... 13-97

Named Cursor Attribute .................................................................................................................... 13-99

NULL Statement ................................................................................................................................ 13-101

OPEN Statement................................................................................................................................ 13-102

OPEN FOR Statement ...................................................................................................................... 13-104

PIPE ROW Statement ....................................................................................................................... 13-107

Procedure Declaration and Definition.......................................................................................... 13-109

RAISE Statement ............................................................................................................................... 13-111

Record Variable Declaration........................................................................................................... 13-112

RESTRICT_REFERENCES Pragma ............................................................................................... 13-115

RETURN Statement .......................................................................................................................... 13-117

RETURNING INTO Clause ............................................................................................................ 13-119

%ROWTYPE Attribute..................................................................................................................... 13-122

Scalar Variable Declaration............................................................................................................. 13-124

SELECT INTO Statement ................................................................................................................ 13-126

SERIALLY_REUSABLE Pragma..................................................................................................... 13-130

SQLCODE Function ......................................................................................................................... 13-131

SQLERRM Function ......................................................................................................................... 13-132

%TYPE Attribute ............................................................................................................................... 13-134

UPDATE Statement Extensions...................................................................................................... 13-136

WHILE LOOP Statement ................................................................................................................. 13-138

14 SQL Statements for Stored PL/SQL Units

ALTER FUNCTION Statement .......................................................................................................... 14-3

ALTER LIBRARY Statement............................................................................................................... 14-6

ALTER PACKAGE Statement ............................................................................................................ 14-8

ALTER PROCEDURE Statement ..................................................................................................... 14-11

ALTER TRIGGER Statement............................................................................................................ 14-13

ALTER TYPE Statement..................................................................................................................... 14-16

CREATE FUNCTION Statement...................................................................................................... 14-32

CREATE LIBRARY Statement.......................................................................................................... 14-41

CREATE PACKAGE Statement........................................................................................................ 14-44

CREATE PACKAGE BODY Statement........................................................................................... 14-47

CREATE PROCEDURE Statement .................................................................................................. 14-51

CREATE TRIGGER Statement......................................................................................................... 14-55

CREATE TYPE Statement.................................................................................................................. 14-73

CREATE TYPE BODY Statement..................................................................................................... 14-89

DROP FUNCTION Statement .......................................................................................................... 14-94

DROP LIBRARY Statement .............................................................................................................. 14-96

xvii

DROP PACKAGE Statement............................................................................................................ 14-97

DROP PROCEDURE Statement....................................................................................................... 14-99

DROP TRIGGER Statement ........................................................................................................... 14-101

DROP TYPE Statement .................................................................................................................... 14-102

DROP TYPE BODY Statement ....................................................................................................... 14-104

A PL/SQL Source Text Wrapping

PL/SQL Source Text Wrapping Limitations....................................................................................... A-2

PL/SQL Source Text Wrapping Guidelines ....................................................................................... A-2

PL/SQL Source Text Wrapping with PL/SQL Wrapper Utility...................................................... A-2

PL/SQL Source Text Wrapping with DBMS_DDL Subprograms................................................. A-8

B PL/SQL Name Resolution

Qualified Names and Dot Notation .................................................................................................... B-1

Column Name Precedence..................................................................................................................... B-3

Differences Between PL/SQL and SQL Name Resolution Rules .................................................. B-5

Resolution of Names in Static SQL Statements ................................................................................ B-5

What is Capture?...................................................................................................................................... B-6

Outer Capture.................................................................................................................................... B-6

Same-Scope Capture......................................................................................................................... B-6

Inner Capture..................................................................................................................................... B-7

Avoiding Inner Capture in SELECT and DML Statements............................................................ B-7

Qualifying References to Attributes and Methods....................................................................... B-8

Qualifying References to Row Expressions................................................................................... B-9

C PL/SQL Program Limits

D PL/SQL Reserved Words and Keywords

E PL/SQL Predefined Data Types

Index

xviii

List of Examples

1–1 PL/SQL Block Structure ............................................................................................................ 1-5

1–2 Processing Query Result Rows One at a Time ....................................................................... 1-9

2–1 Valid Case-Insensitive Reference to Quoted User-Defined Identifier ................................ 2-6

2–2 Invalid Case-Insensitive Reference to Quoted User-Defined Identifier ............................. 2-7

2–3 Reserved Word as Quoted User-Defined Identifier .............................................................. 2-7

2–4 Neglecting Double Quotation Marks....................................................................................... 2-8

2–5 Neglecting Case-Sensitivity....................................................................................................... 2-8

2–6 Single-Line Comments............................................................................................................ 2-10

2–7 Multiline Comments................................................................................................................ 2-11

2–8 Whitespace Characters Improving Source Text Readability............................................. 2-11

2–9 Scalar Variable Declarations................................................................................................... 2-12

2–10 Constant Declarations ............................................................................................................. 2-13

2–11 Variable and Constant Declarations with Initial Values.................................................... 2-13

2–12 Variable Initialized to NULL by Default.............................................................................. 2-14

2–13 Variable Declaration with NOT NULL Constraint............................................................. 2-14

2–14 Variables Initialized to NULL Values................................................................................... 2-15

2–15 Declaring Variable of Same Type as Column...................................................................... 2-15

2–16 Declaring Variable of Same Type as Another Variable...................................................... 2-16

2–17 Scope and Visibility of Identifiers ......................................................................................... 2-17

2–18 Qualifying Redeclared Global Identifier with Block Label................................................ 2-18

2–19 Qualifying Identifier with Subprogram Name.................................................................... 2-18

2–20 Duplicate Identifiers in Same Scope...................................................................................... 2-19

2–21 Declaring Same Identifier in Different Units....................................................................... 2-19

2–22 Label and Subprogram with Same Name in Same Scope.................................................. 2-20

2–23 Block with Multiple and Duplicate Labels........................................................................... 2-20

2–24 Assigning Values to Variables with Assignment Statement ............................................. 2-22

2–25 Assigning Value to Variable with SELECT INTO Statement............................................ 2-22

2–26 Assigning Value to Variable as IN OUT Subprogram Parameter .................................... 2-23

2–27 Assigning Value to BOOLEAN Variable.............................................................................. 2-24

2–28 Concatenation Operator.......................................................................................................... 2-25

2–29 Concatenation Operator with NULL Operands.................................................................. 2-25

2–30 Controlling Evaluation Order with Parentheses................................................................. 2-26

2–31 Expression with Nested Parentheses.................................................................................... 2-26

2–32 Improving Readability with Parentheses............................................................................. 2-26

2–33 Operator Precedence ............................................................................................................... 2-26

2–34 Procedure Prints BOOLEAN Variable.................................................................................. 2-28

2–35 AND Operator.......................................................................................................................... 2-28

2–36 OR Operator.............................................................................................................................. 2-29

2–37 NOT Operator .......................................................................................................................... 2-30

2–38 NULL Value in Unequal Comparison .................................................................................. 2-30

2–39 NULL Value in Equal Comparison....................................................................................... 2-31

2–40 NOT NULL Equals NULL...................................................................................................... 2-31

2–41 Changing Evaluation Order of Logical Operators.............................................................. 2-32

2–42 Short-Circuit Evaluation ......................................................................................................... 2-32

2–43 Relational Operators in Expressions..................................................................................... 2-33

2–44 LIKE Operator in Expression ................................................................................................. 2-35

2–45 Escape Character in Pattern.................................................................................................... 2-36

2–46 BETWEEN Operator in Expressions..................................................................................... 2-36

2–47 IN Operator in Expressions.................................................................................................... 2-36

2–48 IN Operator with Sets with NULL Values........................................................................... 2-37

2–49 Equivalent BOOLEAN Expressions...................................................................................... 2-38

2–50 Simple CASE Expression ........................................................................................................ 2-39

2–51 Simple CASE Expression with WHEN NULL..................................................................... 2-39

2–52 Searched CASE Expression .................................................................................................... 2-40

xix

2–53 Searched CASE Expression with WHEN ... IS NULL ........................................................ 2-40

2–54 Predefined Inquiry Directives $$PLSQL_LINE and $$PLSQL_UNIT ............................. 2-45

2–55 Displaying Values of PL/SQL Compilation Parameters ................................................... 2-45

2–56 PLSQL_CCFLAGS Assigns Value to Itself........................................................................... 2-46

2–57 Static Constants ........................................................................................................................ 2-49

2–58 Code for Checking Database Version ................................................................................... 2-50

2–59 Compiling Different Code for Different Database Versions ............................................. 2-51

2–60 Displaying Post-Processed Source Textsource text............................................................. 2-52

3–1 CHAR and VARCHAR2 Blank-Padding Difference ............................................................. 3-6

3–2 Printing BOOLEAN Values....................................................................................................... 3-7

3–3 PLS_INTEGER Calculation Raises Overflow Exception....................................................... 3-8

3–4 Preventing Example 3–3 Overflow........................................................................................... 3-9

3–5 Violating Constraint of SIMPLE_INTEGER Subtype......................................................... 3-10

3–6 User-Defined Unconstrained Subtypes Show Intended Use............................................. 3-12

3–7 User-Defined Constrained Subtype Detects Out-of-Range Values.................................. 3-13

3–8 Implicit Conversion Between Constrained Subtypes with Same Base Type .................. 3-13

3–9 Implicit Conversion Between Subtypes with Base Types in Same Family ..................... 3-14

4–1 IF THEN Statement..................................................................................................................... 4-2

4–2 IF THEN ELSE Statement .......................................................................................................... 4-3

4–3 Nested IF THEN ELSE Statements........................................................................................... 4-4

4–4 IF THEN ELSIF Statement......................................................................................................... 4-5

4–5 IF THEN ELSIF Statement Simulates Simple CASE Statement ........................................... 4-6

4–6 Simple CASE Statement............................................................................................................. 4-7

4–7 Searched CASE Statement ......................................................................................................... 4-8

4–8 EXCEPTION Instead of ELSE Clause in CASE Statement.................................................... 4-8

4–9 Basic LOOP Statement with EXIT Statement....................................................................... 4-10

4–10 Basic LOOP Statement with EXIT WHEN Statement......................................................... 4-10

4–11 Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements........................ 4-11

4–12 Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements..................... 4-11

4–13 CONTINUE Statement in Basic LOOP Statement .............................................................. 4-12

4–14 CONTINUE WHEN Statement in Basic LOOP Statement ................................................ 4-13

4–15 FOR LOOP Statements............................................................................................................ 4-14

4–16 Reverse FOR LOOP Statements............................................................................................. 4-15

4–17 Simulating STEP Clause in FOR LOOP Statement ............................................................. 4-15

4–18 FOR LOOP Statement Tries to Change Index Value .......................................................... 4-16

4–19 Outside Statement References FOR LOOP Statement Index............................................. 4-16

4–20 FOR LOOP Statement Index with Same Name as Variable .............................................. 4-17

4–21 FOR LOOP Statement References Variable with Same Name as Index .......................... 4-17

4–22 Nested FOR LOOP Statements with Same Index Name.................................................... 4-18

4–23 FOR LOOP Statement Bounds............................................................................................... 4-18

4–24 Specifying FOR LOOP Statement Bounds at Run Time..................................................... 4-19

4–25 EXIT WHEN Statement in FOR LOOP Statement .............................................................. 4-19

4–26 EXIT WHEN Statement in Inner FOR LOOP Statement.................................................... 4-19

4–27 CONTINUE WHEN Statement in Inner FOR LOOP Statement....................................... 4-20

4–28 WHILE LOOP Statements ...................................................................................................... 4-21

4–29 GOTO Statement...................................................................................................................... 4-22

4–30 Incorrect Label Placement....................................................................................................... 4-22

4–31 GOTO Statement Goes to Labeled NULL Statement.......................................................... 4-23

4–32 GOTO Statement Transfers Control to Enclosing Block .................................................... 4-23

4–33 GOTO Statement Cannot Transfer Control into IF Statement .......................................... 4-23

4–34 NULL Statement Showing No Action .................................................................................. 4-24

4–35 NULL Statement as Placeholder During Subprogram Creation....................................... 4-25

4–36 NULL Statement in ELSE Clause of Simple CASE Statement .......................................... 4-25

5–1 Associative Array Indexed by String ....................................................................................... 5-4

5–2 Function Returns Associative Array Indexed by PLS_INTEGER ....................................... 5-5

5–3 Declaring Associative Array Constant .................................................................................... 5-6

5–4 Varray (Variable-Size Array)..................................................................................................... 5-9

5–5 Nested Table of Local Type.................................................................................................... 5-11

5–6 Nested Table of Standalone Type.......................................................................................... 5-12

5–7 Initializing Collection (Varray) Variable to Empty............................................................. 5-14

5–8 Data Type Compatibility for Collection Assignment......................................................... 5-15

5–9 Assigning Null Value to Nested Table Variable ................................................................. 5-16

5–10 Assigning Set Operation Results to Nested Table Variable............................................... 5-17

5–11 Two-Dimensional Varray (Varray of Varrays).................................................................... 5-18

5–12 Nested Tables of Nested Tables and Varrays of Integers .................................................. 5-18

5–13 Nested Tables of Associative Arrays and Varrays of Strings............................................ 5-19

5–14 Comparing Varray and Nested Table Variables to NULL ................................................ 5-20

5–15 Comparing Nested Tables for Equality and Inequality ..................................................... 5-20

5–16 Comparing Nested Tables with SQL Multiset Conditions................................................ 5-21

5–17 DELETE Method with Nested Table..................................................................................... 5-23

5–18 DELETE Method with Associative Array Indexed by String............................................ 5-25

5–19 TRIM Method with Nested Table.......................................................................................... 5-27

5–20 EXTEND Method with Nested Table.................................................................................... 5-28

5–21 EXISTS Method with Nested Table....................................................................................... 5-29

5–22 FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER .................. 5-29

5–23 FIRST and LAST Values for Associative Array Indexed by String .................................. 5-30

5–24 Printing Varray with FIRST and LAST in FOR LOOP ....................................................... 5-31

5–25 Printing Nested Table with FIRST and LAST in FOR LOOP ............................................ 5-32

5–26 COUNT and LAST Values for Varray .................................................................................. 5-33

5–27 COUNT and LAST Values for Nested Table ....................................................................... 5-34

5–28 LIMIT and COUNT Values for Different Collection Types............................................... 5-35

5–29 PRIOR and NEXT Methods.................................................................................................... 5-36

5–30 Printing Elements of Sparse Nested Table ........................................................................... 5-37

5–31 Identically Defined Package and Local Collection Types.................................................. 5-38

5–32 Identically Defined Package and Standalone Collection Types........................................ 5-39

5–33 Declaring Record Constant..................................................................................................... 5-40

5–34 RECORD Type Definition and Variable Declarations........................................................ 5-41

5–35 RECORD Type with RECORD Field (Nested Record)....................................................... 5-42

5–36 RECORD Type with Varray Field ......................................................................................... 5-42

5–37 Identically Defined Package and Local RECORD Types................................................... 5-43

5–38 %ROWTYPE Variable Represents Full Database Table Row............................................ 5-44

5–39 %ROWTYPE Variable Does Not Inherit Initial Values or Constraints............................ 5-45

5–40 %ROWTYPE Variable Represents Partial Database Table Row ....................................... 5-46

5–41 %ROWTYPE Variable Represents Join Row........................................................................ 5-46

5–42 Inserting %ROWTYPE Record into Table (Wrong)............................................................ 5-47

5–43 Inserting %ROWTYPE Record into Table (Right)............................................................... 5-47

5–44 Assigning Record to Another Record of Same RECORD Type ........................................ 5-48

5–45 Assigning %ROWTYPE Record to RECORD Type Record............................................... 5-49

5–46 Assigning Nested Record to Another Record of Same RECORD Type........................... 5-49

5–47 SELECT INTO Assigns Values to Record Variable............................................................. 5-50

5–48 FETCH Assigns Values to Record that Function Returns.................................................. 5-51

5–49 UPDATE Statement Assigns Values to Record Variable ................................................... 5-52

5–50 Assigning NULL to Record Variable .................................................................................... 5-53

5–51 Initializing Table by Inserting Record of Default Values................................................... 5-54

5–52 Updating Rows with Record.................................................................................................. 5-55

6–1 Static SQL Statements................................................................................................................. 6-2

6–2 CURRVAL and NEXTVAL Pseudocolumns .......................................................................... 6-4

6–3 SQL%FOUND Implicit Cursor Attribute................................................................................ 6-6

6–4 SQL%ROWCOUNT Implicit Cursor Attribute ...................................................................... 6-7

6–5 Explicit Cursor Declaration and Definition ............................................................................ 6-9

剩余767页未读，继续阅读

mamadewen0

粉丝: 1
资源: 23

Oracle数据库PL/SQL语言参考指南

Oracle Database PL/SQL 11g Release 2 官方参考指南

Oracle数据库PLSQL语言教程

HAP框架PLSQL调用操作详解手册

Oracle Database PLSQL Language Reference 11g

Oracle PLSQL Language 袖珍手册第二版.zip

Oracle Database PLSQL Language Reference 11g Release 2 (11.2)

plsql

PLSQL

PLSQL作业 PLSQL作业 PLSQL作业 PLSQL作业 PLSQL作业 PLSQL作业

PLSQL.rar_plsql_plsql java

最新资源