OraclePlSql包(package)

目录Oracle PL/SQL包(package)Oracle PL/SQL包(package)...............................................................................................................1一、什么是PL/SQL包.....................................................................................................11、PL/SQL包举例......................................................................................................2二、PL/SQL包的优点......................................................................................................4三、理解包说明..............................................................................................................4四、理解包体..................................................................................................................6五、包特性的例子..........................................................................................................7六、包中私有项和公有项.............................................................................................13七、重载包级子程序....................................................................................................13八、包STANDARD是如何定义PL/SQL环境的...........................................................14九、系统包一览............................................................................................................141、关于DBMS_ALERT包.........................................................................................142、关于DBMS_OUTPUT包.....................................................................................153、关于DBMS_PIPE包............................................................................................154、关于UTL_FILE包................................................................................................155、关于UTL_HTTP包..............................................................................................16十、包编写准则............................................................................................................16

一、什么是PL/SQL包package_name {IS | AS}我们可以从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];包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。包说明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体(Body)部分完全定义游标和子程序，并对说明中的内容加以实现。

如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。我们可以调试、增强或替换一个包体而不同改变接口(包说明)。

[PRAGMA SERIALLY_REUSABLE;][CREATE [OR REPLACE] PACKAGE BODY

[BEGIN [call_spec ...] [cursor_body ...]在包说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。

AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。

一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本(SQL counterpart)中来发布程序。

job VARCHAR2,END [package_name];] [function_spec ...] [procedure_spec ...] sequence_of_statements] [record_declaration ...] [object_declaration ...] [subtype_definition ...] [variable_declaration ...] [constant_declaration ...] [exception_declaration ...] [collection_declaration ...] [record_type_definition ...] ename VARCHAR2,1、PL/SQL包举例 PROCEDURE hire_employee( [collection_type_definition ...] CURSOR desc_salary RETURN emprectyp;在下面的例子中，我们把一个记录类型、游标和两个employment过程进行打包。要注意，过程hire_employee使用数据库序列empno_seq和函数SYSDATE分别插入到字段雇员编号和雇佣日期。

CREATE OR REPLACE PACKAGE emp_actions AS -- spec TYPE emprectyp IS RECORD( emp_id INT, salary REAL );

); ) IS BEGIN BEGIN FROM empEND emp_actions;END emp_actions;

