Oracle PlSql包package.docx

1、Oracle PlSql包packageOracle PL/SQL包(package)一、 什么是PL/SQL包包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。包说明(简写为spec)是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体(Body)部分完全定义游标和子程序，并对说明中的内容加以实现。 如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个黑盒。我们可以调试、增强或替换一个包体而不同改变接口(包说明)。 我们可以从SQL*Plus中使用CREATE PACKAGE语句

2、来创建一个包。语法如下：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_declaratio

3、n . 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 . coll

4、ection_declaration . constant_declaration . exception_declaration . object_declaration . record_declaration . variable_declaration . cursor_body . function_spec . procedure_spec . call_spec .BEGIN sequence_of_statementsEND package_name;在包说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编

5、译指示必须跟在函数说明之后)声明之后才可以声明子程序。包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。 AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。 一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本(SQL counterpart)中来发布程序。 1、 PL/SQL包举例在下面的例子中，我们把一

6、个记录类型、游标和两个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

7、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, sa

8、l 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; 只有在包说明部分的声明内容对应用程序才是可见可

9、访问的；包体的详细实现是不可见不可访问的。所以，我们可以在不重新编译调用程序的前提下修改包体(实现)。 二、 PL/SQL包的优点包提供了几个优点：模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。 1、 模块化包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。 2、 轻松的程序设计设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。3、 信息隐

10、藏有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程 序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样， 对用户隐藏实现细节也能保证包的完整性。4、 附加功能打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。5、 良好的性能在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次

11、读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。 三、 理解包说明包说明包括了公有声明。这些声明的作用于对于数据库模式来说是本地的，对于包来说是全局的。所以，被声明的内容可以从应用程序中和包的任何地方访问。下图演示了包的作用范围： 说明中列出了包中对应用程序所有可用的资源。例如，下面的声明演示了一个接受INTEGER类型的参数并返回一个INTEGER结果的函数fac：FUNCTIONfac(nINTEGER)RETURNINTEGER;-returnsn!这些就是我们要调用的函数的所有信息。

12、我们并不需要考虑它的实现细节(如，是使用迭代还是递归)。只有子程序和游标有实现部分。所以，如果一个说明只有类型、常量、变量、异常的声明和调用说明，那么包体就没有必要的了。下面就是一个没有包体的包：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 ); minim

13、um_balance CONSTANT REAL := 10.00; number_processed INT; insufficient_funds EXCEPTION;END trans_data;包trans_data不需要包体，因为类型、常量、变量和异常并没有实现部分。这样的包能让我们定义全局变量，可供子程序和数据库触发器使用。1、 引用包的内容如果要引用包内声明的类型、常量、变量、异常和子程序等，就得使用点标识： package_name.type_namepackage_name.item_namepackage_name.subprogram_namepackage_name.c

14、all_spec_name 我们可以从数据库触发器、存储子程序、3GL(第三代程序语言)应用程序和各种Oracle工具中引用包中的内容。例如，我们可以从SQL*Plus中调用过程hire_employee： SQLCALLemp_actions.hire_employee(TATE,CLERK,.); 下例中，我们可以从一个嵌入到Pro*C的匿名PL/SQL块调用同样的过程。实参emp_name和job_title是主变量(即声明在主环境中的变量)。 EXECSQLEXECUTEBEGINemp_actions.hire_employee(:emp_name,:job_title,.); 引用

15、约束 我们不能直接或间接地引用远程打包变量。例如，我们不能远程调用下面的过程，因为它在参数初始化子句中引用了打包变量： CREATEPACKAGErandomASseedNUMBER;PROCEDUREinitialize(starterINNUMBER:=seed,.); 同样，我们也不能在包的内部引用主变量。 四、 理解包体包体是对包说明的实现。也就是说包体中包含了包说明中声明的每一个游标和子程序的实现。一定要记住，包体内实现的内容只有在包说明中声明之后才能在 包外引用。为了匹配包说明和包体，PL/SQL做了一个token-by-token的子程序头比较。所以，除了空白内容，头部内容必须完

16、全一致。否 则，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%TYP

17、E word for word BEGIN . END;END emp_actions;包体能包含私有声明，可以定义类型和其它所需的内容。这些声明的内容对于包体来说是本地的。因此，声明的内容在包体之外是无法访问的。与包说明部分不同的是，包体的声明部分可以包含子程序体。 在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。 包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。因此，包初始化部分只能在我们首次引用包的时候运行一次。 请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。但是，包体可以用于初始化包说明中声明的

18、内容。 五、 包特性的例子下面是一个名为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

19、, 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

20、, 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

21、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

22、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 em

23、pno = 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 = m

24、ax_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_sala

25、ry; 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,

26、 USER, emp_actions); number_hired := 0;END emp_actions;请记住，包初始化部分只是在我们首次引用包的时候执行一次。所以，在上面的例子中，只有一行数据被插入数据表emp_audit。同样，变量number_hired也只被初始化一次。 每次hire_employee被调用的时候，变量nubmer_hired就会被更新。但是，number_hired所记录的数字是与特定的会话相关的。也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。 在下面的例子中，我们把一些典型的银行事务进行打包。假设借款与贷款事务都是在营业

27、时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。 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_tr

28、ansactions;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. */ PROC

29、EDURE 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 S

30、ELECT 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 LO