Detailed example of MySQL data storage process parameters

There are three types of MySQL stored procedure parameters: in, out, and inout. What are their respective functions and characteristics?

1. MySQL stored procedure parameters (in)

MySQL stored procedure "in" parameter: Similar to the value passing of function parameters in C language, this parameter may be modified inside the MySQL stored procedure, but the modification of the in type parameter is not visible to the caller.

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 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 |
+----------+
| 11 |
+----------+

mysql> select @id as id_out;
+--------+
| id_out |
+--------+
| 10 |
+--------+

It can be seen that the value passed into the user variable @id is 10. After executing the stored procedure, the value inside the procedure is: 11 (id_inner), but the value of the external variable is still: 10 (id_out).

2. MySQL stored procedure parameters (out)

MySQL stored procedure "out" parameter: pass values ​​from within the stored procedure to the caller. Inside a 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
)
begin
  select id as id_inner_1; -- The initial value of id is null
  if (id is not null) then
   set id = 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 inside the stored procedure is always null (id_inner_1). Finally the id value (id_out = 1) is passed back to the caller.

3. MySQL stored procedure parameters (inout)

MySQL stored procedure inout parameters are similar to out parameters, and both can pass values ​​from within the stored procedure to the caller. The difference is that the caller can also pass values ​​to the stored procedure through inout parameters.

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 by the caller if (id is not null) then
   set id = 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 after we pass @id (10) to the stored procedure, the stored procedure finally returns the calculated value 11 (id_inner_3) to the caller. The behavior of inout parameters in MySQL stored procedures is similar to passing by reference in C language functions.

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

Summarize

The above is all the content of this article about the detailed explanation of MySQL data storage process parameter examples. I hope it will help you understand MySQL. Interested friends can continue to refer to this site: Analysis of the selection problem of storing time and date types in MySQL, MySQL declaration of variables and stored procedure analysis, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!