Oracle PL/SQL Einzel-Tipp ansehen

Cursor bleibt im Package offen

Tipp 52

CREATE OR REPLACE PACKAGE my_curs AS
procedure cursorauf;
procedure ausgabe (zeilenanzahl NUMBER);
procedure cursorzu;
END;
/

CREATE OR REPLACE PACKAGE BODY my_curs AS
CURSOR c_emp IS SELECT * FROM emp;
rec_emp c_emp%ROWTYPE;

PROCEDURE cursorauf
IS
BEGIN
 OPEN c_emp;
 FETCH c_emp INTO rec_emp;
 dbms_output.put_line('Name:'||rec_emp.ename);
 dbms_output.new_line;
END cursorauf;

PROCEDURE ausgabe(zeilenanzahl NUMBER)
IS
BEGIN
    IF NOT c_emp%isopen THEN
         OPEN c_emp;
     END IF;
    FOR I IN 1 .. zeilenanzahl LOOP
        FETCH c_emp INTO rec_emp;
        EXIT WHEN c_emp%NOTFOUND OR c_emp%NOTFOUND IS NULL;
        dbms_output.put_line('Name:'||rec_emp.ename);
 END LOOP;
END ausgabe;

PROCEDURE cursorzu is
BEGIN    
 CLOSE c_emp;
END cursorzu;
END my_curs;
/

exec my_curs.cursorauf;
exec my_curs.ausgabe(5);

exec my_curs.cursorzu;