oracle存储过程----游标(cursor）的学习

红太狼 2023-10-17 23:21 43阅读 0赞

##### oracle存储过程—-游标(cursor）的学习 #####

今天又学了一个新的概念`Cursor` ，即游标。  
  接上一篇，[oracle存储过程—-存储过程执行简单的增删改查sql][oracle_-_sql] ，上一篇中，写到`存储过程的查询sql` ，当时在写到查询的时候，忽然不知道怎么对查询结果是多条的数据，如何操作遍历呢。

###### 游标（`Cursor`）的定义与作用 ######

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。  
  不过我是新手，我理解的是，**游标就像我们查询数据返回的集合类型，比如`List` ，我就把它当做一个引用类型，它指向了内存区域中的数据结果集**。

###### 游标（`Cursor`）的应用场景 ######

就以我现在理解的来说，游标既然像查询结果集`list` ，那使用`cursor` 的情况，肯定会有下边这两种情况，才可能使用到`cursor` ：

1.  需要依据查询到的结果集，作为条件，进行下一步的查询。
2.  需要在结果集某个位置上，满足某种条件时，对数据进行不同的修改。

###### 游标（`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;

如下方式执行：

set serveroutput on;
    begin
     test_cryptic_procedure;
    end;

将会看到如下的结果：

匿名块已完成
    名称：eee 年龄：23
    名称：王五 年龄：22
    名称：赵六六 年龄：29
    名称：李四 年龄：20

**提示一下：**因为隐式游标默认打开游标，但是如果调用`%ISOPEN` 却返回`false` 。这个注意一下，如果还没懂`%ISOPEN` 啥意思，那暂且不要管这个提示了。

**显式游标**：  
  显示游标还分为`静态游标`和`动态游标` 。显示游标的写法会在存储过程中定义`Cursor` ,并且一般都有固定的四个步骤：`声明游标` 、`打开游标` 、`提取数据` 、`关闭游标` 。显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。  
  隐式游标其实默认已经打开游标，并在执行完成后关闭游标释放资源。  
 &ems;如果你想详细了解一下`游标的概念` ，我是参考了下边这两篇文章：

*  [oracle中游标详细用法][oracle]
 *  [【Oracle】PL/SQL 显式游标、隐式游标、动态游标][Oracle_PL_SQL]

###### 游标（`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` 循环的退出，发生下边的情况，才能退出：

1.  有exit,并满足条件后退出。
2.  loop中抛出了异常。
3.  存在`goto` 标识。

最后，可能大家注意到，上边并没有显示游标中一定有的四个步骤，  
`声明游标` 、`打开游标` 、`提取数据` 、`关闭游标` ，那是因为`for` 循环这样的遍历会自动给我们打开游标，并在结束后关闭游标，下边会学习其他的遍历方法，但是都没有`for` 这样智能，所以觉得`for` 循环游标，是个很好的选择。

######   执行上边的存储过程： ######

set serveroutput on;
    execute test_select3_procedure('女');

会得到如下的遍历结果，正是我根据条件`女`查询到的结果集。

PROCEDURE TEST_SELECT3_PROCEDURE 已编译
    匿名块已完成
    数据是:2_王五_女_22
    数据是:3_赵六六_女_29
    数据是:4_李四_女_20

下一篇[oracle存储过程—-遍历游标的方法（for、fetch、while）][oracle_-_for_fetch_while]

[oracle_-_sql]: https://blog.csdn.net/wohaqiyi/article/details/81529997
[oracle]: https://blog.csdn.net/liyong199012/article/details/8948952
[Oracle_PL_SQL]: https://blog.csdn.net/vking_wang/article/details/9113401
[oracle_-_for_fetch_while]: https://blog.csdn.net/wohaqiyi/article/details/81631257