记录一下~~
实现生成指定年份的工作日休息日数据,并将生成的数据放入表year_data中
1、创建带参存储过程(以创建2023年数据为例)
create procedure getAllYearInfo(in yearParam int(8))
begin
DECLARE count INT default 0;
DECLARE startDay Date;
DECLARE yearNum INT DEFAULT 365;--默认一年365天
DECLARE week varchar(10);--周几
DECLARE stauts varchar(3);--工作日或休息日
DECLARE yearCount INT DEFAULT 0;
--先判断year_data表中是否存在指定年份的数据,不存在才可进行以下操作
SELECT count(*) into yearCount FROM year_data WHERE YEAR = yearParam;
if yearCount = 0 then
set startDay = DATE(concat(yearParam,'-01-01'));
--判断是否是闰年,确定一年的总天数
if yearParam % 4 = 0 AND yearParam % 100<>0 OR yearParam % 400 = 0 THEN
set yearNum = 366;
end if;
set @r:= 202406110000000;--YEAR_DATE_ID参数值
--if条件需要根据每年的一月一号是周几进行相应的调整
--此处以生成2023年的数据为例,2023-01-01是周日,所以count % 7 = 0为星期日
while count < yearNum DO
if count %7 = 0 then
set week = '星期日';
set stauts = '2';--休息日
elseif count %7 = 1 then
set week = '星期一';
set stauts = '1';--工作日
elseif count %7 = 2 then
set week = '星期二';
set stauts = '1';
elseif count %7 = 3 then
set week = '星期三';
set stauts = '1';
elseif count %7 = 4 then
set week = '星期四';
set stauts = '1';
elseif count %7 = 5 then
set week = '星期五';
set stauts = '1';
elseif count %7 = 6 then
set week = '星期六';
set stauts = '2';
end if;
INSERT INTO year_data (YEAR_DATE_ID,RID,YM_DATE,YEAR,MON,DAYS,WEEK,RESTDAY_FLAG,CRTE_TIME)
values(cast(@r:=@r+1 as char), concat('000000',cast(@r:=@r+1 as char)),startDay,year(startDay),Month(startDay),Day(startDay),week,stauts,now());
set @r:= @r+1;
SET count=count+1;
SET startDay = DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
end while;
end if;
end;
2、调用存储过程
call getAllYearInfo(2023);
3、如需记录法定节假日、调休工作日等信息,可以update具体日期的值