原先使用ForUpdateSkipLocked,但直到11g,这个参数还未被正式支持,而且在此之上使用排序还存在问题,所以改用符合ANSI的ForUpdateNowait来实现。
CREATE
OR
REPLACE
PACKAGE BODY RESB_MT_TABLE_PKG
AS
-- Try to lock thw row by RowId
-- 1 Successful
-- 0 Failed
FUNCTION RESB_MT_LOCK_ROW(i_table_source in varchar2 , i_rid in rowid)
RETURN NUMBER IS
o_ret_id number : = 0 ;
BEGIN
EXECUTE IMMEDIATE ' select 1
from ' || i_table_source || '
where rowid = :x
for update nowait '
INTO o_ret_id
USING i_rid;
RETURN 1 ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = - 54 THEN
RETURN 0 ;
ELSE
RAISE;
END IF ;
END ;
-- Try to lock thw row by RowId
-- 1 Successful
-- 0 Failed
FUNCTION RESB_MT_LOCK_ROW(i_table_source in varchar2 , i_rid in rowid)
RETURN NUMBER IS
o_ret_id number : = 0 ;
BEGIN
EXECUTE IMMEDIATE ' select 1
from ' || i_table_source || '
where rowid = :x
for update nowait '
INTO o_ret_id
USING i_rid;
RETURN 1 ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = - 54 THEN
RETURN 0 ;
ELSE
RAISE;
END IF ;
END ;