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
| 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. | | 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.
| 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.
|