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. |
| 
No comments:
Post a Comment