我在PhpMyAdmin中运行以下存储过程:
DELIMITER //
CREATE PROCEDURE usp_ForgotPassword (
-- Add the parameters for the stored procedure here
p_mobileNo VARCHAR(50),
p_emailId VARCHAR(300),
p_password VARCHAR(300),
p_otp VARCHAR(50),
p_appInstanceCode CHAR(36))
BEGIN
DECLARE v_IsOTPValid TINYINT DEFAULT NULL;
SET v_IsOTPValid = NULL;
DECLARE v_userCode CHAR(36);
IF (v_IsOTPValid = 1)
THEN
IF EXISTS(SELECT * FROM OTP WHERE otp = p_otp AND mobileNo = p_mobileNo)
THEN
SET v_userCode = (SELECT userCode FROM UserLogin WHERE userName = p_emailId);
UPDATE UserLogin UL
SET password = p_password
WHERE userName = p_emailId AND userCode = v_userCode;
-- Delete the verified OTP
DELETE FROM OTP WHERE otp = p_otp AND mobileNo = p_mobileNo;
DECLARE v_Token CHAR(36);
INSERT INTO Token (createdAt, updatedAt,code,userCode,appInstanceCode,deviceIMEI,deviceName)
VALUES (NOW(),NOW(),v_Token,v_userCode,p_appInstanceCode,NULL,NULL);
SELECT 200 code, 'Password changed succesfully' as message,v_Token;
ELSE
SELECT 400 as code, 'Invalid OTP or Mobile No' as message,v_Token;
END IF;
ELSE
SELECT 401 as code, 'OTP expired' as message,v_Token;
END IF;
END
//
delimiter ;
但它不断向我显示一个错误:
1064-您的SQL语法有错误;请查看MariaDB服务器版本对应的手册,以了解在'DECLARE v_ISOPTValid TINYINT DEFAULT NULL附近使用的正确语法;SET v_IsOTPWalid=NULL;第9行DECL'
我尝试过:
mysql中的声明是否存在语法错误?
和
MariaDB/MySQL中CREATE PROCEDURE中的神秘错误
但这些答案都无济于事!
在所有DECLARE语句结束之前都有一个SET。。
根据official documentation
,将所有声明放在集合或任何其他语句之前:
BEGIN
DECLARE v_IsOTPValid TINYINT DEFAULT NULL;
DECLARE v_userCode CHAR(36);
DECLARE v_Token CHAR(36);
SET v_IsOTPValid = NULL;