MySQL 存储过程：在游标声明中使用变量作为数据库名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1680850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Stored Procedures : Use a variable as the database name in a cursor declaration
提问by Justin Noel
I need to use a variable to indicate what database to query in the declaration of a cursor. Here is a short snippet of the code :
我需要使用一个变量来指示在游标声明中要查询的数据库。这是代码的一小段：
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;
As you can see, I'm TRYING to use the variable dbName to indicate in which database the query should occur within. However, MySQL will NOT allow that. I also tried things such as :
如您所见，我正在尝试使用变量 dbName 来指示查询应在哪个数据库中进行。但是，MySQL 不允许这样做。我也尝试过诸如：
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;
Of course this doesn't work either as MySQL only allows a standard SQL statement in the cursor declaration.
当然，这也不起作用，因为 MySQL 只允许在游标声明中使用标准 SQL 语句。
Can anyone think of a way to use the same stored procedure in multiple databases by passing in the name of the db that should be affected?
任何人都可以通过传入应该受到影响的数据库的名称来想出一种在多个数据库中使用相同存储过程的方法吗？
回答by Sender
The answer of Vijay Jadhavis the right way to solve this limitation by MySQL. Actually, you need 3 proc to accomplish it:
Vijay Jadhav的答案是解决 MySQL 限制的正确方法。实际上，您需要 3 个 proc 来完成它：
proc1 using Vijay Jadhav's way, works like a data collector. You need to pass the variables to proc1 and let it create the tmp table for proc2. There is one limiation of Vijay's way, he should create a TEMPORARY table by using "CREATE TEMPORARY TABLE tmp_table_name SELECT ...". Because temporary table is thread safe.
proc1 使用 Vijay Jadhav 的方式，就像一个数据收集器。您需要将变量传递给 proc1 并让它为 proc2 创建 tmp 表。Vijay 的方法有一个限制，他应该使用“CREATE TEMPORARY TABLE tmp_table_name SELECT ...”创建一个临时表。因为临时表是线程安全的。
proc2 declare the cursor on the tmp table which is created by proc1. Since the tmp table is already known and hard coded into the declaration, no more "table not found" error.
proc2 在 proc1 创建的 tmp 表上声明游标。由于 tmp 表是已知的并且硬编码到声明中，因此不会再出现“找不到表”错误。
proc3 works like a "main" function, with all the parameters need to be sent to proc1 and proc2. proc3 simply calls proc1 first and then proc2 with the parameters need by each proc.
proc3 就像一个“主”函数，所有参数都需要发送到 proc1 和 proc2。proc3 简单地先调用 proc1，然后调用 proc2，并使用每个 proc 所需的参数。
p.s Need to set system variable "sql_notes" to 0, otherwise proc1 will stop on DROP TABLE command.
ps 需要将系统变量“sql_notes”设置为0，否则proc1将停止在DROP TABLE命令上。
Here is my example:
这是我的例子：
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$$
回答by Vijay Jadhav
Try to create (temporary) table using prepared statement in a different procedure.
尝试在不同的过程中使用准备好的语句创建（临时）表。
SET @query = CONCAT("CREATE TABLE temp_table AS SELECT cdrs_id, called, calling FROM " ,dbName, ".cdrs WHERE lrn_checked = 'N' ");
...
...
And then select data from that table in your 'test' procedure.
然后在“测试”过程中从该表中选择数据。
回答by noonex
No, you can't do that in cursors. Maybe just prepared statements may do the job? :
不，你不能在游标中做到这一点。也许只是准备好的语句可以完成这项工作？：
delimiter ;;
create procedure test(in dbName varchar(40))
begin
set @query := CONCAT("SELECT * FROM " , dbName, ".db;");
PREPARE s from @query;
EXECUTE s;
DEALLOCATE PREPARE s;
end;;
delimiter ;
call test("mysql");
回答by Justin Noel
The answer to this is that it cannot be done. You cannot use variables in the cursor declaration. I appreciate noonex's response. However, his solution does not allow me to walk through the results. It simply executes the query.
对此，答案是无法做到。不能在游标声明中使用变量。我很欣赏 noonex 的回应。但是，他的解决方案不允许我浏览结果。它只是执行查询。
回答by user3692317
create procedure test(in dbName varchar(40)) READS SQL DATA<- this line returns will allow you to walk through the results begin ... $result = call test("mysql");
create procedure test(in dbName varchar(40)) READS SQL DATA<- 这行返回将允许您遍历结果 begin ... $result = call test("mysql");
