MySQL 如何从mysql的存储过程中检索多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8307939/
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 retrieve multiple rows from stored procedure in mysql?
提问by Bala
Am trying to fetch out a field through stored procedure and I used following query. I aimed at fetching out multiple rows, but it executes the result successfully only when a single row exists. Or else it returns an error as I mentioned below.
我试图通过存储过程提取一个字段，我使用了以下查询。我的目标是提取多行，但只有当存在单行时它才能成功执行结果。否则它会返回我在下面提到的错误。
MYSQL Query
MYSQL 查询
delimiter ;;
drop procedure if exists Sample1;;
CREATE PROCEDURE Sample1(IN lft1 INT,IN rgt1 INT, OUT emp1 VARCHAR(20))
BEGIN
SELECT p.emp into emp1 FROM personnell p WHERE p.lft>lft1 and p.rgt < rgt1 LIMIT 10;
END;;
call Sample1(1,10,@emp);;
select @emp;
Error Message
错误信息
MySQL said: Documentation
#1172 - Result consisted of more than one row
Note
笔记
-----
Sample1--- procedure name;
emp -----selected field from table personnell
lft -----use to check the condition,it is also one of the field of table personnell
personnell------table name
回答by Devart
The error is not in your procedure. The error is in your query - it returns more then one row, but you cannot set multiple result into scalar value 'emp1'.
错误不在您的程序中。错误在您的查询中 - 它返回多于一行，但您不能将多个结果设置为标量值“emp1”。
You should limit your query so that it returns one row.
您应该限制您的查询，使其返回一行。
How to retreive multiple rows from stored procedure in mysql?
如何从mysql中的存储过程中检索多行？
- Plan A: Fill another table, it may be a temporary table.
- Plan B: Just execute your SELECT statement without INTO clause from the procedure; then you could read data-set from the application (c#, PHP+mysqli,...)
- Plan C: Do not use the procedure, just execute the SELECT query.
- 方案A：填充另一个表，它可能是一个临时表。
- 计划 B：只需从过程中执行没有 INTO 子句的 SELECT 语句；然后你可以从应用程序中读取数据集（c#，PHP+mysqli，...）
- 计划 C：不要使用过程，只执行 SELECT 查询。
回答by user3761776
Just had the same question. After a little research I found a solution in the official documentation. http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-tutorials-stored-routines-statements.htmlIt requires MySQL 5.5.3 or higher.
刚刚有同样的问题。经过一番研究，我在官方文档中找到了解决方案。http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-tutorials-stored-routines-statements.html它需要 MySQL 5.5.3 或更高版本。
In contrast to the inital stored procedure from @Bala.C it doesn't use an out parameter.
与来自 @Bala.C 的初始存储过程相反，它不使用 out 参数。
CREATE PROCEDURE get_data ()
BEGIN
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'Oceania' AND Population < 10000;
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'Europe' AND Population < 10000;
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'North America' AND Population < 10000;
END;
回答by GaCon
You can use Cursor in MySql
你可以在 MySql 中使用 Cursor
CREATE PROCEDURE Sample1(IN lft1 INT,IN rgt1 INT, OUT emp1 VARCHAR(20))
BEGIN
DECLARE p_emp INT;
DECLARE cur_emp CURSOR FOR SELECT p.emp FROM personnell p WHERE p.lft>lft1 and p.rgt < rgt1 LIMIT 10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRow = 0;
OPEN cur_emp;
LOOPROWS: LOOP
IF noMoreRow = 0 THEN
CLOSE cur_emp;
LEAVE LOOPROWS;
END IF;
FETCH cur_emp INTO p_emp;
SELECT p_emp;
END LOOP;
END;;
