drop PROCEDURE if EXISTS create_table;
delimiter //
create procedure create_table()
begin
declare date_end date;
declare date_tmp date;
declare str_sql varchar(4000);
declare sql_tmp varchar(4000);
declare tbl_nm varchar(50);
declare month_day char(4);
set @date_end=date_format(date_add(now(), INTERVAL 1 year), '%Y%m%d');
set @date_tmp=date_format(date_add(now(), INTERVAL 0 day), '%Y%m%d');
set @str_sql="
(
`name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`tel` char(11) NOT NULL,
PRIMARY KEY (`tel`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;";
repeat
set @month_day=substr(@date_tmp, 5, 8);
set @tbl_nm=concat('tbl_student_', @month_day);
set @sql_tmp=concat('create table ', @tbl_nm, @str_sql);
-- select @sql_tmp;
prepare sql_tmp from @sql_tmp;
execute sql_tmp;
set @date_tmp=date_format(date_add(@date_tmp, INTERVAL 1 day), '%Y%m%d');
until @date_tmp=@date_end
end repeat;
end;
//
delimiter ;
call create_table();
drop PROCEDURE if EXISTS create_table;
delimiter //
create procedure create_table()
begin
declare date_end date;
declare date_tmp date;
declare sql_tmp varchar(4000);
declare tbl_nm varchar(50);
declare month_day char(4);
set @date_end=date_format(date_add(now(), INTERVAL 1 year), '%Y%m%d');
set @date_tmp=date_format(date_add(now(), INTERVAL 0 day), '%Y%m%d');
repeat
set @month_day=substr(@date_tmp, 5, 8);
select @month_day;
set @tbl_nm=concat('tbl_student_', @month_day);
set @sql_tmp=concat('drop table if EXISTS ', @tbl_nm);
prepare sql_tmp from @sql_tmp;
execute sql_tmp;
set @date_tmp=date_format(date_add(@date_tmp, INTERVAL 1 day), '%Y%m%d');
until @date_tmp=@date_end
end repeat;
end;
//
delimiter ;
call create_table();