begin select column into l_column from table_name for update nowait; exception when others then --如果锁住直接被错误捕捉 end;
利用select for update nowait会成功或马上引发异常的原理,自己写个函数来处理吧(这只是个例子,提供个思路,未对其他异常进行排错处理,直接使用会死的很惨): create or replace package body pk_test is if_locked boolean; procedure pro_testlocked (TestSql varchar2) is myexception exception; begin pk_test.if_locked := false; execute immediate TestSql||' nowait'; rollback; exception when others then pk_test.if_locked := true; end pro_testlocked; function fun_getlocked(TestSql varchar2) return number is begin pro_testlocked (TestSql); if pk_test.if_locked then return 1; else return -1; end if; end fun_getlocked; end pk_test;测试: -- Created on 2006-4-1 by ADMINISTRATOR declare -- Local variables here i integer; begin -- Test statements here i := pk_test.fun_getlocked('select * from test001 for update') ; dbms_output.put_line(i); end; 结果:表被锁定时返回1,未被锁定返回-1;
select column into l_column from table_name for update nowait;
exception
when others then
--如果锁住直接被错误捕捉
end;
create or replace package body pk_test
is
if_locked boolean;
procedure pro_testlocked (TestSql varchar2)
is
myexception exception;
begin
pk_test.if_locked := false;
execute immediate TestSql||' nowait';
rollback;
exception
when others then
pk_test.if_locked := true;
end pro_testlocked;
function fun_getlocked(TestSql varchar2) return number
is
begin
pro_testlocked (TestSql);
if pk_test.if_locked then
return 1;
else
return -1;
end if;
end fun_getlocked;
end pk_test;测试:
-- Created on 2006-4-1 by ADMINISTRATOR
declare
-- Local variables here
i integer;
begin
-- Test statements here
i := pk_test.fun_getlocked('select * from test001 for update') ;
dbms_output.put_line(i);
end;
结果:表被锁定时返回1,未被锁定返回-1;