我有仔细检查此选择语句,肯定返回行:
SELECT name, id FROM MyTable WHERE id > 3;
但是当我为光标做时,它说没有获取数据!
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]无数据 - 零行获取,选择或处理过
您必须定义一个继续处理程序:
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 ;