具有动态查询和表名的批量收集/FORALL语句-Oracle PL/SQL

大家好，

我需要帮助优化此查询以使用批量收集和forall语句。我已经创建了备份表 (BCK_xxxx) 来复制原始表 (ORIG_xxx) 中的所有数据，但是我在将其转换为批量收集时遇到问题。我在BULK collect中看到的大多数示例都已经使用 % rowtype定义了表名称和结构。但是，我有几百个表要备份，所以我需要我的查询，特别是表名是动态的。这是我的原始查询，一个接一个地插入/删除数据，没有批量收集，需要很多时间:

DECLARE
--select all table names from backup tables (ex: BCK_tablename)
CURSOR cur_temp_tbl IS
    SELECT table_name
    FROM all_tables 
    WHERE OWNER = 'BCKUP'
    ORDER BY 1;  

--select all table names from original tables (ex: ORIG_tablename)
 CURSOR cur_original_tbl IS
    SELECT table_name
    FROM all_tables 
    WHERE OWNER = 'ORIG'
    ORDER BY 1;    
    l_tbl_nm VARCHAR2(30 CHAR);
l_inserted_cnt number(5) :=0;
l_deleted_cnt number(5) :=0;
BEGIN
    --first loop to delete all tables from backup
    FOR a IN cur_temp_tbl LOOP
       l_tbl_nm := a.table_name;                                    
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm;
       l_deleted_cnt :=  l_deleted_cnt +1;            
    END LOOP;

    --second loop to insert data from original to backup        
    FOR b IN cur_original_tbl LOOP            
         l_tbl_nm := b.table_name;   
        CASE
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_');
          ELSE
           l_tbl_nm := 'BCK_' || l_tbl_nm;
        END CASE;  

        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name;
        l_inserted_cnt :=  l_inserted_cnt +1;
    END LOOP; 

    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt);
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(SQLERRM);
 dbms_output.put_line(l_tbl_nm);
END;

-------------- 代码结束 -------------

我正在考虑将下面的代码添加到第二个循环中，但是我遇到了如何声明 'cur_tbl '光标和 'l_tbl_data' 表数据类型的问题。我无法使用rowtype，因为tablename应该是动态的，并且会在我的第二个循环的每次迭代中发生变化，这将列出原始表中的所有表名:

TYPE CurTblTyp  IS REF CURSOR;
cur_tbl    CurTblTyp; 
TYPE l_tbl_t IS TABLE OF tablename.%ROWTYPE;
l_tbl_data l_tbl_t ;

OPEN cur_tbl FOR  'SELECT * FROM  :s ' USING b.table_name;
FETCH cur_tbl BULK COLLECT INTO l_tbl_data LIMIT 5000;
EXIT WHEN cur_tbl%NOTFOUND;     
CLOSE cur_tbl;         

FORALL i IN 1 .. l_tbl_data .count
EXECUTE IMMEDIATE 'insert into '||l_tbl_nm||' values (:1)' USING l_tbl_data(i);

-------------- 代码结束 -------------

希望您能帮助我，并建议我如何使此代码更简单。非常感谢。

批量绑定旨在提高执行单行操作的代码的性能。但是您的代码没有这样做，它已经在执行insert-select，因此您无需担心转换为bulk bind。

我的第一个问题是-为什么不只是使用DataPump？对我来说似乎容易多了。

但是，如果要保留当前的方案，则可以使用一些简单的方法来提高性能:

1) 将删除更改为截断

立即执行 '删除自' | | l_tbl_nm;

变成

立即执行 “截断表” | | l_tbl_nm;

2) 直接模式插入

立即执行 '插入' | | l_tbl_nm | | '从' 中选择 * | | b.表名称;

变成

立即执行 '插入/* 追加 */到' | | l_tbl_nm | | '从' 中选择 * | | b.表名称;