我希望程序接受参数答案表 and partid在选择语句中,
但是当我调用它时,它不会用值替换参数answertable
电话call updateTotalScores('quiz_participation', 'quiz_answer', 1)
返回错误:1146 - Table 'quizdb.answertable' doesn't exist
传递 id 有效,但传递表名无效
那么我如何将表名传递给 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 游标,至少目前还不能
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 $$