MySQL Stored Procedures
Programming in MySQL – Part 4
MySQL Course
Foreword: In this part of the series I talk about the syntax of the stored procedure; I say how to pass parameters to a stored procedure and how the procedure can return results.
By: Chrysanthus Date Published: 27 May 2015
Introduction
You must have read all the previous parts of the series before reaching here, as this is a continuation.
A Procedure
A trigger is an immediate response to an event. I trigger is called automatically from an update, insert or delete event in a table. A procedure is like a trigger, but it is not called automatically. It has more flexibility in its coding and can do more analysis than a trigger. A trigger can call a procedure to broaden its activity and power.
A procedure can return a result set (columns and rows) but I will not go into that in this series.
Procedure Privileges
To keep things simple, let the DBA code the procedure. If the procedure would access a table, let the user of the procedure have the privilege to access the table. I will address privileges and security in a separate series.
The Create Procedure Syntax
In simple terms the CREATE PROCEDURE Syntax is:
CREATE
PROCEDURE p_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
routine_body:
Valid SQL routine statements within BEGIN and END
Remember, anything in square brackets is optional. | means and/or in its position.
p_name
The p_name is the name of your choice that you give to the procedure.
proc_parameter
proc_parameter are parameters separated by commas; non is obligatory. The syntax of each parameter is:
[ IN | OUT | INOUT ] param_name type
param_name is a name of your choice. type is a table column data type.
IN means the param_name represents a value that the procedure would receive from outside and use.
OUT means the param_name represents a value that can be read from outside the procedure. This value is normally given within the procedure after the procedure has done some analysis.
INOUT stands for both IN and OUT.
routine_body
A compound-statement is a BEGIN statement. routine_body is the compound statement.
A procedure can be dropped. In simple terms the syntax is:
DROP PROCEDURE p_name
Calling a Procedure
A procedure can be called as a separate statement in the command line tool. A trigger can also call a procedure in the same way. The syntax of the SQL Calling statement is:
CALL p_name([parameter[,...]]);
Note: a procedure can also call another procedure.
Parameter Passing
Imagine that you have a trigger that calls a procedure to carry out a certain task for the trigger. It should be possible for the trigger to send data to the procedure in the calling statement, so that the procedure can use the data to carry out the task the trigger wants. This process is called Passing Parameters to the Procedure.
Consider the following procedure:
delimiter //
CREATE PROCEDURE sampleProcedure (IN par1 INTEGER, IN par2 INTEGER)
BEGIN
#SQL statements
END;//
delimiter ;
With this procedure, we say the parentheses hold the declarations of the parameters. IN means that the parameters represent values that would be received from outside the procedure (when the procedure is called).
IN is a parameter mode. There are two other parameter modes, which are OUT and INOUT. You saw these above.
A compound statement can call a procedure, sending data to the procedure. The data passed can be the variables of the compound statement. The following code segment illustrates this.
Delimiter //
BEGIN
DECLARE var1 INTEGER DEFAULT 25;
DECLARE var2 INTEGER DEFAULT 30;
CALL sampleProcedure (var1, var2);
END;//
The CALL statement in the compound statement calls the procedure sending 25 and 30 with var1 and var2, respectively. The procedure will receive these numbers in the IN variables, say par1 and par2. Note how the variable declarations have been done in the compound statement and note how the CALLING statement has been typed (the name of the procedure is included).
Note that the data types for the declarations in the compound statement must be the same as the corresponding data types for the procedure parameters.
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. 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.
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;//
delimiter ;
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.
The SQL SELECT Statement
In one of the previous parts of the previous 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.
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 or set to default) 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 (saved just after creation) 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. In the parameter list (argument list) of the calling statement, the variable declared outside the procedure has to be in the position of OUT.
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 code sample illustrates this:
SET @var = 0;
delimiter //
CREATE PROCEDURE sampleProcedure (OUT parA INTEGER)
BEGIN
DECLARE variab1 INTEGER DEFAULT 25;
SET parA = variab1;
END;//
delimiter ;
CALL sampleProcedure (@var);
@ is used to set (declare) a variable (not an array) outside a compound statement.
Remember, whenever you create a procedure, that procedure is saved. You can then call the procedure without seeing the code of the procedure.
Dropping a Procedure
The syntax to drop a procedure is:
DROP PROCEDURE p_name;
This is it for this part of the series. We take a break and continue in the next part.
Chrys
Related Links
Implementing Database in MySQLProgramming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course
BACK NEXT