SQL每日一题F0729，存储过程创建日历表

学习了,谢谢UP！顺带 MySQL版(只求编译通过的写法(哭笑))

drop table if exists calendar_info;

create table if not exists calendar_info(

country varchar(5) comment "国家",

cdr_code varchar(5) comment "日历代码",

date_name varchar(20) comment "日期名称",

year char(4) comment "年",

month char(2) comment "月",

week char(9) comment "星期",

day char(2) comment "日",

cdr_date char(10) comment "日历星期",

creator varchar(32) comment"创建人",

create_date datetime(3) comment "创建日期",

create_inst varchar(20) comment "创建机构",

del_flag char(1) default 0 comment "删除标志:0不删;1已删",

ts datetime(3) default now(3) comment "时间戳"

)engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

-----------------------------------

drop procedure if exists proc_calendar;

delimiter //

create procedure proc_calendar(in thisyear int(4))

begin

declare start_date varchar(20);

declare end_date varchar(20);

declare date_count int default 0;

set @i = 0;

set start_date = concat(thisyear, "-01-01");

set end_date = concat(thisyear + 1, "-01-01");

delete from calendar_info where year = thisyear;

set date_count = timestampdiff(day, start_date, end_date);

while @i < date_count 

do

insert into calendar_info(country, cdr_code, date_name, year, month, week, day, cdr_date, creator, create_date, create_inst) 

(select "CN", "CN01", 

convert(date_format(start_date , "%Y-%m-%d") , char(10)) as date_name,

convert(year(start_date), char(4)) as year,

convert(month(start_date), char(2)) as month,

convert(dayname(start_date), char(10)) as week,

convert(day(start_date), char(2)) as day,

convert(start_date, char(10)) as cdr_date,

"本人",

now(3) as create_date,

"SQL数据库开发");

set @i = @i + 1;

set start_date = convert(adddate(start_date, 1) , char(10));

end while;

end//

delimiter ; 

call proc_calendar(2023);

select * from calendar_info;