CURSOR Expressions A CURSOR expression returns a nested cursor. This form of expression is similar to the PL/SQL REF cursor. A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when: The nested cursor is explicitly closed by the user The parent cursor is reexecuted The parent cursor is closed The parent cursor is cancelled An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up) Restrictions: The following restrictions apply to the CURSOR expression: Nested cursors can appear only in a SELECT statement that is not nested in any other query expression, except when it is a subquery of the CURSOR expression itself. Nested cursors can appear only in the outermost SELECT list of the query specification. Nested cursors cannot appear in views. You cannot perform BIND and EXECUTE operations on nested cursors. ExampleSELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum, p.projname FROM projects p WHERE p.empno = e.empno) FROM TABLE(d.employees) e) FROM dept d WHERE d.dno = 605;
A CURSOR expression returns a nested cursor. This form of expression is similar to the PL/SQL REF cursor. A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when: The nested cursor is explicitly closed by the user The parent cursor is reexecuted The parent cursor is closed The parent cursor is cancelled An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up) Restrictions: The following restrictions apply to the CURSOR expression: Nested cursors can appear only in a SELECT statement that is not nested in any other query expression, except when it is a subquery of the CURSOR expression itself. Nested cursors can appear only in the outermost SELECT list of the query specification. Nested cursors cannot appear in views. You cannot perform BIND and EXECUTE operations on nested cursors. ExampleSELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum,
p.projname
FROM projects p
WHERE p.empno = e.empno)
FROM TABLE(d.employees) e)
FROM dept d
WHERE d.dno = 605;
一般系统会限制最多打开的游标数量,
像游标嵌套这样一般会超过系统的最大游标数量
会报错