MySQL Stored Procedure Parameters

Summary: in this tutorial, we will show you how to write MySQL stored procedures with parameters. We will also give you a couple of stored procedure examples to help you understand how to use different kinds of stored procedure 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 orINOUT.

开发的存储过程大多需要参数。参数使存储过程更加灵活可用。在mysql中，参数有IN、OUT或者INOUT中的一个模式。

• IN – is the default mode. When you define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. In addition, the value of an INparameter is protected. It means that even the value of the IN 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 the IN parameter.
• IN —是默认的模式。当在存储过程中定义IN参数时，调用程序需要传递参数至存储过程。另外，IN参数的值是受保护的。这意味着IN参数的值在存储过程中被改变，其原来的值在存储过程结束会被还原。换句话说，存储过程仅对IN参数的副本进行操作。
  
• OUT – the value of an OUT 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 the OUT parameter when it starts.
• OUT - OUT参数的值可以在存储过程中改变并将新值返回至调用程序。注意的是在out参数开始时，存储过程不能访问OUT的初始值。
  
• INOUT – an INOUT parameter is the combination of IN parameter and OUT parameter. 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.
• INOUT - INOUT参数是IN参数和OUT参数的组合。即调用参数可以传递参数并且存储过程可以修改参数并返回新值。
  

The syntax of defining a parameter in the stored procedures is as follows:

1	MODE param_name param_type ( param_size )

• The MODE could be IN, OUT or INOUT , depending on the purpose of parameter in the stored procedure.
• MODE可以是IN、OUT或者INOUT中的一个，根据存储过程中参数的目的而定。
  
• The param_name is the name of the parameter. The name of parameter must follow the naming rules of the column name in MySQL.
• parm_name 是参数的名字。参数名字必须遵守mysql中列名的命名规则。
  
• Followed the parameter name is its data type and size. Like a variable, the data type of the parameter can by any MySQL data type.
• 接在参数名字后的是类型和大小。像变量类似，参数类型可以是mysql数据类型中的任何一个。
  

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.

MySQL stored procedure parameter examples

IN parameter example

The following example illustrates how to use the IN parameter in the GetOfficeByCountry stored procedure that selects offices located in a specified country.

下面的例子展示如何在GetOfficeByCountry存储过程中使用IN参数，该存储过程根据指定的国家选择部门。

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.

countryName是存储过程的IN参数。在存储过程内部，我们选出countryName参数指定的国家的所有部门。

Suppose, you want to get all offices in the USA, you just need to pass a value (USA) to the stored procedure as follows:

假设，想获得在USA的所有部门：

1	CALL GetOfficeByCountry ( 'USA' )

To get all offices in France, you pass the France literal string to the GetOfficeByCountry stored procedure as follows:

1	CALL GetOfficeByCountry ( 'France' )

OUT parameter example

The following stored procedure returns the number of orders by order status. It has two parameters:

下面的存储过程根据order status返回orders的数量。它有两个参数：

• orderStatus: IN parameter that is the order status which you want to count the orders.
• orderStatus：order status是IN参数，该参数传入想计算的orders
  
• total: OUT parameter that stores the number of orders for a specific order status.
• total：out参数返回指定order status的orders的数量
  

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 3

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 3

CALL CountOrderByStatus ( 'in process' , @ total ) ;   SELECT @ total AS    total_in_process ;

INOUT parameter example

The following example demonstrates how to use 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 one INOUT parameter ( count) and one INparameter ( inc).
• Inside the stored procedure, we increase the counter ( count) by the value of the incparameter.

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 in stored procedures, and introduced you to different parameter modes including IN, OUT and INOUT.

Related Tutorials

• Introduction to MySQL Stored Procedures