如何在 PLSQL 中存储立即执行的结果

Question

我有一个表，其中包含选择查询并插入存储为varchar的查询。我必须执行选择查询，并使用插入查询插入过程插入选择查询的结果。现在，我正在执行立即并将选择查询批量收集到一个 varchar 表中。

在此之后，我转到变量的每一行并获取值并将其放入插入查询中。 我有许多表的选择和插入查询对。所以这必须动态地完成。我的问题是，有没有更好的方法来存储选择查询的结果？除了使用瓦尔查尔表？因为选择查询的结果集可能有数百万条记录，这可能会导致问题。使用嵌套表类型并在其上使用扩展可以解决问题吗？

PROCEDURE SEL_INS
AS
  CURSOR C
  IS
    SELECT 
      SELEQRY SELQRY,
      INSQUERY INSERTQRY,
      cols COLS
      FROM TAB1;
      selqry  VARCHAR2(1000);
      insqry VARCHAR2(1000);
      tab1 vartable:=vartable();
      cols   NUMBER;
 BEGIN
   tab1:=vartable(NULL);
     FOR X    IN C
      LOOP
          selqry:= X.SELQRY;
          cols:=X.COLS;
  EXECUTE immediate selqry bulk collect INTO tab1;
 -- select statement is concatenated before executing. so that each index has one record    
 --with values separated by commas 
   --- a sample column in tab1 will have values like (abc,abc1,abc2)
     FOR i IN 1..tab1.count
     LOOP
       insqry :=X.INSERTQRY; 
       --- insert query will have values like insert into tab2   values('abc,'abc1','abc2')
         EXECUTE immediate insqry;
     END LOOP;
   END LOOP;

  END SEL_INS;

vartable 是 varchars2（4000） 类型的表

Answer 1

如注释中所述，您应该尝试将语句重写为 INSERT INTO ... SELECT ... .让我们假设无论出于何种原因这是不可能的。在这种情况下，您可以使用如下过程：

PROCEDURE SEL_INS AS
    CURSOR C IS
    SELECT SELEQRY, INSQUERY, COLS
    FROM TAB1;
    selqry  VARCHAR2(1000);
    insqry VARCHAR2(1000);
    tab1 vartable;
    cols   NUMBER;
    cur INTEGER;
    res INTEGER;
    col_cnt INTEGER;
    desctab DBMS_SQL.DESC_TAB;
    i INTEGER;
BEGIN
    FOR aQuery IN C LOOP
        tab1 := vartable();
        cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, aQuery.SELEQRY, DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, desctab)
        FOR i IN 1..col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN(cur, i, desctab(i).COL_NAME, 2000);
        END LOOP;
        res := DBMS_SQL.EXECUTE_AND_FETCH(cur, TRUE);
        FOR i IN 1..col_cnt LOOP
            tab1.EXTEND;
            DBMS_SQL.COLUMN_VALUE(cur, i, tab1(tab1.LAST));
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(cur);
        -- ... do whatever with tab1(xyz) -> otherwise this procedure would be an overkill
        cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, aQuery.INSQUERY, DBMS_SQL.NATIVE);
        i := tab1.FIRST;
        WHILE i IS NOT NULL LOOP
            DBMS_SQL.BIND_VARIABLE(cur, ':b'||i, tab1(i));
            i := tab1.NEXT(i);
        END LOOP;
        res := DBMS_SQL.EXECUTE(cur);
        DBMS_SQL.CLOSE_CURSOR(cur);
    END LOOP;   
END;

请注意，此过程假定所有列都是VARCHAR2数据类型（最大长度为 2000 个字符）。如果您有其他数据类型，则必须像IF desctab(c).col_type = 1 THEN ...一样扩展行DBMS_SQL.DEFINE_COLUMN(cur, i, desctab(i).COL_NAME, 2000);

另外，请注意，除非您的选择只返回一行，否则DBMS_SQL.EXECUTE_AND_FETCH将失败。如果您的查询可能只返回一行，则必须使用

DBMS_SQL.EXECUTE(cur);
WHILE (DBMS_SQL.FETCH_ROWS(cur) > 0) LOOP
   ...
END LOOP;

请参阅 Oracle 内置数据类型以获取每种数据类型的代码号。

Answer 2

假设您能够更改存储在存储要运行的 sql 语句的表中的数据，以下是我获取存储数据的示例：

INSERT INTO sel_ins (selqry, insquery)
SELECT 'select col1, col2, col3 from table1' selqry, 'insert into other_table1 (col1, col2, col3)') insqry FROM dual
UNION ALL
SELECT 'select col1, col2 from table2' selqry, 'insert into other_table2 (col1, col2)') insqry FROM dual
UNION ALL
SELECT 'select col1, col2, col3 from table3 where col4 = ''fred''' selqry, 'insert into other_table3 (col1, col2, col3)') insqry FROM dual;

通过这样做，您进行插入的过程现在要简单得多：

PROCEDURE sel_ins IS
  CURSOR ins_sel_cur IS
    SELECT seleqry selqry, insquery insqry, cols cols
    FROM   tab1;
  v_selqry tab1.seleqry%TYPE;
  v_insqry tab1.insquery%TYPE;
BEGIN
  FOR ins_sel_rec IN ins_sel_cur
  LOOP
    EXECUTE IMMEDIATE ins_sel_rec.v_insqry || CHR(10) || ins_sel_rec.v_selqry;
  END LOOP;
END;
/

这样，您就不会从表中检索大量数据并将其存储在内存中，只是获取该数据并将其逐行添加回另一个表中 - 您在单个 DML 语句中完成所有工作（相当于建筑商获得包含她需要交付的所有砖块的卡车，以直接运送到她正在建造墙壁的点， 而不是在驱动器的底部，然后逐个去取每块砖）。这应该使事情变得更快，更不用说更容易阅读、维护等了。

如果您希望单独存储列，例如：

INSERT INTO sel_ins (selqry, insquery, cols)
SELECT 'select <COLS> from table1' selqry, 'insert into other_table1 (<COLS>)') insqry, 'col1, col2, col3' cols FROM dual
UNION ALL
SELECT 'select <COLS> from table2' selqry, 'insert into other_table2 (<COLS>)') insqry, 'col1, col2' cols FROM dual
UNION ALL
SELECT 'select <COLS> from table3 where col4 = ''fred''' selqry, 'insert into other_table3 (<COLS>)') insqry, 'col1, col2, col3' cols FROM dual;

然后你的执行立即变成：

execute immediate replace(ins_sel_rec.v_insqry, '<COLS>', ins_sel_rec.cols) ||
                  chr(10) ||
                  replace(ins_sel_rec.v_selqry, '<COLS>', ins_sel_rec.cols);