create table test1(id number,name varchar2(20));
create table test2(id number,name varchar2(20));
create table test3(id number,name varchar2(20));
1. t1中没有显示commit;
create or replace procedure t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
end loop;
end;
t1中没有显示commit;
exec t1 之后,如果不退出session的话,是不会提交的,此时如果rollback,则回滚,如果commit则提交, 如果disconn的话,会自动提交;
2. t1中有显示commit;
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
commit;
end loop;
end;
/
t1中有显示commit时:
exec t1之后,会直接提交;
3.
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
commit;
if i=20 then
exit;
end if;
end loop;
end;
/
循环中有显示commit, exit前已经提交的就commit了.
4. procedure中既有commit也有rollback,commit之前的就提交,commit和rollback之间的就回滚.
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
commit;
if i=20 then
rollback;
exit;
end if;
end loop;
end;
/
4. procedure中既有commit也有rollback,commit之前的就提交,commit和rollback之间的就回滚.
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
commit;
if i=20 then
rollback;
exit;
end if;
end loop;
end;
/
5.procedure中有部分commit,commit之前的就提交,commit之后的就不提交,如果在session中rollback则回滚,commit则提交,退出自动提交.
CREATE OR REPLACE procedure SCOTT.t1
as
begin
for i in 1..10000 loop
insert into test1(id,name) values(i,'leng'||i);
if i