Types and features of MySQL stored procedure parameters

The following articles mainly introduce three different types of MySQL stored procedure parameters, including in, out, And inout, the following articles describe their different functions and different characteristics. The following is the description of the main content of the article. I hope you will gain some benefits.

I. MySQL stored procedure parameters in)

MySQL stored procedure "in" parameter: similar to the value passing of function parameters in C language, MySQL stored procedure may modify this parameter internally, but modify the in type parameter, not visible for callers ).

Drop procedure if exists pr_param_in; create procedure pr_param_in (in id int -- in type MySQL Stored procedure Parameter)

 
 

  
  
begin if (id is not null) then set idid = 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 |+----------+| 11 |+---------+mysql> select @id as id_out;  
  
  
+--------+| id_out |+--------+| 10 |+--------+  
 
 

We can see that the input value of user variable @ id is 10. After the stored procedure is executed, the internal value of the procedure is 11id_inner), but the external variable value is still 10id_out ).

Ii. MySQL Stored Procedure Parameter out)

MySQL Stored Procedure "out" parameter: transfers a value from the stored procedure to the caller. In a stored procedure, the initial value of this 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)begin select id as id_inner_1;  
 
 

The initial id value is

 
 

  
  
null if (id is not null) then set idid = id + 1;   
  
  
select id as id_inner_2;   
  
  
else select 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 |+--------+  
 
 

It can be seen that although we set the User-Defined variable @ id to 10, after passing @ id to the stored procedure, the initial value of id is always nul1__inner_1) within the stored procedure ). The last id value id_out = 1) is returned to the caller.

Iii. MySQL Stored Procedure Parameter inout)

The inout parameter of the MySQL stored procedure is similar to the out parameter, and can be passed to the caller from the stored procedure. 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)begin select id as id_inner_1;  
 
 

The id value is the value passed in by the caller.

 
 

  
  
if (id is not null) then set idid = id + 1;   
  
  
select id as id_inner_2;  
  
  
else select 1 into id;   
  
  
end if; select id as id_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 |+------------+| 10 |+------------++------------+| id_inner_2 |  
  
  
+------------+| 11 |+------------++------------+| id_inner_3 |+------------+  
  
  
| 11 |+------------+mysql>mysql> select @id as id_out;  
  
  
+--------+| id_out |+--------+| 11 |+--------+  
 
 

From the results, we can see that @ id10) is passed to the stored procedure, and the stored procedure finally returns the calculated result value 11id_inner_3 to the caller. The inout parameter of the MySQL stored procedure is similar to that of the C-language function.

Using the preceding example: If you only want to pass data to the MySQL stored procedure, use the "in" type parameter. If you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. In this case, you need to use the "inout" type parameter.

The above content provides three types of MySQL stored procedure parameters: in, out, And inout. I hope you will find some gains.