| Lab Objectives After this Lab, you will be able to: In the previous lab you have seen an example of the nested record where one of the record fields was defined as an index-by table. PL/SQL also gives you ability to define a collection of records (for example, an index-by table where its element type is a cursor-based record, as shown in the following example).  FOR EXAMPLE DECLARE CURSOR name_cur IS SELECT first_name, last_name FROM student WHERE ROWNUM <= 4; TYPE name_type IS TABLE OF name_cur%ROWTYPE 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; 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 declaration portion of the example, you define the name_cur cursor, which returns the first and last names of the first four students. Next, you define an index-by table type with its element type based on the cursor defined previously via the %ROWTYPE attribute. Then you define an index-by table variable and the counter that is used later to reference individual rows of the index-by table. In the executable portion of the example, you populate the index-by table and display its records on the screen. Consider the notation used in the example when referencing individual elements of the index-by table: name_tab(v_counter).first_name and name_tab(v_counter).last_name Notice that to reference each row of the index-by table, you use the counter variable just like in all previous examples. However, because each row of this table is a record, you must also reference individual fields of the underlying record. This example produces the following output: 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. Lab 19.3 Exercises 19.3.1 Use Collections of Records In this exercise, you will learn more about collections of records. Answer the following questions: | a) | Modify the script used earlier in this lab. Instead of using index-by table, use nested table. | | b) | Modify the script used earlier in this lab. Instead of using index-by table, use a varray. | | 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. |
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. |
Lab 19.3 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 19.3. | 1) | Collections of records are not supported by Oracle 9i. _____ True _____ False
| | 2) | A varray of records has an upper bound _____ that is fixed and cannot be extended to all. _____ that can be extended to its maximum size. _____ that can be extended without any limits.
| | 3) | There is no need to initialize a nested table of records prior to its use. _____ True _____ False
| | 4) | There is no need to increase the size of a nested table of records before it is populated with a new record. _____ True _____ False
| | 5) | It is illegal to use a user-defined record as an element type when creating a collection of records. _____ True _____ False
|
|
