Oracle PL/SQL Bulk Collect

--> PL/SQL Normal Insert and Bulk Insert

CREATE TABLE XXAA_INSERT_TEST (

  id           NUMBER(10),

  code         VARCHAR2(10),

  description  VARCHAR2(50));

ALTER TABLE XXAA_INSERT_TEST ADD (CONSTRAINT XXAA_INSERT_TEST_pk PRIMARY KEY (id));

ALTER TABLE XXAA_INSERT_TEST ADD (CONSTRAINT XXAA_INSERT_TEST_uk UNIQUE (code));

SET SERVEROUTPUT ON

DECLARE

  TYPE t_XXAA_INSERT_TEST_tab IS TABLE OF XXAA_INSERT_TEST%ROWTYPE;

  l_tab    t_XXAA_INSERT_TEST_tab := t_XXAA_INSERT_TEST_tab();

  l_start  NUMBER;

  l_size   NUMBER            := 10000;

BEGIN

  -- Populate collection.

  FOR i IN 1 .. l_size

  LOOP

    l_tab.extend;

    l_tab(l_tab.last).id          := i;

    l_tab(l_tab.last).code        := TO_CHAR(i);

    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);

  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time regular inserts.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP

    INSERT INTO XXAA_INSERT_TEST (id, code, description)

    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);

  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start));

  EXECUTE IMMEDIATE 'TRUNCATE TABLE XXAA_INSERT_TEST';

  -- Time bulk inserts. 

  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last

    INSERT INTO XXAA_INSERT_TEST VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || (DBMS_UTILITY.get_time - l_start));

  COMMIT;

END;