Chapter 19 Records
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. | 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.
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.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.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.
|
لطفا منتظر باشید ...
