Oracle存储过程汇总

短命女 2022-05-19 11:56 219阅读 0赞

开场来一发本次举例的脚本：

--创建mq表
    create table t_commodity_frozen_test(id int primary key,commodity_id int,province varchar2(20),create_time date,status integer default 0);
    ---------------------------------------------------------------------------------------
    /*--测试数据
    select * from t_commodity_frozen_test;
    delete from t_commodity_frozen_test where id =2;
    insert into t_commodity_frozen_test values(1,123211,'anhui',sysdate,0);
    drop table t_temp_frozen
    */
    ---------------------------------------------------------------------------------------
    --创建包头
    create or replace package commodity_frozen_test is
    procedure tempfrozen;
    procedure commodityfrozen
    end commodity_frozen_test;
    
    -----------------------------------------------------------------------------------------
    --procedure1 创建临时表
    
    create or replace package body commodity_frozen_test is
    procedure tempfrozen is
    begin
      execute immediate
      'create global temporary
      table t_temp_frozen(ext1 number(10),ext2 varchar2(100))
    on commit preserve rows';
    end tempfrozen;
    
    ------------------------------------------------------------------------------------------
    --procedure2 执行体
    procedure commodityfrozen is
    V_commoditid t_commodity_info.commodity_id%type;
    V_provinceid t_config.configvalue%type;
    V_exchangecodeid t_commodity_info.exchangescopeid%type;
    V_merchantid t_merchant_expressscope.merchantid%type;
    V_provincename t_config.configname%type;
    
    --定义游标范围
    cursor  C_frozen is
    select t.commodity_id,t.province from t_commodity_frozen_test t
    where t.status=0
    and t.create_time > trunc(sysdate-1);
    
    -- 定义商品范围
      begin
      open C_frozen;
      loop fetch  C_frozen into V_commoditid,V_provincename;
        exit when  C_frozen%notfound;
    -- 获取要冻结省份value
        select g.configvalue
          into V_provinceid
          from t_config g
         where g.configname = V_provincename and g.configtype=4;
    
    -- 判断商品还是商户
    select fo.exchangescopeid
      into V_exchangecodeid
      from t_commodity_info fo
     where fo.commodity_id = V_commoditid;
    
    if V_exchangecodeid is null
      then
    
        select me.merchantid
          into V_merchantid
          from t_merchant_expressscope me
          join t_commodity_info fo
            on me.merchantid = fo.business_id
         where fo.commodity_id = V_commoditid;
    -- 判断商户是单省商品还是多省商品（单省直接删掉商户区域配置）
    if V_merchantid is null then
         delete from (select me.provincecode
                        from t_merchant_expressscope me
                        join t_merchant_info mi
                          on me.merchantname = mi.merchant_loginname
                        join t_commodity_info fo
                          on mi.id = fo.business_id
                       where fo.commodity_id = V_commoditid)
          where provincecode = V_provinceid;
    else
    --基于商户配置修改
    insert into t_temp_frozen(ext1, ext2)
      select fo.commodity_id, me.provincecode
        from t_merchant_expressscope me
        join t_commodity_info fo
          on me.merchantid = fo.business_id
       where fo.commodity_id = V_commoditid;
    end if;
    --基于商品配置修改
    else
      insert into t_temp_frozen
        (ext1, ext2)
        select ce.commodityid, ce.provincecode
          from t_commodity_exchangescope ce
         where ce.commodityid = V_commoditid;
    
     end if;
    -- 删除冻结省份保留其他正常省份
    delete from t_temp_frozen t
     where ext1 = V_commoditid
       and ext2 = V_provinceid;
    
    --清除原始信息（商户类型delete 0行）
    
    delete from t_commodity_exchangescope ce
     where ce.commodityid = V_commoditid;
    
    --插入新值
    insert into t_commodity_exchangescope
      select seq_commodityexchangescope.nextval, t.ext1, t.ext2, null, sysdate
        from t_temp_frozen t
       where t.ext1 = V_commoditid;
    
    --更新时间
    insert into t_commodityinfo_log
      (id, commodity_id, action_id, remark, oper_id, oper_time)
      select seq_commodityinfolog.nextval,
             s.commodity_id,
             4,
             '限制用户兑换虚拟礼品',
             'DBA',
             sysdate
        from t_commodity_info s
       where s.commodity_id = V_commoditid;
    
    --反馈处理结果并更新状态
    update t_commodity_frozen_test
       set status = '1'
     where commodity_id = V_commoditid;
    
    
    end loop;
    close C_frozen;
    
    --异常
    exception
      when others then 
        dbms_output.put_line('程序异常执行');
    
    
    end commodityfrozen;
    end commodity_frozen_test;
    
    
    ---------------------------------------------------------------------------
    --创建触发器
    create or replace trigger commodity_frozentest
    after insert or update on t_commodity_frozen_test
      begin
        commodity_frozen_test.commodityfrozen;
      end;

