与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。

声明游标变量

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

语法格式如下：

  1. TYPE ref_type_name IS REF CURSOR [ RETURN return_type];

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

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

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

  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.     ->     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. 200: Jennifer is a AD_ASST
  25. 202: Pat is a MK_REP
  26. 201: Michael is a MK_MAN
  27. 119: Karen is a PU_CLERK
  28. 118: Guy is a PU_CLERK
  29. ...

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

  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.     ->     -- 这里emp_job_refcur_type并没有定义返回类型。
  8.     ->     TYPE emp_job_refcur_type IS REF CURSOR;
  9.     ->     refcur_emp emp_job_refcur_type;
  10.     ->     emp_job rec_emp_job;
  11.     -> BEGIN
  12.     ->     OPEN refcur_emp FOR
  13.     ->         SELECT employee_id, first_name, job_id
  14.     ->           FROM employees
  15.     ->           ORDER BY department_id;
  16.     ->     FETCH refcur_emp INTO emp_job;
  17.     ->     WHILE refcur_emp%FOUND LOOP
  18.     ->         DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
  19.     ->     FETCH refcur_emp INTO emp_job;
  20.     ->     END LOOP;
  21.     -> END;
  22.     -> /
  23. Query OK, 0 rows affected (0.14 sec)
  25. 200: Jennifer is a AD_ASST
  26. 202: Pat is a MK_REP
  27. 201: Michael is a MK_MAN
  28. 119: Karen is a PU_CLERK
  29. ...

弱类型游标并没有返回值，是个通用的类型，可以通过系统内置的类型 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);
  18.     ->     FETCH refcur_emp INTO emp_job;
  19.     ->     END LOOP;
  20.     -> END;
  21.     -> /
  22. Query OK, 0 rows affected (0.13 sec)
  24. 200: Jennifer is a AD_ASST
  25. 202: Pat is a MK_REP
  26. 201: Michael is a MK_MAN
  27. 119: Karen is a PU_CLERK
  28. 118: Guy is a PU_CLERK
  29. ...

注意

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

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

使用动态 SQL 定义游标变量

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

  1. obclient> DECLARE
  2.     ->   cv SYS_REFCURSOR;  -- cursor variable
  3.     ->   query_2 VARCHAR2(200) :=
  4.     ->     'SELECT * FROM employees
  5.     '>     ORDER BY job_id';
  6.     ->   v_employees employees%ROWTYPE;  -- record variable row of table
  7.     -> BEGIN
  8.     ->   OPEN cv FOR query_2;
  9.     ->   LOOP
  10.     ->       FETCH cv INTO v_employees;
  11.     ->       EXIT WHEN cv%NOTFOUND;
  12.     ->       DBMS_OUTPUT.PUT_LINE(v_employees.employee_id||'-'||v_employees.first_name);
  13.     ->   END LOOP;
  14.     ->   CLOSE cv;
  15.     -> END;
  16.     -> /
  17. Query OK, 0 rows affected (0.14 sec)
  19. 206-William
  20. 205-Shelley
  21. 200-Jennifer
  22. 102-Lex
  23. 101-Neena
  24. 109-Daniel
  25. ...

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

使用游标变量循环获取记录集的每行记录

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

  1. 在声明部分

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

  3. 声明一个变量使用上面的动态游标类型：cursor_variable cursor_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; ]...
  6. END LOOP;

关闭游标：CLOSE cursor_name;

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

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

  1. delimiter /
  2. CREATE OR REPLACE PACKAGE pkg_ware_mgmt AS
  3.     TYPE TYPE_REFCURSOR_WARE    IS REF CURSOR RETURN ware%ROWTYPE;
  4.     PROCEDURE sp_record_print;
  5. END;
  6. /
  7. delimiter ;
  10. delimiter /
  11. CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt 
  12. AS  
  13.     PROCEDURE sp_record_print_by_record(p_cursor IN TYPE_REFCURSOR_WARE)
  14.     AS
  15.         rec_ware    ware%ROWTYPE;
  16.     BEGIN
  17.         LOOP
  18.             dbms_output.put_line('Try to fetch a row from the ref cursor .');
  19.             FETCH p_cursor INTO rec_ware;
  20.             EXIT WHEN p_cursor%NOTFOUND ;
  22.             dbms_output.put_line('Print a record :');
  23.             dbms_output.put_line('W_ID : ' || rec_ware.w_id 
  24.                 || ', W_YTD : ' || rec_ware.w_ytd 
  25.                 || ', W_TAX : ' || rec_ware.w_tax 
  26.                 || ', W_NAME : ' || rec_ware.w_name
  27.                 || ', W_STREET_1 : ' || rec_ware.w_street_1
  28.                 || ', W_STREET_2 : ' || rec_ware.w_street_2
  29.                 || ', W_CITY : ' || rec_ware.w_city
  30.                 || ', W_STATE : ' || rec_ware.w_state
  31.                 || ', W_ZIP : ' || rec_ware.w_zip )
  32.             ;
  33.             dbms_output.put_line('');
  34.         END LOOP;
  35.         dbms_output.put_line('Processed ' || p_cursor%ROWCOUNT || ' rows. ');
  36.     END;
  38.     PROCEDURE sp_record_print
  39.     IS
  40.         cursor_ware     TYPE_REFCURSOR_WARE ;
  41.     BEGIN
  43.         OPEN cursor_ware FOR SELECT * FROM ware ORDER BY w_id ;
  44.         dbms_output.put_line('Open a ref cursor using query at ware.');
  46.         sp_record_print_by_record(cursor_ware);
  48.         CLOSE cursor_ware;
  50.     EXCEPTION 
  51.         WHEN OTHERS THEN
  52.             dbms_output.put_line('Raise an unkown exception !');          
  53.   END ;
  55. END; 
  56. /
  57. delimiter ;
  59. obclient> set serveroutput on;
  60. Query OK, 0 rows affected (0.00 sec)
  62. obclient> call pkg_ware_mgmt.sp_record_print();