MYSQL的存储过程如何在游标声明中用变量去存储和引用数据库名称

作者：Sender Su 来源：原创内容发布日期：2022-02-12 最后修改日期：2022-02-12

多年前，stackoverflow上有人问：“MySQL Stored Procedures : Use a variable as the database name in a cursor declaration”，MYSQL的存储过程如何在游标声明中用变量去存储和引用数据库名称。对于这个问题，我的回答内容是唯一正确的做法，且以后STACKOVERFLOW上再无类似的问题产生。当时回答完了也懒得搬运到自己的网站，反正大家都懂英文是不是，都能上STACKOVERFLOW是不是......只是，现在看还是要搬运回来。

笔者：国际认证信息系统审计师、软考系统分析师

原文链接如下：
https://stackoverflow.com/questions/1680850/mysql-stored-procedures-use-a-variable-as-the-database-name-in-a-cursor-declar

这个问题产生的时候，刚好我就在研究学习如何写MYSQL的存储过程。于是进行了解答。当然，现在需要先说明的是，无论是什么数据库，存储过程、触发器，这些都是可以发挥强大作用但又极其容易踩坑的功能。尤其是在事务操作中如果涉及调用存储过程或触发器，尤其要小心会否产生死锁。

言归正传。当时提出的问题是：

在声明游标时，需要通过变量实现在查询中给出数据库名称。但MYSQL不允许。因为MYSQL仅允许在游标声明时给出符合标准SQL语法的语句。

提问人尝试了如下两种做法都失败：

尝试1：
CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)
cdr_records: BEGIN
DECLARE cdr_record_cursor CURSOR FOR
SELECT cdrs_id, called, calling FROM dbName.cdrs WHERE lrn_checked = 'N';

# Setup logging
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
#call log_debug('Got exception in update_cdrs_lnp_data');
SET returnCode = -1;
END;
END;

尝试2：
CREATE PROCEDURE `update_cdrs_lnp_data`(IN dbName VARCHAR(25), OUT returnCode SMALLINT)
cdr_records:BEGIN
DECLARE cdr_record_cursor CURSOR FOR
SET @query = CONCAT("SELECT cdrs_id, called, calling FROM " ,dbName, ".cdrs WHERE lrn_checked = 'N' ");
PREPARE STMT FROM @query;
EXECUTE STMT;
# Setup logging
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
#call log_debug('Got exception in update_cdrs_lnp_data');
SET returnCode = -1;
END;
END;

我给出的解决办法，解释如下：

要实现这个想法，必须用3个存储过程去实现。

存储过程1，即proc1，功能类似“数据收集器”。需要把保存了数据库名称的变量，传递给proc1，并由proc1为存储过程proc2创建临时表。需要注意临时表要用

CREATE TEMPORARY TABLE tmp_table_name SELECT ...

这种语法，实现线程安全的临时表。这个临时表名无法随机。

存储过程2，即proc2，对存储过程proc1创建的临时表，声明游标。由于临时表名称是已知并硬编码到声明中，这样就不会出现“table not found”的错误。

存储过程3，即proc3，就像一个“主函数”，带有所有需要传递给proc1和proc2的参数。proc3简单地，首先调用proc1，然后调用proc2，从而完成整个处理过程。

另外，需要设置MYSQL的系统参数变量 sql_notes 为0，否则proc1在执行到 DROP TABLE 命令时就会停止。

最后，代码如下：

CREATE PROCEDURE `proc1`(SourceDBName CHAR(50), SourceTableName CHAR(50))
BEGIN
DECLARE SQLStmt TEXT;

SET @SQLStmt = CONCAT('DROP TEMPORARY TABLE IF EXISTS tmp_table_name');
PREPARE Stmt FROM @SQLStmt;
EXECUTE Stmt;
DEALLOCATE PREPARE Stmt;

SET @SQLStmt = CONCAT('CREATE TEMPORARY TABLE tmp_table_name SELECT ... FROM ',SourceDBName,'.',SourceTableName,' WHERE ... ');
PREPARE Stmt FROM @SQLStmt;
EXECUTE Stmt;
DEALLOCATE PREPARE Stmt;
END$$

CREATE PROCEDURE `proc2`(TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE FieldValue CHAR(50);
DECLARE CursorSegment CURSOR FOR SELECT ... FROM tmp_table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN CursorSegment;
REPEAT
    FETCH CursorSegment INTO FieldValue;
    IF NOT done THEN
    ...
END IF;
UNTIL done END REPEAT;
CLOSE CursorSegment;
END$$

CREATE PROCEDURE `proc3`(SourceDBName CHAR(50), SourceTableName CHAR(50), TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))
BEGIN
CALL proc1(SourceDBName, SourceTableName);
CALL proc2(TargetDBName, TargetTemplateTableName);
END$$