如何从准备好的语句中获取标量结果? [英] How to get scalar result from prepared statement?

问题描述

是否可以将准备好的语句的结果设置为变量?我正在尝试创建以下存储过程，但是失败了:

Is it possible to set the result from a prepared statement into a variable? I am trying to create the following stored procedure but it is failing:

第31行的错误1064(42000):您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'stmt USING @ m，@ c，@ a;

ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stmt USING @m, @c, @a;

DROP PROCEDURE IF EXISTS deleteAction;

DELIMITER $$
CREATE PROCEDURE deleteAction(
    IN modul CHAR(64),
    IN controller CHAR(64),
    IN actn CHAR(64))

MODIFIES SQL DATA

BEGIN

    PREPARE stmt FROM 'SELECT id 
                         FROM actions 
                        WHERE `module` = ? 
                          AND `controller` = ? 
                          AND `action` = ?';

    SET @m = modul;
    SET @c = controller;
    SET @a = actn;

    SET @i = EXECUTE stmt USING @m, @c, @a;

    DEALLOCATE PREPARE stmt;

    DELETE FROM acl WHERE action_id = @i;
    DELETE FROM actions WHERE id = @i; 

END 
$$
DELIMITER ;

推荐答案

这似乎很奇怪，但是您可以在准备好的语句字符串中直接分配变量:

It may seem strange, but you can assign the variable directly in the prepared statement string:

PREPARE stmt FROM 'SELECT @i := id FROM ...';

-- ...

EXECUTE stmt USING @m, @c, @a;

-- @i will hold the id returned from your query.

测试用例:

CREATE TABLE actions (id int, a int);

INSERT INTO actions VALUES (1, 100);
INSERT INTO actions VALUES (2, 200);
INSERT INTO actions VALUES (3, 300);
INSERT INTO actions VALUES (4, 400);
INSERT INTO actions VALUES (5, 500);

DELIMITER $$
CREATE PROCEDURE myProc(
    IN p int
)

MODIFIES SQL DATA

BEGIN

    PREPARE stmt FROM 'SELECT @i := id FROM actions WHERE `a` = ?';

    SET @a = p;

    EXECUTE stmt USING @a;

    SELECT @i AS result;

    DEALLOCATE PREPARE stmt;

END 
$$
DELIMITER ;

结果:

CALL myProc(400);

+---------+
| result  |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

这篇关于如何从准备好的语句中获取标量结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！