Lab 19.2 Nested Records

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 19.  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.

graphics/intfig03.gif 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.

graphics/intfig03.gif 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. 

Lab 19.2 Exercises

19.2.1 Use Nested Records

In this exercise, you will learn more about nested records.

Create the following PL/SQL script:

 -- ch19_3a.sql, version 1.0  SET SERVEROUTPUT ON  DECLARE     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     TYPE zip_info_type IS RECORD        (zip VARCHAR2(5),        last_name_tab last_name_type);     CURSOR name_cur (p_zip VARCHAR2) IS        SELECT last_name          FROM student         WHERE zip = p_zip;     zip_info_rec zip_info_type;     v_zip VARCHAR2(5) := '&sv_zip';     v_counter INTEGER := 0;  BEGIN     zip_info_rec.zip := v_zip;     FOR name_rec IN name_cur (v_zip) LOOP        v_counter := v_counter + 1;        zip_info_rec.last_name_tab(v_counter) :=           name_rec.last_name;     END LOOP;  END; 

Answer the following questions:

a)

Explain the script ch19_3a.sql.

b)

Modify the script so that zip_info_rec data is displayed on the screen. Make sure that a value of the zipcode is displayed only once. Provide the value of '11368' when running the script.

c)

Modify the script created in the previous exercise (ch19_3b.sql). Instead of providing a value for a zipcode at runtime, populate via the cursor FOR loop. The SELECT statement associated with the new cursor should return zipcodes that have more than one student in them.

Lab 19.2 Exercise Answers

This section gives you some suggested answers to the questions in Lab 19.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

19.2.1 Answers

a)

Explain the script ch19_3a.sql.

A1:

Answer: The declaration portion of the script contains index-by table type, last_name_type, record type, zip_info_type, and nested-user-defined record, zip_info_rec, declarations. The field, last_name_tab, of the zip_info_rec is an index-by table that is populated with the help of the cursor, NAME_CUR. In addition, the declaration portion also contains two variables, v_zip and v_counter. The variable v_zip is used to store incoming value of the zipcode provided at runtime. The variable v_counter is used to populate the index-by table, last_name_tab. The executable portion of the script assigns values to the individual record fields, zip and last_name_tab. As mentioned previously, the last_name_tab is an index-by table, and it is populated via cursor FOR loop.

b)

Modify the script so that zip_info_rec data is displayed on the screen. Make sure that a value of the zipcode is displayed only once. Provide the value of '11368' when running the script.

A2:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch19_3b.sql, version 2.0  SET SERVEROUTPUT ON  DECLARE     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     TYPE zip_info_type IS RECORD        (zip VARCHAR2(5),        last_name_tab last_name_type);     CURSOR name_cur (p_zip VARCHAR2) IS        SELECT last_name          FROM student         WHERE zip = p_zip;     zip_info_rec zip_info_type;     v_zip VARCHAR2(5) := '&sv_zip';     v_counter INTEGER := 0;  BEGIN     zip_info_rec.zip := v_zip;     DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);     FOR name_rec IN name_cur (v_zip) LOOP        v_counter := v_counter + 1;        zip_info_rec.last_name_tab(v_counter) :=           name_rec.last_name;        DBMS_OUTPUT.PUT_LINE ('Names('||v_counter||'): '||           zip_info_rec.last_name_tab(v_counter));     END LOOP;  END; 

In order to display the value of the zipcode only once, the DBMS_OUTPUT. PUT_LINE statement

 DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip); 

is placed outside the loop.

When run, this script produces the following output:

 Enter value for sv_zip: 11368  old  15:    v_zip VARCHAR2(5) := '&sv_zip';  new  15:    v_zip VARCHAR2(5) := '11368';  Zip: 11368  Names(1): Lasseter  Names(2): Miller  Names(3): Boyd  Names(4): Griffen  Names(5): Hutheesing  Names(6): Chatman  PL/SQL procedure successfully completed. 
c)

Modify the script created in the previous exercise (ch19_3b.sql). Instead of providing a value for a zipcode at runtime, populate via the cursor FOR loop. The SELECT statement associated with the new cursor should return zipcodes that have more than one student in them.

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters.

 -- ch19_3c.sql, version 3.0  SET SERVEROUTPUT ON SIZE 20000  DECLARE     TYPE last_name_type IS TABLE OF student.last_name%TYPE        INDEX BY BINARY_INTEGER;     TYPE zip_info_type IS RECORD        (zip VARCHAR2(5),        last_name_tab last_name_type);     CURSOR zip_cur IS        SELECT zip, COUNT(*)          FROM student        GROUP BY zip        HAVING COUNT(*) > 1;     CURSOR name_cur (p_zip VARCHAR2) IS        SELECT last_name          FROM student         WHERE zip = p_zip;     zip_info_rec zip_info_type;     v_counter INTEGER;  BEGIN     FOR zip_rec IN zip_cur LOOP        zip_info_rec.zip := zip_rec.zip;        DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);        v_counter := 0;        FOR name_rec IN name_cur (zip_info_rec.zip) LOOP           v_counter := v_counter + 1;           zip_info_rec.last_name_tab(v_counter) :=              name_rec.last_name;           DBMS_OUTPUT.PUT_LINE ('Names('||v_counter||'): '||              zip_info_rec.last_name_tab(v_counter));        END LOOP;        DBMS_OUTPUT.PUT_LINE ('----------');     END LOOP;  END; 

In the preceding script, you declared a new cursor called zip_cur. This cursor returns zipcodes that have more than one student in them. Next, in the body of the script, you use nested cursors to populate the last_name_tab index-by table for each value of zipcode. First, the outer cursor FOR loop populates the zip field of the zip_info_rec and displays its value on the screen. Then it passes the zip field as a parameter to the inner cursor FOR loop that populates last_name_tab table with last names of corresponding students.

Consider the partial output of the preceding example:

 Zip: 06820  Names(1): Scrittorale  Names(2): Padel  Names(3): Kiraly  -------------------- Zip: 06830  Names(1): Dennis  Names(2): Meshaj  Names(3): Dalvi  -------------------- Zip: 06880  Names(1): Miller  Names(2): Cheevens  -------------------- Zip: 06903  Names(1): Segall  Names(2): Annina  -------------------- Zip: 07003  Names(1): Wicelinski  Names(2): Intal  -------------------- Zip: 07010  Names(1): Lopez  Names(2): Mulroy  Names(3): Velasco  Names(4): Kelly  Names(5): Tucker  Names(6): Mithane  --------------------   PL/SQL procedure successfully completed. 

Lab 19.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 19.2.

1)

A record is called a nested record if it contains

  1. _____ other records.

  2. _____ collections.

  3. _____ all of the above

  4. _____ none of the above

2)

When creating a nested record, you are allowed to nest only a single record or a single collection.

  1. _____ True

  2. _____ False

3)

When creating a nested record, you must initialize

  1. _____ all of the fields of the enclosing record.

  2. _____ at least one of the fields of the enclosing record.

  3. _____ a field of the enclosing record only if there is a NOT NULL constraint defined in it.

4)

It is illegal to declare a record field as an index-by table.

  1. _____ True

  2. _____ False

5)

It is illegal to declare a record field as a varray.

  1. _____ True

  2. _____ False


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net