create or replace procedure SRD_SERV_HISTORY is
v_acc_nbr1 srd.serv_history.acc_nbr%Type;
v_acc_nbr2 srd.serv_history.acc_nbr%Type;
v_acc_nbr srd.serv_history.acc_nbr%Type;
v_serv_id number(12);
v_count int ; CURSOR c_crm_servhis is
select acc_nbr, SUBSTR(acc_nbr, 2, 3), SUBSTR(acc_nbr, 6, 12)
from serv_history ;Begin
Open c_crm_servhis;
Loop
FETCH c_crm_servhis
Into v_acc_nbr, v_acc_nbr1, v_acc_nbr2;
select serv_id
into v_serv_id
from crm.serv
where area_code = v_acc_nbr1 and billing_no = v_acc_nbr2;
Update serv_history
Set crm_serv_id = v_serv_id
where acc_nbr = v_acc_nbr;
if v_count = 100000 then
commit;
v_count:=0;
else
v_count:=v_count + 1;
end if;
EXIT WHEN c_crm_servhis%NOTFOUND;
commit;
End loop;
Close c_crm_servhis;
End;
这是我的存储过程。我在这个过程里面用了游标但是在执行select serv_id into v_serv_id from crm.serv where area_code = v_acc_nbr1 and billing_no = v_acc_nbr2; 句的时候有可能出现null。。现在我就想出现null后,后面的语句不执行,游标取下一条记录。。这个要怎么处理一下。。
v_acc_nbr1 srd.serv_history.acc_nbr%Type;
v_acc_nbr2 srd.serv_history.acc_nbr%Type;
v_acc_nbr srd.serv_history.acc_nbr%Type;
v_serv_id number(12);
v_count int ; CURSOR c_crm_servhis is
select acc_nbr, SUBSTR(acc_nbr, 2, 3), SUBSTR(acc_nbr, 6, 12)
from serv_history ;Begin
Open c_crm_servhis;
Loop
FETCH c_crm_servhis
Into v_acc_nbr, v_acc_nbr1, v_acc_nbr2;
select serv_id
into v_serv_id
from crm.serv
where area_code = v_acc_nbr1 and billing_no = v_acc_nbr2;
Update serv_history
Set crm_serv_id = v_serv_id
where acc_nbr = v_acc_nbr;
if v_count = 100000 then
commit;
v_count:=0;
else
v_count:=v_count + 1;
end if;
EXIT WHEN c_crm_servhis%NOTFOUND;
commit;
End loop;
Close c_crm_servhis;
End;
这是我的存储过程。我在这个过程里面用了游标但是在执行select serv_id into v_serv_id from crm.serv where area_code = v_acc_nbr1 and billing_no = v_acc_nbr2; 句的时候有可能出现null。。现在我就想出现null后,后面的语句不执行,游标取下一条记录。。这个要怎么处理一下。。
-----------
你直接用条件过null值过滤掉不是更好吗?
而且运行效率更高
select count(serv_id) into n from crm.serv where area_code = v_acc_nbr1 and billing_no = v_acc_nbr2;
if n>0 then 执行你要执行的语句