上虞網(wǎng)站建設(shè)baidu營銷網(wǎng)站優(yōu)化推廣
二八佳人體似酥,腰懸利劍斬愚夫,雖然不見人頭落,暗里教君骨髓枯。
上一章簡單介紹了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)的整體效率。
謝謝!!!