Mysql中if-else的使用
主要是对if-else
的使用。这里给出一个存储过程,可以通过测试:
drop procedure if exists update_hour_total;
delimiter //
create procedure update_hour_total(out isSucceed int)
begin
declare currentHour int; -- 设置当前的小时
declare yesterday date;
declare i int default 1; -- 循环变量
-- 为变量赋值
select date_format(date_sub(current_date, interval 1 day),'%Y%m%d') into yesterday;
select hour(current_timestamp) into currentHour;
if (currentHour != 0)
then
update lawson rt_1 -- 首先将总数设置成当前的amount_paid
set rt_1.total_amount_paid = rt_1.amount_paid;
while i <= currentHour -- 如果小于当前小时
do
update lawson rt_1
inner join lawson rt_2
on rt_1.value_date = rt_2.value_date
and rt_2.value_time = i - 1
set rt_1.total_amount_paid = rt_1.amount_paid + rt_2.total_amount_paid
where rt_1.value_time = i;
set i = i + 1;
end while;
-- 如果当前为0时,已经把昨天23时的数据运行完毕了。这时候需要更新23时的总数
elseif(currentHour = 0)
then
update lawson rt_1 -- 首先将总数设置成当前的amount_paid
set rt_1.total_amount_paid = rt_1.amount_paid;
set i = 23; -- 将i设置成23
update lawson rt_1
inner join lawson rt_2
on rt_1.value_date = rt_2.value_date
and rt_2.value_time = i - 1
set rt_1.total_amount_paid = rt_1.amount_paid + rt_2.total_amount_paid
where rt_1.value_time = i
and rt_1.value_date = yesterday; -- 将日期设置为昨天
end if;
end //
delimiter ;