重庆装修公司前十:Oracle存储过程实现多线程对表数据的抽取
来源:百度文库 编辑:九乡新闻网 时间:2024/04/27 21:51:10
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也同样实现,各位大虾谁能讲解一下其实现方法? 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/leo_fanaq/archive/2008/01/23/2061022.aspx
原先使用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也同样实现,各位大虾谁能讲解一下其实现方法? 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/leo_fanaq/archive/2008/01/23/2061022.aspx
Oracle存储过程实现多线程对表数据的抽取
Oracle存储过程实现多线程对表数据的抽取
Oracle存储过程
oracle 存储过程
数据仓库的数据存储和实现
zhouweifeng | ORACLE动态调用存储过程
Oracle数据库实现获取前几条数据的方法
自己写的存储过程
分页的存储过程方法
Oracle闪回truncate删除的表中数据
ado.net执行oracle 存储过程-ASP.NET应用-www.knowsky.co...
可以实现随身存储的网盘
存储过程和触发器的区别
Oracle中大批量删除数据的方法
Oracle数据的异地自动备份
在线数据存储
PLC数据存储
存储过程 简介 数据库
存储过程基本知识教程
Excel函数实现工作表间的数据关联
NOSQL存储的基于事件的事务实现
使用Oracle可传输表空间的特性复制数据(1)基础概念
使用Oracle可传输表空间的特性复制数据(4)转换字节顺序
使用Oracle可传输表空间的特性复制数据(6)RMAN备份也创建