mysql - SELECT 可以单独工作，但在分配给游标时返回 0 行

Question

我有一个 SELECT 语句，它可以独立工作，并返回约 8000 行。我试图将它作为游标添加到存储过程中，但它什么也没返回。

你能发现我错过了什么吗？

作品

SELECT a.customer, a.order_id
FROM temp_orders a
INNER JOIN (
    SELECT customer, MAX(order_id) AS last_order
    FROM temp_orders
    WHERE pay_status = 3
       OR pay_status = 4
    GROUP BY customer
) AS b ON  a.customer = b.customer
       AND a.order_id = b.last_order
WHERE pay_status = 3
   OR pay_status = 4
ORDER BY a.customer;

不再有效

DELIMITER //

DROP PROCEDURE IF EXISTS get_last_orders //
CREATE PROCEDURE get_last_orders()

    BEGIN

        DECLARE order_id VARCHAR(15);
        DECLARE customer_id INT;
        DECLARE done INT DEFAULT 0;

        DECLARE cur1 CURSOR FOR SELECT a.customer, a.order_id
                                FROM temp_orders a
                                INNER JOIN (
                                    SELECT customer, MAX(order_id) AS last_order
                                    FROM temp_orders
                                    WHERE pay_status = 3
                                       OR pay_status = 4
                                    GROUP BY customer
                                ) AS b ON  a.customer = b.customer
                                       AND a.order_id = b.last_order
                                WHERE pay_status = 3
                                   OR pay_status = 4
                                ORDER BY a.customer;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        DROP TABLE IF EXISTS temp_last_orders;

        CREATE TABLE temp_last_orders (
            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            order_id VARCHAR(15) NOT NULL,
            customer_id INT NOT NULL
        ) ENGINE = MYISAM
        COMMENT = 'Last paid order from each customer';

        OPEN cur1;
            read_loop: LOOP
                FETCH cur1 INTO order_id, customer_id;
                IF done THEN
                    LEAVE read_loop;
                END IF;

                INSERT INTO temp_last_orders (order_id, customer_id) VALUES (order_id, customer_id);

            END LOOP;
        CLOSE cur1;

    END; //

CALL get_last_orders();

Answer 1

变量和列名同名存在问题。尝试将变量 order_id 和 customer_id 重命名为 v_order_id 和 v_customer_id 并尝试一下。