当前位置： 首页 > news >正文

【Oracle学习笔记】游标

news 来源：原创 2024/4/29 19:30:52

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

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

 

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

       【注】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

 

       【注】%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;

       【注】如果一个表有较多的列，使用%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的使用

4. cursor 与refcursor及sys_refcursor的区别 (转载)

转载于:https://www.cnblogs.com/yscit/p/10027788.html

相关文章：

  • Zilliqa 的设计构思 第3部分：使共识更有效
  • MMS-A01型三轴有线加速度传感器
  • 八年！性能提升10倍，阿里云HBase 2.0首发商用
  • Effective_STL 学习笔记（二十九） 需要一个一个字符输入时考虑使用 istreambuf_iterator...
  • HashMap源码解析
  • Linux文件类型
  • SSM框架面试题及答案整理
  • 笔记整理2
  • .NET处理HTTP请求
  • JAVA入门到精通-第85讲-url.port.socket编程原理
  • Java Socket实战之四：传输压缩对象
  • Python csv模块读取基本操作
  • PHP语法之如何获取变量的名称
  • vue-cli3搭建项目
  • Spark ShuffleDependency Shuffle依赖关系
  • .pyc 想到的一些问题
  • ES6核心特性
  • Fastjson的基本使用方法大全
  • gcc介绍及安装
  • Git的一些常用操作
  • If…else
  • JavaScript 奇技淫巧
  • javascript 总结（常用工具类的封装）
  • JS数组方法汇总
  • js写一个简单的选项卡
  • Netty 框架总结「ChannelHandler 及 EventLoop」
  • node-sass 安装卡在 node scripts/install.js 解决办法
  • Python利用正则抓取网页内容保存到本地
  • Python连接Oracle
  • swift基础之_对象 实例方法 对象方法。
  • vue脚手架vue-cli
  • 从零搭建Koa2 Server
  • 精益 React 学习指南 （Lean React）- 1.5 React 与 DOM
  • 名企6年Java程序员的工作总结，写给在迷茫中的你！
  • 七牛云假注销小指南
  • 入职第二天：使用koa搭建node server是种怎样的体验
  • 异常机制详解
  • 译有关态射的一切
  • 智能合约开发环境搭建及Hello World合约
  • 积累各种好的链接
  • 容器镜像
  • 我们雇佣了一只大猴子...
  • 曜石科技宣布获得千万级天使轮投资，全方面布局电竞产业链 ...
  • #define与typedef区别
  • #绘制圆心_R语言——绘制一个诚意满满的圆 祝你2021圆圆满满
  • （Redis使用系列） SpirngBoot中关于Redis的值的各种方式的存储与取出 三
  • （附源码）springboot车辆管理系统 毕业设计 031034
  • （附源码）springboot美食分享系统 毕业设计 612231
  • （附源码）springboot助农电商系统 毕业设计 081919
  • (数位dp) 算法竞赛入门到进阶 书本题集
  • (转)IOS中获取各种文件的目录路径的方法
  • .net core webapi 大文件上传到wwwroot文件夹
  • .NET delegate 委托 、 Event 事件,接口回调
  • .NET Framework 和 .NET Core 在默认情况下垃圾回收（GC）机制的不同（局部变量部分）
  • .NET 中使用 Mutex 进行跨越进程边界的同步