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;