[PL/SQL] CURSOR例子

CREATE OR REPLACE PROCEDURE EXPCURSOR_TEST
(
    V_DEPTNO IN DEPT.DEPTNO%TYPE
) 
IS
    -- 声明CURSOR
    CURSOR DEPT_AVG IS
    SELECT B.DNAME                  AS DNAME
         , COUNT(A.EMPNO)           AS EMPCNT
         , ROUND(AVG(A.SAL), 3)     AS SAL       
      FROM EMP A, DEPT B
     WHERE A.DEPTNO = B.DEPTNO
       AND B.DEPTNO = V_DEPTNO
  GROUP BY B.DNAME;
     
    -- FETCH VARABLE
    V_DNAME DEPT.DNAME%TYPE;
    V_EMPCNT NUMBER;
    V_SAL NUMBER;
    
BEGIN
    -- CURSOR OPEN
    OPEN DEPT_AVG;
    
    -- CURSOR FETCH
    FETCH DEPT_AVG INTO V_DNAME, V_EMPCNT, V_SAL;
    
    DBMS_OUTPUT.PUT_LINE('部门：' || V_DNAME);
    DBMS_OUTPUT.PUT_LINE('员工数：' || V_EMPCNT);
    DBMS_OUTPUT.PUT_LINE('平均工资：' || V_SAL);
    
    --CLOSE CURSOR
    CLOSE DEPT_AVG;
    
    EXCEPTION         
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE));
           DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); 
END EXPCURSOR_TEST;