MySQL全面瓦解19:遊標相關

itread01 2021-01-21 20:05:01
Mysql 数据库/缓存 全面 itread01 瓦解


定義

我們經常會遇到這樣的一種情況,需要對我們查詢的結果進行遍歷操作,並對遍歷到的每一條資料進行處理,這時候就會使用到遊標。
所以:遊標(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 BEGIN10 /*定義實時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:LOOP24 /*先獲取當前行的資料,然後將當前行的資料放入var_studentId,var_score中,如果無資料行了,var_done會被置為true*/25 FETCH cur_test INTO var_studentId,var_score;26 /*通過var_done來判斷遊標是否結束了,退出迴圈*/27 IF var_done THEN28 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、遊標的缺點是隻能一行一行操作,在資料量大的情況下,是不適用的,速度過慢。資料庫大部分是面對集合的,業務會比較複雜,而遊標使用會有死鎖,影響其他的業務操作,不可取。 當資料量大時,使用遊標會造成記憶體不足現象。

 

 

 

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1611230464.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课程百度云