首页 文章

带有动态查询和表名的BULK COLLECT / FORALL语句 - Oracle PL / SQL

提问于
浏览 次
0

我需要帮助优化此查询以使用批量收集和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，因为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);

希望你能帮助我，并建议我如何使这段代码更简单 . 非常感谢 .

sql oracle plsql bulkinsert forall

3 回答

  • 3

    看起来您要从现有备份表中删除所有行，然后将原始表中的所有内容重新复制到备份表 . 如果这是正确的，使用 DELETE 进行删除，并且插入的任何循环操作都会很慢 .

    首先，要删除数据，请使用 TRUNCATE . 由于您要重新填充，请使用 REUSE STORAGE 选项 . 这是从表中删除所有行的最有效方法 .

    TRUNCATE TABLE <backup table> REUSE STORAGE;

    第二，重新填充，只需 INSERT 与 SELECT .

    INSERT INTO <backup table> SELECT * FROM <orig table>;

    您可以在循环中使用这些循环 . 无需在表行中进行游标，因为这样会更快 .

    如果你有一张新 table ，你可以用CTAS做类似的事情......

    CREATE TABLE <backup table> AS SELECT * FROM <orig_table>;
    回复于
  • 0

    除了删除和截断选项之外还有第3个选项：重命名/删除 . 您重命名旧备份表，重新创建新备份（CTAS） . 如果create-insert成功，则删除重命名的表，如果新备份失败，则将先前的旧备份重命名回初始备份名称 . 您基本上会为重做日志交换磁盘空间的临时使用 .

    您不需要批量处理，CTAS仍然比批量处理更快 .

    回复于
  • -1

    你用过FORCE DELETE吗？它首先由Oracle Master J.B.E引入，它用于删除数据并忽略表可能具有的约束，并且比其他删除语句快得多 .

    FORCE DELETE FROM <table_name>;
    回复于

相关问题