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;