Sql通过存储过程生成全年日历考勤表数据

#2020云栖大会#阿里云海量offer来啦！投简历、赢阿里云限量礼品及阿里云ACA认证免费考试资格！>>>

需求: 生成考勤日历表

CREATE TABLE `year_date_all` (
    `id` int primary key AUTO_INCREMENT COMMENT 'id',
    `repDate` date NOT NULL COMMENT '日期',
    `repYear` int(8) NOT NULL COMMENT '年号',
    `repMonth` int(8) NOT NULL COMMENT '月',
    `repDay` int(8) NOT NULL COMMENT '天',
    `repWeek` int(8) NOT NULL COMMENT '周',
    `db_status` int(8) DEFAULT '0' COMMENT '0:工作日；1：周末休息日；2：法定休息日；3：调休工作日',
    `repWeeks` varchar(50)  NOT NULL COMMENT '星期(*)'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全年日期表';


# 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `getAllYearDateFunc`(in yaerParam int(8))
BEGIN
# 默认数据
DECLARE count INT DEFAULT 0;
DECLARE startDay DATE ;
DECLARE yearNum INT DEFAULT 365;
DECLARE workOrWeek INT DEFAULT 1;
DECLARE yearBool INT DEFAULT 0;
DECLARE db_status_ INT DEFAULT 0;
DECLARE repWeeks varchar(50) DEFAULT '星期一';
# 判断数据是否存在
 SELECT count(*) INTO yearBool FROM year_date_all WHERE repYear=yaerParam;
 IF yearBool=0 THEN
 SET startDay = DATE(concat(yaerParam,'-01-01')); 
# 判断平闰年
 IF yaerParam  % 4 = 0 AND yaerParam  % 100 <> 0 OR yaerParam  % 400 = 0 THEN 
  SET yearNum = 366;
 ELSE
  SET yearNum = 365;
 END IF;

 WHILE count < yearNum DO
 SET  workOrWeek = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day));
# 判断是否是周六周日
 IF (workOrWeek)>5 THEN  
   SET db_status_=1;
 ELSE 
   SET db_status_=0;
 END IF;
#判断是星期几
 set repWeeks = (case workOrWeek  when 7 then '星期日' when 1 then '星期一' when 2 then '星期二' when 3 then '星期三' when 4 then '星期四' when 5 then '星期五' when 6 then '星期六' end);
# 写入数据
 INSERT INTO year_date_all(id,repDate,repYear,repMonth,repDay,repWeek,db_status,repWeeks) VALUES(null,startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay),db_status_,repWeeks);
 
 SET count=count+1;
 SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
END WHILE;
END IF;
END

# 执行存储过程
call getAllYearDateFunc(2020)
# 删除存储过程
DROP PROCEDURE IF EXISTS getAllYearDateFunc