Detailed Explanation of mysql Data Stored Procedure Parameter Example

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

1. MySQL Stored Procedure Parameters (in)

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

drop procedure if exists pr_param_in;
create procedure pr_param_in
(
  in id int -- in  Type of  MySQL  Stored procedure parameters 
)
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   |
+--------+

You can see that the user variable @ id passed in a value of 10, and after executing the stored procedure, the value inside the procedure is: 11 (id_inner), but the value outside the procedure is still: 10 (id_out).

2. MySQL Stored Procedure Parameters (out)

MySQL Stored Procedure "out" Parameter: Pass a value from within the stored procedure to the caller. Inside the 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; -- id  The initial value 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   |
+--------+

As you can see, although we set the user-defined variable @ id to 10, after passing @ id to the stored procedure, the initial value of id is always null (id_inner_1) inside the stored procedure. Finally, the id value (id_out = 1) is returned to the caller.

3. MySQL Stored Procedure Parameters (inout)

MySQL Stored Procedure inout parameters are similar to out in that they can pass values to the caller from within 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; -- id  Value is the value passed in 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   |
+--------+

As you can see from the results, after we pass @ id (10) to the stored procedure, the stored procedure finally passes back the calculated value of 11 (id_inner_3) to the caller. The MySQL stored procedure inout parameters behave similarly to the referential value-passing in C language functions.

Use the "in" type parameter if you just want to pass data to an MySQL stored procedure; If you return a value only from the MySQL stored procedure, use the "out" type parameter; If you need to pass the data to the MySQL stored procedure and pass it back to us after a little calculation, use the "inout" type parameter.

Summarize

The above is the full content of this article about the detailed explanation of mysql data stored procedure parameters, hoping to be helpful for everyone to understand MySQL. Interested friends can continue to refer to this site: Analysis of MySQL storage time and date type selection issues, MySQL declaration variables and stored process analysis, any questions can leave a message at any time, this site will reply to everyone in time. Thank you friends for your support to this site!