ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 修改的存储过程

修改的存储过程

原创 Linux操作系统 作者:lihy114 时间:2013-08-15 17:12:22 0 删除 编辑
使用kettle导入oralce数据表,其中两个表涉及到了触发器,同时触发器还调用了存储过程;以为已经写好的东西不会有错误的,这种盲目相信导致了自己耗费了接近一天的时间!!!引以为戒。

--自己在里面加了进入loop循环的判断,跳出循环以及最后的commit



CREATE OR REPLACE PROCEDURE "TOPICIS"."REALTIMECAUTION2"( V_SIGN VARCHAR2, YWBS NUMBER) AS
   ztbs NUMBER;
   zch varchar(50);
   qymc varchar(200);
   zxrq TIMESTAMP;
   bgrq TIMESTAMP;
   cjr_bs NUMBER;
   jg_bs NUMBER;
   zgjg_bs NUMBER;
   zw_bs NUMBER;
   ajbs NUMBER;

--游标声明
    cursor cursor6 is  
       select a.ID as ztbs,a.RegNO,a.EntName as qymc, b.RegDate as zxrq, a.PerID,a.OrgID,a.PriOrgID,a.JobID  from REG_MarPriPInfo a,REG_MarPriPCancel b,REG_ParentEnt c where a.EntType in ('2100','2200') and b.RegNO = c.RegNO and b.ID=YWBS and a.ID = c.MarPrID;
    cursor cursor7 is  
       select a.ID as ztbs,a.RegNO,a.EntName as qymc, a.EstDate as zxrq,a.PerID,a.OrgID,a.PriOrgID,a.JobID from REG_MarPriPInfo a,REG_MarPriPCancel b,REG_Investor c where b.MarPrID in (select d.ID from REG_MarPriPInfo d where d.RegNO=c.BLicNO) and c.BLicType in('11','12','14','21','22','31','32','33') and b.ID=YWBS and c.Batch ='1' and a.ID = c.MarPrID;
    cursor cursor8 is  
       select a.ID as ztbs,a.RegNO,a.EntName as qymc, b.AccDate as bgrq, a.PerID,a.OrgID,a.PriOrgID,a.JobID from REG_MarPriPInfo a,REG_AltAccInfo b,REG_AltAccItem c,REG_MarPriPInfo d,REG_ParentEnt e where a.EntType in ('2100','2200') and c.AltItem = '110' and b.MarPrID = d.ID and d.RegNO = e.RegNO and b.ID = c.AltAccId and b.ID=YWBS and a.ID = e.MarPrID;
    cursor cursor10 is  
        select a.ID, a.CerNO, a.Name, a.PerID, a.OrgID, a.PriOrgID, a.JobID from LAW_CaseInfo a where a.ID = YWBS;

BEGIN
if(V_SIGN = 'cancel')    then
open cursor6;
fetch cursor6 into ztbs,zch,qymc,zxrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
--需要进行控制判断,如果游标中没有数据的话,就不能进行循环   
 while cursor6%found Loop
insert into SIS_CauInfo 
               (ID,MarPrID,BusiActId,RegNO,EntName,LicNo,WarType,
                WarUnit,LimitCorrDate,WarFrom,WarTo,
                WarCont,Remark,RegDate,
                PerID,OrgID,PriOrgID,JobID,State,FromType,WarState,WarStatus) 
                values
   (SEQ_TOPICMS.nextval,ztbs,null,zch,qymc,'',
                '母公司注(吊)销警示信息',null,null,zxrq,
    null,'母公司注(吊)销;注(吊)销时间:' ||to_char(zxrq),null,sysdate,
    cjr_bs,jg_bs,coalesce(zgjg_bs, jg_bs),zw_bs,'02','01','1','16');
fetch cursor6 into ztbs,zch,qymc,zxrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
--设置推出循环的条件,否则就进入了死循环
exit when cursor6%notfound;
    end Loop;
close cursor6;


open cursor7;
fetch cursor7 into ztbs,zch,qymc,zxrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
    while cursor7%found Loop
insert into SIS_CauInfo 
               (ID,MarPrID,BusiActId,RegNO,EntName,LicNo,WarType,WarUnit,LimitCorrDate,
                WarFrom,WarTo,WarCont,Remark,RegDate,
                PerID,OrgID,PriOrgID,JobID,State,FromType,WarState,WarStatus) values
  ( SEQ_TOPICMS.nextval,ztbs,null,zch,qymc,'','投资公司注(吊)销警示信息',null,null,
zxrq,null,'投资公司注(吊)销;注(吊)销时间:' ||to_char(zxrq),null,sysdate,
   cjr_bs,jg_bs,coalesce(zgjg_bs, jg_bs),zw_bs,'02','01','1','16');
fetch cursor7 into ztbs,zch,qymc,zxrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
exit when cursor7%notfound;
    end Loop;
close cursor7;
end if;
--插入母公司变更名称警示信息
if(V_SIGN = 'altname')    then
open cursor8;
fetch cursor8 into ztbs,zch,qymc,bgrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
    while cursor8%found Loop
insert into SIS_CauInfo
              (ID,MarPrID,BusiActId,RegNO,EntName,LicNo,WarType,
               WarUnit,LimitCorrDate,WarFrom,WarTo,WarCont,
               Remark,RegDate,PerID,OrgID,PriOrgID,JobID,State,FromType,WarState,WarStatus) 
               values
  (SEQ_TOPICMS.nextval,ztbs,null,zch,qymc,'', '母公司变更名称警示信息',
                null,null,bgrq,null,'母公司变更名称;变更时间:' ||to_char(bgrq),
                null,sysdate,cjr_bs,jg_bs,coalesce(zgjg_bs, jg_bs),zw_bs,'02','01','1','30');
fetch cursor8 into ztbs,zch,qymc,bgrq,cjr_bs,jg_bs,zgjg_bs,zw_bs;
exit when cursor8%notfound;
end Loop;
close cursor8;
end if;

--对企业立案后,要加警示
if(V_SIGN = 'setcase')    then
open cursor10;
fetch cursor10 into ajbs,zch,qymc,cjr_bs,jg_bs,zgjg_bs,zw_bs;
    while cursor10%found Loop
insert into SIS_CauInfo 
               (ID,MarPrID,BusiActId,RegNO,EntName,LicNo,WarType,WarUnit,LimitCorrDate,
               WarFrom,WarTo,WarCont,Remark,RegDate,PerID,OrgID,PriOrgID,
               JobID,State,FromType,WarState,WarStatus) 
               values
  (SEQ_TOPICMS.nextval,ajbs,null,zch,qymc,'','企业已经被立案',null,null,
   sysdate,null,'企业已经被立案',null,sysdate,cjr_bs,jg_bs,coalesce(zgjg_bs, jg_bs),
   zw_bs,'02','01','1','51');
fetch cursor10 into ajbs,zch,qymc,cjr_bs,jg_bs,zgjg_bs,zw_bs;
exit when cursor10%notfound;
end Loop;
close cursor10;
end if;
commit;
END REALTIMECAUTION2;

没有人可以信赖,只有自己!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28803801/viewspace-768601/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-08-08

  • 博文量
    54
  • 访问量
    151790