PL/SQL - 嵌套游标 cursor - 网络管理 - TechWeb-技术社区

TechWeb-技术社区 » 网络管理 » PL/SQL - 嵌套游标 cursor

‹‹ 上一主题 | 下一主题 ››

淋雨了

TW高级会员

UID 80710
精华 29
积分 842
帖子 389
阅读权限 50
注册 2006-8-8
状态离线

#1

发表于 2008-7-4 15:10 资料短消息加为好友

PL/SQL - 嵌套游标 cursor

cursor() 函数可以将一个查询结果集封装成一个类似 REF CURSOR 的游标变量，可以 FETCH 记录，也可以作为 REF CURSOR 类型的参数进行传递。它被称为“嵌套游标（nested cursor）”。*T%d3z!F#w"h,t Z
      1. FETCH 记录
      我们先看一下测试表 test1 和 test2 的数据：
+K7E9o3r%R:w,}
SQL> select * from test1;

      ATechWeb-技术社区8^7f#v'_.D%G Q;v
----------
      1程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛!W2{3p4^"F&C*o
      1tech.techweb.com.cn-`"B/x7_:R/_,F#|
      2tech.techweb.com.cn6C(}9a;\%{&`
      3tech.techweb.com.cn!w)@'?!g/A/m*p

SQL> select * from test2;
*I&I.O)}/H4f
      ID NAMETechWeb-技术社区%d6z6V(L/Y$[
---------- ------------------------------------------
      1 yuechaotian1TechWeb-技术社区+@6g/_0O%V:P%t&{
      2 yuechaotian2
      3 yuechaotian3$Q6k3O&|3G&m"]5k
      4 yuechaotian4TechWeb-技术社区4e:I4e!r.[3J;c9l7H$y+g"V
      5 yuechaotian5 TechWeb-技术社区0?*}.q;X8@+a7t/R
/e f#m4k(b)U&i!Y9Z4[
(@$y4I/|7Y7A&d0Q'N3V
　　我们可能会发出这样一个查询：#R:a%[2a.]"F)V

SQL> select id, name, (select a from test1 where a = test2.id)4E6e.]2l+K;W!H%R
  2 from test2;
select id, name, (select a from test1 where a = test2.id)
               *!t$O(a"E:k6j
ERROR 位于第 1 行:6e6I"b1B5{)n.q:W
ORA-01427: 单行子查询返回多个行

程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛'Q,r0e0z'~#R
　　因为表 test1 中有两条 a=1 的记录，所以这个查询执行失败了。但有时候我们确实需要这样的查询，怎么办呢？你可以试试 cursor() 函数：TechWeb-技术社区 q%e8P'[#]0r0e8N
程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛$J5v4[&s a0R:y
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;程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛:k"M)k6p8x&?
  9  begin&S:N c5d)q!O
10 open cur_test2; S+Y5^"_'C'@
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);)e1H O4b;{#p#W0t,T-P
15    -- 这里不需要再显式 OPEN 游标 cur_test1，也不需要显式关闭,f0U3@$];S J6j8v,z;K
16    loopTechWeb-技术社区)S1H5})q(z&Y8H
17       fetch cur_test1 into rec_test1;
18       exit when cur_test1%notfound;程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛%n;F'\4L2x4G;Q
19       dbms_output.put_line( 'rec_test1.a: ' || rec_test1.a );
20    end loop;
21 end loop;TechWeb-技术社区:]8h,A3L;X(y;V:?'H
22 close cur_test2;2G!W"[,W*_&}
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程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛7H9E9h&h7O"h*N.u
rec_test2.id: 5 | rec_test2.name: yuechaotian5
,k3q2q6n2E;]0X3W
PL/SQL 过程已成功完成。

引用回复

淋雨了

TW高级会员

UID 80710
精华 29
积分 842
帖子 389
阅读权限 50
注册 2006-8-8
状态离线

#2

发表于 2008-7-4 15:10 资料短消息加为好友

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

