لطفا منتظر باشید ...
Lab 9.2 Exercise AnswersA1: | Answer: Your block should look like this: -- ch09_10a.sql DECLARE CURSOR c_group_discount IS SELECT DISTINCT s.course_no FROM section s, enrollment e WHERE s.section_id = e.section_id GROUP BY s.course_no, e.section_id, s.section_id HAVING COUNT(*)>=8; BEGIN FOR r_group_discount IN c_group_discount LOOP UPDATE course SET cost = cost * .95 WHERE course_no = r_group_discount.course_no; END LOOP; COMMIT; END; The cursor c_group_discount is declared in the declarative section. The proper SQL is used to generate the select statement to answer the question given. The cursor is processed in a FOR loopin each iteration of the loop the SQL update statement will be executed. This means it does not have to be opened, fetched, and closed. Also, it means that a cursor attribute does not have to be used to create an exit condition for the loop that is processing the cursor. |
DECLARE
v_sid student.student_id%TYPE;
CURSOR c_student IS
SELECT student_id, first_name, last_name
FROM student
WHERE student_id < 110;
CURSOR c_course IS
SELECT c.course_no, c.description
FROM course c, section s, enrollment e
WHERE c.course_no = s.course_no
AND s.section_id = e.section_id
AND e.student_id = v_sid;
BEGIN
FOR r_student IN c_student
LOOP
v_sid := r_student.student_id;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(' The Student '||
r_student.student_id||' '||
r_student.first_name||' '||
r_student.last_name);
DBMS_OUTPUT.PUT_LINE(' is enrolled in the '||
'following courses: ');
FOR r_course IN c_course
LOOP
DBMS_OUTPUT.PUT_LINE(r_course.course_no||
' '||r_course.description);
END LOOP;
END LOOP;
END;
The select statements for the two cursors are defined in the declarative section of the PL/SQL block. A variable to store the student_id from the parent cursor is also declared. The course cursor is the child cursor, and, since it makes use of the variable v_sid, the variable must be declared first. Both cursors are processed with a FOR loop, which eliminates the need for OPEN, FETCH, and CLOSE. When the parent student loop is processed, the first step is to initialize the variable v_sid, and the value is then used when the child loop is processed. DBMS_OUTPUT is used so that display is generated for each cursor loop. The parent cursor will display the student name once, and the child cursor will display the name of each course in which the student is enrolled.
| A1: | Answer: The declaration section contains a declaration for two variables. The first is v_amount of the datatype matching that of the cost in the course table; the second is the v_instructor_id of the datatype matching the instructor_id in the instructor table. There are also two declarations for two cursors. The first is for c_inst, which is comprised of the first_name, last_name, and instructor_id for an instructor from the instructor table. The second cursor, c_cost, will produce a result set of the cost of the course taken for each student enrolled in a course by the instructor that matches the variable v_instructor_id. These two cursors will be run in nested fashion. First, the cursor c_inst is opened in a FOR loop. The value of the variable v_instructor_id is initialized to match the instructor_id of the current row of the c_inst cursor. The variable v_amount is initialized to 0. The second cursor is open within the loop for the first cursor. This means that for each iteration of the cursor c_inst, the second cursor will be opened, fetched, and closed. The second cursor will loop through all the cost generated by each student enrolled in a course for the instructor, which is current of the c_inst cursor. Each time the nest loop iterates, it will increase the variable v_amount by adding the current cost in the c_cost loop. Prior to opening the c_cost loop, there is a DBMS_OUTPUT to display the instructor name. After the c_cost cursor loop is closed, it will display the total amount generated by all the enrollments of the current instructor. |
| A2: | Answer: The result set would be as follows: Generated by instructor Fernand Hanks $16,915 Generated by instructor Tom Wojick $18,504 Generated by instructor Nina Schorin $30,137 Generated by instructor Gary Pertez $24,044 Generated by instructor Anita Morris $13,389 Generated by instructor Todd Smythe $14,940 Generated by instructor Rick Chow $0 Generated by instructor Charles Lowry $12,175 Generated by instructor Marilyn Frantzen $13,224 PL/SQL procedure successfully completed. In this example, the nested cursor is tied to the current row of the outer cursor by means of the variable v_instructor_id. A more common way of doing this is to pass a parameter to a cursor. You will learn more about how to achieve this in Chapter 15, " Advanced Cursors." |
