我需要帮助来优化此查询以使用批量收集和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);
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"TABLE 数据类型时遇到了问题。我无法使用rowtype，因为表名应该是动态的，并且在第二个循环的每次迭代中都会发生变化，该循环将列出原始表中的所有表名：
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);
希望你能帮助我，并建议我如何使这段代码更简单。多谢。
您似乎希望从现有备份表中删除所有行，然后将原始表中的全部内容重新复制到备份表。 如果这是正确的，则使用DELETE进行删除和插入的任何循环操作都会很慢。
首先，要删除数据，请使用TRUNCATE。 由于您要重新填充，请使用REUSE STORAGE选项。 这是从表中删除所有行的最有效方法。
TRUNCATE TABLE <backup table> REUSE STORAGE;
其次，要重新填充，只需INSERTSELECT.
INSERT INTO <backup table> SELECT * FROM <orig table>;
您可以在循环中按表循环使用这些内容。 无需在表行中光标，因为这会更快。
如果您有新表，则可以使用 CTAS 执行类似操作...
CREATE TABLE <backup table> AS SELECT * FROM <orig_table>;
除了删除和截断选项之外，还有第三个选项：即重命名/删除。重命名旧的备份表，重新创建新的备份 (CTAS)。如果创建 - 插入成功，则删除重命名的表，如果新备份失败，则将以前的旧备份重命名回初始备份名称。 您基本上将磁盘空间的临时使用换成了重做日志。
您不需要批量处理，CTAS 仍然比批量处理更快。
你用过强制删除吗？ 它首先由甲骨文大师J.B.E引入 它用于删除数据并忽略表可能具有的约束，并且比其他 DELETE 语句快得多。
FORCE DELETE FROM <table_name>;