定义

我们经常会遇到这样的一种情况,需要对我们查询的结果进行遍历操作,并对遍历到的每一条数据进行处理,这时候就会使用到游标。
所以:游标(Cursor)是处理数据的一种存储在MySQL服务器上的数据库查询方法,为了查看或者处理结果集中的数据,提供了在结果集中一次一行遍历数据的能力。
游标主要用在循环处理、存储过程、函数、触发器 中。

游标的作用

比如我们上面那个students学生,需要对每个用户进行遍历,然后根据他们的其他评价进行加分或者减分。这时候我们就需要查询到所有的学生信息(包含成绩)。
1 select studentid,studentname,score from students; 
执行之后返回了的学生数据集合,我们如果需要对学生数据逐一遍历,然后根据具体的情况进行加分,那就需要是使用游标了。
游标相当于一个指针,这个指针指向select的第一行数据,可以通过移动指针来遍历后面的数据。 

游标的使用

声明游标:创建一个游标,并指定这个游标需要遍历的select查询,声明游标时并不会去执行这个sql。
打开游标:打开游标的时候,会执行游标对应的select语句。
遍历数据:使用游标循环遍历select结果中每一行数据,然后进行处理。
业务操作:对遍历到的每行数据进行操作的过程,可以放置任何需要执行的执行的语句(增删改查):这里视具体情况而定
关闭游标:游标使用完之后一定要释放。
注:使用的临时字段需要在定义游标之前进行声明。

声明游标

1 DECLARE cursor_name CURSOR FOR select_statement; 
声明一个游标。也可以在子程序中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。声明游标后也是单条操作的,但是SELECT语句不能有INTO子句。
一个begin end中只能声明一个游标。

打开游标

1 OPEN cursor_name; 
打开先前声明的游标。

遍历游标数据

1 FETCH cursor_name INTO var_list;
这个语句用指定的打开游标读取下一行(如果有下一行的话),并且前进游标指针。取出当前行的结果,将结果放在对应的变量中,并将游标指针指向下一行的数据。
当调用fetch的时候,会获取当前行的数据,如果当前行无数据,会引发mysql内部的NOT FOUND错误。

关闭游标

1 CLOSE cursor_name; 
切记游标使用完毕之后要关闭。

游标举例

写一个函数,里面包含对students 学生用户成绩的计算和附加分计算
数据基础
 1 mysql> select * from students;
2 +-----------+-------------+-------+---------+
3 | studentid | studentname | score | classid |
4 +-----------+-------------+-------+---------+
5 | 1 | brand | 97.5 | 1 |
6 | 2 | helen | 96.5 | 1 |
7 | 3 | lyn | 96 | 1 |
8 | 4 | sol | 97 | 1 |
9 | 5 | b1 | 81 | 2 |
10 | 6 | b2 | 82 | 2 |
11 | 7 | c1 | 71 | 3 |
12 | 8 | c2 | 72.5 | 3 |
13 | 9 | lala | 73 | 0 |
14 | 10 | A | 99 | 3 |
15 | 16 | test1 | 100 | 0 |
16 | 17 | trigger2 | 107 | 0 |
17 | 22 | trigger1 | 100 | 0 |
18 +-----------+-------------+-------+---------+
19 13 rows in set 
编写包含游标的函数

这边注释很清晰,关键知识点都已经标红

 1 mysql>
2 /*判断函数如果存在则删除*/
3 DROP FUNCTION IF EXISTS fun_test;
4 /*声明结束符为$*/
5 DELIMITER $
6 /*创建函数,对符合条件的每个同学的分数进行加分,加的分数不能超过给定的值max_score*/
7 CREATE FUNCTION fun_test(max_score decimal(10,2))
8 RETURNS int
9 BEGIN
10 /*定义实时StudentId的变量*/
11 DECLARE var_studentId int DEFAULT 0;
12 /*定义计算后分数的变量*/
13 DECLARE var_score decimal(10,2) DEFAULT 0;
14 /*定义游标结束标志变量*/
15 DECLARE var_done int DEFAULT FALSE;
16 /*创建游标*/
17 DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
18 /*游标结束时会设置var_done为true,后续可以使用var_done来判断游标是否结束*/
19 DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
20 /*打开游标*/
21 OPEN cur_test;
22 /*使用Loop循环遍历游标*/
23 select_loop:LOOP
24 /*先获取当前行的数据,然后将当前行的数据放入var_studentId,var_score中,如果无数据行了,var_done会被置为true*/
25 FETCH cur_test INTO var_studentId,var_score;
26 /*通过var_done来判断游标是否结束了,退出循环*/
27 IF var_done THEN
28 LEAVE select_loop;
29 END IF;
30 /*对var_score值添加随机值,不能超过给定的分数*/
31 set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
32 update students set score = var_score where studentId= var_studentId;
33 END LOOP;
34 /*关闭游标*/
35 CLOSE cur_test;
36 /*返回结果:可以根据实际情况返回需要的内容*/
37 RETURN 1;
38 END $
39 /*结束符置为;*/
40 DELIMITER ;
41 Query OK, 0 rows affected
调用函数
1 mysql>
2 /* 参数为8,表示加分上限为8 */
3 select fun_test(8);
4 +-------------+
5 | fun_test(8) |
6 +-------------+
7 | 1 |
8 +-------------+
9 1 row in set
查看结果

