1、概念
游标:从字面来理解就是游动的光标。用数据库语言来描述,游标是映射在结果集中一行数据上的位置实体,有了游标,用户就可以访问结果集中的任意一行数据了。将游标放置到某行后,即可对该行数据进行操作,最常见的操作是提取当前行数据。
2、分类
2.1、静态游标:显式游标、隐式游标
2.2、动态游标:强类型(限制)、弱类型(非限制)
3、属性
3.1、%ISOPEN 判断游标是否被打开,若打开,则%ISOPEN等于TRUE;否则等于FALSE。
3.2、%FOUND 判断游标所在行是否有效。若有效,则%FOUND等于TRUE;否则等于FALSE。
3.3、%NOTFOUND 与%FOUND相似,功能相反。
3.4、%ROWCOUNT 返回到当前位置为止,游标所读取的记录行数。
4、详细说明
4.1、显式游标:
Cursor 游标名(参数) [返回值类型] Is Select语句
OPEN 游标名(参数)
FETCH 游标名(参数) INTO 变量
CLOSE 游标名(参数)
4.2、隐式游标:
select **** into **** from **** where ****
for var_name in (select ****)
loop
end loop
for var_name in (游标名(参数))
loop
end loop
4.3、动态游标
动态游标,在运行的时候才能确定游标使用的查询。分类:
1.强类型(限制)REF CURSOR,规定返回类型
2.弱类型(非限制)REF CURSOR,不规定返回类型,可以获取任何结果集。
5、举例:
5.1、静态显式游标
2 declare
3 r_d2tlog d2t_Log % rowtype;
4 cursor cs_d2tlog is select * from d2t_log;
5 begin
6 open cs_d2tlog;
7 loop
8 fetch cs_d2tlog into r_d2tlog;
9 exit when cs_d2tlog % notfound;
10 dbms_output.put_line( ' activeid= ' || r_d2tlog.activeid || ' ;is already read counts= ' || cs_d2tlog % rowcount );
11 end loop;
12 close cs_d2tlog;
13 end ;
14 /
5.2、动态弱类型游标
2 r_d2tlog d2t_Log % rowtype;
3 type d2tlog is ref cursor ;
4 t_d2tlog d2tlog;
5 begin
6 open t_d2tlog for select * from d2t_log;
7 loop
8 fetch t_d2tlog into r_d2tlog;
9 exit when t_d2tlog % notfound;
10 dbms_output.put_line( ' activeid= ' || r_d2tlog.activeid || ' ;is already read counts= ' || t_d2tlog % rowcount );
11 end loop;
12 close t_d2tlog;
13 end ;
14 /
15
5.3、动态强类型游标
2 l_sqlstr varchar2 ( 100 );
3 type t_d2tlog is record(
4 activeid number ( 30 ),
5 writedate date
6 );
7 type c_d2tlog is ref cursor return t_d2tlog;
8 st_d2tlog t_d2tlog;
9 sc_d2tlog c_d2tlog;
10 begin
11 open sc_d2tlog for select activeid,writedate from d2t_log;
12 loop
13 fetch sc_d2tlog into st_d2tlog;
14 exit when sc_d2tlog % notfound;
15 dbms_output.put_line( ' activeid= ' || st_d2tlog.activeid || ' ;is already read counts= ' || sc_d2tlog % rowcount );
16 end loop;
17 close sc_d2tlog;
18
19 open sc_d2tlog for select activeid,writedate from d2t_log where activeid = 130208 ;
20 loop
21 fetch sc_d2tlog into st_d2tlog;
22 exit when sc_d2tlog % notfound;
23 dbms_output.put_line( ' activeid= ' || st_d2tlog.activeid || ' ;is already read counts= ' || sc_d2tlog % rowcount );
24 end loop;
25 close sc_d2tlog;
26
27 /*
28 l_sqlstr:='select activeid,writedate from d2t_log where activeid=124531';
29 open sc_d2tlog for l_sqlstr;
30 loop
31 fetch sc_d2tlog into st_d2tlog;
32 exit when sc_d2tlog%notfound;
33 dbms_output.put_line('activeid='||st_d2tlog.activeid||';is already read counts='||sc_d2tlog%rowcount);
34 end loop;
35 close sc_d2tlog;
36 */
37 end ;
38 /
5.4、动态强类型游标
2 r_d2tlog d2t_log % rowtype;
3 type cursor1 is ref cursor ;
4 type cursor2 is ref cursor return d2t_log % rowtype;
5
6 cs_cursor2 cursor2;
7 begin
8 dbms_output.put_line( ' dynamic cursor define ok ' );
9
10 open cs_cursor2 for select * from d2t_log;
11 loop
12 fetch cs_cursor2 into r_d2tlog;
13 exit when cs_cursor2 % notfound;
14 dbms_output.put_line( ' activeid= ' || r_d2tlog.activeid || ' ;is already read counts= ' || cs_cursor2 % rowcount );
15 end loop;
16 close cs_cursor2;
17 end ;
18 /
5.5、游标定义
2 r_d2tlog d2t_log % rowtype;
3 type cursor1 is ref cursor ;
4 type cursor2 is ref cursor return r_d2tlog;
5 begin
6 dbms_output.put_line( ' dynamic cursor define error ' );
7 end ;
8 /