MySQL全面瓦解19：遊標相關

翁智華發表於2021-01-21
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、遊標的缺點是隻能一行一行操作，在資料量大的情況下，是不適用的，速度過慢。資料庫大部分是面對集合的，業務會比較複雜，而遊標使用會有死鎖，影響其他的業務操作，不可取。 當資料量大時，使用遊標會造成記憶體不足現象。

 

 

 

相關文章