CREATE TABLE `year_date_all` (
`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:法定休息日',
PRIMARY KEY (`repDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='全年日期表';
CREATE 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;
// 判断是否数据存在
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;
INSERT INTO year_date_all(repDate,repYear,repMonth,repDay,repWeek,db_status) VALUES(startDay,YEAR(startDay),MONTH(startDay),DAY(startDay),WEEKOFYEAR(startDay),
(
select case when date_format(startDay,'%w') in (0,6) then 1 else 0 end
)
);
SET count=count+1;
SET startDay=DATE_ADD(DATE(startDay),INTERVAL 1 DAY);
END WHILE;
END IF;
END
文章借鉴文章借鉴