PLSQL带参数的CURSOR

开始

SET SERVEROUTPUT ON;

DECLARE
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  
  CURSOR emp_cursor
  (p_deptno NUMBER,p_job VARCHAR2) IS
  SELECT empno,ename FROM emp WHERE deptno=p_deptno AND JOB=p_job;
  
  emp_record emp_cursor%ROWTYPE;
  
BEGIN

LOOP

  IF NOT emp_cursor%ISOPEN  THEN
     OPEN emp_cursor(30,'SALESMAN');
  END IF; 
  
  FETCH emp_cursor INTO  emp_record; 
  EXIT WHEN emp_cursor%NOTFOUND;
  
  dbms_output.put_line
     ('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' || emp_record.empno 
        || '-- emp name is:' || emp_record.ename);

END LOOP;

IF emp_cursor%ISOPEN  THEN
     dbms_output.put_line('Now to close cursor!');
     CLOSE emp_cursor;
END IF; 
  
dbms_output.put_line('-------------------------------');

LOOP

  IF NOT emp_cursor%ISOPEN  THEN
     OPEN emp_cursor(20,'CLERK');
  END IF; 
  
  FETCH emp_cursor INTO  emp_record; 
  EXIT WHEN emp_cursor%NOTFOUND;
  
  dbms_output.put_line
     ('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' || emp_record.empno 
        || '-- emp name is:' || emp_record.ename);

END LOOP;

IF emp_cursor%ISOPEN  THEN
     dbms_output.put_line('Now to close cursor!');
     CLOSE emp_cursor;
END IF; 


END;
/

运行结果

anonymous block completed
rowcount is:1-- empno is:7499-- emp name is:ALLEN
rowcount is:2-- empno is:7521-- emp name is:WARD
rowcount is:3-- empno is:7654-- emp name is:MARTIN
rowcount is:4-- empno is:7844-- emp name is:TURNER
Now to close cursor!
-------------------------------
rowcount is:1-- empno is:7369-- emp name is:SMITH
rowcount is:2-- empno is:7876-- emp name is:ADAMS
Now to close cursor!

 

结束