我有一个大型源数据集(几百万行(,需要复杂的处理,导致数据量大得多,然后应该卸载并存储为文件。存储需要根据某些参数(即满足特定条件的 N 个源行(划分结果数据。
由于可以在 PL/SQL 中计算上述参数,因此决定最有效的方法是创建一个包,为其中的源行指定一个规范级游标,然后编写一个过程,该过程将部分消耗打开的游标,直到满足条件并用结果数据填充临时表, 然后将被卸载,并且将再次调用该过程,重复直到没有更多的源行。PL/SQL基本上看起来像这样:
create or replace PACKAGE BODY generator as
cursor glob_cur_body(cNo number) is
select *
from source_table
where no = cNo
order by conditions;
procedure close_cur is
begin
if glob_cur_body%isopen then
close glob_cur_body;
end if;
end close_cur;
procedure open_cur(pNo number) is
begin
close_cur;
open glob_cur_body(pNo);
end open_cur;
function consume_cur return varchar2 is
v source_table%rowtype;
part_id varchar2(100);
begin
fetch glob_cur_body into v;
if glob_cur_body%notfound then
return null;
end if;
--Clear temporary tables
--Do the processing until criteria is meet of there's no more rows
--Fill the temporary tables and part_id
return part_id;
end consume_cur;
end generator;
消费者正在执行以下操作(在伪代码中(
generator.open_cur;
part_id = generator.consume;
while ( part_id != null )
{
//offload data from temp tables
part_id = generator.consume;
}
generator.close_cur;
它工作正常,但不幸的是有一个问题:规范级游标使包有状态,这意味着它的重新编译会导致之前已经访问过的会话ORA-04068
。它使维护变得繁琐,因为除了上述功能之外,软件包还有很多功能,并且它被积极用于不相关的目的。
所以,我想摆脱规范级光标,但我不确定这是否可能。我已经放弃了一些想法:
重新打开游标并跳过 N 行:性能糟糕,不可靠,因为受到打开之间对数据所做的任何更改的影响
将源游标提取到 plsql 表中:大小太大。
一次填满整个卸载表,稍后拆分它们:尺寸太大,性能不佳。
将游标作为 refcursor 打开并将 refcursor 变量存储在专用包中:不可能,因为 pl/sql 不允许在规范级别sys_refcursor变量
让
open_cur
过程返回 refcursor,将其存储在卸载器中,然后以某种方式将其传递给consume_cur
:看起来可行,但卸载器是 Java 中的,JDBC 不允许绑定SYS_REFCURSOR
参数。将
consume_cur
更改为流水线函数:本来可以工作,但 oracle 缓冲流水线行,这意味着它在逐行从中获取数据时会执行多次。也违反直觉。
到目前为止,我唯一的另一个想法是制作一个专用的包来存储所述游标,具有open
和close
过程,并get_cursor
返回 refcursor;然后从generator.consume_cur
调用get_cursor
。这将使专用包(不太可能更改(有状态,主包无状态。但是,这似乎是一个半生不熟的补丁,而不是问题解决方案。有没有更体面的方式来实现我需要的东西?也许完全更改逻辑而不会对性能和存储限制产生太大影响。
我有一个问题来理解你的问题。但我可以澄清你的想法。
-
将游标作为 refcursor 打开并将 refcursor 变量存储在 专用包:不可能,因为PL/SQL不允许sys_refcursor 规范级别的变量
dbms_sql
的解决方法。
create table test_rows as (select level rr from dual connect by level <= 100);
create or replace package cursor_ctx is
ctx_number integer;
end;
declare
p_cursor sys_refcursor;
begin
open p_cursor for 'select rr from test_rows';
cursor_ctx.ctx_number := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor);
end;
消耗的这部分是来自光标的数据。
declare
p_cursor sys_refcursor;
type l_number is table of number;
v_numbers l_number;
begin
if DBMS_SQL.IS_OPEN(cursor_ctx.ctx_number) then
p_cursor := DBMS_SQL.TO_REFCURSOR( cursor_ctx.ctx_number);
fetch p_cursor bulk collect into v_numbers limit 10;
if v_numbers.count < 10 then
dbms_output.put_line('No more data, close cursor');
close p_cursor;
cursor_ctx.ctx_number := null;
else
cursor_ctx.ctx_number := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor);
end if;
for i in nvl(v_numbers.first,1) .. nvl(v_numbers.last,-1) loop
dbms_output.put_line(v_numbers(i));
end loop;
else
dbms_output.put_line('Null or cursor close ');
end if;
end;
流水线函数将来可以将输入光标拆分为块。并行启用的流水线表函数
JDBC 允许使用 sys_refcursor 作为输出参数。
sys_refcursor = ResultSet
.