oracle存储过程中回滚,Oracle中存储过程如何控制提交和回滚

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<20 then

commit;

end if;

end loop;

end;

/

6. procedure中没有显示commit和rollback, 如果程序出错,则强制退出程序并回滚.

CREATE OR REPLACE procedure SCOTT.t1

as

var_name varchar2(20);

begin

for i in 1..10000 loop

insert into test1(id) values(i);

if i=100 then

select name into var_name from test1 where id=0 ;  --模拟出错

end if;

end loop;

end;

/

6. procedure中有显示commit, 如果程序出错,commit之前的就已经提交了,commit和出错之间的强制回滚.

CREATE OR REPLACE procedure SCOTT.t1

as

var_name varchar2(20);

begin

for i in 1..10000 loop

insert into test1(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;

/

result: 19

7. 嵌套出错. 出错前commit的就提交了,未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;

commit;

t2;

end;

CREATE OR REPLACE procedure SCOTT.t2

as

var_name varchar2(20);

begin

for i in 1..10000 loop

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

8.  t1嵌套t2, t2的commit对t1也起效.

create or replace procedure 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

var_name varchar2(20);

begin

for i in 1..10000 loop

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

9.  t1嵌套t2, t2的rollback对t1也起效.

create or replace procedure 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

var_name varchar2(20);

begin

for i in 1..10000 loop

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时默认提交.