Lab 19.2 Nested Records

Lab Objective

After this Lab, you will be able to:

Use Nested Records

As mentioned in the introduction to this chapter, PL/SQL allows you to define nested records. These are records that contain other records and collections. The record that contains a nested record or collection is called an enclosing record .

Consider the following code fragment.

FOR EXAMPLE

 DECLARE    TYPE name_type IS RECORD       (first_name VARCHAR2(15),        last_name VARCHAR2(30));    TYPE person_type IS       (  name name_type,  street VARCHAR2(50),        city VARCHAR2(25),        state VARCHAR2(2),        zip VARCHAR2(5));    person_rec person_type; 

This code fragment contains two user-defined record types. The second user -defined record type, person_type , is a nested record type because its field name is a record of the name_type type.

Next, consider the complete version of the preceding example.

FOR EXAMPLE

 DECLARE    TYPE name_type IS RECORD       (first_name VARCHAR2(15),        last_name VARCHAR2(30));    TYPE person_type IS RECORD       (name name_type,        street VARCHAR2(50),        city VARCHAR2(25),        state VARCHAR2(2),        zip VARCHAR2(5));    person_rec person_type; BEGIN    SELECT first_name, last_name, street_address, city, state, zip      INTO  person_rec.name.first_name, person_rec.name.last_name  , person_rec.street,           person_rec.city, person_rec.state, person_rec.zip      FROM student      JOIN zipcode USING (zip)     WHERE rownum < 2;    DBMS_OUTPUT.PUT_LINE ('Name:   '  person_rec.name.first_name' '   person_rec.name.last_name  );    DBMS_OUTPUT.PUT_LINE ('Street: '       person_rec.street);    DBMS_OUTPUT.PUT_LINE ('City:   '       person_rec.city);    DBMS_OUTPUT.PUT_LINE ('State:  '       person_rec.state);    DBMS_OUTPUT.PUT_LINE ('Zip:    '       person_rec.zip); END; 

In this example, the person_rec record is a user-defined nested record. As a result, in order to reference its field name that is a record with two fields, the following syntax is used:

  enclosing_record.(nested_record or   nested_collection).field_name  

In this case, the person_rec is enclosing record because it contains the name record as one of its fields while the name record is nested in the person_rec record.

This example produces the following output:

  Name:   James E. Norman   Street: PO Box 809 Curran Hwy   City:   North Adams   State:  MA   Zip:    01247   PL/SQL procedure successfully completed.