Oracle PL/SQL
大约 113 分钟
Oracle PL/SQL
Overview
官方的定义：
PL/SQL 是一种旨在对 SQL 进行扩展的过程语言，其语法涵盖了 SQL 语句。PL/SQL 程序单元由 Oracle 数据库服务器编译并存储在数据库中。在运行时，PL/SQL 和 SQL 都在同一服务器进程中运行，从而带来出色的效率。PL/SQL 自动继承了 Oracle 数据库的可靠性、安全性和可移植性。
简单来说，PL/SQL（Procedural Language/SQL）就是在 SQL 的基础上，增加了过程控制语句，就可以在数据库端进行复杂逻辑的数据处理。有很多业务系统将业务逻辑写在 PL/SQL 中，这样做可以避免数据在应用和数据库之间来回传递，提高性能。但是这样就将业务和数据库强绑定在一起了。
PL/SQL Engine
使用 PL/SQL 引擎来编译和运行 PL/SQL 单元，但是该引擎只处理 PL/SQL 块中的过程语句，SQL 语句还是会发送给数据库的 SQL 引擎处理。
Blocks
PL/SQL 以 Block（块）为基本单元，块包含声明和语句。
使用关键字 DECLARE，BEGIN，EXCEPTION 和 END 定义 PL/SQL 块，这几个关键字将块分为声明部分，执行部分和异常处理部分，只有执行部分是必要的。
语法：
<< label >> (optional)
DECLARE -- Declarative part (optional)
-- Declarations of local types, variables, & subprograms
BEGIN -- Executable part (required)
-- Statements (which can use items declared in declarative part)
[EXCEPTION -- Exception-handling part (optional)
-- Exception handlers for exceptions (errors) raised in executable part]
END;
例子：一个简单的 PL/SQL 程序
SQL> set serveroutput on
SQL> declare
2 x int := 20;
3 y int := 10;
4 z number;
5 begin
6 z := (x - y) / (x + y);
7 dbms_output.put_line(z);
8 exception
9 when zero_divide then
10 dbms_output.put_line('The divisor must not be zero!');
11 end;
12 /
.3333333333333333333333333333333333333333
PL/SQL procedure successfully completed.
Comments
PL/SQ L注释分为单行注释和多行注释。
Single-Line Comments
单行注释以 -- 开头。
例子：单行注释
DECLARE
howmany NUMBER;
num_tables NUMBER;
BEGIN
-- Begin processing
SELECT COUNT(*) INTO howmany
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
num_tables := howmany; -- Compute another value
END;
/
Multiline Comments
多行注释以/*开头，由*/结尾，可以跨多行。
例子：多行注释
DECLARE
some_condition BOOLEAN;
pi NUMBER := 3.1415926;
radius NUMBER := 15;
area NUMBER;
BEGIN
/* Perform some simple tests and assignments */
IF 2 + 2 = 4 THEN
some_condition := TRUE;
/* We expect this THEN to always be performed */
END IF;
/* This line computes the area of a circle using pi,
which is the ratio between the circumference and diameter.
After the area is computed, the result is displayed. */
area := pi * radius**2;
DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
END;
/
注意：
- 多行注释中可以包含单行注释
- 多行注释中不能包含多行注释
Declarations
在块的声明部分定义变量和常量，先定义后使用。
Variable Declarations
声明变量需要指定变量的名称和类型。
例子：声明变量
DECLARE
part_number NUMBER(6); -- SQL data type
part_name VARCHAR2(20); -- SQL data type
in_stock BOOLEAN; -- PL/SQL-only data type
part_price NUMBER(6,2); -- SQL data type
part_description VARCHAR2(50); -- SQL data type
BEGIN
NULL;
END;
/
Constant Declarations
使用 CONSTANT 关键字声明常量并指定初始值。
例子：声明常量
DECLARE
credit_limit CONSTANT REAL := 5000.00; -- SQL data type
max_days_in_year CONSTANT INTEGER := 366; -- SQL data type
urban_legend CONSTANT BOOLEAN := FALSE; -- PL/SQL-only data type
BEGIN
NULL;
END;
/
Initial Values
- 变量声明中，初始值可选，但如果指定了
NOT NULL约束，则必须指定 - 常量声明中，初始值必须
- 使用操作符
:=或者关键字DEFAULT指定初始值
例子：声明变量和常量并指定初始值
DECLARE
hours_worked INTEGER := 40;
employee_count INTEGER := 0;
pi CONSTANT REAL := 3.14159;
radius REAL := 1;
area REAL := (pi * radius**2);
BEGIN
NULL;
END;
/
例子：变量的默认初始值为 NULL
DECLARE
counter INTEGER; -- initial value is NULL by default
BEGIN
counter := counter + 1; -- NULL + 1 is still NULL
IF counter IS NULL THEN
DBMS_OUTPUT.PUT_LINE('counter is NULL.');
END IF;
END;
/
NOT NULL Constraint
可以为变量或常量指定非空约束，以阻止为其赋予空值。
例子：声明非空约束变量
DECLARE
acct_id INTEGER(4) NOT NULL := 9999;
a NATURALN := 9999;
b POSITIVEN := 9999;
c SIMPLE_INTEGER := 9999;
BEGIN
NULL;
END;
/
PL/SQL 将任何零长度字符串视为 NULL 值。这包括字符函数和布尔表达式返回的值。
例子：变量初始化为 NULL
DECLARE
null_string VARCHAR2(80) := TO_CHAR('');
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != '');
BEGIN
NULL;
END;
/
%TYPE Attribute
使用 %TYPE 属性声明与先前声明的变量或列具有相同数据类型。
语法：
referencing_item referenced_item%TYPE;
声明与字段数据类型相同的变量的语法：
variable_name table_name.column_name%TYPE;
例子：声明与字段数据类型相同的变量
DECLARE
surname employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/
例子：声明与其他变量类型相同的变量
DECLARE
name VARCHAR(25) NOT NULL := 'Smith';
surname name%TYPE := 'Jones';
BEGIN
DBMS_OUTPUT.PUT_LINE('name=' || name);
DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/
Assigning Values to Variables
声明变量后，可以使用以下三种方式为变量赋值：
- 使用赋值语句
- 使用
SELECT INTO或者FETCH语句 - 作为
OUT或者IN OUT参数传递给子程序
Assigning Values to Variables with the Assignment Statement
语法：
variable_name := expression;
例子：使用赋值语句为变量赋值
DECLARE -- You can assign initial values here
wages NUMBER;
hours_worked NUMBER := 40;
hourly_salary NUMBER := 22.50;
bonus NUMBER := 150;
country VARCHAR2(128);
counter NUMBER := 0;
done BOOLEAN;
valid_id BOOLEAN;
emp_rec1 employees%ROWTYPE;
emp_rec2 employees%ROWTYPE;
TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
comm_tab commissions;
BEGIN -- You can assign values here too
wages := (hours_worked * hourly_salary) + bonus;
country := 'France';
country := UPPER('Canada');
done := (counter > 100);
valid_id := TRUE;
emp_rec1.first_name := 'Antonio';
emp_rec1.last_name := 'Ortiz';
emp_rec1 := emp_rec2;
comm_tab(5) := 20000 * 0.15;
END;
/
Assigning Values to Variables with the SELECT INTO Statement
语法：
SELECT select_item [, select_item ]...
INTO variable_name [, variable_name ]...
FROM table_name;
例子：使用 SELECT INTO 语句为变量赋值
DECLARE
bonus NUMBER(8,2);
BEGIN
SELECT salary * 0.10 INTO bonus
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
END;
/
Assigning Values to Variables as Parameters of a Subprogram
作为 IN OUT 子程序参数为变量赋值
DECLARE
emp_salary NUMBER(8,2);
PROCEDURE adjust_salary (
emp NUMBER,
sal IN OUT NUMBER,
adjustment NUMBER
) IS
BEGIN
sal := sal + adjustment;
END;
BEGIN
SELECT salary INTO emp_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE
('Before invoking procedure, emp_salary: ' || emp_salary);
adjust_salary (100, emp_salary, 1000);
DBMS_OUTPUT.PUT_LINE
('After invoking procedure, emp_salary: ' || emp_salary);
END;
/
Result:
Before invoking procedure, emp_salary: 24000
After invoking procedure, emp_salary: 25000
Assigning Values to BOOLEAN Variables
只能为 BOOLEAN 变量赋值 TRUE，FALSE 和 NULL。
例子：为 BOOLEAN 变量赋值
DECLARE
done BOOLEAN; -- Initial value is NULL by default
counter NUMBER := 0;
BEGIN
done := FALSE; -- Assign literal value
WHILE done != TRUE -- Compare to literal value
LOOP
counter := counter + 1;
done := (counter > 500); -- Assign value of BOOLEAN expression
END LOOP;
END;
/
Data Types
每个 PL/SQL 的常量，变量，参数以及函数返回值都需要指定数据类型。
PL/SQL 数据类型有：
标量数据类型（scalar data types）
SQL Data Types
BOOLEAN
PLS_INTEGER
BINARY_INTEGER
REF CURSOR
复合数据类型（composite data types）
- COLLECTION
- RECORD
SQL Data Types
PL/SQL 数据类型包含了 SQL 数据类型，但是对于数据类型最大值的设定存在差异。
| Data Type | Maximum Size in PL/SQL | Maximum Size in SQL |
|---|---|---|
CHAR | 32,767 bytes | 2,000 bytes |
NCHAR | 32,767 bytes | 2,000 bytes |
RAW | 32,767 bytes | 2,000 bytes |
VARCHAR2 | 32,767 bytes | 4,000 bytes |
NVARCHAR2 | 32,767 bytes | 4,000 bytes |
LONG | 32,760 bytes | 2 gigabytes (GB) - 1 |
LONG RAW | 32,760 bytes | 2 GB |
BLOB | 128 terabytes (TB) | (4 GB - 1) * database_block_size |
CLOB | 128 TB | (4 GB - 1) * database_block_size |
NCLOB | 128 TB | (4 GB - 1) * database_block_size |
BOOLEAN
BOOLEAN 数据类型存储逻辑值，值只能为 TRUE，FALSE 和 NULL
定义 BOOLEAN 变量的语法：
variable_name BOOLEAN
不能将 BOOLEAN 传递给 DBMS_OUTPUT.PUT 或者 DBMS_OUTPUT.PUTLINE 。打印 BOOLEAN 值需要使用 IF 或者 CASE 语句将其转换为字符值。
例子：打印 BOOLEAN 值
CREATE PROCEDURE print_boolean (b BOOLEAN) AUTHID DEFINER
AS
BEGIN
DBMS_OUTPUT.put_line (
CASE
WHEN b IS NULL THEN 'Unknown'
WHEN b THEN 'Yes'
WHEN NOT b THEN 'No'
END
);
END;
/
BEGIN
print_boolean(TRUE);
print_boolean(FALSE);
print_boolean(NULL);
END;
/
结果：
Yes
No
Unknown
PLS_INTEGER
PLS_INTEGER 和 BINARY_INTEGER 是等价的。
PLS_INTEGER 可表示的数据范围：-2,147,483,648 至 2,147,483,647
相比 NUMBER 数据类型，PLS_INTEGER 的优势：
- 更少存储空间
- 更快运算速度
Preventing PLS_INTEGER Overflow
两个 PLS_INTEGER 类型变量进行运算，如果超出 PLS_INTEGER 数据范围会抛出异常。
例子：PLS_INTEGER 溢出异常
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 PLS_INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 6
例子：使用 INTEGER 数据类型解决溢出异常
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
PL/SQL procedure successfully completed.
Predefined PLS_INTEGER Subtypes
| Data Type | Data Description |
|---|---|
NATURAL | Nonnegative PLS_INTEGER value |
NATURALN | Nonnegative PLS_INTEGER value with NOT NULL constraint |
POSITIVE | Positive PLS_INTEGER value |
POSITIVEN | Positive PLS_INTEGER value with NOT NULL constraint |
SIGNTYPE | PLS_INTEGER value -1, 0, or 1 (useful for programming tri-state logic) |
SIMPLE_INTEGER | PLS_INTEGER value with NOT NULL constraint. |
Collection
集合（ collect ）包含相同数据类型的元素，通过唯一的下标来访问集合变量（collection variable ）元素。
PL/SQL 有三种集合类型：
- associative array
- VARRAY
- nested tabl
| Collection Type | Number of Elements | Index Type | Dense or Sparse | Uninitialized Status | Where Defined | Can Be ADT Attribute Data Type |
|---|---|---|---|---|---|---|
| Associative array (or index-by table) | Unspecified | String or PLS_INTEGER | Either | Empty | In PL/SQL block or package | No |
VARRAY (variable-size array) | Specified | Integer | Always dense | Null | In PL/SQL block or package or at schema level | Only if defined at schema level |
| Nested table | Unspecified | Integer | Starts dense, can become sparse | Null | In PL/SQL block or package or at schema level | Only if defined at schema level |
Associative Arrays
associative array 是一组键值对，键可以是有序的整数或字符串。
例子：Associative Array Indexed by String
DECLARE
-- Associative array indexed by string:
TYPE population IS TABLE OF NUMBER -- Associative array type
INDEX BY VARCHAR2(64); -- indexed by string
city_population population; -- Associative array variable
i VARCHAR2(64); -- Scalar variable
BEGIN
-- Add elements (key-value pairs) to associative array:
city_population('Smallville') := 2000;
city_population('Midland') := 750000;
city_population('Megalopolis') := 1000000;
-- Change value associated with key 'Smallville':
city_population('Smallville') := 2001;
-- Print associative array:
i := city_population.FIRST; -- Get first element of array
WHILE i IS NOT NULL LOOP
DBMS_Output.PUT_LINE
('Population of ' || i || ' is ' || city_population(i));
i := city_population.NEXT(i); -- Get next element of array
END LOOP;
END;
/
输出：
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001
Varrays (Variable-Size Arrays)
varray 是一个元素数量可变的数组，通过下标访问，下标最小值为1。数据量较大的时候慎用。
例子：Varray (Variable-Size Array)
DECLARE
TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); -- VARRAY type
-- varray variable initialized with constructor:
team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('2001 Team:');
team(3) := 'Pierre'; -- Change values of two elements
team(4) := 'Yvonne';
print_team('2005 Team:');
-- Invoke constructor to assign new values to varray variable:
team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
print_team('2009 Team:');
END;
/
输出：
2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
---
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
---
Nested Tables
nested table 是一种列类型，通过下标访问，下标最小值为1。
例子：Nested Table of Local Type
DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
-- nested table variable initialized with constructor:
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE print_names (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_names('Initial Values:');
names(3) := 'P Perez'; -- Change value of one element
print_names('Current Values:');
names := Roster('A Jansen', 'B Gupta'); -- Change entire table
print_names('Current Values:');
END;
/
输出：
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta
例子：Nested Table of Standalone Type
CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
i NUMBER;
BEGIN
i := nt.FIRST;
IF i IS NULL THEN
DBMS_OUTPUT.PUT_LINE('nt is empty');
ELSE
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
i := nt.NEXT(i);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END print_nt;
/
DECLARE
nt nt_type := nt_type(); -- nested table variable initialized to empty
BEGIN
print_nt(nt);
nt := nt_type(90, 9, 29, 58);
print_nt(nt);
END;
/
输出：
nt is empty
---
nt.(1) = 90
nt.(2) = 9
nt.(3) = 29
nt.(4) = 58
---
Nested Tables 和 Arrays 的区别：
- array 声明时指定了元素数量，但是 nested table 不需要指定
- array 始终是密集的，但是 nested table 最初是密集的，如果删除了元素，则会变得稀疏
Collection Methods
| Method | Type | Description |
|---|---|---|
DELETE | Procedure | Deletes elements from collection. |
TRIM | Procedure | Deletes elements from end of varray or nested table. |
EXTEND | Procedure | Adds elements to end of varray or nested table. |
EXISTS | Function | Returns TRUE if and only if specified element of varray or nested table exists. |
FIRST | Function | Returns first index in collection. |
LAST | Function | Returns last index in collection. |
COUNT | Function | Returns number of elements in collection. |
LIMIT | Function | Returns maximum number of elements that collection can have. |
PRIOR | Function | Returns index that precedes specified index. |
NEXT | Function | Returns index that succeeds specified index. |
例子：DELETE Method with Nested Table
DECLARE
nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
print_nt(nt);
nt.DELETE(2); -- Delete second element
print_nt(nt);
nt(2) := 2222; -- Restore second element
print_nt(nt);
nt.DELETE(2, 4); -- Delete range of elements
print_nt(nt);
nt(3) := 3333; -- Restore third element
print_nt(nt);
nt.DELETE; -- Delete all elements
print_nt(nt);
END;
/
输出：
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 2222
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 3333
nt.(5) = 55
nt.(6) = 66
---
nt is empty
---
例子：DELETE Method with Associative Array Indexed by String
DECLARE
TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
aa_str aa_type_str;
PROCEDURE print_aa_str IS
i VARCHAR2(10);
BEGIN
i := aa_str.FIRST;
IF i IS NULL THEN
DBMS_OUTPUT.PUT_LINE('aa_str is empty');
ELSE
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT('aa_str.(' || i || ') = '); print(aa_str(i));
i := aa_str.NEXT(i);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END print_aa_str;
BEGIN
aa_str('M') := 13;
aa_str('Z') := 26;
aa_str('C') := 3;
print_aa_str;
aa_str.DELETE; -- Delete all elements
print_aa_str;
aa_str('M') := 13; -- Replace deleted element with same value
aa_str('Z') := 260; -- Replace deleted element with new value
aa_str('C') := 30; -- Replace deleted element with new value
aa_str('W') := 23; -- Add new element
aa_str('J') := 10; -- Add new element
aa_str('N') := 14; -- Add new element
aa_str('P') := 16; -- Add new element
aa_str('W') := 23; -- Add new element
aa_str('J') := 10; -- Add new element
print_aa_str;
aa_str.DELETE('C'); -- Delete one element
print_aa_str;
aa_str.DELETE('N','W'); -- Delete range of elements
print_aa_str;
aa_str.DELETE('Z','M'); -- Does nothing
print_aa_str;
END;
/
输出：
aa_str.(C) = 3
aa_str.(M) = 13
aa_str.(Z) = 26
---
aa_str is empty
---
aa_str.(C) = 30
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---
例子：TRIM Method with Nested Table
DECLARE
nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
print_nt(nt);
nt.TRIM; -- Trim last element
print_nt(nt);
nt.DELETE(4); -- Delete fourth element
print_nt(nt);
nt.TRIM(2); -- Trim last two elements
print_nt(nt);
END;
/
输出：
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---
例子：EXTEND Method with Nested Table
DECLARE
nt nt_type := nt_type(11, 22, 33);
BEGIN
print_nt(nt);
nt.EXTEND(2,1); -- Append two copies of first element
print_nt(nt);
nt.DELETE(5); -- Delete fifth element
print_nt(nt);
nt.EXTEND; -- Append one null element
print_nt(nt);
END;
/
输出：
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(5) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(6) = NULL
---
例子：EXISTS Method with Nested Table
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
BEGIN
n.DELETE(2); -- Delete second element
FOR i IN 1..6 LOOP
IF n.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
END IF;
END LOOP;
END;
/
输出：
n(1) = 1
n(2) does not exist
n(3) = 5
n(4) = 7
n(5) does not exist
n(6) does not exist
例子：FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER
DECLARE
TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa_int aa_type_int;
PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
END print_first_and_last;
BEGIN
aa_int(1) := 3;
aa_int(2) := 6;
aa_int(3) := 9;
aa_int(4) := 12;
DBMS_OUTPUT.PUT_LINE('Before deletions:');
print_first_and_last;
aa_int.DELETE(1);
aa_int.DELETE(4);
DBMS_OUTPUT.PUT_LINE('After deletions:');
print_first_and_last;
END;
/
输出：
Before deletions:
FIRST = 1
LAST = 4
After deletions:
FIRST = 2
LAST = 3
例子：FIRST and LAST Values for Associative Array Indexed by String
DECLARE
TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
aa_str aa_type_str;
PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST);
END print_first_and_last;
BEGIN
aa_str('Z') := 26;
aa_str('A') := 1;
aa_str('K') := 11;
aa_str('R') := 18;
DBMS_OUTPUT.PUT_LINE('Before deletions:');
print_first_and_last;
aa_str.DELETE('A');
aa_str.DELETE('Z');
DBMS_OUTPUT.PUT_LINE('After deletions:');
print_first_and_last;
END;
/
输出：
Before deletions:
FIRST = A
LAST = Z
After deletions:
FIRST = K
LAST = R
例子：Printing Varray with FIRST and LAST in FOR LOOP
DECLARE
TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
team team_type;
PROCEDURE print_team (heading VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('Team Status:');
team := team_type(); -- Team is funded, but nobody is on it.
print_team('Team Status:');
team := team_type('John', 'Mary'); -- Put 2 members on team.
print_team('Initial Team:');
team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Change team.
print_team('New Team:');
END;
/
输出：
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. John
2. Mary
---
New Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---
例子：Printing Nested Table with FIRST and LAST in FOR LOOP
DECLARE
TYPE team_type IS TABLE OF VARCHAR2(15);
team team_type;
PROCEDURE print_team (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF team IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF team.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Has no members');
ELSE
FOR i IN team.FIRST..team.LAST LOOP
DBMS_OUTPUT.PUT(i || '. ');
IF team.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(team(i));
ELSE
DBMS_OUTPUT.PUT_LINE('(to be hired)');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_team('Team Status:');
team := team_type(); -- Team is funded, but nobody is on it.
print_team('Team Status:');
team := team_type('Arun', 'Amitha', 'Allan', 'Mae'); -- Add members.
print_team('Initial Team:');
team.DELETE(2,3); -- Remove 2nd and 3rd members.
print_team('Current Team:');
END;
/
输出：
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---
Current Team:
1. Arun
2. (to be hired)
3. (to be hired)
4. Mae
---
例子：COUNT and LAST Values for Varray
DECLARE
TYPE NumList IS VARRAY(10) OF INTEGER;
n NumList := NumList(1,3,5,7);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
n.EXTEND(3);
print_count_and_last;
n.TRIM(5);
print_count_and_last;
END;
/
输出：
n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2
例子：COUNT and LAST Values for Nested Table
DECLARE
TYPE NumList IS TABLE OF INTEGER;
n NumList := NumList(1,3,5,7);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
n.DELETE(3); -- Delete third element
print_count_and_last;
n.EXTEND(2); -- Add two null elements to end
print_count_and_last;
FOR i IN 1..8 LOOP
IF n.EXISTS(i) THEN
IF n(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') = NULL');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
END IF;
END LOOP;
END;
/
输出：
n.COUNT = 4, n.LAST = 4
n.COUNT = 3, n.LAST = 4
n.COUNT = 5, n.LAST = 6
n(1) = 1
n(2) = 3
n(3) does not exist
n(4) = 7
n(5) = NULL
n(6) = NULL
n(7) does not exist
n(8) does not exist
例子：LIMIT and COUNT Values for Different Collection Types
DECLARE
TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa aa_type; -- associative array
TYPE va_type IS VARRAY(4) OF INTEGER;
va va_type := va_type(2,4); -- varray
TYPE nt_type IS TABLE OF INTEGER;
nt nt_type := nt_type(1,3,5); -- nested table
BEGIN
aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12;
DBMS_OUTPUT.PUT('aa.COUNT = '); print(aa.COUNT);
DBMS_OUTPUT.PUT('aa.LIMIT = '); print(aa.LIMIT);
DBMS_OUTPUT.PUT('va.COUNT = '); print(va.COUNT);
DBMS_OUTPUT.PUT('va.LIMIT = '); print(va.LIMIT);
DBMS_OUTPUT.PUT('nt.COUNT = '); print(nt.COUNT);
DBMS_OUTPUT.PUT('nt.LIMIT = '); print(nt.LIMIT);
END;
/
输出：
aa.COUNT = 4
aa.LIMIT = NULL
va.COUNT = 2
va.LIMIT = 4
nt.COUNT = 3
nt.LIMIT = NULL
例子：PRIOR and NEXT Methods
DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);
BEGIN
nt.DELETE(4);
DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.');
FOR i IN 1..7 LOOP
DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i));
DBMS_OUTPUT.PUT('nt.NEXT(' || i || ') = '); print(nt.NEXT(i));
END LOOP;
END;
/
输出：
nt(4) was deleted.
nt.PRIOR(1) = NULL
nt.NEXT(1) = 2
nt.PRIOR(2) = 1
nt.NEXT(2) = 3
nt.PRIOR(3) = 2
nt.NEXT(3) = 5
nt.PRIOR(4) = 3
nt.NEXT(4) = 5
nt.PRIOR(5) = 3
nt.NEXT(5) = 6
nt.PRIOR(6) = 5
nt.NEXT(6) = NULL
nt.PRIOR(7) = 6
nt.NEXT(7) = NULL
例子：Printing Elements of Sparse Nested Table
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
idx INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('First to last:');
idx := n.FIRST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT('n(' || idx || ') = ');
print(n(idx));
idx := n.NEXT(idx);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------');
DBMS_OUTPUT.PUT_LINE('Last to first:');
idx := n.LAST;
WHILE idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT('n(' || idx || ') = ');
print(n(idx));
idx := n.PRIOR(idx);
END LOOP;
END;
/
输出：
First to last:
n(1) = 1
n(2) = 2
n(3) = NULL
n(4) = NULL
n(5) = 5
n(6) = NULL
n(7) = 7
n(8) = 8
n(9) = 9
n(10) = NULL
--------------
Last to first:
n(10) = NULL
n(9) = 9
n(8) = 8
n(7) = 7
n(6) = NULL
n(5) = 5
n(4) = NULL
n(3) = NULL
n(2) = 2
n(1) = 1
Record
记录（ record ）包含不同数据类型的字段，创建记录变量（ record variable）的方法：
- 定义一个 RECORD Types 并为变量声明为该类型
- 使用 %TYPE
- 使用 %ROWTYPE
RECORD Types
例子：定义 RECORD Types 并声明变量
DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id NUMBER(4) NOT NULL := 10,
dept_name VARCHAR2(30) NOT NULL := 'Administration',
mgr_id NUMBER(6) := 200,
loc_id NUMBER(4)
);
dept_rec DeptRecTyp;
dept_rec_2 dept_rec%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('dept_rec:');
DBMS_OUTPUT.PUT_LINE('---------');
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.dept_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.mgr_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.loc_id);
DBMS_OUTPUT.PUT_LINE('-----------');
DBMS_OUTPUT.PUT_LINE('dept_rec_2:');
DBMS_OUTPUT.PUT_LINE('-----------');
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec_2.dept_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec_2.dept_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec_2.mgr_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec_2.loc_id);
END;
/
输出：
dept_rec:
---------
dept_id: 10
dept_name: Administration
mgr_id: 200
loc_id:
-----------
dept_rec_2:
-----------
dept_id: 10
dept_name: Administration
mgr_id: 200
loc_id:
PL/SQL procedure successfully completed.
例子：定义 RECORD Types，其中包含一个 RECORD Types
DECLARE
TYPE name_rec IS RECORD (
first employees.first_name%TYPE,
last employees.last_name%TYPE
);
TYPE contact IS RECORD (
name name_rec, -- nested record
phone employees.phone_number%TYPE
);
friend contact;
BEGIN
friend.name.first := 'John';
friend.name.last := 'Smith';
friend.phone := '1-650-555-1234';
DBMS_OUTPUT.PUT_LINE (
friend.name.first || ' ' ||
friend.name.last || ', ' ||
friend.phone
);
END;
/
输出：
John Smith, 1-650-555-1234
%ROWTYPE Attribute
使用 %ROWTYPE 属性声明记录变量，表示表或视图的完整或部分行。对于整行或部分行的每一列，记录变量都有一个具有相同名称和数据类型的字段。如果行的结构发生更改，则记录变量的结构也会相应更改。
声明完整行记录变量的语法：
variable_name table_or_view_name%ROWTYPE;
例子：声明完整行记录变量
DECLARE
dept_rec departments%ROWTYPE;
BEGIN
-- Assign values to fields:
dept_rec.department_id := 10;
dept_rec.department_name := 'Administration';
dept_rec.manager_id := 200;
dept_rec.location_id := 1700;
-- Print fields:
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.department_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.manager_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.location_id);
END;
/
输出：
dept_id: 10
dept_name: Administration
mgr_id: 200
loc_id: 1700
声明部分行记录变量需要使用游标，语法：
variable_name cursor%ROWTYPE;
例子：声明部分行记录变量
DECLARE
CURSOR c IS
SELECT first_name, last_name, phone_number
FROM employees;
friend c%ROWTYPE;
BEGIN
friend.first_name := 'John';
friend.last_name := 'Smith';
friend.phone_number := '1-650-555-1234';
DBMS_OUTPUT.PUT_LINE (
friend.first_name || ' ' ||
friend.last_name || ', ' ||
friend.phone_number
);
END;
/
输出：
John Smith, 1-650-555-1234
Assigning Values to Record Variables
Assigning One Record Variable to Another
为一个记录变量赋予另一个记录变量的值的情况有：
- 这两个变量有相同的 RECORD Types
- 目标变量声明为 RECORD Types，原变量声明为 %ROWTYPE，两个变量的字段数量、顺序和类型都相同
例子：相同 RECORD Types 的赋值
DECLARE
TYPE name_rec IS RECORD (
first employees.first_name%TYPE DEFAULT 'John',
last employees.last_name%TYPE DEFAULT 'Doe'
);
name1 name_rec;
name2 name_rec;
BEGIN
name1.first := 'Jane'; name1.last := 'Smith';
DBMS_OUTPUT.PUT_LINE('name1: ' || name1.first || ' ' || name1.last);
name2 := name1;
DBMS_OUTPUT.PUT_LINE('name2: ' || name2.first || ' ' || name2.last);
END;
/
输出：
name1: Jane Smith
name2: Jane Smith
例子：为 RECORD Types 赋值 %ROWTYPE
DECLARE
TYPE name_rec IS RECORD (
first employees.first_name%TYPE DEFAULT 'John',
last employees.last_name%TYPE DEFAULT 'Doe'
);
CURSOR c IS
SELECT first_name, last_name
FROM employees;
target name_rec;
source c%ROWTYPE;
BEGIN
source.first_name := 'Jane'; source.last_name := 'Smith';
DBMS_OUTPUT.PUT_LINE (
'source: ' || source.first_name || ' ' || source.last_name
);
target := source;
DBMS_OUTPUT.PUT_LINE (
'target: ' || target.first || ' ' || target.last
);
END;
/
输出：
source: Jane Smith
target: Jane Smith
Assigning Full or Partial Rows to Record Variables
（1）SELECT INTO Statement for Assigning Row to Record Variable
语法：
SELECT select_list INTO record_variable_name FROM table_or_view_name;
例子：使用 SELECT INTO 赋值
DECLARE
TYPE RecordTyp IS RECORD (
last employees.last_name%TYPE,
id employees.employee_id%TYPE
);
rec1 RecordTyp;
BEGIN
SELECT last_name, employee_id INTO rec1
FROM employees
WHERE job_id = 'AD_PRES';
DBMS_OUTPUT.PUT_LINE ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/
输出：
Employee #100 = King
（2）FETCH Statement for Assigning Row to Record Variable
语法：
FETCH cursor INTO record_variable_name;
例子：使用 FETCH 赋值
DECLARE
TYPE EmpRecTyp IS RECORD (
emp_id employees.employee_id%TYPE,
salary employees.salary%TYPE
);
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC;
highest_paid_emp EmpRecTyp;
next_highest_paid_emp EmpRecTyp;
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
emp_rec EmpRecTyp;
BEGIN
OPEN desc_salary;
FOR i IN 1..n LOOP
FETCH desc_salary INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
BEGIN
highest_paid_emp := nth_highest_salary(1);
next_highest_paid_emp := nth_highest_salary(2);
DBMS_OUTPUT.PUT_LINE(
'Highest Paid: #' ||
highest_paid_emp.emp_id || ', $' ||
highest_paid_emp.salary
);
DBMS_OUTPUT.PUT_LINE(
'Next Highest Paid: #' ||
next_highest_paid_emp.emp_id || ', $' ||
next_highest_paid_emp.salary
);
END;
/
输出：
Highest Paid: #100, $26460
Next Highest Paid: #101, $18742.5
（3）SQL Statements that Return Rows in PL/SQL Record Variables
使用 INSERT，UPDATE 和 DELETE 中的 RETURNING INTO 子句将受影响的行赋予记录变量
例子：使用 UPDATE 语句
DECLARE
TYPE EmpRec IS RECORD (
last_name employees.last_name%TYPE,
salary employees.salary%TYPE
);
emp_info EmpRec;
old_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO old_salary
FROM employees
WHERE employee_id = 100;
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 100
RETURNING last_name, salary INTO emp_info;
DBMS_OUTPUT.PUT_LINE (
'Salary of ' || emp_info.last_name || ' raised from ' ||
old_salary || ' to ' || emp_info.salary
);
END;
/
输出：
Salary of King raised from 26460 to 29106
Assigning NULL to Record Variable
将 NULL 赋予记录变量，则记录变量中的所有字段都为 NULL，包括其中的记录变量。
例子：为记录变量赋予 NULL
DECLARE
TYPE age_rec IS RECORD (
years INTEGER DEFAULT 35,
months INTEGER DEFAULT 6
);
TYPE name_rec IS RECORD (
first employees.first_name%TYPE DEFAULT 'John',
last employees.last_name%TYPE DEFAULT 'Doe',
age age_rec
);
name name_rec;
PROCEDURE print_name AS
BEGIN
DBMS_OUTPUT.PUT(NVL(name.first, 'NULL') || ' ');
DBMS_OUTPUT.PUT(NVL(name.last, 'NULL') || ', ');
DBMS_OUTPUT.PUT(NVL(TO_CHAR(name.age.years), 'NULL') || ' yrs ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(name.age.months), 'NULL') || ' mos');
END;
BEGIN
print_name;
name := NULL;
print_name;
END;
/
输出：
John Doe, 35 yrs 6 mos
NULL NULL, NULL yrs NULL mos
Inserting Records into Tables
例子：使用记录变量初始化表
DROP TABLE schedule;
CREATE TABLE schedule (
week NUMBER,
Mon VARCHAR2(10),
Tue VARCHAR2(10),
Wed VARCHAR2(10),
Thu VARCHAR2(10),
Fri VARCHAR2(10),
Sat VARCHAR2(10),
Sun VARCHAR2(10)
);
DECLARE
default_week schedule%ROWTYPE;
i NUMBER;
BEGIN
default_week.Mon := '0800-1700';
default_week.Tue := '0800-1700';
default_week.Wed := '0800-1700';
default_week.Thu := '0800-1700';
default_week.Fri := '0800-1700';
default_week.Sat := 'Day Off';
default_week.Sun := 'Day Off';
FOR i IN 1..6 LOOP
default_week.week := i;
INSERT INTO schedule VALUES default_week;
END LOOP;
END;
/
COLUMN week FORMAT 99
COLUMN Mon FORMAT A9
COLUMN Tue FORMAT A9
COLUMN Wed FORMAT A9
COLUMN Thu FORMAT A9
COLUMN Fri FORMAT A9
COLUMN Sat FORMAT A9
COLUMN Sun FORMAT A9
SELECT * FROM schedule;
WEEK MON TUE WED THU FRI SAT SUN
---- --------- --------- --------- --------- --------- --------- ---------
1 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
2 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
3 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
Updating Rows with Records
例子：使用记录变量更新数据
DECLARE
default_week schedule%ROWTYPE;
BEGIN
default_week.Mon := 'Day Off';
default_week.Tue := '0900-1800';
default_week.Wed := '0900-1800';
default_week.Thu := '0900-1800';
default_week.Fri := '0900-1800';
default_week.Sat := '0900-1800';
default_week.Sun := 'Day Off';
FOR i IN 1..3 LOOP
default_week.week := i;
UPDATE schedule
SET ROW = default_week
WHERE week = i;
END LOOP;
END;
/
SELECT * FROM schedule;
WEEK MON TUE WED THU FRI SAT SUN
---- --------- --------- --------- --------- --------- --------- ---------
1 Day Off 0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
2 Day Off 0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
3 Day Off 0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
Restrictions on Record Inserts and Updates
These restrictions apply to record inserts and updates:
Record variables are allowed only in these places:
- On the right side of the
SETclause in anUPDATEstatement- In the
VALUESclause of anINSERTstatement- In the
INTOsubclause of aRETURNINGclauseRecord variables are not allowed in a
SELECTlist,WHEREclause,GROUPBYclause, orORDERBYclause.The keyword
ROWis allowed only on the left side of aSETclause. Also, you cannot useROWwith a subquery.In an
UPDATEstatement, only oneSETclause is allowed ifROWis used.If the
VALUESclause of anINSERTstatement contains a record variable, no other variable or value is allowed in the clause.If the
INTOsubclause of aRETURNINGclause contains a record variable, no other variable or value is allowed in the subclause.These are not supported:
- Nested
RECORDtypes- Functions that return a
RECORDtype- Record inserts and updates using the
EXECUTEIMMEDIATEstatement.
Control Statements
PL/SQL 有三类控制语句：
- Conditional selection statements
- Loop statements
- Sequential control statements
Conditional Selection Statements
使用 IF 或 CASE，根据条件执行不同的语句。
IF ：
IFTHENIFTHENELSEIFTHENELSIF
CASE：
- Simple
- Searched
IF THEN Statement
语法：
IF condition THEN
statements
END IF;
如果 condition 为 true，则执行 statements。
例子：IF THEN
DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER
)
IS
bonus NUMBER := 0;
updated VARCHAR2(3) := 'No';
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
updated := 'Yes';
END IF;
DBMS_OUTPUT.PUT_LINE (
'Table updated? ' || updated || ', ' ||
'bonus = ' || bonus || '.'
);
END p;
BEGIN
p(10100, 10000, 120);
p(10500, 10000, 121);
END;
/
输出：
Table updated? No, bonus = 0.
Table updated? Yes, bonus = 125.
IF THEN ELSE Statement
语法：
IF condition THEN
statements
ELSE
else_statements
END IF;
如果 condition 为 true，执行 statements，否则执行 else_statements。
例子：IF THEN ELSE
DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER
)
IS
bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
bonus := 50;
END IF;
DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
END p;
BEGIN
p(10100, 10000, 120);
p(10500, 10000, 121);
END;
/
输出：
bonus = 50
bonus = 125
例子：Nested IF THEN ELSE
DECLARE
PROCEDURE p (
sales NUMBER,
quota NUMBER,
emp_id NUMBER
)
IS
bonus NUMBER := 0;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
IF sales > quota THEN
bonus := 50;
ELSE
bonus := 0;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
UPDATE employees
SET salary = salary + bonus
WHERE employee_id = emp_id;
END p;
BEGIN
p(10100, 10000, 120);
p(10500, 10000, 121);
p(9500, 10000, 122);
END;
/
输出：
bonus = 50
bonus = 125
bonus = 0
IF THEN ELSIF Statement
语法：
IF condition_1 THEN
statements_1
ELSIF condition_2 THEN
statements_2
[ ELSIF condition_3 THEN
statements_3
]...
[ ELSE
else_statements
]
END IF;
如果 condition_1 为 true，执行 statements_1 并退出，否则继续判断，如果都不满足，则执行 else_statements。
例子：IF THEN ELSIF
DECLARE
PROCEDURE p (sales NUMBER)
IS
bonus NUMBER := 0;
BEGIN
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
DBMS_OUTPUT.PUT_LINE (
'Sales = ' || sales || ', bonus = ' || bonus || '.'
);
END p;
BEGIN
p(55000);
p(40000);
p(30000);
END;
/
输出：
Sales = 55000, bonus = 1500.
Sales = 40000, bonus = 500.
Sales = 30000, bonus = 100.
Simple CASE Statement
语法：
CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
else_statements ]
END CASE;]
selector 为表达式（一般为一个变量），如果 selector_value_1 等于 selector，则执行 statements_1 并退出，否则继续判断，如果都不满足，则执行 else_statements。
例子：Simple CASE
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
输出：
Very Good
Searched CASE Statement
语法：
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
else_statements ]
END CASE;]
如果 condition_1 为 true，执行 statements_1 并退出，否则继续判断，如果都不满足，则执行 else_statements。
例子：Searched CASE
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
/
输出：
Very Good
例子：EXCEPTION Instead of ELSE Clause in CASE Statement
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
END;
/
输出：
Very Good
LOOP Statements
循环语句：
- Basic
LOOP FORLOOP- Cursor
FORLOOP WHILELOOP
退出整个循环：
EXITEXITWHEN
退出当前循环，继续下一循环：
CONTINUECONTINUEWHEN
Basic LOOP Statement
语法：
[ label ] LOOP
statements
END LOOP [ label ];
EXIT Statement
退出整个循环。
例子：Basic LOOP Statement with EXIT Statement
DECLARE
x NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
IF x > 3 THEN
EXIT;
END IF;
END LOOP;
-- After EXIT, control resumes here
DBMS_OUTPUT.PUT_LINE(' After loop: x = ' || TO_CHAR(x));
END;
/
输出：
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop: x = 3
After loop: x = 4
EXIT WHEN Statement
根据条件判断是否退出整个循环。
例子：Basic LOOP Statement with EXIT WHEN Statement
DECLARE
x NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Inside loop: x = ' || TO_CHAR(x));
x := x + 1; -- prevents infinite loop
EXIT WHEN x > 3;
END LOOP;
-- After EXIT statement, control resumes here
DBMS_OUTPUT.PUT_LINE('After loop: x = ' || TO_CHAR(x));
END;
/
输出：
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop: x = 3
After loop: x = 4
例子：Nested, Labeled Basic LOOP Statements with EXIT WHEN Statements
DECLARE
s PLS_INTEGER := 0;
i PLS_INTEGER := 0;
j PLS_INTEGER;
BEGIN
<<outer_loop>>
LOOP
i := i + 1;
j := 0;
<<inner_loop>>
LOOP
j := j + 1;
s := s + i * j; -- Sum several products
EXIT inner_loop WHEN (j > 5);
EXIT outer_loop WHEN ((i * j) > 15);
END LOOP inner_loop;
END LOOP outer_loop;
DBMS_OUTPUT.PUT_LINE
('The sum of products equals: ' || TO_CHAR(s));
END;
/
输出：
The sum of products equals: 166
例子：Nested, Unabeled Basic LOOP Statements with EXIT WHEN Statements
DECLARE
i PLS_INTEGER := 0;
j PLS_INTEGER := 0;
BEGIN
LOOP
i := i + 1;
DBMS_OUTPUT.PUT_LINE ('i = ' || i);
LOOP
j := j + 1;
DBMS_OUTPUT.PUT_LINE ('j = ' || j);
EXIT WHEN (j > 3);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Exited inner loop');
EXIT WHEN (i > 2);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Exited outer loop');
END;
/
输出：
i = 1
j = 1
j = 2
j = 3
j = 4
Exited inner loop
i = 2
j = 5
Exited inner loop
i = 3
j = 6
Exited inner loop
Exited outer loop
CONTINUE Statement
退出当前循环，继续下一循环。
例子：CONTINUE Statement in Basic LOOP Statement
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- After CONTINUE statement, control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
IF x < 3 THEN
CONTINUE;
END IF;
DBMS_OUTPUT.PUT_LINE
('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/
输出：
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop, after CONTINUE: x = 3
Inside loop: x = 3
Inside loop, after CONTINUE: x = 4
Inside loop: x = 4
Inside loop, after CONTINUE: x = 5
After loop: x = 5
CONTINUE WHEN Statement
根据条件判断是否退出当前循环。
例子：CONTINUE WHEN Statement in Basic LOOP Statement
DECLARE
x NUMBER := 0;
BEGIN
LOOP -- After CONTINUE statement, control resumes here
DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
CONTINUE WHEN x < 3;
DBMS_OUTPUT.PUT_LINE
('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
EXIT WHEN x = 5;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
END;
/
输出：
Inside loop: x = 0
Inside loop: x = 1
Inside loop: x = 2
Inside loop, after CONTINUE: x = 3
Inside loop: x = 3
Inside loop, after CONTINUE: x = 4
Inside loop: x = 4
Inside loop, after CONTINUE: x = 5
After loop: x = 5
FOR LOOP Statement
语法：
[ label ] FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
statements
END LOOP [ label ];
例子：FOR LOOP Statements
BEGIN
DBMS_OUTPUT.PUT_LINE ('lower_bound < upper_bound');
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('lower_bound = upper_bound');
FOR i IN 2..2 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('lower_bound > upper_bound');
FOR i IN 3..1 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
输出：
lower_bound < upper_bound
1
2
3
lower_bound = upper_bound
2
lower_bound > upper_bound
例子：Reverse FOR LOOP Statements
BEGIN
DBMS_OUTPUT.PUT_LINE ('upper_bound > lower_bound');
FOR i IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('upper_bound = lower_bound');
FOR i IN REVERSE 2..2 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('upper_bound < lower_bound');
FOR i IN REVERSE 3..1 LOOP
DBMS_OUTPUT.PUT_LINE (i);
END LOOP;
END;
/
输出:
upper_bound > lower_bound
3
2
1
upper_bound = lower_bound
2
upper_bound < lower_bound
例子：FOR LOOP Statement Bounds
DECLARE
first INTEGER := 1;
last INTEGER := 10;
high INTEGER := 100;
low INTEGER := 12;
BEGIN
-- Bounds are numeric literals:
FOR j IN -5..5 LOOP
NULL;
END LOOP;
-- Bounds are numeric variables:
FOR k IN REVERSE first..last LOOP
NULL;
END LOOP;
-- Lower bound is numeric literal,
-- Upper bound is numeric expression:
FOR step IN 0..(TRUNC(high/low) * 2) LOOP
NULL;
END LOOP;
END;
/
例子：Specifying FOR LOOP Statement Bounds at Run Time
DROP TABLE temp;
CREATE TABLE temp (
emp_no NUMBER,
email_addr VARCHAR2(50)
);
DECLARE
emp_count NUMBER;
BEGIN
SELECT COUNT(employee_id) INTO emp_count
FROM employees;
FOR i IN 1..emp_count LOOP
INSERT INTO temp (emp_no, email_addr)
VALUES(i, 'to be added later');
END LOOP;
END;
/
例子：EXIT WHEN Statement in FOR LOOP Statement
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1 is SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into v_employees record:
FOR i IN 1..10 LOOP
FETCH c1 INTO v_employees;
EXIT WHEN c1%NOTFOUND;
-- Process data here
END LOOP;
CLOSE c1;
END;
/
例子：EXIT WHEN Statement in Inner FOR LOOP Statement
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1 is SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into v_employees record:
<<outer_loop>>
FOR i IN 1..10 LOOP
-- Process data here
FOR j IN 1..10 LOOP
FETCH c1 INTO v_employees;
EXIT outer_loop WHEN c1%NOTFOUND;
-- Process data here
END LOOP;
END LOOP outer_loop;
CLOSE c1;
END;
/
例子：CONTINUE WHEN Statement in Inner FOR LOOP Statement
DECLARE
v_employees employees%ROWTYPE;
CURSOR c1 is SELECT * FROM employees;
BEGIN
OPEN c1;
-- Fetch entire row into v_employees record:
<<outer_loop>>
FOR i IN 1..10 LOOP
-- Process data here
FOR j IN 1..10 LOOP
FETCH c1 INTO v_employees;
CONTINUE outer_loop WHEN c1%NOTFOUND;
-- Process data here
END LOOP;
END LOOP outer_loop;
CLOSE c1;
END;
/
WHILE LOOP Statement
语法：
[ label ] WHILE condition LOOP
statements
END LOOP [ label ];
例子：WHILE LOOP Statements
DECLARE
done BOOLEAN := FALSE;
BEGIN
WHILE done LOOP
DBMS_OUTPUT.PUT_LINE ('This line does not print.');
done := TRUE; -- This assignment is not made.
END LOOP;
WHILE NOT done LOOP
DBMS_OUTPUT.PUT_LINE ('Hello, world!');
done := TRUE;
END LOOP;
END;
/
输出：
Hello, world!
Sequential Control Statements
GOTO Statement
跳转到指定标签，谨慎使用。
例子：GOTO Statement
DECLARE
p VARCHAR2(30);
n PLS_INTEGER := 37;
BEGIN
FOR j in 2..ROUND(SQRT(n)) LOOP
IF n MOD j = 0 THEN
p := ' is not a prime number';
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/
输出：
37 is a prime number
NULL Statement
什么也不做。
例子：NULL Statement Showing No Action
DECLARE
v_job_id VARCHAR2(10);
v_emp_id NUMBER(6) := 110;
BEGIN
SELECT job_id INTO v_job_id
FROM employees
WHERE employee_id = v_emp_id;
IF v_job_id = 'SA_REP' THEN
UPDATE employees
SET commission_pct = commission_pct * 1.2;
ELSE
NULL; -- Employee is not a sales rep
END IF;
END;
/
Cursors
cursor（游标）是指向私有 SQL 区的指针。
这里介绍的游标是 session cursor （会话游标）。会话游标驻留在会话内存中，直到会话结束。
由 PL/SQL 构造和管理的会话游标是 implicit cursor（隐式游标）。由用户构造和管理的会话游标是 explicit cursor（显式游标）。
Implicit Cursors
隐式游标是由 PL/SQL 构造和管理的会话游标。PL/SQL 每次运行 SELECT 或 DML 语句时都会打开一个隐式游标。用户无法控制隐式游标，但可以从其属性中获取信息。
隐式游标属性值的语法是 SQLattribute（因此，隐式游标也称为 SQL 游标）。SQL属性始终引用最近运行的 SELECT 或 DML 语句。如果没有运行此类语句，则 SQLattribute 的值为 NULL。
隐式游标在其关联语句运行后关闭，在另一个 SELECT 或 DML 语句运行之前，其属性值仍然可用。
最近运行的 SELECT 或 DML 语句可能位于不同的作用域中。要保存属性值供以后使用，请立即将其分配给局部变量。否则，其他操作（如子程序调用）可能会更改属性的值。
| 属性 | 描述 |
|---|---|
| SQL%ISOPEN | 总是返回 FALSE，因为隐式游标总是在其关联语句运行后关闭。 |
| SQL%FOUND | 如果没有 SELECT 或者 DML 语句运行，则为 NULL。 如果 SELECT 语句返回了一行或多行，或者 DML 语句影响了一行或多行，则为 TRUE。 其他情况为 FALSE。 |
| SQL%NOTFOUND | 如果没有 SELECT 或者 DML 语句运行，则为 NULL。 如果 SELECT 语句返回了一行或多行，或者 DML 语句影响了一行或多行，则为 FALSE。 其他情况为 TRUE。 对 PL/SQL SELECT INTO 语句没有用。 |
| SQL%ROWCOUNT | 如果没有 SELECT 或者 DML 语句运行，则为 NULL。 否则，为SELECT 语句返回的行数或受 DML 语句影响的行数。 |
例子：SQL%FOUND Implicit Cursor Attribute
DROP TABLE dept_temp;
CREATE TABLE dept_temp AS
SELECT * FROM departments;
CREATE OR REPLACE PROCEDURE p (
dept_no NUMBER
) AUTHID DEFINER AS
BEGIN
DELETE FROM dept_temp
WHERE department_id = dept_no;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE (
'Delete succeeded for department number ' || dept_no
);
ELSE
DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
END IF;
END;
/
BEGIN
p(270);
p(400);
END;
/
输出：
Delete succeeded for department number 270
No department number 400
例子：SQL%ROWCOUNT Implicit Cursor Attribute
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
SELECT * FROM employees;
DECLARE
mgr_no NUMBER(6) := 122;
BEGIN
DELETE FROM employees_temp WHERE manager_id = mgr_no;
DBMS_OUTPUT.PUT_LINE
('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/
输出：
Number of employees deleted: 8
Explicit Cursors
显式游标是用户构造和管理的会话游标。必须声明并定义显式游标，为其指定名称并将其与查询关联（通常，查询返回多行）。然后，可以通过以下任一方式处理查询结果集：
打开显式游标（使用 OPEN 语句），从结果集中读取行（使用 FETCH 语句），然后关闭显式游标（使用 CLOSE 语句）。
在游标 FOR LOOP 语句中使用显式游标。
不能为显式游标赋值、在表达式中使用该游标或将其用作正式子程序参数或主机变量。可以使用 cursor variable （游标变量）执行这些操作。
与隐式游标不同，可以按名称引用显式游标或游标变量。因此，显式游标或游标变量称为命名游标。
Declaring and Defining Explicit Cursors
声明显示游标语法：
CURSOR cursor_name [ parameter_list ] RETURN return_type;
定义显示游标语法：
CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;
例子：Explicit Cursor Declaration and Definition
DECLARE
CURSOR c1 RETURN departments%ROWTYPE; -- Declare c1
CURSOR c2 IS -- Declare and define c2
SELECT employee_id, job_id, salary FROM employees
WHERE salary > 2000;
CURSOR c1 RETURN departments%ROWTYPE IS -- Define c1,
SELECT * FROM departments -- repeating return type
WHERE department_id = 110;
CURSOR c3 RETURN locations%ROWTYPE; -- Declare c3
CURSOR c3 IS -- Define c3,
SELECT * FROM locations -- omitting return type
WHERE country_id = 'JP';
BEGIN
NULL;
END;
/
Opening and Closing Explicit Cursors
使用 OPEN 语句打开游标，执行以下操作：
- 分配数据库资源以处理查询
- 处理查询
- 标识结果集
- 如果查询有 FOR UPDATE 子句，则锁定结果集的行
- 将游标定位在结果集的第一行之前
使用 CLOSE 语句关闭打开的显式游标，从而允许重用其资源。关闭游标后，不能从其结果集中读取记录或引用其属性。否则会引发预定义的异常 INVALID_CURSOR。
可以重新打开关闭的游标。在尝试重新打开显式游标之前，必须将其关闭。否则会引发预定义的异常 CURSOR_ALREADY_OPEN。
Fetching Data with Explicit Cursors
打开显式游标后，可以使用 FETCH 语句读取查询结果集的行。返回一行的 FETCH 语句的基本语法为：
FETCH cursor_name INTO into_clause
into_clause 是变量列表或单个记录变量。对于查询返回的每个列，变量列表或记录必须具有相应的类型兼容的变量或字段。为方便在 FETCH 语句中使用，一般使用 %TYPE 和 %ROWTYPE 属性声明变量和记录。
FETCH 语句获取结果集的当前行，将该行的列值存储到变量或记录中，然后移动游标到下一行。
通常，在 LOOP 语句中使用 FETCH 语句，当 FETCH 语句的行数遍历完时退出。使用游标属性 %NOTFOUND 检测此退出条件。
例子：FETCH Statements Inside LOOP Statements
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
CURSOR c2 IS
SELECT * FROM employees
WHERE REGEXP_LIKE (job_id, '[ACADFIMKSA]_M[ANGR]')
ORDER BY job_id;
v_employees employees%ROWTYPE; -- record variable for row of table
BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1 INTO v_lastname, v_jobid;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
OPEN c2;
LOOP -- Fetches entire row into the v_employees record
FETCH c2 INTO v_employees;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id );
END LOOP;
CLOSE c2;
END;
/
输出：
Atkinson ST_CLERK
Bell SH_CLERK
Bissot ST_CLERK
...
Walsh SH_CLERK
-------------------------------------
Higgins AC_MGR
Greenberg FI_MGR
Hartstein MK_MAN
...
Zlotkey SA_MAN
例子：Fetching Same Explicit Cursor into Different Variables
DECLARE
CURSOR c IS
SELECT e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id AND e.manager_id = 100
ORDER BY last_name;
-- Record variables for rows of cursor result set:
job1 c%ROWTYPE;
job2 c%ROWTYPE;
job3 c%ROWTYPE;
job4 c%ROWTYPE;
job5 c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO job1; -- fetches first row
FETCH c INTO job2; -- fetches second row
FETCH c INTO job3; -- fetches third row
FETCH c INTO job4; -- fetches fourth row
FETCH c INTO job5; -- fetches fifth row
CLOSE c;
DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/
输出：
Sales Manager (SA_MAN)
Administration Vice President (AD_VP)
Sales Manager (SA_MAN)
Stock Manager (ST_MAN)
Marketing Manager (MK_MAN)
Variables in Explicit Cursor Queries
显式游标查询可以引用其作用域中的任何变量。打开显式游标时，PL/SQL 会计算查询中的任何变量，并在标识结果集时使用这些值。稍后更改变量的值不会更改结果集。
例子：Variable in Explicit Cursor Query—No Result Set Change
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT salary, salary*factor FROM employees
WHERE job_id LIKE 'AD_%';
BEGIN
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1 INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
factor := factor + 1; -- Does not affect sal_multiple
END LOOP;
CLOSE c1;
END;
/
输出：
factor = 2
sal = 4451
sal_multiple = 8902
factor = 3
sal = 26460
sal_multiple = 52920
factor = 4
sal = 18742.5
sal_multiple = 37485
factor = 5
sal = 18742.5
sal_multiple = 37485
若要更改结果集，必须关闭游标，更改变量的值，然后再次打开游标。
例子：Variable in Explicit Cursor Query—Result Set Change
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT salary, salary*factor FROM employees
WHERE job_id LIKE 'AD_%';
BEGIN
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1 INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN c1; -- PL/SQL evaluates factor
LOOP
FETCH c1 INTO sal, sal_multiple;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE c1;
END;
/
输出：
factor = 2
sal = 4451
sal_multiple = 8902
sal = 26460
sal_multiple = 52920
sal = 18742.5
sal_multiple = 37485
sal = 18742.5
sal_multiple = 37485
factor = 3
sal = 4451
sal_multiple = 13353
sal = 26460
sal_multiple = 79380
sal = 18742.5
sal_multiple = 56227.5
sal = 18742.5
sal_multiple = 56227.5
When Explicit Cursor Queries Need Column Aliases
当显式游标查询包含虚拟列（表达式）时，如果满足以下任一条件，则该列必须具有别名：
使用游标获取数据到使用 %ROWTYPE 声明的记录。
需要在程序中引用虚拟列。
例子：Explicit Cursor with Virtual Column that Needs Alias
DECLARE
CURSOR c1 IS
SELECT employee_id,
(salary * .05) raise
FROM employees
WHERE job_id LIKE '%_MAN'
ORDER BY employee_id;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
'Raise for employee #' || emp_rec.employee_id ||
' is $' || emp_rec.raise
);
END LOOP;
CLOSE c1;
END;
/
输出：
Raise for employee #114 is $550
Raise for employee #120 is $533.61
Raise for employee #121 is $520.905
Raise for employee #122 is $501.8475
Raise for employee #123 is $412.9125
Raise for employee #124 is $368.445
Raise for employee #145 is $700
Raise for employee #146 is $675
Raise for employee #147 is $600
Raise for employee #148 is $550
Raise for employee #149 is $525
Raise for employee #201 is $650
Explicit Cursors that Accept Parameters
可以创建具有形参的显式游标，然后在每次打开游标时将不同的实参传递给游标。在游标查询中，在可以使用常量的任何位置使用形参。在游标查询之外，不能引用形参。
例子：Explicit Cursor that Accepts Parameters
DECLARE
CURSOR c (job VARCHAR2, max_sal NUMBER) IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job
AND salary > max_sal
ORDER BY salary;
PROCEDURE print_overpaid IS
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
LOOP
FETCH c INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
' (by ' || overpayment_ || ')');
END LOOP;
END print_overpaid;
BEGIN
DBMS_OUTPUT.PUT_LINE('----------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Stock Clerks:');
DBMS_OUTPUT.PUT_LINE('----------------------');
OPEN c('ST_CLERK', 5000);
print_overpaid;
CLOSE c;
DBMS_OUTPUT.PUT_LINE('-------------------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
DBMS_OUTPUT.PUT_LINE('-------------------------------');
OPEN c('SA_REP', 10000);
print_overpaid;
CLOSE c;
END;
/
输出：
----------------------
Overpaid Stock Clerks:
----------------------
Davies, Curtis (by 15.3)
Nayer, Julia (by 177.08)
Stiles, Stephen (by 177.08)
Bissot, Laura (by 338.87)
Mallin, Jason (by 338.87)
Rajs, Trenna (by 662.43)
Ladwig, Renske (by 824.21)
-------------------------------
Overpaid Sales Representatives:
-------------------------------
Fox, Tayler (by 80)
Tucker, Peter (by 500)
King, Janette (by 500)
Bloom, Harrison (by 500)
Vishney, Clara (by 1025)
Abel, Ellen (by 1550)
Ozer, Lisa (by 2075)
使用形参创建显式游标时，可以指定默认值。当形参具有默认值时，其对应的实参是可选的。
例子：Cursor Parameters with Default Values
DECLARE
CURSOR c (location NUMBER DEFAULT 1700) IS
SELECT d.department_name,
e.last_name manager,
l.city
FROM departments d, employees e, locations l
WHERE l.location_id = location
AND l.location_id = d.location_id
AND d.department_id = e.department_id
ORDER BY d.department_id;
PROCEDURE print_depts IS
dept_name departments.department_name%TYPE;
mgr_name employees.last_name%TYPE;
city_name locations.city%TYPE;
BEGIN
LOOP
FETCH c INTO dept_name, mgr_name, city_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name || ' (Manager: ' || mgr_name || ')');
END LOOP;
END print_depts;
BEGIN
DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT HEADQUARTERS:');
DBMS_OUTPUT.PUT_LINE('--------------------------------');
OPEN c;
print_depts;
DBMS_OUTPUT.PUT_LINE('--------------------------------');
CLOSE c;
DBMS_OUTPUT.PUT_LINE('DEPARTMENTS IN CANADA:');
DBMS_OUTPUT.PUT_LINE('--------------------------------');
OPEN c(1800); -- Toronto
print_depts;
CLOSE c;
OPEN c(1900); -- Whitehorse
print_depts;
CLOSE c;
END;
/
输出：
DEPARTMENTS AT HEADQUARTERS:
--------------------------------
Administration (Manager: Whalen)
Purchasing (Manager: Colmenares)
Purchasing (Manager: Baida)
Purchasing (Manager: Himuro)
Purchasing (Manager: Raphaely)
Purchasing (Manager: Khoo)
Purchasing (Manager: Tobias)
Executive (Manager: Kochhar)
Executive (Manager: De Haan)
Executive (Manager: King)
Finance (Manager: Popp)
Finance (Manager: Greenberg)
Finance (Manager: Faviet)
Finance (Manager: Chen)
Finance (Manager: Urman)
Finance (Manager: Sciarra)
Accounting (Manager: Gietz)
Accounting (Manager: Higgins)
--------------------------------
DEPARTMENTS IN CANADA:
--------------------------------
Marketing (Manager: Hartstein)
Marketing (Manager: Fay)
例子：Adding Formal Parameter to Existing Cursor
DECLARE
CURSOR c (job VARCHAR2, max_sal NUMBER, hired DATE DEFAULT '31-DEC-99') IS
SELECT last_name, first_name, (salary - max_sal) overpayment
FROM employees
WHERE job_id = job
AND salary > max_sal
AND hire_date > hired
ORDER BY salary;
PROCEDURE print_overpaid IS
last_name_ employees.last_name%TYPE;
first_name_ employees.first_name%TYPE;
overpayment_ employees.salary%TYPE;
BEGIN
LOOP
FETCH c INTO last_name_, first_name_, overpayment_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(last_name_ || ', ' || first_name_ ||
' (by ' || overpayment_ || ')');
END LOOP;
END print_overpaid;
BEGIN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives:');
DBMS_OUTPUT.PUT_LINE('-------------------------------');
OPEN c('SA_REP', 10000); -- existing reference
print_overpaid;
CLOSE c;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Overpaid Sales Representatives Hired After 2004:');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
OPEN c('SA_REP', 10000, '31-DEC-04'); -- new reference
print_overpaid;
CLOSE c;
END;
/
输出：
-------------------------------
Overpaid Sales Representatives:
-------------------------------
Fox, Tayler (by 80)
Tucker, Peter (by 500)
King, Janette (by 500)
Bloom, Harrison (by 500)
Vishney, Clara (by 1025)
Abel, Ellen (by 1550)
Ozer, Lisa (by 2075)
------------------------------------------------
Overpaid Sales Representatives Hired After 2004:
------------------------------------------------
Fox, Tayler (by 80)
Tucker, Peter (by 500)
Bloom, Harrison (by 500)
Vishney, Clara (by 1025)
Ozer, Lisa (by 2075)
Explicit Cursor Attributes
与隐式游标属性类似，显示游标属性的语法：
cursor_name%attribute
| 属性 | 描述 |
|---|---|
| %ISOPEN | 如果 OPEN，则返回 TRUE，否则返回 FALSE。 |
| %FOUND | 在显式游标打开之后，但在第一次读取之前，为 NULL。 如果最近从显式游标读取返回一行，为 TRUE。 其他情况为 FALSE。 |
| %NOTFOUND | 在显式游标打开之后，但在第一次读取之前，为 NULL。 如果最近从显式游标读取返回一行，为 FALSE。 其他情况为 TRUE。 |
| %ROWCOUNT | 在显式游标打开之后，但在第一次读取之前，为 0。 否则，为获取的行数。 |
例子：%ISOPEN Explicit Cursor Attribute
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF NOT c1%ISOPEN THEN
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
END;
/
例子：%FOUND Explicit Cursor Attribute
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed
EXIT;
END IF;
END LOOP;
END;
/
输出：
Name = Abel, salary = 11000
Name = Ande, salary = 6400
Name = Atkinson, salary = 3557.4
Name = Austin, salary = 4800
Name = Baer, salary = 10000
Name = Baida, salary = 2900
Name = Banda, salary = 6200
Name = Bates, salary = 7300
Name = Bell, salary = 5082
Name = Bernstein, salary = 9500
例子：%NOTFOUND Explicit Cursor Attribute
DECLARE
CURSOR c1 IS
SELECT last_name, salary FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE
('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/
输出：
Name = Abel, salary = 11000
Name = Ande, salary = 6400
Name = Atkinson, salary = 3557.4
Name = Austin, salary = 4800
Name = Baer, salary = 10000
Name = Baida, salary = 2900
Name = Banda, salary = 6200
Name = Bates, salary = 7300
Name = Bell, salary = 5082
Name = Bernstein, salary = 9500
例子：%ROWCOUNT Explicit Cursor Attribute
DECLARE
CURSOR c1 IS
SELECT last_name FROM employees
WHERE ROWNUM < 11
ORDER BY last_name;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
END IF;
END LOOP;
CLOSE c1;
END;
/
输出：
1. Abel
2. Ande
3. Atkinson
4. Austin
5. Baer
--- Fetched 5th row ---
6. Baida
7. Banda
8. Bates
9. Bell
10. Bernstein
Query Result Set Processing
在 PL/SQL 中，可以使用游标来处理查询结果集。
使用隐式游标：
- SELECT INTO
- 隐式游标
FORLOOP
使用显示游标：
- 显示游标
FORLOOP - OPEN，FETCH 和 CLOSE
Query Result Set Processing With SELECT INTO Statements
SELECT INTO 语句使用隐式游标从一个或多个数据库表中检索值，并将它们存储在变量中。
- Single-Row Result Sets：如果希望查询仅返回一行，使用 SELECT INTO 语句将该行中的值存储在一个或多个标量变量中，如果查询可能返回多行，但只需要第 n 行，则使用子句
WHERE ROWNUM=n将结果集限制为该行。 - Large Multiple-Row Result Sets：如果必须将大量表数据分配给变量，Oracle 建议将 SELECT INTO 语句与 BULK COLLECT 子句一起使用。此语句将整个结果集检索到一个或多个集合变量中。
Query Result Set Processing With Cursor FOR LOOP Statements
游标 FOR LOOP 语句允许运行 SELECT 语句，然后立即遍历结果集的行。此语句可以用于隐式或显式游标。
游标 FOR LOOP 语句隐式声明其循环索引为 %ROWTYPE 记录变量。此记录是循环的本地记录，仅在循环执行期间存在。循环中的语句可以引用记录及其字段。
声明循环索引记录变量后，FOR LOOP 语句将打开指定的游标。对于循环的每次迭代，FOR LOOP 语句从结果集中提取一行并将其存储在记录中。当没有更多要读取的行时，游标 FOR LOOP 语句将关闭游标。如果循环内的语句将控制权转移到循环外部，或者 PL/SQL 引发异常，则游标也会关闭。
例子：Implicit Cursor FOR LOOP Statement
BEGIN
FOR item IN (
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%'
AND manager_id > 120
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
输出：
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK
例子：Explicit Cursor FOR LOOP Statement
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/
输出：
Name = Atkinson, Job = ST_CLERK
Name = Bell, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...
Name = Walsh, Job = SH_CLERK
例子：Passing Parameters to Explicit Cursor FOR LOOP Statement
DECLARE
CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
SELECT * FROM employees
WHERE job_id = job
AND salary > max_wage;
BEGIN
FOR person IN c1('ST_CLERK', 3000)
LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE (
'Name = ' || person.last_name || ', salary = ' ||
person.salary || ', Job Id = ' || person.job_id
);
END LOOP;
END;
/
输出：
Name = Nayer, salary = 4065.6, Job Id = ST_CLERK
Name = Mikkilineni, salary = 3430.35, Job Id = ST_CLERK
Name = Landry, salary = 3049.2, Job Id = ST_CLERK
...
Name = Vargas, salary = 3176.25, Job Id = ST_CLERK
例子：Cursor FOR Loop References Virtual Columns
BEGIN
FOR item IN (
SELECT first_name || ' ' || last_name AS full_name,
salary * 10 AS dream_salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY dream_salary DESC, last_name ASC
) LOOP
DBMS_OUTPUT.PUT_LINE
(item.full_name || ' dreams of making ' || item.dream_salary);
END LOOP;
END;
/
输出：
Michael Hartstein dreams of making 143325
Pat Fay dreams of making 66150
Jennifer Whalen dreams of making 48510
Douglas Grant dreams of making 31531.5
Donald OConnell dreams of making 31531.5
Query Result Set Processing With Explicit Cursors, OPEN, FETCH, and CLOSE
若要完全控制查询结果集处理，声明显式游标并使用语句 OPEN、FETCH 和 CLOSE 管理它们。
这种结果集处理技术比其他方法更复杂，但也更灵活：
使用多个游标并行处理多个结果集。
在单个循环迭代中处理多行、跳过行或将处理拆分为多个循环。
在一个 PL/SQL 单元中指定查询，但在另一个单元中检索行。
Query Result Set Processing with Subqueries
如果通过循环遍历查询结果集，并为每一行运行另一个查询来处理查询结果集，则可以通过从循环内部删除第二个查询，并使其成为第一个查询的子查询来提高性能。
例子：Subquery in FROM Clause of Parent Query
DECLARE
CURSOR c1 IS
SELECT t1.department_id, department_name, staff
FROM departments t1,
( SELECT department_id, COUNT(*) AS staff
FROM employees
GROUP BY department_id
) t2
WHERE (t1.department_id = t2.department_id) AND staff >= 5
ORDER BY staff;
BEGIN
FOR dept IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Department = '
|| dept.department_name || ', staff = ' || dept.staff);
END LOOP;
END;
/
输出：
Department = IT, staff = 5
Department = Purchasing, staff = 6
Department = Finance, staff = 6
Department = Sales, staff = 34
Department = Shipping, staff = 45
例子：Correlated Subquery
DECLARE
CURSOR c1 IS
SELECT department_id, last_name, salary
FROM employees t
WHERE salary > ( SELECT AVG(salary)
FROM employees
WHERE t.department_id = department_id
)
ORDER BY department_id, last_name;
BEGIN
FOR person IN c1
LOOP
DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
END LOOP;
END;
/
输出：
Making above-average salary = Hartstein
Making above-average salary = Raphaely
Making above-average salary = Bell
...
Making above-average salary = Higgins
Cursor Variables
cursor variable（游标变量）类似于显式游标，不同之处在于：
不限于一个查询。可以为查询打开游标变量，处理结果集，然后将游标变量用于另一个查询。
可以为其赋值。
可以在表达式中使用。
可以是子程序参数。可以使用游标变量在子程序之间传递查询结果集。
可以是主机变量。可以使用游标变量在 PL/SQL 存储的子程序及其客户端之间传递查询结果集。
不能接受参数。不能将参数传递给游标变量，但可以将整个查询传递给游标变量。
在引用游标变量之前，必须通过打开游标变量，或者为其分配打开的 PL/SQL 游标变量或者打开主机游标变量的值，使其指向 SQL 工作区。
Creating Cursor Variables
若要创建游标变量，可以声明预定义类型 SYS_REFCURSOR 的变量或定义 REF CURSOR 类型，然后声明该类型的变量。游标变量也称为 REF CURSOR。
语法：
TYPE type_name IS REF CURSOR [ RETURN return_type ]
如果指定 return_type，则 REF CURSOR 类型和该类型的游标变量为强类型；如果没有指定则为弱类型。SYS_REFCURSOR 和及其游标变量为弱类型。
使用强类型游标变量，只能关联返回指定类型的查询。使用弱类型游标变量，可以关联任何查询。
弱类型游标变量比强类型游标变量更容易出错，但也更灵活。可以将弱类型游标变量的值分配给任何其他弱类型游标变量。
仅当两个游标变量具有相同的类型（而不仅仅是相同的返回类型）时，才能将强类型游标变量的值分配给另一个强类型游标变量。
例子：Cursor Variable Declarations
DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong type
TYPE genericcurtyp IS REF CURSOR; -- weak type
cursor1 empcurtyp; -- strong cursor variable
cursor2 genericcurtyp; -- weak cursor variable
my_cursor SYS_REFCURSOR; -- weak cursor variable
TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; -- strong type
dept_cv deptcurtyp; -- strong cursor variable
BEGIN
NULL;
END;
/
例子：Cursor Variable with User-Defined Return Type
DECLARE
TYPE EmpRecTyp IS RECORD (
employee_id NUMBER,
last_name VARCHAR2(25),
salary NUMBER(8,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp;
BEGIN
NULL;
END;
/
Opening and Closing Cursor Variables
声明游标变量后，可以使用 OPEN FOR 语句打开，该语句执行以下操作：
将游标变量与查询关联（通常，查询返回多行）。查询可以包含绑定变量的占位符，这些变量的值在 OPEN FOR 语句的 USING 子句中指定。
分配数据库资源以处理查询
处理查询
标识结果集
如果查询有 FOR UPDATE 子句，则锁定结果集的行
将游标定位在结果集的第一行之前
在重新打开游标变量之前，不需要关闭（即在另一个 OPEN FOR 语句中使用）。重新打开游标变量后，以前与其关联的查询将丢失。
当不再需要游标变量时，使用 CLOSE 语句将其关闭，从而允许重用其资源。关闭游标变量后，无法从其结果集中读取记录或引用其属性。否则 PL/SQL 会抛出预定义的异常 INVALID_CURSOR。
可以重新打开关闭的游标变量。
Fetching Data with Cursor Variables
打开游标变量后，可以使用 FETCH 语句获取查询结果集的行。
游标变量的返回类型必须与 FETCH 语句的 into_clause 兼容。如果是强类型游标变量，PL/SQL 会在编译时捕获异常。如果是弱类型游标变量，PL/SQL 会在运行时捕获异常，在第一次获取之前抛出预定义异常 ROWTYPE_MISMATCH。
例子：Fetching Data with Cursor Variables
DECLARE
cv SYS_REFCURSOR; -- cursor variable
v_lastname employees.last_name%TYPE; -- variable for last_name
v_jobid employees.job_id%TYPE; -- variable for job_id
query_2 VARCHAR2(200) :=
'SELECT * FROM employees
WHERE REGEXP_LIKE (job_id, ''[ACADFIMKSA]_M[ANGR]'')
ORDER BY job_id';
v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
OPEN cv FOR
SELECT last_name, job_id FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
LOOP -- Fetches 2 columns into variables
FETCH cv INTO v_lastname, v_jobid;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_lastname, 25, ' ') || v_jobid );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
OPEN cv FOR query_2;
LOOP -- Fetches entire row into the v_employees record
FETCH cv INTO v_employees;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( RPAD(v_employees.last_name, 25, ' ') ||
v_employees.job_id );
END LOOP;
CLOSE cv;
END;
/
输出：
Atkinson ST_CLERK
Bell SH_CLERK
Bissot ST_CLERK
...
Walsh SH_CLERK
-------------------------------------
Higgins AC_MGR
Greenberg FI_MGR
Hartstein MK_MAN
...
Zlotkey SA_MAN
使用 FETCH 语句的 BULK COLLECT 子句从游标变量提取数据到两个集合（嵌套表）中。
例子：Fetching from Cursor Variable into Collections
DECLARE
TYPE empcurtyp IS REF CURSOR;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
TYPE sallist IS TABLE OF employees.salary%TYPE;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
OPEN emp_cv FOR
SELECT last_name, salary FROM employees
WHERE job_id = 'SA_REP'
ORDER BY salary DESC;
FETCH emp_cv BULK COLLECT INTO names, sals;
CLOSE emp_cv;
-- loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || names(i) || ', salary = ' || sals(i));
END LOOP;
END;
/
输出：
Name = Ozer, salary = 12075
Name = Abel, salary = 11550
Name = Vishney, salary = 11025
...
Name = Kumar, salary = 6405
Assigning Values to Cursor Variables
可以将另一个 PL/SQL 游标变量或主机游标变量的值赋予给一个 PL/SQL 游标变量。语法为：
target_cursor_variable := source_cursor_variable;
如果 source_cursor_variable 处于打开状态，则在赋值后，target_cursor_variable 也处于打开状态。这两个游标变量指向同一个 SQL 工作区。
如果 source_cursor_variable 未打开，则在赋值后打开 target_cursor_variable ，不会打开 source_cursor_variable。
Variables in Cursor Variable Queries
与游标变量关联的查询可以引用其作用域中的任何变量。使用 OPEN FOR 语句打开游标变量时，PL/SQL 将计算查询中的任何变量，并在标识结果集时使用这些值。稍后更改变量的值不会更改结果集。
例子：Variable in Cursor Variable Query—No Result Set Change
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
cv SYS_REFCURSOR;
BEGIN
OPEN cv FOR
SELECT salary, salary*factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
factor := factor + 1; -- Does not affect sal_multiple
END LOOP;
CLOSE cv;
END;
/
输出：
factor = 2
sal = 4451
sal_multiple = 8902
factor = 3
sal = 26460
sal_multiple = 52920
factor = 4
sal = 18742.5
sal_multiple = 37485
factor = 5
sal = 18742.5
sal_multiple = 37485
若要更改结果集，必须更改变量的值，然后再次打开同一查询的游标变量。
例子：Variable in Cursor Variable Query—Result Set Change
DECLARE
sal employees.salary%TYPE;
sal_multiple employees.salary%TYPE;
factor INTEGER := 2;
cv SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN cv FOR
SELECT salary, salary*factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
factor := factor + 1;
DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
OPEN cv FOR
SELECT salary, salary*factor
FROM employees
WHERE job_id LIKE 'AD_%'; -- PL/SQL evaluates factor
LOOP
FETCH cv INTO sal, sal_multiple;
EXIT WHEN cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
END LOOP;
CLOSE cv;
END;
/
输出：
factor = 2
sal = 4451
sal_multiple = 8902
sal = 26460
sal_multiple = 52920
sal = 18742.5
sal_multiple = 37485
sal = 18742.5
sal_multiple = 37485
factor = 3
sal = 4451
sal_multiple = 13353
sal = 26460
sal_multiple = 79380
sal = 18742.5
sal_multiple = 56227.5
sal = 18742.5
sal_multiple = 56227.5
Cursor Variable Attributes
游标变量具有与显式游标相同的属性。游标变量属性值的语法：cursor_variable_name 紧跟属性（例如，cv%ISOPEN）。如果游标变量未打开，则引用除 %ISOPEN 以外的任何属性都会引发预定义的异常 INVALID_CURSOR。
Cursor Variables as Subprogram Parameters
可以使用游标变量作为子程序参数，在子程序之间传递查询结果非常有用。例如：
可以在一个子程序中打开游标变量，然后在另一个子程序中处理它。
在多语言应用程序中，PL/SQL子程序可以使用游标变量将结果集返回给用不同语言编写的子程序。
将游标变量声明为子程序的形参时：
如果子程序打开或为游标变量赋值，则参数模式必须为 IN OUT。
如果子程序仅从游标变量读取或关闭游标变量，则参数模式可以是 IN 或 IN OUT。
要在不同 PL/SQL 单元中的子程序之间传递游标变量参数，在包中定义参数的 REF CURSOR 类型。
例子：Procedure to Open Cursor Variable for One Query
CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
END emp_data;
/
CREATE OR REPLACE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM employees;
END open_emp_cv;
END emp_data;
/
例子：Opening Cursor Variable for Chosen Query (Same Return Type)
CREATE OR REPLACE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT);
END emp_data;
/
CREATE OR REPLACE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT *
FROM employees
WHERE salary > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT *
FROM employees
WHERE department_id = 100;
END IF;
END;
END emp_data;
/
例子：Opening Cursor Variable for Chosen Query (Different Return Types)
CREATE OR REPLACE PACKAGE admin_data AS
TYPE gencurtyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
END admin_data;
/
CREATE OR REPLACE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM employees;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM departments;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM jobs;
END IF;
END;
END admin_data;
/
Transaction
有关 Oracle Transaction，COMMIT，ROLLBACK 参考 Oracle SQL。
SET TRANSACTION Statement
可以使用 SET TRANSACTION 语句开始只读或读写事务、建立隔离级别或将当前事务分配给指定的回滚段。只读事务对于在其他用户更新相同表时运行多个查询非常有用。
在只读事务期间，所有查询都引用数据库的同一快照，从而提供多表、多查询、读一致性视图。其他用户可以像往常一样继续查询或更新数据，提交或回滚事务。
例子：SET TRANSACTION Statement in Read-Only Transaction
DECLARE
daily_order_total NUMBER(12,2);
weekly_order_total NUMBER(12,2);
monthly_order_total NUMBER(12,2);
BEGIN
COMMIT; -- end previous transaction
SET TRANSACTION READ ONLY NAME 'Calculate Order Totals';
SELECT SUM (order_total)
INTO daily_order_total
FROM orders
WHERE order_date = SYSDATE;
SELECT SUM (order_total)
INTO weekly_order_total
FROM orders
WHERE order_date = SYSDATE - 7;
SELECT SUM (order_total)
INTO monthly_order_total
FROM orders
WHERE order_date = SYSDATE - 30;
COMMIT; -- ends read-only transaction
END;
/
SET TRANSACTION 语句必须是只读事务中的第一个 SQL 语句，并且在事务中只能出现一次。如果将事务设置为 READ ONLY，则后续查询只能看到事务开始之前提交的更改。使用 READ ONLY 不会影响其他用户或事务。
只读事务中只允许使用 SELECT、OPEN 、FETCH、CLOSE、LOCK TABLE、COMMIT 和 ROLLBACK 语句。不能使用 FOR UPDATE 查询。
Overriding Default Locking
默认情况下，Oracle 数据库会自动锁定数据结构。
如果在事务期间必须对数据具有独占访问权限，则可以使用以下 SQL 语句覆盖默认锁定：
LOCK TABLE，显式锁定整个表。
SELECT FOR UPDATE，显式锁定表的特定行。
LOCK TABLE Statement
LOCK TABLE 语句在指定的锁定模式下显式锁定一个或多个表，以控制对它们的访问。
锁定模式确定可以在表上放置哪些锁。例如，许多用户可以同时获取表上的行共享锁（row share locks），但一次只能有一个用户获取独占锁（exclusive lock）。当一个用户对表具有独占锁时，其他用户无法在该表中插入、删除或更新行。
表锁不会阻塞其他用户查询表，查询也不会获取表锁。仅当两个不同的事务尝试修改同一行时，一个事务才会等待另一个事务完成。LOCK TABLE 语句允许指定等待另一个事务完成的时间。
当获取表锁的事务提交或回滚时，将释放表锁。
SELECT FOR UPDATE and FOR UPDATE Cursors
SELECT FOR UPDATE 语句选择结果集的行并锁定它们。
默认情况下，SELECT FOR UPDATE 语句会一直等到获取请求的行锁。若要更改此行为，可使用 SELECT FOR UPDATE 语句的 NOWAIT、WAIT 或 SKIP LOCK 子句。
当 SELECT FOR UPDATE 与显式游标相关联时，该游标称为 FOR UPDATE 游标。只有 FOR UPDATE 游标可以出现在 UPDATE 或 DELETE 语句的 CURRENT OF 子句中。
例子：UPDATE Cursor in CURRENT OF Clause of UPDATE Statement
DECLARE
my_emp_id NUMBER(6);
my_job_id VARCHAR2(10);
my_sal NUMBER(8,2);
CURSOR c1 IS
SELECT employee_id, job_id, salary
FROM employees FOR UPDATE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_emp_id, my_job_id, my_sal;
IF my_job_id = 'SA_REP' THEN
UPDATE employees
SET salary = salary * 1.02
WHERE CURRENT OF c1;
END IF;
EXIT WHEN c1%NOTFOUND;
END LOOP;
END;
/
当 SELECT FOR UPDATE 查询多个表时，仅锁定其列出现在 FOR UPDATE 子句中的行。
例子：SELECT FOR UPDATE Statement for Multiple Tables
DECLARE
CURSOR c1 IS
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND job_id = 'SA_MAN'
FOR UPDATE OF salary;
BEGIN
NULL;
END;
/
结果集的行在打开 FOR UPDATE 游标时被锁定。提交或回滚事务时，解锁行。行解锁后，无法从 FOR UPDATE 游标中读取数据。
例子：FETCH with FOR UPDATE Cursor After COMMIT Statement
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
CURSOR c1 IS
SELECT * FROM emp
FOR UPDATE OF salary
ORDER BY employee_id;
emp_rec emp%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec; -- fails on second iteration
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
'emp_rec.employee_id = ' ||
TO_CHAR(emp_rec.employee_id)
);
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 105;
COMMIT; -- releases locks
END LOOP;
END;
/
输出：
emp_rec.employee_id = 100
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 11
Autonomous Transactions
自治事务是由另一个事务（主事务）启动的独立事务。自治事务执行 SQL 操作并提交或回滚，而无需提交或回滚主事务。
主事务 （MT） 和自治事务 （AT）控制流：
自治事务不是嵌套事务，因为：
不与主事务共享事务资源（如锁）。
不依赖于主事务。如果主事务回滚，嵌套事务将回滚，但自治事务不会回滚。
其提交的更改会立即对其他事务可见。嵌套事务的已提交更改在主事务提交之前对其他事务不可见。
自治事务中引发的异常会导致事务级回滚，而不是语句级回滚。
使用 AUTONOMOUS_TRANSACTION 声明自治事务。
例子：Declaring Autonomous Function in Package
CREATE OR REPLACE PACKAGE emp_actions AS -- package specification
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body
-- code for function raise_salary
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER)
RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER(8,2);
BEGIN
UPDATE employees SET salary =
salary + sal_raise WHERE employee_id = emp_id;
COMMIT;
SELECT salary INTO new_sal FROM employees
WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;
/
例子：Declaring Autonomous Standalone Procedure
CREATE OR REPLACE PROCEDURE lower_salary
(emp_id NUMBER, amount NUMBER)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END lower_salary;
/
例子：Declaring Autonomous PL/SQL Block
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER(6) := 200;
amount NUMBER(6,2) := 200;
BEGIN
UPDATE employees
SET salary = salary - amount
WHERE employee_id = emp_id;
COMMIT;
END;
/
Autonomous Triggers
触发器必须是自治的，才能运行 TCL 或 DDL 语句。要运行 DDL 语句，触发器必须使用原生动态 SQL。
例子：Autonomous Trigger Logs INSERT Statements
DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
-- Log table:
DROP TABLE log;
CREATE TABLE log (
log_id NUMBER(6),
up_date DATE,
new_sal NUMBER(8,2),
old_sal NUMBER(8,2)
);
-- Autonomous trigger on emp table:
CREATE OR REPLACE TRIGGER log_sal
BEFORE UPDATE OF salary ON emp FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log (
log_id,
up_date,
new_sal,
old_sal
)
VALUES (
:old.employee_id,
SYSDATE,
:new.salary,
:old.salary
);
COMMIT;
END;
/
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 115;
COMMIT;
UPDATE emp
SET salary = salary * 1.05
WHERE employee_id = 116;
ROLLBACK;
-- Show that both committed and rolled-back updates
-- add rows to log table
SELECT * FROM log
WHERE log_id = 115 OR log_id = 116;
LOG_ID UP_DATE NEW_SAL OLD_SAL
---------- --------- ---------- ----------
115 28-APR-10 3417.75 3255
116 28-APR-10 3197.25 3045
2 rows selected.
例子：Autonomous Trigger Uses Native Dynamic SQL for DDL
DROP TABLE temp;
CREATE TABLE temp (
temp_id NUMBER(6),
up_date DATE
);
CREATE OR REPLACE TRIGGER drop_temp_table
AFTER INSERT ON log
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp';
COMMIT;
END;
/
-- Show how trigger works
SELECT * FROM temp;
no rows selected
INSERT INTO log (log_id, up_date, new_sal, old_sal)
VALUES (999, SYSDATE, 5000, 4500);
1 row created.
SELECT * FROM temp;
SELECT * FROM temp
*
ERROR at line 1:
ORA-00942: table or view does not exist
Native Dynamic SQL
动态 SQL 是一种在运行时生成和运行 SQL 语句的编程方法。
需要使用动态 SQL 的情形：
- 编译时 SQL 文本未知
- 不支持作为静态 SQL 的 SQL
PL/SQL 提供了两种编写动态 SQL 的方法：
原生动态 SQL
DBMS_SQL 包
原生动态 SQL 比使用 DBMS_SQL 包更易于读取和编写，并且运行速度更快。但是，要编写原生动态 SQL 代码，必须在编译时知道动态 SQL 语句的输入和输出变量的数量和数据类型。如果在编译时不知道这些信息，则必须使用 DBMS_SQL 包。
原生动态 SQL 使用 EXECUTE IMMEDIATE 语句处理大多数动态 SQL 语句。
如果动态 SQL 语句是返回多行的 SELECT 语句，则原生动态 SQL 提供以下选择：
将 EXECUTE IMMEDIATE 语句与 BULK COLLECT INTO 子句一起使用。
使用 OPEN FOR、FETCH 和 CLOSE 语句。
EXECUTE IMMEDIATE Statement
EXECUTE IMMEDIATE 语句是原生动态 SQL 处理大多数动态 SQL 语句的方法。
如果动态 SQL 语句是自包含的（没有绑定变量的占位符，并且可能返回的唯一结果是错误），则 EXECUTE IMMEDIATE 语句不需要子句。
如果动态 SQL 语句包含绑定变量的占位符，则每个占位符必须在 EXECUTE IMMEDIATE 语句的相应子句中具有相应的绑定变量，如下所示：
如果动态 SQL 语句是最多返回一行的 SELECT 语句，在 INTO 子句中放入输出绑定变量，在 USING 子句中放入输入绑定变量。
如果动态 SQL 语句是可以返回多行的 SELECT 语句，在 BULK COLLECT INTO 子句中放入输出绑定变量，在 USING 子句中放入输入绑定变量。
如果动态 SQL 语句是没有 RETURNING INTO 子句的 DML 语句，将所有绑定变量放在 USING 子句中。
如果动态 SQL 语句是带有 RETURNING INTO 子句的 DML 语句，将输入绑定变量放在 USING 子句中，将输出绑定变量放在 RETURNING INTO 子句中。
如果动态 SQL 语句是匿名 PL/SQL 块或 CALL 语句，将所有绑定变量放在 USING 子句中。
如果动态 SQL 语句调用子程序，请确保：
对应于子程序参数的占位符的每个绑定变量都具有与该子程序参数相同的参数模式，并且数据类型与子程序参数的数据类型兼容。
没有绑定变量具有 SQL 不支持的数据类型。
USING 子句不能包含 NULL。
例子：Invoking Subprogram from Dynamic PL/SQL Block
-- Subprogram that dynamic PL/SQL block invokes:
CREATE OR REPLACE PROCEDURE create_dept (
deptid IN OUT NUMBER,
dname IN VARCHAR2,
mgrid IN NUMBER,
locid IN NUMBER
) AS
BEGIN
deptid := departments_seq.NEXTVAL;
INSERT INTO departments (
department_id,
department_name,
manager_id,
location_id
)
VALUES (deptid, dname, mgrid, locid);
END;
/
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
/* Specify bind variables in USING clause.
Specify mode for first parameter.
Modes of other parameters are correct by default. */
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
例子：Unsupported Data Type in Native Dynamic SQL
DECLARE
dyn_stmt VARCHAR2(200);
b1 BOOLEAN;
FUNCTION f (x INTEGER)
RETURN BOOLEAN
AS
BEGIN
NULL;
END f;
BEGIN
dyn_stmt := 'BEGIN :b := f(5); END;';
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;
/
输出：
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
*
ERROR at line 15:
ORA-06550: line 15, column 40:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored
例子：Uninitialized Variable Represents NULL in USING Clause
CREATE TABLE employees_temp AS SELECT * FROM EMPLOYEES;
DECLARE
a_null CHAR(1); -- Set to NULL automatically at run time
BEGIN
EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x'
USING a_null;
END;
/
OPEN FOR, FETCH, and CLOSE Statements
如果动态 SQL 语句为返回多行的 SELECT 语句，则可以使用原生动态 SQL 对其进行处理，如下所示：
使用 OPEN FOR 语句将游标变量与动态 SQL 语句相关联。在 OPEN FOR 语句的 USING 子句中，为动态 SQL 语句中的每个占位符指定一个绑定变量。
使用 FETCH 语句可以一次检索一行、一次检索几行或一次检索所有结果集。
使用 CLOSE 语句关闭游标变量。
例子：Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record employees%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job employees.job%TYPE;
BEGIN
-- Dynamic SQL statement with placeholder:
v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';
-- Open cursor & specify bind variable in USING clause:
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
-- Fetch rows from result set one at a time:
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
-- Close cursor:
CLOSE v_emp_cursor;
END;
/
Subprograms
PL/SQL 子程序是一个命名的 PL/SQL 块，可以重复调用。
子程序可以是存储过程（procedure）或者函数（function）。通常，使用存储过程来执行操作，使用函数来计算和返回值。
可以在 PL/SQL 块、包内或模式级别创建子程序。
- 在 PL/SQL 块内创建的子程序是嵌套子程序（nested subprogram）。
- 在包内创建的子程序是包子程序（package subprogram）。
- 在模式级别创建的子程序是独立子程序（standalone subprogram）。可以使用 CREATE PROCEDURE 或 CREATE FUNCTION 语句创建。
- 创建独立子程序或包时，可以指定 AUTHID 属性，该属性会影响子程序在运行时发出的 SQL 语句的名称解析和权限检查。
创建 PROCEDURE 语法：
创建 FUNCTION 语法：
调用语法：
subprogram_name [ ( [ parameter [, parameter]... ] ) ]
Subprogram Parts
子程序以子程序标题开头，指定其名称和（可选）参数列表。
与匿名块一样，子程序具有以下部分：
- 声明部分（可选）
- 执行部分（必须）
- 异常处理（可选）
例子：Declaring, Defining, and Invoking a Simple PL/SQL Procedure
DECLARE
first_name employees.first_name%TYPE;
last_name employees.last_name%TYPE;
email employees.email%TYPE;
employer VARCHAR2(8) := 'AcmeCorp';
-- Declare and define procedure
PROCEDURE create_email ( -- Subprogram heading begins
name1 VARCHAR2,
name2 VARCHAR2,
company VARCHAR2
) -- Subprogram heading ends
IS
-- Declarative part begins
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN -- Executable part begins
email := name1 || '.' || name2 || '@' || company;
EXCEPTION -- Exception-handling part begins
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(error_message);
END create_email;
BEGIN
first_name := 'John';
last_name := 'Doe';
create_email(first_name, last_name, employer); -- invocation
DBMS_OUTPUT.PUT_LINE ('With first name first, email is: ' || email);
create_email(last_name, first_name, employer); -- invocation
DBMS_OUTPUT.PUT_LINE ('With last name first, email is: ' || email);
first_name := 'Elizabeth';
last_name := 'MacDonald';
create_email(first_name, last_name, employer); -- invocation
END;
/
输出：
With first name first, email is: John.Doe@AcmeCorp
With last name first, email is: Doe.John@AcmeCorp
Email address is too long.
Additional Parts for Functions
函数具有与存储过程相同的结构，不同之处在于：
函数标题必须包含 RETURN 子句，指定函数返回值的数据类型。
函数的执行部分中每个执行路径必须指向一个 RETURN 语句。
只有函数标题可以包含以下选项：
| Option | Description |
|---|---|
DETERMINISTIC option | Helps the optimizer avoid redundant function invocations. |
PARALLEL_ENABLE option | Enables the function for parallel execution, making it safe for use in slave sessions of parallel DML evaluations. |
PIPELINED option | Makes a table function pipelined, for use as a row source. |
RESULT_CACHE option | Stores function results in the PL/SQL function result cache (appears only in declaration). |
RESULT_CACHE clause | Stores function results in the PL/SQL function result cache (appears only in definition). |
例子：Declaring, Defining, and Invoking a Simple PL/SQL Function
DECLARE
-- Declare and define function
FUNCTION square (original NUMBER) -- parameter list
RETURN NUMBER -- RETURN clause
AS
-- Declarative part begins
original_squared NUMBER;
BEGIN -- Executable part begins
original_squared := original * original;
RETURN original_squared; -- RETURN statement
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(square(100)); -- invocation
END;
/
输出：
10000
RETURN Statement
RETURN 语句立即结束子程序或匿名块的执行。子程序或匿名块可以包含多个 RETURN 语句。
（1）在函数中，每个执行路径都必须指向一个 RETURN 语句，每个 RETURN 语句都必须指定一个表达式。RETURN 语句将表达式的值分配给函数标识符，并将控制权返回给调用程序。
例子：Execution Resumes After RETURN Statement in Function
DECLARE
x INTEGER;
FUNCTION f (n INTEGER)
RETURN INTEGER
IS
BEGIN
RETURN (n*n);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE (
'f returns ' || f(2) || '. Execution returns here (1).'
);
x := f(2);
DBMS_OUTPUT.PUT_LINE('Execution returns here (2).');
END;
/
f returns 4. Execution returns here (1).Execution returns here (2).
例子：Function Where Not Every Execution Path Leads to RETURN Statement
CREATE OR REPLACE FUNCTION f (n INTEGER)
RETURN INTEGER
IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n = 1 THEN
RETURN n;
END IF;
END;
/
例子：Function Where Every Execution Path Leads to RETURN Statement
CREATE OR REPLACE FUNCTION f (n INTEGER)
RETURN INTEGER
IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n = 1 THEN
RETURN n;
ELSE
RETURN n*n;
END IF;
END;
/
BEGIN
FOR i IN 0 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE('f(' || i || ') = ' || f(i));
END LOOP;
END;
/
输出：
f(0) = 1
f(1) = 1
f(2) = 4
f(3) = 9
（2）在存储过程中，RETURN 语句将控制权返回给调用程序，调用程序在调用后立即恢复执行。RETURN 语句不能指定表达式。
例子：Execution Resumes After RETURN Statement in Procedure
DECLARE
PROCEDURE p IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inside p');
RETURN;
DBMS_OUTPUT.PUT_LINE('Unreachable statement.');
END;
BEGIN
p;
DBMS_OUTPUT.PUT_LINE('Control returns here.');
END;
/
输出：
Inside p
Control returns here.
（3）在匿名块中，RETURN 语句退出自身块和所有封闭块。RETURN 语句不能指定表达式。
例子：Execution Resumes After RETURN Statement in Anonymous Block
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE('Inside inner block.');
RETURN;
DBMS_OUTPUT.PUT_LINE('Unreachable statement.');
END;
DBMS_OUTPUT.PUT_LINE('Inside outer block. Unreachable statement.');
END;
/
输出：
Inside inner block.
Forward Declaration
如果 PL/SQL 块中的嵌套子程序相互调用，则需要前置声明，因为必须先声明子程序才能调用。
使用前置声明来声明嵌套子程序，稍后必须在同一块中定义。
前置声明和定义必须具有相同的子程序标题。
例子：Nested Subprograms Invoke Each Other
DECLARE
-- Declare proc1 (forward declaration):
PROCEDURE proc1(number1 NUMBER);
-- Declare and define proc2:
PROCEDURE proc2(number2 NUMBER) IS
BEGIN
proc1(number2);
END;
-- Define proc 1:
PROCEDURE proc1(number1 NUMBER) IS
BEGIN
proc2 (number1);
END;
BEGIN
NULL;
END;
/
Subprogram Parameters
Formal and Actual Subprogram Parameters
可以在子程序标题中声明形参，指定参数的名称和数据类型，以及（可选）模式和默认值。
在子程序的执行部分，按名称引用形参。
调用子程序时，指定实参。实参和形参必须具有兼容的数据类型。
例子：Formal Parameters and Actual Parameters
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6) := 100;
merit NUMBER(4) := 50;
PROCEDURE raise_salary (
emp_id NUMBER, -- formal parameter
amount NUMBER -- formal parameter
) IS
BEGIN
UPDATE employees
SET salary = salary + amount -- reference to formal parameter
WHERE employee_id = emp_id; -- reference to formal parameter
END raise_salary;
BEGIN
raise_salary(emp_num, bonus); -- actual parameters
/* raise_salary runs this statement:
UPDATE employees
SET salary = salary + 100
WHERE employee_id = 120; */
raise_salary(emp_num, merit + bonus); -- actual parameters
/* raise_salary runs this statement:
UPDATE employees
SET salary = salary + 150
WHERE employee_id = 120; */
END;
/
Formal Parameters of Constrained Subtypes
如果形参的数据类型是受约束的子类型，则：
如果子类型具有 NOT NULL 约束，则实参将继承它。
如果子类型具有基本类型 VARCHAR2，则实参不会继承子类型的大小。
例子：Actual Parameter Inherits Only NOT NULL from Subtype
DECLARE
SUBTYPE License IS VARCHAR2(7) NOT NULL;
n License := 'DLLLDDD';
PROCEDURE p (x License) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(x);
END;
BEGIN
p('1ABC123456789'); -- Succeeds; size is not inherited
p(NULL); -- Raises error; NOT NULL is inherited
END;
/
输出：
p(NULL); -- Raises error; NOT NULL is inherited
*
ERROR at line 12:
ORA-06550: line 12, column 5:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
如果子类型具有数值基本类型，则实参将继承子类型的范围，但不继承精度或小数位数。
例子：Actual Parameter and Return Value Inherit Only Range From Subtype
DECLARE
FUNCTION test (p INTEGER) RETURN INTEGER IS
BEGIN
DBMS_OUTPUT.PUT_LINE('p = ' || p);
RETURN p;
END test;
BEGIN
DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66));
END;
/
输出：
p = .66
test(p) = .66
例子：Function Implicitly Converts Formal Parameter to Constrained Subtype
DECLARE
FUNCTION test (p NUMBER) RETURN NUMBER IS
q INTEGER := p; -- Implicitly converts p to INTEGER
BEGIN
DBMS_OUTPUT.PUT_LINE('p = ' || q); -- Display q, not p
RETURN q; -- Return q, not p
END test;
BEGIN
DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66));
END;
/
输出：
p = 1
test(p) = 1
Subprogram Parameter Passing Methods
PL/SQL 编译器有两种将实参传递给子程序的方法：
通过引用：编译器向子程序传递指向实参的指针。实参和形参引用相同的内存位置。
通过值：编译器将实参的值赋予给相应的形参。实参和形参是指不同的内存位置。
如有必要，编译器会将实参的数据类型隐式转换为形参的数据类型。
例子：Avoiding Implicit Conversion of Actual Parameters
CREATE OR REPLACE PROCEDURE p (
n NUMBER
) IS
BEGIN
NULL;
END;
/
DECLARE
x NUMBER := 1;
y VARCHAR2(1) := '1';
BEGIN
p(x); -- No conversion needed
p(y); -- z implicitly converted from VARCHAR2 to NUMBER
p(TO_NUMBER(y)); -- z explicitly converted from VARCHAR2 to NUMBER
END;
/
Subprogram Parameter Modes
子程序参数模式：
| IN | OUT | IN OUT |
|---|---|---|
| Default mode | Must be specified. | Must be specified. |
| Passes a value to the subprogram. | Returns a value to the invoker. | Passes an initial value to the subprogram and returns an updated value to the invoker. |
| Formal parameter acts like a constant: When the subprogram begins, its value is that of either its actual parameter or default value, and the subprogram cannot change this value. | Formal parameter is initialized to the default value of its type. The default value of the type is NULL except for a record type with a non-NULL default value.When the subprogram begins, the formal parameter has its initial value regardless of the value of its actual parameter. Oracle recommends that the subprogram assign a value to the formal parameter. | Formal parameter acts like an initialized variable: When the subprogram begins, its value is that of its actual parameter. Oracle recommends that the subprogram update its value. |
| Actual parameter can be a constant, initialized variable, literal, or expression. | If the default value of the formal parameter type is NULL, then the actual parameter must be a variable whose data type is not defined as NOT NULL. | Actual parameter must be a variable (typically, it is a string buffer or numeric accumulator). |
| Actual parameter is passed by reference. | By default, actual parameter is passed by value; if you specify NOCOPY, it might be passed by reference. | By default, actual parameter is passed by value (in both directions); if you specify NOCOPY, it might be passed by reference. |
注意：不要将 OUT 和 IN OUT 用于函数参数
无论 OUT 或 IN OUT 参数如何传递：
如果子程序成功退出，则实参的值是赋予给形参的最终值。
如果子程序异常结束，则实参的值未定义。
例子：Parameter Values Before, During, and After Procedure Invocation
CREATE OR REPLACE PROCEDURE print (x PLS_INTEGER) IS
BEGIN
IF x IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(x);
ELSE
DBMS_OUTPUT.PUT_LINE('NULL');
END IF;
END print;
/
CREATE OR REPLACE PROCEDURE p (
a PLS_INTEGER, -- IN by default
b IN PLS_INTEGER,
c OUT PLS_INTEGER,
d IN OUT BINARY_FLOAT
) IS
BEGIN
-- Print values of parameters:
DBMS_OUTPUT.PUT_LINE('Inside procedure p:');
DBMS_OUTPUT.PUT('IN a = '); print(a);
DBMS_OUTPUT.PUT('IN b = '); print(b);
DBMS_OUTPUT.PUT('OUT c = '); print(c);
DBMS_OUTPUT.PUT_LINE('IN OUT d = ' || TO_CHAR(d));
-- Can reference IN parameters a and b,
-- but cannot assign values to them.
c := a+10; -- Assign value to OUT parameter
d := 10/b; -- Assign value to IN OUT parameter
END;
/
DECLARE
aa CONSTANT PLS_INTEGER := 1;
bb PLS_INTEGER := 2;
cc PLS_INTEGER := 3;
dd BINARY_FLOAT := 4;
ee PLS_INTEGER;
ff BINARY_FLOAT := 5;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:');
DBMS_OUTPUT.PUT('aa = '); print(aa);
DBMS_OUTPUT.PUT('bb = '); print(bb);
DBMS_OUTPUT.PUT('cc = '); print(cc);
DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));
p (aa, -- constant
bb, -- initialized variable
cc, -- initialized variable
dd -- initialized variable
);
DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');
DBMS_OUTPUT.PUT('aa = '); print(aa);
DBMS_OUTPUT.PUT('bb = '); print(bb);
DBMS_OUTPUT.PUT('cc = '); print(cc);
DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));
DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:');
DBMS_OUTPUT.PUT('ee = '); print(ee);
DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff));
p (1, -- literal
(bb+3)*4, -- expression
ee, -- uninitialized variable
ff -- initialized variable
);
DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');
DBMS_OUTPUT.PUT('ee = '); print(ee);
DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff));
END;
/
输出：
Before invoking procedure p:
aa = 1
bb = 2
cc = 3
dd = 4.0E+000
Inside procedure p:
IN a = 1
IN b = 2
OUT c = NULL
IN OUT d = 4.0E+000
After invoking procedure p:
aa = 1
bb = 2
cc = 11
dd = 5.0E+000
Before invoking procedure p:
ee = NULL
ff = 5.0E+000
Inside procedure p:
IN a = 1
IN b = 20
OUT c = NULL
IN OUT d = 5.0E+000
After invoking procedure p:
ee = 11
ff = 5.0E-001
例子：OUT and IN OUT Parameter Values After Unhandled Exception
DECLARE
j PLS_INTEGER := 10;
k BINARY_FLOAT := 15;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:');
DBMS_OUTPUT.PUT('j = '); print(j);
DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k));
p(4, 0, j, k); -- causes p to exit with exception ZERO_DIVIDE
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('After invoking procedure p:');
DBMS_OUTPUT.PUT('j = '); print(j);
DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k));
END;
/
输出：
Before invoking procedure p:
j = 10
k = 1.5E+001
Inside procedure p:
IN a = 4
IN b = 0
OUT c = NULL
d = 1.5E+001
After invoking procedure p:
j = 10
k = 1.5E+001
例子：OUT Formal Parameter of Record Type with Non-NULL Default Value
CREATE OR REPLACE PACKAGE r_types AUTHID DEFINER IS
TYPE r_type_1 IS RECORD (f VARCHAR2(5) := 'abcde');
TYPE r_type_2 IS RECORD (f VARCHAR2(5));
END;
/
CREATE OR REPLACE PROCEDURE p (
x OUT r_types.r_type_1,
y OUT r_types.r_type_2,
z OUT VARCHAR2)
AUTHID DEFINER IS
BEGIN
DBMS_OUTPUT.PUT_LINE('x.f is ' || NVL(x.f,'NULL'));
DBMS_OUTPUT.PUT_LINE('y.f is ' || NVL(y.f,'NULL'));
DBMS_OUTPUT.PUT_LINE('z is ' || NVL(z,'NULL'));
END;
/
DECLARE
r1 r_types.r_type_1;
r2 r_types.r_type_2;
s VARCHAR2(5) := 'fghij';
BEGIN
p (r1, r2, s);
END;
/
输出：
x.f is abcde
y.f is NULL
z is NULL
Subprogram Parameter Aliasing
别名是指同一内存位置具有两个不同的名称。
当编译器通过引用传递实参时，总是会产生子程序参数别名；当子程序具有游标变量参数时，也会产生子程序参数别名。
Subprogram Parameter Aliasing with Parameters Passed by Reference
当编译器通过引用传递实参时，实参和形参引用相同的内存位置。因此，如果子程序更改了形参的值，则更改会立即显示在实参中。
编译器始终通过引用传递 IN 参数，子程序无法为 IN 参数赋值。
如果为 OUT 或 IN OUT 参数指定 NOCOPY，则编译器可能会通过引用传递该参数。NOCOPY 只是一个提示（hint）— 每次调用子程序时，编译器都会静默地决定是使用还是忽略 NOCOPY。因此，一个调用可能会出现别名，而另一个调用则不会，从而使子程序结果不确定。例如：
如果实参是全局变量，则全局参数中可能会显示对形参的赋值。
如果同一变量是两个形参的实参，则对任一形参的赋值可能会立即显示在两个形参中。
如果实参是包变量，则对形参或包变量的赋值可能会立即显示在形参和包变量中。
如果子程序因未处理的异常而退出，则在实参中可能会显示对形参的赋值。
例子：Aliasing from Global Variable as Actual Parameter
DECLARE
TYPE Definition IS RECORD (
word VARCHAR2(20),
meaning VARCHAR2(200)
);
TYPE Dictionary IS VARRAY(2000) OF Definition;
lexicon Dictionary := Dictionary(); -- global variable
PROCEDURE add_entry (
word_list IN OUT NOCOPY Dictionary -- formal NOCOPY parameter
) IS
BEGIN
word_list(1).word := 'aardvark';
END;
BEGIN
lexicon.EXTEND;
lexicon(1).word := 'aardwolf';
add_entry(lexicon); -- global variable is actual parameter
DBMS_OUTPUT.PUT_LINE(lexicon(1).word);
END;
/
输出：
aardvark
例子：Aliasing from Same Actual Parameter for Multiple Formal Parameters
DECLARE
n NUMBER := 10;
PROCEDURE p (
n1 IN NUMBER,
n2 IN OUT NUMBER,
n3 IN OUT NOCOPY NUMBER
) IS
BEGIN
n2 := 20; -- actual parameter is 20 only after procedure succeeds
DBMS_OUTPUT.put_line(n1); -- actual parameter value is still 10
n3 := 30; -- might change actual parameter immediately
DBMS_OUTPUT.put_line(n1); -- actual parameter value is either 10 or 30
END;
BEGIN
p(n, n, n);
DBMS_OUTPUT.put_line(n);
END;
/
如果编译器使用 NOCOPY hint
输出：
10
30
20
如果编译器忽略 NOCOPY hint
输出：
10
10
30
Subprogram Parameter Aliasing with Cursor Variable Parameters
游标变量参数是指针。因此，如果子程序将一个游标变量参数分配给另一个游标变量参数，则它们将引用相同的内存位置。这可能会产生意想不到的结果。
例子：Aliasing from Cursor Variable Subprogram Parameters
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
c1 EmpCurTyp;
c2 EmpCurTyp;
PROCEDURE get_emp_data (
emp_cv1 IN OUT EmpCurTyp,
emp_cv2 IN OUT EmpCurTyp
)
IS
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv1 FOR SELECT * FROM employees;
emp_cv2 := emp_cv1; -- now both variables refer to same location
FETCH emp_cv1 INTO emp_rec; -- fetches first row of employees
FETCH emp_cv1 INTO emp_rec; -- fetches second row of employees
FETCH emp_cv2 INTO emp_rec; -- fetches third row of employees
CLOSE emp_cv1; -- closes both variables
FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked
END;
BEGIN
get_emp_data(c1, c2);
END;
/
输出：
DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 19
ORA-06512: at line 22
Default Values for IN Subprogram Parameters
声明 IN 参数时，可以为其指定默认值。具有默认值的形参称为可选参数（optional parameter）。如果省略实参，则会将默认值分配给形参。没有默认值的形参称为必需参数（required parameter），在子程序调用时需要相应的实参。
省略实参不会使相应形参的值为 NULL。若要使形参的值为 NULL，需将 NULL 指定为默认值或实参。
例子：Procedure with Default Parameter Values
DECLARE
PROCEDURE raise_salary (
emp_id IN employees.employee_id%TYPE,
amount IN employees.salary%TYPE := 100,
extra IN employees.salary%TYPE := 50
) IS
BEGIN
UPDATE employees
SET salary = salary + amount + extra
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
raise_salary(120); -- same as raise_salary(120, 100, 50)
raise_salary(121, 200); -- same as raise_salary(121, 200, 50)
END;
/
形参的默认值可以是任何表达式。如果子程序调用为形参指定了实际参数，则该调用不会计算默认值。
例子：Function Provides Default Parameter Value
DECLARE
global PLS_INTEGER := 0;
FUNCTION f RETURN PLS_INTEGER IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inside f.');
global := global + 1;
RETURN global * 2;
END f;
PROCEDURE p (
x IN PLS_INTEGER := f()
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE (
'Inside p. ' ||
' global = ' || global ||
', x = ' || x || '.'
);
DBMS_OUTPUT.PUT_LINE('--------------------------------');
END p;
PROCEDURE pre_p IS
BEGIN
DBMS_OUTPUT.PUT_LINE (
'Before invoking p, global = ' || global || '.'
);
DBMS_OUTPUT.PUT_LINE('Invoking p.');
END pre_p;
BEGIN
pre_p;
p(); -- default expression is evaluated
pre_p;
p(100); -- default expression is not evaluated
pre_p;
p(); -- default expression is evaluated
END;
/
输出：
Before invoking p, global = 0.
Invoking p.
Inside f.
Inside p. global = 1, x = 2.
--------------------------------
Before invoking p, global = 1.
Invoking p.
Inside p. global = 1, x = 100.
--------------------------------
Before invoking p, global = 1.
Invoking p.
Inside f.
Inside p. global = 2, x = 4.
--------------------------------
例子：Adding Subprogram Parameter Without Changing Existing Invocations
创建存储过程：
CREATE OR REPLACE PROCEDURE print_name (
first VARCHAR2,
last VARCHAR2
) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(first || ' ' || last);
END print_name;
/
调用存储过程：
BEGIN
print_name('John', 'Doe');
END;
/
输出：
John Doe
增加带默认值的参数：
CREATE OR REPLACE PROCEDURE print_name (
first VARCHAR2,
last VARCHAR2,
mi VARCHAR2 := NULL
) IS
BEGIN
IF mi IS NULL THEN
DBMS_OUTPUT.PUT_LINE(first || ' ' || last);
ELSE
DBMS_OUTPUT.PUT_LINE(first || ' ' || mi || '. ' || last);
END IF;
END print_name;
/
调用存储过程：
BEGIN
print_name('John', 'Doe'); -- original invocation
print_name('John', 'Public', 'Q'); -- new invocation
END;
/
输出：
John Doe
John Q. Public
Positional, Named, and Mixed Notation for Actual Parameters
调用子程序时，可以使用位置、命名或混合表示法指定实参。
| Positional | Named | Mixed |
|---|---|---|
| Specify the actual parameters in the same order as the formal parameters are declared. | Specify the actual parameters in any order, using this syntax:formal => actual formal is the name of the formal parameter and actual is the actual parameter. | Start with positional notation, then use named notation for the remaining parameters. |
| You can omit trailing optional parameters. | You can omit any optional parameters. | In the positional notation, you can omit trailing optional parameters; in the named notation, you can omit any optional parameters. |
| Specifying actual parameters in the wrong order can cause problems that are hard to detect, especially if the actual parameters are literals. | There is no wrong order for specifying actual parameters. | In the positional notation, the wrong order can cause problems that are hard to detect, especially if the actual parameters are literals. |
| Subprogram invocations must change if the formal parameter list changes, unless the list only acquires new trailing optional parameters . | Subprogram invocations must change only if the formal parameter list acquires new required parameters. | Changes to the formal parameter list might require changes in the positional notation. |
| Recommended when you invoke a subprogram defined or maintained by someone else. | Convenient when you invoke a subprogram that has required parameters followed by optional parameters, and you must specify only a few of the optional parameters. |
例子：Equivalent Invocations with Different Notations in Anonymous Block
DECLARE
emp_num NUMBER(6) := 120;
bonus NUMBER(6) := 50;
PROCEDURE raise_salary (
emp_id NUMBER,
amount NUMBER
) IS
BEGIN
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
END raise_salary;
BEGIN
-- Equivalent invocations:
raise_salary(emp_num, bonus); -- positional notation
raise_salary(amount => bonus, emp_id => emp_num); -- named notation
raise_salary(emp_id => emp_num, amount => bonus); -- named notation
raise_salary(emp_num, amount => bonus); -- mixed notation
END;
/
例子：Equivalent Invocations with Different Notations in SELECT Statements
CREATE OR REPLACE FUNCTION compute_bonus (
emp_id NUMBER,
bonus NUMBER
) RETURN NUMBER
IS
emp_sal NUMBER;
BEGIN
SELECT salary INTO emp_sal
FROM employees
WHERE employee_id = emp_id;
RETURN emp_sal + bonus;
END compute_bonus;
/
SELECT compute_bonus(120, 50) FROM DUAL; -- positional
SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named
SELECT compute_bonus(120, bonus => 50) FROM DUAL; -- mixed
Overloaded Subprograms
PL/SQL 可以重载嵌套子程序、包子程序和类型方法。如果子程序形参的名称、数量、顺序或数据类型族不同，则可以使用相同的子程序名称。
例子：Overloaded Subprogram
DECLARE
TYPE date_tab_typ IS TABLE OF DATE INDEX BY PLS_INTEGER;
TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
hiredate_tab date_tab_typ;
sal_tab num_tab_typ;
PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Invoked first version');
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Invoked second version');
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
BEGIN
initialize(hiredate_tab, 50);
initialize(sal_tab, 100);
END;
/
输出：
Invoked first version
Invoked second version
Recursive Subprograms
递归子程序调用自身。
递归子程序必须至少有两个执行路径，一个递归调用，一个终止条件。
例子：Recursive Function Returns n Factorial (n!)
CREATE OR REPLACE FUNCTION factorial (
n POSITIVE
) RETURN POSITIVE
IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN n;
ELSE
RETURN n * factorial(n-1); -- recursive invocation
END IF;
END;
/
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i || '! = ' || factorial(i));
END LOOP;
END;
/
输出：
1! = 1
2! = 2
3! = 6
4! = 24
5! = 120
例子：Recursive Function Returns nth Fibonacci Number
CREATE OR REPLACE FUNCTION fibonacci (
n PLS_INTEGER
) RETURN PLS_INTEGER
IS
fib_1 PLS_INTEGER := 0;
fib_2 PLS_INTEGER := 1;
BEGIN
IF n = 1 THEN -- terminating condition
RETURN fib_1;
ELSIF n = 2 THEN
RETURN fib_2; -- terminating condition
ELSE
RETURN fibonacci(n-2) + fibonacci(n-1); -- recursive invocations
END IF;
END;
/
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT(fibonacci(i));
IF i < 10 THEN
DBMS_OUTPUT.PUT(', ');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ...');
END;
/
输出：
0, 1, 1, 2, 3, 5, 8, 13, 21, 34 ...
游标 FOR LOOP 语句内或 OPEN FOR 语句与 CLOSE 语句之间的递归调用会在每次调用时打开另一个游标，这可能会导致打开游标数超过数据库初始化参数 OPEN_CURSORS 设置的限制。
Function Result Cache
PL/SQL 函数结果缓存机制将 PL/SQL 函数的结果缓存在全局共享区域 （SGA） 中。
例子：Declaring and Defining Result-Cached Function
CREATE OR REPLACE PACKAGE department_pkg IS
TYPE dept_info_record IS RECORD (
dept_name departments.department_name%TYPE,
mgr_name employees.last_name%TYPE,
dept_size PLS_INTEGER
);
-- Function declaration
FUNCTION get_dept_info (dept_id PLS_INTEGER)
RETURN dept_info_record
RESULT_CACHE;
END department_pkg;
/
CREATE OR REPLACE PACKAGE BODY department_pkg IS
-- Function definition
FUNCTION get_dept_info (dept_id PLS_INTEGER)
RETURN dept_info_record
RESULT_CACHE RELIES_ON (DEPARTMENTS, EMPLOYEES)
IS
rec dept_info_record;
BEGIN
SELECT department_name INTO rec.dept_name
FROM departments
WHERE department_id = dept_id;
SELECT e.last_name INTO rec.mgr_name
FROM departments d, employees e
WHERE d.department_id = dept_id
AND d.manager_id = e.employee_id;
SELECT COUNT(*) INTO rec.dept_size
FROM EMPLOYEES
WHERE department_id = dept_id;
RETURN rec;
END get_dept_info;
END department_pkg;
/
Triggers
触发器类似于在发生指定事件时自动调用的存储过程。
与存储过程一样，触发器是存储在数据库中并且可以重复调用的命名 PL/SQL 单元。与存储过程不同的是，可以启用和禁用触发器，但不能显式调用。启用触发器时，每当发生触发事件时，数据库都会自动调用。禁用触发后，则不会触发。
使用 CREATE TRIGGER 语句创建触发器。根据触发语句及其作用的项目来指定触发事件。触发器可以创建在表、视图、模式或数据库上。还可以指定触发点，确定是在触发语句运行之前还是之后触发，以及是否针对触发语句影响的每一行进行触发。默认情况下，触发器是在启用状态。
创建触发器语法：
plsql_trigger_source：
如果触发器创建在表或视图上，则触发事件由 DML 语句组成，该触发器称为 DML 触发器。
如果触发器创建在模式或数据库上，则触发事件由 DDL 或数据库操作语句组成，该触发器称为系统触发器。
条件触发器具有一个 WHEN 子句，该子句指定 SQL 条件。
INSTEAD OF 触发器为：
在非版本视图或非版本视图的嵌套表列上创建的 DML 触发器
CREATE 语句定义的系统触发器
DML Triggers
DML 触发器创建在表或视图上，其触发事件由 DML 语句 DELETE、INSERT 和 UPDATE 组成。若要创建响应 MERGE 语句的触发器，需在 MERGE 操作分解到的 INSERT 和 UPDATE 语句上创建触发器。
简单 DML 触发器触发点：
在触发语句运行之前，称为语句级 BEFORE 触发器。
在触发语句运行之后，称为语句级 AFTER 触发器。
在触发语句影响的每一行之前，称为行级 BEFORE 触发器。
在触发语句影响的每一行之后，称为行级 AFTER 触发器。
在表或版本视图上创建的复合 DML 触发器可以在上述触发点触发。
在行级别触发的简单或复合 DML 触发器可以访问正在处理的行中的数据。
INSTEAD OF DML 触发器是在非版本视图或非版本视图的嵌套表列上创建的 DML 触发器。
除了 INSTEAD OF 触发器，触发的 UPDATE 语句可以包含字段列表。对于字段列表，触发器仅在更新指定的字段时触发。如果没有指定字段列表，则在更新关联表的任何字段时触发。
简单 DML 触发器语法：
Conditional Predicates for Detecting Triggering DML Statement
DML 触发器的触发事件可以由多个触发语句组成。使用以下条件谓词确定哪一个触发语句：
| Conditional Predicate | TRUE if and only if: |
|---|---|
INSERTING | An INSERT statement fired the trigger. |
UPDATING | An UPDATE statement fired the trigger. |
UPDATING ('column') | An UPDATE statement that affected the specified column fired the trigger. |
DELETING | A DELETE statement fired the trigger. |
例子：Trigger Uses Conditional Predicates to Detect Triggering Statement
CREATE OR REPLACE TRIGGER t
BEFORE
INSERT OR
UPDATE OF salary, department_id OR
DELETE
ON employees
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/
Correlation Names and Pseudorecords
行级别触发可以使用 OLD，NEW 和 PARENT 访问正在处理的数据。
如果触发器创建在视图中的嵌套表上，则 OLD 和 NEW 引用嵌套表的当前行，PARENT 引用父表的当前行。如果触发器创建在表或视图上，则 OLD 和 NEW 引用表或视图的当前行，PARENT 未定义。
OLD、NEW 和 PARENT 也称为伪记录，因为它们具有记录结构。伪记录的结构是 table_name%ROWTYPE，其中table_name是在其上创建触发器的表的名称（对于 OLD 和 NEW）或父表的名称（对于 PARENT）。
使用以下语法引用伪记录的字段：
:pseudorecord_name.field_name
OLD 和 NEW 伪记录字段值：
| Triggering Statement | OLD.field Value | NEW.field Value |
|---|---|---|
INSERT | NULL | Post-insert value |
UPDATE | Pre-update value | Post-update value |
DELETE | Pre-delete value | NULL |
例子：Trigger Logs Changes to EMPLOYEES.SALARY
创建表：
DROP TABLE Emp_log;
CREATE TABLE Emp_log (
Emp_id NUMBER,
Log_date DATE,
New_salary NUMBER,
Action VARCHAR2(20));
创建触发器：
CREATE OR REPLACE TRIGGER log_salary_increase
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/
更新数据：
UPDATE employees
SET salary = salary + 1000.0
WHERE Department_id = 20;
查看日志：
SELECT * FROM Emp_log;
EMP_ID LOG_DATE NEW_SALARY ACTION
---------- --------- ---------- --------------------
201 28-APR-10 15049.13 New Salary
202 28-APR-10 6945.75 New Salary
2 rows selected.
例子：Conditional Trigger Prints Salary Change Information
创建触发器：
CREATE OR REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES') -- do not print information about President
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/
查询：
SELECT last_name, department_id, salary, job_id
FROM employees
WHERE department_id IN (10, 20, 90)
ORDER BY department_id, last_name;
LAST_NAME DEPARTMENT_ID SALARY JOB_ID
------------------------- ------------- ---------- ----------
Whalen 10 2800 AD_ASST
Fay 20 6000 MK_REP
Hartstein 20 13000 MK_MAN
De Haan 90 17000 AD_VP
King 90 24000 AD_PRES
Kochhar 90 17000 AD_VP
6 rows selected.
触发语句：
UPDATE employees
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 90);
Whalen: Old salary = 2800, New salary = 2940, Difference: 140
Hartstein: Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old salary = 17000, New salary = 17850, Difference: 850
De Haan: Old salary = 17000, New salary = 17850, Difference: 850
6 rows updated.
例子：Trigger Modifies LOB Columns
DROP TABLE tab1;
CREATE TABLE tab1 (c1 CLOB);
INSERT INTO tab1 VALUES ('<h1>HTML Document Fragment</h1><p>Some text.');
CREATE OR REPLACE TRIGGER trg1
BEFORE UPDATE ON tab1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Old value of CLOB column: '||:OLD.c1);
DBMS_OUTPUT.PUT_LINE('Proposed new value of CLOB column: '||:NEW.c1);
:NEW.c1 := :NEW.c1 || TO_CLOB('<hr><p>Standard footer paragraph.');
DBMS_OUTPUT.PUT_LINE('Final value of CLOB column: '||:NEW.c1);
END;
/
SET SERVEROUTPUT ON;
UPDATE tab1 SET c1 = '<h1>Different Document Fragment</h1><p>Different text.';
SELECT * FROM tab1;
INSTEAD OF DML Triggers
使用 INSTEAD OF 触发器的一个常用场景是对视图的操作，如果一个视图由多个基表组成，则该视图不允许进行 INSERT、UPDATE 和 DELETE 这样的 DML 操作。当在视图上编写 INSTEAD OF 触发器后，用户对视图的 DML 操作就不会执行，而是执行触发器中的 PL/SQL 语句块，这样就可以通过在替代触发器中编写适当的代码，完成对组成视图的各个表进行操作。
INSTEAD OF DML 触发器语法：
例子：INSTEAD OF Trigger
CREATE OR REPLACE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
查询：
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
COUNT(*)
----------
0
1 row selected.
插入：
INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
1 row created.
查询：
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
COUNT(*)
----------
1
1 row selected.
SELECT COUNT(*) FROM customers WHERE customer_id = 999;
COUNT(*)
----------
1
1 row selected.
SELECT COUNT(*) FROM orders WHERE customer_id = 999;
COUNT(*)
----------
1
1 row selected.
Compound DML Triggers
在表或版本视图上创建的复合 DML 触发器可以有多个触发点。每个触发部分都有可执行部分和可选的异常处理部分。
复合触发器的两个常见用途是：
批量插入数据
避免变异表（定义该触发器的表）错误 （ORA-04091）
语法：
| Timing Point | Section |
|---|---|
| Before the triggering statement runs | BEFORE STATEMENT |
| After the triggering statement runs | AFTER STATEMENT |
| Before each row that the triggering statement affects | BEFORE EACH ROW |
| After each row that the triggering statement affects | AFTER EACH ROW |
如果复合 DML 触发器既没有 BEFORE 语句也没有 AFTER 语句，并且其触发语句不影响任何行，则触发器永远不会触发。
复合 DML 触发器具有以下限制：
OLD、NEW 和 PARENT 不能出现在声明部分、BEFORE STATEMENT 部分或 AFTER STATEMENT 部分。
只有在 BEFORE EACH ROW 部分才能更改 NEW 值。
一个触发部分无法处理另一个触发部分中引发的异常。
如果某部分包含 GOTO 语句，则 GOTO 语句的目标必须位于同一部分中。
Using Compound DML Triggers with Bulk Insertion
复合 DML 触发器在使用批量 SQL 时具有更大的性能优势。
若要从复合触发器中获得性能优势，必须在 FORALL 语句中指定 BULK COLLECT INTO （否则，FORALL 语句会多次执行单行 DML 操作）。
例子：Compound Trigger Logs Changes to One Table in Another Table
CREATE TABLE employee_salaries (
employee_id NUMBER NOT NULL,
change_date DATE NOT NULL,
salary NUMBER(8,2) NOT NULL,
CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
REFERENCES employees (employee_id)
ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
-- Declarative Part:
-- Choose small threshhold value to show how example works:
threshhold CONSTANT SIMPLE_INTEGER := 7;
TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
salaries salaries_t;
idx SIMPLE_INTEGER := 0;
PROCEDURE flush_array IS
n CONSTANT SIMPLE_INTEGER := salaries.count();
BEGIN
FORALL j IN 1..n
INSERT INTO employee_salaries VALUES salaries(j);
salaries.delete();
idx := 0;
DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
END flush_array;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
idx := idx + 1;
salaries(idx).employee_id := :NEW.employee_id;
salaries(idx).change_date := SYSDATE();
salaries(idx).salary := :NEW.salary;
IF idx >= threshhold THEN
flush_array();
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
flush_array();
END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 50
/
/* Wait two seconds: */
BEGIN
DBMS_LOCK.SLEEP(2);
END;
/
/* Increase salary of every employee in department 50 by 5%: */
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 50
/
Using Compound DML Triggers to Avoid Mutating-Table Error
复合 DML 触发器可用于避免变异表错误 （ORA-04091），就是在表的行级（FOR EACH ROW）触发器中不能对该表进行DML操作，可用两个触发器（一个行级，一个语句级）解决。
例子：Compound Trigger Avoids Mutating-Table Error
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
-- Declare collection type and variable:
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;
System Triggers
系统触发器创建在模式或数据库上。其触发事件由 DDL 语句或数据库操作语句组成。
语法：
ddl_event：
- ALTER
- ANALYZE
- AUDIT
- COMMENT
- CREATE
- DISASSOCIATE STATISTICS
- DROP
- GRANT
- NOAUDIT
- RENAME
- REVOKE
- TRUNCATE
database_event：
- AFTER STARTUP
- BEFORE SHUTDOWN
- AFTER DB_ROLE_CHANGE
- AFTER SERVERERROR
- AFTER LOGON
- BEFORE LOGOFF
- AFTER SUSPEND
SCHEMA Triggers
SCHEMA 触发器是创建在模式上。
例子：BEFORE Statement Trigger on Sample Schema HR
CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop object');
END;
/
DATABASE Triggers
DATABASE 触发器是创建在数据库上。
例子：AFTER Statement Trigger on Database
CREATE TRIGGER log_errors
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
NULL; -- (substitute code that processes logon error)
ELSE
NULL; -- (substitute code that logs error code)
END IF;
END;
/
例子：Trigger Monitors Logons
CREATE OR REPLACE TRIGGER check_user
AFTER LOGON ON DATABASE
BEGIN
check_user;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);
END;
/
INSTEAD OF CREATE Triggers
INSTEAD OF CREATE 触发器是一个 SCHEMA 触发器，只在 CREATE 语句上触发。
例子：INSTEAD OF CREATE Trigger on Schema
CREATE OR REPLACE TRIGGER t
INSTEAD OF CREATE ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';
END;
/
Order in Which Triggers Fire
如果为同一表上的同一语句定义了两个或多个触发器，则按以下顺序触发：
所有 BEFORE STATEMENT 触发器
所有 BEFORE EACH ROW 触发器
所有 AFTER EACH ROW 触发器
所有 AFTER STATEMENT 触发器
最佳实践是使用复合触发器替换这些触发器。
使用 FOLLOW 和 PRECEDES 子句控制相同触发条件的触发顺序。
复合触发器的触发可以与简单触发器的触发交错触发。
当一个触发器导致另一个触发器触发时，这些触发器被称为级联触发器。数据库最多允许同时级联 32 个触发器。
Trigger Enabling and Disabling
默认情况下，CREATE TRIGGER 语句创建处于启用状态的触发器。若要创建处于禁用状态的触发器，需指定 DISABLE。
临时禁用触发器的一些原因包括：
触发器引用不可用的对象。
提供大数据量加载性能。
启用或禁用触发器语法：
ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
启用或禁用表上的所有触发器：
ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
Views for Information About Triggers
使用 *_TRIGGERS 数据字典视图查询触发器信息。
例子：Viewing Information About Triggers
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON employees
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM employees;
DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.');
END;
/
COLUMN Trigger_type FORMAT A15
COLUMN Triggering_event FORMAT A16
COLUMN Table_name FORMAT A11
COLUMN Trigger_body FORMAT A50
SET LONG 9999
SELECT Trigger_type, Triggering_event, Table_name
FROM USER_TRIGGERS
WHERE Trigger_name = 'EMP_COUNT';
TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME
--------------- ---------------- -----------
AFTER STATEMENT DELETE EMPLOYEES
SELECT Trigger_body
FROM USER_TRIGGERS
WHERE Trigger_name = 'EMP_COUNT';
TRIGGER_BODY
--------------------------------------------------
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM employees;
DBMS_OUTPUT.PUT_LINE('There are now ' || n || '
employees.');
END;
1 row selected.
Packages
包是一个模式对象，包含逻辑上相关的 PL/SQL 类型、变量、常量、子程序、游标和异常。包被编译并存储在数据库中，可以将包视为应用程序。
包必须有规范（specification），用于声明可从包外部引用的公共项（public items）。可以将包规范视为应用程序编程接口 （API）。
如果公共项包括游标或子程序，则包还必须有包体（body）。包体必须定义公共游标的查询和公共子程序的代码。
注意：不能从包内部引用主机变量。
Package Specification
包规范（package specification）声明公共项（public items）。
每个公共项声明都包含使用该项所需的所有信息。例如，声明函数阶乘：
FUNCTION factorial (n INTEGER) RETURN INTEGER; -- returns n!
Appropriate Public Items
合适的公共项有：
多个子程序使用的类型、变量、常量、子程序、游标和异常。
独立子程序参数的关联数组类型，不能在模式级别声明关联数组类型。因此，若要将关联数组变量作为参数传递给独立子程序，必须在包规范中声明该变量的类型。这样做会使该类型既可用于被调用的子程序（声明该类型的形参），也可用于调用子程序或匿名块（声明该类型的变量）。
在同一会话中的子程序调用之间必须保持可用的变量。
读取和写入公共变量的子程序（“get”和“set”子程序），提供这些子程序来阻止包用户直接读取和写入公共变量。
相互调用的子程序。
重载子程序
Creating Package Specifications
语法：
例子：Simple Package Specification
CREATE OR REPLACE PACKAGE trans_data AS
TYPE TimeRec IS RECORD (
minutes SMALLINT,
hours SMALLINT);
TYPE TransRec IS RECORD (
category VARCHAR2(10),
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;
/
例子：Passing Associative Array to Standalone Subprogram
CREATE OR REPLACE PACKAGE aa_pkg IS
TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);
END;
/
CREATE OR REPLACE PROCEDURE print_aa (
aa aa_pkg.aa_type
) IS
i VARCHAR2(15);
BEGIN
i := aa.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i);
i := aa.NEXT(i);
END LOOP;
END;
/
DECLARE
aa_var aa_pkg.aa_type;
BEGIN
aa_var('zero') := 0;
aa_var('one') := 1;
aa_var('two') := 2;
print_aa(aa_var);
END;
/
输出：
1 one
2 two
0 zero
Package Body
如果包规范声明了游标或子程序，则需要包体。
包规范中的每个游标或子程序声明都必须在包体中具有相应的定义，且标题必须逐字匹配。
语法：
例子：Matching Package Specification and Body
CREATE PACKAGE emp_bonus AS
PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/
CREATE PACKAGE BODY emp_bonus AS
-- DATE does not match employees.hire_date%TYPE
PROCEDURE calc_bonus (date_hired DATE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE
('Employees hired on ' || date_hired || ' get bonus.');
END;
END emp_bonus;
/
Warning: Package Body created with compilation errors.
在SQL*Plus中查看错误
SHOW ERRORS
Errors for PACKAGE BODY EMP_BONUS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13 PLS-00323: subprogram or cursor 'CALC_BONUS' is declared in a
package specification and must be defined in the package body
修改
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
PROCEDURE calc_bonus
(date_hired employees.hire_date%TYPE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE
('Employees hired on ' || date_hired || ' get bonus.');
END;
END emp_bonus;
/
Package body created.
Package Example
例子：Creating emp_admin Package
-- Log to track changes (not part of package):
DROP TABLE log;
CREATE TABLE log (
date_of_action DATE,
user_id VARCHAR2(20),
package_name VARCHAR2(30)
);
-- Package specification:
CREATE OR REPLACE PACKAGE emp_admin AS
-- Declare public type, cursor, and exception:
TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
CURSOR desc_salary RETURN EmpRecTyp;
invalid_salary EXCEPTION;
-- Declare public subprograms:
FUNCTION hire_employee (
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER
) RETURN NUMBER;
-- Overload preceding public subprogram:
PROCEDURE fire_employee (emp_id NUMBER);
PROCEDURE fire_employee (emp_email VARCHAR2);
PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;
/
-- Package body:
CREATE OR REPLACE PACKAGE BODY emp_admin AS
number_hired NUMBER; -- private variable, visible only in this package
-- Define cursor declared in package specification:
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC;
-- Define subprograms declared in package specification:
FUNCTION hire_employee (
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER
) RETURN NUMBER
IS
new_emp_id NUMBER;
BEGIN
new_emp_id := employees_seq.NEXTVAL;
INSERT INTO employees (
employee_id,
last_name,
first_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
)
VALUES (
new_emp_id,
hire_employee.last_name,
hire_employee.first_name,
hire_employee.email,
hire_employee.phone_number,
SYSDATE,
hire_employee.job_id,
hire_employee.salary,
hire_employee.commission_pct,
hire_employee.manager_id,
hire_employee.department_id
);
number_hired := number_hired + 1;
DBMS_OUTPUT.PUT_LINE('The number of employees hired is '
|| TO_CHAR(number_hired) );
RETURN new_emp_id;
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM employees WHERE employee_id = emp_id;
END fire_employee;
PROCEDURE fire_employee (emp_email VARCHAR2) IS
BEGIN
DELETE FROM employees WHERE email = emp_email;
END fire_employee;
-- Define private function, available only inside package:
FUNCTION sal_ok (
jobid VARCHAR2,
sal NUMBER
) RETURN BOOLEAN
IS
min_sal NUMBER;
max_sal NUMBER;
BEGIN
SELECT MIN(salary), MAX(salary)
INTO min_sal, max_sal
FROM employees
WHERE job_id = jobid;
RETURN (sal >= min_sal) AND (sal <= max_sal);
END sal_ok;
PROCEDURE raise_salary (
emp_id NUMBER,
amount NUMBER
)
IS
sal NUMBER(8,2);
jobid VARCHAR2(10);
BEGIN
SELECT job_id, salary INTO jobid, sal
FROM employees
WHERE employee_id = emp_id;
IF sal_ok(jobid, sal + amount) THEN -- Invoke private function
UPDATE employees
SET salary = salary + amount
WHERE employee_id = emp_id;
ELSE
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.');
END raise_salary;
FUNCTION nth_highest_salary (
n NUMBER
) RETURN EmpRecTyp
IS
emp_rec EmpRecTyp;
BEGIN
OPEN desc_salary;
FOR i IN 1..n LOOP
FETCH desc_salary INTO emp_rec;
END LOOP;
CLOSE desc_salary;
RETURN emp_rec;
END nth_highest_salary;
BEGIN -- initialization part of package body
INSERT INTO log (date_of_action, user_id, package_name)
VALUES (SYSDATE, USER, 'EMP_ADMIN');
number_hired := 0;
END emp_admin;
/
-- Invoke packages subprograms in anonymous block:
DECLARE
new_emp_id NUMBER(6);
BEGIN
new_emp_id := emp_admin.hire_employee (
'Belden',
'Enrique',
'EBELDEN',
'555.111.2222',
'ST_CLERK',
2500,
.1,
101,
110
);
DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR(new_emp_id));
emp_admin.raise_salary (new_emp_id, 100);
DBMS_OUTPUT.PUT_LINE (
'The 10th highest salary is '||
TO_CHAR (emp_admin.nth_highest_salary(10).sal) ||
', belonging to employee: ' ||
TO_CHAR (emp_admin.nth_highest_salary(10).emp_id)
);
emp_admin.fire_employee(new_emp_id);
-- You can also delete the newly added employee as follows:
-- emp_admin.fire_employee('EBELDEN');
END;
/
输出：
The number of employees hired is 1
The employee id is 212
The 10th highest salary is 12075, belonging to employee: 168
There are now 107 employees.
Error Handling
本章介绍如何处理 PL/SQL 编译时警告和 PL/SQL 运行时错误（异常）。
Compile-Time Warnings
在编译存储的 PL/SQL 单元时，PL/SQL 编译器会针对不严重的情况（例如，使用已弃用的 PL/SQL 功能）生成警告。
查询数据字典视图 *_ERRORS，或者在 SQL*Plus 使用命令 SHOW ERRORS 查看编译期间生成的警告（和错误）。
编译时警告类别：
| Category | Description | Example |
|---|---|---|
SEVERE | Condition might cause unexpected action or wrong results. | Aliasing problems with parameters |
PERFORMANCE | Condition might cause performance problems. | Passing a VARCHAR2 value to a NUMBER column in an INSERT statement |
INFORMATIONAL | Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable. |
通过设置 PLSQL_WARNINGS 编译参数，可以：
启用和禁用所有警告、一个或多个警告类别或特定警告
将特定警告视为错误（以便在编译 PL/SQL 单元之前必须更正这些条件）
可以为以下各项设置 PLSQL_WARNINGS ：
数据库实例
会话
存储的 PL/SQL 单元
例子：Setting Value of PLSQL_WARNINGS Compilation Parameter
在会话级别启用所有警告，推荐在开发阶段使用：
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
在会话级别启用 PERFORMANCE 告警：
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
为存储过程启用 PERFORMANCE 告警：
ALTER PROCEDURE loc_var
COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE'
REUSE SETTINGS;
在会话级别启用 SEVERE 警告，禁用 PERFORMANCE 警告，将 PLW-06002 警告当作错误:
ALTER SESSION
SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002';
在会话级别禁用所有警告：
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
如果在编译 PL/SQL 单元的开发环境（如 SQL*Plus）中编写 PL/SQL 单元，则可以通过调用 DBMS_WARNING 包中的子程序来显示和设置 PLSQL_WARNINGS 的值。
例子：Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
在当前会话禁用所有警告：
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
则以下存储过程编译时不会产生警告：
CREATE OR REPLACE PROCEDURE unreachable_code AUTHID DEFINER AS
x CONSTANT BOOLEAN := TRUE;
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END unreachable_code;
/
为当前会话启用所有警告：
CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION');
检查警告设置：
SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;
DBMS_WARNING.GET_WARNING_SETTING_STRING()
-----------------------------------------
ENABLE:ALL
1 row selected.
重新编译：
ALTER PROCEDURE unreachable_code COMPILE;
SP2-0805: Procedure altered with compilation warnings
查看警告：
SHOW ERRORS
Errors for PROCEDURE UNREACHABLE_CODE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/5 PLW-06002: Unreachable code
Exception Handling
异常（PL/SQL 运行时错误）可能由设计错误、编码错误、硬件故障等导致。可以编写异常处理程序，让程序在出现异常时继续运行。
PL/SQL 块可以有一个异常处理部分，包含一个或多个异常处理程序。
语法：
EXCEPTION
WHEN ex_name_1 THEN statements_1 -- Exception handler
WHEN ex_name_2 OR ex_name_3 THEN statements_2 -- Exception handler
WHEN OTHERS THEN statements_3 -- Exception handler
END;
当可执行部分发生异常时，可执行部分将停止并将控制权转交到异常处理部分。如果抛出 ex_name_1 异常，则执行 statements_1 语句。如果抛出 ex_name_2 或 ex_name_3 异常，则执行 statements_2 语句。如果抛出任何其他异常，则执行 statements_3 语句。
Exception Categories
异常可以分为：
- 内部定义异常
- 预定义异常
- 用户定义异常
| Category | Definer | Has Error Code | Has Name | Raised Implicitly | Raised Explicitly |
|---|---|---|---|---|---|
| Internally defined | Runtime system | Always | Only if you assign one | Yes | Optionally |
| Predefined | Runtime system | Always | Always | Yes | Optionally |
| User-defined | User | Only if you assign one | Always | No | Always |
Advantages of Exception Handlers
使用异常处理程序进行错误处理使程序更易于编写和理解，并降低未经处理的异常的可能性。
例子：Single Exception Handler for Multiple Exceptions
CREATE OR REPLACE PROCEDURE select_item (
t_column VARCHAR2,
t_name VARCHAR2
) AUTHID DEFINER
IS
temp VARCHAR2(30);
BEGIN
temp := t_column; -- For error message if next SELECT fails
-- Fails if table t_name does not have column t_column:
SELECT COLUMN_NAME INTO temp
FROM USER_TAB_COLS
WHERE TABLE_NAME = UPPER(t_name)
AND COLUMN_NAME = UPPER(t_column);
temp := t_name; -- For error message if next SELECT fails
-- Fails if there is no table named t_name:
SELECT OBJECT_NAME INTO temp
FROM USER_OBJECTS
WHERE OBJECT_NAME = UPPER(t_name)
AND OBJECT_TYPE = 'TABLE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected error');
RAISE;
END;
/
调用存储过程（有 DEPARTMENTS 表，但是没有 LAST_NAME 字段）：
BEGIN
select_item('departments', 'last_name');
END;
/
No Data found for SELECT on departments
调用存储过程（没有 emp 表）：
BEGIN
select_item('emp', 'last_name');
END;
/
No Data found for SELECT on emp
如果多个语句使用相同的异常处理程序，通过使用定位器变量确定哪个语句失败。
例子：Locator Variables for Statements that Share Exception Handler
CREATE OR REPLACE PROCEDURE loc_var AUTHID DEFINER IS
stmt_no POSITIVE;
name_ VARCHAR2(100);
BEGIN
stmt_no := 1;
SELECT table_name INTO name_
FROM user_tables
WHERE table_name LIKE 'ABC%';
stmt_no := 2;
SELECT table_name INTO name_
FROM user_tables
WHERE table_name LIKE 'XYZ%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Table name not found in query ' || stmt_no);
END;
/
CALL loc_var();
输出：
Table name not found in query 1
Internally Defined Exceptions
内部定义异常（ORA-n 错误）在运行时会被隐式（自动）抛出。
内部定义异常没有名称，除非为其指定名称。
如果知道数据库操作可能会抛出没有名称的特定内部定义异常，请为其命名，以便可以专门为其编写异常处理程序。否则，只能使用 OTHERS 异常处理程序处理。
若要为内部定义异常命名，需在相应匿名块、子程序或包的声明部分中执行以下操作：
- 声明名称，语法：
exception_name EXCEPTION;
- 将名称与内部定义异常的错误代码相关联，语法：
PRAGMA EXCEPTION_INIT (exception_name, error_code)
例子：Naming Internally Defined Exception
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
...
END;
/
Predefined Exceptions
预定义异常是有名称的内部定义异常，运行时系统隐式（自动）抛出预定义异常。
由于预定义异常具有名称，因此可以专门为其编写异常处理程序。
PL/SQL 预定义异常：
| Exception Name | Error Code |
|---|---|
ACCESS_INTO_NULL | -6530 |
CASE_NOT_FOUND | -6592 |
COLLECTION_IS_NULL | -6531 |
CURSOR_ALREADY_OPEN | -6511 |
DUP_VAL_ON_INDEX | -1 |
INVALID_CURSOR | -1001 |
INVALID_NUMBER | -1722 |
LOGIN_DENIED | -1017 |
NO_DATA_FOUND | +100 |
NO_DATA_NEEDED | -6548 |
NOT_LOGGED_ON | -1012 |
PROGRAM_ERROR | -6501 |
ROWTYPE_MISMATCH | -6504 |
SELF_IS_NULL | -30625 |
STORAGE_ERROR | -6500 |
SUBSCRIPT_BEYOND_COUNT | -6533 |
SUBSCRIPT_OUTSIDE_LIMIT | -6532 |
SYS_INVALID_ROWID | -1410 |
TIMEOUT_ON_RESOURCE | -51 |
TOO_MANY_ROWS | -1422 |
VALUE_ERROR | -6502 |
ZERO_DIVIDE | -1476 |
例子：Anonymous Block Handles ZERO_DIVIDE
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio := stock_price / net_earnings; -- raises ZERO_DIVIDE exception
DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
pe_ratio := NULL;
END;
/
输出：
Company had zero earnings.
例子：Anonymous Block Avoids ZERO_DIVIDE
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio :=
CASE net_earnings
WHEN 0 THEN NULL
ELSE stock_price / net_earnings
END;
END;
/
User-Defined Exceptions
可以在 PL/SQL 匿名块、子程序或包的声明部分声明用户定义异常。
语法：
exception_name EXCEPTION;
必须显式抛出用户定义异常。
Raising Exceptions Explicitly
使用 RAISE 语句或 RAISE_APPLICATION_ERROR 过程显示抛出异常。
RAISE Statement
RAISE 语句显式抛出异常。
在异常处理程序外，必须指定异常名称。
在异常处理程序中，如果省略异常名称，RAISE 语句将重新抛出当前异常。
例子：Declaring, Raising, and Handling User-Defined Exception
CREATE PROCEDURE account_status (
due_date DATE,
today DATE
) AUTHID DEFINER
IS
past_due EXCEPTION; -- declare exception
BEGIN
IF due_date < today THEN
RAISE past_due; -- explicitly raise exception
END IF;
EXCEPTION
WHEN past_due THEN -- handle exception
DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/
BEGIN
account_status ('1-JUL-10', '9-JUL-10');
END;
/
输出：
Account past due.
例子：Explicitly Raising Predefined Exception
DROP TABLE t;
CREATE TABLE t (c NUMBER);
CREATE PROCEDURE p (n NUMBER) AUTHID DEFINER IS
default_number NUMBER := 0;
BEGIN
IF n < 0 THEN
RAISE INVALID_NUMBER; -- raise explicitly
ELSE
INSERT INTO t VALUES(TO_NUMBER('100.00', '9G999')); -- raise implicitly
END IF;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('Substituting default value for invalid number.');
INSERT INTO t VALUES(default_number);
END;
/
BEGIN
p(-1);
END;
/
输出：
Substituting default value for invalid number.
BEGIN
p(1);
END;
/
输出：
Substituting default value for invalid number.
例子：Reraising Exception
DECLARE
salary_too_high EXCEPTION;
current_salary NUMBER := 20000;
max_salary NUMBER := 10000;
erroneous_salary NUMBER;
BEGIN
BEGIN
IF current_salary > max_salary THEN
RAISE salary_too_high; -- raise exception
END IF;
EXCEPTION
WHEN salary_too_high THEN -- start handling exception
erroneous_salary := current_salary;
DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.');
DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');
RAISE; -- reraise current exception (exception name is optional)
END;
EXCEPTION
WHEN salary_too_high THEN -- finish handling exception
current_salary := max_salary;
DBMS_OUTPUT.PUT_LINE (
'Revising salary from ' || erroneous_salary ||
' to ' || current_salary || '.'
);
END;
/
输出：
Salary 20000 is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.
RAISE_APPLICATION_ERROR Procedure
只能从存储的子程序或方法调用 RAISE_APPLICATION_ERROR 过程（在 DBMS_STANDARD 包中定义）。通常，调用此过程以抛出用户定义异常，并将其错误代码和错误消息返回给调用程序。
语法：
RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);
必须使用 EXCEPTION_INIT 将 error_code 分配给用户定义异常。语法：
PRAGMA EXCEPTION_INIT (exception_name, error_code)
error_code 是 -20000 至 -20999 范围内的整数，message 是最多 2048 字节的字符串。
如果指定 TRUE，PL/SQL 会将 error_code 放在错误堆栈的顶部。否则，将错误堆栈替换为 error_code。
例子：Raising User-Defined Exception with RAISE_APPLICATION_ERROR
CREATE PROCEDURE account_status (
due_date DATE,
today DATE
) AUTHID DEFINER
IS
BEGIN
IF due_date < today THEN -- explicitly raise exception
RAISE_APPLICATION_ERROR(-20000, 'Account past due.');
END IF;
END;
/
DECLARE
past_due EXCEPTION; -- declare exception
PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception
BEGIN
account_status ('1-JUL-10', '9-JUL-10'); -- invoke procedure
EXCEPTION
WHEN past_due THEN -- handle exception
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
/
输出:
ORA-20000: Account past due.
Exception Propagation
如果在没有异常处理程序的块中出现异常，则异常将传播。如果没有异常处理程序，则 PL/SQL 会向调用程序或主机环境返回未处理的异常错误。
下图中处理异常 A 不传播：
下图中处理异常 B 会将异常从内部块传播到外部块：
下图中处理异常 C 会将异常从内部块传播到主机环境：
用户定义异常可以传播到其范围之外（即，超出声明它的块），但其名称不存在于其范围之外。因此，超出其范围，用户定义的异常只能使用 OTHERS 异常处理程序进行处理。
例子：Exception that Propagates Beyond Scope is Handled
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
BEGIN
DECLARE
past_due EXCEPTION;
due_date DATE := trunc(SYSDATE) - 1;
todays_date DATE := trunc(SYSDATE);
BEGIN
IF due_date < todays_date THEN
RAISE past_due;
END IF;
END;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
如果外部块不处理用户定义异常，则会发生错误。
例子：Exception that Propagates Beyond Scope is Not Handled
BEGIN
DECLARE
past_due EXCEPTION;
due_date DATE := trunc(SYSDATE) - 1;
todays_date DATE := trunc(SYSDATE);
BEGIN
IF due_date < todays_date THEN
RAISE past_due;
END IF;
END;
END;
/
输出：
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
Error Code and Error Message Retrieval
在异常处理程序中，对于正在处理的异常：
可以使用 PL/SQL 函数 SQLCODE 获取错误代码
可以使用以下任一方法获取错误消息：
SQLERRM
DBMS_UTILITY.FORMAT_ERROR_STACK
Oracle 建议使用 DBMS_UTILITY.FORMAT_ERROR_STACK。
SQL 语句不能调用 SQLCODE 或 SQLERRM。要在 SQL 语句中使用它们的值，请先将它们分配给局部变量。
例子：Displaying SQLCODE and SQLERRM Values
DROP TABLE errors;
CREATE TABLE errors (
code NUMBER,
message VARCHAR2(64)
);
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
name EMPLOYEES.LAST_NAME%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT last_name INTO name
FROM EMPLOYEES
WHERE EMPLOYEE_ID = -1;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 64);
DBMS_OUTPUT.PUT_LINE
('Error code ' || v_code || ': ' || v_errm);
/* Invoke another procedure,
declared with PRAGMA AUTONOMOUS_TRANSACTION,
to insert information about errors. */
INSERT INTO errors (code, message)
VALUES (v_code, v_errm);
RAISE;
END;
/
输出：
Error code 100: ORA-01403: no data found
Continuing Execution After Handling Exceptions
异常处理程序运行后，控制权将转移到封闭块的下一条语句（如果没有封闭块，则转移到调用程序或主机环境）。异常处理程序无法将控制权转移回其自己的块。
例子：Exception Handler Runs and Execution Ends
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
SELECT employee_id, salary, commission_pct
FROM employees;
DECLARE
sal_calc NUMBER(8,2);
BEGIN
INSERT INTO employees_temp (employee_id, salary, commission_pct)
VALUES (301, 2500, 0);
SELECT (salary / commission_pct) INTO sal_calc
FROM employees_temp
WHERE employee_id = 301;
INSERT INTO employees_temp VALUES (302, sal_calc/100, .1);
DBMS_OUTPUT.PUT_LINE('Row inserted.');
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero.');
END;
/
输出：
Division by zero.
如果异常处理程序处理了 SELECT INTO 语句抛出的 ZERO_DIVIDE 异常，则无法从 SELECT INTO 语句后面的 INSERT 语句继续执行。
如果希望 SELECT INTO 语句后面的 INSERT 语句恢复执行，需要将 SELECT INTO 语句放在具有自己的 ZERO_DIVIDE 异常处理程序的内部块中。
例子：Exception Handler Runs and Execution Continues
DECLARE
sal_calc NUMBER(8,2);
BEGIN
INSERT INTO employees_temp (employee_id, salary, commission_pct)
VALUES (301, 2500, 0);
BEGIN
SELECT (salary / commission_pct) INTO sal_calc
FROM employees_temp
WHERE employee_id = 301;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Substituting 2500 for undefined number.');
sal_calc := 2500;
END;
INSERT INTO employees_temp VALUES (302, sal_calc/100, .1);
DBMS_OUTPUT.PUT_LINE('Enclosing block: Row inserted.');
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Enclosing block: Division by zero.');
END;
/
输出：
Substituting 2500 for undefined number.
Enclosing block: Row inserted.
Retrying Transactions After Handling Exceptions
若要在异常处理后重试事务，需要：
将事务包含在具有异常处理部分的子块中。
在子块中，事务开始之前创建一个保存点。
在子块的异常处理部分，放置一个回滚到保存点的异常处理程序，然后尝试修复问题。
将子块放在 LOOP 语句中。
在子块中，在结束事务的 COMMIT 语句之后，放置一个 EXIT 语句。
如果事务成功，则执行 COMMIT 和 EXIT 语句。
如果事务失败，控制权将转移到子块的异常处理部分，并且在异常处理程序运行后，循环继续。
例子：Retrying Transaction After Handling Exception
DROP TABLE results;
CREATE TABLE results (
res_name VARCHAR(20),
res_answer VARCHAR2(3)
);
CREATE UNIQUE INDEX res_name_ix ON results (res_name);
INSERT INTO results (res_name, res_answer) VALUES ('SMYTHE', 'YES');
INSERT INTO results (res_name, res_answer) VALUES ('JONES', 'NO');
DECLARE
name VARCHAR2(20) := 'SMYTHE';
answer VARCHAR2(3) := 'NO';
suffix NUMBER := 1;
BEGIN
FOR i IN 1..5 LOOP -- Try transaction at most 5 times.
DBMS_OUTPUT.PUT('Try #' || i);
BEGIN -- sub-block begins
SAVEPOINT start_transaction;
-- transaction begins
DELETE FROM results WHERE res_answer = 'NO';
INSERT INTO results (res_name, res_answer) VALUES (name, answer);
-- Nonunique name raises DUP_VAL_ON_INDEX.
-- If transaction succeeded:
COMMIT;
DBMS_OUTPUT.PUT_LINE(' succeeded.');
EXIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE(' failed; trying again.');
ROLLBACK TO start_transaction; -- Undo changes.
suffix := suffix + 1; -- Try to fix problem.
name := name || TO_CHAR(suffix);
END; -- sub-block ends
END LOOP;
END;
/
输出：
Try #1 failed; trying again.
Try #2 succeeded.
Optimization and Tuning
Bulk SQL and Bulk Binding
批量 SQL 大大减少了 PL/SQL 和 SQL 之间通信的性能开销。
PL/SQL 和 SQL 通信方式如下：
- 运行 SELECT INTO 或 DML 语句，PL/SQL 引擎将查询或 DML 语句发送到 SQL 引擎。
- SQL 引擎运行查询或 DML 语句，并将结果返回到 PL/SQL 引擎。
使用 PL/SQL 的 FORALL 语句和 BULK COLLECT 子句批量处理 SQL。FORALL 语句将 DML 语句从 PL/SQL 批量发送到 SQL。BULK COLLECT 子句将结果从 SQL 批量返回到 PL/SQL。
将值分配给 SQL 语句中出现的 PL/SQL 变量称为绑定。
| Binding Category | When This Binding Occurs |
|---|---|
| In-bind | When an INSERT, UPDATE. or MERGE statement stores a PL/SQL or host variable in the database |
| Out-bind | When the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement assigns a database value to a PL/SQL or host variable |
DEFINE | When a SELECT or FETCH statement assigns a database value to a PL/SQL or host variable |
对于输入绑定和输出绑定，批量 SQL 使用批量绑定（bulk binding）。对于 n 个元素的集合，批量 SQL 使用单个操作来执行等效的 n 个 SELECT INTO 或 DML 语句。使用批量 SQL 的查询可以返回任意数量的行，而无需对每行使用 FETCH 语句。
FORALL Statement
FORALL 语句将 DML 语句从 PL/SQL 批量发送到 SQL。
对比 FOR LOOP 和 FORALL 语句。
例子：DELETE Statement in FOR LOOP Statement
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM employees_temp
WHERE department_id = depts(i);
END LOOP;
END;
/
例子：DELETE Statement in FORALL Statement
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees_temp
WHERE department_id = depts(i);
END;
/
FORALL 语句通常比等价的 FOR LOOP 语句快得多。但是，FOR LOOP 语句可以包含多个 DML 语句，而 FORALL 语句只能包含一个。FORALL 语句批量发送到 SQL 的 DML 语句仅在其 VALUES 和 WHERE 子句上有所不同。
例子：Time Difference for INSERT Statement in FOR LOOP and FORALL Statements
DROP TABLE parts1;
CREATE TABLE parts1 (
pnum INTEGER,
pname VARCHAR2(15)
);
DROP TABLE parts2;
CREATE TABLE parts2 (
pnum INTEGER,
pname VARCHAR2(15)
);
DECLARE
TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
pnums NumTab;
pnames NameTab;
iterations CONSTANT PLS_INTEGER := 50000;
t1 INTEGER;
t2 INTEGER;
t3 INTEGER;
BEGIN
FOR j IN 1..iterations LOOP -- populate collections
pnums(j) := j;
pnames(j) := 'Part No. ' || TO_CHAR(j);
END LOOP;
t1 := DBMS_UTILITY.get_time;
FOR i IN 1..iterations LOOP
INSERT INTO parts1 (pnum, pname)
VALUES (pnums(i), pnames(i));
END LOOP;
t2 := DBMS_UTILITY.get_time;
FORALL i IN 1..iterations
INSERT INTO parts2 (pnum, pname)
VALUES (pnums(i), pnames(i));
t3 := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
DBMS_OUTPUT.PUT_LINE('---------------------');
DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100));
DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100));
COMMIT;
END;
/
输出：
Execution Time (secs)
---------------------
FOR LOOP: 2.16
FORALL: .11
例子：FORALL Statement for Subset of Collection
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
FORALL j IN 4..7
DELETE FROM employees_temp WHERE department_id = depts(j);
END;
/
FORALL Statements for Sparse Collections
如果 FORALL 语句的边界子句引用稀疏集合，则仅使用 INDICES OF 或 VALUES OF 子句指定现有索引值。可以将 INDICES OF 用于除按字符串编制索引的关联数组之外的任何集合。只能将 VALUES OF 用于按 PLS_INTEGER 编制索引的 PLS_INTEGER 元素的集合。
索引集合对于使用不同的 FORALL 语句处理同一集合的不同子集很有用。不需要将原始集合的元素复制到表示子集的新集合中（这会占用大量时间和内存），而是使用索引集合表示每个子集，然后在不同 FORALL 语句的 VALUES OF 子句中使用每个索引集合。
例子：FORALL Statements for Sparse Collection and Its Subsets
DROP TABLE valid_orders;
CREATE TABLE valid_orders (
cust_name VARCHAR2(32),
amount NUMBER(10,2)
);
DROP TABLE big_orders;
CREATE TABLE big_orders AS
SELECT * FROM valid_orders
WHERE 1 = 0;
DROP TABLE rejected_orders;
CREATE TABLE rejected_orders AS
SELECT * FROM valid_orders
WHERE 1 = 0;
DECLARE
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLE OF cust_name;
cust_tab cust_typ; -- Collection of customer names
SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab amount_typ; -- Collection of order amounts
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
/* Collections for pointers to elements of cust_tab collection
(to represent two subsets of cust_tab): */
big_order_tab index_pointer_t := index_pointer_t();
rejected_order_tab index_pointer_t := index_pointer_t();
PROCEDURE populate_data_collections IS
BEGIN
cust_tab := cust_typ(
'Company1','Company2','Company3','Company4','Company5'
);
amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
END;
BEGIN
populate_data_collections;
DBMS_OUTPUT.PUT_LINE ('--- Original order data ---');
FOR i IN 1..cust_tab.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)
);
END LOOP;
-- Delete invalid orders:
FOR i IN 1..cust_tab.LAST LOOP
IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
cust_tab.delete(i);
amount_tab.delete(i);
END IF;
END LOOP;
-- cust_tab is now a sparse collection.
DBMS_OUTPUT.PUT_LINE ('--- Order data with invalid orders deleted ---');
FOR i IN 1..cust_tab.LAST LOOP
IF cust_tab.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE (
'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)
);
END IF;
END LOOP;
-- Using sparse collection, populate valid_orders table:
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders (cust_name, amount)
VALUES (cust_tab(i), amount_tab(i));
populate_data_collections; -- Restore original order data
-- cust_tab is a dense collection again.
/* Populate collections of pointers to elements of cust_tab collection
(which represent two subsets of cust_tab): */
FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
rejected_order_tab.EXTEND;
rejected_order_tab(rejected_order_tab.LAST) := i;
END IF;
IF amount_tab(i) > 2000 THEN
big_order_tab.EXTEND;
big_order_tab(big_order_tab.LAST) := i;
END IF;
END LOOP;
/* Using each subset in a different FORALL statement,
populate rejected_orders and big_orders tables: */
FORALL i IN VALUES OF rejected_order_tab
INSERT INTO rejected_orders (cust_name, amount)
VALUES (cust_tab(i), amount_tab(i));
FORALL i IN VALUES OF big_order_tab
INSERT INTO big_orders (cust_name, amount)
VALUES (cust_tab(i), amount_tab(i));
END;
/
输出：
--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
查看结果：
SELECT cust_name "Customer", amount "Valid order amount"
FROM valid_orders
ORDER BY cust_name;
Customer Valid order amount
-------------------------------- ------------------
Company1 5000.01
Company3 150.25
Company4 4000
3 rows selected.
SELECT cust_name "Customer", amount "Big order amount"
FROM big_orders
ORDER BY cust_name;
Customer Big order amount
-------------------------------- ----------------
Company1 5000.01
Company4 4000
2 rows selected.
SELECT cust_name "Customer", amount "Rejected order amount"
FROM rejected_orders
ORDER BY cust_name;
Customer Rejected order amount
-------------------------------- ---------------------
Company2 0
Company5
2 rows selected.
Handling FORALL Exceptions Immediately
如果一个 DML 语句抛出可处理的异常，则 PL/SQL 将回滚该语句所做的更改，但不回滚先前 DML 语句所做的更改。
例子：Handling FORALL Exceptions Immediately
DROP TABLE emp_temp;
CREATE TABLE emp_temp (
deptno NUMBER(2),
job VARCHAR2(18)
);
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
error_message VARCHAR2(100);
BEGIN
-- Populate table:
INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
COMMIT;
-- Append 9-character string to each job:
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp_temp SET job = job || ' (Senior)'
WHERE deptno = depts(j);
EXCEPTION
WHEN OTHERS THEN
error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE (error_message);
COMMIT; -- Commit results of successful updates
RAISE;
END;
/
调用：
BEGIN
p;
END;
/
ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,
maximum: 18)
ORA-06512: at "HR.P", line 27
ORA-06512: at line 2
PL/SQL procedure successfully completed.
查询结果：
SELECT * FROM emp_temp;
DEPTNO JOB
---------- ------------------
10 Clerk (Senior)
20 Bookkeeper
30 Analyst
3 rows selected.
Handling FORALL Exceptions After FORALL Statement Completes
若要允许 FORALL 语句在其某些 DML 语句失败时继续，需包含 SAVE EXCEPTIONS 子句。当 DML 语句失败时，PL/SQL 不会抛出异常，而去保存有关失败的信息。FORALL 语句完成后，PL/SQL 会为 FORALL 语句抛出单个异常（ORA-24381）。在 ORA-24381 的异常处理程序中，可以从隐式游标属性 SQL%BULK_EXCEPTIONS 获取有关每个单独的 DML 语句失败的信息。
SQL%BULK_EXCEPTIONS 类似于有关在最近运行 FORALL 语句期间失败的 DML 语句的信息的关联数组。
SQL%BULK_EXCEPTIONS.COUNT 是失败的 DML 语句数。如果为 SQL%BULK_EXCEPTIONS.COUNT 不为零，则对于每个索引值 i 从 1 到 SQL%BULK_EXCEPTIONS.COUNT：
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX 是失败的 DML 语句的编号。
SQL%BULK_EXCEPTIONS(i).ERROR_CODE 是失败的 Oracle 数据库错误代码。
例如，如果 FORALL SAVE EXCEPTIONS 语句运行 100 个 DML 语句，并且第十个和第六十四个语句失败，错误代码分别为 ORA-12899 和 ORA-19278，则：
SQL%BULK_EXCEPTIONS.COUNT = 2
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278
注意：在没有 SAVE EXCEPTIONS 子句的 FORALL 语句抛出异常后，SQL%BULK_EXCEPTIONS.COUNT = 1
使用错误代码，可以获取与 SQLERRM 函数关联的错误消息：
SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))
例子：Handling FORALL Exceptions After FORALL Statement Completes
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
error_message VARCHAR2(100);
bad_stmt_no PLS_INTEGER;
bad_deptno emp_temp.deptno%TYPE;
bad_job emp_temp.job%TYPE;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
-- Populate table:
INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
COMMIT;
-- Append 9-character string to each job:
FORALL j IN depts.FIRST..depts.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job = job || ' (Senior)'
WHERE deptno = depts(j);
EXCEPTION
WHEN dml_errors THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
DBMS_OUTPUT.PUT_LINE (error_message);
bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
DBMS_OUTPUT.PUT_LINE('Bad statement #: ' || bad_stmt_no);
bad_deptno := depts(bad_stmt_no);
DBMS_OUTPUT.PUT_LINE('Bad department #: ' || bad_deptno);
SELECT job INTO bad_job FROM emp_temp WHERE deptno = bad_deptno;
DBMS_OUTPUT.PUT_LINE('Bad job: ' || bad_job);
END LOOP;
COMMIT; -- Commit results of successful updates
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unrecognized error.');
RAISE;
END;
/
调用：
BEGIN
p;
END;
/
ORA-12899: value too large for column (actual: , maximum: )
Bad statement #: 2
Bad department #: 20
Bad job: Bookkeeper
PL/SQL procedure successfully completed.
查询结果：
SELECT * FROM emp_temp;
DEPTNO JOB
---------- ------------------
10 Clerk (Senior)
20 Bookkeeper
30 Analyst (Senior)
3 rows selected.
Getting Number of Rows Affected by FORALL Statement
FORALL 语句完成后，可以从隐式游标属性 SQL%BULK_ROWCOUNT 获取每个 DML 语句影响的行数。从隐式游标属性 SQL%ROWCOUNT获取受 FORALL 语句影响的行总数。
SQL%BULK_ROWCOUNT 类似于一个关联数组，其第 i 个元素是最近完成的 FORALL 语句中受第 i 个 DML 语句影响的行数。元素的数据类型为 PLS_INTEGER。
例子：Showing Number of Rows Affected by Each DELETE in FORALL
DROP TABLE emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(30, 50, 60);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE department_id = depts(j);
FOR i IN depts.FIRST..depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
'Statement #' || i || ' deleted ' ||
SQL%BULK_ROWCOUNT(i) || ' rows.'
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/
输出：
Statement #1 deleted 6 rows.
Statement #2 deleted 45 rows.
Statement #3 deleted 5 rows.
Total rows deleted: 56
例子：Showing Number of Rows Affected by Each INSERT SELECT in FORALL
DROP TABLE emp_by_dept;
CREATE TABLE emp_by_dept AS
SELECT employee_id, department_id
FROM employees
WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
FORALL i IN 1..deptnums.COUNT
INSERT INTO emp_by_dept (employee_id, department_id)
SELECT employee_id, department_id
FROM employees
WHERE department_id = deptnums(i)
ORDER BY department_id, employee_id;
FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
DBMS_OUTPUT.PUT_LINE (
'Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records'
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);
END;
/
输出：
Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Dept 280: inserted 0 records
Total records inserted: 106
BULK COLLECT Clause
BULK COLLECT 子句将结果从 SQL 批量返回到 PL/SQL。可以出现在：
SELECT INTO
FETCH
RETURNING INTO
- DELETE
- INSERT
- UPDATE
- EXECUTE IMMEDIATE
SELECT INTO Statement with BULK COLLECT Clause
带有 BULK COLLECT 子句的 SELECT INTO 语句（也称为 SELECT BULK COLLECT INTO 语句）将整个结果集放到一个或多个集合变量中。
例子：Bulk-Selecting Two Database Columns into Two Nested Tables
DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;
names NameTab;
PROCEDURE print_first_n (n POSITIVE) IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
ELSE
DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
FOR i IN 1 .. n LOOP
DBMS_OUTPUT.PUT_LINE (
' Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END IF;
END;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;
print_first_n(3);
print_first_n(6);
END;
/
输出：
First 3 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
First 6 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
Employee #103: Hunold
Employee #104: Ernst
Employee #105: Austin
例子：Bulk-Selecting into Nested Table of Records
DECLARE
CURSOR c1 IS
SELECT first_name, last_name, hire_date
FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
stock_managers NameSet; -- nested table of records
BEGIN
-- Assign values to nested table of records:
SELECT first_name, last_name, hire_date
BULK COLLECT INTO stock_managers
FROM employees
WHERE job_id = 'ST_MAN'
ORDER BY hire_date;
-- Print nested table of records:
FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
stock_managers(i).hire_date || ' ' ||
stock_managers(i).last_name || ', ' ||
stock_managers(i).first_name
);
END LOOP;END;
/
输出：
01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin
例子：Limiting Bulk Selection with ROWNUM and SAMPLE
DECLARE
TYPE SalList IS TABLE OF employees.salary%TYPE;
sals SalList;
BEGIN
SELECT salary BULK COLLECT INTO sals
FROM employees
WHERE ROWNUM <= 50;
SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);
END;
/
FETCH Statement with BULK COLLECT Clause
带有 BULK COLLECT 子句的 FETCH 语句（也称为 FETCH BULK COLLECT 语句）将整个结果集提取到一个或多个集合变量中。
例子：Bulk-Fetching into Two Nested Tables
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS
SELECT last_name, salary
FROM employees
WHERE salary > 10000
ORDER BY last_name;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
-- Check if collections are empty:
IF names IS NULL OR names.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Result: ');
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
-- Now all columns from result set come from one record
DBMS_OUTPUT.PUT_LINE (
' Employee ' || recs(i).last_name || ': $' || recs(i).salary
);
END LOOP;
END;
/
输出：
--- Processing all results simultaneously ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500
--- Processing 10 rows at a time ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Result:
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500
--- Fetching records rather than columns ---
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500
例子：Bulk-Fetching into Nested Table of Records
DECLARE
CURSOR c1 IS
SELECT first_name, last_name, hire_date
FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
stock_managers NameSet; -- nested table of records
TYPE cursor_var_type is REF CURSOR;
cv cursor_var_type;
BEGIN
-- Assign values to nested table of records:
OPEN cv FOR
SELECT first_name, last_name, hire_date
FROM employees
WHERE job_id = 'ST_MAN'
ORDER BY hire_date;
FETCH cv BULK COLLECT INTO stock_managers;
CLOSE cv;
-- Print nested table of records:
FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
DBMS_OUTPUT.PUT_LINE (
stock_managers(i).hire_date || ' ' ||
stock_managers(i).last_name || ', ' ||
stock_managers(i).first_name
);
END LOOP;END;
/
输出：
01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin
例子：Limiting Bulk FETCH with LIMIT
DECLARE
TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR c1 IS
SELECT employee_id
FROM employees
WHERE department_id = 80
ORDER BY employee_id;
empids numtab;
BEGIN
OPEN c1;
LOOP -- Fetch 10 rows or fewer in each iteration
FETCH c1 BULK COLLECT INTO empids LIMIT 10;
DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');
FOR i IN 1..empids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));
END LOOP;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
/
输出：
------- Results from One Bulk Fetch --------
Employee Id: 145
Employee Id: 146
Employee Id: 147
Employee Id: 148
Employee Id: 149
Employee Id: 150
Employee Id: 151
Employee Id: 152
Employee Id: 153
Employee Id: 154
------- Results from One Bulk Fetch --------
Employee Id: 155
Employee Id: 156
Employee Id: 157
Employee Id: 158
Employee Id: 159
Employee Id: 160
Employee Id: 161
Employee Id: 162
Employee Id: 163
Employee Id: 164
------- Results from One Bulk Fetch --------
Employee Id: 165
Employee Id: 166
Employee Id: 167
Employee Id: 168
Employee Id: 169
Employee Id: 170
Employee Id: 171
Employee Id: 172
Employee Id: 173
Employee Id: 174
------- Results from One Bulk Fetch --------
Employee Id: 175
Employee Id: 176
Employee Id: 177
Employee Id: 179
RETURNING INTO Clause with BULK COLLECT Clause
带有 BULK COLLECT 子句的 RETURNING INTO 子句（也称为 RETURNING BULK COLLECT INTO 子句）可以出现在 INSERT, UPDATE, DELETE 或者 EXECUTE IMMEDIATE 语句中。使用 RETURNING BULK COLLECT INTO 子句，将结果集存储在一个或多个集合中。
例子：Returning Deleted Rows in Two Nested Tables
DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp_temp
WHERE department_id = 30
RETURNING employee_id, last_name
BULK COLLECT INTO enums, names;
DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
输出：
Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares
Using FORALL Statement and BULK COLLECT Clause Together
在 FORALL 语句中，DML 语句可以具有 RETURNING BULK COLLECTION INTO 子句。对于 FORALL 语句的每次迭代，DML 语句将指定的值存储在指定的集合中，而不会覆盖以前的值。
例子：DELETE with RETURNING BULK COLLECT INTO in FORALL Statement
DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id, department_id;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,30);
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
e_ids enum_t;
TYPE dept_t IS TABLE OF employees.department_id%TYPE;
d_ids dept_t;
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp
WHERE department_id = depts(j)
RETURNING employee_id, department_id
BULK COLLECT INTO e_ids, d_ids;
DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN e_ids.FIRST .. e_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
);
END LOOP;
END;
/
输出：
Deleted 9 rows:
Employee #200 from dept #10
Employee #201 from dept #20
Employee #202 from dept #20
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30
例子：DELETE with RETURNING BULK COLLECT INTO in FOR LOOP Statement
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,30);
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
e_ids enum_t;
TYPE dept_t IS TABLE OF employees.department_id%TYPE;
d_ids dept_t;
BEGIN
FOR j IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp_temp
WHERE department_id = depts(j)
RETURNING employee_id, department_id
BULK COLLECT INTO e_ids, d_ids;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN e_ids.FIRST .. e_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
);
END LOOP;
END;
/
输出:
Deleted 6 rows:
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30