9. 程序包--PACKAGE
9.1 包的定义和编译包：一个PLSQL相关对象的逻辑分组和单个对象存储在数据库对象中的数据单元。相关的PLSQL对象包括：常量、变量、游标、异常、SP、FUN包由两部分组成： 规范部分(包头、调用接口) + 主体部分(包体、实现部分)(1) 包头的创建： create or replace package org_Master is max_sites_for_an_org number; type rc is ref cursor; procedure createOrg(x in number); --函数和存储过程的签名头，必须放在包头的规范部分 function rrr() return number; end org_Master;包的规范部分：包头，调用接口。只有在包头中声明的，才能够被外部程序所调用。注意： A 对于SP和FUN来说，包头中是有SP和FUN的签名头procedure createOrg(x in number);声明在包头部分，实现部分在包体。 B 常量、变量、游标、异常等复合数据类型，既可以在包头中出现，也可以出现包体中，在包头中声明叫做公共声明，在包体声明叫做私有声明，私有声明只能在包体中使用，外部无法调用。 C 仅在包体中定义的存储过程或者函数，而不在包头中声明，那么该存储过程或者函数是私有的，不能被外部调用(2) 包体的创建 create or replace package body org_Master is procedure createOrg(x in number) is begin end; function rrr() return number is begin end; end org_Master;案例： 创建包头：create or replace package org_Master is max_sites_for_an_org number; type rc is ref cursor; procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2); function getOrginfo(ip_org_id number) return rc; function getAllorginfo(ip_hrc_code number) return rc; procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2);end org_Master;创建包体：create or replace package body org_Master is procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin insert into org_tab values(ip_hrc_code,ip_org_id,ip_org_short_name,ip_org_long_name); commit; op_retcd:=0; op_err_msg:='successful!'; exception when dup_val_on_index then op_retcd:=-1; op_err_msg:='error with id '||to_char(ip_org_id)||' already exists!'; v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('dup_val_on_index'),v_sqlcode,v_sqlerrm,sysdate); commit; when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin update org_tab set org_short_name=ip_org_short_name,org_long_name=ip_org_long_name where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.updateOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin delete from org_tab where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.removeOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; function getOrginfo(ip_org_id number) return rc is r_rc rc; begin open r_rc for select * from org_tab where org_id=ip_org_id; return r_rc; exception when others then return null; end; function getAllorginfo(ip_hrc_code number) return rc is r_rc rc; begin open r_rc for select * from org_tab where hrc_code=ip_hrc_code; return r_rc; exception when others then return null; end; procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2) is v_num number; begin select count(1) into v_num from org_site_tab where org_id=ip_org_id and site_no=ip_site_no; if v_num=1 then op_retcd:=-1;create or replace package body org_Master is procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin insert into org_tab values(ip_hrc_code,ip_org_id,ip_org_short_name,ip_org_long_name); commit; op_retcd:=0; op_err_msg:='successful!'; exception when dup_val_on_index then op_retcd:=-1; op_err_msg:='error with id '||to_char(ip_org_id)||' already exists!'; v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('dup_val_on_index'),v_sqlcode,v_sqlerrm,sysdate); commit; when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin update org_tab set org_short_name=ip_org_short_name,org_long_name=ip_org_long_name where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.updateOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin delete from org_tab where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.removeOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; function getOrginfo(ip_org_id number) return rc is r_rc rc; begin open r_rc for select * from org_tab where org_id=ip_org_id; return r_rc; exception when others then return null; end; function getAllorginfo(ip_hrc_code number) return rc is r_rc rc; begin open r_rc for select * from org_tab where hrc_code=ip_hrc_code; return r_rc; exception when others then return null; end; procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2) is v_num number; begin select count(1) into v_num from org_site_tab where org_id=ip_org_id and site_no=ip_site_no; if v_num=1 then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' and '||to_char(ip_site_no)||' already exists!'; return; else insert into org_site_tab values(ip_org_id,ip_site_no); op_retcd:=0; op_err_msg:='successful!'; commit; end if; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id)||' '||to_char(ip_site_no),upper('org_Master.assignSiteOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; end org_Master; op_err_msg:='id '||to_char(ip_org_id)||' and '||to_char(ip_site_no)||' already exists!'; return; else insert into org_site_tab values(ip_org_id,ip_site_no); op_retcd:=0; op_err_msg:='successful!'; commit; end if; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id)||' '||to_char(ip_site_no),upper('org_Master.assignSiteOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; end org_Master;测试：A createOrg存储过程declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.createOrg(3,1007,'uuuu','asdfag',v_sqlcode,v_sqlerrm); dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;B updateOrg存储过程declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.updateOrg(1008,'aaaaa','dsdgsgsg',v_sqlcode,v_sqlerrm); dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;C removeOrg存储过程declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.removeOrg(1007,v_sqlcode,v_sqlerrm); dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;D getOrginfo函数的测试declare v_rc1 org_Master.rc; --包变量的引用 v_sqlcode number; v_sqlerrm varchar2(200); rec_org org_tab%rowtype;begin select org_Master.getOrginfo(1006) into v_rc1 from dual; loop fetch v_rc1 into rec_org; exit when(v_rc1%notfound); dbms_output.put_line(to_char(rec_org.org_id)||' '||rec_org.org_short_name||' '||rec_org.org_long_name); end loop; if v_rc1%isopen then close v_rc1; end if;end;E assignSiteOrg存储过程declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.assignSiteOrg(1006,2,v_sqlcode,v_sqlerrm); dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;总结：A 包头跟包体必须是相同的名字B 包的开始处是没有begin的C 声明变量的时候不用declareD 定义函数或者存储过程的时候，没有create or replace的字样E 公共声明在包头的任何位置出现都可以，但是必须在引用前出现create or replace package org_Master is max_sites_for_an_org number; procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2); function getOrginfo(ip_org_id number) return rc; function getAllorginfo(ip_hrc_code number) return rc; type rc is ref cursor; --会报错，编译不通过 procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2);end org_Master;(4)编译包的方法注意：编译的时候先编译包头，再编译包体，反之则不行。方法1：oracle内部包方法编译SQL> exec dbms_ddl.alter_compile('PACKAGE','PLSQL','ORG_MASTER'); --编译包头PL/SQL procedure successfully completed.SQL> call dbms_ddl.alter_compile('PACKAGE BODY','PLSQL','ORG_MASTER'); --编译包体Call completed.方法2：通过命令来编译SQL> alter package org_master compile; --编译包头Package altered.SQL> alter package org_master compile body; --编译包体Package body altered.方法3：通过第三方工具developer编译编译之后使用以下select语句查看编译的结果SQL> select object_type,status from user_objects where object_name='ORG_MASTER';OBJECT_TYPE STATUS------------------- -------PACKAGE VALIDPACKAGE BODY INVALID##########################################################################################9.2 包的引用
(1) 包变量的引用declare v_rc1 org_Master.rc; --包变量的引用，org_Master为已编译的包，rc为包头中已定义的游标变量 v_sqlcode number; v_sqlerrm varchar2(200); rec_org org_tab%rowtype;begin select org_Master.getOrginfo(1006) into v_rc1 from dual; loop fetch v_rc1 into rec_org; exit when(v_rc1%notfound); dbms_output.put_line(to_char(rec_org.org_id)||' '||rec_org.org_short_name||' '||rec_org.org_long_name); end loop; if v_rc1%isopen then close v_rc1; end if;end;(2) 包类型的引用declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.updateOrg(1008,'aaaaa','dsdgsgsg',v_sqlcode,v_sqlerrm); --引用包中的方法 dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;###########################################################################################9.3 私有对象
私有对象--包头中无声明，但是在包体中有定义的对象称为私有对象包头不变包体改变：create or replace package body org_Master is procedure removeOrgSite(ip_org_id in number, op_retcd out number, op_err_msg out varchar2 )is --私有对象 v_sqlcode number; v_sqlerrm varchar2(200); begin delete from org_tab where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.removeOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin insert into org_tab values(ip_hrc_code,ip_org_id,ip_org_short_name,ip_org_long_name); commit; op_retcd:=0; op_err_msg:='successful!'; exception when dup_val_on_index then op_retcd:=-1; op_err_msg:='error with id '||to_char(ip_org_id)||' already exists!'; v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('dup_val_on_index'),v_sqlcode,v_sqlerrm,sysdate); commit; when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.createOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin update org_tab set org_short_name=ip_org_short_name,org_long_name=ip_org_long_name where org_id=ip_org_id; if sql%notfound then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd:=0; op_err_msg:='successful!'; end if; commit; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.updateOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2) is v_sqlcode number; v_sqlerrm varchar2(200); begin removeOrgSite(ip_org_id,op_retcd,op_err_msg); --调用私有对象 if op_retcd <> 0 then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' does not exists!'; else op_retcd=0; op_err_msg:='successful!'; end if; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id),upper('org_Master.removeOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; function getOrginfo(ip_org_id number) return rc is r_rc rc; begin open r_rc for select * from org_tab where org_id=ip_org_id; return r_rc; exception when others then return null; end; function getAllorginfo(ip_hrc_code number) return rc is r_rc rc; begin open r_rc for select * from org_tab where hrc_code=ip_hrc_code; return r_rc; exception when others then return null; end; procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2) is v_num number; begin select count(1) into v_num from org_site_tab where org_id=ip_org_id and site_no=ip_site_no; if v_num=1 then op_retcd:=-1; op_err_msg:='id '||to_char(ip_org_id)||' and '||to_char(ip_site_no)||' already exists!'; return; else insert into org_site_tab values(ip_org_id,ip_site_no); op_retcd:=0; op_err_msg:='successful!'; commit; end if; exception when others then op_retcd:=sqlcode; op_err_msg:=sqlerrm; insert into exception_monitor values('ORG_TAB',to_char(ip_org_id)||' '||to_char(ip_site_no),upper('org_Master.assignSiteOrg'),upper('others'),op_retcd,op_err_msg,sysdate); commit; end; end org_Master;私有对象在外部是不能被调用的，只能调用包头中存在的declare v_sqlcode number; v_sqlerrm varchar2(200);begin org_Master.removeOrgSite(1007,v_sqlcode,v_sqlerrm); dbms_output.put_line(to_char(v_sqlcode)); dbms_output.put_line(v_sqlerrm);end;报错：PLS-00302：component 'REMOVEORGSITE' must be declared########################################################################################### 9.4 包的实例化和初始化 包的实例化--最早一次引用包变量或者第一次调用包的存储过程和函数的时候，包从磁盘加载到内存的共享池 包的初始化--将实际参数或者各道包体中定义声明赋值叫做初始化(1)定义变量的时候可以对变量进行初始化create or replace package org_Master is max_sites_for_an_org number:=2; procedure createOrg(ip_hrc_code in number, ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure updateOrg(ip_org_id in number, ip_org_short_name in varchar2, ip_org_long_name in varchar2, op_retcd out number, op_err_msg out varchar2); procedure removeOrg(ip_org_id in number, op_retcd out number, op_err_msg out varchar2); type rc is ref cursor; function getOrginfo(ip_org_id number) return rc; function getAllorginfo(ip_hrc_code number) return rc; procedure assignSiteOrg(ip_org_id number, ip_site_no number, op_retcd out number, op_err_msg out varchar2);end org_Master;(2) 在包体中对变量初始化 create or replace package body org_Master is xxxxxxxxxxxxxxxxxxxx begin max_sites_for_an_org:=2; --在包体中初始化，当调用包中任何一个结构的时候这个begin都要被执行，即max_sites_for_an_org都被赋为2 end org_Master;###########################################################################################9.6 命名块的重载
(1) 重载：一个包内有两个相同名字的命名块，但是形参列表不一样，调用的时候根据参数的形式来选择使用哪个命名块包头：create or replace package org_Master1 is function getAll(x number) return number; function getAll(x number,y number) return number;end org_Master1;包体：create or replace package body org_Master1 is function getAll(x number) return number is begin return 1; end; function getAll(x number,y number) return number is begin return 2; end;end org_Master1;SQL> select org_master1.getAll(3) from dual;ORG_MASTER1.GETALL(3)--------------------- 1SQL> select org_master1.getAll(3,4) from dual;ORG_MASTER1.GETALL(3,4)----------------------- 2有缺省值的情况create or replace package org_Master1 is function getAll(x number) return number; function getAll(x number,y number default 4) return number;end org_Master1;create or replace package body org_Master1 is function getAll(x number) return number is begin return 1; end; function getAll(x number,y number default 4) return number is begin return 2; end;end org_Master1;SQL> select org_master1.getAll(3) from dual;select org_master1.getAll(3) from dual *ERROR at line 1:ORA-06553: PLS-307: too many declarations of 'GETALL' match this callSQL> select org_master1.getAll(3,5) from dual;ORG_MASTER1.GETALL(3,5)----------------------- 2重载情况的包，尽量不要用缺省值，否则有容易出错########################################################################################### 9.7 包的连续可用性(1) 包的连续可重用 连续可重用的包-- 包的全局变量随着调用的结束而结束，不存值。一般都希望是连续可重用的包，不会影响程序的反复调用 非连续可重用的包 -- 包的全局变量不随着调用的结束而结束，存值。 pragma serially_reusable --执行该指令，包将是连续可重用的，也就是包的全局变量不存值。(2)案例 A 创建一个表 create table site_tab_1(site_no number,site_descr varchar2(200)); B 创建一个包create or replace package srpkg1 is pragma serially_reusable; num_var number; char_var varchar2(20); procedure initalize; function display_num return number; function display_char return varchar2;end srpkg1;create or replace package body srpkg1 is pragma serially_reusable; procedure initalize is begin num_var:=20; char_var:='String Test 1'; end; function display_num return number is begin return num_var; end; function display_char return varchar2 is begin return char_var; end;end srpkg1;测试：A 编写测试程序declare v_num number; v_char varchar2(20);begin srpkg1.initalize; v_num:=srpkg1.display_num; v_char:=srpkg1.display_char; insert into site_tab_1 values(v_num,v_char); commit;exception when others then null;end;B 执行后SQL> col site_descr format a30;SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 20 String Test 1C 第二次调用SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 20 String Test 1 null null 第二次调用没有初始化，返回值都是null，因为包是连续可重用的，前面的程序的值在变量中不存储，从而不影响后面的执行。(3) 将包改为非连续可重用create or replace package srpkg1 is --pragma serially_reuseable; --注释掉 num_var number; char_var varchar2(20); procedure initalize; function display_num return number; function display_char return varchar2;end srpkg1;create or replace package body srpkg1 is --pragma serially_reuseable; procedure initalize is begin num_var:=20; char_var:='String Test 1'; end; function display_num return number is begin return num_var; end; function display_char return varchar2 is begin return char_var; end;end srpkg1;测试：A 和上面一样的测试程序declare v_num number; v_char varchar2(20);begin srpkg1.initalize; v_num:=srpkg1.display_num; v_char:=srpkg1.display_char; insert into site_tab_1 values(v_num,v_char); commit;exception when others then null;end;B 第一次执行SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 20 String Test 1C 第二次执行 第二次测试时把 --srpkg1.initalize; 注释掉 ，所以第二次没有初始化SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 20 String Test 1 20 String Test 1 --上面第一次调用对变量的赋值会保存下来，下次调用还是有效的，包的非连续可重用案例：(1) 创建包和包体create or replace package srpkg2 is pragma serially_reusable; cursor csr_sites is select * from site_tab order by 1; procedure displaysites;end srpkg2;create or replace package body srpkg2 is pragma serially_reusable; procedure displaysites is site_rec site_tab%rowtype; begin if not csr_sites%isopen then open csr_sites; end if; fetch csr_sites into site_rec; insert into site_tab_1 values(site_rec.site_no,site_rec.site_descr); fetch csr_sites into site_rec; insert into site_tab_1 values(site_rec.site_no,site_rec.site_descr); commit; end;end srpkg2;(2)测试begin srpkg2.displaysites;end;SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington再调用一次SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington 1 New York --因为连续可重用，第二次执行会把游标的指针状态清除 2 Washington将包改为非连续可重用create or replace package srpkg2 is --pragma serially_reusable; cursor csr_sites is select * from site_tab order by 1; procedure displaysites;end srpkg2;create or replace package body srpkg2 is --pragma serially_reusable; procedure displaysites is site_rec site_tab%rowtype; begin if not csr_sites%isopen then open csr_sites; end if; fetch csr_sites into site_rec; insert into site_tab_1 values(site_rec.site_no,site_rec.site_descr); fetch csr_sites into site_rec; insert into site_tab_1 values(site_rec.site_no,site_rec.site_descr); commit; end;end srpkg2;测试：begin srpkg2.displaysites;end;SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington在调用一次：SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington 3 Chicago 4 Dallas --指针的状态不清除，所以指针在上一次的基础上继续往下再调用一次：SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington 3 Chicago 4 Dallas 5 San Francisco 5 San Francisco6 rows selected.SQL> select * from site_tab_1; SITE_NO SITE_DESCR---------- ------------------------------ 1 New York 2 Washington 3 Chicago 4 Dallas 5 San Francisco 5 San Francisco8 rows selected.指针的状态存储在包的游标变量中，第二次调用的时候会继续找到指针当前的位置。非连续可重用的包上面的包使用游标的时候，没有关闭，尝试关闭会怎样？###########################################################################################9.8 练习
在HR用户下开发一个包 A 封装功能：给emp表插入数据的功能，要注意和dept的参考关系和主键冲突问题 B 封装功能：给emp表修改数据，注意和dept的参考关系 C 删除功能：给emp表删除数据，注意和dept的参考关系 D 计算部门工资总和函数：传入部门号，返回总和 E 计算每个部门平均工资的函数：传入部门号，返回平均值 F 计算员工的年薪：sal*12+comm，传入员工号，返回年薪 G 计算员工的薪水的等级：传入员工号，返回工资等级 H 写一个函数，根据传入的部门号，返回销售的区域（返回国家） I 写一个SP，传入部门号，OUT参数返回数组，将部门对应的员工号存储在数组中返回 J 写一个SP，传入国家ID，OUT参数返回数组，将国家代码对应的location存储在数组中返回包头：create or replace package hr_master is--------------------cursor type csr is ref cursor;--------------------array type varrary is varray(50) of number; type rec is record(location_id number,city varchar2(50)); type lo_array is varray(50) of rec;-----------------------insert procedure in_emp( employee_id number, first_name varchar2, last_name varchar2, email varchar2, phone_number varchar2, hire_date date, job_id varchar2, salary number, commission_pct number, manager_id number, department_id number, emp_status out number, emp_err_msg out varchar2 );----------------------------update procedure update_emp( v_employee_id number, v_first_name varchar2, v_last_name varchar2, v_email varchar2, v_phone_number varchar2, v_hire_date date, v_job_id varchar2, v_salary number, v_commission_pct number, v_manager_id number, v_department_id number, emp_status out number, emp_err_msg out varchar2 ); ---------------------delete procedure update_emp( v_employee_id number, emp_status out number, emp_err_msg out varchar2 ); ----------------function avalege salary function salavg(deptno in number) return number; ----------------function count salary function sumsal(deptno in number) return number; ------------------function year salary function yearsal(v_employee_id in number) return number; ------------------sale country function country(deptno in number) return varchar2; ------------------employees id array function emp_id(deptno in number,id_array out varrary) return varrary; procedure employees_id( deptno in number, id_array out varrary, emp_status out number, emp_err_msg out varchar2 ); --------------------country_id procedure country_id( ctry_id in varchar2, id_array out lo_array, emp_status out number, emp_err_msg out varchar2 ); end hr_master;包体：create or replace package body hr_master is ------------------insert start procedure in_emp( employee_id number, first_name varchar2, last_name varchar2, email varchar2, phone_number varchar2, hire_date date, job_id varchar2, salary number, commission_pct number, manager_id number, department_id number, emp_status out number, emp_err_msg out varchar2 ) is forign_exception exception; pragma exception_init(forign_exception,-2291); begin insert into employees values(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id); if sql%found then emp_status:=0; emp_err_msg:=' insert ok'; else emp_status:=-1; emp_err_msg:='insert no'; end if; commit; exception when DUP_VAL_ON_INDEX then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',employee_id,'hr_master.in_emp','DUP_VAL_ON_INDEX',emp_status,emp_err_msg,sysdate); commit; when forign_exception then emp_status:=sqlcode; emp_err_msg:='foreign conflict'; insert into plsql.error_info values('EMPLOYEES',employee_id,'hr_master.in_emp','forign_exception',emp_status,emp_err_msg,sysdate); commit; when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',employee_id,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); commit; end; -------------------update start procedure update_emp( v_employee_id number, v_first_name varchar2, v_last_name varchar2, v_email varchar2, v_phone_number varchar2, v_hire_date date, v_job_id varchar2, v_salary number, v_commission_pct number, v_manager_id number, v_department_id number, emp_status out number, emp_err_msg out varchar2 ) is forign_exception exception; pragma exception_init(forign_exception,-2291); begin update employees set first_name=v_first_name, last_name=v_last_name, email=v_email, phone_number=v_phone_number, hire_date=v_hire_date, job_id=v_job_id, salary=v_salary, commission_pct=v_commission_pct, manager_id=v_manager_id, department_id=v_department_id where employee_id=v_employee_id; if sql%found then emp_status:=0; emp_err_msg:='update ok'; else emp_status:=-1; emp_err_msg:='update no'; end if; commit; exception when DUP_VAL_ON_INDEX then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','DUP_VAL_ON_INDEX',emp_status,emp_err_msg,sysdate); commit; when forign_exception then emp_status:=sqlcode; emp_err_msg:='foreign conflict'; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','forign_exception',emp_status,emp_err_msg,sysdate); commit; when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); commit; end; -----------------------------delete procedure update_emp( v_employee_id number, emp_status out number, emp_err_msg out varchar2 ) is begin delete from employees where employee_id=v_employee_id; if sql%found then emp_status:=0; emp_err_msg:='delete ok'; else emp_status:=-1; emp_err_msg:='delete no'; end if; commit; exception when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); commit; end; -------------------function avalegefunction salavg(deptno in number) return number is v_avg number; emp_status number; emp_err_msg varchar2(100); begin select avg(salary) into v_avg from employees where department_id=deptno; return v_avg; exception when NO_DATA_FOUND then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','no_date_found',emp_status,emp_err_msg,sysdate); when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end;-------------------------function sumsal function sumsal(deptno in number) return number is v_sumsal number; emp_status number; emp_err_msg varchar2(100); begin select count(salary) into v_sumsal from employees where department_id=deptno; return v_sumsal; exception when NO_DATA_FOUND then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','no_date_found',emp_status,emp_err_msg,sysdate); when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end; -------------------------function year salary function yearsal(v_employee_id in number) return number is v_yearsal number; emp_status number; emp_err_msg varchar2(100); begin select salary*12+nvl(commission_pct,0) into v_yearsal from employees where employee_id=v_employee_id; return v_yearsal; exception when NO_DATA_FOUND then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','no_date_found',emp_status,emp_err_msg,sysdate); when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',v_employee_id,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end;--------------------------sale country function country(deptno in number) return varchar2 is v_country varchar2(50); emp_status number; emp_err_msg varchar2(100); begin select c.country_name into v_country from countries c,departments d,locations l where c.country_id=l.country_id and l.location_id=d.location_id and d.department_id=deptno; return v_country; exception when NO_DATA_FOUND then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','no_date_found',emp_status,emp_err_msg,sysdate); when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end; ----------------------------employees id array function emp_id(deptno in number,id_array out varrary) return varrary is i integer:=1; begin id_array:=varrary(null); for idx in (select e.employee_id from employees e,departments d where e.department_id=d.department_id and d.department_id=deptno ) loop id_array(i):=idx.employee_id; id_array.extend; i:=i+1; end loop; return id_array; end; ------------------------------sp employees id array procedure employees_id( deptno in number, id_array out varrary, emp_status out number, emp_err_msg out varchar2 ) is i integer:=1; begin id_array:=varrary(null); for idx in (select e.employee_id from employees e,departments d where e.department_id=d.department_id and d.department_id=10 ) loop id_array(i):=idx.employee_id; id_array.extend; i:=i+1; end loop; exception when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',deptno,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end; ----------------------sp country_id procedure country_id( ctry_id in varchar2, id_array out lo_array, emp_status out number, emp_err_msg out varchar2 ) is i integer:=1; local_rec hr_master.rec; begin id_array:=lo_array(null); for idx in ( select l.location_id,l.city from countries c,locations l where l.country_id=c.country_id and c.country_id=ctry_id ) loop id_array(i).location_id:=idx.location_id; id_array(i).city:=idx.city; id_array.extend; i:=i+1; end loop; exception when others then emp_status:=sqlcode; emp_err_msg:=sqlerrm; insert into plsql.error_info values('EMPLOYEES',ctry_id,'hr_master.in_emp','others',emp_status,emp_err_msg,sysdate); end;end hr_master;