drop table USERINFO ;
create table USERINFO
(
ID VARCHAR2(32) not null,
username VARCHAR2(20),
password VARCHAR2(20)
);
insert into USERINFO(id, Username, Password) values('1','1','1');
create or replace procedure sp_test
is
temp1 varchar2(1000); /*定义临时变量*/
temp2 varchar2(1000); /*定义临时变量*/
begin
select username into temp1 from USERINFO where id=1; /*查询并返回值*/
Dbms_Output.put_line('===temp1==='||temp1);
savepoint point1; /*保存点*/
--sys.DBMS_LOCK.SLEEP(10);
DBMS_LOCK.SLEEP(10);
select username into temp2 from USERINFO where id=1; /*查询并返回值*/
Dbms_Output.put_line('===temp2==='||temp2);
savepoint point2;
exception
when others then
rollback to savepoint point1; /*异常处理,保存点下面的操作都不会被执行*/
return;
end;
执行以上存储过程,
然后快速在另外一个窗口中(10秒内,因为上面存储过程sleep了10秒)
执行update USERINFO a set a.username = '2' where a.id = 1;
最后存储过程输出:
===temp1===1
===temp2===2
在存储过程中begin后增加
set transaction read only;
最终输出结果
===temp1===1
===temp2===1