Oracle存储过程实现多线程对表数据的抽取 - 狮子尾巴的专栏 - CSDN博客
来源:百度文库 编辑:神马文学网 时间:2024/04/30 02:51:57
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