Oracle存储过程实现多线程对表数据的抽取 收藏 原先使用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;
-- 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; 这个性能的关键是要及时回写状态栏位,使下一个线程不会尝试太多的记录。 Oracle的AQ也同样实现,各位大虾谁能讲解一下其实现方法? 发表于 @ 2008年01月23日 13:15:00 | 评论( 0 ) | 编辑| 举报| 收藏
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/leo_fanaq/archive/2008/01/23/2061022.aspx
|