第九章 PL/SQL包
一、什么是PL/SQL包
包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。说 明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体部分完全定义游标和子程序，并对说明中的内容加以实现。
如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。我们可以调试、增强或替换一个包体而不同改变接口(包说明)。
我们可以从SQL*Plus中使用CREATE PACKAGE语句来创建一个包。语法如下：
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS }
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition ...]
[record_type_definition ...]
[subtype_definition ...]
[collection_declaration ...]
[constant_declaration ...]
[exception_declaration ...]
[object_declaration ...]
[record_declaration ...]
[variable_declaration ...]
[cursor_spec ...]
[function_spec ...]
[procedure_spec ...]
[call_spec ...]
[PRAGMA RESTRICT_REFERENCES(assertions) ...]
END [package_name];
[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS }
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition ...]
[record_type_definition ...]
[subtype_definition ...]
[collection_declaration ...]
[constant_declaration ...]
[exception_declaration ...]
[object_declaration ...]
[record_declaration ...]
[variable_declaration ...]
[cursor_body ...]
[function_spec ...]
[procedure_spec ...]
[call_spec ...]
[BEGIN
sequence_of_statements]
END [package_name];]
在说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。
包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。
AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。
一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本(SQL counterpart)中来发布程序。
1、PL/SQL包举例
在下面的例子中，我们把一个记录类型、游标和两个employment过程进行打包。要注意，过程hire_employee使用数据库序列empno_seq和函数SYSDATE分别插入到字段雇员编号和雇佣日期。
CREATE OR REPLACE PACKAGE emp_actions AS -- spec
TYPE emprectyp IS RECORD (
emp_id INT,
salary REAL
);
CURSOR desc_salary RETURN emprectyp;
PROCEDURE hire_employee(
ename VARCHAR2 ,
job VARCHAR2 ,
mgr NUMBER ,
sal NUMBER ,
comm NUMBER ,
deptno NUMBER
);
PROCEDURE fire_employee(emp_id NUMBER );
END emp_actions;
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body
CURSOR desc_salary RETURN emprectyp IS
SELECT empno, sal
FROM emp
ORDER BY sal DESC ;
PROCEDURE hire_employee(
ename VARCHAR2 ,
job VARCHAR2 ,
mgr NUMBER ,
sal NUMBER ,
comm NUMBER ,
deptno NUMBER
) IS
BEGIN
INSERT INTO emp
VALUES (empno_seq.NEXTVAL ,
ename,
job,
mgr,
SYSDATE ,
sal,
comm,
deptno);
END hire_employee;
PROCEDURE fire_employee(emp_id NUMBER ) IS
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
END fire_employee;
END emp_actions;
只有在包说明部分的声明内容对应用程序才是可见可访问的；包体的详细实现是不可见不可访问的。所以，我们可以在不重新编译调用程序的前提下修改包体(实现)。
二、PL/SQL包的优点
包提供了几个优点：模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。
- 模块化
包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。
- 轻松的程序设计
设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。
- 信息隐藏
有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程 序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样， 对用户隐藏实现细节也能保证包的完整性。
- 附加功能
打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。
- 良好的性能
在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。
三、理解包说明
包说明包括了公有声明。这些声明的作用于对于数据库模式来说是本地的，对于包来说是全局的。所以，被声明的内容可以从应用程序中和包的任何地方访问。下图演示了包的作用范围：
说明中列出了包中对应用程序所有可用的资源。例如，下面的声明演示了一个接受INTEGER类型的参数并返回一个INTEGER结果的函数fac：
FUNCTION fac (n INTEGER ) RETURN INTEGER ; -- returns n!
这些就是我们要调用的函数的所有信息。我们并不需要考虑它的实现细节(如，是使用迭代还是递归)。
只有子程序和游标有实现部分。所以，如果一个说明只有类型、常量、变量、异常的声明和调用说明，那么包体就没有必要的了。下面就是一个没有包体的包：
CREATE PACKAGE trans_data AS -- bodiless package
TYPE timerec IS RECORD (
minutes SMALLINT ,
hours SMALLINT
);
TYPE transrec IS RECORD (
CATEGORY VARCHAR2 ,
ACCOUNT INT,
amount REAL ,
time_of timerec
);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION ;
END trans_data;
包trans_data不需要包体，因为类型、常量、变量和异常并没有实现部分。这样的包能让我们定义全局变量，可供子程序和数据库触发器使用。
1、引用包的内容
如果要引用包内声明的类型、常量、变量、异常和子程序等，就得使用点标识：
package_name.type_name
package_name.item_name
package_name.subprogram_name
package_name.call_spec_name
我们可以从数据库触发器、存储子程序、3GL(第三代程序语言)应用程序和各种Oracle工具中引用包中的内容。例如，我们可以从SQL*Plus中调用过程hire_employee：
SQL > CALL emp_actions.hire_employee('TATE' , 'CLERK' , ...);
下例中，我们可以从一个嵌入到Pro*C的匿名PL/SQL块调用同样的过程。实参emp_name和job_title是主变量(即声明在主环境中的变量)。
EXEC SQL EXECUTE
BEGIN
emp_actions.hire_employee(:emp_name, :job_title, ...);
- 约束
我们不能直接或间接地引用远程打包变量。例如，我们不能远程调用下面的过程，因为它在参数初始化子句中引用了打包变量：
CREATE PACKAGE random AS
seed NUMBER ;
PROCEDURE initialize (starter IN NUMBER := seed, ...);
同样，我们也不能在包的内部引用主变量。
四、理解包体
包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在 包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完全一致。否 则，PL/SQL就会抛出异常，如下例所示：
CREATE PACKAGE emp_actions AS
...
PROCEDURE calc_bonus(date_hired emp.hiredate%TYPE , ...);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
...
PROCEDURE calc_bonus(date_hired DATE , ...) IS
-- parameter declaration raises an exception because 'DATE'
-- does not match 'emp.hiredate%TYPE' word for word
BEGIN ... END ;
END emp_actions;
包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。
在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。
包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。
请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的内容。
五、包特性的例子
下面是一个名为emp_actions的包。包说明声明了类型、游标、异常和子程序：
- 类型EmpRecTyp和DeptRecTyp
- 游标desc_salary
- 异常invalid_salary
- 函数hire_employee和raise_salary
- 过程fire_empire和raise_salary
在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。创建包时，它就会被存放在Oracle数据库*广泛地调用。
CREATE PACKAGE emp_actions AS
/* Declare externally visible types, cursor, exception. */
TYPE emprectyp IS RECORD (
emp_id INT,
salary REAL
);
TYPE deptrectyp IS RECORD (
dept_id INT,
LOCATION VARCHAR2
);
CURSOR desc_salary RETURN emprectyp;
invalid_salary EXCEPTION ;
/* Declare externally callable subprograms. */
FUNCTION hire_employee(
ename VARCHAR2 ,
job VARCHAR2 ,
mgr REAL ,
sal REAL ,
comm REAL ,
deptno REAL
)
RETURN INT;
PROCEDURE fire_employee(emp_id INT);
PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL );
FUNCTION nth_highest_salary(n INT)
RETURN emprectyp;
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package
/* Fully define cursor specified in package. */
CURSOR desc_salary RETURN emprectyp IS
SELECT empno, sal
FROM emp
ORDER BY sal DESC ;
/* Fully define subprograms specified in package. */
FUNCTION hire_employee(
ename VARCHAR2 ,
job VARCHAR2 ,
mgr REAL ,
sal REAL ,
comm REAL ,
deptno REAL
)
RETURN INT IS
new_empno INT;
BEGIN
SELECT empno_seq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, SYSDATE , sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;
PROCEDURE fire_employee(emp_id INT) IS
BEGIN
DELETE FROM emp
WHERE empno = emp_id;
END fire_employee;
/* Define local function, available only inside package. */
FUNCTION sal_ok(RANK INT, salary REAL )
RETURN BOOLEAN IS
min_sal REAL ;
max_sal REAL ;
BEGIN
SELECT losal, hisal
INTO min_sal, max_sal
FROM salgrade
WHERE grade = RANK;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;
PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL ) IS
salary REAL ;
BEGIN
SELECT sal
INTO salary
FROM emp
WHERE empno = emp_id;
IF sal_ok(grade, salary + amount) THEN
UPDATE emp
SET sal = sal + amount
WHERE empno = emp_id;
ELSE
RAISE invalid_salary;
END IF ;
END raise_salary;
FUNCTION nth_highest_salary(n INT)
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 starts here
INSERT INTO emp_audit
VALUES (SYSDATE , USER , 'emp_actions' );
number_hired := 0;
END emp_actions;
请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表emp_audit。同样，变量number_hired也只被初始化一次。
每次hire_employee被调用的时候，变量nubmer_hired就会被更新。但是，number_hired所记录的数字是与特定的会话相关的。也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。
在下面的例子中，我们把一些典型的银行事务进行打包。假设借款与贷款事务都是在营业时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。
CREATE PACKAGE bank_transactions AS
/* Declare externally visible constant. */
minimum_balance CONSTANT REAL := 100.00;
/* Declare externally callable procedures. */
PROCEDURE apply_transactions;
PROCEDURE enter_transaction(acct INT, kind CHAR , amount REAL );
END bank_transactions;
CREATE PACKAGE BODY bank_transactions AS
/* Declare global variable to hold transaction status. */
new_status VARCHAR2 (70) := 'Unknown' ;
/* Use forward declarations because apply_transactions
calls credit_account and debit_account, which are not
yet declared when the calls are made. */
PROCEDURE credit_account(acct INT, credit REAL );
PROCEDURE debit_account(acct INT, debit REAL );
/* Fully define procedures specified in package. */
PROCEDURE apply_transactions IS
/* Apply pending transactions in transactions table
to accounts table. Use cursor to fetch rows. */
CURSOR trans_cursor IS
SELECT acct_id, kind, amount
FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status; -- to lock rows
BEGIN
FOR trans IN trans_cursor LOOP
IF trans.kind = 'D' THEN
debit_account(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
credit_account(trans.acct_id, trans.amount);
ELSE
new_status := 'Rejected' ;
END IF ;
UPDATE transactions
SET status = new_status
WHERE CURRENT OF trans_cursor;
END LOOP ;
END apply_transactions;
PROCEDURE enter_transaction(
/* Add a transaction to transactions table. */
acct INT, kind CHAR , amount REAL ) IS
BEGIN
INSERT INTO transactions
VALUES (acct, kind, amount, 'Pending' , SYSDATE );
END enter_transaction;
/* Define local procedures, available only in package. */
PROCEDURE do_journal_entry(
/* Record transaction in journal. */
acct INT, kind CHAR , new_bal REAL ) IS
BEGIN
INSERT INTO journal
VALUES (acct, kind, new_bal, SYSDATE );
IF kind = 'D' THEN
new_status := 'Debit applied' ;
ELSE
new_status := 'Credit applied' ;
END IF ;
END do_journal_entry;
PROCEDURE credit_account(acct INT, credit REAL ) IS
/* Credit account unless account number is bad. */
old_balance REAL ;
new_balance REAL ;
BEGIN
SELECT balance
INTO old_balance
FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the row
new_balance := old_balance + credit;
UPDATE accounts
SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'C' , new_balance);
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number' ;
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM , 1, 70);
END credit_account;
PROCEDURE debit_account(acct INT, debit REAL ) IS
/* Debit account unless account number is bad or
account has insufficient funds. */
old_balance REAL ;
new_balance REAL ;
insufficient_funds EXCEPTION ;
BEGIN
SELECT balance
INTO old_balance
FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the row
new_balance := old_balance - debit;
IF new_balance >= minimum_balance THEN
UPDATE accounts
SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'D' , new_balance);
ELSE
RAISE insufficient_funds;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number' ;
WHEN insufficient_funds THEN
new_status := 'Insufficient funds' ;
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM , 1, 70);
END debit_account;
END bank_transactions;
在这个包中，我们没有使用初始化部分。
六、包中私有项和公有项
再看一下包emp_actions。包体声明了一个名为number_hired的变量，它被初始化为零。与在包说明中声明的内容不同，包体中的内 容只局限于在当前包内使用。因此，在包外的PL/SQL代码就不能引用变量number_hired，这样的内容就是私有的。
但是，在emp_actions的说明中声明的内容，如异常invalid_salary，它对包外的程序来说就是可见的。因此，任何PL/SQL代码都可以访问它。这样的内容就是公有的。
当我们必须通过一个会话或跨事务来维护一些内容时，就需要把它们放在包体的声明部分。例如，number_hired的值在同一个会话中会被不断的更新。当会话结束时，这个值就会丢失。
如果我们必须把某些内容声明为公有的，就要把它们放在包的说明中。例如，声明在包bank_transactions中的常量minimum_balance就可以被广泛地使用。
七、重载包级子程序
PL/SQL允许两个或多个包级子程序拥有相同的名称。当我们想让一个子程序接受类型不同的相似的参数集合时，这个功能就很有效。例如，下面的包就定义了两个名为journalize过程：
CREATE PACKAGE journal_entries AS
...
PROCEDURE journalize(amount REAL , trans_date VARCHAR2 );
PROCEDURE journalize(amount REAL , trans_date INT);
END journal_entries;
CREATE PACKAGE BODY journal_entries AS
...
PROCEDURE journalize(amount REAL , trans_date VARCHAR2 ) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY' ));
END journalize;
PROCEDURE journalize(amount REAL , trans_date INT) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'J' ));
END journalize;
END journal_entries;
第一个过程接受一个字符串trans_date，而第二个过程接受一个数字类型trans_date。每个过程都对数据进行了适当的处理操作。
八、包STANDARD是如何定义PL/SQL环境的
STANDARD包定义了PL/SQL环境。这个包的说明部分定义了全局类型、异常和子程序，这些内容对PL/SQL程序来说都是可用的。例如，STANDARD包声明了能返回一个数字的绝对值的ABS函数：
FUNCTION ABS (n NUMBER ) RETURN NUMBER ;
包STANDARD的内容对应用程序是可见的。我们不需要在引用这些内容的前面加限定修饰词。例如，我们可以从数据库触发器、存储子程序、Oracle工具或3GL应用程序中直接调用ABS：
abs_diff := ABS(x - y);
如果我们在PL/SQL中重新声明了ABS，我们的本地声明就会覆盖掉全局声明。但是，我们仍然可以通过添加限定修饰词来引用内部函数ABS：
abs_diff := STANDARD.ABS(x - y);
大多数的内部函数都被重载了。例如，包STANDARD包含了下面几种声明：
FUNCTION TO_CHAR (right DATE ) RETURN VARCHAR2 ;
FUNCTION TO_CHAR (left NUMBER ) RETURN VARCHAR2 ;
FUNCTION TO_CHAR (left DATE , right VARCHAR2 ) RETURN VARCHAR2 ;
FUNCTION TO_CHAR (left NUMBER , right VARCHAR2 ) RETURN VARCHAR2 ;
PL/SQL会按照形参与实参的个数和类型来解析对函数TO_CHAR的调用。
九、系统包一览
Oracle和各种Oracle工具都提供了系统包来帮助我们建立基于PL/SQL的应用程序。例如，Oracle提供了许多工具包，下面介绍一下其中比较典型的包。
1、关于DBMS_ALERT包
DBMS_ALERT能让数据库触发器在特定的数据库值发生变化时向应用程序发送报警。报警是基于事务的并且是异步的(也就是它们的操作与定时机制无关)。例如，当新的股票和债券上市时公司就可以通过这个包更新来他的投资总额。
2、关于DBMS_OUTPUT包
包DBMS_OUTPUT能让我们显示来自PL/SQL块和子程序中的输出内容，这样就会很容易地进行测试和调试。过程put_line能把信息输 出到SGA的一个缓存中。我们可以通过调用过程get_line或在SQL*Plus中设置SERVEROUTPUT ON就能显示这些信息。假设我们创建了下面的存储过程：
CREATE PROCEDURE calc_payroll(payroll OUT NUMBER ) AS
CURSOR c1 IS
SELECT sal, comm
FROM emp;
BEGIN
payroll := 0;
FOR c1rec IN c1 LOOP
c1rec.comm := NVL(c1rec.comm, 0);
payroll := payroll + c1rec.sal + c1rec.comm;
END LOOP ;
/* Display debug info. */
DBMS_OUTPUT.put_line('Value of payroll: ' || TO_CHAR(payroll));
END ;
使用下面的命令时，SQL*Plus就能显示出payroll的值：
SQL > SET SERVEROUTPUT ON
SQL > VARIABLE num NUMBER
SQL > CALL calc_payroll(:num);
Value of payroll: 31225
3、关于DBMS_PIPE包
包DBMS_PIPE允许不同的会话通过命名管道来进行通信(管道就是一块内存区域，进程使用这个区域把消息传递给另外一个进程)。我们可以使用过 程pack_message和send_message把消息封装到一个管道，然后把消息发送到同一个实例中的另一个会话中。
管道的另一个终端，我们可以使用过程recieve_message和unpack_message来接受并打开要读取的消息。命名管道在很多地方都很有用。例如，我们可以用C语言编写一个收集信息的程序，然后把信息通过管道传递给存储过程。
4、关于UTL_FILE包
包UTL_FILE能让我们的PL/SQL程序读写操作系统(OS)文本文件。它提供了标准的OS流文件I/O，包括open、put、get和close操作。
当我们想要读写文件的时候，我们可以调用函数fopen，它能返回一个在后续过程调用中使用到的文件句柄。例如，过程put_line能往打开的文件中写入文本字符串，并在后边添加一个换行符，过程get_line能从打开的文件读取一行内容到放到一个输出缓存中。
5、关于UTL_HTTP包
包UTL_HTTP可以让我们PL/SQL程序使用超文本传输协议(HTTP)进行通信。它可以从互联网接收数据或调用Oracle Web服务器的cartridge。这个包有两个入口点，每一个都接受一个URL(统一资源定位器)字符串，然后连接到一个指定的网站并返回所请求的数 据，这些数据通常是超文本标记语言HTML格式。
十、包编写准则
在编写包时，尽量让它们保持通用性，这样就能在以后的程序中多次使用。避免编写那些与Oracle已经提供的特性相同的包。
包说明反映了我们的应用程序设计。所以，一定在包体之前定义它们。只有那些对包用户必须可见的内容才可以放在说明部分。这样，其他的开发人员就不会滥用包中的内容了。
为了减少因代码改变而引起的重编译，尽量不要在包说明部分放置过多的内容。对包体内容的改变不需要编译其他独立的过程，但是，如果包说明发生改变，Oracle就得重新编译每一个引用到那个包的存储子程序了。