MYSQL Stored routines procedures and functions
Stored procedure with IN, OUT, INOUT parameters
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_nested_loop$$
CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
DECLARE a INTEGER DEFAULT 0;
DECLARE b INTEGER DEFAULT 0;
DECLARE c INTEGER DEFAULT 0;
WHILE a < i DO
WHILE b < j DO
SET c = c + 1;
SET b = b + 1;
END WHILE;
SET a = a + 1;
SET b = 0;
END WHILE;
SET x = a, y = c;
SET z = x + y + z;
END $$
DELIMITER ;Invokes (CALL) the stored procedure:
SET @z = 30;
call sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;Result:
+------+------+------+
| @x | @y | @z |
+------+------+------+
| 10 | 200 | 240 |
+------+------+------+An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns.
An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns.
An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.