存储过程 - 创建游标 - 接受 OUT 参数

发布于 2025-01-16 12:31:09 字数 1180 浏览 3 评论 0原文

我希望得到以下问题的一些帮助，不幸的是我创建的脚本不起作用。任何帮助将不胜感激！

问题：编写一个脚本来创建一个名为 test 的存储过程。此存储过程应为结果集创建一个游标，该结果集由标价大于 700 美元的每种产品的product_name 和list_price 列组成。此结果集中的行应按标价降序排序。存储过程应接受 OUT 参数，其中消息从过程中传递出去。然后，该过程应将 out 参数设置为一个字符串变量，其中包括每个产品的产品名称和标价，因此它看起来像这样： Gibson SG，2517.00|Gibson Les Paul，1199.00| 这里，每个值都用星号 (*) 括起来，每列由逗号 (,) 分隔，每行由竖线字符 (|) 分隔。

我的脚本：

CREATE PROCEDURE test( OUT message VARCHAR(200) )
BEGIN
DECLARE product_name_var VARCHAR(50);
DECLARE list_price_var DECIMAL(9,2);
DECLARE row_not_found TINYINT DEFAULT FALSE;
DECLARE s_var VARCHAR(400) DEFAULT '';

DECLARE invoice_cursor CURSOR for
    SELECT 
        product_name,
        list_price
    FROM
        products
    WHERE
        list_price > 700
    ORDER BY list_price DESC;
    
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET row_not_found = TRUE;

OPEN invoice_cursor;

FETCH invoice_cursor INTO product_name_var, list_price_var;
WHILE row_not_found = FALSE DO

SET s_var = CONCAT(s_var,'*', product_name_var,'*,*',list_price_var,'*|');
    FETCH invoice_cursor INTO product_name_var, list_price_var;
END WHILE;

SELECT s_var AS message;
END

原文

I was hoping to get some help the the below question, unfortunately the script I have created isn't working. Any assistance would be greatly appreciated!

Question:
Write a script that creates 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. The stored procedure should accept an OUT parameter where a message is passed out of the procedure. Then, the procedure should set the out parameter to 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|
Here, each value is enclosed in asterisk(*), each column is separated by a comma (,) and each row is separated by a pipe character (|).

My script:

CREATE PROCEDURE test( OUT message VARCHAR(200) )
BEGIN
DECLARE product_name_var VARCHAR(50);
DECLARE list_price_var DECIMAL(9,2);
DECLARE row_not_found TINYINT DEFAULT FALSE;
DECLARE s_var VARCHAR(400) DEFAULT '';

DECLARE invoice_cursor CURSOR for
    SELECT 
        product_name,
        list_price
    FROM
        products
    WHERE
        list_price > 700
    ORDER BY list_price DESC;
    
DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET row_not_found = TRUE;

OPEN invoice_cursor;

FETCH invoice_cursor INTO product_name_var, list_price_var;
WHILE row_not_found = FALSE DO

SET s_var = CONCAT(s_var,'*', product_name_var,'*,*',list_price_var,'*|');
    FETCH invoice_cursor INTO product_name_var, list_price_var;
END WHILE;

SELECT s_var AS message;
END

分享到QQ

分享到微博