Write a script that creates and calls a stored procedure named test. This stored

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"| Here, each value is enclosed in double quotes ("), each column is separated by a comma (,) and each row is separated by a pipe character (|).

Explanation / Answer

USE my_guitar_shop; DROP PROCEDURE IF EXISTS test; DELIMITER // Create Procedure test(OUT results VARCHAR(150) Begin DECLARE names_var VARCHAR(50); DECLARE prices_var DECIMAL (8,2); 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; END // DELIMITER ; Call test(@res); SELECT @res;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.