如何从mysql中的存储过程中检索多行? [英] How to retrieve multiple rows from stored procedure in mysql?

问题描述

我试图通过存储过程获取一个字段，我使用了以下查询.我的目的是获取多行，但只有在存在单行时，它才能成功执行结果.否则，它将返回错误，如下所述.

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.

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;

错误消息

MySQL said: Documentation
#1172 - Result consisted of more than one row 

注意

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

推荐答案

该错误不在您的过程中.错误出在您的查询中-返回多于一行，但是您无法将多个结果设置为标量值'emp1'.

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'.

您应限制查询以使其返回一行.

You should limit your query so that it returns one row.

如何从mysql中的存储过程检索多行?

• 计划A :填写另一个表格，它可能是临时表格.
• 计划B :只需执行过程中没有INTO子句的SELECT语句即可；那么您可以从应用程序中读取数据集(c#，PHP + mysqli等)
• 计划C :请勿使用该过程，只需执行SELECT查询即可.

• 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.

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