MySQL存储过程:表名为变量

原创
2017/11/16 17:44
阅读数 402

如果一个存储过程的变化的部分只有表名的部分,我们可以给存储过程传入这个表名。这就需要我们承接一下传入的参数,然后使用PREPARE了,关于PREPARE,需要参考官方文档来解释一下,这里先贴上解决问题的代码:

DELIMITER $$

USE `reptail`$$

DROP PROCEDURE IF EXISTS `updateTest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateTest`(IN table_name VARCHAR(50))
BEGIN
    SET @tbl_name=CONCAT("",table_name);
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x1` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x2` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x3` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x4` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x5` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    END$$

DELIMITER ;

问题解决了,那么,何为PREPARE呢?时至2017年8月24日20:11:48,官方的手册的解释是:

The PREPARE statement prepares a SQL statement and assigns it a name, stmt_name, by which to refer to the statement later. The prepared statement is executed withEXECUTE and released with DEALLOCATE PREPARE.

翻译为中文的大概意思就是:

PREPARE语句准备一个SQL的statement,并且,它会给这个statement一个名字以便我们之后引用。我们可以使用EXECUTE语句执行这个准备好的statement,也可以使用DEALLOCATE PREPARE来释放掉它。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部