-- 调用存储过程
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;
数据库:存储过程+游标
于 2021-03-15 10:49:48 首次发布