MySQL游标问题: what did i do wrong?

Question

我一直在编写这段代码，根据这本书应该可以做到这一点:

Write a script that creates and calls a stored procedure named test. This stored procedure should create a cursor for a result set that consists of the product_name and list_price columns for each product with a list price that’s greater than $700. The rows in this result set should be sorted in descending sequence by list price. Then, the procedure should display a string variable that includes the product_name and list price for each product so it looks something like this: "Gibson SG", "2517.00" | "Gibson Les Paul", "1199.00""

不幸的是，我的代码所做的是返回空值而不是任何值。我做错了什么？

USE my_guitar_shop;

DROP PROCEDURE IF EXISTS test;

DELIMITER //

Create Procedure test()
Begin
    DECLARE names_var VARCHAR(50);
    DECLARE prices_var DECIMAL (8,2);
    DECLARE results VARCHAR(150);
    DECLARE done TINYINT;

    DECLARE products_cursor CURSOR FOR
        SELECT product_name, list_price FROM products;

    DECLARE continue handler for not found set done = 1;
    SET done = 0;

    OPEN products_cursor;

    testloop : loop
    FETCH products_cursor INTO names_var, prices_var;
    IF done = 1 then leave testloop;
    END IF;

    IF prices_var > 700 THEN
        SET results = concat(results, "names_var", ', ', "prices_var", '|');
    END if;
    END loop testloop; 

    CLOSE products_cursor;

    SELECT results;


END //

DELIMITER ;

Call test;

Answer 1

根据文档，CONCAT如果任何参数为 null，则返回 null。我怀疑发生的情况是您的选择从其中一行返回 null，这会清除 结果 的全部内容.

要处理这种情况，您可以使用 COALESCE ，像这样:

IF prices_var > 700 THEN
    SET results = concat(results, '"', COALESCE(names_var, ' '), '", "', COALESCE(prices_var, ' '), '" | ');

如果变量不为空，则对COALESCE的调用将返回该变量的值；如果变量为空，则返回空字符串' '。

(我想我已经在输出中直接得到了所有单引号和双引号，但我不做任何 promise 。;)