存储过程-示例01

1.循环遍历结果集，并通过结果集中的结果为参数执行业务逻辑

-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS statistics_otp;
-- 创建过程
CREATE PROCEDURE statistics_otp()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE createdate VARCHAR(20);
	-- 定义游标
	DECLARE report CURSOR FOR SELECT DATE_FORMAT(CREATED_TIME,'%Y-%m-%d') FROM otp_business_logs GROUP BY DATE_FORMAT(CREATED_TIME,'%Y-%m-%d');
	-- 声明当游标到最后时 标志 s 变成 1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	OPEN report;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		FETCH report into createdate;
		-- while 循环 当s ！= 1 时 执行循环
		WHILE s <> 1 DO
			-- 执行业务逻辑
			INSERT INTO otp_statistics (ACT,APP_ID,DEVICE_MODEL,PLATFORM,OTP_MODE ,OTP_TYPE ,STATISTICS_DATA,CREATED_TIME) 
			SELECT ACT,APP_ID,DEVICE_MODEL,PLATFORM,OTP_MODE ,OTP_TYPE ,count(1) AS 'STATISTICS_DATA',CREATED_TIME FROM otp_business_logs
			WHERE DATE_FORMAT(CREATED_TIME,'%Y-%m-%d') = createdate and ACT in('register','unregister','verify') 
			GROUP BY ACT,APP_ID,DEVICE_MODEL,PLATFORM,OTP_MODE,OTP_TYPE;
			-- 将游标中的值再次复制给变量
			FETCH report into createdate;
		END WHILE;
	-- 关闭游标
	CLOSE report;
END;

转载于:https://my.oschina.net/u/3086656/blog/3099698