一次运用存储历程游标碰到的坑

2019-11-18杂谈搜奇网33°c

A⁺ A^-

一次运用存储历程游标碰到的坑

　　有如许一个需求：统计某省某市某区前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