CURSOR Expressions

CURSOR Expressions

A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function.
CRUSOR表达式返回一个嵌套的CUROS。这种格式的表达式相当于PL/SQL中的REF CURSOR，并且可以作为 一个REF CURSOR类型的参数传给函数。

A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:
当对cursor表达式求值时，嵌套cursor会被隐式的打开。比如，cursor表达式出现在select语句中，查询语句获取的每一行数据都会打开一个嵌套的cursor。嵌套cursor在以下情况下会被关闭：  

• The nested cursor is explicitly closed by the user
  嵌套cursor被用户显示的关闭

• The parent cursor is reexecuted
  父cursor被重新执行

• The parent cursor is closed
  父cursor被关闭

• The parent cursor is cancelled
  父cursor被取消

• An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)
  获取父cursor时出错（嵌套cursor被闭是清理的一部分）

Restrictions on CURSOR Expressions 
Cursor表达式的限制
The following restrictions apply to CURSOR expressions:
Cursor表达式有以下限制：

• If the enclosing statement is not a SELECT statement, then nested cursors can appear only as REF CURSOR arguments of a procedure.
  如果外层语句不是select语句，嵌套cursor只能作为存储过程的REF CURSOR类型的参数被使用（以处应该也包含function）。

• If the enclosing statement is a SELECT statement, then nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.
  如果外层语句是select语句，嵌套cursor可以出现在查询语句的最外层，或是另一个嵌套cursor中的查询语句的最外层。  

• Nested cursors cannot appear in views.
  嵌套cursor不能出现在视图中。

• You cannot perform BIND and EXECUTE operations on nested cursors.
  不能在在嵌套cursor上执行BIND和EXECUTE。

Examples The following example shows the use of a CURSOR expression in the select list of a query:
以下事例演示了cursor表达式在select语句的用法：

SELECT department_name, CURSOR(SELECT salary, commission_pct 
   FROM employees e
   WHERE e.department_id = d.department_id)
   FROM departments d
   ORDER BY department_name;

The next example shows the use of a CURSOR expression as a function argument. The example begins by creating a function in the sample OE schema that can accept theREF CURSOR argument. (The PL/SQL function body is shown in italics.)
接下来的事例演示了cursor作为函数参数的用法。此事例以在OE用户下创建一个可以接收REF CURSOR类型参数的函数开始（PL/SQL函数体以斜体展示）

CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE) 
   RETURN NUMBER IS
   emp_hiredate DATE;
   before number :=0;
   after number:=0;
begin
  loop
    fetch cur into emp_hiredate;
    exit when cur%NOTFOUND;
    if emp_hiredate > mgr_hiredate then
      after:=after+1;
    else
      before:=before+1;
    end if;
  end loop;
  close cur;
  if before > after then
    return 1;
  else
    return 0;
  end if;
end;
/

The function accepts a cursor and a date. The function expects the cursor to be a query returning a set of dates. The following query uses the function to find those managers in the sample employees table, most of whose employees were hired before the manager.
此函数有两个参数，一个cursor类型，一个日期类型。函数要求cursor返回一组日期。以下的查询使用上面的函数在employees表中查找大部分下属入取时间比自己早的经理的名字。

SELECT e1.last_name FROM employees e1
   WHERE f(
   CURSOR(SELECT e2.hire_date FROM employees e2
   WHERE e1.employee_id = e2.manager_id),
   e1.hire_date) = 1
   ORDER BY last_name;
 
LAST_NAME
-------------------------
Cambrault
Higgins
Hunold
Kochhar
Mourgos
Zlotkey