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.