mysql 存储过程 运行不出来,请高手帮帮忙,mysql5.5

delimiter$$DROPFUNCTIONIFEXISTSsp_login$$CREATEFUNCTIONsp_login(userIDVARCHAR(20),pas... delimiter $$
DROP FUNCTION IF EXISTS sp_login $$
CREATE FUNCTION sp_login( userID VARCHAR(20), passPwd VARCHAR(20)) RETURNS INT(1)
BEGIN
DECLARE flag INT default 5;
IF SELECT Stu1.Authority FROM (SELECT * from Student where Student.StuID = userID and Student.StuPwd = passPwd)Stu1
where Stu1.Authority = '1' > 0 THEN
SET flag = 1;
RETURN flag;
ELSEIF SELECT * from Supervisor where Supervisor.SVID = userID and Supervisor.SVPwd = passPwd > 0 THEN
SET flag = 3;
RETURN flag;
ELSEIF SELECT Stu2.Authority FROM (SELECT * from Student where Student.StuID = userID and Student.StuPwd = passPwd)Stu2
where Stu2.Authority = '2' > 0 THEN
SET flag = 2;
RETURN flag;
ELSEIF SELECT * from Administrator where Administrator.AdminID = userID and Administrator.AdminPwd = passPwd > 0 THEN
SET flag = 0;
RETURN flag;
ELSE
RETURN flag;
END IF;
END $$
delimiter ;
想返回一个整型,用来判断用户登录,执行的时候出错
我想定义一个函数,用来进行用户登录和用户权限判断,有学生、教师、管理员、信息员四种角色(其中信息员是学生的一种角色),这四个角色被存在3张表(Student、Supervisor、Administrator)中,每个表都有用户ID,用户密码和用户权限三个字段,用户权限取值范围为0-3,1代表学生、2代表信息员、3代表老师、0代表管理员,我想在mysql中创建function,接收用户名和密码两个参数,返回一个flag的整型,返回不同的整数,代表不同的角色,现在函数写出来了,但是不能执行,小弟新手,请高手帮忙解决一下,先说声谢啦~~我的mysql版本是5.5,
是无法创建
[Err] 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 'SELECT Stu1.Authority FROM (SELECT * from Student where Student.StuID = userID a' at line 4
展开
 我来答
百度网友4b68195
2012-12-10 · TA获得超过1520个赞
知道大有可为答主
回答量:1773
采纳率:100%
帮助的人:1723万
展开全部
DROP FUNCTION IF EXISTS sp_login $$
CREATE FUNCTION sp_login( userID VARCHAR(20), passPwd VARCHAR(20)) RETURNS INT(1)
BEGIN
DECLARE flag INT default 5;
DECLARE B BOOLEAN;
SET B=1;
if b then
select count(*) into flag from Student where Student.StuID = userID and Student.StuPwd = passPwd;
if flag then
SELECT Authority into flag FROM Student where Student.StuID = userID and Student.StuPwd = passPwd;
set b=0;
end if;
end if;
if b then
select count(*) into flag from Supervisor where Supervisor.SVID = userID and Supervisor.SVPwd = passPwd;
if flag then
SELECT Authority into flag FROM Supervisor where Supervisor.SVID = userID and Supervisor.SVPwd = passPwd;
set b=0;
end if;
end if;
if b then
select count(*) into flag from Administrator where Administrator.AdminID = userID and Administrator.AdminPwd = passPwd;
if flag then
SELECT Authority into flag FROM Administrator where Administrator.AdminID = userID and Administrator.AdminPwd = passPwd;
set b=0;
end if;
end if;
if b then
return 5;
else
return flag;
end if ;
END $$
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式