Oracle 11g数据库PL/SQL编程指南

oracle

需积分: 9 44 浏览量更新于2024-07-18 收藏 15.17MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"Oracle 11g 手册是一份详细的数据库使用指南，专注于PL/SQL语言参考，它是Oracle数据库对SQL的程序性扩展。该手册由Sheila Moore为主编，多个贡献者协同完成，涵盖了从基础到高级的PL/SQL编程知识，适用于11g Release 2 (11.2)版本。" Oracle数据库是世界上最广泛使用的数据库管理系统之一，而PL/SQL是其核心组件，提供了一种强大的、过程化的编程语言，用于在Oracle环境中创建复杂的数据处理逻辑和应用程序。在Oracle 11g中，PL/SQL具有以下关键知识点： 1. **基本语法与数据类型**：PL/SQL支持多种数据类型，包括数值、字符串、日期、布尔值等。了解这些数据类型以及如何声明变量是学习PL/SQL的基础。 2. **块结构**：PL/SQL程序由声明部分、执行部分和异常处理部分组成，通常称为PL/SQL块。这些块可以是匿名的（一次性执行）或命名的（存储过程、函数和包）。 3. **控制流语句**：包括IF-THEN-ELSE、CASE、WHILE、FOR循环等，用于根据条件或迭代执行代码。 4. **游标**：游标允许程序逐行处理查询结果，是数据库操作中的重要概念，尤其是在处理复杂的数据处理任务时。 5. **异常处理**：通过使用EXCEPTION子句，PL/SQL能够捕获并处理运行时错误，增强程序的健壮性。 6. **包（Packages）**：包是将相关的变量、常量、过程和函数组合在一起的容器，提供了一种组织和重用代码的方法。 7. **存储过程和函数**：这两种是PL/SQL中的核心元素，用于执行数据库操作并返回结果。存储过程可以包含多个SQL语句和PL/SQL逻辑，而函数则通常用于返回单一值。 8. **触发器（Triggers）**：自动在特定数据库事件（如INSERT、UPDATE或DELETE）发生时执行的代码段，常用于实现业务规则和数据完整性。 9. **动态SQL**：允许在运行时构建和执行SQL语句，提供了更大的灵活性。 10. **游标表达式（Cursor Expressions）**：简化了对结果集的操作，提高了代码的简洁性和可读性。 11. **记录（Records）**：自定义数据结构，可以用来存储多列数据，类似其他语言中的结构体或对象。 12. **并发控制**：Oracle数据库提供了事务管理和锁定机制，确保在多用户环境中数据的一致性和完整性。 13. **性能优化**：通过索引、物化视图、绑定变量等方式提升查询性能，是数据库管理员和开发人员关注的重要领域。 Oracle 11g PL/SQL Language Reference手册详细阐述了这些概念，并提供了丰富的示例和最佳实践，帮助开发者深入理解和掌握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页未读，继续阅读

johnson_sj

粉丝: 0
资源: 1

Oracle 11g数据库PL/SQL编程指南

oracle 11g 完全参考手册

Oracle11g帮助文档

oracle11g官方文档（概念手册加初学者指南）

oracle11g最新手册教程

oracle11g rman手册

oracle11g中文手册chm

oracle 11g rac asm安装部署手册

oracle 11g adg实施手册(亲测,已成功部署多次)

《oracle database 11g 完全参考手册》

oracle database 11g完全参考手册电子版

oracle 11g rac+dg 非常牛逼的部署手册

oracle11g运维

oracle11g的7个压缩文件下载

oracle11gsql手册

oracle 存储过程 编写 手册 下载

学习oracle的工具书

查询Oracle 操作的详细信息方法

oracle数据库参考文献

oracle关于存储过程的官方文档

oraclexe112_win64.zip

最新资源

oracle 存储过程编写手册下载