Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 218

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:

Explain the script ch19_3a.sql.

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.

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.