Oracle 11g PL/SQL语言参考11.2版权威指南

需积分: 5 16 浏览量更新于2024-06-25 收藏 4.69MB PDF 举报

Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) 是Oracle公司官方发布的一份详细的文档，针对Oracle数据库11.2版本的PL/SQL语言进行了全面的参考指南。PL/SQL是Oracle数据库中的主要编程语言，它结合了SQL（Structured Query Language）的强大数据操作能力与过程化编程的灵活性，被广泛用于数据库管理、存储过程编写、触发器、函数和包等高级数据库操作。该文档由Sheila Moore和Eric Belden两位主要作者共同撰写，还有多位专家如D.Alpern、E.Belden等人参与贡献。文档版权属于Oracle及其关联公司，所有内容受1996年和2014年的版权法保护。使用此文档必须遵守相应的许可协议，且不得在未经许可的情况下进行复制、修改、分发等行为，除非法律另有规定或许可协议允许。 PL/SQL语言参考包括但不限于以下关键知识点： 1. **语言基础**：介绍PL/SQL语法结构，如变量声明、数据类型、控制流语句（如IF、LOOP、CASE）、异常处理以及基本的函数和过程定义。 2. **SQL集成**：如何在PL/SQL代码中嵌入SQL查询，以及如何处理查询结果集。 3. **存储过程和函数**：讲解如何创建和调用存储过程，以及编写过程参数、返回值和异常处理的详细规则。 4. **包和程序单元**：包的概念、组织方式以及包中可以包含的元素，如过程、函数、类型和常量。 5. **数据操纵**：涉及PL/SQL的数据操作，如集合操作、记录操作和游标，以及如何处理表和视图。 6. **并发控制**：讨论事务处理、锁机制和并发控制语句，确保数据一致性。 7. **性能优化**：提供性能调优建议，包括代码优化、索引使用和数据库设计的最佳实践。 8. **异常处理和错误报告**：详细解释如何捕获和处理运行时错误，以及如何编写健壮的错误处理程序。 9. **PL/SQL最佳实践**：包含一系列实用的编程技巧和模式，帮助开发者写出高效、可维护的代码。 10. **安全性**：介绍如何使用PL/SQL来实现数据访问控制、角色和权限管理，确保数据库安全。最后，文档强调未经许可的逆向工程、拆解或反编译软件的行为是严格禁止的，以保护Oracle的知识产权。对于任何希望深入学习和使用Oracle PL/SQL的开发人员来说，这份参考手册是一本不可或缺的权威参考资料。

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-34

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-37

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-40

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

xix

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

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

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

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

2–57 Static Constants ........................................................................................................................ 2-50

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

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

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

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页未读，继续阅读

weixin_40191861_zj

粉丝: 86
资源: 1万+

Oracle 11g PL/SQL语言参考11.2版权威指南

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

Oracle® Database SQL Language Reference 11g Release 2 (11.2)

Oracle Database Net Services Reference 11g Release 2 (11.2)

Oracle Database SQL Language Reference 11g Release 2 (11.2)-1634

Oracle Database SQL Language Quick Reference 11g Release 2 (11.2

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

Oracle Database PL/SQL 学习指南

Oracle Database 11g SQL Language Reference Manual

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

Oracle 11g PL/SQL 语言参考指南

最新资源