光标表达式

光标表达式返回嵌套光标。

cursor(subquery)

可以在非子查询的SELECT语句中使用光标表达式，或将它传递给接受光标变量参数的函数。不能通过隐式光标使用光标表达式。

DECLARE

TYPE emp_cur_typ IS REF CURSOR;

emp_cur emp_cur_typ;

dept_name departments.department_name%TYPE;

emp_name employees.last_name%TYPE;

CURSOR c1 IS

SELECT department_name,

CURSOR ( SELECT e.last_name

FROM employees e

WHERE e.department_id = d.department_id

ORDER BY e.last_name

) employees

FROM departments d

WHERE department_name LIKE 'A%'

ORDER BY department_name;

BEGIN

OPEN c1;

LOOP --处理结果集中的每一行

FETCH c1 INTO dept_name, emp_cur;

EXIT WHEN c1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);

LOOP -- 处理子查询结果集中的每一行

FETCH emp_cur INTO emp_name;

EXIT WHEN emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);

END LOOP;

CLOSE c1;

END;

ITPub博客