oracle where嵌套游标,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