MySQL 如何在mysql中存储存储过程的返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9430260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to store returning values of a stored procedure in mysql
提问by Harsha
Though there are many questions in Stack Overflow regarding this problem I didn't able to figure it out properly, could anyone can help me to figure this out, let's say that there are two stored procedures as,
尽管 Stack Overflow 中有很多关于这个问题的问题，但我无法正确解决，任何人都可以帮我解决这个问题，假设有两个存储过程，
CREATE PROCEDURE `procOne`(stName varchar(7),fullItemCode varchar(12), fullLedNo varchar(12))
BEGIN
set @x = concat('
SELECT ID, Uprice FROM ',stName,'
where Uprice > 0 and FulItmCode="',fullItemCode,'" and FullLedgerNo = "',fullLedNo,'" order by Dat desc limit 1;
');
PREPARE stmt FROM @x;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
and
和
CREATE PROCEDURE `prcTwo`()
BEGIN
CREATE TEMPORARY TABLE tmpStore (
ID int(10),
Uprice decimal(18,2)
)
insert into tmpStore exec procOne(@param1, @param2)
select * from tempStore;
DROP TABLE tmpStore;
END
I have tried this, but am getting the following error when attempting to save prcTwo
我已经试过了，但是在尝试保存时出现以下错误 prcTwo
ERROR 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
'insert into tmpStore exec procOne 'St_BFG','SCI019', 'BIO-JVS-30' output
sele' at line 9 SQL Statement: CREATE DEFINER=user1@localhost PROCEDURE
prcTwo() BEGIN CREATE TEMPORARY TABLE tmpStore ( ID int(10), Uprice
decimal(18,2) ) insert into tmpStore exec procOne 'St_BFG','SCI019',
'BIO-JVS-30' output select * from tempStore; DROP TABLE tmpStore; END
采纳答案by Johan
Procedures do not return values, you'll have to declare procOneas a function instead:
过程不返回值，您必须改为声明procOne为函数：
CREATE FUNCTION `procOne`(stName varchar(7),fullItemCode varchar(12), fullLedNo varchar(12))
RETURNS INTEGER
BEGIN
set @x = concat('
SELECT ID, Uprice FROM ',stName,'
INTO @var1, @var2
WHERE Uprice > 0
AND FulItmCode="',fullItemCode,'"
AND FullLedgerNo = "',fullLedNo,'"
ORDER BY Dat DESC limit 1;
');
PREPARE stmt FROM @x;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
RETURN @var1;
END
Note that a function can only return a single value per call.
请注意，一个函数每次调用只能返回一个值。
回答by silly
use the out parameter like this
像这样使用 out 参数
CREATE PROCEDURE abc(IN id INTEGER, OUT text VARCHAR(255))
BEGIN
SET text = 'asd';
END
you can call this procedure like this:
你可以这样调用这个过程：
SET @outvar = '';
CALL abc(1, @outvar);
than the 'asd' will be saved in @outvar
比“asd”将保存在@outvar
回答by Simon at My School Portal
As mentioned in above comments, I don't fully understand what you are doing. Taking your information above verbatim though, the following would seem to achieve the desired effect.
正如上面的评论中提到的，我不完全理解你在做什么。尽管逐字逐句地获取您的信息，但以下内容似乎可以达到预期的效果。
CREATE PROCEDURE `prcTwo`()
BEGIN
CALL procOne(@param1, @param2);
END
In your exact procedure, you were missing some semicolons. The below is your prcTwoprocedure with errors corrected
在您的确切过程中，您遗漏了一些分号。以下是您prcTwo纠正错误的程序
CREATE PROCEDURE `prcTwo`()
BEGIN
CREATE TEMPORARY TABLE tmpStore (
ID int(10),
Uprice decimal(18,2)
);
INSERT INTO tmpStore CALL procOne(@param1, @param2) ;
SELECT * FROM tempStore;
DROP TABLE tmpStore;
END
