二八佳人體似酥，腰懸利劍斬愚夫，雖然不見人頭落,暗里教君骨髓枯。

上一章簡單介紹了MySQL流程控制(二十八) ,如果沒有看過,請(qǐng)觀看上一章

一. 游標(biāo)

一.一 什么是游標(biāo)

雖然我們也可以通過篩選條件 WHERE 和 HAVING，或者是限定返回記錄的關(guān)鍵字 LIMIT 返回一條記錄，但是，卻無法在結(jié)果集中像指針一樣，向前定位一條記錄、向后定位一條記錄，或者是隨意定位到某一條記錄，并對(duì)記錄的數(shù)據(jù)進(jìn)行處理。

這個(gè)時(shí)候，就可以用到游標(biāo)。游標(biāo)，提供了一種靈活的操作方式，讓我們能夠?qū)Y(jié)果集中的每一條記錄進(jìn)行定位，并對(duì)指向的記錄中的數(shù)據(jù)進(jìn)行操作的數(shù)據(jù)結(jié)構(gòu)。游標(biāo)讓 SQL 這種面向集合的語言有了面向過程開發(fā)的能力。

在 SQL 中，游標(biāo)是一種臨時(shí)的數(shù)據(jù)庫對(duì)象，可以指向存儲(chǔ)在數(shù)據(jù)庫表中的數(shù)據(jù)行指針。這里游標(biāo)充當(dāng)了指針的作用，我們可以通過操作游標(biāo)來對(duì)數(shù)據(jù)行進(jìn)行操作。

MySQL中游標(biāo)可以在存儲(chǔ)過程和函數(shù)中使用。

比如，我們查詢了 employees 數(shù)據(jù)表中工資高于15000的員工都有哪些：

select * from user;

這里我們就可以通過游標(biāo)來操作數(shù)據(jù)行，如圖所示此時(shí)游標(biāo)所在的行是“108”的記錄，我們也可以在結(jié)果集上滾動(dòng)游標(biāo)，指向結(jié)果集中的任意一行。

一.二 使用游標(biāo)步驟

游標(biāo)必須在聲明處理程序之前被聲明，并且變量和條件還必須在聲明游標(biāo)或處理程序之前被聲明。

如果我們想要使用游標(biāo)，一般需要經(jīng)歷四個(gè)步驟。不同的 DBMS 中，使用游標(biāo)的語法可能略有不同。

第一步，聲明游標(biāo)

在MySQL中，使用DECLARE關(guān)鍵字來聲明游標(biāo)，其語法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement; 

這個(gè)語法適用于 MySQL，SQL Server，DB2 和 MariaDB。

要使用 SELECT 語句來獲取數(shù)據(jù)結(jié)果集，而此時(shí)還沒有開始遍歷數(shù)據(jù)，這里 select_statement 代表的是 SELECT 語句，返回一個(gè)用于創(chuàng)建游標(biāo)的結(jié)果集。

比如：

DECLARE cur_emp CURSOR FOR 
SELECT age FROM user;

DECLARE cursor_fruit CURSOR FOR 
SELECT age FROM user;

第二步，打開游標(biāo)

打開游標(biāo)的語法如下：

OPEN cursor_name

當(dāng)我們定義好游標(biāo)之后，如果想要使用游標(biāo)，必須先打開游標(biāo)。打開游標(biāo)的時(shí)候 SELECT 語句的查詢結(jié)果集就會(huì)送到游標(biāo)工作區(qū)，為后面游標(biāo)的逐條讀取結(jié)果集中的記錄做準(zhǔn)備。

OPEN　cur_emp ;

第三步，使用游標(biāo)（從游標(biāo)中取得數(shù)據(jù)）

語法如下：

FETCH cursor_name INTO var_name [, var_name] ...

這句的作用是使用 cursor_name 這個(gè)游標(biāo)來讀取當(dāng)前行，并且將數(shù)據(jù)保存到 var_name 這個(gè)變量中，游標(biāo)指針指到下一行。如果游標(biāo)讀取的數(shù)據(jù)行有多個(gè)列名，則在 INTO 關(guān)鍵字后面賦值給多個(gè)變量名即可。

注意：var_name必須在聲明游標(biāo)之前就定義好。

FETCH　cur_emp INTO emp_age;

注意：游標(biāo)的查詢結(jié)果集中的字段數(shù)，必須跟 INTO 后面的變量數(shù)一致，否則，在存儲(chǔ)過程執(zhí)行的時(shí)候，MySQL 會(huì)提示錯(cuò)誤。

