mysql存储过程 日期参数_MYSQL 存储过程带参数 按时间创建相应的表 | 学步园

废话少说直接看代码。不明白的就留言

create procedure sm_creatMonthTbProc(mdate varchar (8))

begin

declare sm_results_name varchar(30);

set @sm_results_name = concat('sm_results_',mdate);

set @csql = concat("create table if not exists ",@sm_results_name , " (ssh_id int(20) NOT NULL,`result` text,`testime` bigint(20) NOT NULL default '0', `mid` bigint(20) NOT NULL, PRIMARY KEY (`ssh_id`,`testime`,`mid`),KEY ",@sm_results_name,"_index01 (`ssh_id`), KEY ",@sm_results_name,"_index02 (`mid`), KEY ",@sm_results_name,"_index03 (`testime`)) ENGINE=MyISAM ;");

PREPARE create_stmt from @csql;

EXECUTE create_stmt;

end

执行 命令查看是否创建成功

SHOW PROCEDURE STATUS

mysql> SHOW PROCEDURE STATUS //

+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+

| Db           | Name                | Type      | Definer        | Modified            | Created             | Security_type | Comment |

+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+

| monitordb3   | sm_creatMonthTbProc | PROCEDURE | root@localhost | 2011-11-04 16:58:11 | 2011-11-04 16:58:11 | DEFINER       |         |

| seldnsdistdb | dx_lt_proc          | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |

| seldnsdistdb | tt_dns_proc         | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |

| seldnsdistdb | yd_dns_proc         | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |

+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+

4 rows in set, 1 warning (0.00 sec)

红色行是我们创建的

执行命令删除 存储过程

DROP PROCEDURE sm_creatMonthTbProc

执行命令 调用创建的存储过程

call sm_creatMonthTbProc('20111104')

执行结果我就不贴上来了 ，不会的同学试试吧。