Oracle（七）游标

最新推荐文章于 2024-08-01 07:10:05 发布

抠脚的鱼

最新推荐文章于 2024-08-01 07:10:05 发布

阅读量391

点赞数

分类专栏： Oracle 文章标签： Oracle 游标

本文链接：https://blog.csdn.net/sky_format/article/details/89365971

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

基本概念

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。

游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。

对于SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

如果要提取多行数据，就要定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

关系数据库管理系统实质是面向集合的，因此我们必须借助于游标来进行面向单条记录的数据处理。

隐式游标

执行一个SQL语句时，ORACLE会自动创建一个隐式游标，这个游标是内存中处理该语句的工作区域，隐式游标主要处理UPDATE 、DELETE的执行结果，特殊情况下也处理SELECT的查询结果，隐式游标也有属性，当使用隐式游标的属性时，需要加上它的默认名称——SQL。隐式游标的属性值只反映最近的一条SQL语句的处理结果

使用显式游标的4个步骤：

说明游标。
打开游标。
读取数据。
关闭游标。

游标控制语句

声明游标语句CURSOR

DECLARE CURSOR 游标名
[ (参数列表) ]
IS
SELECT语句;

示例：

DECLARE CURSOR MyCur(varType NUMBER) IS
  SELECT UserId, UserName FROM Users
  WHERE UserType = varType;

打开游标语句OPEN

打开游标时，数据库数据将被存储到游标变量中。

OPEN 游标名 [ (参数列表) ];

注：显式游标必须事先声明，才能使用OPEN语句打开，否则会出现错误

游标取值语句FETCH

游标取值时不需要为游标名加上参数，因为其中存储的数据已经在游标打开时存储进去，其中的数据已经相对固定。其取值对应的变量应与定义游标时select获取的列对应。

FETCH 游标名 INTO 变量列表;

示例：

FETCH MyCur INTO varId, varName;

关闭游标语句CLOSE

CLOSE 游标名;

显式游标使用完后，应该及时关闭，从而释放存储空间。

完整应用示例：

SET ServerOutput ON;   
DECLARE  --开始声明部分
  varId  NUMBER;  --声明变量,用来保存游标中的用户编号
  varName VARCHAR2(50);   --声明变量,用来保存游标中的用户名
  --定义游标
  CURSOR MyCur(varType NUMBER) IS
    SELECT UserId, UserName FROM Users    WHERE UserType = varType;
BEGIN   --开始程序体
  OPEN MyCur(1); --打开游标,参数为1,表示读取用户类型编号为1的记录
  FETCH MyCur INTO varId, varName;  --读取当前游标位置的数据
  CLOSE MyCur;   --关闭游标
  dbms_output.put_line('用户编号:' || varId ||', 用户名:' || varName); --显示数据
END;   --结束程序体

游标属性

%ISOPEN属性
此属性多用于在程序体中判断游标是否被打开：

IF MyCur%ISOPEN = FALSE Then
    OPEN MyCur(2);
  END IF;

%FOUND属性和%NOTFOUND属性
使用%FOUND属性可以循环执行游标读取数据

  WHILE MyCur%FOUND --如果当前游标有效，则执行循环
  LOOP
    dbms_output.put_line('用户编号:' || varId ||', 用户名:' || varName); --显示读取的数据
    FETCH MyCur INTO varId, varName;  --读取当前游标位置的数据
  END LOOP;

%ROWCOUNT属性
用于标记当前游标所在位置

FETCH MyCur INTO varId, varName;  --读取当前游标位置的数据
  WHILE MyCur%FOUND --如果当前游标有效，则执行循环
  LOOP
    dbms_output.put_line('用户编号:' || varId ||', 用户名:' || varName
    IF MyCur%ROWCOUNT = 2 THEN
      EXIT;
    END IF;
    FETCH MyCur INTO varId, varName;  --读取当前游标位置的数据
  END LOOP;

游标FOR循环

记录：
其用处在于将多个变量放置在一个群体中，类似于C++的struct结构

TYPE 记录类型名 IS RECORD
   ( 字段声明 [, 字段声明] …);
  

TYPE User_Record_Type IS RECORD
  ( UserId             Users.UserId%Type, 
   UserName        Users.UserName%Type);

定义记录变量的方法与定义普通变量的方法相同，语法如下

记录变量名 记录变量类型

var_UserRecord User_Record_Type;

访问记录中的字段

记录名.字段名

var_UserRecord.UserId

如果要声明的记录类型与某个表或视图的结构完全相同，则可以直接使用%ROWTYPE属性来定义记录变量：

变量名  表名%ROWTYPE;

var_UserRecord1 User%ROWTYPE;

使用这种方法定义记录变量，不需要声明记录类型。记录变量的字段名与对应表或视图中的列名一致，而且拥有相同的数据类型。

游标FOR循环的语法：
由以下可知，for循环一定有记录

FOR <记录名> IN <游标名> LOOP
  语句1；
  语句2;
  …
  语句n;
END LOOP;

带子查询的游标FOR循环的语法
就是将select替代游标的作用

FOR <记录名> IN <SELECT子查询> LOOP
  语句1；
  语句2;
  …
  语句n;
END LOOP;

管理游标结果集

修改游标结果集中的行

定义游标时可以使用FOR UPDATE子句指定可以更新的列：

DECLARE CURSOR 游标名
[ (参数列表) ]
IS
SELECT语句
FOR UPDATE OF 可以更新的列名;


DECLARE CURSOR MyEmpCursor (varDepId NUMBER) IS
  SELECT Last_name, Salary FROM HR.Employees
  WHERE Department_id = varDepId
  FOR UPDATE OF salary;

使用UPDATE语句修改游标中数据：
需要使用WHERE CURRENT OF子句指定要更新的游标：

UPDATE 表名 SET子句
WHERE CURRENT OF 游标名

DECLARE CURSOR MyEmpCursor(varDepId NUMBER) IS
  SELECT Last_name, Salary FROM HR.Employees
  WHERE Department_id = varDepId
  FOR UPDATE OF Salary;
BEGIN
  FOR rec1 IN MyEmpCursor(1) LOOP
    UPDATE HR.Employees SET Salary = Salary + 100 
    WHERE CURRENT OF MyEmpCursor;
  END LOOP;
END;

删除游标结果集中的行

DELETE FROM 表名
WHERE CURRENT OF 游标名

DECLARE CURSOR MyEmpCursor (varDepId NUMBER) IS
  SELECT Last_name, Salary FROM HR.Employees
  WHERE Department_id = varDepId
  FOR UPDATE OF salary;
BEGIN
  FOR rec1 IN MyEmpCursor(1) LOOP
    DELETE FROM HR.Employees 
    WHERE CURRENT OF MyEmpCursor;
  END LOOP;
END;