欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久

  Oracle存儲過(guò)程實(shí)現多線(xiàn)程對表數據的抽取 收藏
原先使用ForUpdateSkipLocked，但直到11g，這個(gè)參數還未被正式支持，而且在此之上使用排序還存在問(wèn)題，所以改用符合ANSI的ForUpdateNowait來(lái)實(shí)現。
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;
 
這個(gè)性能的關(guān)鍵是要及時(shí)回寫(xiě)狀態(tài)欄位，使下一個(gè)線(xiàn)程不會(huì )嘗試太多的記錄。
Oracle的AQ也同樣實(shí)現，各位大蝦誰(shuí)能講解一下其實(shí)現方法？
發(fā)表于 @ 2008年01月23日　13:15:00 | 評論( 0 ) | 編輯| 舉報| 收藏

本文來(lái)自CSDN博客，轉載請標明出處：http://blog.csdn.net/leo_fanaq/archive/2008/01/23/2061022.aspx