Value or Reference?
IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.
Here’s the analogy and below is the code to support it:
“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she can’t shrink, grow, or learn throughout the story; whereas, a pass-by-reference parameter in a procedure is like sending Alice into the rabbit hole where she can shrink, grow, fight the Jabberwocky, and learn things that make her life better when she exits the rabbit hole – consistent with the storyline of Alice’s revisit to Wonderland.”
The example code creates a stored procedure that accepts two parameters – one pass-by-value and one pass-by-reference. Inside the procedure there’s a local variable and a reassignment of value to the pass-by-reference parameter. It’s in this wonderland procedure (by the way don’t forget to manage the DELIMITER value when you test it):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | CREATE PROCEDURE wonderland ( IN pv_value_param VARCHAR(20) , INOUT pv_ref_param VARCHAR(20)) BEGIN /* Declare a variable. */ DECLARE lv_value_param VARCHAR(20); /* Query the local variable and reference parameter before changing values. */ SELECT 'On Entry' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; /* Assign a lowercase value parameter to a local variable. */ SET lv_value_param := LOWER(pv_value_param); /* Assign a uppercase reference parameter value to the reference parameter. */ SET pv_ref_param := UPPER(pv_ref_param); /* Query the local variable and reference parameter after changing values. */ SELECT 'On Exit ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter" , IFNULL(lv_value_param,' ') AS "Local Variable"; END; $$ |
A tester procedure than tests how the pass-by-value and pass-by-reference modes of operation differ. It’s here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE tester ( IN pv_value_param VARCHAR(20) , IN pv_ref_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ CALL wonderland(pv_value_param, pv_ref_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter" , pv_ref_param AS "Reference Parameter"; END; $$ |
You call the tester program with this syntax:
CALL tester('Alice','Alice'); |
The test case returns the following values:
+----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | Before | Alice | Alice | +----------+-----------------+---------------------+ 1 row in set (0.00 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Entry | Alice | Alice | | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+----------------+ | Where | Value Parameter | Reference Parameter | Local Variable | +----------+-----------------+---------------------+----------------+ | On Exit | Alice | ALICE | alice | +----------+-----------------+---------------------+----------------+ 1 row in set (0.02 sec) +----------+-----------------+---------------------+ | Where | Value Parameter | Reference Parameter | +----------+-----------------+---------------------+ | After | Alice | ALICE | +----------+-----------------+---------------------+ 1 row in set (0.03 sec) |
Basically, Alice inside the pv_ref_param parameter grows to uppercase during the trip through the wonderland procedure, while Alice inside the pv_value_param remains unchanged. If it didn’t help you learn a principle, maybe it gave you a laugh on how to view the travels of IN-only and INOUT parameters. 😉
NOTE: Line 12 in the alice procedure is impossible with an immutable variable because the value of a call parameter to an immutable IN-only formal parameter shouldn’t allow the call parameter value to change during the execution of the program. This means that MySQL IN-only mode parameter values actually hold a mutable copy of the call parameter and the call parameter can be either a variable or literal value. The parameter value is discarded at the completion of procedure. This is more easily demonstrated with this assignment procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE PROCEDURE assignment ( IN pv_value_param VARCHAR(20)) BEGIN /* Query the local and reference parameters. */ SELECT 'Before ' AS "Where" , pv_value_param AS "Value Parameter"; /* Call the wonderland procedure that changes the pass-by-reference parameter. */ SET pv_value_param := UPPER(pv_value_param); /* Query the local and reference parameters. */ SELECT 'After ' AS "Where" , pv_value_param AS "Value Parameter"; END; $$ |
Therefore a call like this prints an uppercase INBOUND string inside the program but can return nothing to the calling scope since the call parameter is a string literal.
SET @sv_session = 'inbound'; CALL assignment(@sv_session); SELECT @sv_test AS "Current Value"; |
The query displays:
+---------------+ | Current Value | +---------------+ | inbound | +---------------+ |
I hope this helps.
Subscribe via RSS