Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 19.3 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.