oracle触发器 办公设备验收流程

 

最近写了个 办公设备验收流程 归档后反写卡片 的触发器，修改了好几次，感觉很典型，在这备份下：

1.首先流程主表有两个子表，分别记录对应的 随机工具 随机资料

  这就需要我先写两个游标来查询对应子表的信息，然后for loop 分别遍历插入子表

2.验收入库数量为多少，我就的反写多少条记录，这样我就的首先查询到入库数量，然后利用for loop插入反写主表

3.代码：

  create or replace trigger bgsbjjyanshou_trigger
  after update on workflow_requestbase
  for each row
     --流程号为721的检验设备验收流程
when (new.workflowid = 721)
declare
       -- local variables here
       shebei1 varchar2(100);
       shebei2 varchar2(100);
       --jingzhundus varchar2(200);
      -- waigaunyanshourens varchar2(4000);
      -- xingnengyanshourens varchar2(4000);
      -- waigaunyanshourenName varchar2(4000);
      -- xingnengyanshourenName varchar2(4000);
       num number(10);
       bianhao varchar2(200);--用户选择的资产编号
       fixbh varchar2(200);-- 编号前缀
       sufbh varchar2(200);-- 编号后缀
       maxbianhao number(10); --便利出来的最大编号  根据前缀来分组  注意！
       xop integer;--
       ids number(10);

       ---------
       zichanids  number(32);
       zichanbianhaos varchar2(32);
       fenleidaimas varchar2(32);
       fenleimingchens varchar2(160);
       zichanmingchens varchar2(160);
       yijifenleis  varchar2(32);
       erjifenleis  varchar2(32);
       jiages  number(18,3);
       jiliangdanweis  varchar2(32);
       gouzhiriqis varchar2(32);
       fapiaohaos varchar2(32);
       --jiliangshuxings varchar2(32);
       zichanyongtus varchar2(255);
       zichanleishus varchar2(32);
       zichanleixins varchar2(32);
       danweis varchar2(128);
       shiyongnianxians number;
       pingpais varchar2(64);
       guigexinhaos varchar2(64);
       zuixiaofenduzhis varchar2(32);
       liangchens number(18,3);
       guobies varchar2(32);
       caigourens varchar2(32);
       shengchanchangjias varchar2(160);
       chuchangbianhaos varchar2(160);
       chuchangriqis  varchar2(24);
       gongyingshangs varchar2(32);
       shouhoufuwudws  varchar2(160);
       shouhoufuwlxrs varchar2(160);
       shouhoufuwulxdhs varchar2(32);
       hetongbianhaos varchar2(32);
       zhibaoqijiezhirqs varchar2(24);
       jishuwenjianbhs varchar2(32);
       jishucanshu1s varchar2(160);
       jishucanshu2s varchar2(160);
       yanshoudanbhs varchar2(32);
       shiyongzhuangkuangs varchar2(255);
       shiyongfangxiangs varchar2(32);
       qiyongrqs varchar2(24);
       tingyongrqs varchar2(24);
       zhejiukaishirqs varchar2(24);
       shifouyouxiaos varchar2(32);
       shiyongbumens varchar2(64);
       shiyongrens number(10);
       anzhuangdidians varchar2(32);
       shuliang1s number(18,3);
       lingjiekucunsls number(18,3);
       danjias number(18,3);
       jines number(18,3);
       chuzuchujiedfdws varchar2(64);
       jiazhileixins varchar2(32);
       zichanlaiyuans varchar2(32);
       jingfeilaiyuans varchar2(32);
       caiwubianhs varchar2(32);
       fuzhangriqis varchar2(24);
       caigouzuzhixss varchar2(32);
       leijizhejius varchar2(24);
       zhangmianjingzhis number(18,3);
       zhengshuleibies varchar2(32);
       shejijiaozhunsmss varchar2(255);
       yiqishebeijiaozhunqrjls varchar2(255);
       jiandingxingshis varchar2(255);
       jiandingzhouqis number(18,3);
       hechashuxings varchar2(32);
       hechazhouqis varchar2(32);
       baoyangzhouqis  varchar2(32);
       baoyangzhongdians varchar2(1600);
       baoyangfangfas varchar2(1600);
       baoyangzhuyisxs  varchar2(1600);
       gonghuodanweis varchar2(64);
       gonghuofangkaihuyhs varchar2(64);
       zhanghaos varchar2(64);
       hetongbianhaofks varchar2(64);
       xiangmubianhaos varchar2(64);
       hetongqiandingrqs varchar2(24);
       hetongjines number(18,3);
       shebeidaohuorqs varchar2(24);
       hetongbianhaogzs varchar2(64);
       baoyangzhongdianmzs varchar2(1600);
       baoyangfangfamzs  varchar2(1600);
       baoyangzhuyisxmzs varchar2(1600);
       canzhilvs number(18,3);
       tiaoxingmas varchar2(64);
       danduhesuans varchar2(32);
       tupianurls varchar2(1600);
       tupianmingzis varchar2(1600);
       yanshourens varchar2(200);

       --定义游标 并且查询对应子表信息
       cursor cur_gongju is
          select (select zc.zichanmingchen  from gmzichanxinxi zc where zc.zichanid = f.shebei) as shebei1,f1.gongjumingcheng,f1.gongjudanwei,f1.gongjuyingyoushuliang,f1.gongjushiyoushuliang
           from formtable_main_113_dt1 f1,formtable_main_113 f
           where f.requestid=:new.requestid and f.id = f1.mainid;

       c_gongju cur_gongju%rowtype;

      cursor cur_ziliao is
           select (select zc.zichanmingchen  from gmzichanxinxi zc where zc.zichanid = f.shebei) as shebei2,f2.ziliaomingcheng,f2.ziliaodanwei,f2.ziliaoyingyoushuliang,f2.ziliaoshiyoushuliang
            from formtable_main_113_dt2 f2,formtable_main_113 f
            where f.requestid=:new.requestid and f.id = f2.mainid;
       c_ziliao cur_ziliao%rowtype;

