MySQL将表名传递给游标选择 [英] MySQL Pass table name to cursor select

问题描述

我希望该过程在select语句中使用参数 answertable 和 partid ， 但是当我调用它时，它不会用值替换参数answertable

I want the procedure to take parameter answertable and partid in the select statement, but when i call it it doesn't replace the parameter answertable with the value

通话call updateTotalScores('quiz_participation', 'quiz_answer', 1)

返回错误:1146 - Table 'quizdb.answertable' doesn't exist

传递id是有效的，但是传递表名却不可行 所以我如何将表名传递给选择中的

passing the id works, but passing the table name doesn't so how do i pass the table name to the select in

DECLARE cur1 CURSOR FOR SELECT SUM(`score`), SUM(`maxscore`) FROM answertable WHERE `idParticipation`=partid;

整个过程:

DELIMITER $$
CREATE PROCEDURE updateTotalScores(IN participationtable CHAR(64), IN answertable CHAR(64), IN partid INT)
BEGIN
 DECLARE done INTEGER DEFAULT 0;
 DECLARE sscore INTEGER DEFAULT 0;
 DECLARE smaxscore INTEGER DEFAULT 0;
 DECLARE cur1 CURSOR FOR SELECT SUM(`score`), SUM(`maxscore`) FROM answertable WHERE `idParticipation`=partid;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 OPEN cur1;
 REPEAT
  FETCH cur1 INTO sscore, smaxscore;
  UNTIL done = 1
 END REPEAT;
 CLOSE cur1;

 UPDATE participationtable SET `score`=sscore, `maxscore`=smaxscore WHERE `idParticipation`=partid;
END $$
DELIMITER ;

为了完整性

不能将表名传递给MySql游标，至少现在还不能传递

the table name cannot be passed to a MySql cursor, at least not yet

http://forge.mysql.com/worklog/task.php? id = 3433

下面的答案(已更正)

DELIMITER $$

CREATE PROCEDURE updateTotalScores(IN participation_table VARCHAR(45), IN answer_table VARCHAR(45), IN part_id INT)
BEGIN
    SET @stmt_text=CONCAT("SELECT @score := SUM(`score`), @maxscore := SUM(`maxscore`) FROM ",
                         answer_table, " WHERE `idParticipation`=",  part_id);
    PREPARE stmt FROM @stmt_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @stmt_text=CONCAT("UPDATE ", participation_table, 
                        " SET `score`=?, `maxscore`=? WHERE `idParticipation`=", part_id);
    PREPARE stmt FROM @stmt_text;
    EXECUTE stmt USING @score, @maxscore;
    DEALLOCATE PREPARE stmt;
END $$

推荐答案

我相信您不能以这种方式进行操作.

I believe you cannot do it in this manner.

为了实现这一点，您应该使用动态SQL.

In order to achieve this, you should use Dynamic SQL.

请注意，您也不能使用Dynamic SQL打开游标.但是在您的情况下，似乎不需要游标.

Note that you cannot open a cursor using Dynamic SQL either. But in your case, there seems to be no need for a cursor.

如果我正确理解了您的代码，则可以只使用用户变量，并可以使用2个动态准备好的语句来实现您要执行的操作.

If i understand your code correctly, you can just use user variables and probably achieve what you are trying to do using 2 Dynamically prepared statements.

  SET @stmt_text=CONCAT("SELECT @score = SUM(`score`), @maxscore=SUM(`maxscore`) FROM ",                
                         answertable, "WHERE `idParticipation`= ",  partid);
  PREPARE stmt FROM @stmt_text;
  EXECUTE stmt USING @a;

然后使用以下语句更新值

And then you update the values using the below statement

  SET @stmt_text=CONCAT("UPDATE", participationtable, " SET `score`=@score,  
                      `maxscore`=@maxscore WHERE `idParticipation`=", partid);

  PREPARE stmt FROM @stmt_text;
  EXECUTE stmt USING @a;

  DEALLOCATE PREPARE stmt;

注意:请检查语法.我无法对其进行测试以准确验证它，但希望您能理解.

Note: Please check the syntax. I cannot test it to verify it exactly but i hope you get the idea.

这篇关于MySQL将表名传递给游标选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！