当我有一个"WHERE"消息时，为什么我的存储过程会抛出错误“无数据-提取，选择或处理了零行"?在声明中? [英] Why does my stored procedure throw error 'No data - zero rows fetched, selected, or processed' when I have a "WHERE" in the statement?

问题描述

我仔细检查了这个选择语句，它肯定返回行:

I have double checked this select statement and it definitely returns rows:

SELECT name, id FROM MyTable WHERE id > 3;

但是当我为游标执行此操作时，它说没有获取任何数据！

But when I do it for a cursor, it says no data fetched!

DROP PROCEDURE IF EXISTS Test;
DELIMITER //

CREATE PROCEDURE Test()
BEGIN

    -- Our columns
    DECLARE name VARCHAR(45);
    DECLARE id INT DEFAULT -1;

    -- Our cursor
    DECLARE cur CURSOR FOR SELECT name, id FROM MyTable WHERE id > 3;

    DROP TEMPORARY TABLE IF EXISTS TempTest;
    CREATE TEMPORARY TABLE TempTest
    (
    name VARCHAR(45) NOT NULL,
    id INT NOT NULL
    );

    -- Open our cursor
    open cur;

    -- Start our for loop
    forLoop: LOOP

    -- Get the row
    FETCH cur INTO name, id;

    INSERT INTO TempTest (name, id)
    VALUES ( name, id);

    END LOOP forLoop;

    -- Close the cursor
    CLOSE cur;

    -- NOW GET THE RESULTS
    SELECT * FROM TempTest;

END; //

DELIMITER ;

CALL Test();

它引发错误:

[1329]无数据-提取，选择或处理了零行

[1329] No data - zero rows fetched, selected, or processed

推荐答案

您必须定义一个继续处理程序:

you must define a continue handler:

DROP PROCEDURE IF EXISTS Test;
DELIMITER //

CREATE PROCEDURE Test()
BEGIN

    -- Our columns
    DECLARE name VARCHAR(45);
    DECLARE id INT DEFAULT -1;
    DECLARE done INT DEFAULT 0;
    -- Our cursor
    DECLARE cur CURSOR FOR SELECT name, id FROM MyTable WHERE id > 3;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DROP TEMPORARY TABLE IF EXISTS TempTest;
    CREATE TEMPORARY TABLE TempTest
    (
    name VARCHAR(45) NOT NULL,
    id INT NOT NULL
    );

    -- Open our cursor
    open cur;

    -- Start our for loop
    forLoop: LOOP

    -- Get the row
    FETCH cur INTO name, id;
     IF done = 1 THEN
        LEAVE forLoop;
    END IF;
    INSERT INTO TempTest (name, id)
    VALUES ( name, id);

    END LOOP forLoop;

    -- Close the cursor
    CLOSE cur;

    -- NOW GET THE RESULTS
    SELECT * FROM TempTest;

END; //

DELIMITER ;

这篇关于当我有一个"WHERE"消息时，为什么我的存储过程会抛出错误“无数据-提取，选择或处理了零行"?在声明中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！