数据库：存储过程+游标

-- 调用存储过程
call sp_name1('2017-01-01','2021-03-09');
call sp_name2();
-- 删除存储过程
drop procedure sp_name1;
drop procedure sp_name2;


create procedure sp_name1(starttime datetime,endtime datetime)
begin
	-- 创建临时表
	drop table if exists tmp_table21;
	CREATE TABLE tmp_table21 (
		`id` bigint(20)  AUTO_INCREMENT,
		`customer_id_value` varchar(36),
		`count_value` int(10),
		`product_id_value` int(10),
		`product_name_value` varchar(20),
		`pay_date_value` datetime,     -- 月份
		`pay_amount_value` decimal(20,2) ,
		`pay_principal_value` decimal(20,2) ,
		`pay_interest_value` decimal(20,2) ,
		`flag` int DEFAULT '0',     -- 1为当月计划存量  0为计划当月末及以后存量
		`statistical_time` datetime DEFAULT CURRENT_TIMESTAMP,  -- 数据统计时间
		PRIMARY KEY (`id`)
	);

-- 计算当月存量
	insert into tmp_table21(customer_id_value,count_value,product_id_value,product_name_value,pay_date_value,pay_amount_value,pay_principal_value,pay_interest_value)
		SELECT customer_id, count(*), product_id, product_name, pay_date, sum(pay_amount), sum(pay_principal), sum(pay_interest) 
		FROM repayment_bank
		where pay_date>=date_add(starttime, interval - day(starttime) + 1 day)-- 大于开始时间月初
			and pay_date<=last_day(endtime)  -- 小于结束时间月末
		GROUP BY YEAR(pay_date) * 100 + MONTH(pay_date);
	update tmp_table21 set flag = 1;

END; 




create procedure sp_name2()
begin
	DECLARE s int DEFAULT 0;
	DECLARE idvalue int;
	DECLARE timevalue datetime;
	DECLARE pricevalue varchar(256);
	DECLARE customer_id_value_name varchar(36);
	DECLARE count_value_name int(10);
	DECLARE product_id_value_name int(10);
	DECLARE product_name_value_name varchar(20);
	DECLARE pay_date_value_name datetime;
	DECLARE pay_amount_value_name  decimal(20,2);
	DECLARE pay_principal_value_name  decimal(20,2);
	DECLARE pay_interest_value_name  decimal(20,2);
	-- 定义游标，并将sql结果集赋值到游标中;
	DECLARE report CURSOR FOR select  customer_id_value,count_value,product_id_value,product_name_value,pay_date_value,pay_amount_value,pay_principal_value,pay_interest_value from tmp_table21;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
   -- 打开游标
	open report;
		 -- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		-- fetch report into idvalue,timevalue,pricevalue;  
		fetch report into customer_id_value_name,count_value_name,product_id_value_name,product_name_value_name,pay_date_value_name,pay_amount_value_name,pay_principal_value_name,pay_interest_value_name;  
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
		-- 执行业务逻辑
				insert into tmp_table21 (customer_id_value,count_value,product_id_value,product_name_value,pay_date_value,pay_amount_value,pay_principal_value,pay_interest_value)
					SELECT customer_id_value, sum(count_value), product_id_value, product_name_value, pay_date_value, sum(pay_amount_value), sum(pay_principal_value), sum(pay_interest_value) 
						FROM tmp_table21
						WHERE flag = 1 and pay_date_value>LAST_DAY(pay_date_value_name);
				-- 当s等于1时表明遍历以完成，退出循环
				fetch report into  customer_id_value_name,count_value_name,product_id_value_name,product_name_value_name,pay_date_value_name,pay_amount_value_name,pay_principal_value_name,pay_interest_value_name;  
		end while;
		select * from tmp_table21 ;
	-- 关闭游标
	close report;
END;