Mysql Stored Procedure: How to Process an Empty Resultset
I wrote a procedure where one statement is not executed as expected:
SELECT thumb_image into v_thumb_image FROM RESTAURANT_IMAGE WHERE
RESTAURANT_ID = v_restaurant_id
The reason I researched is because at any given time the result set is empty, the procedure does not take any further action.
Note that I am calling this in LOOP.
I don't want to stop execution if for any, the v_restaurant_id
result set is empty.
FULL PROCEDURE:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_restaurant_details`()
BEGIN
DECLARE v_finished_cuisines,
v_finished,
v_restaurant_id,
v_count_discount
INT DEFAULT 0;
DECLARE v_cuisines,
v_thumb_image
varchar(200) DEFAULT "";
DECLARE cuisine_title varchar(50) DEFAULT "";
-- Fetch all restaurant id
DECLARE restaurant_cursor CURSOR FOR
SELECT id FROM delhifoodonline.restaurant order by id desc;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN restaurant_cursor;
get_restaurant: LOOP
FETCH restaurant_cursor INTO v_restaurant_id;
IF v_finished = 1 THEN
LEAVE get_restaurant;
END IF;
SET v_finished_cuisines ="";
SET v_thumb_image = "";
begin
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;
SELECT thumb_image into v_thumb_image
FROM restaurant_image
WHERE restaurant_id = v_restaurant_id
ORDER BY id
LIMIT 1;
end;
SELECT count(*) into v_count_discount FROM restaurant_discount WHERE
restaurant_id = v_restaurant_id;
BLOCK2: BEGIN
DECLARE cuisines_cursor CURSOR FOR
SELECT cuisine.title FROM restaurant_cuisine INNER JOIN cuisine
ON restaurant_cuisine.cuisine_id = cuisine.id
WHERE
restaurant_cuisine.restaurant_id = v_restaurant_id
LIMIT 0,5;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished_cuisines = 1;
SET v_cuisines = "";
OPEN cuisines_cursor;
get_cuisine: LOOP
FETCH cuisines_cursor INTO cuisine_title;
IF v_finished_cuisines = 1 THEN
LEAVE get_cuisine;
END IF;
SET v_cuisines = CONCAT(cuisine_title,", ",v_cuisines);
END LOOP get_cuisine;
CLOSE cuisines_cursor;
END BLOCK2;
SET v_cuisines = TRIM(BOTH ", " FROM v_cuisines);
IF v_count_discount > 0 THEN
SET v_count_discount = 1;
ELSE
SET v_count_discount = 0;
END IF;
UPDATE restaurant SET
thumb_image = v_thumb_image,
cuisines_list = v_cuisines,
discount_available = v_count_discount
WHERE id= v_restaurant_id;
END LOOP get_restaurant;
CLOSE restaurant_cursor;
END
source to share
From the documentation :
NOT FOUND is shorthand for the SQLSTATE value class that starts with '02'. This is relevant in the context of cursors and is used to control what happens when the cursor reaches the end of the dataset. If no more rows are available, a No Data condition occurs with the SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for the NOT FOUND state). For example, see Section 13.6.6, "Cursors". This condition also occurs for SELECT ... INTO var_list statements that do not retrieve rows.
So your selection from the table restaurant_image
also matches the state NOT FOUND
where it doesn't return any rows and calls a specific handler that causes the loop to exit.
One solution is to declare another handler for this selection, by placing it inside a block BEGIN...END
:
begin
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_thumb_image = NULL;
SELECT thumb_image into v_thumb_image
FROM restaurant_image
WHERE restaurant_id = v_restaurant_id
ORDER BY id
LIMIT 1;
end;
In the end why is it done using stored procedure and cursors that would be slow. You can achieve the same functionality by doing a single statement:
UPDATE restaurant
SET thumb_image = (
SELECT thumb_image
FROM restaurant_image
WHERE restaurant_id = restaurant.id
ORDER BY id
LIMIT 1),
discount_available = IF(EXISTS(
SELECT 1
FROM restaurant_discount
WHERE restaurant_id = restaurant.id), 1, 0),
cuisines_list = (
SELECT group_concat(cuisine.title separator ', ')
FROM restaurant_cuisine
INNER JOIN cuisine ON restaurant_cuisine.cuisine_id = cuisine.id
WHERE restaurant_cuisine.restaurant_id = restaurant.id
LIMIT 0,5)
Or make it even faster by eliminating the subroutines for each line:
UPDATE restaurant r
LEFT JOIN
(SELECT restaurant_id, count(*) AS discount_available
FROM restaurant_discount
GROUP BY restaurant_id) d ON r.id = d.restaurant_id
LEFT JOIN
(SELECT restaurant_id, thumb_image
FROM restaurant_image r1
WHERE NOT EXISTS (
SELECT 1 FROM restaurant_image r2 WHERE r2.restaurant_id = r1.restaurant_id AND r2.id < r1.id
)) t ON r.id = t.restaurant_id
LEFT JOIN
(SELECT rc.restaurant_id, SUBSTRING_INDEX(GROUP_CONCAT(c.title SEPARATOR ', '), ',', 5) AS cuisines_list
FROM restaurant_cuisine rc
INNER JOIN cuisine c ON rc.cuisine_id = c.id
GROUP BY rc.restaurant_id
) rc ON r.id = rc.restaurant_id
SET r.discount_available = IF(d.discount_available = 0, 0, 1),
r.thumb_image = t.thumb_image,
r.cuisines_list = rc.cuisines_list
Try these subqueries separately to find a better understanding.
source to share