Oracle存儲過程實現(xiàn)多線程對表數(shù)據(jù)的抽取 收藏
原先使用ForUpdateSkipLocked，但直到11g，這個參數(shù)還未被正式支持，而且在此之上使用排序還存在問題，所以改用符合ANSI的ForUpdateNowait來實現(xiàn)。
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;
-- Update the columns which you want.
PROCEDURE RESB_MT_UPDATE_COLUMNS(i_table_source in varchar2,
i_update_expression in varchar2) IS
BEGIN
EXECUTE IMMEDIATE 'update ' || i_table_source || ' set ' ||
i_update_expression ||
' where rowid in (select rid from RESB_MT_TT_ROWIDS)';
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
-- Find in all and Skip locked
-- Void
PROCEDURE RESB_MT_FIND_ROWS_VOID(i_table_source in varchar2,
i_search_condition in varchar2,
i_order_expression in varchar2,
i_update_expression in varchar2,
i_rcount in number) IS
TYPE c_type IS REF CURSOR;
resb_mt_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000) := 'select rowid from ' || i_table_source ||
' where ' || i_search_condition ||
' order by ' || i_order_expression;
BEGIN
OPEN resb_mt_cur FOR v_sql;
LOOP
FETCH resb_mt_cur
INTO v_rowid;
EXIT WHEN resb_mt_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE resb_mt_cur;
-- Update the columns which you want
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
-- Find in all and Skip locked
-- Return CURSOR
PROCEDURE RESB_MT_FIND_ROWS(i_table_source in varchar2,
i_search_condition in varchar2,
i_order_expression in varchar2,
i_update_expression in varchar2,
i_rcount in number,
o_resb_mt_cur out resb_mt_cursor_type) IS
TYPE c_type IS REF CURSOR;
resb_mt_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000) := 'select rowid from ' || i_table_source ||
' where ' || i_search_condition ||
' order by ' || i_order_expression;
v_o_sql VARCHAR2(4000) := 'select * from ' || i_table_source ||
' where rowid in (select rid from RESB_MT_TT_ROWIDS)' ||
' order by ' || i_order_expression;
BEGIN
OPEN resb_mt_cur FOR v_sql;
LOOP
FETCH resb_mt_cur
INTO v_rowid;
EXIT WHEN resb_mt_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE resb_mt_cur;
-- Update the columns which you want
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
OPEN o_resb_mt_cur FOR v_o_sql;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END;
這個性能的關(guān)鍵是要及時回寫狀態(tài)欄位，使下一個線程不會嘗試太多的記錄。
Oracle的AQ也同樣實現(xiàn)，各位大蝦誰能講解一下其實現(xiàn)方法？
發(fā)表于 @ 2008年01月23日 13:15:00 | 評論( 0 ) | 編輯| 舉報| 收藏
本文來自CSDN博客，轉(zhuǎn)載請標明出處：http://blog.csdn.net/leo_fanaq/archive/2008/01/23/2061022.aspx
