Oracle中存储过程如何控制提交和回滚
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle中存储过程如何控制提交和回滚,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4873字,纯文字阅读大概需要7分钟。
内容图文
![Oracle中存储过程如何控制提交和回滚](/upload/InfoBanner/zyjiaocheng/551/512c222c2f2b483d82739429534f0879.jpg)
create table test1(id number,name varchar2(20));create table test2(id number,name varchar2(20));create table test3(id nu
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时默认提交.
内容总结
以上是互联网集市为您收集整理的Oracle中存储过程如何控制提交和回滚全部内容,希望文章能够帮你解决Oracle中存储过程如何控制提交和回滚所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。