Oracle PL/SQL Tutorial/Collections/Table of Record

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

   <source lang="sql">

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></source>

Bulk DML with table of records

   <source lang="sql">

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></source>

Defining an Index-by table of records

   <source lang="sql">

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.</source>

Table collection of records

   <source lang="sql">

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></source>