mysql获取存储过程返回值,如何在mysql中存储存储过程的返回值

虽然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

和

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

我试过这个,但在尝试保存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

解决方法:

程序不返回值,您必须将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

请注意,函数每次调用只能返回一个值.

标签：mysql,stored-procedures

来源： https://codeday.me/bug/20190723/1513719.html