如何在 MySQL 儲存過程中使用準備好的語句？

我正在使用 mysql，我需要以某種方式在後面的查詢中使用準備好的語句返回的列 curid。我使用準備好的語句，因為正如我所讀到的，這是將變數傳遞給 LIMIT 子句的唯一方法。我在這裡有這個儲存過程：

DROP PROCEDURE IF EXISTS fixbalance;
CREATE PROCEDURE fixbalance (userid INT)
 BEGIN
 DECLARE i INT DEFAULT 0;
 DECLARE balance INT DEFAULT 0;
 DECLARE idcnt INT;

 SET idcnt = (SELECT COALESCE(COUNT(id), 0) 
              FROM coupon_operations 
              WHERE user_id = userid);
 IF idcnt <> 0 THEN
   WHILE i <= idcnt DO
     BEGIN
       SET @iter = i;
       SET @user_id = userid; 
       SET @sql = CONCAT('SELECT id AS curid 
                          FROM coupon_operations 
                          WHERE user_id = ? 
                          ORDER BY id ASC 
                          LIMIT ?, 1');
       PREPARE stmt FROM @sql;
       EXECUTE stmt USING @user_id, @iter;
       DEALLOCATE PREPARE stmt;
       SET balance = balance + (SELECT points 
                                FROM coupon_operations 
                                WHERE user_id = @user_id 
                                AND id = @curid);
       UPDATE coupon_operations SET balance = balance;
       SET i = i + 1;
     END;
   END WHILE;
 END IF;
END;
|

這不起作用 - 我不確定如何通過 curid。

解決方案是在準備好的語句本身中設置變數，如下所示：

SET @sql = CONCAT('SET @curid = SELECT id
                               FROM coupon_operations 
                               WHERE user_id = ? 
                               ORDER BY id ASC 
                               LIMIT ?, 1');

我很高興你找到了答案。另一種解決方案是使用SELECT…INTO語法：

SET @sql = CONCAT('SELECT id INTO @curid FROM coupon_operations 
                  WHERE user_id = ? 
                  ORDER BY id ASC 
                  LIMIT ?, 1');

引用自：https://dba.stackexchange.com/questions/1935