以上sql主要是解决我的一个按省份和商品号冻结该区域的商品的需求，分析需求主要做两件事：

1、找到商品省份

2、找到该商品并更新其省份信息进行冻结

由于商品表上的省份信息是通过另外一张表中的数据触发更新的，所以我不能直接更新商品表上的省份信息，必须要去找到商品省份触发的表，并且更新其内容，触发表是多年前的历史表了，每次变更信息必须delete所有的数据，再插入需要的数据，所以sql写的很复杂。

sql的注释已经能看出我的解决方法，

1、先建立一个mq表来接收外部传出的需要更新的商品和省份数据，并且给他定义一个默认状态和时间；

2、建立一个触发器，设定为一旦有数据insert到临时表，并且判断下数据状态和插入时间后，调用存储过程；

3、当满足触发器条件后，触发器会调用存储过程，此时存储过程会进行相应处理并且变更相关信息来达到目的。

先讨论这个存储过程：

之前我们讨论过存储过程基本体是procedure begin  end；但是存储过程不能执行create table静态的操作（这里引入静态sql和动态sql概念），有时候我们不得不采用动态sql，即使采用动态sql若是要创建临时表，我们的create操作和update操作是不能在一个存储过程里面的，所以我们要在一件事情完整处理的情况下用上多个存储过程，这时候我们就要用到包（package的概念），在pl/sql Oracle中的表现为左边文件栏的package和package body，如下图：

![70][]

sql上表现为：package 包体：

--创建包头
    create or replace package commodity_frozen_test is
    procedure tempfrozen;
    procedure commodityfrozen
    end commodity_frozen_test;

和package bodies：

后面那串body代码：

![70 1][]

我们右键查看body：

![70 2][]

得出结论，body里面写了几个存储过程，都会在文件夹declaration显示，所以我们能快速的判断这个body有多少存储过程。

package的格式：

create or replace package 包头名
    
    procedur1（包体参数）;--包体1
    procedur2（包体参数）;--包体2
    procedur3（包体参数）;--包体3
    procedur4（包体参数）;--包体4
    
    
    end；
    
    ------------------------------------------------------------------------------------
    --创建包体
    create or replace package body 包头名
    
    procedure 1 as
    begin 
    
    *****
    end；--存储过程1
    
    procedure 2 as
    begin 
    
    *****
    end；--存储过程2
    
    procedure 3 as
    begin 
    
    *****
    end；--存储过程3
    
    
    procedure 4 as
    begin 
    
    *****
    end；--存储过程4
    
    
    end；--包体结束

这里必须要强调，生成包和包体要对应的必须条件是：包体名必须和包头名一样；

用包的主要目的是，当你的一个完整的需求，一个存储过程解决不了的时候，要考虑用多个存储过程或者函数去解决，这时候就可以考虑用package；package中procedure和function是可以混用的。

概括下来就是：

package 的目的是让多个存储过程或者存储函数合并在一起，组成一个包的形式，让使用这只需要调用一个包就可以达到使用者的目的。

如何调用执行一个包？

在sql编辑页面处：

begin 
    
    dbms_output.put_line('hello world');
    
    end；

上面这个执行语句是我们常用的输出执行语句，这里的dbms\_output就是包头，后面接的put\_line就是包体中的存储函数/过程；

所以我们执行包的方法是

方法一：sql窗口：begin   包头名.包体名； end；

方法二：命令窗口：exec 包头名.包体名；

现在我们package基本机构讲完了，接下来讲讲内容：

本次，将引入动态sql  临时表  游标  参数等等几个概念。

我们创建了包和包体，为什么我要复杂的去创建包呢？

这里我的需求是要用一张临时表来存我计算的数据，因为我在拿到外部给的1个商品的时候，搜索出来可能是多个省份的值，属于我计算的时候就有1对多的原始数据，我要存储这些原始数据所以需要创建一个存储过程用的临时表（即计算结束表就释放掉不占用系统资源）；那么这里引入一个概念：临时表的创建：

