如何在Pl/SQl中使用批量收集和插入 [英] How do I use bulk collect and insert in Pl/SQl

问题描述

我想从一个表中获取大约600万行，并将它们全部插入到另一个表中. 如何使用BULK COLLECT和FORALL?

I want to fetch around 6 millions rows from one table and insert them all into another table. How do I do it using BULK COLLECT and FORALL ?

推荐答案

declare
  -- define array type of the new table
  TYPE new_table_array_type IS TABLE OF NEW_TABLE%ROWTYPE INDEX BY BINARY_INTEGER;

  -- define array object of new table
  new_table_array_object new_table_array_type;

  -- fetch size on  bulk operation, scale the value to tweak
  -- performance optimization over IO and memory usage
  fetch_size NUMBER := 5000;

  -- define select statment of old table
  -- select desiered columns of OLD_TABLE to be filled in NEW_TABLE
  CURSOR old_table_cursor IS
    select * from OLD_TABLE; 

BEGIN

  OPEN old_table_cursor;
  loop
    -- bulk fetch(read) operation
    FETCH old_table_cursor BULK COLLECT
      INTO new_table_array_object LIMIT fetch_size;
    EXIT WHEN old_table_cursor%NOTFOUND;

    -- do your business logic here (if any)
    -- FOR i IN 1 .. new_table_array_object.COUNT  LOOP
    --   new_table_array_object(i).some_column := 'HELLO PLSQL';    
    -- END LOOP;    

    -- bulk Insert operation
    FORALL i IN INDICES OF new_table_array_object SAVE EXCEPTIONS
      INSERT INTO NEW_TABLE VALUES new_table_array_object(i);
    COMMIT;

  END LOOP;
  CLOSE old_table_cursor;
End;

希望这会有所帮助.

这篇关于如何在Pl/SQl中使用批量收集和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！