我想定义一个函数,用来进行用户登录和用户权限判断,有学生、教师、管理员、信息员四种角色(其中信息员是学生的一种角色),这四个角色被存在3张表(Student、Supervisor、Administrator)中,每个表都有用户ID,用户密码和用户权限三个字段,用户权限取值范围为0-3,1代表学生、2代表信息员、3代表老师、0代表管理员,我想在mysql中创建function,接收用户名和密码两个参数,返回一个flag的整型,返回不同的整数,代表不同的角色,现在函数写出来了,但是不能执行,小弟新手,请高手帮忙解决一下,先说声谢啦~~
代码如下:
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 ;
代码如下:
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 ;
解决方案 »
- 使用mysqlmanager管理mysql实例时,mysqlmanager自身怎样退出,只能kill进程吗?
- 这样的SQL该怎么写?(在线等!)
- 关于mysql中left join的问题
- mysql维护方法、工具汇总
- mySQL的服务器信息如下,查询时提示错误,请指教
- 大家用mysql觉得爽吗?
- 有没办法把Excel表中的数据导入到mysql数据库里!
- 急!高分求救!
- 求助(很急)!!! Mysql join 問題 求大神指點~
- 麻烦大家帮忙看看,oracle过程转mysql
- Mysql 无法启动---121210 15:03:02 InnoDB: Page dump in ascii and hex (16384 bytes):
- MySQL用户权限可以在数据库中的表进行限制吗?限制某个用户只读写某个表?
[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
是无法创建
改为如下SELECT count(*) into iCnt from Student where Student.StuID = userID and Student.StuPwd = passPwd and Authority = '1' > 0 IF iCnt > 0 THEN
SET flag = 1;
CREATE FUNCTION sp_login( userID VARCHAR(20), passPwd VARCHAR(20)) RETURNS INT(1)
里面的returns必须要加上吗?