第四步，關(guān)閉游標(biāo)

CLOSE cursor_name

有 OPEN 就會(huì)有 CLOSE，也就是打開和關(guān)閉游標(biāo)。當(dāng)我們使用完游標(biāo)后需要關(guān)閉掉該游標(biāo)。因?yàn)橛螛?biāo)會(huì)占用系統(tǒng)資源，如果不及時(shí)關(guān)閉，游標(biāo)會(huì)一直保持到存儲(chǔ)過程結(jié)束，影響系統(tǒng)運(yùn)行的效率。而關(guān)閉游標(biāo)的操作，會(huì)釋放游標(biāo)占用的系統(tǒng)資源。

關(guān)閉游標(biāo)之后，我們就不能再檢索查詢結(jié)果中的數(shù)據(jù)行，如果需要檢索只能再次打開游標(biāo)。

CLOSE　cur_emp;

一.三 游標(biāo)的例子

使用游標(biāo)統(tǒng)計(jì)每個(gè)性別的總年齡

DELIMITER //
CREATEPROCEDURE `demo`.`c1`(OUT sum_age int ,IN s_sex varchar(50))-- 存儲(chǔ)過程體BEGINdeclare t_age int default 0;declare t_sex varchar(20) default '';-- 創(chuàng)建結(jié)束標(biāo)志變量DECLARE done INT DEFAULT 0 ;-- 1. 定義游標(biāo)declare cur_emp cursor for select sex,age from user ;-- 指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;-- 設(shè)置年齡為0 set sum_age = 0;-- 2. 打開游標(biāo)open cur_emp;-- 3. 循環(huán)使用游標(biāo)read_loop: LOOPfetch cur_emp into t_sex,t_age ;if done then leave read_loop;end if;-- 沒有結(jié)束的話，進(jìn)行處理，即主要的業(yè)務(wù)邏輯if t_sex =s_sex then set sum_age = sum_age + t_age;end if;-- 結(jié)束游標(biāo)end LOOP;-- 4. 關(guān)閉游標(biāo)close cur_emp;END //
DELIMITER ;

調(diào)用游標(biāo):

-- 調(diào)用游標(biāo)set @sum_age = 0;call c1(@sum_age, '男');-- 62 
select @sum_age;call c1(@sum_age, '女');-- 60 
select @sum_age;

游標(biāo)設(shè)置數(shù)據(jù)更新

DELIMITER //
CREATEPROCEDURE `demo`.`c2`(OUT sum_age int ,IN s_sex varchar(50))-- 存儲(chǔ)過程體BEGINdeclare t_age int default 0;declare t_sex varchar(20) default '';declare t_id int default 0;-- 創(chuàng)建結(jié)束標(biāo)志變量DECLARE done INT DEFAULT 0 ;-- 1. 定義游標(biāo)declare cur_emp cursor for select sex,age,id from user ;-- 指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;-- 設(shè)置年齡為0 set sum_age = 0;-- 2. 打開游標(biāo)open cur_emp;-- 3. 循環(huán)使用游標(biāo)read_loop: LOOPfetch cur_emp into t_sex,t_age,t_id ;if done then leave read_loop;end if;-- 沒有結(jié)束的話，進(jìn)行處理if t_sex =s_sex then update user set name = concat(t_sex,t_age) where id = t_id;set sum_age = sum_age + t_age;end if;-- 結(jié)束游標(biāo)end LOOP;-- 4. 關(guān)閉游標(biāo)close cur_emp;END //
DELIMITER ;

調(diào)用：

游標(biāo)是 MySQL 的一個(gè)重要的功能，為逐條讀取結(jié)果集中的數(shù)據(jù)，提供了完美的解決方案。跟在應(yīng)用層面實(shí)現(xiàn)相同的功能相比，游標(biāo)可以在存儲(chǔ)程序中使用，效率高，程序也更加簡潔。

但同時(shí)也會(huì)帶來一些性能問題，比如在使用游標(biāo)的過程中，會(huì)對(duì)數(shù)據(jù)行進(jìn)行加鎖，這樣在業(yè)務(wù)并發(fā)量大的時(shí)候，不僅會(huì)影響業(yè)務(wù)之間的效率，還會(huì)消耗系統(tǒng)資源，造成內(nèi)存不足，這是因?yàn)橛螛?biāo)是在內(nèi)存中進(jìn)行的處理。

建議：養(yǎng)成用完之后就關(guān)閉的習(xí)慣，這樣才能提高系統(tǒng)的整體效率。

謝謝!!!