PL\SQL用户指南与参考9.1 转载
我们可以从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)中来发布程序。
说明中列出了包中对应用程序所有可用的资源。例如，下面的声明演示了一个接受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的包。包说明声明了类型、游标、异常和子程序：
在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。创建包时，它就会被存放在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的调用。
?
