代码之家 › 专栏 › 技术社区 › Izbassar Tolegen

按块插入

plsql oracle

Izbassar Tolegen · 技术社区 · 6 年前

我想用 insert into 语句,但获取错误:

UNDO 表空间不是一个选项,所以我想要一种按块插入这些数据的方法(例如一次插入一百万行)。有人能帮忙把这个程序改写成那样吗?

CREATE OR REPLACE PROCEDURE create_chunks (
  p_source_table IN VARCHAR2,
  p_table_name_chunk IN VARCHAR2,
  p_chunks IN VARCHAR2
) AS
v_insert_sql CLOB;
BEGIN
  v_insert_sql := 'INSERT INTO ' || p_table_name_chunk ||
    ' (rid, chunk_number) ' ||
    'SELECT /*+ parallel(64) */ rowid rid,' ||
      'mod( ora_hash(rowid), :p_chunks ) as chunk_number '
    'FROM ' || p_source_table;
   EXECUTE IMMEDIATE v_insert_sql USING p_chunks;
   COMMIT;
END;

v_insert_sql 由于上述错误而失败。我有一个工作解决方案,使用如下的游标抓取:

DECLARE
  CURSOR v_cur IS SELECT /*+ parallel(64) */
    rowid rid, mod( ora_hash(rowid), 20000 ) AS chunk_number
  -- I need this table to be parametric name
  FROM some_table;
  TYPE t_sample IS TABLE OF v_cur%ROWTYPE;
  v_sample t_sample;
  v_row_limit CONSTANT NUMBER := 1000000;
BEGIN
  OPEN v_cur;
  LOOP
    FETCH v_cur BULK COLLECT INTO v_sample LIMIT v_row_limit;
    FORALL i IN v_sample.first .. v_sample.last
      INSERT INTO chunk_table VALUES v_sample(i);
    COMMIT;
    EXIT WHEN v_cur%NOTFOUND;
  END LOOP;
  CLOSE v_cur;
END;

我不能直接将光标移动到过程中,因为表名是不同的,我需要它是参数化的,因为使用光标方法时,我必须为不同的表重复相同的代码。所以问题是如何处理这个问题?

1 回复 | 直到 6 年前

Izbassar Tolegen 6 年前

基本上,我可以通过动态查询的方式在过程中使用整个块方法,如下所示:

CREATE OR REPLACE PROCEDURE create_chunks (
  p_source_table IN VARCHAR2,
  p_table_name_chunk IN VARCHAR2,
  p_chunks IN VARCHAR2
) AS
  v_insert_sql CLOB;
BEGIN
  v_insert_sql := '' ||
    ' DECLARE ' || CHR(10) ||
    ' CURSOR cur1 IS SELECT ' || CHR(10) ||
    '   /*+ parallel(64) full(tbn)*/ rowid rid,' || CHR(10) ||
    '   mod( ora_hash(rowid), :p_chunks ) AS chunk_number' || CHR(10) ||
    ' FROM ' || p_source_table || ' tbn;' || CHR(10) ||
    ' TYPE t_sample IS TABLE OF cur1%ROWTYPE;' || CHR(10) ||
    ' v_sample t_sample;' || CHR(10) ||
    ' v_row_limit CONSTANT NUMBER := 1000000;' || CHR(10) ||
    ' BEGIN' || CHR(10) ||
    '   OPEN cur1;' || CHR(10) ||
    '   LOOP' || CHR(10) ||
    '     FETCH cur1 BULK COLLECT INTO v_sample LIMIT v_row_limit;' || CHR(10) ||
    '     FORALL i IN v_sample.first .. v_sample.last' || CHR(10) ||
    '       INSERT INTO ' || p_table_name_chunk || ' VALUES v_sample(i);' || CHR(10) ||
    '     COMMIT;' || CHR(10) ||
    '     EXIT WHEN cur1%NOTFOUND;' || CHR(10) ||
    '   END LOOP;' || CHR(10) ||
    '   CLOSE cur1;' || CHR(10) ||
    ' END;';
  EXECUTE IMMEDIATE v_insert_sql USING p_chunks;
  COMMIT;
END;