mysql 存储过程 动态表名

今天写存储过程时，遇到要将表名最为参数的问题，如果不涉及到游标的话，使用prepare可以解决问题，但是，动态表名要运用在游标中的话，则prepare就得靠边站了。

集众人之智慧，最后，使用临时表解决了问题。

如何在MySQL的存储过程中实现把过程参数用在游标定义的SELECT命令里面作为表名引用

 

首先，我们来把场景描绘一下，比如下面的例子（当然是无法正确运行的）：

1.  

2. CREATE PROCEDURE `proc`(SourceDBName CHAR(50), SourceTableName CHAR(50),

3. TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))

4. BEGIN

5. DECLARE done INT DEFAULT 0;

6. DECLARE FieldValue CHAR(50);

7. DECLARE CursorSegment CURSOR FOR SELECT ... FROM SourceDBName.SourceTableName;

8. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

9.  

10. OPEN CursorSegment;

11. REPEAT

12. FETCH CursorSegment INTO FieldValue;

13. IF NOT done THEN

14. ...

15. END IF;

16. UNTIL done END REPEAT;

17. CLOSE CursorSegment;

18. END$$

上面的例子试图通过存储过程的参数传递，向存储过程内部的游标定义传递要SELECT的数据库名称和表名称。但是，这个存储过程在运行时MySQL会提示“SourceDBName.SourceTableName”不存在。也就是说MySQL不会把SourceDBName和SourceTableName两个标识符作为局部变量去解析，而是直接作为表引用。

要解决这个问题，唯一的方法就是把上面这个存储过程分为3个存储过程。对，3个。所以说这是一个比较复杂的解决办法。

第一个存储过程，扮演的是数据收集器的角色。它接收参数传递过来的数据库名和表名，然后把数据SELECT到一个临时表中。需要注意，临时表的最大好处是它是线程安全的。

第二个存储过程，基于第一个存储过程生成的临时表而创建游标，并处理具体的工作。

第三个存储过程，作为一个入口，负责依次调用存储过程1和存储过程2，并提供相应的参数。

三个存储过程综合起来，就得到下面的例子：

1.  

2. CREATE PROCEDURE `proc1`(SourceDBName CHAR(50), SourceTableName CHAR(50))

3. BEGIN

4. DECLARE SQLStmt TEXT;

5.  

6. SET SQL_NOTES=0;

7.  

8. SET @SQLStmt = CONCAT('DROP TEMPORARY TABLE IF EXISTS tmp_table_name');

9. PREPARE Stmt FROM @SQLStmt;

10. EXECUTE Stmt;

11. DEALLOCATE PREPARE Stmt;

12.  

13. SET @SQLStmt = CONCAT('CREATE TEMPORARY TABLE tmp_table_name SELECT ... FROM ',

14. SourceDBName,'.',SourceTableName,' WHERE ... ');

15. PREPARE Stmt FROM @SQLStmt;

16. EXECUTE Stmt;

17. DEALLOCATE PREPARE Stmt;

18. END$$

19.  

20. CREATE PROCEDURE `proc2`(TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))

21. BEGIN

22. DECLARE done INT DEFAULT 0;

23. DECLARE FieldValue CHAR(50);

24. DECLARE CursorSegment CURSOR FOR SELECT Period FROM tmp_table_name;

25. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

26.  

27. OPEN CursorSegment;

28. REPEAT

29. FETCH CursorSegment INTO FieldValue;

30. IF NOT done THEN

31. ...

32. END IF;

33. UNTIL done END REPEAT;

34. CLOSE CursorSegment;

35. END$$

36.  

37. CREATE PROCEDURE `proc3`(SourceDBName CHAR(50), SourceTableName CHAR(50),

38. TargetDBName CHAR(50), TargetTemplateTableName CHAR(50))

39. BEGIN

40. CALL proc1(SourceDBName, SourceTableName);

41. CALL proc2(TargetDBName, TargetTemplateTableName);

42. END$$

43.  

补充：运行前需要把系统参数变量“sql_notes”设置为0，否则proc1在DROP TABLE时会停下来。原因是

1.  

2. "SQL_NOTES = {0 | 1}

3. If set to 1 (the default), warnings of Note level are recorded.

4. If set to 0, Note warnings are suppressed."