PL/SQL - 嵌套游标 cursor()

cursor() 函数可以将一个查询结果集封装成一个类似 REF CURSOR 的游标变量，可以 FETCH 记录，也可以作为 REF CURSOR 类型的参数进行传递。它被称为“嵌套游标（nested cursor）”。

 

1. FETCH 记录

 

我们先看一下测试表 test1 和 test2 的数据：

 

SQL> select * from test1;

 

         A
----------
         1
         1
         2
         3

 

SQL> select * from test2;

 

        ID NAME
---------- ------------------------------------------
         1 yuechaotian1
         2 yuechaotian2
         3 yuechaotian3
         4 yuechaotian4
         5 yuechaotian5

 

我们可能会发出这样一个查询：

 

SQL> select id, name, (select a from test1 where a = test2.id)
  2    from test2;
select id, name, (select a from test1 where a = test2.id)
                  *
ERROR 位于第 1 行:
ORA-01427: 单行子查询返回多个行

 

因为表 test1 中有两条 a=1 的记录，所以这个查询执行失败了。但有时候我们确实需要这样的查询，怎么办呢？你可以试试 cursor() 函数：

 

SQL> set serveroutput on

SQL> declare
  2    cursor cur_test2 is
  3      select id, name, cursor(select a from test1 where a = test2.id)
  4        from test2;
  5    rec_test2 test2%rowtype;
  6
  7    cur_test1 sys_refcursor;
  8    rec_test1 test1%rowtype;
  9  begin
 10    open cur_test2;
 11    loop
 12      fetch cur_test2 into rec_test2.id, rec_test2.name, cur_test1;
 13      exit when cur_test2%notfound;
 14      dbms_output.put_line('rec_test2.id: ' || rec_test2.id || ' | rec_test2.name: ' || rec_test2.name);
 15      -- 这里不需要再显式 OPEN 游标 cur_test1，也不需要显式关闭
 16      loop
 17        fetch cur_test1 into rec_test1;
 18        exit when cur_test1%notfound;
 19        dbms_output.put_line( 'rec_test1.a: ' || rec_test1.a );
 20      end loop;
 21    end loop;

 22    close cur_test2;
 23  end;
 24  /
rec_test2.id: 1 | rec_test2.name: yuechaotian1
rec_test1.a: 1
rec_test1.a: 1
rec_test2.id: 2 | rec_test2.name: yuechaotian2
rec_test1.a: 2
rec_test2.id: 3 | rec_test2.name: yuechaotian3
rec_test1.a: 3
rec_test2.id: 4 | rec_test2.name: yuechaotian4
rec_test2.id: 5 | rec_test2.name: yuechaotian5

 

PL/SQL 过程已成功完成。

 

怎么样？达到你的目的了吧。我们再看一个嵌套了两个 cursor() 函数的例子：

 

SQL> declare
  2    -- 嵌套定义游标
  3    cursor cur_test2 is
  4      select id, name, cursor(select a, cursor(select * from dual)
  5                                          from test1
  6                                        where test1.a = test2.id)
  7        from test2;
  8    cur_test1 sys_refcursor;
  9    cur_dual  sys_refcursor;
 10    rec_test1 test1%rowtype;
 11    rec_test2 test2%rowtype;
 12    rec_dual varchar2(10);
 13  begin
 14    open cur_test2;
 15    loop
 16      fetch cur_test2 into rec_test2.id, rec_test2.name, cur_test1;
 17      exit when cur_test2%notfound;
 18      dbms_output.put_line( 'rec_test2.id: ' || rec_test2.id || '   rec_test2.name: ' || rec_test2.name);
 19      -- 这里不需要再显式 OPEN 游标 cur_test1，也不需要显式关闭
 20      loop
 21        fetch cur_test1 into rec_test1.a, cur_dual;
 22        exit when cur_test1%notfound;
 23        dbms_output.put_line( 'rec_test1.a: ' || rec_test1.a );
 24        -- 这里不需要再显式 OPEN 游标 cur_dual，也不需要显式关闭
 25        loop
 26          fetch cur_dual into rec_dual;
 27          exit when cur_dual%notfound;
 28          dbms_output.put_line( 'rec_dual: ' || rec_dual );
 29        end loop;
 30      end loop;
 31    end loop;
 32    close cur_test2;
 33  end;
 34  /
rec_test2.id: 1   rec_test2.name: yuechaotian1
rec_test1.a: 1
rec_dual: X
rec_test1.a: 1
rec_dual: X
rec_test2.id: 2   rec_test2.name: yuechaotian2
rec_test1.a: 2
rec_dual: X
rec_test2.id: 3   rec_test2.name: yuechaotian3
rec_test1.a: 3
rec_dual: X
rec_test2.id: 4   rec_test2.name: yuechaotian4
rec_test2.id: 5   rec_test2.name: yuechaotian5

 

PL/SQL 过程已成功完成。

 

由以上例子可以看出，嵌套游标是隐式打开的。它在以下情况下被关闭：

 

• 显式关闭
• 父游标再次执行时（比如，下一次循环前，会先关闭嵌套游标，再根据新数据重新打开）
• 父游标关闭时
• 父游标退出时
• fetch 父游标出错时

 

2. 传递参数

 

我们先看看测试表中的数据：

 

SQL> select * from test2;

 

        ID NAME
---------- ---------------------------
         1 yuechaotian1
         2 yuechaotian2
         3 yuechaotian3
         4 yuechaotian4
         5 yuechaotian5
         6 yuechaotian6
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10

 

已选择10行。

 

SQL> select * from test1 order by a;

 

         A
----------
         1
         1
         1
         2
         2
         3
         3
         4
         5
         6

 

已选择10行。

 

下面我要查询 test2 中的数据，查询条件是 test2.id 在 test1.a 中对应的记录数。比如我要查询表 test2.id 在 test1.a 中不存在的记录；查询表 test2.id 在test1.a 中存在1条的记录，存在2条的记录，存在3条的记录……，我可以使用嵌套游标实现：

 

SQL> create function f_count(cur_names in sys_refcursor) return number
  2  is
  3    v_name test2.name%type;
  4    n_count number(5) := 0;
  5  begin
  6    loop
  7      fetch cur_names into v_name;
  8      exit when cur_names%notfound;
  9      n_count := n_count + 1;
 10    end loop;
 11    return n_count;
 12  end f_count;
 13  /

 

函数已创建。

 

SQL> select id, name
  2    from test2
  3   where f_count( cursor( select a from test1 where a = test2.id ) ) = 1;

 

        ID NAME
---------- ---------------------------------------------------
         4 yuechaotian4
         5 yuechaotian5
         6 yuechaotian6

 

SQL> select id, name
  2    from test2
  3   where f_count( cursor( select a from test1 where a = test2.id ) ) = 0;

 

        ID NAME
---------- ---------------------------------------------------
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10

 

SQL> select id, name
  2    from test2
  3   where f_count( cursor( select a from test1 where a = test2.id ) ) = 2;

 

        ID NAME
---------- ---------------------------------------------------
         2 yuechaotian2
         3 yuechaotian3

 

SQL> select id, name
  2    from test2
  3   where f_count( cursor( select a from test1 where a = test2.id ) ) = 3;

 

        ID NAME
---------- ---------------------------------------------------
         1 yuechaotian1