下面是我的存储过程。它工作得很好,但我的问题是我无法获得VARCHAR
的输出参数。
我遇到问题的部分是将@curcName
分配给输出参数op_resultMessage
BEGIN
SET op_resultMessage = @curcName;
END;
这是存储过程。
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCurriculum`(
IN p_curcName varchar(100),
IN p_description TEXT,
IN p_yearLevel VARCHAR(50),
IN p_syStart INT,
IN p_syEnd INT,
IN p_creator VARCHAR(50),
OUT op_resultMessage VARCHAR(50))
BEGIN
DECLARE curcName VARCHAR(20) ;
IF EXISTS
(SELECT @curcName := `name`
FROM curriculum
WHERE
yearLevel = p_yearLevel
AND syStart = p_syStart
AND syEnd = p_syEnd )
THEN --
BEGIN
SET op_resultMessage = @curcName;
END;
ELSE
BEGIN
INSERT INTO curriculum(`name`, description, yearLevel, syStart, syEnd, creator)
VALUES(p_curcName,p_description,p_yearLevel,p_syStart,p_syEnd,p_creator);
END;
END IF;
END
如果name
存在,我正试图返回消息
所以它应该像一样
SET op_resultMessage = @curcName 'already uses the school year and year level you're trying to insert';
但我不知道如何正确连接和分配值。我仍然对:= SET
和=
运算符感到困惑。我想这就是我的问题所在
如果我将out参数的类型更改为类似的INT
OUT op_resultMessage VARCHAR(50)
然后像CCD_ 9 一样给CCD_
它将数字1作为out参数值返回。它就是不适用于varchar。
所以当我尝试调用程序时
CALL `enrollmentdb`.`addCurriculum`
('Test Curriculum ','Test ','Grade 1',2015,2016,'jordan',@outputMsg);
SELECT @outputMsg; -- this doesn't return any value even if Grade 1, 2015 and 2016 exists
如果有任何帮助,我将不胜感激。事实上,我最近刚刚学习mysql。
谢谢。
drop procedure if exists addCurriculum;
delimiter $$
CREATE PROCEDURE `addCurriculum`(
IN p_curcName varchar(100),
IN p_description TEXT,
IN p_yearLevel VARCHAR(50),
IN p_syStart INT,
IN p_syEnd INT,
IN p_creator VARCHAR(50),
OUT op_resultMessage VARCHAR(50))
BEGIN
DECLARE curcName VARCHAR(20) ;
SELECT `name` into @curcName
FROM curriculum
WHERE
yearLevel = p_yearLevel
AND syStart = p_syStart
AND syEnd = p_syEnd
LIMIT 1;
-- Note change above. When selecting into a variable (or more than 1)
-- then 0 or 1 rows can come back max or an Error occurs
IF @curcName is not null then
SET op_resultMessage = @curcName;
ELSE
BEGIN
INSERT INTO curriculum(`name`, description, yearLevel, syStart, syEnd, creator)
VALUES(p_curcName,p_description,p_yearLevel,p_syStart,p_syEnd,p_creator);
END;
SET op_resultMessage = 'GEEZ I am right here'; -- Drew added this
END IF;
END$$
delimiter ;
请注意存储过程中的注释,尤其是只有0或1行返回的部分,否则select into var
模式将出现错误。所以LIMIT 1
。这可能是也可能不是你想要的行(限制1(,但这就是它现在的位置。