一、什么是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的调用。
?