我在
MySQL的过程,它有两个IN参数:USERLOGIN(VARCHAR)和的userPassword(VARCHAR),以及两个OUT参数:用户ID(INT)和userRights(VARCHAR).
该过程的工作原理如下:检查具有给定登录名和密码的用户是否在数据库中,如果是,则返回其ID,userRights和设置.权限可以连接成一个字符串,但我想避免连接并随后解析websettings,因为数据的结构更复杂.
现在,我的过程只返回userID和userRights,如果成功,我运行另一个select语句来获取设置.
目前程序:
DELIMITER $$
CREATE PROCEDURE `proc_login_user`(IN UserName VARCHAR(45), IN Pwd VARCHAR(250), OUT Uid INT, OUT Rights VARCHAR(100))
BEGIN
DECLARE Uname VARCHAR(45);
DECLARE Pass VARCHAR(250);
DECLARE UserId INT;
SET @Uname := UserName;
SET @Pass := Pwd;
SET @UserId = (SELECT ID FROM `users` WHERE Login = @Uname AND Password = @Pass);
SET Uid = @UserId;
SET Rights = /* some SELECT GROUP_CONCAT statement to create string */
END;
我希望在procedure中添加一个输出参数:userSettings,它应该是一个结果集.
像这样的东西:
DELIMITER $$
CREATE PROCEDURE `proc_login_user`(IN UserName VARCHAR(45), IN Pwd VARCHAR(250), OUT Uid INT, OUT Rights VARCHAR(100), OUT Settings)
BEGIN
DECLARE Uname VARCHAR(45);
DECLARE Pass VARCHAR(250);
DECLARE UserId INT;
SET @Uname := UserName;
SET @Pass := Pwd;
SET @UserId = (SELECT ID FROM `users` WHERE Login = @Uname AND Password = @Pass);
SET Uid = @UserId;
SET Rights = /* some SELECT GROUP_CONCAT statement to create string */
SET Settings = SELECT * FROM `settings` WHERE UserID = @UserId;
END;
任何帮助,将不胜感激.
谢谢,
兹比涅克