Chapter 19 Records

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Appendix D.  Answers to Test Your Thinking Sections


1)

Create the following script. Create an index-by table with the element type of a user-defined record. This record should contain first name, last name, and the total number of courses that a particular instructor teaches. Display the records of the index-by table on the screen.

A1:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE     CURSOR instructor_cur IS        SELECT first_name, last_name, COUNT(UNIQUE        s.course_no) courses          FROM instructor i          LEFT OUTER JOIN section s            ON (s.instructor_id = i.instructor_id)        GROUP BY first_name, last_name;     TYPE rec_type IS RECORD        (first_name INSTRUCTOR.FIRST_NAME%TYPE,         last_name INSTRUCTOR.LAST_NAME%TYPE,         courses_taught NUMBER);     TYPE instructor_type IS TABLE OF REC_TYPE     INDEX BY BINARY_INTEGER;     instructor_tab instructor_type;     v_counter INTEGER := 0;  BEGIN     FOR instructor_rec IN instructor_cur LOOP        v_counter := v_counter + 1;        -- Populate index-by table of records        instructor_tab(v_counter).first_name :=           instructor_rec.first_name;        instructor_tab(v_counter).last_name :=           instructor_rec.last_name;        instructor_tab(v_counter).courses_taught :=           instructor_rec.courses;        DBMS_OUTPUT.PUT_LINE ('Instructor, '||           instructor_tab(v_counter).first_name||' '||           instructor_tab(v_counter).last_name||', teaches '||           instructor_tab(v_counter).courses_taught||'           courses.');     END LOOP;  END; 

Consider the SELECT statement used in this script. This SELECT statement returns the instructor's name and total number of courses that he or she teaches. The statement is using an outer join so that if a particular instructor is not teaching any courses, he or she will be included in the results of the SELECT statement. Note that the SELECT statement uses ANSI 1999 SQL standard.

graphics/intfig07.gif

You will find detailed explanations and examples of the statements using the new ANSI 1999 SQL standard in Appendix E and in the Oracle help. Throughout this book we try to provide you with examples illustrating both standards; however, our main focus is on PL/SQL features rather than SQL.

In this script, you define a cursor against the INSTRUCTOR and SECTION tables that is used to populate the index-by table of records, instructor_tab. Each row of this table is a user-defined record of three elements. You populate the index-by table via the cursor FOR loop. Consider the notation used to reference each record element of the index-by table:

 instructor_tab(v_counter).first_name  instructor_tab(v_counter).last_name  instructor_tab(v_counter).courses_taught 

To reference each row of the index-by table, you use the counter variable. However, because each row of this table is a record, you must also reference individual fields of the underlying record. When run, this script produces the following output:

 Instructor, Anita Morris, teaches 10 courses.  Instructor, Charles Lowry, teaches 9 courses.  Instructor, Fernand Hanks, teaches 9 courses.  Instructor, Gary Pertez, teaches 10 courses.  Instructor, Marilyn Frantzen, teaches 9 courses.  Instructor, Nina Schorin, teaches 10 courses.  Instructor, Rick Chow, teaches 1 courses.  Instructor, Todd Smythe, teaches 10 courses.  Instructor, Tom Wojick, teaches 9 courses.  PL/SQL procedure successfully completed. 
2

Modify the script created in project (1). Instead of using an index-by table, use a nested table.

A2:

Answer: Your script should look similar to the following. All changes are highlighted in bold.

 SET SERVEROUTPUT ON  DECLARE     CURSOR instructor_cur IS        SELECT first_name, last_name, COUNT(UNIQUE        s.course_no) courses          FROM instructor i          LEFT OUTER JOIN section s            ON (s.instructor_id = i.instructor_id)        GROUP BY first_name, last_name;     TYPE rec_type IS RECORD        (first_name INSTRUCTOR.FIRST_NAME%TYPE,         last_name INSTRUCTOR.LAST_NAME%TYPE,         courses_taught NUMBER);    TYPE instructor_type IS TABLE OF REC_TYPE;     instructor_tab instructor_type := instructor_type();     v_counter INTEGER := 0;  BEGIN     FOR instructor_rec IN instructor_cur LOOP        v_counter := v_counter + 1;        instructor_tab.EXTEND;        -- Populate index-by table of records        instructor_tab(v_counter).first_name :=           instructor_rec.first_name;        instructor_tab(v_counter).last_name :=           instructor_rec.last_name;        instructor_tab(v_counter).courses_taught :=           instructor_rec.courses;        DBMS_OUTPUT.PUT_LINE ('Instructor, '||           instructor_tab(v_counter).first_name||' '||           instructor_tab(v_counter).last_name||', teaches '||           instructor_tab(v_counter).courses_taught||'           courses.');     END LOOP;  END; 

Notice that the instructor_tab must be initialized and extended before its individual elements can be referenced.

3)

Modify the script created in project (2). Instead of using a nested table, use a varray.

