月头月尾oracle取数,Oracle存储过程实现多线程对表数据的抽取 - 狮子尾巴的专栏 - CSDN博客...

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 ) | 编辑| 举报| 收藏