我已经用mysql编写了下面提到的过程。
CREATE DEFINER=`root`@`localhost` PROCEDURE `IsUploaderLoggedIn`(
IN `inMobile` CHAR(10),
IN `inSessionID` varchar(34)
)
BEGIN
DECLARE isLoggedIn TINYINT(1) DEFAULT 0;
DECLARE uploaderType VARCHAR(10) DEFAULT '';
CALL GetUploaderType(inMobile, @x);
SELECT @x INTO uploaderType;
IF uploaderType = "surveyor" THEN
SELECT Count(*) INTO isLoggedIn FROM surveyors WHERE Mobile = inMobile AND SessionID = inSessionID;
SELECT "surveyor";
ELSE
SELECT Count(*) INTO isLoggedIn FROM uploaders WHERE Mobile = inMobile AND SessionID = inSessionID;
SELECT "uploader";
END IF;
SELECT isLoggedIn;
END;
在navicat中执行过程时,对于给定的值 inMobile
以及 inSessionID
,返回的值为:
结果1:用户类型|测量员
结果1(2):上传者|上传者
结果1(3):0或1(视情况而定)
式中,结果1(2)s的值应为测量员|测量员。
下面是存储过程的定义 GetUploaderType
:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUploaderType`(
IN `inMobile` CHAR(10),
OUT `Usertype` VARCHAR(8)
)
BEGIN
DECLARE usertype VARCHAR(8) DEFAULT '';
DECLARE userExists TINYINT DEFAULT 0;
/*SET officeExists = 0;*/
/* Check if mobile number belongs to a surveyor */
SELECT Count(*) INTO userExists FROM surveyors WHERE Mobile = inMobile;
IF userExists = 1 THEN
SET usertype = 'surveyor';
ELSE
/* If user is not surveyor, check for it in uploaders */
SELECT Count(*) INTO userExists FROM uploaders WHERE Mobile = inMobile;
IF userExists = 1 THEN
SET usertype = 'uploader';
END IF;
END IF;
SELECT usertype;
结束
请问我的剧本怎么了?
1条答案
按热度按时间ikfrs5lh1#
在
GetUploaderType
,您重新声明了变量Usertype
,从而隐藏了(不同的)out
-相同名称的变量,请参见范围:局部变量的作用域是begin。。。在其中声明的结束块。可以在嵌套在声明块中的块中引用变量,但声明具有相同名称的变量的块除外。