pl/sql

are you simply looking to declare the cursor in a package?


CREATE OR REPLACE PACKAGE your_package
IS
    CURSOR c_instr(p_instr IN instructor.instructor_id%TYPE)
    IS
        SELECT i.instructor_id, i.last_name, c.description
          FROM instructor i
              JOIN section s
                  ON i.instructor_id = s.instructor_id
              JOIN course c
                  ON s.course_no = c.course_no
         WHERE i.instructor_id = p_instr;

    vr_instr c_instr%ROWTYPE;
END;

or were you looking for a procedure that would mimic your entire pl/sql block and open/fetch/close the cursor?

if you want the latter then try something like this...


CREATE OR REPLACE PACKAGE your_package
IS
    PROCEDURE process_cursor(p_instr IN instructor.instructor_id%TYPE);
END;

CREATE OR REPLACE PACKAGE BODY your_package
IS
    PROCEDURE process_cursor(p_instr IN instructor.instructor_id%TYPE)
    IS
        v_count INTEGER := 0;
    BEGIN
        DBMS_OUTPUT.put_line('instructor ' || p_instr);

        FOR vr_instr IN (SELECT i.instructor_id, i.last_name, c.description
                           FROM instructor i
                               JOIN section s
                                   ON i.instructor_id = s.instructor_id
                               JOIN course c
                                   ON s.course_no = c.course_no
                          WHERE i.instructor_id = p_instr)
        LOOP
            v_count := v_count + 1;
            DBMS_OUTPUT.put_line(
                   vr_instr.instructor_id
                || ',   '
                || vr_instr.last_name
                || ',   '
                || vr_instr.description
            );
        END LOOP;

        DBMS_OUTPUT.put_line('There are ' || v_count || ' classes.');
    EXCEPTION
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.put_line('An error has occurred');
    END;
END;

considering the first case,looking to declare the cursor in a package. How would I use the cursor  after creating the package?