对比原来的成绩的值,发现成绩添加了随机值,但没超过给定的分数 8

 1 mysql> select * from students;
2 +-----------+-------------+-------+---------+
3 | studentid | studentname | score | classid |
4 +-----------+-------------+-------+---------+
5 | 1 | brand | 105.5 | 1 |
6 | 2 | helen | 98.5 | 1 |
7 | 3 | lyn | 97 | 1 |
8 | 4 | sol | 97 | 1 |
9 | 5 | b1 | 89 | 2 |
10 | 6 | b2 | 90 | 2 |
11 | 7 | c1 | 76 | 3 |
12 | 8 | c2 | 73.5 | 3 |
13 | 9 | lala | 73 | 0 |
14 | 10 | A | 100 | 3 |
15 | 16 | test1 | 100 | 0 |
16 | 17 | trigger2 | 107 | 0 |
17 | 22 | trigger1 | 100 | 0 |
18 +-----------+-------------+-------+---------+
19 13 rows in set
查看触发器日志

符合条件被修改分数的有9条数据,都已经被触发器记录到日志里面了

 1 mysql>
2 /*上一篇编写了触发器,当修改students表的时候触发日志记录 */
3 select * from triggerlog;
4 +----+--------------+---------------+-----------------------------------------+
5 | id | trigger_time | trigger_event | memo |
6 +----+--------------+---------------+-----------------------------------------+
7 | 1 | after | insert | new student info,id:21 |
8 | 2 | after | update | update student info,id:21 |
9 | 3 | after | update | delete student info,id:21 |
10 | 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
11 | 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
12 | 6 | after | update | delete student info,id:11 |
13 | 7 | after | update | from:brand,97.50 to:brand,105.50 |
14 | 8 | after | update | from:helen,96.50 to:helen,98.50 |
15 | 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
16 | 10 | after | update | from:sol,97.00 to:sol,97.00 |
17 | 11 | after | update | from:b1,81.00 to:b1,89.00 |
18 | 12 | after | update | from:b2,82.00 to:b2,90.00 |
19 | 13 | after | update | from:c1,71.00 to:c1,76.00 |
20 | 14 | after | update | from:c2,72.50 to:c2,73.50 |
21 | 15 | after | update | from:A,99.00 to:A,100.00 |
22 +----+--------------+---------------+-----------------------------------------+
23 15 rows in set  
游标的执行过程
按照上面的例子,分析下这个游标的执行过程。
1、我们创建了一个游标,数据源取自于student学生表。
2、游标中有个指针,当打开游标的时候,会执行游标对应的select语句,这个指针会指向select结果中第一行记录。
3、当调用fetch 游标名称时,会获取当前行的数据,如果当前行无数据,会触发NOT FOUND异常。
当触发NOT FOUND异常的时候,我们可以使用一个变量来标记一下,如上面的:DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
将变量var_done的值置为TURE,循环中就可以通过var_done的值控制循环的退出:LEAVE select_loop;。
如果当前行有数据,则将当前行数据存到对应的变量中,并将游标指针指向下一行数据,如下语句:FETCH cur_test INTO var_studentId,var_score;

总结

1、游标用来对查询结果进行遍历处理。
2、游标的使用过程:声明游标、打开游标、遍历游标、关闭游标。
3、游标主要用在循环处理、存储过程、函数中使用,用来查询结果集。
4、游标的缺点是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。 当数据量大时,使用游标会造成内存不足现象。

