例子：
當前有三張表A、B、C其中A和B是一對多關系，B和C是一對多關系，現在需要將B中A表的主鍵存到C中；
常規思路就是將B中查詢出來然后通過一個update語句來更新C表就可以了，但是B表中有2000多條數據，
難道要執行2000多次？顯然是不現實的；最終找到寫一個存儲過程然后通過循環來更新C表，
然而存儲過程中的寫法用的就是游標的形式。
【簡介】
游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。
? 游標充當指針的作用。
? 盡管游標能遍歷結果中的所有行，但他一次只指向一行。
? 游標的作用就是用于對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作。
【用法】
一、聲明一個游標: declare 游標名稱 CURSOR for table;(這里的table可以是你查詢出來的任意集合)
? 二、打開定義的游標:open 游標名稱;
? 三、獲得下一行數據:FETCH 游標名稱 into testrangeid,versionid;
? 四、需要執行的語句(增刪改查):這里視具體情況而定
? 五、釋放游標:CLOSE 游標名稱;
注:mysql存儲過程每一句后面必須用;結尾，使用的臨時字段需要在定義游標之前進行聲明。
【實例】
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
- BEGIN --定義變量 declare testrangeid BIGINT; declare versionid BIGINT; declare done int; --創建游標，并存儲數據 declare cur_test CURSOR for select id as testrangeid,version_id as versionid from tp_testrange; --游標中的內容執行完后將done設置為1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; --打開游標 open cur_test; --執行循環 posLoop:LOOP --判斷是否結束循環 IF done=1 THEN LEAVE posLoop; END IF; --取游標中的值 FETCH cur_test into testrangeid,versionid; --執行更新操作 update tp_data_execute set version_id=versionid where testrange_id = testrangeid; END LOOP posLoop; --釋放游標 CLOSE cur_test; END- |
例子2：
我們現在要用存儲過程做一個功能，統計iphone的總庫存是多少，并把總數輸出到控制臺。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
--在windows系統中寫存儲過程時，如果需要使用declare聲明變量，需要添加這個關鍵字，否則會報錯。 delimiter // drop procedure if exists StatisticStore; CREATE PROCEDURE StatisticStore() BEGIN --創建接收游標數據的變量 declare c int; declare n varchar(20); --創建總數變量 declare total int default 0; --創建結束標志變量 declare done int default false; --創建游標 declare cur cursor for select name,count from store where name = 'iphone'; --指定游標循環結束時的返回值 declare continue HANDLER for not found set done = true; --設置初始值 set total = 0; --打開游標 open cur; --開始循環游標里的數據 read_loop:loop --根據游標當前指向的一條數據 fetch cur into n,c; --判斷游標的循環是否結束 if done then leave read_loop; --跳出游標循環 end if; --獲取一條數據時，將count值進行累加操作，這里可以做任意你想做的操作， set total = total + c; --結束游標循環 end loop; --關閉游標 close cur; --輸出結果 select total; END; --調用存儲過程 call StatisticStore(); |
fetch是獲取游標當前指向的數據行，并將指針指向下一行，當游標已經指向最后一行時繼續執行會造成游標溢出。
使用loop循環游標時，他本身是不會監控是否到最后一條數據了，像下面代碼這種寫法，就會造成死循環；
|
1
2
3
4
|
read_loop:loop fetch cur into n,c; set total = total+c; end loop; |
在MySql中，造成游標溢出時會引發mysql預定義的NOT FOUND錯誤，所以在上面使用下面的代碼指定了當引發not found錯誤時定義一個continue 的事件，指定這個事件發生時修改done變量的值。
|
1
|
declare continue HANDLER for not found set done = true; |
所以在循環時加上了下面這句代碼：
|
1
2
3
4
|
--判斷游標的循環是否結束 if done then leave read_loop; --跳出游標循環 end if; |
如果done的值是true，就結束循環。繼續執行下面的代碼
使用方式
游標有三種使用方式：
第一種就是上面的實現，使用loop循環；
第二種方式如下，使用while循環：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
drop procedure if exists StatisticStore1; CREATE PROCEDURE StatisticStore1() BEGIN declare c int; declare n varchar(20); declare total int default 0; declare done int default false; declare cur cursor for select name,count from store where name = 'iphone'; declare continue HANDLER for not found set done = true; set total = 0; open cur; fetch cur into n,c; while(not done) do set total = total + c; fetch cur into n,c; end while; close cur; select total; END; call StatisticStore1(); |
第三種方式是使用repeat執行：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
drop procedure if exists StatisticStore2; CREATE PROCEDURE StatisticStore2() BEGIN declare c int; declare n varchar(20); declare total int default 0; declare done int default false; declare cur cursor for select name,count from store where name = 'iphone'; declare continue HANDLER for not found set done = true; set total = 0; open cur; repeat fetch cur into n,c; if not done then set total = total + c; end if; until done end repeat; close cur; select total; END; call StatisticStore2(); |
游標嵌套
在mysql中，每個begin end 塊都是一個獨立的scope區域，由于MySql中同一個error的事件只能定義一次，如果多定義的話在編譯時會提示Duplicate handler declared in the same block。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
drop procedure if exists StatisticStore3; CREATE PROCEDURE StatisticStore3() BEGIN declare _n varchar(20); declare done int default false; declare cur cursor for select name from store group by name; declare continue HANDLER for not found set done = true; open cur; read_loop:loop fetch cur into _n; if done then leave read_loop; end if; begin declare c int; declare n varchar(20); declare total int default 0; declare done int default false; declare cur cursor for select name,count from store where name = 'iphone'; declare continue HANDLER for not found set done = true; set total = 0; open cur; iphone_loop:loop fetch cur into n,c; if done then leave iphone_loop; end if; set total = total + c; end loop; close cur; select _n,n,total; end; begin declare c int; declare n varchar(20); declare total int default 0; declare done int default false; declare cur cursor for select name,count from store where name = 'android'; declare continue HANDLER for not found set done = true; set total = 0; open cur; android_loop:loop fetch cur into n,c; if done then leave android_loop; end if; set total = total + c; end loop; close cur; select _n,n,total; end; begin end; end loop; close cur; END; call StatisticStore3(); |
上面就是實現一個嵌套循環，當然這個例子比較牽強。湊合看看就行。
動態SQL
Mysql 支持動態SQL的功能
|
1
2
3
4
5
6
|
set @sqlStr='select * from table where condition1 = ?'; prepare s1 for @sqlStr; --如果有多個參數用逗號分隔 execute s1 using @condition1; --手工釋放，或者是 connection 關閉時， server 自動回收 deallocate prepare s1; |
以上就是詳解Mysql 游標的詳細內容，更多關于Mysql 游標的資料請關注服務器之家其它相關文章！
原文鏈接：https://cloud.tencent.com/developer/article/1681593
