Chapter 9 Introduction to Cursors

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)

Write a nested cursor where the parent cursor calls information about each section of a course. The child cursor counts the enrollment. The only output is one line for each course with the Course Name and Section Number and the total enrollment.

A1:

Answer: Your answer should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE     CURSOR c_course IS        SELECT course_no, description          FROM course         WHERE course_no < 120;     CURSOR c_enrollment(p_course_no IN  course.course_no%TYPE)     IS        SELECT s.section_no section_no, count(*) count          FROM section s, enrollment e         WHERE s.course_no = p_course_no           AND s.section_id = e.section_id         GROUP BY s.section_no;  BEGIN     FOR r_course IN c_course LOOP        DBMS_OUTPUT.PUT_LINE           (r_course.course_no||' '|| r_course.description);        FOR r_enroll IN c_enrollment(r_course.course_no) LOOP           DBMS_OUTPUT.PUT_LINE              (Chr(9)||'Section:  '||r_enroll.section_no||               ' has an enrollment of: '||r_enroll.count);        END LOOP;     END LOOP;  END; 
2)

Write an anonymous PL/SQL block that finds all the courses that have at least one section that is at its maximum enrollment. If there are no courses that meet that criterion, then pick two courses and create that situation for each.

  1. For each of those courses, add another section. The instructor for the new section should be taken from the existing records in the instruct table. Use the instructor who is signed up to teach the least number of courses. Handle the fact that, during the execution of your program, the instructor teaching the most courses may change.

  2. Use any exception-handling techniques you think are useful to capture error conditions.

A2:

Answer: Your answer should look similar to the following:

 SET SERVEROUTPUT ON  DECLARE     v_instid_min instructor.instructor_id%TYPE;     v_section_id_new section.section_id%TYPE;     v_snumber_recent section.section_no%TYPE := 0;     -- This cursor determines the courses that have at least     -- one section filled to capacity.     CURSOR c_filled IS        SELECT DISTINCT s.course_no          FROM section s         WHERE s.capacity = (SELECT COUNT(section_id)                               FROM enrollment e                              WHERE e.section_id =                              s.section_id);  BEGIN     FOR r_filled IN c_filled LOOP        -- For each course in this list, add another section.        -- First, determine the instructor who is teaching        -- the least number of courses. If there are more        -- than one instructor teaching the same number of        -- minimum courses (e.g. if there are three        -- instructors teaching 1 course) use any of those        -- instructors.        SELECT instructor_id          INTO v_instid_min          FROM instructor         WHERE EXISTS (SELECT NULL                         FROM section                        WHERE section.instructor_id =                              instructor.instructor_id                       GROUP BY instructor_id                        HAVING COUNT(*) =                         (SELECT MIN(COUNT(*))                             FROM section                           WHERE instructor_id IS NOT NULL                          GROUP BY instructor_id)                      )           AND ROWNUM = 1;        -- Determine the section_id for the new section        -- Note that this method would not work in a multi-user        -- environment. A sequence should be used instead.        SELECT MAX(section_id) + 1          INTO v_section_id_new          FROM section;        -- Determine the section number for the new section        -- This only needs to be done in the real world if        -- thesystem specification calls for a sequence in        -- a parent. The sequence in parent here refers to        -- the section_no incrementing within the course_no,        -- and not the section_no incrementing within        -- the section_id.        DECLARE           CURSOR c_snumber_in_parent IS              SELECT section_no              FROM section             WHERE course_no = r_filled.course_no           ORDER BY section_no;        BEGIN           -- Go from the lowest to the highest section_no           -- and find any gaps. If there are no gaps make           -- the new section_no equal to the highest           -- current section_no + 1.           FOR r_snumber_in_parent IN c_snumber_in_parent LOOP             EXIT WHEN r_snumber_in_parent.section_no >             v_snumber_recent + 1;             v_snumber_recent := r_snumber_in_parent.             section_no + 1;           END LOOP;           -- At this point, v_snumber_recent will be equal           -- either to the value preceeding the gap or to           -- the highest section_no for that course.        END;        -- Do the insert.        INSERT INTO section          (section_id, course_no, section_no, instructor_id)        VALUES          (v_section_id_new, r_filled.course_no,           v_snumber_recent, v_instid_min);        -- COMMIT;     END LOOP;  EXCEPTION     WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE ('An error has occurred');  END; 


    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