MySQL中游標的作用有哪些

MySQL中游標的作用有哪些？很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

游標的作用

比如我們上面那個students學生，需要對每個用戶進行遍歷，然后根據他們的其他評價進行加分或者減分。這時候我們就需要查詢到所有的學生信息（包含成績）。

select studentid,studentname,score from students;

執行之后返回了的學生數據集合，我們如果需要對學生數據逐一遍歷，然后根據具體的情況進行加分，那就需要是使用游標了。
游標相當于一個指針，這個指針指向select的第一行數據，可以通過移動指針來遍歷后面的數據。 

游標的使用

• 聲明游標：創建一個游標，并指定這個游標需要遍歷的select查詢，聲明游標時并不會去執行這個sql。

• 打開游標：打開游標的時候，會執行游標對應的select語句。

• 遍歷數據：使用游標循環遍歷select結果中每一行數據，然后進行處理。

• 業務操作：對遍歷到的每行數據進行操作的過程，可以放置任何需要執行的執行的語句(增刪改查):這里視具體情況而定

• 關閉游標：游標使用完之后一定要釋放。

注:使用的臨時字段需要在定義游標之前進行聲明。

聲明游標

DECLARE cursor_name CURSOR FOR select_statement;

聲明一個游標。也可以在子程序中定義多個游標，但是一個塊中的每一個游標必須有唯一的名字。聲明游標后也是單條操作的，但是SELECT語句不能有INTO子句。
一個begin end中只能聲明一個游標。

打開游標

OPEN cursor_name;

打開先前聲明的游標。

遍歷游標數據

FETCH cursor_name INTO var_list;

這個語句用指定的打開游標讀取下一行（如果有下一行的話），并且前進游標指針。取出當前行的結果，將結果放在對應的變量中，并將游標指針指向下一行的數據。
當調用fetch的時候，會獲取當前行的數據，如果當前行無數據，會引發mysql內部的NOT FOUND錯誤。

關閉游標

CLOSE cursor_name;

切記游標使用完畢之后要關閉。

游標舉例

寫一個函數，里面包含對students 學生用戶成績的計算和附加分計算

數據基礎

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | b1 | 81 | 2 |
| 6 | b2 | 82 | 2 |
| 7 | c1 | 71 | 3 |
| 8 | c2 | 72.5 | 3 |
| 9 | lala | 73 | 0 |
| 10 | A | 99 | 3 |
| 16 | test1 | 100 | 0 |
| 17 | trigger2 | 107 | 0 |
| 22 | trigger1 | 100 | 0 |
+-----------+-------------+-------+---------+
13 rows in set

編寫包含游標的函數

這邊注釋很清晰

mysql>
/*判斷函數如果存在則刪除*/
DROP FUNCTION IF EXISTS fun_test;
/*聲明結束符為$*/
DELIMITER $
/*創建函數，對符合條件的每個同學的分數進行加分，加的分數不能超過給定的值max_score*/
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int
BEGIN
/*定義實時StudentId的變量*/
DECLARE var_studentId int DEFAULT 0;
/*定義計算后分數的變量*/
DECLARE var_score decimal(10,2) DEFAULT 0;
/*定義游標結束標志變量*/
DECLARE var_done int DEFAULT FALSE;
/*創建游標*/
DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
/*游標結束時會設置var_done為true，后續可以使用var_done來判斷游標是否結束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
/*打開游標*/
OPEN cur_test;
/*使用Loop循環遍歷游標*/
select_loop:LOOP
/*先獲取當前行的數據，然后將當前行的數據放入var_studentId,var_score中，如果無數據行了，var_done會被置為true*/
FETCH cur_test INTO var_studentId,var_score;
/*通過var_done來判斷游標是否結束了，退出循環*/
IF var_done THEN
LEAVE select_loop;
END IF;
/*對var_score值添加隨機值，不能超過給定的分數*/
set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
update students set score = var_score where studentId= var_studentId;
END LOOP;
/*關閉游標*/
CLOSE cur_test;
/*返回結果:可以根據實際情況返回需要的內容*/
RETURN 1;
END $
/*結束符置為;*/
DELIMITER ;
Query OK, 0 rows affected

調用函數

mysql>
/* 參數為8，表示加分上限為8 */
select fun_test(8);
+-------------+
| fun_test(8) |
+-------------+
| 1 |
+-------------+
1 row in set

查看結果

對比原來的成績的值，發現成績添加了隨機值，但沒超過給定的分數 8

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

查看觸發器日志

符合條件被修改分數的有9條數據，都已經被觸發器記錄到日志里面了

mysql>
/*上一篇編寫了觸發器，當修改students表的時候觸發日志記錄 */
select * from triggerlog;
+----+--------------+---------------+-----------------------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+-----------------------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
| 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
| 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
| 6 | after | update | delete student info,id:11 |
| 7 | after | update | from:brand,97.50 to:brand,105.50 |
| 8 | after | update | from:helen,96.50 to:helen,98.50 |
| 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
| 10 | after | update | from:sol,97.00 to:sol,97.00 |
| 11 | after | update | from:b1,81.00 to:b1,89.00 |
| 12 | after | update | from:b2,82.00 to:b2,90.00 |
| 13 | after | update | from:c1,71.00 to:c1,76.00 |
| 14 | after | update | from:c2,72.50 to:c2,73.50 |
| 15 | after | update | from:A,99.00 to:A,100.00 |
+----+--------------+---------------+-----------------------------------------+
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、游標的缺點是只能一行一行操作，在數據量大的情況下，是不適用的，速度過慢。數據庫大部分是面對集合的，業務會比較復雜，而游標使用會有死鎖，影響其他的業務操作，不可取。 當數據量大時，使用游標會造成內存不足現象。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。