begin
     if :new.currentnodetype = 3 then
 -----------------------用户选择的资产编号    开始-----------------------

     select f.rukushuliang  into  num from formtable_main_113 f where f.requestid=:new.requestid  ;
     --判断如果数量

      ----------------------------------- --查询主表信息-开始-------------------------------- 

      --先查出选中的shebei
     select t.shebei,(case when t.shiyongfangxiang=1 then '73' else '74' end) as shiyongfangxiang ,guigexinghao,waigaunyanshouren
     into xop ,shiyongfangxiangs,guigexinhaos,yanshourens
     from formtable_main_113 t where t.requestid = :new.requestid;

 

     --验收人 人名字符串
    select ltrim(max(sys_connect_by_path(lastname, ',')),',') into yanshourens
    from (select lastname, rownum t  from hrmresource where   INSTR(   (select  ',' ||   waigaunyanshouren  || ','   from formtable_main_113 where requestid = :new.requestid),    ',' ||  TRIM(TO_CHAR(id ))  || ','  )  > 0)
    connect by prior t = t - 1  start with t = 1;

 

      update gmzichanxinxi set shiyongfangxiang=shiyongfangxiangs ,yanshouren = yanshourens,guigexinhao=guigexinhaos,jiliangshuxing='02',shifouyanshou='01'
      where zichanid = xop;

    select zichanid,zichanbianhao,
        fenleidaima,fenleimingchen,zichanmingchen,yijifenlei,erjifenlei,jiage,
        jiliangdanwei,gouzhiriqi,fapiaohao,zichanyongtu,zichanleishu,zichanleixin,danwei,
        shiyongnianxian,pingpai,guobie,caigouren,
        shengchanchangjia,gongyingshang,shouhoufuwudw,shouhoufuwlxr,
        shouhoufuwulxdh,hetongbianhao,zhibaoqijiezhirq,jishuwenjianbh,jishucanshu1,jishucanshu2,
        yanshoudanbh,shiyongzhuangkuang,qiyongrq,tingyongrq,zhejiukaishirq,
        shifouyouxiao,shiyongbumen,shiyongren,anzhuangdidian,shuliang1,lingjiekucunsl,danjia,
        jine,chuzuchujiedfdw,jiazhileixin,zichanlaiyuan,jingfeilaiyuan,caiwubianh,fuzhangriqi,
        caigouzuzhixs,leijizhejiu,zhangmianjingzhi,zhengshuleibie,shejijiaozhunsms,yiqishebeijiaozhunqrjl,
        jiandingxingshi,jiandingzhouqi,hechashuxing,hechazhouqi,baoyangzhouqi,baoyangzhongdian,
        baoyangfangfa,baoyangzhuyisx,gonghuodanwei,gonghuofangkaihuyh,zhanghao,hetongbianhaofk,
        xiangmubianhao,hetongqiandingrq,hetongjine,shebeidaohuorq,hetongbianhaogz,baoyangzhongdianmz,
        baoyangfangfamz,baoyangzhuyisxmz,canzhilv,tiaoxingma,danduhesuan,tupianurl,tupianmingzi
       into zichanids,zichanbianhaos,
        fenleidaimas,fenleimingchens,zichanmingchens,yijifenleis,erjifenleis,jiages,
        jiliangdanweis,gouzhiriqis,fapiaohaos,zichanyongtus,zichanleishus,zichanleixins,danweis,
        shiyongnianxians,pingpais,guobies,caigourens,
        shengchanchangjias,gongyingshangs,shouhoufuwudws,shouhoufuwlxrs,
        shouhoufuwulxdhs,hetongbianhaos,zhibaoqijiezhirqs,jishuwenjianbhs,jishucanshu1s,jishucanshu2s,
        yanshoudanbhs,shiyongzhuangkuangs,qiyongrqs,tingyongrqs,zhejiukaishirqs,
        shifouyouxiaos,shiyongbumens,shiyongrens,anzhuangdidians,shuliang1s,lingjiekucunsls,danjias,
        jines,chuzuchujiedfdws,jiazhileixins,zichanlaiyuans,jingfeilaiyuans,caiwubianhs,fuzhangriqis,
        caigouzuzhixss,leijizhejius,zhangmianjingzhis,zhengshuleibies,shejijiaozhunsmss,yiqishebeijiaozhunqrjls,
        jiandingxingshis,jiandingzhouqis,hechashuxings,hechazhouqis,baoyangzhouqis,baoyangzhongdians,
        baoyangfangfas,baoyangzhuyisxs,gonghuodanweis,gonghuofangkaihuyhs,zhanghaos,hetongbianhaofks,
        xiangmubianhaos,hetongqiandingrqs,hetongjines,shebeidaohuorqs,hetongbianhaogzs,baoyangzhongdianmzs,
        baoyangfangfamzs,baoyangzhuyisxmzs,canzhilvs,tiaoxingmas,danduhesuans,tupianurls,tupianmingzis
        from gmzichanxinxi
    where zichanid = xop;

    select f.zichanbianhao into bianhao from gmzichanxinxi f where f.zichanid= xop ;

       --截取编号的前缀
     select substr(bianhao,0,length(bianhao)-4) into fixbh from dual;
    

       --截取编号的后缀
     select substr(bianhao,length(bianhao)-3,length(bianhao)) into sufbh from dual;

     select max(substr(f.zichanbianhao,length(f.zichanbianhao)-3,length(f.zichanbianhao))) into maxbianhao from gmzichanxinxi f where f.zichanbianhao like fixbh||'%' ;

     --------------------------------开始添加数据    开始---------------------------------

        --插入随机工具数据
           for c_gongju in cur_gongju loop

               -- 状态：00-代表随机工具  01-代表随机资料
             insert into gmcjibenshuxing (jibenshuxingid,zichanid,zhuangtai,suijimingcheng,suijidanwei,suijiyingyousl,suijishiyousl)
                   values(seqjbenshuxing.nextval,xop,'00',c_gongju.gongjumingcheng,c_gongju.gongjudanwei,c_gongju.gongjuyingyoushuliang,c_gongju.gongjushiyoushuliang);
          end loop;
           --插入随机资料数据
          for c_ziliao in cur_ziliao loop
               -- 状态：00-代表随机工具  01-代表随机资料
            insert into gmcjibenshuxing (jibenshuxingid,zichanid,zhuangtai,suijimingcheng,suijidanwei,suijiyingyousl,suijishiyousl)
                    values(seqjbenshuxing.nextval,xop,'01',c_ziliao.ziliaomingcheng,c_ziliao.ziliaodanwei,c_ziliao.ziliaoyingyoushuliang,c_ziliao.ziliaoshiyoushuliang);
          end loop;

        for i in 1..num-1 loop

         select seqzichanxinxi.nextval into ids from dual;
        --插入随机工具数据
           for c_gongju in cur_gongju loop

               -- 状态：00-代表随机工具  01-代表随机资料
             insert into gmcjibenshuxing (jibenshuxingid,zichanid,zhuangtai,suijimingcheng,suijidanwei,suijiyingyousl,suijishiyousl)
                   values(seqjbenshuxing.nextval,ids,'00',c_gongju.gongjumingcheng,c_gongju.gongjudanwei,c_gongju.gongjuyingyoushuliang,c_gongju.gongjushiyoushuliang);
          end loop;
           --插入随机资料数据
          for c_ziliao in cur_ziliao loop
               -- 状态：00-代表随机工具  01-代表随机资料
            insert into gmcjibenshuxing (jibenshuxingid,zichanid,zhuangtai,suijimingcheng,suijidanwei,suijiyingyousl,suijishiyousl)
                    values(seqjbenshuxing.nextval,ids,'01',c_ziliao.ziliaomingcheng,c_ziliao.ziliaodanwei,c_ziliao.ziliaoyingyoushuliang,c_ziliao.ziliaoshiyoushuliang);
          end loop;

        insert into gmzichanxinxi (
        zichanid,zichanbianhao,
        fenleidaima,fenleimingchen,zichanmingchen,yijifenlei,erjifenlei,jiage,
        jiliangdanwei,gouzhiriqi,fapiaohao,jiliangshuxing,zichanyongtu,zichanleishu,zichanleixin,danwei,
        shiyongnianxian,pingpai,guigexinhao,zuixiaofenduzhi,liangchen,guobie,caigouren,yanshouren,
        shengchanchangjia,chuchangbianhao,chuchangriqi,gongyingshang,shouhoufuwudw,shouhoufuwlxr,
        shouhoufuwulxdh,hetongbianhao,zhibaoqijiezhirq,jishuwenjianbh,jishucanshu1,jishucanshu2,
        yanshoudanbh,shiyongzhuangkuang,shiyongfangxiang,qiyongrq,tingyongrq,zhejiukaishirq,
        shifouyouxiao,shiyongbumen,shiyongren,anzhuangdidian,shuliang1,lingjiekucunsl,danjia,
        jine,chuzuchujiedfdw,jiazhileixin,zichanlaiyuan,jingfeilaiyuan,caiwubianh,fuzhangriqi,
        caigouzuzhixs,leijizhejiu,zhangmianjingzhi,zhengshuleibie,shejijiaozhunsms,yiqishebeijiaozhunqrjl,
        jiandingxingshi,jiandingzhouqi,hechashuxing,hechazhouqi,baoyangzhouqi,baoyangzhongdian,
        baoyangfangfa,baoyangzhuyisx,gonghuodanwei,gonghuofangkaihuyh,zhanghao,hetongbianhaofk,
        xiangmubianhao,hetongqiandingrq,hetongjine,shebeidaohuorq,hetongbianhaogz,baoyangzhongdianmz,
        baoyangfangfamz,baoyangzhuyisxmz,canzhilv,tiaoxingma,danduhesuan,tupianurl,tupianmingzi,shifouyanshou)
        values(
         ids, fixbh || replace(lpad(to_char(maxbianhao + i),4),' ','0'),
         fenleidaimas,fenleimingchens,zichanmingchens,yijifenleis,erjifenleis,jiages,
        jiliangdanweis,gouzhiriqis,fapiaohaos,'02',zichanyongtus,zichanleishus,zichanleixins,danweis,
        shiyongnianxians,pingpais,guigexinhaos,zuixiaofenduzhis,liangchens,guobies,caigourens,yanshourens,
        shengchanchangjias,chuchangbianhaos,chuchangriqis,gongyingshangs,shouhoufuwudws,shouhoufuwlxrs,
        shouhoufuwulxdhs,hetongbianhaos,zhibaoqijiezhirqs,jishuwenjianbhs,jishucanshu1s,jishucanshu2s,
        yanshoudanbhs,shiyongzhuangkuangs,shiyongfangxiangs,qiyongrqs,tingyongrqs,zhejiukaishirqs,
        shifouyouxiaos,shiyongbumens,shiyongrens,anzhuangdidians,shuliang1s,lingjiekucunsls,danjias,
        jines,chuzuchujiedfdws,jiazhileixins,zichanlaiyuans,jingfeilaiyuans,caiwubianhs,fuzhangriqis,
        caigouzuzhixss,leijizhejius,zhangmianjingzhis,zhengshuleibies,shejijiaozhunsmss,yiqishebeijiaozhunqrjls,
        jiandingxingshis,jiandingzhouqis,hechashuxings,hechazhouqis,baoyangzhouqis,baoyangzhongdians,
        baoyangfangfas,baoyangzhuyisxs,gonghuodanweis,gonghuofangkaihuyhs,zhanghaos,hetongbianhaofks,
        xiangmubianhaos,hetongqiandingrqs,hetongjines,shebeidaohuorqs,hetongbianhaogzs,baoyangzhongdianmzs,
        baoyangfangfamzs,baoyangzhuyisxmzs,canzhilvs,tiaoxingmas,danduhesuans,tupianurls,tupianmingzis,'01'
        );

     end loop;

end if;

 

end;