MySQL全面瓦解19:游标相关的更多相关文章

  1. 搭建 MySQL 5.7.19 主从复制,以及复制实现细节分析

    主从复制可以使MySQL数据库主服务器的主数据库,复制到一个或多个MySQL从服务器从数据库,默认情况下,复制异步; 根据配置,可以复制数据库中的所有数据库,选定的数据库或甚至选定的表. Mysql ...

  2. MySQL中函数、游标、事件、视图

    MySQL中函数.游标.事件.视图基本应用举例(代码) MySQL中function用户自定义函数c,fun,fun是面向过程的实现方式只能传入参数,或不传入参数,不能传出参数,必有返回值函数中是不能 ...

  3. 一起学ASP.NET Core 2.0学习笔记(二): ef core2.0 及mysql provider 、Fluent API相关配置及迁移

    不得不说微软的技术迭代还是很快的,上了微软的船就得跟着她走下去,前文一起学ASP.NET Core 2.0学习笔记(一): CentOS下 .net core2 sdk nginx.superviso ...

  4. WordPress 4.8 安装配置教程 (基于 centos 7.3, php 7.0, mysql 5.7.19, nginx 1.12.1)

    最近想要整个 blog,记录自己工作.学习中的点滴.Wordpress 自然是首选,因为内容才是关键,所以也就不怕别人说太 low.网上大部份都是讲 wordpress 配合 apache 的安装教程 ...

  5. mysql_linux(centos7 mysql 5.7.19)

    centos7  mysql 5.7.19安装 1.解压文件 [root@centos3 ~]# tar -zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.g ...

  6. 2.19 cookie相关操作

    2.19 cookie相关操作 前言虽然cookie相关操作在平常ui自动化中用得少,偶尔也会用到,比如登录有图形验证码,可以通过绕过验证码方式,添加cookie方法登录.登录后换账号登录时候,也可作 ...

  7. (2.14)Mysql之SQL基础——游标

    (2.14)Mysql之SQL基础——游标 关键词:Mysql游标 -- (1)定义游标 declare cur_name cursor for select * from table_name wh ...

  8. MySQL(8)---游标

    Mysql(8)-游标 上一遍博客写了有关存储过程的语法知识 Mysql(7)---存储过程 游标或许你在工作中很少用到,但用不到不代表不去了解它,但你真正需要它来解决问题的时候,再花时间去学习很可能 ...

  9. MySQL学习之路 一 : MySQL 5.7.19 源码安装

    MySQL 5.7.19 源码安装 查看系统: # cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) 安装依赖包 # yum - ...

  10. 实战分享丨MySQL 与Django版本匹配相关经验

    摘要:关于MySQL 与Django版本匹配相关知识的经验分享. run: (env) E:\PythonPro\PyDjangoProDemo011\xuanyuaniotpro>python ...

随机推荐

  1. butterknife 使用注意事项

    写了个demo,一直报错 Caused by: java.lang.IllegalStateException: Required view 'tv1' with ID 2131492943 for ...

  2. Visual Studio 2012 比较好用的插件推荐

    为了高效率的开发,下面笔者推荐几款非常不错的插件,方便大家.   以上控件的安装方式是: 然后通过联网的方式下载,安装后,需要重启一下Visual Studio方可使用.

  3. Oracle定义常量和变量

    1.定义变量 变量指的就是可变化的量,程序运行过程中可以随时改变其数据存储结构 标准语法格式:<变量名><数据类型>[(长度):=<初始值>] 示例: declar ...

  4. iOS程序 # 启动过程

    [ App 应用 ] 中文名:缺省 外文名:default 拼音:quē shěng 释义:系统默认状态 全称:缺省状态 -------------- 1.程序启动顺序 1> main.m程序入 ...

  5. [Swift]LeetCode70. 爬楼梯 | Climbing Stairs

    You are climbing a stair case. It takes n steps to reach to the top. Each time you can either climb ...

  6. 使用 js,自己写一个简单的滚动条

    当我们给元素加上 overflow: auto;  的时候,就会出现滚动条,然而浏览的不同,滚动条的样式大不一样,有些甚至非常丑. 于是就想着自己写一个滚动条,大概需要弄清楚一下这几个点: 1.滚动条 ...

  7. 【转】 strrchr()函数---C语言

    转自:https://baike.baidu.com/item/strrchr/4621437?fr=aladdin   函数名称: strrchr 函数原型:char *strrchr(const ...

  8. Android------------------系统服务调用的学习

    一.ServiceManager的方法: 此方法getService,用于根据名称获取当前的IBinder的代理(并没有直接获取服务), 服务提供的功能是依靠IBinder间接调用的(返回值IBind ...

  9. 对象关系映射(ORM)

    1.什么是 对象-关系映射 对象-关系映射(Object Relational Mapping,简称ORM,对象关系映射)是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术. 简单的说,OR ...

  10. Vue2.5入门-3

    安装和使用 全局安装vue npm install --global vue-cli 创建基于webpack模板的新项目 vue init webpack my-project 安装依赖 cd my- ...