我正在使用mysql并有一个登录认证程序,
CREATE PROCEDURE CheckPassword (IN username CHAR(8),IN password_p VARCHAR(20), OUT yes_no char(1))
BEGIN
IF EXISTS(SELECT * FROM USER WHERE USER_ID = username AND password = password_p) then
set yes_no = '0';
ELSE
set yes_no = '1';
END IF;
END;
但是如果mysql警告说在创建过程时有错误,它说在第4行有错误,它是"set yes_no = '0';"行?我也试过这种方法,
CREATE PROCEDURE CheckPassword (IN username CHAR(8),IN password_p VARCHAR(20), OUT yes_no char(1))
BEGIN
IF EXISTS(SELECT * FROM USER WHERE USER_ID = username AND password = password_p) then
select '0' into yes_no;
ELSE
select '1' into yes_no;
END IF;
END;
也不能工作,是不是必须在创建过程时使用分隔符?你能告诉我调用这个过程的语句吗?
我认为问题是USER是一个保留词。另外,我建议您使用函数,而不是过程(您只需要返回一个值)。这个函数起作用:
DELIMITER ||
CREATE FUNCTION CheckPassword (username VARCHAR(8), password_p VARCHAR(20))
RETURNS BOOL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
RETURN EXISTS (SELECT username FROM `USER` WHERE USER_ID = username AND password = password_p);
END;
||
DELIMITER ;
SELECT CheckPassword('a','b');
试试这个:
CREATE PROCEDURE `IsUserPasswordValid`(
IN username varchar(50),
IN password_p VARCHAR(20),
OUT yes_no int
)
BEGIN
SELECT count(*) INTO yes_no
FROM USER u
WHERE u.USER_ID = username && u.password = password_p;
END
如果返回1,则user是有效的。