【Oracle学习笔记】游标

程序员文章站 2022-05-07 13:54:38

1. 分类常见的游标可分为显示游标、隐式游标、静态游标和动态游标四大类： 1.1 显示游标显式是相对与隐式cursor而言的，就是有一个明确的声明的cursor。显式游标的声明类似如下： delcare 游标关键字cursor 游标名 is 数据集; 游标从declare、open、fetch、 ......

1. 分类

常见的游标可分为显示游标、隐式游标、静态游标和动态游标四大类：

1.1 显示游标

显式是相对与隐式cursor而言的，就是有一个明确的声明的cursor。显式游标的声明类似如下：

delcare 游标关键字cursor 游标名 is 数据集;

游标从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用她。下面看一个简单的静态显式cursor的示例：

 1 declare
 2   cursor get_subid(pid a_test.parentid%type) is
 3     select subid from a_test where parentid = pid;
 4   v_subid a_test.subid%type;
 5 begin
 6   open get_subid(1);
 7   loop
 8     fetch get_subid
 9       into v_subid;
10     exit when get_subid%notfound;
11     dbms_output.put_line(v_subid);
12   end loop;
13   close get_subid;
14   dbms_output.put_line('--------这是分割线----------');
15   open get_subid(4);
16   loop
17     fetch get_subid
18       into v_subid;
19     exit when get_subid%notfound;
20     dbms_output.put_line(v_subid);
21   end loop;
22   close get_subid;
23 end;

view code

【Oracle学习笔记】游标

1.2 隐式游标

隐式cursor当然是相对于显式而言的，就是没有明确的cursor的declare。在oracle的pl/sql中，所有的dml操作都被oracle内部解析为一个cursor名为sql的隐式游标，只是对我们透明罢了。

