blocks|key|410662|text|该错误不在您的过程中。错误在查询中--它返回多行，但不能将多个结果设置为标量值“emp1+1”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410663|您应该限制查询，使其返回一行。|410664|如何从mysql中的存储过程中提取多行？|410665|计划A：填充另一个表，它可能是一个临时表。|unordered-list-item|offset|length|style|BOLD|410666|计划B：只需执行SELECT语句，而不使用INTO子句；然后可以从应用程序读取数据集(c#，PHP%2Bmysqli，.)|410667|计划C：不要使用该过程，只需执行SELECT查询。|410668|entityMap^0|0|0|0|0|3|0|0|3|0|0|3|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@$I|W|J|X|K|L]]|9|@]|A|$]]|$1|M|3|N|5|H|7|Y|8|@$I|Z|J|10|K|L]]|9|@]|A|$]]|$1|O|3|P|5|H|7|11|8|@$I|12|J|13|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|R|$]]

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

<p>You should limit your query so that it returns one row.</p>

<hr>

<p><em>How to retreive multiple rows from stored procedure in mysql?</em></p>

<ul>
<li><strong>Plan A</strong>: Fill another table, it may be a temporary table.</li>
<li><strong>Plan B</strong>: Just execute your SELECT statement without INTO clause from the procedure; then you could read data-set from the application (c#, PHP+mysqli,...)</li>
<li><strong>Plan C</strong>: Do not use the procedure, just execute the SELECT query.</li>
</ul>


blocks|key|192133|text|您可以在游标中使用MySQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|192134|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;;|code-block|syntax|javascript|192135|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/cursors.html^0|4|2|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

<p>You can use a <a href="https://dev.mysql.com/doc/refman/8.0/en/cursors.html" rel="nofollow noreferrer">cursor</a> in MySQL.</p>
<pre><code>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 &gt; lft1
              and p.rgt &lt; 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;;
</code></pre>


blocks|key|410725|text|只是有同样的问题。经过一番研究，我在官方的MySQL文档中找到了一个解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|410726|用语句对象调用存储过程|unordered-list-item|offset|length|410727|它需要MySQL+5.5.3或更高版本。|410728|与@Bala.C中的inital存储过程不同，它不使用out参数。|style|CODE|410729|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;|code-block|syntax|javascript|410730|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-tutorials-stored-routines-statements.html^0|0|0|B|0|0|0|R|3|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@$E|11|F|12|1|13]]|A|$]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@$E|16|F|17|K|L]]|9|@]|A|$]]|$1|M|3|N|5|O|7|18|8|@]|9|@]|A|$P|Q]]|$1|R|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

<p>Just had the same question. After a little research I found a solution in the official MySQL documentation:</p>
<ul>
<li><a href="https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-tutorials-stored-routines-statements.html" rel="nofollow noreferrer">Calling Stored Procedures with Statement Objects</a></li>
</ul>
<p>It requires MySQL 5.5.3 or higher.</p>
<p>In contrast to the inital stored procedure from @Bala.C it doesn't use an <code>out</code> parameter.</p>
<pre><code>CREATE PROCEDURE get_data ()
BEGIN
   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'Oceania'
         AND Population &lt; 10000;

   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'Europe'
         AND Population &lt; 10000;

   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'North America'
         AND Population &lt; 10000;
END;
</code></pre>


<p>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.</p>
<h3>MySQL Query</h3>
<pre><code>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 personnel p
    WHERE p.lft &gt; lft1
          AND p.rgt &lt; rgt1
    LIMIT 10;
END;;

CALL Sample1(1, 10, @emp);;
SELECT @emp;
</code></pre>
<h2>Error Message</h2>
<pre><code>MySQL said: Documentation
#1172 - Result consisted of more than one row 
</code></pre>
<hr />
<h2>NOTE</h2>
<blockquote>
<p><code>Sample1</code> - procedure name<br />
<code>emp</code> - selected field from table personnel<br />
<code>lft</code> - use to check the condition, it is also one of the field of table personnel<br />
<code>personnel</code> - table name</p>
</blockquote>


How to retrieve multiple rows from stored procedure in MySQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我试图通过存储过程提取一个字段，我使用了以下查询。我的目标是取出多个行，但只有在存在单个行时，它才能成功地执行结果。否则，它将返回一个错误，正如我在下面提到的。MySQL查询DELIMITER ;;DROP PROCEDURE IF EXISTS Sample1;;CREATE PROCEDURE Sample1(IN...

问如何在MySQL中从存储过程中检索多行？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中从存储过程中检索多行？EN