Oracle PlSql包package.docx
《Oracle PlSql包package.docx》由会员分享，可在线阅读，更多相关《Oracle PlSql包package.docx（20页珍藏版）》请在冰豆网上搜索。
OraclePlSql包package
OraclePL/SQL包（package）
一、什么是PL/SQL包
包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。
包通常由两个部分组成：
包说明和包体，但有时包体是不需要的。
包说明（简写为spec）是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体（Body）部分完全定义游标和子程序，并对说明中的内容加以实现。
如下例所示，我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。
我们可以调试、增强或替换一个包体而不同改变接口（包说明）。
我们可以从SQL*Plus中使用CREATEPACKAGE语句来创建一个包。
语法如下：
CREATE[ORREPLACE]PACKAGEpackage_name
[AUTHID{CURRENT_USER|DEFINER}]
{IS|AS}
[PRAGMASERIALLY_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...]
[PRAGMARESTRICT_REFERENCES（assertions）...]
END[package_name];
[CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}
[PRAGMASERIALLY_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副本（SQLcounterpart）中来发布程序。
1、PL/SQL包举例
在下面的例子中，我们把一个记录类型、游标和两个employment过程进行打包。
要注意，过程hire_employee使用数据库序列empno_seq和函数SYSDATE分别插入到字段雇员编号和雇佣日期。
CREATEORREPLACEPACKAGEemp_actionsAS--spec
TYPEemprectypISRECORD（
emp_idINT,
salaryREAL
）;
CURSORdesc_salaryRETURNemprectyp;
PROCEDUREhire_employee（
enameVARCHAR2,
jobVARCHAR2,
mgrNUMBER,
salNUMBER,
commNUMBER,
deptnoNUMBER
）;
PROCEDUREfire_employee（emp_idNUMBER）;
ENDemp_actions;
CREATEORREPLACEPACKAGEBODYemp_actionsAS--body
CURSORdesc_salaryRETURNemprectypIS
SELECTempno,sal
FROMemp
ORDERBYsalDESC;
PROCEDUREhire_employee（
enameVARCHAR2,
jobVARCHAR2,
mgrNUMBER,
salNUMBER,
commNUMBER,
deptnoNUMBER
）IS
BEGIN
INSERTINTOemp
VALUES（empno_seq.NEXTVAL,
ename,
job,
mgr,
SYSDATE,
sal,
comm,
deptno）;
ENDhire_employee;
PROCEDUREfire_employee（emp_idNUMBER）IS
BEGIN
DELETEFROMemp
WHEREempno=emp_id;
ENDfire_employee;
ENDemp_actions;
只有在包说明部分的声明内容对应用程序才是可见可访问的；包体的详细实现是不可见不可访问的。
所以，我们可以在不重新编译调用程序的前提下修改包体（实现）。
二、PL/SQL包的优点
包提供了几个优点：
模块化、方便应用程序设计、信息隐藏、附加功能和良好的性能。
1、模块化
包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL模块中。
每一个包都容易理解，包与包之间接口简单、清晰。
这将有助于程序开发。
2、轻松的程序设计
设计应用程序时，我们首先要确定的是包说明中的接口信息。
我们可以在没有包体的条件下编写并编译说明部分。
然后引用该包的存储子程序也会被编译。
在完成整个应用程序之前，我们是不需要完全实现包体部分的。
3、信息隐藏
有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有（可见和可访问）或私有（隐藏和不可访问）。
例如，如果一个包里包含了四个子程序，其中三个是公有的一个是私有的。
包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身（不是我们的应用程序）。
同样，对用户隐藏实现细节也能保证包的完整性。
4、附加功能
打包公有变量和游标在一个会话期会一直存在。
所以，它们可以被当前环境下的所有子程序共享。
并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。
5、良好的性能
在我们首次调用打包子程序时，整个包就会被加载到内存中。
所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。
包能阻塞级联依赖，这样就能避免不必要的编译。
例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。
三、理解包说明
包说明包括了公有声明。
这些声明的作用于对于数据库模式来说是本地的，对于包来说是全局的。
所以，被声明的内容可以从应用程序中和包的任何地方访问。
下图演示了包的作用范围：
说明中列出了包中对应用程序所有可用的资源。
例如，下面的声明演示了一个接受INTEGER类型的参数并返回一个INTEGER结果的函数fac：
FUNCTION fac （n INTEGER） RETURN INTEGER; -- returns n!
这些就是我们要调用的函数的所有信息。
我们并不需要考虑它的实现细节（如，是使用迭代还是递归）。
只有子程序和游标有实现部分。
所以，如果一个说明只有类型、常量、变量、异常的声明和调用说明，那么包体就没有必要的了。
下面就是一个没有包体的包：
CREATEPACKAGEtrans_dataAS--bodilesspackage
TYPEtimerecISRECORD（
minutesSMALLINT,
hoursSMALLINT
）;
TYPEtransrecISRECORD（
CATEGORYVARCHAR2,
ACCOUNTINT,
amountREAL,
time_oftimerec
）;
minimum_balanceCONSTANTREAL:
=10.00;
number_processedINT;
insufficient_fundsEXCEPTION;
ENDtrans_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就会抛出异常，如下例所示：
CREATEPACKAGEemp_actionsAS
...
PROCEDUREcalc_bonus（date_hiredemp.hiredate%TYPE,...）;
ENDemp_actions;
CREATEPACKAGEBODYemp_actionsAS
...
PROCEDUREcalc_bonus（date_hiredDATE,...）IS
--parameterdeclarationraisesanexceptionbecause'DATE'
--doesnotmatch'emp.hiredate%TYPE'wordforword
BEGIN...END;
ENDemp_actions;
包体能包含私有声明，可以定义类型和其它所需的内容。
这些声明的内容对于包体来说是本地的。
因此，声明的内容在包体之外是无法访问的。
与包说明部分不同的是，包体的声明部分可以包含子程序体。
在包体的声明部分之后是一个可选的初始化部分，一般是用于初始化包中变量。
包初始化部分起到的作用并不大，与子程序不同，它不能被调用或是接受参数。
因此，包初始化部分只能在我们首次引用包的时候运行一次。
请记住，如果一个包说明只声明了类型、常量、变量、异常和调用说明，那么，包体部分就不是必需的了。
但是，包体可以用于初始化包说明中声明的内容。
五、包特性的例子
下面是一个名为emp_actions的包。
包说明声明了类型、游标、异常和子程序：
●类型EmpRecTyp和DeptRecTyp
●游标desc_salary
●异常invalid_salary
●函数hire_employee和raise_salary
●过程fire_empire和raise_salary
在编写包之后，我们就可以开发引用它声明的类型，调用它的子程序、游标和异常的应用程序。
创建包时，它就会被存放在Oracle数据库中供广泛地调用。
CREATEPACKAGEemp_actionsAS
/*Declareexternallyvisibletypes,cursor,exception.*/
TYPEemprectypISRECORD（
emp_idINT,
salaryREAL
）;
TYPEdeptrectypISRECORD（
dept_idINT,
LOCATIONVARCHAR2
）;
CURSORdesc_salaryRETURNemprectyp;
invalid_salaryEXCEPTION;
/*Declareexternallycallablesubprograms.*/
FUNCTIONhire_employee（
enameVARCHAR2,
jobVARCHAR2,
mgrREAL,
salREAL,
commREAL,
deptnoREAL
）
RETURNINT;
PROCEDUREfire_employee（emp_idINT）;
PROCEDUREraise_salary（emp_idINT,gradeINT,amountREAL）;
FUNCTIONnth_highest_salary（nINT）
RETURNemprectyp;
ENDemp_actions;
CREATEPACKAGEBODYemp_actionsAS
number_hiredINT;--visibleonlyinthispackage
/*Fullydefinecursorspecifiedinpackage.*/
CURSORdesc_salaryRETURNemprectypIS
SELECTempno,sal
FROMemp
ORDERBYsalDESC;
/*Fullydefinesubprogramsspecifiedinpackage.*/
FUNCTIONhire_employee（
enameVARCHAR2,
jobVARCHAR2,
mgrREAL,
salREAL,
commREAL,
deptnoREAL
）
RETURNINTIS
new_empnoINT;
BEGIN
SELECTempno_seq.NEXTVAL
INTOnew_empno
FROMDUAL;
INSERTINTOemp
VALUES（new_empno,ename,job,mgr,SYSDATE,sal,comm,deptno）;
number_hired:
=number_hired+1;
RETURNnew_empno;
ENDhire_employee;
PROCEDUREfire_employee（emp_idINT）IS
BEGIN
DELETEFROMemp
WHEREempno=emp_id;
ENDfire_employee;
/*Definelocalfunction,availableonlyinsidepackage.*/
FUNCTIONsal_ok（RANKINT,salaryREAL）
RETURNBOOLEANIS
min_salREAL;
max_salREAL;
BEGIN
SELECTlosal,hisal
INTOmin_sal,max_sal
FROMsalgrade
WHEREgrade=RANK;
RETURN（salary>=min_sal）AND（salary<=max_sal）;
ENDsal_ok;
PROCEDUREraise_salary（emp_idINT,gradeINT,amountREAL）IS
salaryREAL;
BEGIN
SELECTsal
INTOsalary
FROMemp
WHEREempno=emp_id;
IFsal_ok（grade,salary+amount）THEN
UPDATEemp
SETsal=sal+amount
WHEREempno=emp_id;
ELSE
RAISEinvalid_salary;
ENDIF;
ENDraise_salary;
FUNCTIONnth_highest_salary（nINT）
RETURNemprectypIS
emp_recemprectyp;
BEGIN
OPENdesc_salary;
FORiIN1..nLOOP
FETCHdesc_salary
INTOemp_rec;
ENDLOOP;
CLOSEdesc_salary;
RETURNemp_rec;
ENDnth_highest_salary;
BEGIN--initializationpartstartshere
INSERTINTOemp_audit
VALUES（SYSDATE,USER,'emp_actions'）;
number_hired:
=0;
ENDemp_actions;
请记住，包初始化部分只是在我们首次引用包的时候执行一次。
所以，在上面的例子中，只有一行数据被插入数据表emp_audit。
同样，变量number_hired也只被初始化一次。
每次hire_employee被调用的时候，变量nubmer_hired就会被更新。
但是，number_hired所记录的数字是与特定的会话相关的。
也就是说，计数的结果是与一个用户所处理的新雇员的个数，而不是所有会话处理过的雇员个数。
在下面的例子中，我们把一些典型的银行事务进行打包。
假设借款与贷款事务都是在营业时间之后通过自动出纳机处理，然后在第二天早上应用到账户中去。
CREATEPACKAGEbank_transactionsAS
/*Declareexternallyvisibleconstant.*/
minimum_balanceCONSTANTREAL:
=100.00;
/*Declareexternallycallableprocedures.*/
PROCEDUREapply_transactions;
PROCEDUREenter_transaction（acctINT,kindCHAR,amountREAL）;
ENDbank_transactions;
CREATEPACKAGEBODYbank_transactionsAS
/*Declareglobalvariabletoholdtransactionstatus.*/
new_statusVARCHAR2（70）:
='Unknown';
/*Useforwarddeclarationsbecauseapply_transactions
callscredit_accountanddebit_account,whicharenot
yetdeclaredwhenthecallsaremade.*/
PROCEDUREcredit_account（acctINT,creditREAL）;
PROCEDUREdebit_account（acctINT,debitREAL）;
/*Fullydefineproceduresspecifiedinpackage.*/
PROCEDUREapply_transactionsIS
/*Applypendingtransactionsintransactionstable
toaccountstable.Usecursortofetchrows.*/
CURSORtrans_cursorIS
SELECTacct_id,kind,amount
FROMtransactions
WHEREstatus='Pending'
ORDERBYtime_tag
FORUPDATEOFstatus;--tolockrows
BEGIN
FORtransINtrans_cursorLOOP
IFtrans.kind='D'THEN
debit_account（trans.acct_id,trans.amount）;
ELSIFtrans.kind='C'THEN
credit_account（trans.acct_id,trans.amount）;
ELSE
new_status:
='Rejected';
ENDIF;
UPDATEtransactions
SETstatus=new_status
WHERECURRENTOFtrans_cursor;
ENDLO
升级会员 