首先,我们来把场景描绘一下,比如下面的例子(当然是无法正确运行的):
CREATE PROCEDURE `proc`(SourceDBName CHAR(50), SourceTableName CHAR(50),
TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE FieldValue CHAR(50);
DECLARE CursorSegment CURSOR FOR SELECT ... FROM SourceDBName.SourceTableName;
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$$
上面的例子试图通过存储过程的参数传递,向存储过程内部的游标定义传递要SELECT的数据库名称和表名称。但是,这个存储过程在运行时MySQL会提示“SourceDBName.SourceTableName”不存在。也就是说MySQL不会把SourceDBName和SourceTableName两个标识符作为局部变量去解析,而是直接作为表引用。
要解决这个问题,唯一的方法就是把上面这个存储过程分为3个存储过程。对,3个。所以说这是一个比较复杂的解决办法。
第一个存储过程,扮演的是数据收集器的角色。它接收参数传递过来的数据库名和表名,然后把数据SELECT到一个临时表中。需要注意,临时表的最大好处是它是线程安全的。
第二个存储过程,基于第一个存储过程生成的临时表而创建游标,并处理具体的工作。
第三个存储过程,作为一个入口,负责依次调用存储过程1和存储过程2,并提供相应的参数。
三个存储过程综合起来,就得到下面的例子:
CREATE PROCEDURE `proc1`(SourceDBName CHAR(50), SourceTableName CHAR(50))
BEGIN
DECLARE SQLStmt TEXT;
SET SQL_NOTES=0;
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 Period 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$$
补充:运行前需要把系统参数变量“sql_notes”设置为0,否则proc1在DROP TABLE时会停下来。原因是
"SQL_NOTES = {0 | 1}
If set to 1 (the default), warnings of Note level are recorded.
If set to 0, Note warnings are suppressed."