求大神指导Oracle问题
写了这样一段oracle脚本,实现数据搬迁,现在需要修改脚本让其能够实现在数据量按批搬迁的过程中若有某一条数据抛出异常,能偶标记或者记录异常数据,将剩下的数据不rollback的情况下搬完,求大神指导如何修改!!!!急急急!!!
declare
-- Local variables here
i integer;
taskID number;
custID varchar2(32);
TYPE taskInfo is RECORD(
taskIDs NTL_ASSIGNED_TASK_FREEINS.ASSIGNED_TASK_ID%type);
TYPE t_task_info IS TABLE of taskInfo;
v_taskIDs t_task_info;
cursor taskCur is
select NTF.assigned_task_id
from NTL_ASSIGNED_TASK_FREEINS NTF
where exists (select 1
from NTL_TASK_CUSTOMER NTC
where NTC.ASSIGNED_TASK_ID = NTF.ASSIGNED_TASK_ID)
and rownum < 100;
--and NTF.ASSIGNED_TASK_ID < 1000;
begin
-- Test statements here
open taskCur;
loop
fetch taskCur bulk collect
into v_taskIDs limit 10000;
begin
for IDs in 1 .. v_taskIDs.count loop
taskID := v_taskIDs(IDs).taskIDs;
select c.its_cust_id
into custID
from ntl_task_customer c
where c.ASSIGNED_TASK_ID = taskID;
dbms_output.put_line(taskID || ' ' || custID);
insert into NTL_TASK_CUSTOMER_FREEINS
select * from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
insert into NTL_ADDRESS_FREEINS
select * from NTL_ADDRESS where ITS_CUST_ID = custID;
insert into NTL_TELEPHONE_FREEINS
select * from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_TELEPHONE where ITS_CUST_ID = custID;
delete from NTL_ADDRESS where ITS_CUST_ID = custID;
delete from NTL_TASK_CUSTOMER where ASSIGNED_TASK_ID = taskID;
end loop;
commit;
EXCEPTION
WHEN no_data_found then
dbms_output.put_line('123123123');
rollback;
when others then
dbms_output.put_line('123456789'||sqlerrm);
rollback;
end;
exit when taskCur% notfound;
end loop;
close taskCur;
end;