MySQL 遊標的作用與使用相關
定義
我們經常會遇到這樣的一種情況,需要對我們查詢的結果進行遍歷操作,並對遍歷到的每一條數據進行處理,這時候就會使用到遊標。
所以:遊標(Cursor)是處理數據的一種存儲在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、遊標的缺點是隻能一行一行操作,在數據量大的情況下,是不適用的,速度過慢。數據庫大部分是面對集合的,業務會比較復雜,而遊標使用會有死鎖,影響其他的業務操作,不可取。 當數據量大時,使用遊標會造成內存不足現象。
以上就是全面剖析MySQL遊標的詳細內容,更多關於MySQL遊標的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- MySQL 觸發器的使用及需要註意的地方
- MySql存儲過程循環的使用分析詳解
- Mysql存儲過程、觸發器、事件調度器使用入門指南
- MySQL 遊標的定義與使用方式
- MySQL系列之五 視圖、存儲函數、存儲過程、觸發器