Lab 19.2 Nested Records

[ Team LiB ]

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.

[ Team LiB ]