Lab 19.3 Exercise Answers
This section gives you some suggested answers to the questions in Lab 19.3, 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.3.1 Answers
| A1:
| Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch19_4a.sql, version 1.0
SET SERVEROUTPUT ON
DECLARE
CURSOR name_cur IS
SELECT first_name, last_name
FROM student
WHERE ROWNUM <= 4;
TYPE name_type IS TABLE OF name_cur%ROWTYPE;
name_tab name_type := name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab.EXTEND;
name_tab(v_counter).first_name := name_rec.first_name;
name_tab(v_counter).last_name := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('First Name('||v_counter||'): '||
name_tab(v_counter).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_counter||'): '||
name_tab(v_counter).last_name);
END LOOP;
END;
|
In the preceding script, the name_tab is declared as a nested table. As a result, at the time of its declaration it is initialized. In other words, the name_tab is empty but non-null. Furthermore, once the name_tab table is initialized, its size must be increased before it can be populated with the next record. | A2:
| Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch19_4b.sql, version 2.0
SET SERVEROUTPUT ON
DECLARE
CURSOR name_cur IS
SELECT first_name, last_name
FROM student
WHERE ROWNUM <= 4;
TYPE name_type IS VARRAY(4) OF name_cur%ROWTYPE;
name_tab name_type := name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab.EXTEND;
name_tab(v_counter).first_name := name_rec.first_name;
name_tab(v_counter).last_name := name_rec.last_name;
DBMS_OUTPUT.PUT_LINE('First Name('||v_counter||'): '||
name_tab(v_counter).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_counter||'): '||
name_tab(v_counter).last_name);
END LOOP;
END;
|
In this version of the script, the name_tab is declared as a varray with four elements. Just like in the previous version, the collection is initialized and its size is incremented before it is populated with the new record. Both scripts, ch19_4a.sql and ch19_4b.sql, produce the output identical to the original example: First Name(1): Fred
Last Name(1): Crocitto
First Name(2): J.
Last Name(2): Landry
First Name(3): Laetia
Last Name(3): Enison
First Name(4): Angel
Last Name(4): Moskowitz
PL/SQL procedure successfully completed.
| A3:
| Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch19_4c.sql, version 3.0
SET SERVEROUTPUT ON
DECLARE
CURSOR name_cur IS
SELECT first_name, last_name, COUNT(*) total
FROM student
JOIN enrollment USING (student_id)
GROUP BY first_name, last_name;
TYPE student_rec_type IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30),
enrollments INTEGER);
TYPE name_type IS TABLE OF student_rec_type
INDEX BY BINARY_INTEGER;
name_tab name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter).first_name := name_rec.first_name;
name_tab(v_counter).last_name := name_rec.last_name;
name_tab(v_counter).enrollments := name_rec.total;
IF v_counter <= 4 THEN
DBMS_OUTPUT.PUT_LINE('First Name('||v_counter||
'): '||name_tab(v_counter).first_name);
DBMS_OUTPUT.PUT_LINE('Last Name('||v_counter||
'): '||name_tab(v_counter).last_name);
DBMS_OUTPUT.PUT_LINE('Enrollments('||
v_counter||'): '||name_tab(v_counter).
enrollments);
DBMS_OUTPUT.PUT_LINE ('--------------------');
END IF;
END LOOP;
END;
|
In the declaration portion of the script, the cursor SELECT statement has been modified so that for each student it returns total number of enrollments. Next, the user-defined record type, student_rec_type, is declared so that it can be used as the element type for the index-by table type, name_type. In the executable portion of the script, the index-by table, name_tab, is populated via the cursor FOR loop. Next, the index counter variable, v_counter, is evaluated via the IF-THEN statement so that only first four records of the index-by table are displayed on the screen. When run, this script produces the following output: First Name(1): A.
Last Name(1): Tucker
Enrollments(1): 1
--------------------
First Name(2): Adele
Last Name(2): Rothstein
Enrollments(2): 1
--------------------
First Name(3): Adrienne
Last Name(3): Lopez
Enrollments(3): 1
--------------------
First Name(4): Al
Last Name(4): Jamerncy
Enrollments(4): 1
--------------------
PL/SQL procedure successfully completed.
|