PL/SQL - 嵌套游标 cursor

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