How to create and use MySQL stored procedures
This is a simple example of a MySQL stored procedure that has both an in and out parameter:
DELIMITER |
DROP PROCEDURE IF EXISTS category_for |
CREATE PROCEDURE category_for (product_id int, OUT r_category_id INT)
BEGIN
DECLARE category_id INT;
DECLARE products_cursor CURSOR FOR SELECT category_id FROM products where id = product_id;
OPEN products_cursor;
FETCH products_cursor INTO category_id;
CLOSE products_cursor;
SET r_category_id = category_id;
END |
DELIMITER ;
Note that we use ; inside the procedure so we have to set the delimiter to | temporarily.
To call the stored procedure use the following SQL commands:
CALL category_for(202, @category_id);
The @category_id session variable will now contain the procedure's output:
select @category_id;
+-------+
| @category_id |
+-------+
| 3 |
+-------+