对于这种只在当前窗口使用，使用完就自动释放删除数据，不保存数据于系统的临时表我们有三种：

会话临时表

事务临时表

dual

dual临时表我们常用，这里就不做说明，

临时表，就是我要临时存储数据，当我处理完后，数据就不需要的表，会话级临时表和事务级临时表在sql上差异不大；

create global temporary table A(字段信息)
    on commit preserve rows--当事务提交时保存数据（会话级） 
    on commit delete rows --当事务提交时 删除数据（事务级）

以上就是创建临时表的方法，区别在于commit形式，会话级只能在当前窗口查询，新开个sql窗口编辑查询语句是查不到A表数据的，而事务级在未删除数据的情况下，其他窗口能查询到数据；本次我只需当前窗口计算，所以建立的是会话级临时表；

聊完了临时表，大家会发现我用的sql语句创建表不是直接create开始的，而用的execute immediate语句，为什么要用exec语句，这里就要来聊聊动态sql语句；

Oracle编译PL/SQL程序块分为两个种：  
其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。

以上语句简单的理解就是，我用select查询的时候，这个语句是在Oracle中提前编译好的，所以如果我select中条件写错了，少个and 少个where等到会直接报错，这样编译好的语句运行起来非常快，只要条件正确，就可以极快的跑出结果，而后期联编，指的是我这条语句不经过oracle后台默认编译，仅在我执行的时候进行局部编译，只要我语句正确就直接执行。这样的临时编译，会调用系统资源去编译sql语句，和前期编译好的sql相比较，占用系统资源会多一些；

为什么后期连编占用系统资源多我们还要使用呢，因为在存储过程中，不支持ddl语句，不能create drop等到操作，但是我的需求上迫使我需要一个临时表，不得不create table，所以为了达到需求，我们降低一点性能而选择使用后期联编，即动态sql操作；

动态sql基本语法：

executeimmediate 'SQL语句' using ’绑定参数列表‘ returning into '输出参数列表';

using 和 return不是必须的，但是execute必须要；

这样我们就达到了在存储过程中创建表的方法；

这里我要说明的是，一个存储过程中，create动态sql和查询该表select是无法共存的，具体原因我还在学习中，所以这里我在procedure1中create表后没有直接在这个存储过程中select，尝试后会报错，所以我把select语句放到了procedure2中；

我的动态sql：

begin
      execute immediate
      'create global temporary
      table t_temp_frozen(ext1 number(10),ext2 varchar2(100))
    on commit preserve rows';

动态sql也可以用变量赋值的形式使用，具体方法可以自行百度，这里主要介绍下动态sql的概念，并提供一个存储过程中DDL语句的创建方案。当然我们也可以在动态sql 中执行select，这个写法也是经常出现的。

动态sql说完了我们来说说变量，变量没什么好多说的了，之前有过一些分享，这次主要说说变量的类型定义，因为我们在定义变量的时候，肯定会使用他，那么给变量传值的时候变量类型必须要和传值类型一致，有时候我们是吧表中的字段值传给变量，俺么变量类型怎么定义呢，我们不一定要去查这个表的这个字段，我们可以直接定义表中的类型完全复制给变量使其类型和字段类型一致，方法是：

A 表.字段%type；

这个变量A的类型就是表中定义的字段类型；

说完了变量类型，我们继续聊游标：

游标详解网上太多说名，我就不做原理介绍，这里我来举例说明理解下游标是来干嘛的；

比如我一张表有3个字段10行值；

我想逐行取值，按照我的想法自由取，怎么办？

这是后就可以用游标，游标类似于一个光标放在表中，每次他都只跑一行数据，从第一行开始一直逐行跑到最后一行后跑一次空行，又开始循环从第一行开始跑，

所以我能用游标渠道这张表的一个值，计算完这个值后，游标又去跑拿到第二行值，这样是不是就可以取到不同行的数据来计算了？

所以游标非常方便，有人会问，我去了1行数据，但是有三个值怎么办？ 我们可以用select 字段的形式，只展示一个字段，那不就是一个值了？同样我可以拿2个字段，3个字段，游标会按照表的字段顺序存入字段值，我们一般都会把游标赋值给变量让变量带值去参与逻辑计算，所以我赋值一个时直接into给变量A就行了，若我要待3个字段给变量A B C，那么我游标CURSOR赋值的方式是 corsor into A,B,C这里ABC的顺序是游标中的字段顺序，即你给游标的select展示的字段顺序；

