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

a)

Modify the script used earlier in this lab. Instead of using index-by table, use nested table.

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.

b)

Modify the script used earlier in this lab. Instead of using index-by table, use a varray.

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.  

c)

Modify the script used earlier in this lab. Instead of using a cursor-based record, use a user -defined record. The new record should have three fields: first_name , last_name , enrollments . The last field will contain total number of courses in which a student is currently enrolled.

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.