基本语法
DELIMITER
CREATE PROCEDURE xxx
(IN tableName VARCHAR (50),OUT opstr)
BEGIN
执行体
END$$
DELIMITER ;
创建 年月分表创建的存储过程例子
DELIMITER $$
USE vpwl_2019
$$
DROP PROCEDURE IF EXISTS create_table_month
$$
CREATE DEFINER=vpzb
@%
PROCEDURE create_table_month
(IN tableName VARCHAR (50), IN YEAR INT)
BEGIN
DECLARE i INT ;
DECLARE i_count VARCHAR (20) ;
DECLARE timeStr VARCHAR (20) ;
SET i = 0 ;
WHILE
i < 12 DO SET i_count = CONCAT(YEAR, (i + 1)) ;
SET timeStr = DATE_FORMAT(
STR_TO_DATE(i_count, '%Y%m'),
'%Y%m'
) ;
SET @sqlstrNew = CONCAT(
'CREATE TABLE ',
tableName,
'_',
timeStr,
' like ',
tableName
) ;
PREPARE stmtNew FROM @sqlstrNew ;
EXECUTE stmtNew ;
SET i = i + 1 ;
END WHILE ;
END$$
DELIMITER ;
image.png
网友评论