Mysql游标在存储过程中不返回任何内容

Question

我正在尝试在 MySQL Workbench 的存储过程中使用游标。代码如下所示:

DELIMITER $$

DROP PROCEDURE IF EXISTS order_procedure$$
CREATE PROCEDURE order_procedure()
BEGIN

DECLARE id INT;
DECLARE flag INT DEFAULT 0;

DECLARE ordercursor CURSOR FOR 
SELECT id FROM wp_posts
WHERE wp_posts.post_type = 'shop_order';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

OPEN ordercursor;

get_orders:LOOP
FETCH NEXT FROM ordercursor INTO id;
IF flag = 1 THEN 
    LEAVE get_orders;
END IF;

SELECT * FROM wp_woocommerce_order_items oi
INNER JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id
WHERE oi.order_id = id;

END LOOP;
CLOSE ordercursor; 
END$$
DELIMITER ;

如果我尝试:

SELECT id FROM wp_posts
WHERE wp_posts.post_type = 'shop_order'; 

我得到了我想要的结果，即。 15 行带有 order_ids，但是当我 CALL order_procedure(); 时，我什么也得不到。

当我尝试打印 ID 时，我得到了 NULL。我错过了什么？

编辑:添加了显示光标使用的 SELECT 的屏幕截图。

Answer 1

首先根据所需的列创建一个表“mytable”，无论您想要从这个过程中得到什么，然后在下面的过程中更改字段名称并检查。

DELIMITER $$

DROP PROCEDURE IF EXISTS order_procedure$$
CREATE PROCEDURE order_procedure()
BEGIN

DECLARE _id INT;
DECLARE flag INT DEFAULT 0;

DECLARE ordercursor CURSOR FOR SELECT id FROM wp_posts WHERE wp_posts.post_type = 'shop_order';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;

OPEN ordercursor;
BEGIN
REPEAT
FETCH ordercursor INTO _id;
IF _id = '' THEN
    SET flag = 1;
END IF; 

        IF (flag<>1) THEN
    SET @str1=CONCAT("insert into mytable SELECT oi.field1,oi.field2,oim.field1 FROM wp_woocommerce_order_items oi INNER JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id WHERE oi.order_id ='",_id,"'");
            PREPARE stmt1 FROM @str1;
            EXECUTE stmt1;
            DEALLOCATE PREPARE stmt1;

               END IF;
UNTIL flag
END REPEAT;
END;
CLOSE ordercursor; 
SELECT * FROM mytable;
END$$
DELIMITER ;