一、什么是子程序
子程序就是能够接受参数并被其他程序所调用的命名PL/SQL块。PL/SQL子程序有两种类型，过程和函数。一般地，过程用于执行一个操作，而函数用于计算一个结果值。
与未命名或匿名PL/SQL块一样，子程序也有声明部分，执行部分和一个可选的异常处理部分。声明部分包含类型、游标、常量、变量、异常和嵌套子程序的声明。这些内容都是本地的，在程序退出时会自动销毁。执行部分包含赋值语句、流程控制语句和Oracle的数据操作语句。异常处理部分包含异常处理程序。思考下面用于记入借方银行账户的debit_account过程：
PROCEDURE debit_account(acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal
INTO old_balance
FROM accts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts
SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;
在被调用时，这个过程接受一个银行账号和借贷金额。它使用账号从accts表中查询账目结算信息。然后用借款金额计算新的账目结算。如果计算后的余额比零小，异常就会被抛出；否则，该账号相关信息就会被更新。
二、子程序的优点
子程序能提供扩展性，它能够让我们根据自己的需求来编写特定的PL/SQL。比如，我们需要一个能够创建新部门的过程，就可以像下面这样编写代码：
PROCEDURE create_dept(new_dname VARCHAR2, new_loc VARCHAR2) IS
BEGIN
INSERT INTO dept
VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;
子程序还能提供模块化，就是说它可以把一个程序定义成多个模块，更易管理。这样，我们就可以用自顶而下的设计(top-down design)和逐步求精(stepwise refinement)的方法来解决问题。
此外，子程序在提高程序的重用性和可维护方面也是很有用的。只要编译成功，子程序就可以放心地用在很多应用程序中。如果它的定义内容发生了改变，受到影响的只有子程序本身而已，这就简化了维护过程。最后，子程序还有助于逻辑的抽象。使用子程序时，我们需要知道的是它们的功能，而不是它们实现功能的细节问题。
三、理解PL/SQL过程
过程是一个能执行某个特定操作的子程序。我们可以用下面的语法来编写过程：
[CREATE [OR REPLACE]]
PROCEDURE procedure_name[(parameter[, parameter]...)]
[AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
parameter的含义如下：
parameter_name [IN | OUT [NOCOPY] | IN OUT [NOCOPY]] datatype
[{:= | DEFAULT} expression]
CREATE子句能让我们创建保存在数据库中的独立过程。我们可以从SQL*Plus中或是在使用动态SQL的程序中执行CREATE PROCEDURE语句。
AUTHID子句决定了存储过程是按所有者权限(默认)调用还是按当前用户权限执行，也能决定在没有限定修饰词的情况下，对所引用的对象是按所有者模式进行解析还是按当前用户模式进行解析。我们可以指定CURRENT_USER来覆盖掉程序的默认行为。
编译指示AUTONOMOUS_TRANSACTION会告诉PL/SQL编译器把过程标记为自治(独立)。自治事务能让我们把主事务挂起，执行SQL操作，提交或回滚自治事务，然后再恢复主事务。
我们不能对参数的数据类型进行约束，如下例中对acct_id的声明就是不合法的，因为它对CHAR类型进行了长度限制：
PROCEDURE reconcile (acct_id CHAR(5)) IS ... -- illegal
但是，我们可以使用下面的方法间接的对字符的长度进行限制：
DECLARE
SUBTYPE Char5 IS CHAR(5);
PROCEDURE reconcile (acct_id Char5) IS ...
过程有两个部分，过程说明和过程体。说明部分由关键字PROCEDURE开头，以过程名或参数列表结尾。参数声明是可选的。没有参数的过程是不用使用圆括号的。
过程体由关键字IS(或AS)开头，并以END结尾，END后面可以跟上一个可选的过程名。过程体有三个部分：声明、执行和可选的异常处理。
声明部分包括本地声明，它处于IS和BEGIN之间。在匿名PL/SQL块使用的关键字DECLARE在这里不再需要。执行部分包括许多语句，它们被放到BEGIN和EXCEPTION(或END)之间，并且至少要有一条语句出现在过程的执行部分。NULL语句可以满足这个需求。异常处理部分包含异常处理程序，它被放在关键字EXCEPTION和END之间。
在下面的过程raise_salary中，我们会根据给定的金额来为雇员加薪：
PROCEDURE raise_salary(emp_id INTEGER, amount REAL) IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal
INTO current_salary
FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp
SET sal = sal + amount
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit
VALUES (emp_id, 'No such number');
WHEN salary_missing THEN
INSERT INTO emp_audit
VALUES (emp_id, 'Salary is null');
END raise_salary;
在调用时，过程接受雇员编号和薪资调整金额，然后用雇员编号从emp表找出指定雇员的当前工资。如果雇员编号无法找到或是当前工资为空，异常就会被抛出，否则工资就会被更新。
过程可以作为一个PL/SQL语句来调用。例如，我们可以像下面这样调用raise_salary：
raise_salary(emp_id, amount);
四、理解PL/SQL函数
函数是一个能够计算结果值的子程序，函数除了有一个RETURN子句之外，其它结构跟过程类似。我们可以用下面的语法来编写(本地)函数：
[CREATE [OR REPLACE ] ]
FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN datatype
[ AUTHID { DEFINER | CURRENT_USER } ]
[ PARALLEL_ENABLE
[ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] |
[ORDER parameter BY (column_name [ , column_name ]... ) ] } ]
[ ( PARTITION parameter BY
{ [ {RANGE | HASH } (column_name [, column_name]...)] | ANY }
) ]
]
[DETERMINISTIC] [ PIPELINED [ USING implementation_type ] ]
[ AGGREGATE [UPDATE VALUE] [WITH EXTERNAL CONTEXT]
USING implementation_type ] {IS | AS}
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ local declarations ]
BEGIN
executable statements
[ EXCEPTION
exception handlers ]
END [ name ];
函数的语法结构与过程类似，这里就不再重复。但有几个不同点还是需要注意的。
PARALLEL_ENABLE选项能声明一个在并发DML操作的从属会话(slave session)中被安全调用的存储函数。主(logon)会话的状态不会被从属会话所共享。每个从属会话都有它自己的状态，这是在会话开始时初始化的。函数的结果不应依赖于会话(静态)变量的状态。否则结果就可能随着会话而发生变化。
提示DETERMINISTIC能帮助优化程序避免冗余的函数调用。如果存储函数的调用跟前一次调用时所使用的参数相同，优化程序就直接选出前一次的计算结果值。函数结果不应该依赖于会话变量或模式对象的状态。否则结果会随着调用而发生变化。只有DETERMINISTIC函数才允许被函数索引或是参数query_rewrite_enabled为TRUE的实体化视图调用。
我们不能对参数或是函数返回值的类型添加约束，但可以像前面的过程那样使用间接的约束方法。
思考下面的函数sal_ok，它的作用是检查工资是否超出限定范围：
FUNCTION sal_ok(salary REAL, title VARCHAR2)
RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal
INTO min_sal, max_sal
FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND(salary <= max_sal);
END sal_ok;
调用时，函数接受雇员的工资金额和职别，然后使用职别从数据表sals选择工资范围。函数标识符sal_ok由RETURN语句返回的布尔值赋值。如果salary超过限定范围，sal_ok值就是FALSE，否则就是TRUE。
函数可以作为表达式的一部分而被调用，如下例所示。函数标识符sal_ok就像一个变量一样，它的值是由传递进去的参数所决定的。
IF sal_ok(new_sal, new_title) THEN ...
1、使用RETURN语句
RETURN语句能够立即结束当前执行的子程序并把控制权交还给调用者。然后程序继续执行子程序调用之后的语句。(不要把RETURN语句和函数声明中的RETURN子句搞混淆了，声明中的RETURN只是用于指明函数返回值的数据类型。)
子程序能包含几个RETURN语句。最后一个语句不一定非得是RETURN语句。只要执行RETURN语句就能立即结束当前子程序。但是，在一个子程序包含有多个出口点不是一个好习惯。
在过程中，RETURN语句不能返回值，也不能返回任何表达式。它的作用只是在过程到达正常的过程结尾之前将控制权交给调用者。
但是，在函数中，RETURN语句必须包含一个表达式，该表达式的值会在RETURN语句执行时被计算。计算的结果赋给函数标识符，标识符的作用相当于RETURN说明中的类型的变量。观察下面返回指定银行账户的余额的函数balance：
FUNCTION balance(acct_id INTEGER)
RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal
INTO acct_bal
FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END balance;
下例演示了如何在RETURN语句中使用复杂的表达式：
FUNCTION compound(years NUMBER, amount NUMBER, rate NUMBER)
RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
函数中，至少要有一条执行路径能够到达RETURN语句。否则，在运行时就会得到函数没有返回值的错误。
2、控制PL/SQL子程序的副影响
为了能在SQL中被调用，存储函数必须遵守以下"纯度"规则，这些规则能控制函数副作用：
- 当从SELECT语句或是并发的INSERT、UPDATE、DELETE语句中调用函数时，它不能修改任何数据表。
- 当从INSERT、UPDATE或DELETE语句中调用函数时，它不能查询或修改这些语句所能影响到的数据表。
- 当从SELECT、INSERT、UPDATE或DELETE语句中调用函数时，它不能执行SQL事务控制语句(如COMMIT)，会话控制语句(如SET ROLE)或系统控制语句(如ALTER SYSTEM)。同样，它也不能执行数据定义语句(如CREATE)，因为这些语句都是自动提交事务的。
如果函数内部任何一条SQL语句与上述规则相冲突，我们就会在运行时得到错误(语句被分析的时候)。
为了检查这些冲突项，我们可以使用编译指示RESTRICT_REFERENCES(编译器指令)。编译指示能判断函数是否读写数据表或包中的变量。例如在下面的函数中，编译指示就能判断出函数credit_ok不写数据库(WNDS)也不读取包(RNPS)：
CREATE PACKAGE loans AS
...
FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;
注意：一个静态的INSERT、UPDATE或DELETE语句总是与WNDS相冲突的；如果读取了数据库字段，它也会与RNDS冲突。一个动态INSERT、UPDATE或DELETE语句总与WNDS和RNDS冲突。
五、声明PL/SQL子程序
我们可以在PL/SQL块、子程序或包中声明子程序。但是，子程序只能在其他内容声明之后再声明。
PL/SQL需要我们先声明标识然后才能引用它们。所以，在使用子程序之前必须要先声明。例如，下面对过程award_bonus的声明就是非法的，因为它在过程calc_rating未声明之前就开始调用它：
DECLARE
...
PROCEDURE award_bonus IS
BEGIN
calc_rating(...); -- undeclared identifier
...
END;
PROCEDURE calc_rating (...) IS
BEGIN
...
END;
这种情况下，我们只要简单地把过程calc_rating放到award_bonus之前就可以了。但是简单的做法不一定总是有效的，如：两个过程相互引用或是我们就想按逻辑或字母顺序来定义他们。
我们可以使用"向前声明"来解决这个问题，向前声明由子程序说明和一个分号组成。在下面的例子中，向前声明通知PL/SQL，过程calc_rating的体部分可以在块的后面找到。
DECLARE
PROCEDURE calc_rating ( ... ); -- forward declaration
...
虽然形式参数列表在向前声明中已经出现过了，但它也必须出现在子程序体中。子程序体可以放在向前声明之后的任何地方，但它们必须出现在同一个程序单元中。
六、子程序打包
我们可以把逻辑相关的子程序打包后放到数据库中。那样，子程序就能被许多应用程序共享。子程序说明部分放在包说明部分；子程序体放在包体，子程序体对应用程序是不可见的。因此，包能帮助我们隐藏程序的实现细节。如下例：
CREATE PACKAGE emp_actions AS -- package spec
PROCEDURE hire_employee(emp_id INTEGER, NAME VARCHAR2, ...);
PROCEDURE fire_employee(emp_id INTEGER);
PROCEDURE raise_salary(emp_id INTEGER, amount REAL);
...
END emp_actions;
CREATE PACKAGE BODY emp_actions AS -- package body
PROCEDURE hire_employee(emp_id INTEGER, NAME VARCHAR2, ...) IS
BEGIN
...
INSERT INTO emp
VALUES (emp_id, NAME, ...);
END hire_employee;
PROCEDURE fire_employee(emp_id INTEGER) IS
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
END fire_employee;
PROCEDURE raise_salary(emp_id INTEGER, amount REAL) IS
BEGIN
UPDATE emp
SET sal = sal + amount
WHERE empno = emp_id;
END raise_salary;
...
END emp_actions;
我们还能够直接在包体内定义子程序而不用在包说明部分编写它们的说明。但是，这样的子程序只能在包内的使用。
七、形参VS实参
子程序使用参数来传递信息。调用时子程序参数列表中引用的变量或表达式是实际参数(actual parameter，以下简称实参)。例如，下面的过程列出了两个实参emp_num和amout：
raise_salary(emp_num, amount);
下面的过程调用演示了用表达式作为实参：
raise_salary(emp_num, merit + cola);
子程序声明和子程序体中引用的变量是形式参数(formal parameter，以下简称形参)。例如，下面的过程声明了两个形参emp_id和amount：
PROCEDURE raise_salary(emp_id INTEGER, amount REAL) IS
BEGIN
UPDATE emp
SET sal = sal + amount
WHERE empno = emp_id;
END raise_salary;
好的编程习惯就是使用不同命名的形参和实参。
调用过程raise_salary时，实参的内容会被赋值到对应的形参上，如果有必要的话，在赋值之前PL/SQL会帮助我们进行类型转换。例如，下面对raise_salary的调用就是有效的：
raise_salary(emp_num, '2500');
实参和它对应的形参必须类型兼容，例如，PL/SQL是不能把数据从DATE类型转到REAL类型的。下面的过程调用就会引起预定义异常VALUE_ERROR，因为PL/SQL不能把第二个实参转成一个数字：
raise_salary(emp_num, '$2500'); -- note the dollar sign
八、位置标示法VS名字标示法
在调用子程序时，我们既可以使用位置标示法又可以使用名字标示法来编写实参。也就是说，我们可以按位置或名称来把实参和形参关联起来。如下例所示：
DECLARE
acct INTEGER;
amt REAL;
PROCEDURE credit_acct(acct_no INTEGER, amount REAL) IS ...
我们可以使用四种等价的方法来调用过程credit_acct：
BEGIN
credit_acct(acct, amt); -- positional notation
credit_acct(amount => amt, acct_no => acct); -- named notation
credit_acct(acct_no => acct, amount => amt); -- named notation
credit_acct(acct, amount => amt); -- mixed notation
1、使用位置标示法
第一个过程调用使用了位置标示法。PL/SQL编译器将第一个实参acct和第一个形参acct_no关联，并把第二个实参amt和第二个形参amount关联。
2、使用名字标示法
第二个过程调用使用了名字标示法。箭头(=>)作为关联操作符，把左边的实参和右边的形参关联起来。
第三个过程调用也使用了名字标示法，而且我们可以随意安排参数的位置。所以，我们不需要知道形参的在参数列表中的顺序。
3、使用混合标示法
第四个过程调用使用了名字标示法和位置标示法。在这种情况下，位置标示法必须在名字标示法之前，不能反过来使用，像下面这样的调用方法就是不合法的：
credit_acct(acct_no => acct, amt); -- illegal
九、指定子程序参数模式
我们可以使用参数的模式来定义形式参数的行为。一共有三种模式：IN、OUT和IN OUT。但是，最好避免在函数中使用OUT和IN OUT模式。函数的作用是用来接受零个或多个参数然后返回一个值。用函数返回多个值不是个好习惯。同样，函数应该避免产生负影响，那样会改变那些对子程序来说是非本地的变量值。
1、使用IN模式
IN模式能让我们把值传递给被调用子程序，在子程序中，IN模式参数的作用就像常量一样。因此，它不能被赋值。例如，下面的赋值语句就会引起编译错误：
PROCEDURE debit_account(acct_id IN INTEGER, amount IN REAL) IS
minimum_purchase CONSTANT REAL DEFAULT 10.0;
service_charge CONSTANT REAL DEFAULT 0.50;
BEGIN
IF amount < minimum_purchase THEN
amount := amount + service_charge; -- causes compilation error
END IF;
...
END debit_account;
IN模式形参对应的实参可以是常量、文字、被初始化的变量或是表达式。与OUT和IN OUT模式的参数不同，我们可以为IN模式的参数初始化一个默认值。
2、使用OUT模式
OUT模式的参数能让我们把值返回给子程序的调用者。在子程序中，OUT模式参数的作用就像变量。这也就意味着我们可以把它当作本地变量来使用，例如：
PROCEDURE calc_bonus(emp_id IN INTEGER, bonus OUT REAL) IS
hire_date DATE;
bonus_missing EXCEPTION;
BEGIN
SELECT sal * 0.10, hiredate
INTO bonus, hire_date
FROM emp
WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE bonus_missing;
END IF;
IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
bonus := bonus + 500;
END IF;
...
EXCEPTION
WHEN bonus_missing THEN
...
END calc_bonus;
与OUT模式的形参对应的实参必须是变量；它不能是常量或表达式。例如，下面的调用就不合法：
calc_bonus(7499, salary + commission); -- causes compilation error
一个OUT实参在子程序调用之前是可以有值的。但是，在子程序调用时，这个值就会丢失，除非我们使用了NOCOPY编译器提示或是子程序因未捕获异常而终止。
与变量一样，OUT模式的形参会被初始化为NULL.所以，一个OUT模式的形参的数据类型是不能有NOT NULL约束的(包括内置类型NATURALN和POSITIVEN)。否则的话，PL/SQL就会抛出VALUE_ERROR异常，见下例：
DECLARE
SUBTYPE counter IS INTEGER NOT NULL;
ROWS counter := 0;
PROCEDURE count_emps(n OUT counter) IS
BEGIN
SELECT COUNT(*)
INTO n
FROM emp;
END;
BEGIN
count_emps(ROWS); -- raises VALUE_ERROR
在子程序退出之前，它必须要显式地为所有的OUT模式形参赋值。否则对应的实参值就为空。如果成功地退出子程序，PL/SQL就会把值赋给实参。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。
3、使用IN OUT模式
一个IN OUT模式的参数能让我们把它的初始值传递给被调用的子程序，然后再把子程序更新后的值传递给调用者。在子程序中，一个IN OUT模式参数的作用就像一个初始化了的变量。因此，它能够被赋值，而且它的值还可以赋给其他的变量。
与IN OUT模式形参对应的实参必须是变量；它不可以是常量或表达式。如果成功地退出子程序，PL/SQL就会为实参赋值。但是，如果有未捕获异常发生，PL/SQL就不会为实参赋值。
4、子程序参数模式总结
下表总结了我们应该知道关于参数模式的所有内容：
| IN | OUT | IN OUT |
|---|---|---|
| 默认 | 必须被指定 | 必须被指定 |
| 向子程序传值 | 向调用者返回值 | 向子程序传递初始值并向调用者返回 更新后的结果值 |
| 形参的作用同常量相同 | 形参的作用同变量相同 | 形参的作用同被初始化过的变量相同 |
| 形参不能被赋值 | 形参必须被赋值 | 形参应该被赋值 |
| 实参可以是常量、被初始化的变量、 文字或表达式 |
形参必须是变量 | 形参必须是变量 |
| 形参按引用传递 | 形参按值传递，除非使用了NOCOPY | 形参按值传递，除非使用了NOCOPY |
十、使用NOCOPY编译提示传递大型数据结构
假定子程序声明了一个IN模式参数、一个OUT模式参数和一个IN OUT模式参数。在调用子程序时，IN模式的是按引用传递的，即把指向IN模式的实参指针赋给形参。所以，两个参数引用都指向同一块内存地址,这块内存存放了实参的值。
默认情况下，OUT和IN OUT模式的参数都是按值传递的。就是把实参的值拷贝到对应的形参上。然后，如果子程序正常结束，被赋到OUT和IN OUT形参上的值就会拷贝到对应的实参上。
当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用NOCOPY提示来让编译器按引用传递OUT和IN OUT模式的参数。在下面的例子中，我们请求编译器按引用的方式来传递IN OUT参数my_staff：
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
记住，NOCOPY只是一个提示，而不是指令。所以，编译器也许仍旧会把my_staff按值传递，即使我们已经发出请求了。但是，通常情况下NOCOPY是可以成功的。下例中，我们把一个含有25000条记录的本地嵌套表中分别传递给两个没有任何功能的过程。没有使用NOCOPY的记录花费21秒，而使用的花费不到1秒：
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 NUMBER(5);
5 t2 NUMBER(5);
6 t3 NUMBER(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
20 get_time(t3);
21 dbms_output.put_line('Call Duration (secs)');
22 dbms_output.put_line('--------------------');
23 dbms_output.put_line('Just IN OUT: ' || TO_CHAR(t2 - t1));
24 dbms_output.put_line('With NOCOPY: ' || TO_CHAR(t3 - t2));
25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0
1、权衡NOCOPY所带来的良好性能
NOCOPY能为我们带来良好的性能，但它也能带来以下几个方面的影响：
- 因为NOCOPY只是一个提示，不是指令，所以编译器可以把NOCOPY参数按值或按引用的方式传递给子程序。所以，如果子程序因发生未捕获异常而退出时，我们就不能再信赖实参中的值了。
- 默认地，如果子程序异常退出，赋给OUT和IN OUT参数的值就不会拷贝到对应的实参上，这看起来有点像回滚操作。但是，对于按引用传递的NOCOPY参数来说，我们对形参所作的更改会立即在对应的实参上体现出来。所以，即使子程序是因异常发生而结束，它所做的变更内容也不会"回滚"。
- 目前，RPC协议允许我们只按值传递参数。例如，如果我们把一个含有NOCOPY参数的本地过程传到远程站点，这些参数就不再按引用传递了。
还有，使用NOCOPY会增加参数别名出现的可能性。
2、NOCOPY的限制
在以下几种情况中，PL/SQL编译器会忽略NOCOPY提示而直接使用按值传递参数的方法(不发生错误的情况下)：
- 实参是索引表中的一个元素。这个限制并不适用于整个索引表。
- 实参是受约束的(如精度或NOT NULL等)。这个约束不会扩展到元素或属性。同样，对长度受限的字符串也不适用。
- 实参和形参都是记录，其中一个或两个使用了%ROWTYPE或%TYPE声明，且在记录中对应域的约束不同。
- 实参和形参都是记录，实参是作为游标FOR循环的索引而被声明的(隐式声明)，记录之间对应域的约束不同。
- 实参传递需要进行隐式地数据类型转换。
- 子程序被外部或远程过程调用。
十一、使用子程序参数的默认值
如下例所示，我们可以为IN模式的参数初始化默认值。这样，我们就可以把不同个数的参数传给子程序，其中既可以使用参数默认值又可以使用我们传入的参数值覆盖掉默认值。并且，我们还可以在不修改每个子程序调用的情况下添加新的参数。
PROCEDURE create_dept(
new_dname VARCHAR2 DEFAULT ’temp’,
new_loc VARCHAR2 DEFAULT ’temp’
) IS
BEGIN
INSERT INTO dept
VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
...
END;
如果实参没有被传入，它所对应的形参就会使用定义时的默认值。下面是对过程create_dept的调用：
create_dept;
create_dept('MARKETING');
create_dept('MARKETING', 'NEW YORK');
第一个调用没有传入任何实参，所以子程序会使用两个默认的参数值；而第二个调用只为第一个参数指定了实参，这样，子程序会使用第一个参数传入的值和第二个参数的默认值；最后一个调用接受两个实参，它们将对应的形参默认值覆盖，子程序就使用两个传入的值。通常我们使用位置标示法覆盖形参的默认值，但是，我们不能靠省略实参来跳过它们对应的形参。例如，像下面这样把实参值"NEW YORK"和形参new_dname关联的做法是不对的：
create_dept('NEW YORK'); -- incorrect
create_dept(, 'NEW YORK'); -- not allowed
我们也不可以靠放置一个占位符来解决这个问题。例如，下面的调用就是不允许的：
create_dept(, 'NEW YORK'); -- not allowed
当出现这种情况，我们就需要使用名字标示法：
create_dept(new_loc => 'NEW YORK');
同样，我们也不能靠省略实参来为未初始化的形参赋空值。例如：
DECLARE
FUNCTION gross_pay(
emp_id IN NUMBER,
st_hours IN NUMBER DEFAULT 40,
ot_hours IN NUMBER
)
RETURN REAL IS
BEGIN
...
NULL;
END;
BEGIN
IF gross_pay(emp_num) > max_pay THEN ... -- not allowed
END;
上面代码中最后一句并不会把空值赋给ot_hours。如果确实想赋空值的话，我们需要显式地为其赋值：
IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...
或者是我们像下面这样吧ot_hours初始化为NULL：
ot_hours IN NUMBER DEFAULT NULL;
最后，当创建存储子程序时，我们不能在DEFAULT子句中使用主变量(绑定变量)。下面SQL*Plus例子就会引起绑定变量(bind variable)错误，因为在创建时，num只是一个值可能发生变化的占位符而已：
SQL> VARIABLE num NUMBER
SQL> CREATE FUNCTION gross_pay (emp_id IN NUMBER DEFAULT :num, ...
十二、理解子程序参数别名
为优化子程序调用，PL/SQL采取两种参数传递的方法，传值和传引用。
NOCOPY编译器提示会增大别名出现的可能性(即两个不同的名字引用同一块内存)。在子程序调用中，如果全局变量作为子程序的实参时就可能发生这种情况。这样，结果就无法确定，因为它依赖于编译器选择的传递参数的方式。
下例中，过程add_entry用两种方法引用数组lexicon：作为普通参数和作为全局变量。所以，当add_entry被调用时，标识符word_list和lexicon代表着同一个数组：
DECLARE
TYPE definition IS RECORD(
word VARCHAR2(20),
meaning VARCHAR2(200)
);
TYPE DICTIONARY IS VARRAY(2000) OF definition;
lexicon DICTIONARY := DICTIONARY();
PROCEDURE add_entry(word_list IN OUT NOCOPY DICTIONARY) IS
BEGIN
word_list(1).word := 'aardvark';
lexicon(1).word := 'aardwolf';
END;
BEGIN
lexicon.EXTEND;
add_entry(lexicon);
DBMS_OUTPUT.put_line(lexicon(1).word);
-- prints 'aardvark' if parameter was passed by value
-- prints 'aardwolf' if parameter was passed by reference
END;
过程add_entry的运算结果取决于编译器采取哪种参数传递方式。如果编译器采用按值传递，word_list和lexicon各自独立，它们各自内容的变化不会互相影响。但是，如果采用按引用传递的话，这个两个标识就是同一个数组的不同名称罢了。所以，当其中一个的内容发生变化时，另一个也会受到影响，随之变化。
在子程序中，如果同一实参出现的次数多于一次，也能产生别名。下例中，n2是一个IN OUT模式的参数，所以它的值在子程序退出之前是不会被更新的。这就是为什么第一个put_line打印结果是10(n的初识值)而第三个是20。但是，由于n3是一个NOCOPY参数，所以实参值会被立即更新为30。
DECLARE
n NUMBER := 10;
PROCEDURE do_something(
n1 IN NUMBER,
n2 IN OUT NUMBER,
n3 IN OUT NOCOPY NUMBER
) IS
BEGIN
n2 := 20;
DBMS_OUTPUT.put_line(n1); -- prints 10
n3 := 30;
DBMS_OUTPUT.put_line(n1); -- prints 30
END;
BEGIN
do_something(n, n, n);
DBMS_OUTPUT.put_line(n); -- prints 20
END;
因为它们都是指针，所以游标变量也能增加别名出现的可能性。如下面的例子，赋值后，emp_cv2就是emp_cv1的别名，因为它们都指向同一块查询工作区。所以，它们两个都能改变游标状态，这就是为什么第一次从emp_cv2就能取得第三行数据(不是第一行)，而在关闭emp_cv1之后，第二次从emp_cv2中就取不到数据：
PROCEDURE get_emp_data(emp_cv1 IN OUT empcurtyp, emp_cv2 IN OUT empcurtyp) IS
emp_rec emp%ROWTYPE;
BEGIN
OPEN emp_cv1 FOR
SELECT *
FROM emp;
emp_cv2 := emp_cv1;
FETCH emp_cv1
INTO emp_rec; -- fetches first row
FETCH emp_cv1
INTO emp_rec; -- fetches second row
FETCH emp_cv2
INTO emp_rec; -- fetches third row
CLOSE emp_cv1;
FETCH emp_cv2
INTO emp_rec; -- raises INVALID_CURSOR
...
END;
十三、子程序的重载
PL/SQL允许我们对子程序进行重载，也就是说，我们可以使用相同名称的子程序，只要保证它们的形式参数在个数或顺序或数据类型上不同即可。
假设我们要初始化下面两个索引表的前n行数据：
DECLARE
TYPE datetabtyp IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
TYPE realtabtyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
hiredate_tab datetabtyp;
sal_tab realtabtyp;
BEGIN
...
END;
下面是用于初始化索引表hiredate_tab的过程：
PROCEDURE initialize(tab OUT datetabtyp, n INTEGER) IS
BEGIN
FOR i IN 1 .. n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
下面是用于初始化索引表sal_tab的过程：
PROCEDURE initialize(tab OUT realtabtyp, n INTEGER) IS
BEGIN
FOR i IN 1 .. n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
因为这两个过程的处理过程是相同的，所以在逻辑上它们有着相同的名称。
我们可以把这两个重载过的初始化过程放到同一个块、子程序或包中。PL/SQL能根据它们的形参来判断调用哪个过程。下面例子中，PL/SQL调用哪个版本的初始化过程是跟参数类型是DateTabTyp还是RealTabTyp有关的：
DECLARE
TYPE datetabtyp IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
TYPE realtabtyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
hiredate_tab datetabtyp;
comm_tab realtabtyp;
indx BINARY_INTEGER;
PROCEDURE initialize(tab OUT datetabtyp, n INTEGER) IS
BEGIN
...
END;
PROCEDURE initialize(tab OUT realtabtyp, n INTEGER) IS
BEGIN
...
END;
BEGIN
indx := 50;
initialize(hiredate_tab, indx); -- calls first version
initialize(comm_tab, indx); -- calls second version
...
END;
1、重载的限制
只有本地或打包子程序，或是类型方法(type method)才可以被重载。因此，我们不能对独立的子程序进行重载。同样，对于只是形参的名称或参数传递模式不同的子程序也是不能重载的。例如，下面两个过程就不能被重载：
DECLARE
...
PROCEDURE reconcile (acct_no IN INTEGER) IS
BEGIN ... END;
PROCEDURE reconcile (acct_no OUT INTEGER) IS
BEGIN ... END;
如果子程序的形式参数类型是属于同一基类的子类，那它们也不能被重载。例如，我们不能重载下面两个过程，因为INTEGER和REAL就同属于NUMBER类型：
DECLARE
...
PROCEDURE charge_back (amount INTEGER) IS
BEGIN ... END;
PROCEDURE charge_back (amount REAL) IS
BEGIN ... END;
同样，如果两个子程序的形式参数类型的基类不同，但是基类型属于同一族(family)，那么它们也是不能被重载的。
例如，我们不能重载下面的两个过程，因为CHAR和LONG属于同一族：
DECLARE
SUBTYPE Delimiter IS CHAR;
SUBTYPE Text IS LONG;
...
PROCEDURE scan (x Delimiter) IS
BEGIN ... END;
PROCEDURE scan (x Text) IS
BEGIN ... END;
最后，我们也不能对只是返回值不同的两个函数进行重载。例如，下面的两个函数就不能被重载：
DECLARE
...
FUNCTION acct_ok (acct_id INTEGER) RETURN BOOLEAN IS
BEGIN ... END;
FUNCTION acct_ok (acct_id INTEGER) RETURN INTEGER IS
BEGIN ... END;
十四、如何解析子程序调用
下图演示了编译器是如何解析子程序调用的。当编译器遇到程序或函数调用时，它就尝试着找到与调用相匹配的声明。编译器首先会在当前作用域查找，如果有必要的话，它就会继续扩大查找范围。当编译器找到一个或多个名称匹配的子程序后，就会停止查找操作。
为了能在多个命名相同的子程序中解析出合理的调用内容，编译器就必须找到实参和形参精确匹配的一项。也就是说，它们必须在数量、顺序和数据类型上完全一致(除非其中一些参数使用默认值)。如果没有匹配项或是匹配项多于一个，编译器就会产生语义错误。
在下面的例子中，我们可以在函数reconcile中调用封闭过程swap，但是，由于在当前作用域内没有可用的swap声明，编译器就产生了一个错误：
PROCEDURE swap(n1 NUMBER, n2 NUMBER) IS
num1 NUMBER;
num2 NUMBER;
FUNCTION balance(...)
RETURN REAL IS
PROCEDURE swap(d1 DATE, d2 DATE) IS
BEGIN
...
END;
PROCEDURE swap(b1 BOOLEAN, b2 BOOLEAN) IS
BEGIN
...
END;
BEGIN
...
swap(num1, num2);
RETURN ...
END balance;
BEGIN
...
END;
1、继承中的重载实现
重载算法允许用一个子类的值替代一个基类的形式参数。这种能力被称为替代。如果不止一个被重载的过程实例与过程调用相匹配的话，那么下面的规则就决定到底哪个过程会被最终调用。
如果被重载的过程，他们的参数都继承于同一基类，那么最接近的基类类型的参数所在的过程会被调用。"最接近"的意思是它的继承深度比任何其他满足匹配条件的重载实例的参数的继承深度都要浅。如果有一个以上这样的情况发生，那么就会发生语义错误。
当有两个满足条件的重载实例并且一个重载过程里的参数类型要比其他过程中的更加接近实参，那么就会发生语义错误。
如果某些参数在对象类型层次上位置不同，而其他参数因数据类型不同以至于需要进行隐式地数据转换，也会引起语义错误。
例如，在这里我们创建一个3层深度的继承关系：
CREATE TYPE super_t AS OBJECT(
n NUMBER
)
NOT FINAL;
CREATE OR REPLACE TYPE sub_t UNDER super_t(
n2 NUMBER
)
NOT FINAL;
CREATE OR REPLACE TYPE final_t UNDER sub_t(
n3 NUMBER
)
;
我们声明两个只有参数类型不同的重载函数：
CREATE PACKAGE p IS
FUNCTION foo(arg super_t)
RETURN NUMBER;
FUNCTION foo(arg sub_t)
RETURN NUMBER;
END;
/
CREATE PACKAGE BODY p IS
FUNCTION foo(arg super_t)
RETURN NUMBER IS
BEGIN
RETURN 1;
END;
FUNCTION foo(arg sub_t)
RETURN NUMBER IS
BEGIN
RETURN 2;
END;
END;
/
现在我们声明final_t类型的变量，然后调用重载函数。由于在继承关系上sbu_t类型比super_t类型更接近于final_t类型，所以第二个函数会被调用。
SQL> set serveroutput on
SQL> declare
v final_t := final_t(1,2,3);
begin
dbms_output.put_line(p.foo(v));
end;
/
2
上面的例子中，被调用实例在编译期就确定了，下面我们看一个动态确定调用函数的例子：
CREATE TYPE super_t2 AS OBJECT(
n NUMBER,
MEMBER FUNCTION foo
RETURN NUMBER
)
NOT FINAL;
/
CREATE TYPE BODY super_t2 AS
MEMBER FUNCTION foo
RETURN NUMBER IS
BEGIN
RETURN 1;
END;
END;
/
CREATE OR REPLACE TYPE sub_t2 UNDER super_t2(
n2 NUMBER,
OVERRIDING MEMBER FUNCTION foo
RETURN NUMBER
)
NOT FINAL;
/
CREATE TYPE BODY sub_t2 AS
OVERRIDING MEMBER FUNCTION foo
RETURN NUMBER IS
BEGIN
RETURN 2;
END;
END;
/
CREATE OR REPLACE TYPE final_t2 UNDER sub_t2(
n3 NUMBER
)
;
/
下例先声明一个super_t2类型的实例v，因为我们把final_t2类型的值赋给了它，所以适当的函数实例就会被调用。这种特性被称为动态分派(dynamic dispatch)：
SQL> set serveroutput on
declare
v super_t2 := final_t2(1,2,3);
begin
dbms_output.put_line(v.foo);
end;
/
2
十五、使用表函数接受并返回多行结果
这章要讲述表函数和可能应用于表函数中的通用数据类型ANYTYPE、ANYDATA和ANYDATASET。
主要内容包括：
- 表函数概述
- 编写管道化表函数
- 并行表函数
- 表函数如何把输入数据流化处理
1、表函数一览
表函数就是一个能生成行集合(嵌套表或是变长数组)的函数，它所生成的集合能够像物理数据表那样查询或是直接赋给一个PL/SQL集合变量。我们可以在查询的FROM子句中把表函数当作数据表的名称来使用，或是在SELECT的列表中把表函数当作字段来使用。
表函数可以接受一个行集合参数。它的类型既可以是集合类型(如VARRAY或嵌套表)又可以是REF CURSOR类型。
表函数可以并发执行，并可以不通过中间阶段(intermediate stage)直接把结果流化到下一个进程。由表函数返回的集合中的行也能够被管道化处理——即，在表函数产生数据同时采用迭代法返回数据的方式，来代替以往在所有处理都完成后批量返回处理结果的方式。
表函数的流化、管道化和并发执行都能改善性能：
- 建立多线程并发执行的表函数
- 进程间消除中间阶段
- 改善查询响应时间：使用非管道化表函数时，由表函数创建的集合必须在表函数的查询结果返回之前被构建然后返回到服务器端。管道化让结果的行一产生就被返回。这样就能减少表函数的内存消耗，效果就像对象缓冲不需要物化整个集合一样。
- 结果行从表函数中产生后就能立即被返回，不要再等着整个集合被放到表或内存后在返回。
下图就是一个典型的数据处理方案，这里有几个传输过程，都是靠表函数实现。这个方案中，表函数并不是并发的，整个结果集合在每一次传输之后都要经过临时表。
相比之下，下图是对同一个情况使用流化和并行执行的演示：
2、什么是表函数
表函数可以返回一个数据表中行的集合。它们可以在查询语句的FROM子句中像数据表一样进行查询，也可以在查询的SELECT列表中把它们自己赋值给一个PL/SQL集合。
- 例子：查询一个表函数
下例演示了一个接受一个CLOB参数的表函数GetBooks，它能返回一个集合类型的BookSet_t实例。CLOB字段以某种格式存储了书籍的分类列表。表函数能返回所有的分类以及它们所对应的书籍列表。
集合类型BookSet_t的定义如下：
CREATE TYPE book_t AS OBJECT(
NAME VARCHAR2(100),
author VARCHAR2(30),
abstract VARCHAR2(1000)
);
CREATE TYPE bookset_t AS TABLE OF book_t;
CLOB存在于表Catalogs中：
CREATE TABLE catalogs
( NAME VARCHAR2(30),
cat CLOB);
函数GetBooks定义如下：
CREATE FUNCTION GetBooks(a CLOB) RETURN BookSet_t;
下面的查询可以返回所有的书籍分类以及它们对应的书籍列表。
SELECT c.NAME, book.NAME, book.author, book.abstract
FROM catalogs c, TABLE(getbooks(c.cat)) book;
- 例子：使用表函数的结果进行赋值操作
下例演示了如何把一个表函数的结果赋给PL/SQL集合变量。因为表函数是在SELECT查询列表中调用的，所以我们并不需要使用TABLE关键字。
CREATE TYPE numset_t AS TABLE OF NUMBER;
/
CREATE FUNCTION f1(x NUMBER)
RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1 .. x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
/
-- pipelined function in from clause
SELECT *
FROM TABLE(f1(3));
COLUMN_VALUE
------------
1
2
3
3 rows selected.
-- pipelined function in select list
SELECT f1(3)
FROM DUAL;
F1(3)
---------------------------------
NUMSET_T(1, 2, 3)
-- Since the function returns a collection, we can assign
-- the result to a PL/SQL variable.
DECLARE
func_result numset_t;
BEGIN
SELECT f1(3)
INTO func_result
FROM DUAL;
END;
/
3、什么是管道化表函数
如果生产者生产出的数据，其间不经过任何数据表或缓冲就立即被消费者所消耗掉，那么我们就说数据被管道化。
管道化能让一个表函数快速地返回行并能减少所需要的缓冲内存大小。
管道化能让table函数更快地返回数据行，减少内存消耗。
管道化表函数能以子集的形式返回结果集合。被返回的集合就好像一个"流"(stream)，能够按我们的要求进行选取。这就使它看起来像一个虚表。
管道化表函数有两种实现方式：
- 本地PL/SQL方法：消费者与生产者分别运行在各自的线程上(享有相同或不同的进程关联文)并通过管道或排队机制进行通信。这个方法有些像协同程序(co-routine)的执行。
- 接口方法：消费者和生产者运行在同一个线程上。生产者在生产出结果集后，显示地把控制权交给消费者。并且，生产者会将自身的状态进行保存，以便恢复到先前的状态。接口方法要求我们用过程化语言实现一组明确的接口。
在本章剩余的部分，术语表函数用于特指管道化表函数——就是说，表函数的结果集合是以迭代的管道化形式被返回的。
4、使用管道化表函数来进行转换
管道化表函数可以接受任何普通函数可以接受的参数。它能接受一个REF CURSOR类型参数，从REF CURSOR来取得行数据并对数据进行转换操作，然后把结果管道化输出(使用接口或本地PL/SQL方法)。
例如，下面的代码声明了一个StockPivot函数。这个函数能把行类型(Ticker， OpenPrice，ClosePrice)的数据转成两行类型(Ticker，PriceType，Price)的形式。对行("ORCL", 41, 42)使用函数StockPivot可以生成两行数据("ORCL", "O", 41)和("ORCL", "C", 42)：
表函数的输入数据可能来自于下面这样一个StockTable数据表：
CREATE TABLE stocktable (
ticker VARCHAR(4),
open_price NUMBER,
close_price NUMBER
);
下面是声明的内容：
-- Create the types for the table function's output collection
-- and collection elements
CREATE TYPE tickertype AS OBJECT(
ticker VARCHAR2(4),
pricetype VARCHAR2(1),
price NUMBER
);
CREATE TYPE tickertypeset AS TABLE OF tickertype;
-- Define the ref cursor type
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR
RETURN stocktable%ROWTYPE;
END refcur_pkg;
/
-- Create the table function
CREATE FUNCTION stockpivot(p refcur_pkg.refcur_t)
RETURN tickertypeset PIPELINED ...
/
下面是一个使用表函数StockPivot进行查询的例子：
SELECT *
FROM TABLE(stockpivot(CURSOR(SELECT *
FROM stocktable)));
在上面的查询中，管道化表函数函数StockPivot从游标子查询SELECT * FROM StockTable中取得数据，执行转换操作，最后把管道化结果作为一张表返给用户。函数把每一行输入都会被转换成两行(集合元素)输出。
上面的查询中，管道化表函数StockPivot从游标子查询SELECT * FROM StockTable中取得行数据，执行转换操作并把结果作为一张表返回给用户。函数为每一个输入行产生两个输出行。
要注意的是当游标子查询像上例中那样作为参数从SQL语句传递到一个REF CURSOR函数时，被引用的游标会在函数开始执行之前被打开。
5、编写管道化表函数
我们可以使用PIPELINED关键字来声明一个管道化表函数。这个关键字指示出函数可以反复地返回行数据。管道化表函数返回的类型必须是一个集合类型，如嵌套表或是变长数组。我们可以在模式等级(schema level)或包中声明这个集合。在函数内部，我们返回的是集合类型中的单个元素。
例如，下面是两个管道化表函数的声明(函数体将会在后面的例子中给出)。
CREATE FUNCTION GetBooks(cat CLOB) RETURN BookSet_t
PIPELINED IS ...;
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS...;
6、从表函数返回结果
在PL/SQL中，PIPE ROW语句能让表函数把一行数据进行管道化并继续后面的执行。该语句能让表函数一产生数据就立即把它们返回。(考虑到性能，PL/SQL运行时系统还提供了批量处理行的功能。)例如：
CREATE FUNCTION stockpivot(p refcur_pkg.refcur_t)
RETURN tickertypeset PIPELINED IS
out_rec tickertype := tickertype(NULL, NULL, NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p
INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.ticker := in_rec.ticker;
out_rec.pricetype := 'O';
out_rec.price := in_rec.openprice;
PIPE ROW(out_rec);
-- second row
out_rec.pricetype := 'C';
out_rec.price := in_rec.closeprice;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
例子中，PIPE ROW(out_rec)语句把表函数的数据管道化输出。out_rec是一条记录，它的类型与输出集合中的元素类型相同。
PIPE ROW只能用于管道化表函数的函数体中；如果在别的地方使用，就会有错误发生。PIPE ROW语句对于没有返回结果的管道化表函数来说是可以省略的。
管道化表函数必须要有一个不返回值的RETURN语句。它的作用是把控制权交给消费者并保证取不到数据的时候会得到一个NO_DATA_FOUND异常。
Oracle有三种特殊的SQL数据类型让我们动态地封装和访问类型描述、数据实例和任何其他SQL类型的数据实例集合，其中对象和集合类型也包含在内。我们还可以使用这三种特殊的类型来创建匿名类型，包括匿名集合类型。这三个类型是SYS.ANYTYPE、SYS.ANYDATA和SYS.ANYDATASET。SYS.ANYDATA类型在某些情况下作为表函数的返回值是很有用的。
7、PL/SQL表函数间的数据管道化处理
在连续的执行中，我们可以使用相似的协同执行方法把管道化结果能从一个PL/SQL表函数传到另一个。例如，下面的语句把管道化结果从函数g传递给函数f：
SELECT *
FROM TABLE(f(CURSOR(SELECT *
FROM TABLE(g()))));
并发执行工作原理也是类似的，只是每个函数在不同的进程(或进程集合)中执行。
8、查询Table函数
管道化表函数可以用在SELECT语句的FROM子句中。Oracle使用表函数中反复从结果集中检索数据。例如：
SELECT x.ticker, x.price
FROM TABLE(stockpivot(CURSOR(SELECT *
FROM stocktable))) x
WHERE x.pricetype = 'C';
注意：表函数返回的是一个集合。在某些情况下，如在*查询中使用SELECT *和查询引用一个PL/SQL变量或绑定变量，我们可能会需要CAST操作符来精确指定表函数返回的类型。
9、优化对表函数的多次调用
多次调用表函数，无论是同一个查询还是多个独立的查询，查询都会被多次执行。在默认情况下是没有缓冲或是可重用的行供我们使用。例如：
SELECT *
FROM TABLE(f(...)) t1, TABLE(f(...)) t2
WHERE t1.ID = t2.ID;
SELECT *
FROM TABLE(f());
SELECT *
FROM TABLE(f());
但是，如果表函数的输出内容是由传入它的参数决定的话，比如一个函数总是能为一个特定的传入参数组合产生同样的结果值，我们就可以声明函数为DETERMINISTIC，Oracle就会自动地把结果数据放入缓冲区。要注意，虽然数据库无法得知用DETERMINISTIC标记的函数是否真的是DETERMINISTIC，但只要其中一个不是，结果将是不可预知的。
- 从表函数结果中取得数据
PL/SQL游标和游标引用都能定义成使用表函数进行查询的形式。例如：
OPEN c FOR SELECT * FROM TABLE(f(...));
使用表函数作为查询内容的游标在取得数据的方式上和普通的游标一样。基于表函数的REF CURSOR赋值操作也没有什么特别之处。但是，SQL优化程序不会对PL/SQL语句中的内容进行优化，例如：
BEGIN
OPEN r FOR
SELECT * FROM TABLE(f(CURSOR (SELECT * FROM tab)));
SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
END;
下面这样的语句也不会被优化：
SELECT *
FROM TABLE(g(CURSOR(SELECT *
FROM TABLE(f(CURSOR(SELECT *
FROM tab))))));
10、使用游标变量传递数据
我们可以把行集合以REF CURSOR参数的形式传递给PL/SQL函数。例如，下面这个函数声明就是接受一个预定义的弱REF CURSOR类型SYS_REFCURSOR参数：
FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;
子查询的结果能被直接传入函数：
SELECT *
FROM TABLE(f(CURSOR(SELECT empno
FROM tab)));
上例中CURSOR关键字用来指明子查询的结果可以作为REF CURSOR参数传递给函数f。
预定义弱REF CURSOR类型SYS_REFCURSOR也是支持的。我们可以在不声明SYS_REFCURSOR之前使用它，并且不需要事先在包里创建REF CURSOR类型了。
如果要使用强REF CURSOR类型，我们就必须创建PL/SQL包然后在包内声明强REF CURSOR类型。同样，如果我们想使用强REF CURSOR类型作为表函数的参数，那么实际的REF CURSOR类型参数就必须和字段类型相匹配，否则就会产生错误。表函数中的弱REF CURSOR参数 只能使用PARTITION BY ANY子句进行分区。我们不能对REF CURSOR来使用范围或哈希分区。
- 例子：使用多个REF CURSOR输入变量
PL/SQL函数能接受多个REF CURSOR输入变量：
CREATE FUNCTION g(p1 pkg.refcur_t1, p2 pkg.refcur_t2) RETURN...
PIPELINED ... ;
函数g可以像下面这样调用：
SELECT *
FROM TABLE(g(CURSOR(SELECT empno FROM tab),
CURSOR(SELECT * FROM emp)));
我们可以用REF CURSOR把一个表函数的返回结果传到另一个表函数的参数：
SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));
- 例子：为查询显式打开一个REF CURSOR
我们可以显式地为一个查询打开一个REF CURSOR并把它传递给一个表函数：
BEGIN
OPEN r FOR
SELECT *
FROM TABLE(f(...));
-- Must return a single row result set.
SELECT *
INTO rec
FROM TABLE(g(r));
END;
这种情况下表函数会在执行完毕后自动关闭游标，所以我们就不需要显式地关闭游标了。
- 例子：把管道化表函数作为一个聚合函数使用
表函数能使用输入游标变量来计算聚合结果。下面的例子循环遍历一个输入行集合来计算一个重量的平均值：
DROP TABLE gradereport;
CREATE TABLE gradereport (student VARCHAR2(30), subject
VARCHAR2(30), weight NUMBER, grade NUMBER);
INSERT INTO gradereport
VALUES ('Mark', 'Physics', 4, 4);
INSERT INTO gradereport
VALUES ('Mark', 'Chemistry', 4, 3);
INSERT INTO gradereport
VALUES ('Mark', 'Maths', 3, 3);
INSERT INTO gradereport
VALUES ('Mark', 'Economics', 3, 4);
CREATE OR REPLACE TYPE gpa AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION weighted_average(input_values sys_refcursor)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows.
LOOP
FETCH input_values
INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average.
total_weight := total_weight + weight;
total := total + grade * weight;
END LOOP;
PIPE ROW(total / total_weight);
-- The function returns a single result.
RETURN;
END;
/
SHOW errors;
-- The result comes back as a nested table with a single row.
-- COLUMN_VALUE is a keyword that returns the contents of a nested table.
SELECT weighted_result.COLUMN_VALUE
FROM TABLE(weighted_average(CURSOR(SELECT weight, grade
FROM gradereport))) weighted_result;
COLUMN_VALUE
------------
3.5
11、在表函数内执行DML操作
要执行DML语句，我们就必须使用自治事务编译指示来声明这个表函数。这个编译指示能让函数只在一个不被多进程共享的匿名事务中执行。语法如下：
CREATE FUNCTION f(p SYS_REFCURSOR) return CollType PIPELINED IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN ... END;
并发执行时，每个表函数实例都会创建一个独立的事务。
12、在表函数上执行DML操作
表函数不能成为UPDATE、INSERT或DELETE语句的目标表，否则就会引起异常：
UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
INSERT INTO f(...) VALUES ('any', 'thing');
但是，我们可以通过表函数来创建视图，然后使用INSTEAD OF触发器来更新它。例如：
CREATE VIEW booktable AS
SELECT x.NAME, x.author
FROM TABLE(getbooks('data.txt')) x;
下面的INSTEAD OF触发器在用户向视图BookTable插入一行时会被触发：
CREATE TRIGGER booktable_insert
INSTEAD OF INSERT
ON booktable
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
...
END;
INSERT INTO BookTable VALUES (...);
所有作用于基于表函数创建的视图的DML操作，都能定义与其对应的INSTEAD OF触发器。
13、控制Table函数的异常
表函数的异常处理跟普通的用户自定义函数是一样的。
在有些语言中，如C和Java提供了用户异常控制机制。如果表函数中抛出的异常被捕获了，表函数就会执行异常控制程序然后再继续执行。退出异常控制程序后，控制权被转给封闭作用域。如果异常被清除了，程序就会正常地继续执行下去。
由表函数引起的未捕获异常会使它的父级事务回滚。
十六、表函数并行化处理
对于一个要并行处理的表函数来说，它必须有一个分区输入参数。一个表函数的并行性当且仅当下面两个条件都满足的时候才能被开启：
- 函数在它的声明中有一个PARALLEL_ENABLE子句
- PARTITION BY子句只指定了一个REF CURSOR参数
如果PARTITION BY子句没有指定任何输入REF CURSOR作为PARALLEL_ENABLE子句的一部分，SQL编译器就不能决定如何正确地对数据进行分区。要注意的是，只有强类型REF CURSOR参数才能用PARTITION BY子句指定，除非我们使用的是PARTITION BY ANY。
1、表函数的并行执行
一个出现在SELECT列表中的并行执行的函数，它的执行被压入栈中，数据的处理是由多个从属扫描处理(salve scan process)完成的。每个段上的函数执行都是基于函数的输入数据。
例如，下面的查询：
SELECT f(col1) FROM tab;
如果f是一个纯函数，那么它就可以被并行化。从属扫描处理所执行的SQL就类似于：
SELECT f(col1)
FROM tab
WHERE ROWID BETWEEN :b1 AND :b2;
每个从属扫描都会对一个ROWID区域范围内的数据进行操作并对这些的数据应用函数f。函数f必须在扫描处理中执行；它不能独立执行。
与出现在SELECT列表中的函数不同的是，表函数是在FROM子句中被调用的并且能返回一个数据集合。这会影响到表函数输入数据在从属扫描中的分区方法，因为分区方法必须要适用于表函数要执行的操作。(例如，一个ORDER BY操作需要范围分区(range-partitioned)的输入，而一个GROUP BY操作需要哈希分区(hash-partitioned)输入。)
表函数本身会在它的声明中指定要使用的合适的分区方法，然后再分为两个阶段进行执行。首先，在一组从属处理中直接把数据分区；然后在另一组从属处理中并行地对分过组的数据应用表函数。
例如，下面查询中的表函数接受一个REF CUROSR参数：
SELECT *
FROM TABLE(f(CURSOR(SELECT *
FROM tab)));
扫描工作由一组从属处理来执行，它们会对数据进行重新分区(分区方法基于函数声明中指定的方法)，同时在另一组从属处理执行表函数f。
2、输入数据的分区
表函数声明能够为一个REF CURSOR参数指定数据分区方法。语法如下：
CREATE FUNCTION f(p ref cursor type) RETURN rec_tab_type PIPELINED
PARALLEL_ENABLE(PARTITION p BY [{HASH | RANGE} (column list) | ANY ]) IS
BEGIN ... END;
PARALLEL_ENABLE子句中的PARTITION…BY短句可以指明要用哪个输入游标，如何进行分区和要分区的字段有哪些。
当字段列表中显示地指定了字段名时，就可以指定分区方法为RANGE或HASH。对于输入行中的指定字段分区方法可能是哈希(hash-)分区或范围(range-)分区。
ANY关键字表明函数与输入数据的分区方法无关。使用这个关键字时，运行时系统会在从属处理中随机地进行数据分区。这个关键字适用于那些接受行数据，进行字段操作，最后输出结果行的函数。
例如，与pivot相似的函数StockPivot，它接受一个行类型的输入数据：
(Ticker varchar(4), OpenPrice number, ClosePrice number)
然后生成：
(Ticker varchar(4), PriceType varchar(1), Price number)
所以，一行('ORCL', 41, 42)能生成两行('ORCL', 'O', 41)和('ORCL', 'C', 42)。
CREATE FUNCTION stockpivot(p refcur_pkg.refcur_t)
RETURN rec_tab_type PIPELINED
PARALLEL_ENABLE ( PARTITION p BY ANY ) IS
ret_rec rec_type;
BEGIN
FOR rec IN p LOOP
ret_rec.ticker := rec.ticker;
ret_rec.pricetype := 'O';
ret_rec.price := rec.openprice;
PIPE ROW(ret_rec);
ret_rec.ticker := rec.ticker; -- Redundant; not required
ret_rec.pricetype := 'C';
ret_rec.price := rec.closeprice;
push ret_rec;
END LOOP;
RETURN;
END;
函数f能被用于从表Stocks生成另外一张数据表：
INSERT INTO alternatestocktable
SELECT *
FROM TABLE(stockpivot(CURSOR(SELECT *
FROM stocktable)));
如果StockTable被并行扫描并用OpenPrice进行分区了操作，那么函数StockPivot就和数据流操作符(data-flow operator)结合起来对StockTable进行扫描，因此我们看到的分区结果是相同的。
另一方面，如果StockTable没有被分区，并且对它的扫描也不是并行执行的，那么insert into AlternateStockTable就是顺序执行的了。下面我们看一个稍微复杂一点的例子：
INSERT INTO alternatestocktable
SELECT *
FROM TABLE(f(CURSOR(SELECT *
FROM stocks))), TABLE(g(CURSOR( ... )))
WHERE join_condition;
其中g的定义如下：
CREATE FUNCTION g(p refcur_pkg.refcur_t) RETURN ... PIPELINED
PARALLEL_ENABLE (PARTITION p BY ANY)
BEGIN ... END;
如果g是并行执行的并且使用了ANY分区，那么并行insert就应归于同样的数据流操作符g。
无论什么时候指定关键字ANY，从属处理中都会对数据进行随机分区。这就意味着函数在同一个从属处理中执行，扫描是与输入参数相关。
这里需要没有被重新分区的数据。如果游标p本身非并行化，输入数据就会被随机分区。循环(round-robin)表队列会用于这样的分区。
3、叶级(Leaf-level)表函数的并行执行
要想让一个处理多行结果的函数进行并行执行，并且不需要接受多行输入，也不需要REF CURSOR，我们就需要把东西合理安排起来以便满足创建一个REF CURSOR的需求。那样，函数就可以把工作进行分区处理。
假设我们想要一个函数并行读取外部文件并返回它们所包含的记录。要想提供一个REF CURSOR所需要的内容，我们先创建一个数据表并把文件名作为数据填充进去。然后就可以把一个引用该表的REF CURSOR作为表函数(readfiles)的参数。下面的代码演示了整个操作过程：
CREATE TABLE filetab(filename VARCHAR(20));
INSERT INTO filetab
VALUES ('file0');
INSERT INTO filetab
VALUES ('file1');
...
INSERT INTO filetab
VALUES ('fileN');
SELECT *
FROM TABLE(readfiles(CURSOR(SELECT filename
FROM filetab)));
CREATE FUNCTION readfiles(p pkg.rc_t)
RETURN coll_type
PARALLEL_ENABLE ( PARTITION p BY ANY ) IS
ret_rec rec_type;
BEGIN
FOR rec IN p LOOP
done := FALSE;
WHILE(done = FALSE) LOOP
done := readfilerecord(rec.filename, ret_rec);
PIPE ROW(ret_rec);
END LOOP;
END LOOP;
RETURN;
END;
十七、表函数如何"流化"(Stream)传入的数据
表函数把从游标函数中取得的行进行排序(order)或分组(cluster)的方法被称为数据流化。一个函数可以按照以下几种方法把它的输入数据进行流化处理：
- 不对输入行的排序进行约束
- 按照一个主键字段或普通字段进行排序
- 按照一个特定的键进行分组
分组(Clustering)能让有着相同键值的行同时出现，但不能做排序。
我们可以在定义函数的时候使用ORDERY BY或CLUSTER BY子句控制输入流。
输入流能指定函数的执行顺序是顺序还是并行。
如果ORDER BY或CLUSTER BY子句没有指定，行就会随机地被输入。
注意：这里ORDER BY的含义不同于并行执行SQL语句中的ORDER BY子句。在一个SQL语句中，ORDER BY子句能全局地为整个数据集进行排序。在表函数中，ORDER BY子句只会对每个运行在从属处理程序中的表函数进行排序。
下面的例子是为输入流进行排序的语法。函数f接受(Region, Sales)形式的行并以(Region, AvgSales)的形势返回行，它为每个region计算出sales的平均值。
CREATE FUNCTION f(p ref_cursor_type)
RETURN tab_rec_type PIPELINED
CLUSTER p BY Region
PARALLEL_ENABLE(PARTITION p BY Region)
IS
ret_rec rec_type;
cnt NUMBER;
SUM NUMBER;
BEGIN
FOR rec IN p LOOP
IF (first rec in the group) THEN
cnt := 1;
SUM := rec.sales;
ELSIF (last rec in the group) THEN
IF (cnt <> 0) THEN
ret_rec.region := rec.region;
ret_rec.avgsales := SUM / cnt;
PIPE ROW(ret_rec);
END IF;
ELSE
cnt := cnt + 1;
SUM := SUM + rec.sales;
END IF;
END LOOP;
RETURN;
END;
1、并行执行中分区与分组的选择
分区和集群很容易让人迷惑，但它们确实是不同的东西。例如，在并行执行中，有时使用分区要比用分组高效的多。
考虑一下函数SmallAggr，它能为每一个department_id值计算出工资的总额，其中department_id可以是1，2或3。传入到函数的行可以按照department_id用HASH方法进行分区，这样的话，所有那些department_id等于1的行就会走向一个从属扫描，等于2的走向另一个等等。
输入行不需要在函数中按照department_id来进行分组操作。每个从属扫描都有一个1x3维的数组SmallSum[1..3]，其中每个department_id的聚合结果都会被放到SmallSum[department_id]中。另一方面，如果department_id唯一值的数目较多的话，我们就该考虑使用分组的方法来计算部门的聚合值，把每一个部门一次性地写入到磁盘中。
十八、调用者权限VS定义者权限
默认情况下，存储过程和SQL方法都是按照它们的定义者权限执行的，而不是它们的当前调用者权限。这样的定义者权限子程序是与某个特定的模式对象相绑定的。例如，假设dept表存放于scott和blake两个模式上，而下面的过程存放于scott模式上：
CREATE PROCEDURE create_dept(
my_deptno NUMBER,
my_dname VARCHAR2,
my_loc VARCHAR2
) AS
BEGIN
INSERT INTO dept
VALUES (my_deptno, my_dname, my_loc);
END;
再假设用户scott已经把该过程的EXECUTE权限授予用户blake。当用户blake调用过程时，INSERT语句会按照用户scott权限执行。同样，对于没有限定修饰词的表dept来说，也会在模式scott中解析。所以，即使是使用blake身份来调用过程，它还是会更新模式scott下的数据表dept。
那么，如何让一个模式中的子程序里操作另一个模式中的对象呢？一个方法就是使用完整的限定修饰词来引用要操作的对象，如：
INSERT INTO blake.dept ...
当是，这种方法是缺乏可移植性的。作为一个工作区，我们可以在SQL*Plus中把模式的名称定义成一个变量。
另一个方法就是把子程序拷贝到另外的模式中。但是，这样就不便于维护了。
一个较好的方法就是使用AUTHID子句，它能让存储过程和SQL方法根据当前调用者的权限和模式关联文来执行。这样的调用者权限(invoker-rights)子程序可以不绑定在特定的模式上。它们可以被许多用户调用。下面的过程create_dept就是按照当前用户的权限执行的，把行插入到调用者模式里的dept表中：
CREATE PROCEDURE create_dept(
my_deptno NUMBER,
my_dname VARCHAR2,
my_loc VARCHAR2
)
AUTHID CURRENT_USER AS
BEGIN
INSERT INTO dept
VALUES (my_deptno, my_dname, my_loc);
END;
1、调用者权限的优点
调用者权限的子程序能让我们重用代码并将程序逻辑集中化。它们对于那些在不同的模式中存储数据的应用程序来说特别有用。这种情况下，多个用户可以使用一份代码来管理他们的数据。
假设有一个使用定义者权限(DR)的过程analyze用于分析公司的销售情况。为了提供本地销售的汇总信息，过程分析必须访问每个地区的sales表。所以，如下表所示，过程必须放在每一个地区的站点中。这就会引起一个关于维护的问题。
要解决这个问题，公司就得在总部设置一个调用者权限的过程analyze。现在，如下图所示，所有地区的站点都可以使用同一个过程来查询它们自己的sales表了。
如果要严格限定对敏感数据的访问，我们可以使用调用者权限的子程序来调用定义者权限子程序。假设总部要用过程analyze来计算销售佣金并更新payroll表。
问题是analyze的当前用户并不能直接访问存储了雇员工资和其他敏感数据的payroll表。如下图所示，解决方法就是用过程analyze调用定义者权限的calc_comm过程，变相地更新payroll表。
2、用AUTHID子句为子程序指定权限
要实现调用者权限，就要使用AUTHID子句，它能指明子程序是按照它的定义者权限还是按照当前调用者权限来执行。该子句也指明引用的关联文是限定在所有者的模式中还是在当前调用者的模式中。
AUTHID子句只能出现在子程序、包或对象类型说明的头部，语法如下：
-- standalone function
CREATE [OR REPLACE] FUNCTION [schema_name.]function_name
[(parameter_list)] RETURN datatype
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
-- standalone procedure
CREATE [OR REPLACE] PROCEDURE [schema_name.]procedure_name
[(parameter_list)]
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
-- package spec
CREATE [OR REPLACE] PACKAGE [schema_name.]package_name
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
-- object type spec
CREATE [OR REPLACE] TYPE [schema_name.]object_type_name
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT
DEFINER是默认选项。在包或对象类型中，AUTHID子句可以作用于所有的子程序。
注意：大多数PL/SQL系统包(如DBMS_LOB、DBMS_PIPE、DBMS_ROWID、DBMS_SQL和UTL_FILE)都是使用调用者权限的。
3、执行当前子程序的用户是谁？
在一系列调用中，如果一个调用者权限的子程序是第一个被调用的子程序，那么当前用户就是会话用户。这样的状态会一直保持到一个定义者权限子程序被调用，这时，定义者权限的所有者就成了当前用户。如果定义者权限的子程序调用了调用者权限的子程序，它们就会按定义者权限来执行。当定义者权限的子程序退出时，控制权又回重新归还给原先的当前用户。
4、如何解析调用者权限子程序里的外部引用
如果我们把AUTHID指定为CURRENT_USER，当前用户的权限在运行时就会被检查，并且在当前用户模式中的外部引用也会被解析。但是，这只适用于那些处于下面几种情况的外部引用：
- SELECT、INSERT、UPDATE和DELETE数据操作语句
- LOCK TABLE事务控制语句
- OPEN和OPEN-FOR游标控制语句
- EXECUTE IMMEDIATE和OPEN-FOR-USING动态SQL语句
- 使用DBMS_SQL.PARSE()分析的SQL语句
对于所有的其他语句，在编译时所有者的权限就会被检查，外部引用会在所有者的模式范围内被解析。例如，下面赋值语句引用了打包函数balance。这个外部引用会在过程reconsile的所有者模式中被解析。
CREATE PROCEDURE reconcile (acc_id IN INTEGER)
AUTHID CURRENT_USER AS
bal NUMBER;
BEGIN
bal := bank_ops.balance(acct_id);
...
END;
- 模板对象(Template Objects)在调用者权限子程序中的需求
在调用者权限子程序中的外部引用是在运行时的当前用户模式范围内被解析的。但是，PL/SQL编译器必须在编译期解析所有的引用。所以，所有者必须在它的模式下事先创建模板对象。运行时，模板对象和实际对象必须相匹配,否则就会产生一个错误或是未知结果。
例如，假设用户scott创建了下面的数据表和独立的过程：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(15));
/
CREATE PROCEDURE evaluate(my_empno NUMBER)
AUTHID CURRENT_USER AS
my_ename VARCHAR2(15);
BEGIN
SELECT ename
INTO my_ename
FROM emp
WHERE empno = my_empno;
...
END;
/
现在，假设用户blake创建了一个相似的数据表，然后调用过程evaluate：
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(15),
my_empno NUMBER(4)); -- note extra column
/
DECLARE
...
BEGIN
...
scott.evaluate(7788);
END;
/
过程调用虽没有错误，但在用户blake创建的表中漏掉了字段my_empno。这是因为在当前用户的模式中实际存在的表与编译时使用的模板表并不匹配。
5、覆盖调用者权限子程序中的默认命名解析
有时，我们也许想要覆盖掉默认的调用者权限行为。假定用户scott定义了下面的独立过程。要注意SELECT语句调用了一个外部的函数。正常情况下，这个外部引用会在当前用户的模式中解析。
CREATE PROCEDURE calc_bonus (emp_id INTEGER)
AUTHID CURRENT_USER AS
mid_sal REAL;
BEGIN
SELECT median(sal) INTO mid_sal FROM emp;
...
END;
要想在所有者的模式中解析引用，就要使用CREATE SYNONYM语句为函数创建一个公共的同义词，如下例所示：
CREATE PUBLIC SYNONYM median FOR scott.median;
这样做是有效的，除非当前用户定义了一个名为median函数或是一个私有同义词median。另外，我们也可以使用完整的限定修饰词对引用加以限制：
BEGIN
SELECT scott.median(sal) INTO mid_sal FROM emp;
...
END;
这样做是有效的，除非当前用户定义了一个名为median函数或是一个私有同义词median。
6、授权执行调用者权限子程序
要直接调用子程序，用户必须拥有对被调用程序的EXECUTE权限。获取了EXECUTE权限，用户就能拥有以下功能：
- 直接调用被授权子程序
- 编译那些调用被授权子程序的函数和过程
在当前用户模式中解析外部引用(如DML语句中的引用)时，当前用户必须拥有访问子程序所引用的模式对象的权限。对于其他所有的外部引用(如函数调用)，所有者的权限在编译时就会被检查而不会等到运行时才检查。
一个定义者权限的子程序总是在它的所有者的安全域之下进行操作，无论当前调用它的用户是谁。所以，所有者必须要拥有访问子程序所引用的模式对象的权限。
我们可以编写一个由多个子程序组成的程序，其中的一部分是定义者权限，另一部分是调用者权限。然后，就可以使用EXECUTE权限来对程序的入口点进行限制。这样，一个入口子程序的用户就能间接执行其它子程序而不是直接执行。
- 例子：授权执行调用者权限子程序
假设用户util把子程序fft的EXECUTE权限授予用户app：
GRANT EXECUTE ON util.fft TO app;
现在，用户app就能编译那些引用子程序fft的函数和过程了。运行时，就不再会有权限检查了。所以，如下图所示，用户util并不需要把EXECUTE权限赋给每个可以间接调用fft的用户。
这里需要注意的是，子程序util.fft只能由调用者权限的子程序app.entry直接调用。所以，用户util必须把EXECUTE权限赋给用户app。当util.fft执行的时候，它的当前用户可能是app、scott或black，即使scott和blake并没有相应的EXECUTE权限。
7、对调用者权限的子程序使用角色
一个子程序中角色的使用取决于它是按定义者权限执行还是按调用者权限执行。在一个定义者权限的子程序中，所有的角色都是禁用的。角色不能用于权限检查，并且不能被设置。
如果是一个调用者权限的子程序，角色就是可用的(除非子程序直接或间接地被一个定义者权限的子程序所调用)。这时的角色会被用于权限检查，并且我们可以使用本地动态SQL来设置会话角色。但是，我们不能用角色来授予访问模板对象的权限，因为角色只是应用在运行时而非编译时。
8、在调用者权限子程序中使用视图和数据库触发器
对于执行一个使用视图表达式的调用者权限的子程序来说，视图的所有者，而非视图的用户，就被当作当前的用户。例如，假定用户scott创建了下面的视图。调用者权限的函数layout总会按用户scott的权限执行的，这里的视图所有者就是用户scott。
CREATE VIEW payroll AS SELECT layout(3) FROM dual;
这项规则也同样适用于数据库触发器。
9、在调用者权限子程序中使用数据库连接
调用者权限只能影响到一种数据库连接——current-user连接，它的创建方法如下：
CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER
USING connect_string;
current-user连接能让我们作为另一个用户连接到一个远程数据库，并且拥有另一个用户相应的权限。连接过程中，Oracle就会使用当前用户的用户名(必须是全局用户)。假设用户blake所拥有的调用者权限子程序引用了下面的数据库连接。如果全局用户scott调用了子程序，那么程序就会被当作当前用户用户scott连接到数据库Dallas。
CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...
如果是一个定义者权限的子程序，当前用户就是blake。所以子程序会作为全局用户blake连接到数据库Dallas上。
10、在调用者权限子程序中使用对象类型
要定义在任何模式中都可以使用的对象类型，就要使用AUTHID CURRENT_USER子句。假定用户blake创建了下面的对象类型：
CREATE TYPE num
AUTHID CURRENT_USER
AS OBJECT(
x NUMBER,
STATIC PROCEDURE new_num(
n NUMBER,
schema_name VARCHAR2,
table_name VARCHAR2
)
);
CREATE TYPE BODY num AS
STATIC PROCEDURE new_num(
n NUMBER,
schema_name VARCHAR2,
table_name VARCHAR2
) IS
sql_stmt VARCHAR2(200);
BEGIN
sql_stmt := 'INSERT INTO '
|| schema_name
|| '.'
|| table_name
|| ' VALUES (blake.Num(:1))';
EXECUTE IMMEDIATE sql_stmt
USING n;
END;
END;
然后，用户blake把对象类型Num的EXECUTE权限授予用户scott：
GRANT EXECUTE ON Num TO scott;
最后，用户scott创建对象表来储存Num类型的对象，并调用过程new_num填充数据表：
CONNECT scott/tiger;
CREATE TABLE num_tab OF blake.num;
/
BEGIN
blake.num.new_num(1001, 'scott', 'num_tab');
blake.num.new_num(1002, 'scott', 'num_tab');
blake.num.new_num(1003, 'scott', 'num_tab');
END;
/
这些调用都会成功实现，因为过程是按它的当前用户scott权限执行，而不是它的所有者blake。
对于对象类型层次中的子类型来说，下面的规则是适用的：
- 如果子类没有使用AUTHID子句，它就继承了基类的AUTHID。
- 如果子类使用了AUTHID子句，那它的AUTHID就必须和基类的AUTHID相匹配。并且，如果AUTHID是DEFINER，基类和子类就都得创建在同一个模式中。
一个调用者权限的实例方法是按调用者权限执行，而不是实例的创建者。假设Person是一个调用者权限的对象类型，用户scott创建了Person类型的对象p1。如果用户blake调用了实例方法change_job来操作对象p1，那么当前用户就是blake，而不是scott。思考下面的例子：
-- user blake creates a definer-rights procedure
CREATE PROCEDURE reassign(p person, new_job VARCHAR2) AS
BEGIN
-- user blake calls method change_job, so the
-- method executes with the privileges of blake
p.change_job(new_job);
...
END;
-- user scott passes a Person object to the procedure
DECLARE
p1 person;
BEGIN
p1 := person(...);
blake.reassign(p1, 'CLERK');
...
END;
十九、理解并使用递归
递归是一门简化算法设计的强大技术，递归意味着自我引用。典型的例子就是最初应用于建立兔子繁殖增长模型的斐波纳契序列(Fibonacci sequence)，(0, 1, 1, 2, 3, 5, 8, 13, 21, ...)。序列中的每一项都是前两项之和，这样不断的持续下去。
看一下下面的例子，我们定义了n的阶乘(n!)：
n! = n * (n - 1)!
1、什么是递归子程序
一个递归子程序就是一个自我调用的程序。每个递归调用都会创建一个新的子程序实例，其中包括参数、变量、游标和异常。同样，新的SQL语句实例也会在后续的每一级递归调用中被创建。
一定要小心地使用递归调用。如果我们把它放在一个游标FOR循环内或是OPEN和CLOSE语句之间的话，每次调用都会打开另外一个游标。这样，我们的程序开打的游标个数就有可能超过Oracle数据库初始化参数OPEN_CURSORS所限制可以打开的游标个数。
在一个递归程序中至少要有两条路：一条用于递归调用，而另一个不用于递归调用。并且递归程序中至少应有一条路能到达终止条件。否则就会永远的递归下去(理论上是这样)。实际上，如果递归程序无限制地执行下去，PL/SQL最终会耗光内存并抛出预定义异常STORAGE_ERROR。
- 递归例子：计算阶乘
为了解决某些编程问题，我们必须反复地执行一个语句序列直到满足某个条件为止。我们可以使用迭代或递归来解决这样的问题。使用递归可以把问题分割为自身的简化版本。例如，我们可以像下面这样计算3!：
0! = 1 -- by definition
1! = 1 * 0! = 1
2! = 2 * 1! = 2
3! = 3 * 2! = 6
要实现这个算法，我们可以编写一个递归函数：
FUNCTION fac(n POSITIVE)
RETURN INTEGER IS -- returns n!
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
每次递归调用，n就会减一。最终，n会变成1递归停止。
- 递归例子：遍历树形结构的数据
思考下面的过程，它的作用是找到指定经理下的员工。过程声明了两个形式参数，mgr_no和tier，mgr_no和tier分别代表了经理的雇员编号和他或她在部门组织中的级别。
PROCEDURE find_staff(mgr_no NUMBER, tier NUMBER := 1) IS
boss_name VARCHAR2(10);
CURSOR c1(boss_no NUMBER) IS
SELECT empno, ename
FROM emp
WHERE mgr = boss_no;
BEGIN
/* Get manager's name. */
SELECT ename
INTO boss_name
FROM emp
WHERE empno = mgr_no;
IF tier = 1 THEN
INSERT INTO staff -- single-column output table
VALUES (boss_name || ' manages the staff');
END IF;
/* Find staff members who report directly to manager. */
FOR ee IN c1(mgr_no) LOOP
INSERT INTO staff
VALUES ( boss_name
|| ' manages '
|| ee.ename
|| ' on tier '
|| TO_CHAR(tier));
/* Drop to next tier in organization. */
find_staff(ee.empno, tier + 1); -- recursive call
END LOOP;
COMMIT;
END;
调用的时候，过程接受参数mgr_no，但是tier使用默认值。例如，我们可以像下面这样调用过程：
find_staff(7839);
过程把用于查找后续低级别职员的mgr_no传递给游标FOR循环游标。每次递归调用都会创建一个新的FOR循环实例并打开另外一个游标，但是前一个游标会把位置保持在结果集中的下一行。
当取得数据失败的时候，游标就会自动关闭并退出FOR循环。因为递归调用处在FOR循环的内部，所以递归会停止。不像初始化调用，每个递归调用都会把第二个实参(下一个tier)传递给过程。
- 提示：执行带有CONNECT BY子句的递归查询
上面的例子演示了递归的使用方法，但是没有充分利用面向集合的SQL语句。对于执行同一项任务，我们可能想要比较一下递归过程和下面的SQL语句的性能：
INSERT INTO staff
SELECT PRIOR ename || ' manages ' || ename || ' on tier '
|| TO_CHAR(LEVEL - 1)
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;
SQL语句要明显的比递归过程要快。但是，使用过程会更加灵活。例如，一个多表查询是不能包含CONNECT BY子句。与过程不同的是，SQL语句不能对连接进行修改。(连接就是把来自于两个或多个数据表中的行关联起来。)此外，过程还能够进行数据处理而单条的SQL语句不能。
2、使用相互递归
子程序直接或间接互相调用就成为相互递归。在下面的例子中，布尔函数odd和even，它们分别用于判断一个数字是奇数还是偶数，互相调用。我们需要对odd进行向前声明，因为even必须在odd声明之后才能调用它。
FUNCTION even(n NATURAL)
RETURN BOOLEAN IS
BEGIN
IF n = 0 THEN
RETURN TRUE;
ELSE
RETURN odd(n - 1); -- mutually recursive call
END IF;
END even;
FUNCTION odd(n NATURAL)
RETURN BOOLEAN IS
BEGIN
IF n = 0 THEN
RETURN FALSE;
ELSE
RETURN even(n - 1); -- mutually recursive call
END IF;
END odd;
当一个正整数n被传到odd或even时，函数会轮流地互相调用。在每一次调用中，n值都会减少。最终，n会变成零，最终的结果可能是TRUE或FALSE。例如，把数字4传递给odd就会产生下面的调用顺序：
odd(4)
even(3)
odd(2)
even(1)
odd(0) -- returns FALSE
另一方面，把4传递给even就会产生下面的调用顺序：
even(4)
odd(3)
even(2)
odd(1)
even(0) -- returns TRUE
3、递归VS迭代
与迭代不同的是，递归对PL/SQL来说不是必需的。任何能用递归解决的问题都能用迭代解决。并且子程序的迭代版本要比递归版本容易设计得多。但是，递归版本通常比较简洁，代码少并且容易调试。比较下面的两个用于计算斐波纳契数的函数：
-- recursive version
FUNCTION fib(n POSITIVE)
RETURN INTEGER IS
BEGIN
IF (n = 1) OR(n = 2) THEN
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2);
END IF;
END fib;
-- iterative version
FUNCTION fib(n POSITIVE)
RETURN INTEGER IS
pos1 INTEGER := 1;
pos2 INTEGER := 0;
accumulator INTEGER;
BEGIN
IF (n = 1) OR(n = 2) THEN
RETURN 1;
ELSE
accumulator := pos1 + pos2;
FOR i IN 3 .. n LOOP
pos2 := pos1;
pos1 := accumulator;
accumulator := pos1 + pos2;
END LOOP;
RETURN accumulator;
END IF;
END fib;
递归版本的fib比迭代版本精致得多。但是，迭代版本要更高效；它运行速度快并且使用内存少。这是因为递归调用需要额外的时间和内存。随着递归调用变得越来越大，效率差异也就越来越明显。当我们预计的递归调用次数比较少的时候，就可以考虑使用可读性好的递归方法了。
二十、调用外部子程序
PL/SQL是一个强大的开发工具；我们可以用它做许多事情。但是，它主要是针对于SQL事务处理的。所以，有些任务在像C这样擅长机器精度(machine-precision)计算的低阶语言中处理会更快。而其他一些工作在像Java这样的面向对象语言中处理起来会更容易些。
为了支持这样的特殊目的的处理，我们可以用PL/SQL调用说明(call specs)来调用外部子程序(也就是用其他语言编写的子程序)。这就能让我们从PL/SQL中使用这些语言的增强功能。
例如，我们可以从任何PL/SQL块、子程序或包中调用Java存储过程。假设我们把下面的Java类存放到数据库中：
import java.sql.*;
import oracle.jdbc.driver.*;
public class Adjuster {
public static void raiseSalary(int empNo, float percent)
throws SQLException {
Connection conn = new OracleDriver().defaultConnection();
String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1, (1 + percent / 100));
pstmt.setInt(2, empNo);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
System.err.println(e.getMessage());
}
}
}
类Adjuster只有一个按照给定百分比为雇员涨工资的方法。因为raiseSalary是一个void方法，我们可以用下面的调用说明把它发布为一个过程：
CREATE PROCEDURE raise_salary (empno NUMBER, pct NUMBER)
AS LANGUAGE JAVA
NAME 'Adjuster.raiseSalary(int, float)';
以后，我们就可以从匿名PL/SQL块中调用过程raise_salary了：
DECLARE
emp_id NUMBER;
percent NUMBER;
BEGIN
-- get values for emp_id and percent
-- call external subprogram
raise_salary(emp_id, percent);
一般，外部C子程序用于与嵌入式系统交互，解决工程学问题、分析数据或控制实时设备的运作。外部C程序能让我们扩展数据库服务器的功能，并把受计算量限制的程序从客户端移植到服务器端，这样它们就能执行的更快。
二十一、使用PL/SQL Server Page创建动态网页
PL/SQL Server Page(PSP)能让我们开发动态网页。它们使用存储过程编写HTML代码。
我们可以使用特殊的标记把PL/SQL脚本嵌套到HTML中。当浏览器请求页面时，脚本就被执行。脚本可以接受参数，查询或更新数据库，然后在自定义画面中显示结果。
在开发中，PSP扮演一个有着静态布局和动态内容的角色。我们可以先用HTML编辑工具设计布局，并留出动态内容的部分。然后就可以编写PL/SQL脚本生成动态内容。这些工作完成后，我们就可以把PSP文件作为存储过程保存到数据库中。