mysql 自定义函数系列----------日历函数

mysql 自定义函数系列

日历函数

BEGIN
DECLARE num int DEFAULT -1;
CREATE TEMPORARY TABLE under_日历 (
      yyyy int,
            q int,
      mm int,
            dd VARCHAR(15),
            w INT,
            wd VARCHAR(15)
  );
    lp:LOOP
        set num = num+1;
                insert into under_日历
select year(CURDATE()) as y,
floor((date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%m')+2)/3) as q,
date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%m') as M,
date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%Y-%m-%d') as date,
date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%u') as W,
case DAYOFWEEK(date_format(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL num DAY),'%Y-%m-%d'))-1
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
when 0 then '星期日'
end as DW;
  if num=dayofyear(concat(YEAR(now()),'-12-31'))-1 THEN LEAVE lp;
        END if;
    END LOOP;
select * from under_日历;
DROP TABLE under_日历;
END