insert into test2(id) values(i);
if i<20 then
commit;
end if;
if i=100 then
select name into var_name from test1 where id=0;
end if;
end loop;
end;
/
t1: 10000
t2:19
10. t1嵌套t2,t2嵌套t3, 出错前commit的提交,未提交的强制回滚.
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
end loop;
t2;
end;
/
CREATE OR REPLACE procedure SCOTT.t2
as
begin
for i in 1..10000 loop
insert into test2(id,name) values(i,'leng'||i);
end loop;
t3;
end;
/
CREATE OR REPLACE procedure SCOTT.t3
as
var_name varchar2(20);
begin
for i in 1..10000 loop
insert into test3(id) values(i);
if i<20 then
commit;
end if;
if i=100 then
select name into var_name from test1 where id=0;
end if;
end loop;
end;
/
t1:10000
t2:10000
t3:19
11. 总结
把一个procedure中所有的程序和语句看成顺序执行,不管是嵌套多少层,commit的就起效,未commit的,如果出错则从出错的地方强制退出程序,如果不出错,退出session时默认提交.