批量insert示例

DROP TABLE test1 PURGE
/
DROP TABLE test2 PURGE
/
CREATE TABLE test1 AS SELECT * FROM Dba_Objects
/
CREATE TABLE test2 AS SELECT * FROM test1 WHERE 1=2
/
DECLARE
  v_limit           INT := 10000;
  v_idx             INT := 0;
  bl_notfound       BOOLEAN;
  v_object_id       NUMBER;
  v_object_name     VARCHAR2(30);
  object_id_table   dbms_sql.number_table;
  object_name_table dbms_sql.varchar2_table;
  CURSOR cur_test1 IS
    SELECT object_id, object_name FROM test1;
BEGIN
  DELETE FROM test2;
  COMMIT;
  OPEN cur_test1;
  LOOP
    FETCH cur_test1
      INTO v_object_id, v_object_name;
    bl_notfound := cur_test1%NOTFOUND;
    /*分条件赋值*/
    IF MOD(v_object_id, 7) = 0 THEN
      v_idx := v_idx + 1;
      object_id_table(v_idx) := v_object_id;
      object_name_table(v_idx) := v_object_name;
    END IF;
    /*到达限定条数就insert*/
    IF bl_notfound OR v_idx >= v_limit THEN
      v_idx := 0;
      FORALL i IN 1 .. object_id_table.count
        INSERT INTO test2
          (object_id, object_name)
        VALUES
          (object_id_table(i), object_name_table(i));
      COMMIT;
      object_name_table.delete;
      object_id_table.delete;
      IF bl_notfound THEN
        EXIT;
      END IF;
    END IF;
  END LOOP;
END;
/