Parameters are IN (the default: a copy), OUT (starts as NULL; its final value lands in the caller's variable) or INOUT (both). Local variables come from DECLARE at the top of a BEGIN ... END block and die with it. User variables such as @fee belong to the session and carry OUT values back to the client. A bare name means a local variable before a column, so prefix parameters p_ and locals v_.
DELIMITER //
CREATE PROCEDURE shipping_quote(IN p_order INT UNSIGNED, OUT p_fee DECIMAL(6,2),
INOUT p_note VARCHAR(80))
BEGIN
DECLARE v_country CHAR(2);
SELECT c.country INTO v_country
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.id = p_order;
CASE v_country
WHEN 'MY' THEN SET p_fee = 5.00;
WHEN 'US' THEN SET p_fee = 12.00;
END CASE;
IF order_total(p_order) >= 80 THEN
SET p_fee = 0, p_note = CONCAT(p_note, ' ships free');
END IF;
END //
DELIMITER ;
SET @note = 'Order 2:';
CALL shipping_quote(2, @fee, @note);
SELECT @fee, @note;
CALL shipping_quote(1, @fee, @note);+------+---------------------+ | @fee | @note | +------+---------------------+ | 0.00 | Order 2: ships free | +------+---------------------+ ERROR 1339 (20000): Case not found for CASE statement
Order 1's customer is in Brazil. A CASE statement with no matching branch raises error 1339 (a CASE expression returns NULL), so give it an ELSE. SELECT ... INTO wants one row: none leaves the variable unchanged with warning 1329, two raise error 1172. Loops: WHILE ... DO tests first, REPEAT ... UNTIL tests last, and a labeled LOOP runs until LEAVE label (Cursors and Handlers), with ITERATE label starting the next pass.