描述完了游标，按照这次sql来举例：

cursor  C_frozen is
    select t.commodity_id,t.province from t_commodity_frozen_test t
    where t.status=0
    and t.create_time > trunc(sysdate-1);

游标定义是直接放在变量的位置，即as后 begin前，语法是：

cursor A is +游标范围，即你要取值的结果集；

这里我去了我的mq表中的状态为0创建时间大于昨天的数据结果集给到游标；

使用游标：

在获取到这个结果集后，一般要把值给到变量参与计算，计算完第一个游标数据后，要使得计算第二个游标数据，必须要循环遍历，所以游标经常和循环一起用；

open C_frozen;
      loop fetch  C_frozen into V_commoditid,V_provincename;
        exit when  C_frozen%notfound;

游标赋值给变量参与计算不用想也是在begin计算体里面的，这里语法意思是：打开游标，我们在使用游标时候必须要明确指定打开使用，使用完后明确要关闭，否则会一直占有系统资源；loop是循环语句，类似的有for while等等，循环语句可以搜索了解下；然后into是把游标找到的值赋值给变量，这里我要使得数据唯一性，用了两个字段定义游标，所以赋值的时候给了两个变量，定义多少字段给游标就要赋值给多少字段，如果我单独赋值给1个字段，会报错。exit 退出循环，when后面是加了退出循环的条件，%notfound这个是固定的，意思是如果游标遍历到了空值，即所有数据都跑完了一次后，跳出游标循环；

后续的判断计算逻辑不做说明，记得要关闭循环和关闭游标；

存储过程完整的写法是begin中应该有个exception异常抛出，这个不是必要条件之前没介绍过，这里为了避免出现未知异常直接抛错，所以定义了一个exception，这不是避免错误，二十在错误发生的时候输出exception异常情况而已；

存储过程聊完后，在聊聊本次的触发器：

触发器是系统级的，不可售人工启动，而是感受到定义的系统变化而触发操作定义在触发器中的动作；

触发器篇幅很长，原理不做说明，基本sql结构是：

CREATE \[OR REPLACE\] TRIGGER trigger\_name  
\{BEFORE | AFTER \}--动作前还是动作后触发  
\{INSERT | DELETE | UPDATE \[OF column \[, column …\]\]\}--哪些动作触发  
\[OR \{INSERT | DELETE | UPDATE \[OF column \[, column …\]\]\}...\]  
ON \[schema.\]table\_name | \[schema.\]view\_name --哪张表发生了动作触发  
\[REFERENCING \{OLD \[AS\] old | NEW \[AS\] new| PARENT as parent\}\]--新值还是旧值  
\[FOR EACH ROW \]--行级触发，若不写  
\[WHEN condition\]--条件  
PL/SQL\_BLOCK | CALL procedure\_name;--调用存储过程

这里简化其他条件语法，简单下来：

create or replace trigger commodity_frozentest
    after insert or update on t_commodity_frozen_test
      begin
        commodity_frozen_test.commodityfrozen;
      end;

意思是创建一个存储过程，当表test被update或者insert之后，执行存储过程frozen；

触发器不是所有条件都必须写，按照自己需求来就行了；

触发器中不能执行ddl语句，需要ddl语句的写个过程，然后触发器调用就行了，触发器一张表最多只能建12个，越多性能越差，所以不要随意建立，触发器也有大小限制，sql较多的，请写过程，触发器调用；另外，触发器或者调用过程中不能有commit，比如我之前写完过程后，想执行完后直接commit提交，但是用触发器调用就报错了；如果限定for each row，代表触发器会一行一行去调用过程，那么如果限定为逐行触发，那么过程中游标就没有用了，游标主要目的也是逐行取值，这样会冲突报错，解决方案是触发器for each row时，让触发器传参数给过程，过程就舍去游标通过传入参数的方式执行，若过程要用游标，那触发器就必须要为表级触发器而不能是行级触发器，需要删掉for each row行级限定。

本次存储过程分析就到这里，有什么不懂的欢迎大家一起探讨。

[70]: /images/20220519/fcff2067bc2b4adca9bfa9214211dec1.png
[70 1]: /images/20220519/76bfa72a77d8485dafe9c5104136e343.png
[70 2]: /images/20220519/1ce819d19ac643dfb0312c8b612131ba.png