deptno NUMBER deptno NUMBER sal, mgr, job, sal NUMBER, mgr NUMBER, sal NUMBER, mgr NUMBER, DELETE FROM emp INSERT INTO emp comm NUMBER, comm NUMBER, comm, END fire_employee; END hire_employee; ename, job VARCHAR2, ename VARCHAR2, ORDER BY sal DESC; SELECT empno, sal deptno); SYSDATE, PROCEDURE hire_employee( WHERE empno = emp_id; VALUES (empno_L, CURSOR desc_salary RETURN emprectyp IS PROCEDURE fire_employee(emp_id NUMBER); PROCEDURE fire_employee(emp_id NUMBER) ISCREATE OR REPLACE PACKAGE BODY emp_actions AS -- body只有在包说明部分的声明内容对应用程序才是可见可访问的；包体的详细实现是不可见不可访问的。所以，我们可以在不重新编译调用程序的前提下修改包体(实现)。

三、理解包说明包提供了几个优点：模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。二、PL/SQL包的优点包说明包括了公有声明。这些声明的作用于对于数据库模式来说是本地的，对于包来说是全局的。所以，被声明的内容可以从应用程序中和包的任何地方访问。下图演示了包的作用范围：

1、模块化包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。

2、轻松的程序设计设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。3、信息隐藏有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程 序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样， 对用户隐藏实现细节也能保证包的完整性。4、附加功能打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。5、良好的性能在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。

);END trans_data; ACCOUNT INT, amount REAL,package__name time_of timerec CATEGORY VARCHAR2, TYPE transrec IS RECORD( number_processed INT; insufficient_funds EXCEPTION; minimum_balance CONSTANT REAL := 10.00;说明中列出了包中对应用程序所有可用的资源。例如，下面的声明演示了一个接受INTEGER类型的参数并返回一个INTEGER结果的函数fac：FUNCTION fac (n INTEGER) RETURN INTEGER; -- returns n!包trans_data不需要包体，因为类型、常量、变量和异常并没有实现部分。这样的包能让我们定义全局变量，可供子程序和数据库触发器使用。1、引用包的内容如果要引用包内声明的类型、常量、变量、异常和子程序等，就得使用点标识：

这些就是我们要调用的函数的所有信息。我们并不需要考虑它的实现细节(如，是使用迭代还是递归)。只有子程序和游标有实现部分。所以，如果一个说明只有类型、常量、变量、异常的声明和调用说明，那么包体就没有必要的了。下面就是一个没有包体的包：CREATE PACKAGE trans_data AS -- bodiless package TYPE timerec IS RECORD( minutes SMALLINT, hours SMALLINT );

... BEGIN ... END;CREATE PACKAGE random AS seed NUMBER; PROCEDURE initialize (starter IN NUMBER := seed, ...);

下例中，我们可以从一个嵌入到Pro*C的匿名PL/SQL块调用同样的过程。实参emp_name和job_title是主变量(即声明在主环境中的变量)。

END emp_actions;四、理解包体package__namepackage_gram_namepackage__spec_name

同样，我们也不能在包的内部引用主变量。

CREATE PACKAGE BODY emp_actions ASEXEC SQL EXECUTEBEGIN emp__employee(:emp_name, :job_title, ...);

SQL> CALL emp__employee('TATE', 'CLERK', ...);

PROCEDURE calc_bonus(date_hired DATE, ...) IS -- does not match 'te%TYPE' word for word -- parameter declaration raises an exception because 'DATE'引用约束

我们不能直接或间接地引用远程打包变量。例如，我们不能远程调用下面的过程，因为它在参数初始化子句中引用了打包变量：

我们可以从数据库触发器、存储子程序、3GL(第三代程序语言)应用程序和各种Oracle工具中引用包中的内容。例如，我们可以从SQL*Plus中调用过程hire_employee：

包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在 包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完全一致。否 则，PL/SQL就会抛出异常，如下例所示：CREATE PACKAGE emp_actions AS ... PROCEDURE calc_bonus(date_hired te%TYPE, ...);END emp_actions;包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。

在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。

包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。

) ); RETURN INT; deptno REAL sal REAL, mgr REAL, comm REAL, dept_id INT, job VARCHAR2, ename VARCHAR2, LOCATION VARCHAR2五、包特性的例子 FUNCTION hire_employee( TYPE deptrectyp IS RECORD( invalid_salary EXCEPTION; CURSOR desc_salary RETURN emprectyp; PROCEDURE fire_employee(emp_id INT); /* Declare externally callable subprograms. */ PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL);下面是一个名为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 );请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的内容。

) BEGIN BEGINdeptno); FROM emp FROM DUAL; deptno REALEND emp_actions; sal REAL, mgr REAL, RETURN INT IS comm REAL, DELETE FROM emp INSERT INTO emp new_empno INT; INTO new_empno END fire_employee; END hire_employee; job VARCHAR2, RETURN new_empno; RETURN emprectyp; ename VARCHAR2, ORDER BY sal DESC; SELECT empno, sal FUNCTION hire_employee( SELECT empno_L WHERE empno = emp_id;CREATE PACKAGE BODY emp_actions AS FUNCTION nth_highest_salary(n INT) number_hired := number_hired + 1; PROCEDURE fire_employee(emp_id INT) IS CURSOR desc_salary RETURN emprectyp IS /* Fully define cursor specified in package. */ number_hired INT; -- visible only in this package /* Fully define subprograms specified in package. */ /* Define local function, available only inside package. */ VALUES (new_empno, ename, job, mgr, SYSDATE, sal, comm,

BEGIN BEGIN BEGIN ELSE END IF; END LOOP; END sal_ok; FROM emp SELECT sal UPDATE emp INTO salary salary REAL; max_sal REAL; min_sal REAL; FROM salgrade RETURN emp_rec; END raise_salary; INTO emp_rec; OPEN desc_salary; RETURN BOOLEAN IS CLOSE desc_salary; FETCH desc_salary SELECT losal, hisal RETURN emprectyp IS emp_rec emprectyp; WHERE grade = RANK; FOR i IN 1 .. n LOOP WHERE empno = emp_id; RAISE invalid_salary; INTO min_sal, max_sal WHERE empno = emp_id; SET sal = sal + amount FUNCTION nth_highest_salary(n INT) FUNCTION sal_ok(RANK INT, salary REAL) IF sal_ok(grade, salary + amount) THEN RETURN (salary >= min_sal) AND(salary <= max_sal); PROCEDURE raise_salary(emp_id INT, grade INT, amount REAL) IS

END emp_actions;END bank_transactions; number_hired := 0; INSERT INTO emp_audit END nth_highest_salary; CURSOR trans_cursor IS FROM transactions PROCEDURE apply_transactions; PROCEDURE apply_transactions IS请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表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; WHERE status = 'Pending' SELECT acct_id, kind, amount new_status VARCHAR2(70) := 'Unknown';BEGIN -- initialization part starts here yet declared when the calls are made. */ VALUES (SYSDATE, USER, 'emp_actions'); /* Declare externally callable procedures. */ PROCEDURE debit_account(acct INT, debit REAL); PROCEDURE credit_account(acct INT, credit REAL); to accounts table. Use cursor to fetch rows. */ /* Fully define procedures specified in package. */ /* Apply pending transactions in transactions table calls credit_account and debit_account, which are not /* Use forward declarations because apply_transactionsCREATE PACKAGE BODY

bank_transactions AS /* Declare global variable to hold transaction status. */ PROCEDURE enter_transaction(acct INT, kind CHAR, amount REAL);

*/ BEGIN BEGIN BEGIN ELSE ELSE END IF; END IF; END LOOP; IF kind = 'D' THEN INSERT INTO journal END do_journal_entry; END enter_transaction; UPDATE transactions END apply_transactions; ORDER BY time_tag INSERT INTO transactions PROCEDURE do_journal_entry( IF = 'D' THEN PROCEDURE enter_transaction( SET status = new_status ELSIF = 'C' THEN FOR trans IN trans_cursor LOOP new_status := 'Rejected'; WHERE CURRENT OF trans_cursor; new_status := 'Debit applied'; new_status := 'Credit applied'; FOR UPDATE OF status; -- to lock rows VALUES (acct, kind, new_bal, SYSDATE); debit_account(_id, ); credit_account(_id, ); PROCEDURE credit_account(acct INT, credit REAL) IS /* Credit account unless account number is bad. */ VALUES (acct, kind, amount, 'Pending', SYSDATE); /* Define local procedures, available only in package. */ /* Record transaction in journal. */ acct INT, kind CHAR, amount REAL) IS acct INT, kind CHAR, new_bal REAL) IS /* Add a transaction to transactions table.

BEGIN BEGIN EXCEPTION UPDATE accounts WHEN OTHERS THEN UPDATE accounts END credit_account; new_balance REAL; old_balance REAL; SELECT balance SELECT balance FROM accounts FROM accounts WHERE acct_id = acct; WHEN NO_DATA_FOUND THEN INTO old_balance INTO old_balance WHERE acct_id = acct; new_balance REAL; old_balance REAL; WHERE acct_id = acct WHERE acct_id = acct SET balance = new_balance SET balance = new_balance insufficient_funds EXCEPTION; account has insufficient funds. */ new_balance := old_balance - debit; IF new_balance >= minimum_balance THEN new_balance := old_balance + credit; new_status := 'Bad account number'; do_journal_entry(acct, 'C', new_balance); new_status := SUBSTR(SQLERRM, 1, 70); FOR UPDATE OF balance; -- to lock the row FOR UPDATE OF balance; -- to lock the row do_journal_entry(acct, 'D', new_balance); PROCEDURE debit_account(acct INT, debit REAL) IS /* Debit account unless account number is bad or

...再看一下包emp_actions。包体声明了一个名为number_hired的变量，它被初始化为零。与在包说明中声明的内容不同，包体中的内 容只局限于在当前包内使用。因此，在包外的PL/SQL代码就不能引用变量number_hired，这样的内容就是私有的。但是，在emp_actions的说明中声明的内容，如异常invalid_salary，它对包外的程序来说就是可见的。因此，任何PL/SQL代码都可以访问它。这样的内容就是公有的。

当我们必须通过一个会话或跨事务来维护一些内容时，就需要把它们放在包体的声明部分。例如，number_hired的值在同一个会话中会被不断的更新。当会话结束时，这个值就会丢失。

如果我们必须把某些内容声明为公有的，就要把它们放在包的说明中。例如，声明在包bank_transactions中的常量minimum_balance就可以被广泛地使用。

BEGIN ELSE END IF; EXCEPTION WHEN OTHERS THEN END debit_account;END journal_entries;END bank_transactions;七、重载包级子程序在这个包中，我们没有使用初始化部分。

WHEN NO_DATA_FOUND THEN六、包中私有项和公有项 RAISE insufficient_funds; WHEN insufficient_funds THENCREATE PACKAGE BODY journal_entries AS new_status := 'Bad account number'; new_status := 'Insufficient funds'; new_status := SUBSTR(SQLERRM, 1, 70); PROCEDURE journalize(amount REAL, trans_date INT); PROCEDURE journalize(amount REAL, trans_date VARCHAR2) ISPL/SQL允许两个或多个包级子程序拥有相同的名称。当我们想让一个子程序接受类型不同的相似的参数集合时，这个功能就很有效。例如，下面的包就定义了两个名为journalize过程：CREATE PACKAGE journal_entries AS ... PROCEDURE journalize(amount REAL, trans_date VARCHAR2);

BEGIN END journalize; END journalize;九、系统包一览END journal_entries; INSERT INTO journal INSERT INTO journal1、关于DBMS_ALERT包PL/SQL会按照形参与实参的个数和类型来解析对函数TO_CHAR的调用。 VALUES (amount, TO_DATE(trans_date, 'J')); PROCEDURE journalize(amount REAL, trans_date INT) IS八、包STANDARD是如何定义PL/SQL环境的 VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));大多数的内部函数都被重载了。例如，包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;Oracle和各种Oracle工具都提供了系统包来帮助我们建立基于PL/SQL的应用程序。例如，Oracle提供了许多工具包，下面介绍一下其中比较典型的包。

DBMS_ALERT能让数据库触发器在特定的数据库值发生变化时向应用程序发送报警。报警是基于事务的并且是异步的(也就是它们的操作与定时机制无关)。例如，当新的股票包STANDARD的内容对应用程序是可见的。我们不需要在引用这些内容的前面加限定修饰词。例如，我们可以从数据库触发器、存储子程序、Oracle工具或3GL应用程序中直接调用ABS：abs_diff := ABS(x - y);STANDARD包定义了PL/SQL环境。这个包的说明部分定义了全局类型、异常和子程序，这些内容对PL/SQL程序来说都是可用的。例如，STANDARD包声明了能返回一个数字的绝对值的ABS函数：FUNCTION ABS (n NUMBER) RETURN NUMBER;如果我们在PL/SQL中重新声明了ABS，我们的本地声明就会覆盖掉全局声明。但是，我们仍然可以通过添加限定修饰词来引用内部函数ABS：abs_diff := (x - y);第一个过程接受一个字符串trans_date，而第二个过程接受一个数字类型trans_date。每个过程都对数据进行了适当的处理操作。

END; END LOOP;;SQL> SET SERVEROUTPUT ONSQL> VARIABLE num NUMBERSQL> CALL calc_payroll(:num);Value of payroll: 31225

FOR c1rec IN c1 LOOP /* Display debug info. */ := NVL(, 0); payroll := payroll + +

DBMS__line('Value of

payroll: ' || TO_CHAR(payroll));3、关于DBMS_PIPE包2、关于DBMS_OUTPUT包使用下面的命令时，SQL*Plus就能显示出payroll的值：

和债券上市时公司就可以通过这个包更新来他的投资总额。

包DBMS_PIPE允许不同的会话通过命名管道来进行通信(管道就是一块内存区域，进程使用这个区域把消息传递给另外一个进程)。我们可以使用过 程pack_message和send_message把消息封装到一个管道，然后把消息发送到同一个实例中的另一个会话中。

管道的另一个终端，我们可以使用过程recieve_message和unpack_message来接受并打开要读取的消息。命名管道在很多地方都很有用。例如，我们可以用C语言编写一个收集信息的程序，然后把信息通过管道传递给存储过程。包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;

十、包编写准则4、关于UTL_FILE包5、关于UTL_HTTP包包UTL_FILE能让我们的PL/SQL程序读写操作系统(OS)文本文件。它提供了标准的OS流文件I/O，包括open、put、get和close操作。

当我们想要读写文件的时候，我们可以调用函数fopen，它能返回一个在后续过程调用中使用到的文件句柄。例如，过程put_line能往打开的文件中写入文本字符串，并在后边添加一个换行符，过程get_line能从打开的文件读取一行内容到放到一个输出缓存中。

包UTL_HTTP可以让我们PL/SQL程序使用超文本传输协议(HTTP)进行通信。它可以从互联网接收数据或调用Oracle Web服务器的cartridge。这个包有两个入口点，每一个都接受一个URL(统一资源定位器)字符串，然后连接到一个指定的网站并返回所请求的数 据，这些数据通常是超文本标记语言HTML格式。

在编写包时，尽量让它们保持通用性，这样就能在以后的程序中多次使用。避免编写那些与Oracle已经提供的特性相同的包。

包说明反映了我们的应用程序设计。所以，一定在包体之前定义它们。只有那些对包用户必须可见的内容才可以放在说明部分。这样，其他的开发人员就不会滥用包中的内容了。为了减少因代码改变而引起的重编译，尽量不要在包说明部分放置过多的内容。对包体内容的改变不需要编译其他独立的过程，但是，如果包说明发生改变，Oracle就得重新编译每一个引用到那个包的存储子程序了。