Mysql存储过程错误1064 [英] Mysql stored procedure error 1064

问题描述

我正在编写一些代码，但被这个存储过程问题困住了……我花了很长时间看它，所以如果有人能告诉我我在这方面的菜鸟在哪里.

I'm working on some code, and got stuck on this stored procedure problem... Have spent too long now looking at it, so please if someone can tell me where I am a noob with this.

我收到以下错误:

错误代码:1064您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 '@ownerid varchar(50); 附近使用的正确语法；声明@active tinyint;声明@subuser tinyint;在第 4 行设置 '

Error Code: 1064 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 '@ownerid varchar(50); declare @active tinyint; declare @subuser tinyint; set ' at line 4

关于这个代码:

    DELIMITER //

USE `test`//

DROP PROCEDURE IF EXISTS `getAccount`//

CREATE DEFINER=`peter`@`%` PROCEDURE `getAccount`( IN cid VARCHAR(20))
BEGIN
    DECLARE cardid VARCHAR(50);
    DECLARE @ownerid VARCHAR(50);
    DECLARE @active TINYINT;
    DECLARE @subuser TINYINT;
    SET cardid = MD5( cid + SHA1(cid + 'a salt value'));

    SELECT @active = active, @ownerid = ownerid, @subuser = subuser FROM cards_tbl WHERE cardhash = cardid;

    IF @active = 1 THEN
        IF @subuser = 1 THEN
            SELECT subuser_m2s_tbl.name,subuser_m2s_tbl.image, user_saldo.saldo AS credits FROM subuser_m2s_tbl 
            JOIN user_saldo ON subuser_m2s_tbl.subhash = user_saldo.userhash 
            WHERE subuser_m2s_tbl.subhash = @ownerid;
        ELSE
            SELECT user_m2s_tbl.name,user_m2s_tbl.image, user_saldo.saldo AS credits FROM user_m2s_tbl 
            JOIN user_saldo ON user_m2s_tbl.userhash = user_saldo.userhash 
            WHERE user_m2s_tbl.userhash = @ownerid; 
        END IF;
    END IF; 
    END$$

DELIMITER ;

我是存储过程的新手，所以它非常简单......提前致谢

Am new to the stored procedures, so it is properly something very simple..... Thanks in advance

推荐答案

当你想在 SELECT 语句中将值传递给变量时，你应该使用 ':=' 而不是 '=' ，否则 MySQL 只会与操作数进行比较.

You should use ':=' instead of '=' when you want to pass value into variable in SELECT statement, otherwise MySQL just will compare to operands.

SELECT
  active_var := active, ownerid_var := ownerid, subuser_var := subuser
FROM
  cards_tbl
WHERE
  cardhash = cardid;

您也可以使用 SELECT INTO 语句，例如-

Also you can use SELECT INTO statement, e.g. -

SELECT
  active, ownerid, subuser
INTO
  active_var, ownerid_var, subuser_var
FROM
  cards_tbl
WHERE
  cardhash = cardid;

SELECT 语法.

这篇关于Mysql存储过程错误1064的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！