Oracle存储过程中包的使用举例

傷城~ 2021-09-16 15:48 354阅读 0赞

一、创建测试表

--创建测试表
    create table pes(ID number(10),NAME varchar2(20),pwd varchar2(20),TYPE VARCHAR(20));
     
    insert into pes values(1,'xzw','888','Y');
    insert into pes values(1,'Eric','888','Y');
    insert into pes values(1,'lzq','555','Y');
    insert into pes values(1,'aaa','111','N');
    insert into pes values(1,'bbb','222','N');
     
    select * from pes;

测试表中的数据如下图所示：

![70][]

二、创建临时表

--创建事务级别的person_tmp临时表
    CREATE GLOBAL TEMPORARY TABLE person_tmp(
     ID NUMBER,
     NAME VARCHAR2(100),
     pwd VARCHAR2(100)
    )ON COMMIT DELETE ROWS;
    
    --创建会话级别的person临时表
    CREATE GLOBAL TEMPORARY TABLE person(
     ID NUMBER,
     NAME VARCHAR2(100),
     pwd VARCHAR2(100)
    )ON COMMIT PRESERVE ROWS;

临时表有事务级别的临时表和会话级别的临时表。事务级别的临时表使用ON COMMIT DELETE ROWS进行创建，而会话级别的临时表使用ON COMMIT PRESERVE ROWS进行创建。事务级别的临时表只对当前事务有效，而会话级别的临时表只对当前会话有效。

三、建包并创建包体

--建包并创建包体
    CREATE OR REPLACE PACKAGE person_package --建包
    AS
      PROCEDURE person_select(
         TYPE VARCHAR2 --类型
      );
    END person_package;
    
    CREATE OR REPLACE PACKAGE BODY person_package --创建包体
    AS
     PROCEDURE person_select(
       TYPE VARCHAR2
     )
     AS
       exe_sql_1 varchar2(4000);
       exe_sql_2 varchar2(4000);
     BEGIN
       exe_sql_1:='insert into person_tmp(ID,NAME,pwd) '||
                'select id,name,pwd from ( '||
                'pes where type = '''||type||''' ';
       
       exe_sql_2:='insert into person(ID,NAME,pwd) '||
                'select * from person_tmp ';
          
       execute immediate exe_sql_1;
       execute immediate exe_sql_2;
    
       exception
         when others then
           dbms_output.put_line(sqlerrm);
           
     end person_select;
    
    end person_package;

新建的包和包体分别在plsql左侧栏的Packages和Packages bodies中显示，如果包或包体在创建过程中出错的话，左侧栏会显示红色的叉号。

![70 1][]

这里我们的包以及包体均没有问题。

四、测试

--测试
    declare 
       TYPE VARCHAR2(20);
     begin 
       TYPE := 'Y';
       person_package.person_select(TYPE);
     end;

测试结果：

![70 2][]

![70 3][]

此时，如果点击提交按钮或者执行commit命令，我们得到如下的结果：

![70 4][]

![70 5][]

这就是上面所说的事务级别的临时表只对当前事务有效而会话级别的临时表只对当前会话有效。

[70]: /images/20210726/16ea704331ed42a38512f0334ea70a5a.png
[70 1]: /images/20210726/b418d2acedd045509ad4b5c12ffa8099.png
[70 2]: /images/20210726/b71a85d89f464e229a1b57e4fdf0c45f.png
[70 3]: /images/20210726/eefe6529042a45cebeb219186c6a651b.png
[70 4]: /images/20210726/f4fa2023b6b84660b4ae19ffac3350c1.png
[70 5]: /images/20210726/8903410bc60445c3aeaac881136f74cb.png