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).
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.