學(xué)習(xí)了,謝謝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"創(chuàng)建人",

create_date datetime(3) comment "創(chuàng)建日期",

create_inst varchar(20) comment "創(chuàng)建機(jī)構(gòu)",

del_flag char(1) default 0 comment "刪除標(biāo)志: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數(shù)據(jù)庫開發(fā)");

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;