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


 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.  

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: