Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

pl/sql

how can I put the following pl/sql cursor in a package:

DECLARE
v_instr instructor.instructor_Id%type := &id_instr;
CURSOR c_instr 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=v_instr;

vr_instr c_instr%rowtype;
BEGIN
DBMS_OUTPUT.PUT_LINE('instructor '||v_instr);
OPEN c_instr;
LOOP
  FETCH c_instr INTO vr_instr;
  EXIT WHEN c_instr%notfound;
  DBMS_OUTPUT.PUT_LINE(vr_instr.instructor_id||',   '||vr_instr.last_name  ||',   '||vr_instr.description);
END LOOP;
DBMS_OUTPUT.PUT_LINE('There are '||c_instr%rowcount||' classes.');
CLOSE c_instr;
EXCEPTION
          WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error has occurred');
END;
0
lojayn
Asked:
lojayn
  • 4
1 Solution
 
sdstuberCommented:
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;
0
 
sdstuberCommented:
or were you looking for a procedure that would mimic your entire pl/sql block and open/fetch/close the cursor?
0
 
sdstuberCommented:
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;
0
 
lojaynAuthor Commented:
considering the first case,looking to declare the cursor in a package. How would I use the cursor  after creating the package?

0
 
sdstuberCommented:
declare
   v_your_cursor   sys_refcursor;

    v_row your_package.c_instr%rowtype;
begin
   open v_your_cursor for your_package.c_instr('ABC123');

   fetch v_your_cursor into v_row;

    --  add additional processing for v_your_cursor as you would with any other cursor variable
end;
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now