在存储过程中使用会话变量 [英] Using session variables in a stored procedure

问题描述

我刚开始学习 MySQL 并创建存储过程.当我使用局部变量时，此过程工作正常.当我尝试在我的过程中使用会话变量时，我收到一个语法错误.我可以在程序中不使用会话参数吗?如果不能，我可以在哪里使用它们?

I just started learning MySQL and creating stored procedures. This procedure works fine when I use local variables. When I try to use a session variable in my procedure I receive a syntax error. Can I not use session parameters in procedures and if not, where can I use them?

DELIMITER $$
CREATE PROCEDURE myfirst()
BEGIN
DECLARE @counter INT DEFAULT 1;
DECLARE @result VARCHAR(30); 
WHILE (counter < (SELECT count(*) FROM animals))
DO
SET result= (SELECT name FROM animals WHERE id = counter);
SELECT result;
SET counter = counter + 1;
END while;

END$$
DELIMITER ;

推荐答案

你可以使用用户变量(你调用它们的会话)，但是你不能使用 @ 符号作为存储过程传递它们参数定义.

You can use User Variables (session as you call them), but you cannot pass them with an @ sign as a stored proc parameter definition.

此外，永远不要使用 DECLARE 来 DECLARE 的用户变量.

Also, one never DECLAREs User Variables with DECLARE.

所以就用它们吧.在你的会话中有.也就是说，如果必须的话.

So just use them. There are in your session. That is, if you must.

仅使用正确的参数名称(没有 @ 符号)创建存储过程会更可取，更具可读性和可支持性.您如何传递值，无论是否使用用户变量引用，都取决于您.

It would be preferable and more readable and supportable to merely create your stored procs with proper parameter names (without the @ sign). How you pass values in, whether with a User Variable reference or not, is up to you.

无论如何，存储的 proc 参数不能以 @ 符号出现在其签名中.所有 DECLARES 都必须在 BEGIN 之后首先发生(并且仅适用于局部变量，而不适用于用户变量).

Regardless, stored proc parameters cannot appear in their signature with an @ sign. All the DECLARES must happen first after BEGIN (and only with Local Variables not with User Variables).

DROP PROCEDURE IF EXISTS myfirst;
DELIMITER $$
CREATE PROCEDURE myfirst()
BEGIN
    DECLARE LocalVar_Int INT DEFAULT 1; -- not used, just shown
    DECLARE LocalVar_Varchar VARCHAR(30); -- not used, just shown 

    -- all the DECLARES must come first after BEGIN
    -- and only for LOCAL VARS

    SET @count=1; -- this is a User Variable
    SET @result=''; -- this is a User Variable
    WHILE (@counter < (SELECT count(*) FROM animals))
    DO
    SET @result= (SELECT name FROM animals WHERE id = @counter);
    SELECT @result;
    SET @counter = @counter + 1;
    END while;

END$$
DELIMITER ;

这篇关于在存储过程中使用会话变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！