Lab 19.3 Collections of Records

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 19.  Records


Lab Objectives

After this Lab, you will be able to:

  • Use Collections of Records

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

graphics/intfig03.gif 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.

  1. _____ True

  2. _____ False

2)

A varray of records has an upper bound

  1. _____ that is fixed and cannot be extended to all.

  2. _____ that can be extended to its maximum size.

  3. _____ that can be extended without any limits.

3)

There is no need to initialize a nested table of records prior to its use.

  1. _____ True

  2. _____ False

4)

There is no need to increase the size of a nested table of records before it is populated with a new record.

  1. _____ True

  2. _____ False

5)

It is illegal to use a user-defined record as an element type when creating a collection of records.

  1. _____ True

  2. _____ False


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net