Oracle PL/SQL Einzel-Tipp ansehen

Zwei Cursor

Tipp 70

DECLARE
 CURSOR cur1 (p_comm number DEFAULT NULL) is
            SELECT empno,ename
             FROM scott.emp WHERE comm = p_comm;
 CURSOR cur2  IS
            SELECT empno,ename
             FROM scott.emp WHERE COMM IS NULL;            
 emp_satz  cur1%ROWTYPE;
 p_curs emp.comm%type :=NULL;
BEGIN
 if p_curs is not null then
     OPEN cur1(p_curs);
     LOOP
          FETCH cur1 INTO emp_satz;
          EXIT WHEN cur1%NOTFOUND;
          dbms_output.put_line(emp_satz.empno||'   '||emp_satz.ename);
     END LOOP;

     dbms_output.put_line(' Eingelesene Zeilen :'||cur1%rowcount);
     CLOSE cur1;
 ELSE
    OPEN cur2;
     LOOP
          FETCH cur2 INTO emp_satz;
          EXIT WHEN cur2%NOTFOUND;
          dbms_output.put_line(emp_satz.empno||'   '||emp_satz.ename);
     END LOOP;
     dbms_output.put_line(' Eingelesene Zeilen :'||cur2%rowcount);
     CLOSE cur2;
 END IF;
END;
/