Oracle PL/SQL Tutorial/Collections/Table of Record

An anonymous PL/SQL procedure to demonstrate the use of PL/SQL records

SQL>
SQL> create table employee (
  2  id                             number                         not null,
  3  employee_type_id               number                         not null,
  4  external_id                    varchar2(30)                   not null,
  5  first_name                     varchar2(30)                   not null,
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30)                   not null,
  8  name                           varchar2(100)                  not null,
  9  birth_date                     date                           not null,
 10  gender_id                      number                         not null );
Table created.
SQL>
SQL> declare
  2      TYPE name_record is record (
  3      first_name                            employee.first_name%TYPE,
  4      middle_name                           employee.middle_name%TYPE,
  5      last_name                             employee.last_name%TYPE );
  6      TYPE name_table is table of name_record index by binary_integer;
  7      t_name                                name_table;
  8  begin
  9   t_name(1).first_name  := "JOHN";
 10   t_name(1).last_name   := "DOE";
 11   t_name(2).first_name  := "JANE";
 12   t_name(2).last_name   := "DOE";
 13
 14   dbms_output.put_line(t_name(1).last_name||", "||t_name(1).first_name);
 15   dbms_output.put_line(t_name(2).last_name||", "||t_name(2).first_name);
 16  end;
 17  /
DOE, JOHN
DOE, JANE
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>

Bulk DML with table of records

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>

Defining an Index-by table of records

SQL> DECLARE
  2    TYPE hrc_company_rec IS RECORD
  3      (hrc_company_id NUMBER,
  4       product_description VARCHAR2(20),
  5       company_short_name VARCHAR2(30));
  6
  7    TYPE hrc_company IS TABLE OF hrc_company_rec INDEX BY BINARY_INTEGER;
  8
  9    v_example_tab hrc_company;
 10  BEGIN
 11    /*Do some processing */
 12    null;
 13  END;
 14  /
PL/SQL procedure successfully completed.

Table collection of records

SQL>
SQL> CREATE OR REPLACE PACKAGE process_vacations IS
  2     TYPE pv_vacation_rec IS RECORD
  3        (pv_vacationing_user_txt    VARCHAR2(30),
  4         pv_start_date              VARCHAR2(10),
  5         pv_end_date                VARCHAR2(10),
  6         pv_redirect_interviews_txt VARCHAR2(30),
  7         pv_redirect_reviews_txt    VARCHAR2(30));
  8     TYPE pv_type_vacation_tab IS TABLE OF pv_vacation_rec
  9        INDEX BY BINARY_INTEGER;
 10     PROCEDURE process_schedules (p_vac_tab IN pv_type_vacation_tab);
 11  END process_vacations;
 12  /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY process_vacations IS
  2
  3      PROCEDURE process_schedules (p_vac_tab IN pv_type_vacation_tab) IS
  4      BEGIN
  5         FOR lv_loop_counter_num IN 1..p_vac_tab.COUNT LOOP
  6            NULL;
  7         END LOOP;
  8      END process_schedules;
  9  END process_vacations;
 10  /
Package body created.
SQL>