Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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.

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;

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;