修改的儲存過程

lihy114發表於2013-08-15
儲存過程
使用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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章