oracle游标cursor使用,Oracle游标6(使用CURSOR表达式)

DECLARE

TYPE refcursor IS REF CURSOR;

CURSOR dept_cursor(no NUMBER) IS

SELECT a.dname,CURSOR(SELECT ename,sal FROM emp

WHERE deptno=a.deptno)

FROM dept a WHERE a.deptno=no;

empcur refcursor;

v_dname dept.dname&TYPE;

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

BEGIN

OPEN dept_cursor(&no);

LOOP

FETCH dept_cursor INTO v_dname,empcur;

EXIT WHEN dept_cursor%NOTFOUND;

dbms_output.put_line('部门名'||v_dname);

LOOP

FETCH empcur INTO v_ename,v_sal;

EXIT WHEN empcur%NOTFOUND;

dbms_output.put_line('雇员名'||v_ename||'工资'||v_sal);

END LOOP;

END LOOP;

CLOSE dept_cursor;

END;

/