游标变量为一个指针，类型是 REF CURSOR，所以在声明游标变量类型之前必须先定义游标变量类型。在 PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量参照类型。

语法格式如下：

ref_type_name 为新定义的游标变量类型名称，retuen_type 为游标变量的返回值类型，它必须为记录变量。

在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。

强类型游标变量示例如下：

  2.     ->     TYPE rec_emp_job IS RECORD(
  3.     ->         employee_id employees.employee_id%TYPE,
  4.     ->         employee_name employees.first_name%TYPE,
  5.     ->         job_id employees.job_id%TYPE
  6.     ->     );
  7.     ->     TYPE emp_job_refcur_type IS REF CURSOR RETURN rec_emp_job;
  8.     ->     refcur_emp emp_job_refcur_type;
  9.     ->     emp_job rec_emp_job;
  10.     -> BEGIN
  11.     ->     OPEN refcur_emp FOR
  12.     ->         SELECT employee_id, first_name, job_id
  13.     ->           FROM employees
  14.     ->           ORDER BY department_id;
  15.     ->     FETCH refcur_emp INTO emp_job;
  16.     ->     WHILE refcur_emp%FOUND LOOP
  17.     ->         DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
  18.     ->     FETCH refcur_emp INTO emp_job;
  19.     ->     END LOOP;
  20.     -> END;
  21.     -> /
  22. Query OK, 0 rows affected (0.14 sec)
  24. 202: Pat is a MK_REP
  25. 201: Michael is a MK_MAN
  26. 119: Karen is a PU_CLERK
  27. 118: Guy is a PU_CLERK
  28. ...

弱类型游标变量示例如下：

弱类型游标并没有返回值，是个通用的类型，可以通过系统内置的类型 SYS_REFCURSOR 来直接定义。所以上面的例子可以改写成如下示例：

  1. obclient> DECLARE
  2.     ->     TYPE rec_emp_job IS RECORD(
  3.     ->         employee_id employees.employee_id%TYPE,
  4.     ->         employee_name employees.first_name%TYPE,
  5.     ->         job_id employees.job_id%TYPE
  6.     ->     );
  7.     ->     -- 定义refcur_emp类型为SYS_REFCURSOR
  8.     ->     refcur_emp SYS_REFCURSOR;
  9.     ->     emp_job rec_emp_job;
  10.     -> BEGIN
  11.     ->     OPEN refcur_emp FOR
  12.     ->         SELECT employee_id, first_name, job_id
  13.     ->           FROM employees
  14.     ->           ORDER BY department_id;
  15.     ->     FETCH refcur_emp INTO emp_job;
  16.     ->     WHILE refcur_emp%FOUND LOOP
  17.     ->         DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
  19.     -> END;
  20.     -> /
  21. Query OK, 0 rows affected (0.13 sec)
  23. 200: Jennifer is a AD_ASST
  24. 202: Pat is a MK_REP
  25. 201: Michael is a MK_MAN
  26. 119: Karen is a PU_CLERK
  27. 118: Guy is a PU_CLERK
  28. ...

  • 对于弱类型的游标变量，PL 引擎在编译时并不知道变量的类型，需要在执行期间进行绑定。 这意味着程序在执行期间会消耗更多的资源和时间。

  • SYS_REFCURSOR 类型的游标变量支持重用。

动态 SQL 是一种高级编程方式，指在 PL 程序运行时生成和执行 SQL。 游标变量同样支持动态绑定， 使用 OPEN… FOR 子句通过字符串来定义定标。 下面例子里，游标 cv 通过 query_2 这个字符串变量来定义。

关于动态 SQL 的详细信息，请参见。

使用游标变量循环获取记录集的每行记录的方法如下：

  1. 在声明部分

  2. 定义动态游标类型：TYPE cursor_type IS REF CURSOR [ RETURN return_type ];

  4. 声明一个记录用于保存游标返回的记录：record_name return_type;

  5. 在执行部分

  6. 打开游标变量绑定到特定查询：OPEN cursor_variable FOR query;

  7. 从游标记录集中获取行数据。每次一行，结果下面格式的 LOOP 语句一起使用可以获取多行数据。

  1. LOOP
  2. FETCH cursor_variable INTO record_name;
  3. EXIT WHEN cursor_variable%NOTFOUND;
  4.     statement;
  5.     [ statement; ]...

关闭游标：CLOSE cursor_name;

或者重新打开游标变量，绑定到另外一个查询（会自动关闭当前查询的游标），重复前面的步骤。

示例如下：使用游标变量循环获取记录集的每行记录