Returning Results as Procedure Parameters in MySQL
Handling MySQL Events with Triggers and Procedures Using SQL – Part 8
Division 5
Forward: In this part of the series, we see how procedures can return results as parameters in MySQL.
By: Chrysanthus Date Published: 7 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Why Return Values
Imagine that you have a trigger that calls a procedure to carry out certain task for the trigger. When the procedure finish carrying out the task, the trigger may need to know the results. In other words, the trigger may need to know the values the procedure arrived at. The procedure is written in such a way that it will place its resulting values in its parameters that are of the OUT parameter-mode. The trigger or calling environment will collect the result from there. The values placed by the procedure in its OUT parameters are called returned values.
This is the opposite of what happens with procedure parameters of the IN parameter-mode. The trigger or calling environment places values in the procedure’s IN parameters indirectly or directly. From there the statements in the procedure can use (read) the IN parameters. We saw this in the previous part of the series. Well, here, after the procedure has used the values of its IN variables, it would place the results in its OUT variables.
Placing Values in the OUT Parameters
There is a SQL statement called the SET statement. It is used within compound statement, and its variables only exist inside the compound statement. You can use it to place a value in the OUT parameter inside the procedure. You can also use the SQL SELECT statement to do this as I explain below.
The following illustrates how a procedure can place value in the OUT parameter using the SET statement.
Delimiter //
CREATE PROCEDURE sampleProcedure
(
OUT parA INTEGER
)
BEGIN
DECLARE variab1 INTEGER DEFAULT 25;
SET parA = variab1;
END;
//
In the SET statement, the value of variab1 is assigned to parA and so the value of variab1 is automatically copied to parA. In the SET statement, you type the OUT parameter first. parA is an OUT parameter. Note: when working with parameters inside the procedure compound statement you do not use the IN and OUT reserved words. The SET statement is a SQL statement, so it ends with a semicolon.
Now that value has been placed in an OUT parameter, the Calling environment can read it; I explain how this is done below.
In the above code a SET statement has been used to copy the value of one variable to another. The SET statement can also be used to declare and assign a variable inside a compound statement; the good news here, is that you do not need to specify the data type. If you want an INTEGER declared, you would type:
SET myVar = 46;
You can use any value in place of 46.
In one of the previous parts of the big series, I gave a simplified syntax of the SELECT statement as:
SELECT select-list
FROM from-expression
JOIN table-columns with ON operators
WHERE search-condition
GROUP BY group-by-expression
HAVING search condition
ORDER BY expression
There is an INTO clause in the complete syntax. The INTO clause fits as follows:
SELECT select-list
FROM from-expression
JOIN table-columns with ON operators
WHERE search-condition
GROUP BY group-by-expression
HAVING search condition
ORDER BY expression
INTO var_name [, var_name]]
What interest us here from the INTO clause, is the variable list (var_name [, var_name]]). For simplicity we shall work in this tutorial with only one variable. The number of items in the variable list should be equal to the number of items in the select-list (of the SELECT clause). Since we are dealing with only one variable, then there should be only one item in the select-list. The following procedure illustrates the use of the SELECT statement for the OUT parameter:
CREATE PROCEDURE sampleProcedure
(
OUT parA INTEGER
)
BEGIN
SELECT column-name
FROM table-name
WHERE columnID = value;
INTO parA
END;
parA is declared as an OUT integer in the parameter section of the procedure. parA is used in the INTO clause of the SELECT statement. It is understood that the SELECT statement will retrieve one integer value from the table. That is all you have to do for the returned value to be in the OUT parameter.
Consider the following compound statement of a trigger:
BEGIN
DECLARE var1 INTEGER DEFAULT 25;
SELECT var1;
END;
There is a statement that declares an integer variable in the compound statement. The SELECT statement below it, selects the declared variable, and not some column values from some table. Whenever the SELECT statement selects something, it displays the result. This variable is declared with the integer, 25. So the SELECT statement will display the value, 25. That is how the value of a variable can be displayed with SQL.
Reading the OUT Procedure Parameter Value
How do you read the OUT Procedure parameter value from outside the procedure? There has to be a declared variable (unassigned) outside the procedure. The type of the variable declared has to be the same as the type of the OUT parameter in the procedure.
A procedure is normally stored and you have to call it. In calling the procedure you have to use the variable declared outside the procedure as a parameter in the Calling statement.
Under that condition, the declared variable outside assumes the value of the OUT parameter in the procedure. Then you can display the value from the variable declared outside the procedure, using the SELECT statement. The following compound statement would display the value of the OUT parameter, parA of the last procedure above:
BEGIN
DECLARE var1 INTEGER;
CALL sampleProcedure (var1);
SELECT var1;
END;
Note: INTEGER as a data type can be abbreviated to INT.
Remember, whenever you create a procedure, that procedure is saved. You can then call the procedure without seeing the code of the procedure.
This is it for this part of the series. We take a break and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT