求大神指导Oracle问题

hilly_002 2013-09-23 09:01:15
写了这样一段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;
...全文
81 回复 打赏 收藏 转发到动态 举报
写回复
用AI写文章
回复
切换为时间正序
请发表友善的回复…
发表回复

17,086

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