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