mysql存储过程多行返回 [英] mysql stored procedure multiple rows return

问题描述

我在一个输出中返回多行时遇到问题

i Have a problem returning multiple rows in one output

返回了多个 user_roleID.步骤如下:

there are multiple user_roleID's returned. The procedure is below:

CREATE PROCEDURE Sample(OUT userName VARCHAR(30))
BEGIN
SELECT user_roleID INTO userName FROM users;
END

我想在输出输出值(用户名)时获取数据列表

i would like to get a list of data when i print the output value (userName)

call Sample1(@emp);
select @emp;

但是当我执行此命令时，出现以下错误报告:

but when i excecute this command i get the following error reported:

1172-结果包含多于一行

1172 - Result consisted of more than one row

有人可以告诉我如何解决此问题吗?

Can somebody tell me how to fix this problem?

亲切的问候

推荐答案

例如，如果您要创建一个存储函数以返回基于用户名的ID，则可以使用以下方法

If you are looking to create a stored function to return an ID based on a username, for example, then the following would work

CREATE FUNCTION Sample(inUserName VARCHAR(255)) RETURNS INT(11)
BEGIN
    DECLARE returnValue INT;
    SELECT user_roleID INTO returnValue FROM users WHERE userName = inUserName LIMIT 1;
    RETURN returnValue;
END

这将使用SELECT Sample('SomeUserName') AS userID;

这篇关于mysql存储过程多行返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！