认真学习MySQL中游标与实例分析

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 认真学习MySQL中游标与实例分析

【1】游标简介

游标,又称光标。实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果,每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量,交由主语言进一步处理

游标充当指针的作用。尽管游标能遍历结果中的所有行,但他一次只指向一行。游标初始位置在before first,使用的时候是先移到下一行,然后再去数据。


概括来讲,SQL的游标是一种临时的数据库对象,即可以用来存放在数据库表中的数据行副本,也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

游标的一个常见用途就是保存查询结果,以便以后使用。游标的结果集是由SELECT语句产生,如果处理过程需要重复使用一个记录集,那么创建一次游标而重复使用若干次,比重复查询数据库要快的多。


大部分程序数据设计语言都能使用游标来检索SQL数据库中的数据,在程序中嵌入游标和在程序中嵌入SQL语句相同

游标(cursor)的特性:

  • ① 只读的,不能更新的。
  • ② 不滚动的
  • ③ 不敏感的,不敏感意为服务器可以或不可以复制它的结果表

游标(cursor)必须在声明处理程序之前被声明,并且变量和条件必须在声明游标或处理程序之前被声明。

游标实例

以下为一个存储过程实例,用到了游标和事务及异常处理。

CREATE PROCEDURE `my_cursor1`(out o_result int)
# begin 标号 zero_back
zero_back:BEGIN 
  #Routine body goes here...
  DECLARE p_age int; # 声明变量
  DECLARE p_count int;
  -- 声明游标结束判断变量,默认值为FALSE;
  DECLARE fetchSeqOk boolean DEFAULT FALSE;
  DECLARE my_cursor CURSOR for select age FROM t_user where id < 4; -- 定义游标
  //游标执行结束时将会set fetchSeqOk = true
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
  set fetchSeqOk = true;
  -- 在MySql中,造成游标溢出时会引发mysql预定义的NOT FOUND错误,
  -- 所以在上面代码中指定了当引发not found错误时定义一个continue 的事件 
  # 定义sql异常执行语句
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
    ROLLBACK; -- 如果有异常,进行事务回滚;如果调用存储过程的service,已经进行了事务处理,那么该存储过程不需要再重复开启事务
    set o_result = -500;
  end;
  # 如果表中无数据,直接跳出函数并设置o_result为-1; 
  select count(*) into p_count from t_user where id <4;
  IF p_count < 1 THEN
    SET o_result = -1;
    LEAVE zero_back; 
  END IF;
  # 打开游标
  OPEN my_cursor;
     START TRANSACTION;-- 开启事务,如果service里面已经开启了事务,那么存储过程中不需要再开事务;事务要在循环外面开启!!!
     loop_cursor:LOOP
        fetch my_cursor into p_age; -- 游标改变位置指向下一行,取下一行数据,初始位置在before first row
        IF fetchSeqOk THEN
          LEAVE loop_cursor;
        END IF;
        -- fetch my_cursor into p_age; 此处位置不对,将会重复插入游标最后一个值   
        insert into t_user (age) VALUES(p_age);
        -- 现在游标行位置未变
    END LOOP loop_cursor;
    -- 如果给loop增加了标号,那么请使用END LOOP loop_cursor;进行循环关闭。否则直接END LOOP
    COMMIT; -- 正常执行完,commit
    set o_result = 0;
  CLOSE my_cursor;  -- 关闭游标
END

【2】实例分析

上面示例已经演示了游标的定义,打开,读取值和关闭游标,其中用到了loop循环和事务以及异常处理。这里说明上面示例中需要注意事项。

事务开启关闭相对应

缺一不可。如果调用存储过程的service使用了事务,那么该存储过程中不用开启事务。事务尽量尽量在循环外面。


① 定义异常处理

DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 定义sql异常
  BEGIN
    ROLLBACK; -- 如果有异常,进行事务回滚;如果调用存储过程的service,已经进行了事务处理,那么该存储过程不需要再重复开启事务
    set o_result = -500;
  end;

② 标号应用

标号可以用在begin,repeat,while或者loop语句前,语句标号只能在合法的语句前面使用。

可以跳出循环,使运行指令达到复合语句的最后一步。

loop_cursor:LOOP
LEAVE loop_cursor;

③ loop的关闭两种形式

loop
...///
end loop;
# or 采用标号
loop_LABLES:loop
...//
end loop loop_LABLES;

④ 声明游标

DECLARE cursor_name CURSOR FOR select_statement ;
# select_statement 指一个select语句

这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。SELECT语句不能有INTO子句。

⑤ 游标打开和关闭

# 打开游标
OPEN my_cursor;
# 关闭游标
CLOSE my_cursor;  

CLOSE my_cursor;这个语句关闭先前打开的光标。 如果未被明确地关闭,光标在它被声明的复合语句的末尾被关闭。

⑥ fetch是获取游标当前指向的数据行

fetch是获取游标当前指向的数据行,并将指针指向下一行,当游标已经指向最后一行时继续执行会造成游标溢出。

游标改变位置指向下一行,取下一行数据,初始位置在before first row 。

fetch my_cursor into p_age; 

在MySql中,造成游标溢出时会引发mysql预定义的NOT FOUND错误,所以在代码中指定了当引发not found错误时定义一个continue 的事件

DECLARE CONTINUE HANDLER FOR NOT FOUND  set fetchSeqOk = true;

游标获取多个列

# 获取属性表的所有code、category_code
DECLARE my_cursor CURSOR for select  code,category_code FROM sys_attribute;
# 取出两个列
fetch my_cursor into curr_code,curr_category; 

【3】游标与动态SQL结合

如下是一个删除某个数据库某个表的索引存储过程,其使用用户变量组装动态SQL。

DELIMITER //
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
       DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;      
#若没有数据返回,程序继续,并将变量done设为2
        OPEN _cur;
        FETCH _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
END //
DELIMITER ;


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
22天前
|
存储 关系型数据库 MySQL
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
78 1
|
22天前
|
SQL 前端开发 关系型数据库
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
40 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
|
29天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1607 14
|
16天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
24天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
55 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
25天前
|
Java 关系型数据库 MySQL
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
42 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
|
25天前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
19 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
21天前
|
存储 关系型数据库 MySQL
mysql 8.0 的 建表 和八种 建表引擎实例
mysql 8.0 的 建表 和八种 建表引擎实例
19 0
|
22天前
|
存储 关系型数据库 MySQL
Key_Value 形式 存储_5级省市城乡划分代码 (mysql 8.0 实例)
本文介绍了如何使用MySQL8.0数据库中的Key_Value形式存储全国统计用区划代码和城乡划分代码(5级),包括导入数据、通过数学函数提取省市区信息,以及查询5级行政区划的详细数据。
27 0
|
22天前
|
算法 关系型数据库 MySQL
复购率 mysql 实例(sample database classicmodels _No.1 )
复购率 mysql 实例(sample database classicmodels _No.1 )
24 0