编译正常,数据无法转过去。
create or replace procedure mid_pro
is
vc_subscrbid varchar(40);
vc_Crm_productid varchar(20);
-- vc_Prm_productid varchar(20);
-- vc_serviceid varchar(20);
-- vc_Developid varchar(20);
-- vc_Effdate date;
-- vc_Expdate date;
cursor s1 is
select MDN,OrderID,SP_ProductID,PushId,EffectiveDate,ExpireDate from p_Orderinfo_Syn;
lr1 s1%rowtype;
BEGIN
--open s1;
FOR lr1 IN s1 LOOP
begin
select t.subscrbid into vc_subscrbid from ucis.tab_subscrb t
where t.svcnum = lr1.mdn;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('用户标示查找错误');
end;
begin
select t1.serviceid into vc_Crm_productid from ucis.tab_crmproductservice t1
where t1.spec_productid= lr1.sp_productid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('服务编码查错误');
end;
insert into ucis.tab_subsprelation(subscrbid,Crm_productid,Prm_productid,serviceid,Developid,Effdate,Expdate)
values(vc_subscrbid,
lr1.orderid,
lr1.sp_productid,
vc_Crm_productid,
lr1.pushid,
to_date(lr1.effectivedate,'yyyy-mm-dd hh24:mi:ss'),
to_date(lr1.expiredate,'yyyy-mm-dd hh24:mi:ss'));
update p_orderinfo_syn set
TOPROCESS = '1'
where OrderID = lr1.orderid;
COMMIT;
END LOOP;
exception
when others then
dbms_output.put_line('error');
rollback;
close s1;
end mid_pro;
create or replace procedure mid_pro
is
vc_subscrbid varchar(40);
vc_Crm_productid varchar(20);
-- vc_Prm_productid varchar(20);
-- vc_serviceid varchar(20);
-- vc_Developid varchar(20);
-- vc_Effdate date;
-- vc_Expdate date;
cursor s1 is
select MDN,OrderID,SP_ProductID,PushId,EffectiveDate,ExpireDate from p_Orderinfo_Syn;
lr1 s1%rowtype;
BEGIN
--open s1;
FOR lr1 IN s1 LOOP
begin
select t.subscrbid into vc_subscrbid from ucis.tab_subscrb t
where t.svcnum = lr1.mdn;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('用户标示查找错误');
end;
begin
select t1.serviceid into vc_Crm_productid from ucis.tab_crmproductservice t1
where t1.spec_productid= lr1.sp_productid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('服务编码查错误');
end;
insert into ucis.tab_subsprelation(subscrbid,Crm_productid,Prm_productid,serviceid,Developid,Effdate,Expdate)
values(vc_subscrbid,
lr1.orderid,
lr1.sp_productid,
vc_Crm_productid,
lr1.pushid,
to_date(lr1.effectivedate,'yyyy-mm-dd hh24:mi:ss'),
to_date(lr1.expiredate,'yyyy-mm-dd hh24:mi:ss'));
update p_orderinfo_syn set
TOPROCESS = '1'
where OrderID = lr1.orderid;
COMMIT;
END LOOP;
exception
when others then
dbms_output.put_line('error');
rollback;
close s1;
end mid_pro;
select t.subscrbid into vc_subscrbid from ucis.tab_subscrb t
where t.svcnum = lr1.mdn;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('用户标示查找错误');
end;
是这一句的问题了,知道了,他是每次游标走到第一个位置然后就不走下一个了,直接没有值就报错。谁能告诉我怎么才能循环不报错,找对那个数据呢?
select count(*) into cu
from ucis.tab_subscrb t
where t.svcnum = lr1.mdn;
if cu >0 then
select t.subscrbid into vc_subscrbid from ucis.tab_subscrb t
where t.svcnum = lr1.mdn;
end if;
end;