MySQL stored procedure parameter usage in, out, inout

There are three types of MySQL stored procedure parameters: In, out, inout. What are their roles and characteristics?

One, MySQL stored procedure parameters (in)

MySQL stored procedure "in" parameter: similar to the value of the C language function parameter, this parameter may be modified inside the MySQL stored procedure, but modifications to the in type parameter are not visible to the caller (caller).

drop procedure if exists pr_param_in;create procedure pr_param_in (in ID INT--type MySQL stored procedure parameter) beginif (ID is not NULL ) Thenset id = id + 1;end if;select ID as id_inner;end;

Set @id = 10;call pr_param_in (@id); select @id as Id_out;

Mysql> call pr_param_in (@id); +----------+| Id_inner |+----------+|       |+----------+mysql> Select @id as id_out;+--------+| Id_out |+--------+| Ten     |+--------+

You can see that the user variable @id passed in with a value of 10, after the stored procedure is executed, the internal value of the procedure is: Id_inner, but the value of the external variable is still: ten (id_out).

Ii. MySQL Stored procedure parameters (out)

The MySQL stored procedure "out" parameter: passes the value from inside the stored procedure to the caller. Inside the stored procedure, the initial value of the parameter is NULL, regardless of whether the caller sets a value for the stored procedure parameter.

drop procedure if exists pr_param_out;create procedure pr_param_out (out ID int) Beginselect ID as id_inner_1;  --ID Initial value Nullif (ID is not NULL) Thenset id = ID + 1;select ID as Id_inner_2;elseselect 1 into id;end if;select ID as ID _inner_3;end;

Set @id = 10;call pr_param_out (@id); select @id as Id_out;

mysql> Set @id = 10;mysql>mysql> call Pr_param_out (@id); +------------+| id_inner_1 |+------------+|       NULL |+------------+ +------------+| Id_inner_3 |+------------+|          1 |+------------+mysql> select @id as id_out;+--------+| Id_out |+--------+| 1      |+--------+

As you can see, although we have set the user-defined variable @id to 10, passing @id to the stored procedure, the initial value of the ID is always null (id_inner_1) inside the stored procedure. The last id value (id_out = 1) is passed back to the caller.

Third, MySQL stored procedure parameters (InOut)

The MySQL stored procedure inout parameter is similar to out, and can be passed from inside the stored procedure to the caller. The difference is that the caller can also pass the value to the stored procedure through the InOut parameter.

drop procedure if exists pr_param_inout;create procedure pr_param_inout (inout ID int) Beginselect ID as id_inner_1;  --ID value is the value passed in by the caller if (ID is not NULL) Thenset id = ID + 1;select ID as Id_inner_2;elseselect 1 into id;end if;select ID as I D_inner_3;end;

Set @id = 10;call pr_param_inout (@id); select @id as Id_out;

mysql> Set @id = 10;mysql>mysql> call Pr_param_inout (@id); +------------+| id_inner_1 |+------------+|         |+------------+ +------------+| id_inner_2 |+------------+|         One |+------------+ +------------+| Id_inner_3 |+------------+|         |+------------+mysql>mysql> Select @id as id_out;+--------+| Id_out |+--------+| One     |+--------+

From the results we can see that: after we pass @id (10) to the stored procedure, the stored procedure finally passes the computed value one (id_inner_3) back to the caller. The behavior of the MySQL stored procedure inout parameter is similar to the reference value in a C function.

Using the example above: if you just want to pass the data to the MySQL stored procedure, then use the "in" type parameter; if you only return a value from a MySQL stored procedure, use the "out" type parameter, and if you need to pass the data to the MySQL stored procedure, and then pass it back to us after some calculations, You want to use the "inout" type parameter.

MySQL stored procedure parameter usage in, out, inout