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 声明变量的时候不用declare
D 定义函数或者存储过程的时候，没有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 VALID
PACKAGE 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)
---------------------
1
SQL> 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 call
SQL> 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 1
C 第二次调用
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 1
C 第二次执行
第二次测试时把 --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 Francisco
6 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 Francisco
8 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 avalege
function 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;