begin
  for rec in (select user, sysdate from dual) loop
    dbms_output.put_line(rec.user || '：' ||
                         to_char(rec.sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  end loop;
end;

view code

1.3 静态游标

静态游标是相对于动态游标而言的，普通显示定义的游标都是静态游标。

1.4 动态游标

动态游标是相对于静态游标而言的，要等到运行时才知道结果集查询语句是什么样的。

 1 declare
 2   type atest_rec is record(
 3     pid   a_test.parentid%type,
 4     subid a_test.subid%type);
 5 
 6   type app_ref_cur_type is ref cursor return atest_rec;
 7   my_cur app_ref_cur_type;
 8   my_rec atest_rec;
 9 begin
10 
11   if (to_char(sysdate, 'dd') = 30) then
12     open my_cur for
13       select parentid, subid from a_test where parentid = 1;
14   else
15     open my_cur for
16       select parentid, subid from a_test where parentid = 2;
17   end if;
18 
19   fetch my_cur
20     into my_rec;
21   while my_cur%found loop
22     --当前不是30号 执行else 结果：
23     --2#4
24     --2#5
25     dbms_output.put_line(my_rec.pid || '#' || my_rec.subid);
26     fetch my_cur
27       into my_rec;
28   end loop;
29   close my_cur;
30 
31 end;

view code

　　【注】record为记录数据类型。它类似于c语言中的结构数据类型(structure)，pl/sql提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法，即record复合数据类型。在使用记录数据类型变量时，需要在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。

　　定义记录数据类型的语法如下：

1 type record_name is record(
2 v1  data_type1 [not null][:=default_value],
3 v2  data_type2 [not null][:=default_value],
4 vn  data_typen [not null][:=default_value]);

由上面的例子，可知cursor与ref cursor大致有以下几点区别：

1）pl/sql静态游标不能返回到客户端，只有pl/sql才能利用它。动态游标能够被返回到客户端，这就是从oracle的存储过程返回结果集的方式。

2）pl/sql静态游标可以是全局的，而动态游标则不是，不能在包说明或包体中的过程或函数之外定义动态游标。

3）动态游标可以从子例程传递到子例程，而普通游标则不能。如果要共享静态光标，必须在包说明或包体中把它定义为全局光标。因为使用全局变量通常不是一种很好的编码习惯，因此可以用动态游标来共享pl/sql中的游标，无需混合使用全局变量。

4）静态光标比动态游标标效率要高，所以在使用游标时首先考虑使用静态游标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。这个就因人因事而定吧。

另外，在oracle9i以后系统定义的一个refcursor，这是一个弱类型的游标，相当于.net中用户var声明的变量，主要用在过程中返回结果集。

 1 --创建存储过程
 2 create or replace procedure sp_get_subid(pid       ina_test.parentid%type,
 3  out_subid out sys_refcursor) as
 4 begin
 5   open out_subid for
 6     select * from a_test where parentid = pid;
 7 exception
 8   when others then
 9     raise_application_error(-20101, 'error in sp_get_subid' || sqlcode);
10 end sp_get_subid;
11 
12 --调用存储过程
13 declare
14   v_rent_rows sys_refcursor;
15   v_rent_row  a_test%rowtype;
16 begin
17   sp_get_subid(1, v_rent_rows);
18   dbms_output.put_line('parentid subid');
19   loop
20     fetch v _rows
21       into v _row;
22     exit when v _rows%notfound;
23     dbms_output.put_line(v _row.parentid || ' ' || v _row.subid);
24   end loop;
25   close v_rows;
26 end;

view code

【Oracle学习笔记】游标

2. 属性

2.1 说明

1 %found： bool - true if >1 row returned
2 %notfound：bool - true if 0 rows returned
3 %isopen： bool - true if cursor still open
4 %rowcount：int - number of rows affected by last sql statement

【注】no_data_found和%notfound的用法是有区别的，小结如下：

1）select . . . into 语句触发 no_data_found；

2）当一个显式游标的 where 子句未找到时触发 %notfound；

3）当update或delete 语句的where 子句未找到时触发 sql%notfound；

4）在游标的提取(fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found

2.2 示例

2.2.1 示例一：

 1 begin
 2 
 3   update a_test set subid = '15' where parentid = 4;
 4 
 5   --sql%isopen是一个布尔值，如果游标打开，则为true，如果游标关闭，则为false.
 6 
 7   if sql%isopen then
 8 
 9     dbms_output.put_line('openging');
10 
11   else
12 
13     dbms_output.put_line('closing'); --对于隐式游标而言sql%isopen总是false，这是因为隐式游标在dml语句执行时打开，结束时就立即关闭。
14 
15   end if;
16 
17   if sql%found then
18 
19     dbms_output.put_line('游标指向了有效行'); --判断游标是否指向有效行
20 
21   else
22 
23     dbms_output.put_line('sorry');
24 
25   end if;
26 
27   if sql%notfound then
28 
29     dbms_output.put_line('also sorry');
30 
31   else
32 
33     dbms_output.put_line('haha');
34 
35   end if;
36 
37   dbms_output.put_line(sql%rowcount);
38 
39 exception
40 
41   when no_data_found then
42 
43     dbms_output.put_line('sorry no data');
44 
45   when too_many_rows then
46 
47     dbms_output.put_line('too many rows');
48 
49 end;

view code

【Oracle学习笔记】游标

【注】sql语言分为ddl（data definition language，数据定义语言，用来维护数据对象）和dml（data manipulation language，数据操作语言，用于增删改表中数据，dml是伴随tcl事务控制的）。

2.2.2 示例二：

 1 declare
 2 
 3   empnumber a_test.parentid%type;
 4 
 5   empname   a_test.subid%type;
 6 
 7 begin
 8 
 9   if sql%isopen then
10 
11     dbms_output.put_line('cursor is opinging');
12 
13   else
14 
15     dbms_output.put_line('cursor is close');
16 
17   end if;
18 
19   if sql%notfound then
20 
21     dbms_output.put_line('no value');
22 
23   else
24 
25     dbms_output.put_line(empnumber); --没有赋值，输出为空白
26 
27   end if;
28 
29   dbms_output.put_line(sql%rowcount); --没有记录，输出为空白
30 
31   dbms_output.put_line('-------------');
32 
33  
34 
35   select parentid, subid into empnumber, empname from a_test where parentid = 4;
36 
37   dbms_output.put_line(sql%rowcount);
38 
39  
40 
41   if sql%isopen then
42 
43     dbms_output.put_line('cursor is opinging');
44 
45   else
46 
47     dbms_output.put_line('cursor is closing');
48 
49   end if;
50 
51   if sql%notfound then
52 
53     dbms_output.put_line('no value');
54 
55   else
56 
57     dbms_output.put_line(empnumber);
58 
59   end if;
60 
61 exception
62 
63   when no_data_found then
64 
65     dbms_output.put_line('no value');
66 
67   when too_many_rows then
68 
69     dbms_output.put_line('too many rows');
70 
71 end;

view code

【Oracle学习笔记】游标

【注】%type是oracle提供的一种数据定义方法，为的是使一个新定义的变量与另一个已经定义了的变量(通常是表的某一列)的数据类型保持一致，当被参照的那个变量的数据类型发生改变时,那么这个新定义的变量的数据类型也会随之发生改变。当不能确切的知道那个变量的类型是,就采用这种方法来定义变量的数据类型。

3. 操作

3.1 for循环游标

 1  --声明游标：delcare 游标关键字cursor 游标名 is 数据集;
 2 
 3 declare
 4 
 5 cursorc_list is
 6 
 7 selectp.fid, max(t.exp) exp
 8 
 9 from view_pilot p
10 
11 left join io_fms_billofhealth t
12 
13 ont.phr = p.fjobnumber
14 
15 group by p.fid;
16 
17 
18 --for循环，类似.net中的foreach方法：
19 
20 --begin
21 
22 --for 元素名 in 游标名 循环关键字loop
23 
24 --执行语句;
25 
26 --endloop;
27 
28 begin
29 
30   for c_row in c_list loop
31 
32     update alarm_pilotintelligence
33 
34        set c = getalarmstatebyexp(c_row.exp)
35 
36      where isprimary = 0
37 
38        and pid = c_row.fid;
39 
40 end loop;

3.2 fetch游标

 1 --定义游标
 2 
 3 declare
 4 
 5   cursor c_job is
 6 
 7     select * from a_test order by parentid;
 8 
 9   --定义一个游标变量
10 
11   c_row c_job%rowtype;
12 
13 begin
14 
15   --使用的时候必须要明确的打开游标
16 
17   open c_job;
18 
19   --开始循环标记
20 
21   loop
22 
23     --提取一行数据到c_row，相当ado.net中的sqldatareader.read()方法
24 
25     fetch c_job into c_row;
26 
27     --判读是否提取到值，没取到值就退出
28 
29     --取到值c_job%notfound 是false
30 
31     --取不到值c_job%notfound 是true
32 
33     exit when c_job%notfound;
34 
35     dbms_output.put_line(c_row.parentid || '-' || c_row.subid); --用于输出，这是oracle中最基础的方法之一
36 
37     --结束循环，并关闭游标
38 
39   end loop;
40 
41   close c_job;
42 
43 end;

【Oracle学习笔记】游标

【注】如果一个表有较多的列，使用%rowtype来定义一个表示表中一行记录的变量，比分别使用%type来定义表示表中各个列的变量要简洁得多，并且不容易遗漏、出错。这样会增加程序的可维护性。当不能确切地知道被参照的那个表的结构及其数据类型时，可以采用这种方法定义变量的数据类型。

3.3 while循环游标

　　上面【示例二】中的结果还可以通过while循环与fetch相结合来实现：

 1 --定义游标
 2 
 3 declare
 4 
 5   cursor c_job is
 6 
 7     select * from a_test order by parentid;
 8 
 9   --定义一个游标变量
10 
11   c_row c_job%rowtype;
12 
13 begin
14 
15   --使用的时候必须要明确的打开游标
16 
17   open c_job;
18 
19   --开始循环标记
20 
21   --提取一行数据到c_row，相当ado.net中的sqldatareader.read()方法
22 
23   fetch c_job
24 
25     into c_row;
26 
27   --while循环
28 
29   while c_job%found loop
30 
31     dbms_output.put_line(c_row.parentid || '-' || c_row.subid);
32 
33     fetch c_job
34 
35       into c_row;
36 
37     --结束循环，并关闭游标
38 
39   end loop;
40 
41   close c_job;
42 
43 end;

参考资料：

1．oracle 游标使用全解

2．游标属性sql%found, sql%notfound, sql%rowcount, sql%isopen

3．oracle中%type和%rowtype的使用

上一篇：就剩价格了 realme真我X50曝光信息汇总：1月7日发

下一篇：小米10/10 Pro曝光：不止首发骁龙865 还有大容量电池

【Oracle学习笔记】游标

1. 分类

1.1 显示游标

1.2 隐式游标

1.3 静态游标

1.4 动态游标

2. 属性

2.1 说明

2.2 示例

2.2.1 示例一：

2.2.2 示例二：

3. 操作

3.1 for循环游标

3.2 fetch游标

3.3 while循环游标

C/C+学习笔记：10个免费的c/c+集成开发环境（IDE）

Oracle 数组的学习小知识也要积累，养成好的学习态度

Oracle 数据使用游标

C#.NET学习笔记5 C#中的条件编译

mysql学习笔记之基础知识

Android学习笔记之AndroidManifest.xml文件解析(详解)

Laravel学习笔记之Artisan命令生成自定义模板的方法

oracle 触发器学习笔记

python学习笔记之调用eval函数出现invalid syntax错误问题

Spring学习笔记之bean的基础知识

【Oracle学习笔记】游标

1. 分类

1.1 显示游标

1.2 隐式游标

1.3 静态游标

1.4 动态游标

2. 属性

2.1 说明

2.2 示例

2.2.1 示例一：

2.2.2 示例二：

3. 操作

3.1 for循环游标

3.2 fetch游标

3.3 while循环游标

C/C+学习笔记：10个免费的c/c+集成开发环境（IDE）

Oracle 数组的学习 小知识也要积累，养成好的学习态度

Oracle 数据 使用游标

C#.NET学习笔记5 C#中的条件编译

mysql学习笔记之基础知识

Android学习笔记之AndroidManifest.xml文件解析(详解)

Laravel学习笔记之Artisan命令生成自定义模板的方法

oracle 触发器 学习笔记

python学习笔记之调用eval函数出现invalid syntax错误问题

Spring学习笔记之bean的基础知识

Oracle 数组的学习小知识也要积累，养成好的学习态度

Oracle 数据使用游标

oracle 触发器学习笔记