在PL/SQL的FORALL循环中插入

Question

是否可以在pl/sql中使用FORALL进行批量插入?

TYPE c_type1 IS RECORD
(
column1 table1.column1%TYPE,
column2 table1.column2%TYPE,
client table2.client%TYPE
);
type1 c_type1;
CURSOR cur_t IS  select * BULK COLLECT INTO recs from table3 ;
begin
  FOR recs IN cur_t
  LOOP
  SELECT * INTO type1 FROM  (select a.column1, a.column2,imm.client 
  ...
    from table1 a, table2 imm        
   WHERE 
    a.column1 = recs.column1
  ) WHERE ROWNUM=1;
  INSERT INTO table2 values (recs.column1,type1.column2);
  ...

p。S:还有80多列要插入。

Answer 1

你的问题不是很清楚，但看看你的代码，我有以下内容。检查一下这是不是你要找的。

declare
CURSOR cur_t IS  
                select t3.column1 , t1.column2
                from table3 t3
                inner join table1 t1
                on t3.column1 = t1.column1;
type var_cur is table of cur_t%rowtype;
var var_cur;
begin
open cur_t;
  LOOP
    FETCH cur_t bulk collect into var limit 100;       
     EXIT WHEN cur_t%NOTFOUND;
     FORALL i IN 1 .. var.count SAVE EXCEPTIONS
      INSERT INTO TABLE2 
     VALUES var(i);         
    END LOOP;
  CLOSE distinctUserIdCursor;
COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error in Insertion of record' || '~~~~' || SQLERRM);
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX|| ': '
               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
      END LOOP;
end;