A3:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE    CURSOR instructor_cur IS        SELECT first_name, last_name, COUNT(UNIQUE        s.course_no) courses          FROM instructor i          LEFT OUTER JOIN section s            ON (s.instructor_id = i.instructor_id)        GROUP BY first_name, last_name;     TYPE rec_type IS RECORD        (first_name INSTRUCTOR.FIRST_NAME%TYPE,         last_name INSTRUCTOR.LAST_NAME%TYPE,         courses_taught NUMBER);     TYPE instructor_type IS VARRAY(10) OF REC_TYPE;     instructor_tab instructor_type := instructor_type();     v_counter INTEGER := 0;  BEGIN     FOR instructor_rec IN instructor_cur LOOP        v_counter := v_counter + 1;        instructor_tab.EXTEND;        -- Populate index-by table of records        instructor_tab(v_counter).first_name :=           instructor_rec.first_name;        instructor_tab(v_counter).last_name :=           instructor_rec.last_name;        instructor_tab(v_counter).courses_taught :=           instructor_rec.courses;        DBMS_OUTPUT.PUT_LINE ('Instructor, '||           instructor_tab(v_counter).first_name||' '||           instructor_tab(v_counter).last_name||', teaches '||           instructor_tab(v_counter).courses_taught||'           courses.');     END LOOP;  END; 

This version of the script is almost identical to the previous version. Instead of using a nested table, you are using a varray of 15 elements.

4)

Create the following script. Create a user-defined record with three fields: course_no, description, cost, and prerequisite_rec. The last field, prerequisite_rec, should be a user-defined record with three fields: prereq_no, prereq_desc, and prereq_cost. For any ten courses that have a prerequisite course, populate the user-defined record with all corresponding data and display its information on the screen.

A4:

Answer: Your script should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE      CURSOR c_cur IS         SELECT course_no, description, cost, prerequisite           FROM course          WHERE prerequisite IS NOT NULL            AND rownum <= 10;      TYPE prerequisite_type IS RECORD         (prereq_no NUMBER,          prereq_desc VARCHAR(50),          prereq_cost NUMBER);     TYPE course_type IS RECORD        (course_no NUMBER,         description VARCHAR2(50),         cost NUMBER,         prerequisite_rec PREREQUISITE_TYPE);     course_rec COURSE_TYPE;  BEGIN     FOR c_rec in c_cur LOOP        course_rec.course_no := c_rec.course_no;        course_rec.description := c_rec.description;        course_rec.cost := c_rec.cost;        SELECT course_no, description, cost          INTO course_rec.prerequisite_rec.prereq_no,               course_rec.prerequisite_rec.prereq_desc,               course_rec.prerequisite_rec.prereq_cost          FROM course         WHERE course_no = c_rec.prerequisite;        DBMS_OUTPUT.PUT_LINE ('Course: '||           course_rec.course_no||'  '||           course_rec.description);        DBMS_OUTPUT.PUT_LINE ('Cost: '|| course_rec.cost);        DBMS_OUTPUT.PUT_LINE ('Prerequisite: '||           course_rec.prerequisite_rec. prereq_no||'  '||           course_rec.prerequisite_rec.prereq_desc);        DBMS_OUTPUT.PUT_LINE ('Prerequisite Cost: '||           course_rec.prerequisite_rec.prereq_cost);        DBMS_OUTPUT.PUT_LINE           ('========================================');     END LOOP;  END; 

In the declaration portion of the script, you define a cursor against the COURSE table; two user-defined record types, prerequisite_type and course_type; and user-defined record, course_rec. It is important to note the order in which the record types are declared. The prerequsite_type must be declared first because one of the course_type elements is of the prerequisite_type.

In the executable portion of the script, you populate course_rec via the cursor FOR loop. First, you assign values to the course_rec.course_no, course_ rec.description, and course_rec.cost. Next, you populate the nested record, prerequsite_rec, via the SELECT INTO statement against the COURSE table. Consider the notation used to reference individual elements of the nested record:

 course_rec.prerequisite_rec.prereq_no,  course_rec.prerequisite_rec.prereq_desc,  course_rec.prerequisite_rec.prereq_cost 

You specify the name of the outer record followed by the name of the inner (nested) record followed by the name of the element. Finally, you display record information on the screen.

Note that this script does not contain a NO_DATA_FOUND exception handler even though there is a SELECT INTO statement. Why do you think this is the case?

When run, the script produces the following output:

 Course: 25 - Intro to Programming  Cost: 1195  Prerequisite: 140 - Structured Analysis  Prerequisite Cost: 1195  ========================================  Course: 80 - Structured Programming Techniques  Cost: 1595  Prerequisite: 204 - Intro to SQL  Prerequisite Cost: 1195  ========================================  Course: 100 - Hands-On Windows  Cost: 1195  Prerequisite: 20 - Intro to Computers  Prerequisite Cost: 1195  ========================================  Course: 120 - Intro to Java Programming  Cost: 1195  Prerequisite: 80 - Structured Programming Techniques  Prerequisite Cost: 1595  ========================================  Course: 122 - Intermediate Java Programming  Cost: 1195  Prerequisite: 120 - Intro to Java Programming  Prerequisite Cost: 1195  ========================================  Course: 124 - Advanced Java Programming  Cost: 1195  Prerequisite: 122 - Intermediate Java Programming  Prerequisite Cost: 1195  ========================================  Course: 125 - JDeveloper  Cost: 1195  Prerequisite: 122 - Intermediate Java Programming  Prerequisite Cost: 1195  ========================================  Course: 130 - Intro to Unix  Cost: 1195  Prerequisite: 310 - Operating Systems  Prerequisite Cost: 1195  ========================================  Course: 132 - Basics of Unix Admin  Cost: 1195  Prerequisite: 130 - Intro to Unix  Prerequisite Cost: 1195  ========================================  Course: 134 - Advanced Unix Admin  Cost: 1195  Prerequisite: 132 - Basics of Unix Admin  Prerequisite Cost: 1195  ========================================  PL/SQL procedure successfully completed. 


    Team-Fly    
    Top
     



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

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