Bulk DML with table of records : Table of Record « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Table of Record

SQL>
SQL>
SQL> CREATE TABLE customer_region(
  2     region_id    NUMBER(4)    PRIMARY KEY,
  3     region_name  VARCHAR2(11) NOT NULL
  4  );

Table created.

SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,'REGION1');

1 row created.

SQL> INSERT INTO customer_region VALUES (2,'REGION2');

1 row created.

SQL> INSERT INTO customer_region VALUES (3,'REGION3');

1 row created.

SQL> INSERT INTO customer_region VALUES (4,'REGION4');

1 row created.

SQL>
SQL>
SQL>
SQL> DECLARE
  2    Type regionRecord IS Record(region_id NUMBER(4),region_name VARCHAR2(10));
  3    Type region_tbl IS TABLE of regionRecord INDEX BY BINARY_INTEGER;
  4    regionRecords region_tbl;
  5    returnCode NUMBER;
  6    Ret_errorMessage VARCHAR2(1000);
  7    Procedure load_regions (regionRecords IN region_tbl,
  8           returnCode OUT NUMBER,
  9           errorMessage OUT VARCHAR2)
 10    Is
 11    BEGIN
 12
 13      DELETE FROM customer_region;
 14
 15      FOR i in regionRecords.FIRST..regionRecords.LAST LOOP
 16        INSERT INTO customer_region
 17        values (regionRecords(i).region_id,regionRecords(i).region_name);
 18      END LOOP;
 19
 20      COMMIT;
 21
 22    EXCEPTION WHEN OTHERS THEN
 23
 24      returnCode :=SQLCODE;
 25
 26      errorMessage :=SQLERRM;
 27
 28    END;
 29  BEGIN
 30    FOR i IN 1..5 LOOP
 31      regionRecords(i).region_id :=i;
 32      regionRecords(i).region_name :='REGION'||i;
 33    END LOOP;
 34    Load_regions(regionRecords,returnCode,ret_errorMessage);
 35  EXCEPTION WHEN OTHERS THEN
 36    RAISE_APPLICATION_ERROR(-20111,SQLERRM);
 37  END;
 38  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table customer_region;

Table dropped.

SQL>
SQL>