SQL> declare程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛9H3M,])b"[6?$p6n
  2 -- 嵌套定义游标
  3 cursor cur_test2 is&_,v7D8P.G+l G/V }4o
  4    select id, name, cursor(select a, cursor(select * from dual)
  5                                        from test1TechWeb-技术社区.x"?!N%[*q
  6                                     where test1.a = test2.id)TechWeb-技术社区$?3Q2l3[ Z8_/^&z+D&p
  7       from test2;
  8 cur_test1 sys_refcursor;
  9 cur_dual  sys_refcursor;
10 rec_test1 test1%rowtype;/g){)a4v$}-X%W+^
11 rec_test2 test2%rowtype;"M$j8[;Z.l(M&s
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);tech.techweb.com.cn!r0N$`:w&p7F8L
19    -- 这里不需要再显式 OPEN 游标 cur_test1，也不需要显式关闭(]:x+^2A.e.c
20    looptech.techweb.com.cn"?;v;Z'h*g5^5e
21       fetch cur_test1 into rec_test1.a, cur_dual;TechWeb-技术社区:s.d"T)n!Y#b
22       exit when cur_test1%notfound;
23       dbms_output.put_line( 'rec_test1.a: ' || rec_test1.a );tech.techweb.com.cn.o8m'q%|-J*}2e4\
24       -- 这里不需要再显式 OPEN 游标 cur_dual，也不需要显式关闭
25       loopTechWeb-技术社区4l$p8m:@2U
26       fetch cur_dual into rec_dual;TechWeb-技术社区%U(z;C;a&H c1n
27       exit when cur_dual%notfound;
28       dbms_output.put_line( 'rec_dual: ' || rec_dual );
29       end loop;tech.techweb.com.cn"P$y$W;t7b
30    end loop;
31 end loop;%g)G+v#?5C!{9{1A0r
32 close cur_test2;TechWeb-技术社区9m*N0D'C-M0P0l
33  end;-M$?2V;_,v W:I
34  /,u6X4r)b4N(c*N9J
rec_test2.id: 1 rec_test2.name: yuechaotian1+Z"u4~%y$r6?&\*o1a7A
rec_test1.a: 1TechWeb-技术社区(N3Z3J6T$j.P1j(~'i
rec_dual: X
rec_test1.a: 1:`&H3u-P0Z*x0F
rec_dual: X
rec_test2.id: 2 rec_test2.name: yuechaotian27~7g,y5o&O3L+h7I:W*E
rec_test1.a: 2,o%x:` m%M
rec_dual: X(t'p3o6t6k1Q0F
rec_test2.id: 3 rec_test2.name: yuechaotian3程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛-s+d3~3J1W,V
rec_test1.a: 3&@5k"Y"v6Y,J+E-v
rec_dual: X
rec_test2.id: 4 rec_test2.name: yuechaotian4'B*R9t*\2y$k3x
rec_test2.id: 5 rec_test2.name: yuechaotian5

PL/SQL 过程已成功完成。 2T3l9A!I1b7A;T-\

tech.techweb.com.cn$C*v+n4e9N4@/~1o6{0Q-r#u
      由以上例子可以看出，嵌套游标是隐式打开的。它在以下情况下被关闭：7n3v/l;v%z0\;b!K8i
      显式关闭
      　　父游标再次执行时（比如，下一次循环前，会先关闭嵌套游标，再根据新数据重新打开）
      　　父游标关闭时
      　　父游标退出时
      fetch 父游标出错时
      2. 传递参数程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛3b)j.d5q)N:z+A
      我们先看看测试表中的数据：TechWeb-技术社区%p.? q"a1S$P4R:N;p

SQL> select * from test2;
TechWeb-技术社区(G.I"R2G1u'D$z
      ID NAME程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛#b.G/z'{7g(|
---------- ---------------------------
      1 yuechaotian1TechWeb-技术社区)X9V'V:D;X9}.\4y5i!u"n!h
      2 yuechaotian2:Y1b)F.`,~
      3 yuechaotian3程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛'y(N1t)| s;\)H3}-r
      4 yuechaotian4TechWeb-技术社区,R-O'_$P'Z
      5 yuechaotian5%C#o"X8`.k0m*j${"^
      6 yuechaotian6
      7 yuechaotian7TechWeb-技术社区7O:t:h.?&F-L%Q9b
      8 yuechaotian8
      9 yuechaotian9
      10 yuechaotian10

