MySQL全面瓦解19:游标相关

翁智华 2021-01-21 16:21:04
Mysql 游标 全面 相关 瓦解


定义

我们经常会遇到这样的一种情况,需要对我们查询的结果进行遍历操作,并对遍历到的每一条数据进行处理,这时候就会使用到游标。
所以: 游标(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、游标的缺点是只能一行一行操作,在数据量大的情况下,是不适用的,速度过慢。数据库大部分是面对集合的,业务会比较复杂,而游标使用会有死锁,影响其他的业务操作,不可取。 当数据量大时,使用游标会造成内存不足现象。

 

 

 

版权声明
本文为[翁智华]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/wzh2010/p/13843103.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云