转载自:http://www.mysqltutorial.org/stored-procedures-parameters.aspx
MySQL Stored Procedure Parameters
Summary: in this tutorial, you will learn how to write stored procedures that have parameters. You will also go through a couple of examples to understand different kinds of parameters.
Introduction to MySQL stored procedure parameters
Almost stored procedures that you develop require parameters. The parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes: IN,
OUT
, or INOUT
.
-
IN
– is the default mode. When you define anIN
parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. In addition, the value of anIN
parameter is protected. It means that even the value of theIN
parameter is changed inside the stored procedure, its original value is retained after the stored procedure ends. In other words, the stored procedure only works on the copy of theIN
parameter. -
OUT
– the value of anOUT
parameter can be changed inside the stored procedure and its new value is passed back to the calling program. Notice that the stored procedure cannot access the initial value of theOUT
parameter when it starts. -
INOUT
– anINOUT
parameter is a combination ofIN
andOUT
parameters. It means that the calling program may pass the argument, and the stored procedure can modify theINOUT
parameter, and pass the new value back to the calling program.
The syntax of defining a parameter in the stored procedures is as follows:
1
|
MODE param_name param_type(param_size)
|
- The
MODE
could beIN
,OUT
orINOUT
, depending on the purpose of the parameter in the stored procedure. - The
param_name
is the name of the parameter. The name of the parameter must follow the naming rules of the column name in MySQL. - Followed the parameter name is its data type and size. Like a variable, the data type of the parameter can be any valid MySQL data type.
Each parameter is separated by a comma (,
) if the stored procedure has more than one parameter.
Let’s practice with some examples to get a better understanding. We will use the tables in the sample database for the demonstration.
MySQL stored procedure parameter examples
The IN parameter example
The following example illustrates how to use the IN
parameter in the GetOfficeByCountry
stored procedure that selects offices located in a particular country.
1
2
3
4
5
6
7
8
|
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT *
FROM offices
WHERE country = countryName;
END //
DELIMITER ;
|
The countryName
is the IN
parameter of the stored procedure. Inside the stored procedure, we select all offices that locate in the country specified by the countryName
parameter.
Suppose, we want to get all offices in the USA, we just need to pass a value (USA) to the stored procedure as follows:
1
|
CALL GetOfficeByCountry('USA');
|
To get all offices in France, we pass the France literal string to the GetOfficeByCountry
stored procedure as follows:
1
|
CALL GetOfficeByCountry('France')
|
The OUT parameter example
The following stored procedure returns the number of orders by order status. It has two parameters:
-
orderStatus
: theIN
parameter that is the order status which we want to count the orders. -
total
: theOUT
parameter that stores the number of orders for a specific order status.
The following is the source code of the CountOrderByStatus
stored procedure.
1
2
3
4
5
6
7
8
9
10
11
|
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
IN orderStatus VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(orderNumber)
INTO total
FROM orders
WHERE status = orderStatus;
END$$
DELIMITER ;
|
To get the number of shipped orders, we call the CountOrderByStatus
stored procedure and pass the order status as Shipped
, and also pass an argument ( @total
) to get the return value.
1
2
|
CALL CountOrderByStatus('Shipped',@total);
SELECT @total;
|
To get the number of orders that are in-process, we call the CountOrderByStatus
stored procedure as follows:
1
2
|
CALL CountOrderByStatus('in process',@total);
SELECT @total AS total_in_process;
|
The INOUT parameter example
The following example demonstrates how to use an INOUT
parameter in the stored procedure.
1
2
3
4
5
6
|
DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
SET count = count + inc;
END$$
DELIMITER ;
|
How it works.
- The
set_counter
stored procedure accepts oneINOUT
parameter (count
) and oneIN
parameter (inc
). - Inside the stored procedure, we increase the counter (
count
) by the value of theinc
parameter.
See how we call the set_counter
stored procedure:
1
2
3
4
5
|
SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8
|
In this tutorial, we have shown you how to define parameters for a stored procedure, and introduced you to different parameter modes: IN, OUT, and INOUT.