已选择10行。

SQL> select * from test1 order by a;
!O"B,C&L!a1H'@,X)w
      A7F3P)B'|&\9o9P-W(n#s
----------1p2F9?(K)g/e0I9]9}
      1
      1tech.techweb.com.cn2Q)Z$V#b,O-A,r
      1%h+j%z6J(e.u7|6N7P
      2
      2TechWeb-技术社区)t!|%K5m&f)C:f'X x
      3tech.techweb.com.cn3a/u-d'U6^.T"m$F)L
      3"Q(C+H/y3f'M7E
      4
      5
      6程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛,B'v5c%p#c0s.d1h
8Z"R&N)G8{%O
已选择10行。

引用回复

淋雨了

TW高级会员

UID 80710
精华 29
积分 842
帖子 389
阅读权限 50
注册 2006-8-8
状态离线

#3

发表于 2008-7-4 15:10 资料短消息加为好友

　　下面我要查询 test2 中的数据，查询条件是 test2.id 在 test1.a 中对应的记录数。比如我要查询表 test2.id 在 test1.a 中不存在的记录；查询表 test2.id 在test1.a 中存在1条的记录，存在2条的记录，存在3条的记录……，我可以使用嵌套游标实现：,M z3I:L1t#E6x
6F2c:C&R5I
SQL> create function f_count(cur_names in sys_refcursor) return number(D8z%y3e;k4y1L f1G
  2  is
  3 v_name test2.name%type;
  4 n_count number(5) := 0;
  5  begin3?2F$G6f,A"^0X
  6 loop%~'W8y:E*F%s
  7    fetch cur_names into v_name;
  8    exit when cur_names%notfound;
  9    n_count := n_count + 1;
10 end loop;#F+S4I#P)i"Q3?#t:f9G
11 return n_count;
12  end f_count;+n'?4L9S M&i
13  /

函数已创建。#m%~%n9a.|,G$h
"r4N"B8I-f,? K-G*T
SQL> select id, name.s+z'd5i3d,M'~:U7{
  2 from test2
  3 where f_count( cursor( select a from test1 where a = test2.id ) ) = 1;
TechWeb-技术社区3W2L0~:h8R#f7a;K%~9m
      ID NAME3A4~-T*L*j/a
---------- ---------------------------------------------------
      4 yuechaotian4程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛 l5_7B&K$y&f
      5 yuechaotian5程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛5c#p1}:i9J)];i#A&n
      6 yuechaotian6

SQL> select id, name
  2 from test2
  3 where f_count( cursor( select a from test1 where a = test2.id ) ) = 0;tech.techweb.com.cn.i$Z"U:Q6p,K/b$b4L

      ID NAME
---------- ---------------------------------------------------TechWeb-技术社区 E"O%U$O+r.z
      7 yuechaotian7(n(s L&J1F3r/u
      8 yuechaotian8,c0B6D$H3M8k5?
      9 yuechaotian9
      10 yuechaotian10
(L;U w-{)B3{ l-S/`&v+}
SQL> select id, name
  2 from test2
  3 where f_count( cursor( select a from test1 where a = test2.id ) ) = 2;+[3r)y a1L&I

      ID NAME&P ~9X4I0n)@6q/a;i1B
---------- ---------------------------------------------------
      2 yuechaotian2
      3 yuechaotian3程序开发，操作系统，服务器，源码下载，Linux,Unix,BSD,PHP,Apach,asp,下载,源码,黑客,安全,技术社区,技术论坛'F0^7I2N:j/W2s/t:P,q&c

SQL> select id, name
  2 from test2;`,m:f%C/]0H+n"n
  3 where f_count( cursor( select a from test1 where a = test2.id ) ) = 3;,F1c2u-q0L;k
tech.techweb.com.cn0Z _,q4T-d.N/c x&^
      ID NAME%g7h+K6D'R4A
---------- ---------------------------------------------------tech.techweb.com.cn4_"h3h"w(G$M8~-v
      1 yuechaotian1

引用回复

当前时区 GMT+8, 现在时间是 2008-9-6 05:53
京ICP证060517号