mysql 储存过程 参数_MySQL存储过程参数

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, OUTor INOUT.IN – is the default mode. When you define an INparameter 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 INparameter 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.

OUT – the value of an OUTparameter 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 OUTparameter when it starts.

INOUT – an INOUTparameter is the combination of INparameter and OUTparameter. It means that the calling program may pass the argument, and the stored procedure can modify the INOUTparameter and pass the new value back to the calling program.

The syntax of defining a parameter in the stored procedures is as follows:MODE param_name param_type(param_size)

The MODE could be IN, OUTor INOUT, depending on the purpose of parameter in the stored procedure.

The param_nameis the name of the parameter. The name of 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 by any 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.

MySQL stored procedure parameter examples

IN parameter example

The following example illustrates how to use the INparameter in the GetOfficeByCountrystored procedure that selects offices located in a specified country.DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT *    FROM offices   WHERE country = countryName; END // DELIMITER ;

The countryName is the INparameter of the stored procedure. Inside the stored procedure, we select all offices that locate in the country specified by the countryName parameter.

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

To get all offices in France, you pass the France literal string to the GetOfficeByCountrystored procedure as follows:CALL GetOfficeByCountry('France')

OUT parameter example

The following stored procedure returns the number of orders by order status. It has two parameters:orderStatus: INparameter that is the order status which you want to count the orders.

total: OUTparameter that stores the number of orders for a specific order status.

The following is the source code of the CountOrderByStatusstored procedure.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 CountOrderByStatusstored procedure and pass the order status as Shipped, and also pass an argument ( @total) to get the return value.CALL CountOrderByStatus('Shipped',@total); SELECT @total;

To get the number of orders that are in process, we call the CountOrderByStatus stored procedure as follows:CALL CountOrderByStatus('in process',@total); SELECT @total AS  total_in_process;

INOUT parameter example

The following example demonstrates how to use INOUTparameter in the stored procedure.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_counterstored procedure accepts one INOUTparameter ( count) and one INparameter ( inc).

Inside the stored procedure, we increase the counter ( count) by the value of the inc parameter.

See how we call the set_counterstored procedure: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, OUTand INOUT.

Related Tutorials