Lab 19.3 Collections of Records

[ Team LiB ]

Lab 19.3 Collections of Records Lab Objective After this Lab, you will be able to: Use Collections of Records

In the previous lab you have seen an example of the nested record where one of the record fields was defined as an index-by table. PL/SQL also gives you ability to define a collection of records (for example, an index-by table where its element type is a cursor-based record, as shown in the following example).

FOR EXAMPLE

DECLARE
   CURSOR name_cur IS
      SELECT first_name, last_name
        FROM student
       WHERE ROWNUM <= 4;

   TYPE name_type IS TABLE OF name_cur%ROWTYPE
      INDEX BY BINARY_INTEGER;

   name_tab name_type;
   v_counter INTEGER := 0;
BEGIN
   FOR name_rec IN name_cur LOOP
      v_counter := v_counter + 1;

      name_tab(v_counter).first_name := name_rec.first_name;
      name_tab(v_counter).last_name := name_rec.last_name;

      DBMS_OUTPUT.PUT_LINE('First Name('||v_counter||'): '||
         name_tab(v_counter).first_name);
      DBMS_OUTPUT.PUT_LINE('Last Name('||v_counter||'): '||
         name_tab(v_counter).last_name);
   END LOOP;
END;

In this declaration portion of the example, you define the name_cur cursor, which returns the first and last names of the first four students. Next, you define an index-by table type with its element type based on the cursor defined previously via the %ROWTYPE attribute. Then you define an index-by table variable and the counter that is used later to reference individual rows of the index-by table.

In the executable portion of the example, you populate the index-by table and display its records on the screen. Consider the notation used in the example when referencing individual elements of the index-by table:

name_tab(v_counter).first_name
and name_tab(v_counter).last_name

Notice that to reference each row of the index-by table, you use the counter variable just like in all previous examples. However, because each row of this table is a record, you must also reference individual fields of the underlying record.

This example produces the following output:

First Name(1): Fred
Last Name(1): Crocitto
First Name(2): J.
Last Name(2): Landry
First Name(3): Laetia
Last Name(3): Enison
First Name(4): Angel
Last Name(4): Moskowitz

PL/SQL procedure successfully completed.

[ Team LiB ]