這是Mysql系列第19篇。
環(huán)境：mysql5.7.25，cmd命令中進(jìn)行演示。
代碼中被[]包含的表示可選，|符號(hào)分開(kāi)的表示可選其一。
需求背景
當(dāng)我們需要對(duì)一個(gè)select的查詢結(jié)果進(jìn)行遍歷處理的時(shí)候，如何實(shí)現(xiàn)呢？
此時(shí)我們需要使用游標(biāo)，通過(guò)游標(biāo)的方式來(lái)遍歷select查詢的結(jié)果集，然后對(duì)每行數(shù)據(jù)進(jìn)行處理。
本篇內(nèi)容
-
游標(biāo)定義
-
游標(biāo)作用
-
游標(biāo)使用步驟
-
游標(biāo)執(zhí)行過(guò)程詳解
-
單游標(biāo)示例
-
嵌套游標(biāo)示例
準(zhǔn)備數(shù)據(jù)
創(chuàng)建庫(kù)：javacode2018
創(chuàng)建表：test1、test2、test3
/*建庫(kù)javacode2018*/
drop?database?if?exists?javacode2018;
create?database?javacode2018;
/*切換到j(luò)avacode2018庫(kù)*/
use?javacode2018;
DROP?TABLE?IF?EXISTS?test1;
CREATE?TABLE?test1(a?int,b?int);
INSERT?INTO?test1?VALUES?(1,2),(3,4),(5,6);
DROP?TABLE?IF?EXISTS?test2;
CREATE?TABLE?test2(a?int);
INSERT?INTO?test2?VALUES?(100),(200),(300);
DROP?TABLE?IF?EXISTS?test3;
CREATE?TABLE?test3(b?int);
INSERT?INTO?test3?VALUES?(400),(500),(600);
游標(biāo)定義
游標(biāo)（Cursor）是處理數(shù)據(jù)的一種方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中一次一行遍歷數(shù)據(jù)的能力。
游標(biāo)只能在存儲(chǔ)過(guò)程和函數(shù)中使用。
游標(biāo)的作用
如sql：
select?a,b?from?test1;
上面這個(gè)查詢返回了test1中的數(shù)據(jù)，如果我們想對(duì)這些數(shù)據(jù)進(jìn)行遍歷處理，此時(shí)我們就可以使用游標(biāo)來(lái)進(jìn)行操作。
游標(biāo)相當(dāng)于一個(gè)指針，這個(gè)指針指向select的第一行數(shù)據(jù)，可以通過(guò)移動(dòng)指針來(lái)遍歷后面的數(shù)據(jù)。
游標(biāo)的使用步驟
聲明游標(biāo)：這個(gè)過(guò)程只是創(chuàng)建了一個(gè)游標(biāo)，需要指定這個(gè)游標(biāo)需要遍歷的select查詢，聲明游標(biāo)時(shí)并不會(huì)去執(zhí)行這個(gè)sql。
打開(kāi)游標(biāo)：打開(kāi)游標(biāo)的時(shí)候，會(huì)執(zhí)行游標(biāo)對(duì)應(yīng)的select語(yǔ)句。
遍歷數(shù)據(jù)：使用游標(biāo)循環(huán)遍歷select結(jié)果中每一行數(shù)據(jù)，然后進(jìn)行處理。
關(guān)閉游標(biāo)：游標(biāo)使用完之后一定要關(guān)閉。
游標(biāo)語(yǔ)法
聲明游標(biāo)
DECLARE?游標(biāo)名稱?CURSOR?FOR?查詢語(yǔ)句;
一個(gè)begin end中只能聲明一個(gè)游標(biāo)。
打開(kāi)游標(biāo)
open?游標(biāo)名稱;
遍歷游標(biāo)
fetch?游標(biāo)名稱?into?變量列表;
取出當(dāng)前行的結(jié)果，將結(jié)果放在對(duì)應(yīng)的變量中，并將游標(biāo)指針指向下一行的數(shù)據(jù)。
當(dāng)調(diào)用fetch的時(shí)候，會(huì)獲取當(dāng)前行的數(shù)據(jù)，如果當(dāng)前行無(wú)數(shù)據(jù)，會(huì)引發(fā)mysql內(nèi)部的
NOT FOUND錯(cuò)誤。
關(guān)閉游標(biāo)
close?游標(biāo)名稱;
游標(biāo)使用完畢之后一定要關(guān)閉。
單游標(biāo)示例
寫(xiě)一個(gè)函數(shù)，計(jì)算test1表中a、b字段所有的和。
創(chuàng)建函數(shù)：
/*刪除函數(shù)*/
DROP?FUNCTION?IF?EXISTS?fun1;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建函數(shù)*/
CREATE?FUNCTION?fun1(v_max_a?int)
??RETURNS?int
??BEGIN
????/*用于保存結(jié)果*/
????DECLARE?v_total?int?DEFAULT?0;
????/*創(chuàng)建一個(gè)變量，用來(lái)保存當(dāng)前行中a的值*/
????DECLARE?v_a?int?DEFAULT?0;
????/*創(chuàng)建一個(gè)變量，用來(lái)保存當(dāng)前行中b的值*/
????DECLARE?v_b?int?DEFAULT?0;
????/*創(chuàng)建游標(biāo)結(jié)束標(biāo)志變量*/
????DECLARE?v_done?int?DEFAULT?FALSE;
????/*創(chuàng)建游標(biāo)*/
????DECLARE?cur_test1?CURSOR?FOR?SELECT?a,b?from?test1?where?a<=v_max_a;
????/*設(shè)置游標(biāo)結(jié)束時(shí)v_done的值為true，可以v_done來(lái)判斷游標(biāo)是否結(jié)束了*/
????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?v_done=TRUE;
????/*設(shè)置v_total初始值*/
????SET?v_total?=?0;
????/*打開(kāi)游標(biāo)*/
????OPEN?cur_test1;
????/*使用Loop循環(huán)遍歷游標(biāo)*/
????a:LOOP
??????/*先獲取當(dāng)前行的數(shù)據(jù)，然后將當(dāng)前行的數(shù)據(jù)放入v_a,v_b中，如果當(dāng)前行無(wú)數(shù)據(jù)，v_done會(huì)被置為true*/
??????FETCH?cur_test1?INTO?v_a,?v_b;
??????/*通過(guò)v_done來(lái)判斷游標(biāo)是否結(jié)束了，退出循環(huán)*/
??????if?v_done?THEN
????????LEAVE?a;
??????END?IF;
??????/*對(duì)v_total值累加處理*/
??????SET?v_total?=?v_total?+?v_a?+?v_b;
????END?LOOP;
????/*關(guān)閉游標(biāo)*/
????CLOSE?cur_test1;
????/*返回結(jié)果*/
????RETURN?v_total;
??END?$
/*結(jié)束符置為;*/
DELIMITER?;
上面語(yǔ)句執(zhí)行過(guò)程中可能有問(wèn)題，解決方式如下。
錯(cuò)誤信息：Mysql 創(chuàng)建函數(shù)出現(xiàn)This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
mysql的設(shè)置默認(rèn)是不允許創(chuàng)建函數(shù)
解決辦法1:
執(zhí)行：
SET GLOBAL log_bin_trust_function_creators = 1;
不過(guò) 重啟了 就失效了
注意：有主從復(fù)制的時(shí)候 從機(jī)必須要設(shè)置 ?不然會(huì)導(dǎo)致主從同步失敗
解決辦法2：
在my.cnf里面設(shè)置
log-bin-trust-function-creators=1
不過(guò)這個(gè)需要重啟服務(wù)
見(jiàn)效果：
mysql>?SELECT?a,b?FROM?test1;
+------+------+
|?a????|?b????|
+------+------+
|????1?|????2?|
|????3?|????4?|
|????5?|????6?|
+------+------+
3?rows?in?set?(0.00?sec)
mysql>?SELECT?fun1(1);
+---------+
|?fun1(1)?|
+---------+
|???????3?|
+---------+
1?row?in?set?(0.00?sec)
mysql>?SELECT?fun1(2);
+---------+
|?fun1(2)?|
+---------+
|???????3?|
+---------+
1?row?in?set?(0.00?sec)
mysql>?SELECT?fun1(3);
+---------+
|?fun1(3)?|
+---------+
|??????10?|
+---------+
1?row?in?set?(0.00?sec)
游標(biāo)過(guò)程詳解
以上面的示例代碼為例，咱們來(lái)看一下游標(biāo)的詳細(xì)執(zhí)行過(guò)程。
游標(biāo)中有個(gè)指針，當(dāng)打開(kāi)游標(biāo)的時(shí)候，才會(huì)執(zhí)行游標(biāo)對(duì)應(yīng)的select語(yǔ)句，這個(gè)指針會(huì)指向select結(jié)果中第一行記錄。
當(dāng)調(diào)用fetch 游標(biāo)名稱時(shí)，會(huì)獲取當(dāng)前行的數(shù)據(jù)，如果當(dāng)前行無(wú)數(shù)據(jù)，會(huì)觸發(fā)NOT FOUND異常。
當(dāng)觸發(fā)NOT FOUND異常的時(shí)候，我們可以使用一個(gè)變量來(lái)標(biāo)記一下，如下代碼：
DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?v_done=TRUE;
當(dāng)游標(biāo)無(wú)數(shù)據(jù)觸發(fā)NOT FOUND異常的時(shí)候，將變量v_down的值置為TURE，循環(huán)中就可以通過(guò)v_down的值控制循環(huán)的退出。
如果當(dāng)前行有數(shù)據(jù)，則將當(dāng)前行數(shù)據(jù)存到對(duì)應(yīng)的變量中，并將游標(biāo)指針指向下一行數(shù)據(jù)，如下語(yǔ)句：
fetch?游標(biāo)名稱?into?變量列表;
嵌套游標(biāo)
寫(xiě)個(gè)存儲(chǔ)過(guò)程，遍歷test2、test3，將test2中的a字段和test3中的b字段任意組合，插入到test1表中。
創(chuàng)建存儲(chǔ)過(guò)程：
/*刪除存儲(chǔ)過(guò)程*/
DROP?PROCEDURE?IF?EXISTS?proc1;
/*聲明結(jié)束符為$*/
DELIMITER?$
/*創(chuàng)建存儲(chǔ)過(guò)程*/
CREATE?PROCEDURE?proc1()
??BEGIN
????/*創(chuàng)建一個(gè)變量，用來(lái)保存當(dāng)前行中a的值*/
????DECLARE?v_a?int?DEFAULT?0;
????/*創(chuàng)建游標(biāo)結(jié)束標(biāo)志變量*/
????DECLARE?v_done1?int?DEFAULT?FALSE;
????/*創(chuàng)建游標(biāo)*/
????DECLARE?cur_test1?CURSOR?FOR?SELECT?a?FROM?test2;
????/*設(shè)置游標(biāo)結(jié)束時(shí)v_done1的值為true，可以v_done1來(lái)判斷游標(biāo)cur_test1是否結(jié)束了*/
????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?v_done1=TRUE;
????/*打開(kāi)游標(biāo)*/
????OPEN?cur_test1;
????/*使用Loop循環(huán)遍歷游標(biāo)*/
????a:LOOP
??????FETCH?cur_test1?INTO?v_a;
??????/*通過(guò)v_done1來(lái)判斷游標(biāo)是否結(jié)束了，退出循環(huán)*/
??????if?v_done1?THEN
????????LEAVE?a;
??????END?IF;
??????BEGIN
????????/*創(chuàng)建一個(gè)變量，用來(lái)保存當(dāng)前行中b的值*/
????????DECLARE?v_b?int?DEFAULT?0;
????????/*創(chuàng)建游標(biāo)結(jié)束標(biāo)志變量*/
????????DECLARE?v_done2?int?DEFAULT?FALSE;
????????/*創(chuàng)建游標(biāo)*/
????????DECLARE?cur_test2?CURSOR?FOR?SELECT?b?FROM?test3;
????????/*設(shè)置游標(biāo)結(jié)束時(shí)v_done1的值為true，可以v_done1來(lái)判斷游標(biāo)cur_test2是否結(jié)束了*/
????????DECLARE?CONTINUE?HANDLER?FOR?NOT?FOUND?SET?v_done2=TRUE;
????????/*打開(kāi)游標(biāo)*/
????????OPEN?cur_test2;
????????/*使用Loop循環(huán)遍歷游標(biāo)*/
????????b:LOOP
??????????FETCH?cur_test2?INTO?v_b;
??????????/*通過(guò)v_done1來(lái)判斷游標(biāo)是否結(jié)束了，退出循環(huán)*/
??????????if?v_done2?THEN
????????????LEAVE?b;
??????????END?IF;
??????????/*將v_a、v_b插入test1表中*/
??????????INSERT?INTO?test1?VALUES?(v_a,v_b);
????????END?LOOP?b;
????????/*關(guān)閉cur_test2游標(biāo)*/
????????CLOSE?cur_test2;
??????END;
????END?LOOP;
????/*關(guān)閉游標(biāo)cur_test1*/
????CLOSE?cur_test1;
??END?$
/*結(jié)束符置為;*/
DELIMITER?;
見(jiàn)效果：
mysql>?DELETE?FROM?test1;
Query?OK,?9?rows?affected?(0.00?sec)
mysql>?SELECT?*?FROM?test1;
Empty?set?(0.00?sec)
mysql>?CALL?proc1();
Query?OK,?0?rows?affected?(0.02?sec)
mysql>?SELECT?*?from?test1;
+------+------+
|?a????|?b????|
+------+------+
|??100?|??400?|
|??100?|??500?|
|??100?|??600?|
|??200?|??400?|
|??200?|??500?|
|??200?|??600?|
|??300?|??400?|
|??300?|??500?|
|??300?|??600?|
+------+------+
9?rows?in?set?(0.00?sec)
成功插入了9條數(shù)據(jù)。
總結(jié)
-
游標(biāo)用來(lái)對(duì)查詢結(jié)果進(jìn)行遍歷處理
-
游標(biāo)的使用過(guò)程：聲明游標(biāo)、打開(kāi)游標(biāo)、遍歷游標(biāo)、關(guān)閉游標(biāo)
-
游標(biāo)只能在存儲(chǔ)過(guò)程和函數(shù)中使用
-
一個(gè)begin end中只能聲明一個(gè)游標(biāo)
-
掌握單個(gè)游標(biāo)及嵌套游標(biāo)的使用文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-707707.html
-
大家下去了多練習(xí)一下，熟練掌握游標(biāo)的使用文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-707707.html
到了這里，關(guān)于玩轉(zhuǎn)Mysql系列 - 第19篇：游標(biāo)詳解的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
