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.