一次使用存儲過程游標遇到的坑
- 2019 年 11 月 2 日
- 筆記
一次使用存儲過程游標遇到的坑
有這樣一個需求：統計某省某市某區前6個月的數據，直接sql查詢效率很低，於是打算做定時任務，用定時器執行存儲過程的方式在每月初統計上月的相關數據。
使用存儲過程就要用到游標了，之前很少寫存儲過程，對游標也不是熟悉，咋辦呢，現學現用啦。
創建存儲過程
1 CREATE 2 [DEFINER = { user | CURRENT_USER }] 3 PROCEDURE sp_name ([proc_parameter[,...]]) 4 [characteristic ...] routine_body 5 6 proc_parameter: 7 [ IN | OUT | INOUT ] param_name type 8 9 characteristic: 10 COMMENT 'string' 11 | LANGUAGE SQL 12 | [NOT] DETERMINISTIC 13 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 14 | SQL SECURITY { DEFINER | INVOKER } 15 16 routine_body: 17 Valid SQL routine statement 18 19 [begin_label:] BEGIN 20 [statement_list] 21 …… 22 END [end_label] MYSQL 存儲過程中的關鍵語法
聲明語句結束符，可以自定義:
1 DELIMITER $$ 2 或 3 DELIMITER // 聲明存儲過程:
1 CREATE PROCEDURE demo_in_parameter(IN p_in int) 存儲過程開始和結束符號:
1 BEGIN .... END 變數賦值:
1 SET @p_in=1 變數定義:
1 DECLARE l_int int unsigned default 0; 創建mysql存儲過程、存儲函數:
1 create procedure 存儲過程名(參數) 存儲過程體:
1 create function 存儲函數名(參數)
游標
簡介
游標實際上是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。
游標充當指針的作用。
儘管游標能遍歷結果中的所有行，但他一次只指向一行。
游標的作用就是用於對查詢資料庫所返回的記錄進行遍歷，以便進行相應的操作。
用法
一、聲明一個游標: declare 游標名稱 CURSOR for table;(這裡的table可以是查詢出來的任意集合)
二、打開定義的游標:open 游標名稱;
三、獲得下一行數據:FETCH 游標名稱 into testrangeid,versionid（和查出的欄位順序保持一致）;
四、需要執行的語句(增刪改查):這裡視具體情況而定
五、釋放游標:CLOSE 游標名稱;
注:mysql存儲過程每一句後面必須用;結尾，使用的臨時欄位需要在定義游標之前進行聲明。
先寫之前出現的錯誤寫法：
這樣寫出的雙重循環，裡面的會執行6次，外層只會執行一次，如果在後面把done置為0就會出現死循環。
1 CREATE DEFINER=`root`@`%` PROCEDURE `p_month_count_init`() 2 BEGIN 3 4 -- 定義變數 5 DECLARE done int DEFAULT 0; 6 declare i int DEFAULT 0; 7 declare v_yearMonth varchar(6); 8 declare v_provinceCode varchar(50) default '';-- 省 9 declare v_cityCode varchar(50) default '';-- 市 10 11 -- 定義游標，並將sql結果集賦值到游標中 12 DECLARE cur CURSOR FOR 13 select province_code,city_code from dt_lift_info where is_delete = 0 group by province_code,city_code; 14 -- 聲明當游標遍歷完後將標誌變數置成某個值 15 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 16 -- 打開游標 17 open cur; 18 19 fetch cur into v_provinceCode,v_cityCode; 20 -- 當done不等於1，也就是未遍歷完時，會一直循環 21 while done<>1 do 22 while i<6 DO -- 循環開始 23 set i=i+1; 24 set v_yearMonth = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL i MONTH), '%Y%m'); 25 call p_month_count(v_yearMonth,v_provinceCode,v_cityCode); 26 end while; -- 循環結束 27 -- 將游標中的值再賦值給變數，供下次循環使用 28 fetch cur into v_provinceCode,v_cityCode; 29 -- 當s等於1時表明遍歷以完成，退出循環 30 end while; 31 -- 關閉游標 32 close cur; 33 END
最後改成這樣寫就可以了。 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p_month_count_init`() 2 BEGIN 3 -- 定義變數 4 DECLARE done int DEFAULT 0; 5 declare i int DEFAULT 0; 6 declare v_yearMonth varchar(6); 7 declare v_provinceCode varchar(50) default '';-- 省 8 declare v_cityCode varchar(50) default '';-- 市 9 declare v_areaCode varchar(50) default '';-- 區 10 11 -- 定義游標，並將sql結果集賦值到游標中 12 DECLARE cur CURSOR FOR 13 select province_code,city_code,area_code from dt_lift_info where is_delete = 0 group by province_code,city_code,area_code; 14 -- 聲明當游標遍歷完後將標誌變數置成某個值 15 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 16 17 first_loop:LOOP 18 IF i >= 6 THEN 19 LEAVE first_loop; 20 END IF; 21 set i=i+1; 22 set v_yearMonth = DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL i MONTH), '%Y%m'); 23 -- 打開游標 24 open cur; 25 second_loop:LOOP 26 fetch cur into v_provinceCode,v_cityCode,v_areaCode; 27 IF done = 1 THEN 28 LEAVE second_loop; 29 END IF; 30 call p_month_count(v_yearMonth,v_provinceCode,v_cityCode,v_areaCode); 31 end LOOP second_loop; 32 SET done = 0; -- 注意這個別漏了 33 -- 關閉游標 34 close cur; 35 END LOOP first_loop; 36 END 做完這個存儲過程之後，瞬間感覺對存儲過程理解的深了一點。
