oracle - 批量收集动态sql

Question

我必须编写一个动态 sql 游标，其中有几种可能会生成选择查询。因此我选择动态，我使用 DBMS_SQL 包动态创建游标并动态获取数据。

但是，结果集将是巨大的。大约 11GB(有 240 万条记录，并且 select 语句将大约 80 列长，假设每列大约 50Byte varchar)

因此我无法立即打开光标。我想知道是否有一个功能，我可以从 curosr 中获取数据，同时保持 curosr 为 1000 条记录的 block 打开(我将不得不动态地执行此操作)

请找到附加的代码，它只获取并打印列的值(一个示例案例)我想在这里使用 bul collect\

谢谢

---------------code sample--------------------------------------
--create or replace type TY_DIMDEAL AS TABLE OF VARCHAR2(50) ;
create or replace procedure         TEST_PROC (po_recordset out sys_refcursor)
as


  v_col_cnt   INTEGER;
  v_ind       NUMBER;
  rec_tab     DBMS_SQL.desc_tab;
  v_cursor    NUMBER;
  lvar_output number:=0;
  lvar_output1 varchar2(100);
  lvar_output3 varchar2(100);
  lvar_output2 varchar2(100);
  LVAR_TY_DIMDEAL TY_DIMDEAL;
 lvarcol varchar2(100);
begin
  --
  LVAR_TY_DIMDEAL := TY_DIMDEAL();
  lvar_output1 := '';

  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, 'select to_char(Field1) , to_char(fiel2) , to_char(field3) from table,table2 ', dbms_sql.native);
  dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
  FOR v_pos in 1..rec_tab.LAST LOOP

  LVAR_TY_DIMDEAL.EXTEND();
  DBMS_SQL.define_column( v_cursor, v_pos ,LVAR_TY_DIMDEAL(v_pos),20);
  END LOOP;
 -- DBMS_SQL.define_column( v_cursor, 1 ,lvar_output1,20);
  --DBMS_SQL.define_column( v_cursor, 2 ,lvar_output2,20);
 --DBMS_SQL.define_column( v_cursor, 3 ,lvar_output3,20);
  v_ind := dbms_sql.execute( v_cursor );

  LOOP
    v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
    EXIT WHEN v_ind = 0;
    lvar_output := lvar_output+1;
   dbms_output.put_line ('row number '||lvar_output)  ;

    FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP  
    LVAR_TY_DIMDEAL(v_col_seq):= '';
     DBMS_SQL.COLUMN_VALUE( v_cursor, v_col_seq,LVAR_TY_DIMDEAL(v_col_seq));
    dbms_output.put_line (LVAR_TY_DIMDEAL(v_col_seq));

   END LOOP;



  END LOOP;

end TEST_PROC;

Answer 1

在保持游标打开的同时，以合理大小的 block 从游标中获取数据是 PL/SQL Best Practices 之一.

上述文档(参见 Code 38 item)概述了一种方法，用于在运行时才知道选择列表的情况。基本上:

定义一个适当的类型来获取结果。假设所有返回的列的类型为 VARCHAR2 :

-- inside DECLARE
Ty_FetchResults IS TABLE OF DBMS_SQL.VARCHAR2_TABLE;
lvar_results Ty_FetchResults;

每次调用 DBMS_SQL.FETCH_ROWS 之前, 调用 DBMS_SQL.DEFINE_ARRAY 启用批量获取。

调用 DBMS_SQL.FETCH_ROWS从游标中获取 1000 行。

调用 DBMS_SQL.COLUMN_VALUE将获取的数据复制到结果数组中。

在 FOR 中逐记录处理结果环形。不用担心获取的记录数:如果有记录要处理，FOR循环将正确运行；如果结果数组为空，FOR循环不会运行。

当获取的记录数小于预期大小时退出循环。

记得到 DBMS_SQL.CLOSE 光标。

您的循环体应如下所示:

LOOP
  FOR j IN 1..v_col_cnt LOOP
    DBMS_SQL.DEFINE_ARRAY(v_cursor, j, lvar_results(j), 1000, 1);
  END LOOP;

  v_ind := DBMS_SQL.FETCH_ROWS(v_cursor);

  FOR j IN 1..v_col_cnt LOOP
    lvar_results(j).DELETE;
    DBMS_SQL.COLUMN_VALUE(v_cursor, j, lvar_results(j));
  END LOOP;

  -- process the results, record by record
  FOR i IN 1..lvar_results(1).COUNT LOOP
    -- process a single record...
    -- your logic goes here
  END LOOP;

  EXIT WHEN lvar_results(1).COUNT < 1000;
END LOOP;

-- don't forget: DBMS_CLOSE(v_cursor);

另见 Doing SQL from PL/SQL: Best and Worst Practices .