网上看了下,有遇到过说不能参数名和字段名同名,不能同名有点变态,试了下有种方法在同名情况下可行,就是定义表别名就可以,像这样,当然如果本身不同名也可以不用别名,安全起见全部加上表别名好了CREATE PROCEDURE SP_ValideLogin(IN USER_ACCOUNT varchar(25), IN USER_PASSWORD varchar(100), OUT FLAG INT) BEGIN SET @USER_ACCOUNT = USER_ACCOUNT; SET @USER_PWD = USER_PWD; SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT AND U.USER_PWD=@USER_PWD AND U.USER_VALID='1'; IF(@FLAG>0) THEN UPDATE SY_User U SET U.USER_CURR_DATE=NOW(),U.USER_LAST_DATE = U.USER_CURR_DATE,U.USER_LoginCount=IFNULL(U.USER_LoginCount,0)+1 WHERE U.USER_LOGIN = @USER_ACCOUNT; /*登录成功后记录登录时间*/ INSERT INTO SY_UserLoginInfo(SYU_Login,SYU_LoginDate,SYU_LoginState) VALUES(@USER_ACCOUNT,NOW(),'1'); /*登录记录信息(正常情况)*/ ELSE UPDATE SY_User U SET U.USER_ExceptionCount=IFNULL(U.USER_ExceptionCount,0)+1 WHERE U.USER_LOGIN = @USER_ACCOUNT; /*记录异常登录次数*/ INSERT INTO SY_UserLoginInfo(SYU_Login,SYU_LoginDate,SYU_LoginState) VALUES(@USER_ACCOUNT,NOW(),'0'); /*登录记录信息(异常情况)*/ SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT; IF(@FLAG>0) THEN SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT AND U.USER_VALID='0'; IF(@FLAG>0) THEN SET @FLAG = -2; /*账号被禁用*/ ELSE SET @FLAG = 0; /*密码不正确*/ END IF; ELSE SET @FLAG = -1; /*用户不存在*/ END IF; END IF; SET FLAG=@FLAG; END
IN USER_PASSWORD varchar(100),
OUT FLAG INT)
BEGIN
SET @USER_ACCOUNT = USER_ACCOUNT;
SET @USER_PWD = USER_PWD;
SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT AND U.USER_PWD=@USER_PWD AND U.USER_VALID='1';
IF(@FLAG>0) THEN
UPDATE SY_User U SET U.USER_CURR_DATE=NOW(),U.USER_LAST_DATE = U.USER_CURR_DATE,U.USER_LoginCount=IFNULL(U.USER_LoginCount,0)+1 WHERE U.USER_LOGIN = @USER_ACCOUNT; /*登录成功后记录登录时间*/
INSERT INTO SY_UserLoginInfo(SYU_Login,SYU_LoginDate,SYU_LoginState) VALUES(@USER_ACCOUNT,NOW(),'1'); /*登录记录信息(正常情况)*/
ELSE
UPDATE SY_User U SET U.USER_ExceptionCount=IFNULL(U.USER_ExceptionCount,0)+1 WHERE U.USER_LOGIN = @USER_ACCOUNT; /*记录异常登录次数*/
INSERT INTO SY_UserLoginInfo(SYU_Login,SYU_LoginDate,SYU_LoginState) VALUES(@USER_ACCOUNT,NOW(),'0'); /*登录记录信息(异常情况)*/
SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT;
IF(@FLAG>0) THEN
SELECT @FLAG:=count(*) FROM SY_User U WHERE U.USER_LOGIN=@USER_ACCOUNT AND U.USER_VALID='0';
IF(@FLAG>0) THEN
SET @FLAG = -2; /*账号被禁用*/
ELSE
SET @FLAG = 0; /*密码不正确*/
END IF;
ELSE
SET @FLAG = -1; /*用户不存在*/
END IF;
END IF;
SET FLAG=@FLAG;
END