使用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博客 ” ，链接：https://blog.itpub.net/28803801/viewspace-768601/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
