转载自:http://yangchao20020.blog.163.com/blog/static/48382247200921321442812/
在PL/SQL中的命令窗口执行下面的语句:(先将下面代码粘贴到命令窗口,粘贴完后,再在窗口里输入"/"键则运行你刚粘贴到窗口里的该程序代码)
【1】业务逻辑:A表中有300W的数据,B表中有70W的数据,现在要对A表中的部分数据进行删除,删除条件是:根据B表中70W数据来对A表进行条件删除,条件的字段为usercode。
set serveroutput on
Declare
rcode integer;
begin
loop
--每次将表a删除10000条记录
delete from a
where exists (select usercode from b where a.usercode = b.usercode)
and rownum < 10000;
--每10000条提交一次
commit;
exit when sql%rowcount = 0;
end loop;
rcode := 1;
--delete操作完成后,rcode值为1
dbms_output.put_line('结果是:'||to_char(rcode));
exception
when others then
rollback;
rcode := 0;
--delete操作失败后,rcode值为0
dbms_output.put_line('结果是:'||to_char(rcode));
end;
Declare
rcode integer;
begin
loop
--每次将表a删除10000条记录
delete from a
where exists (select usercode from b where a.usercode = b.usercode)
and rownum < 10000;
--每10000条提交一次
commit;
exit when sql%rowcount = 0;
end loop;
rcode := 1;
--delete操作完成后,rcode值为1
dbms_output.put_line('结果是:'||to_char(rcode));
exception
when others then
rollback;
rcode := 0;
--delete操作失败后,rcode值为0
dbms_output.put_line('结果是:'||to_char(rcode));
end;
语句的逻辑:每循环一次,删除a表中的10000条记录,并commit提交该delete语句,当整个循环执行完后,70W数据成功删除完后,会在命令窗口打印出(delete出现异常则打印0):
结果是:1
PL/SQL 过程成功完成
【2】业务逻辑:批量插入5000调测试数据到susr_info表和susr_basic表中,做为测试数据。
set serveroutput on
Declare
cnt number(10);
v_userindex number(20);
v_customerindex number(20);
v_usercode varchar(100);
rcode varchar(200);
v_message varchar(200);
begin
cnt := 1;
v_usercode:=16500000000;
loop
begin
sp_get_next_seq('susr_info',v_customerindex);--序列器产生v_customerindex
sp_get_next_seq('susr_basic',v_userindex);--同上
v_usercode := v_usercode + 1;
insert into susr_info
(customerindex,
customerid,
usertruename,
useremail,
userpwd
)
values
(v_customerindex,
v_customerindex,
'' || v_usercode,
' ',
'55555'
);
insert into susr_basic
(userindex,
usercode,
accountindex,
customerindex,
msisdntype
)
values
(v_userindex,
'' || v_usercode,
0,
v_customerindex,
1
);
if mod(v_userindex, 1000) = 0 then
commit;
end if;
exception
when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将异常记录做日志
insert into log_info values('zxdbm_ismp.v3_sub_2',v_message,sysdate,v_usercode);
commit;
end;
cnt := cnt + 1;
if cnt > 5000 then
exit;
end if;
end loop;
commit;
rcode := 'over';
dbms_output.put_line('结果是:'||rcode);
end;
Declare
cnt number(10);
v_userindex number(20);
v_customerindex number(20);
v_usercode varchar(100);
rcode varchar(200);
v_message varchar(200);
begin
cnt := 1;
v_usercode:=16500000000;
loop
begin
sp_get_next_seq('susr_info',v_customerindex);--序列器产生v_customerindex
sp_get_next_seq('susr_basic',v_userindex);--同上
v_usercode := v_usercode + 1;
insert into susr_info
(customerindex,
customerid,
usertruename,
useremail,
userpwd
)
values
(v_customerindex,
v_customerindex,
'' || v_usercode,
' ',
'55555'
);
insert into susr_basic
(userindex,
usercode,
accountindex,
customerindex,
msisdntype
)
values
(v_userindex,
'' || v_usercode,
0,
v_customerindex,
1
);
if mod(v_userindex, 1000) = 0 then
commit;
end if;
exception
when others then
rollback;
v_message := sqlcode||':'||sqlerrm;
--将异常记录做日志
insert into log_info values('zxdbm_ismp.v3_sub_2',v_message,sysdate,v_usercode);
commit;
end;
cnt := cnt + 1;
if cnt > 5000 then
exit;
end if;
end loop;
commit;
rcode := 'over';
dbms_output.put_line('结果是:'||rcode);
end;
语句的逻辑:每循环1000次,将LOOP循环体中的两个insert语句的结果commit,每次循环,如果insert语句只其中一个出现异常,则将异常记录写到日志表log_info中,接着执行下一次循环,当执行insert的次数达到5000,则退出循环,结束整个业务流程。执行完后,会在命令窗口打印出
结果是:over
PL/SQL 过程成功完成