Oracle存储过程返回游标实例详解
oracle存储过程—-游标(cursor)的学习
今天又学了一个新的概念Cursor ,即游标。
接上一篇,oracle存储过程—-存储过程执行简单的增删改查sql ,上一篇中,写到存储过程的查询sql ,当时在写到查询的时候,忽然不知道怎么对查询结果是多条的数据,如何操作遍历呢。
游标(Cursor)的定义与作用
游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
不过我是新手,我理解的是,游标就像我们查询数据返回的集合类型,比如List ,我就把它当做一个引用类型,它指向了内存区域中的数据结果集。
游标(Cursor)的应用场景
就以我现在理解的来说,游标既然像查询结果集list ,那使用cursor 的情况,肯定会有下边这两种情况,才可能使用到cursor :
需要依据查询到的结果集,作为条件,进行下一步的查询。
需要在结果集某个位置上,满足某种条件时,对数据进行不同的修改。
游标(Cursor)的使用考虑
上边的两种情况,都会涉及到遍历数据(存储过程 里如何遍历数据,下一篇再写),所以游标的使用也需要注意:
比如,你的查询结果集很大,首先占了很多的内存,其次,遍历这么庞大的结果集,效率也肯定高不了。
另外,如果还有其他方式解决问题的话,那就不要选择使用游标(网上搜来的),我没测试过效率,但是游标在使用中,的确有,定义游标、打开游标、关闭游标,这些操作,这样也肯定快不了多少。
游标(Cursor)的类别
游标有两种,显示游标和隐式游标。
隐式游标:
上一篇中用到的SELECT…INTO…查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标。
DML操作和单行SELECT语句会使用隐式游标,它们是:
插入操作:INSERT。
更新操作:UPDATE。
删除操作:DELETE。
单行查询操作:SELECT … INTO …。
(2018-08-19补充)当然,隐式游标还有一种写法,如下,并没有定义游标:
create or replace procedure test_cryptic_procedure as begin for cryptic_rec in ( select * from ly_ds ) loop dbms_output.put_line('名称:'||cryptic_rec.ly_mc||' 年龄:'||cryptic_rec.ly_nl); end loop; end;
执行语句
begin test_cryptic_procedure; end;
结果
名称:eee 年龄:23 名称:王五 年龄:22 名称:赵六六 年龄:29 名称:李四 年龄:20
提示一下:因为隐式游标默认打开游标,但是如果调用%ISOPEN 却返回false 。这个注意一下,如果还没懂%ISOPEN 啥意思,那暂且不要管这个提示了。
显式游标:
显示游标还分为静态游标和动态游标 。显示游标的写法会在存储过程中定义Cursor ,并且一般都有固定的四个步骤:声明游标 、打开游标 、提取数据 、关闭游标 。显式游标打开后,必须显式地关闭。游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
隐式游标其实默认已经打开游标,并在执行完成后关闭游标释放资源。
游标(Cursor
)的例子
下边是我学习后写的第一个游标的例子。不像上边那个隐式游标的例子连游标都没有定义,下边这个是定义了游标,但是没有去打开,for
循环会默认打开游标的。
create or replace procedure test_select3_procedure (sex varchar) AS --游标的定义 Cursor test_cursor is select id,ly_mc,LY_NB,ly_nl from ly_ds where LY_NB=sex; cur test_cursor%rowtype; --游标的类型,我理解类似于list的泛型 BEGIN for cur in test_cursor loop exit when test_cursor%notfound; dbms_output.put_line('数据是:'||cur.id||'_'||cur.ly_mc||'_'||cur.LY_NB||'_'||cur.ly_nl); end loop; END;
上边,首先看Cursor test_cursor is 这一行,它的意思是定义一个游标,test_cursor 为你要定义的名字,而is 后边是一个sql,也就是说当前这个sql的查询结果,赋值给游标test_cursor 。
然后,往下,接着cur test_cursor%rowtype ,这个是定义了一个类型,而这个类型,即是游标test_cursor 的返回结果类型,类型的名字为cur 。有点类似于java语言 中List 集合中的一个泛型 。
另外,关于for 是一个循环的写法,for cur in test_cursor ,即,从游标test_cursor 中取出一个结果cur 。
还有,注意,loop 和end loop 这是一个循环的开始标志和结束标志,但它俩兄弟是一个很执着的循环,如果没有定义退出条件,永远不会退出的,所以在上边的循环里边,有了退出条件exit when test_cursor%notfound; ,即当游标test_cursor 中没有数据了,就退出循环。
当然loop 循环的退出,发生下边的情况,才能退出:
有exit,并满足条件后退出。
loop中抛出了异常。
存在goto 标识。
最后,可能大家注意到,上边并没有显示游标中一定有的四个步骤,
声明游标 、打开游标 、提取数据 、关闭游标 ,那是因为for 循环这样的遍历会自动给我们打开游标,并在结束后关闭游标,下边会学习其他的遍历方法,但是都没有for 这样智能,所以觉得for 循环游标,是个很好的选择。
原文链接:https://blog.csdn.net/wohaqiyi/article/details/81607172