PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name
-- 知識(shí)點(diǎn) 時(shí)間的處理
-- EXTRACT(unit FROM date)截取時(shí)間的指定位置值
-- DATE_ADD(date,INTERVAL expr unit) 日期運(yùn)算
-- LAST_DAY(date) 獲取日期的最后一天
-- YEAR(date) 返回日期中的年
-- MONTH(date) 返回日期的月
-- DAYOFMONTH(date) 返回日

-- 思路：循環(huán)構(gòu)建表名 user_2021_11_01 到 user_2020_11_30；并執(zhí)行create語句。
use mysql7_procedure;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
declare next_year int;
declare next_month int;
declare next_month_day int;
declare next_month_str char(2);
declare next_month_day_str char(2);
-- 處理每天的表名
declare table_name_str char(10);
declare t_index int default 1;
-- declare create_table_sql varchar(200);

-- 獲取下個(gè)月的年份
set next_year = year(date_add(now(),INTERVAL 1 month));
-- 獲取下個(gè)月是幾月
set next_month = month(date_add(now(),INTERVAL 1 month));
-- 下個(gè)月最后一天是幾號(hào)
set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
if next_month < 10
then set next_month_str = concat('0',next_month);
else
set next_month_str = concat('',next_month);
end if;
while t_index <= next_month_day do
if (t_index < 10)
then set next_month_day_str = concat('0',t_index);
else
set next_month_day_str = concat('',t_index);
end if;

set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
-- 拼接create sql語句
set @create_table_sql = concat(
'create table user_',
table_name_str,
'(`uid` INT ,`ename` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
-- FROM后面不能使用局部變量！
prepare create_table_stmt FROM @create_table_sql;
execute create_table_stmt;
DEALLOCATE prepare create_table_stmt;
set t_index = t_index + 1;
end while;
end $$
delimiter ;
call proc22_demo();

create function func_name ([param_name type[,...]])
returns type
[characteristic ...]?
begin
? ? routine_body
end;

create database mydb9_function;
-- 導(dǎo)入測(cè)試數(shù)據(jù)
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的創(chuàng)建者
-- 創(chuàng)建存儲(chǔ)函數(shù)-沒有輸輸入?yún)?shù)
drop function if exists myfunc1_emp;

delimiter $$
create function myfunc1_emp() returns int
begin
? declare cnt int default 0;
? ? select count(*) into ?cnt from emp;
? return cnt;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)函數(shù)
select myfunc1_emp();

create database mydb9_function;
-- 導(dǎo)入測(cè)試數(shù)據(jù)
use mydb9_function;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的創(chuàng)建者
-- 創(chuàng)建存儲(chǔ)函數(shù)-沒有輸輸入?yún)?shù)
drop function if exists myfunc1_emp;

delimiter $$
create function myfunc1_emp() returns int
begin
? declare cnt int default 0;
? ? select count(*) into ?cnt from emp;
? return cnt;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)函數(shù)
select myfunc1_emp();