• notice
  • Congratulations on the launch of the Sought Tech site

More than three types of mysql stored procedure parameters (in, out, inout)

1.MySQL stored procedure parameter (in)
MySQL stored procedure "in" parameter: similar to the value transfer of C language function parameters, this parameter may be modified inside the MySQL stored procedure, but it is not The modification of the in type parameter is not visible to the caller.
The code is as follows:

drop procedure if exists pr_param_in;
create procedure pr_param_in
(
in id int-MySQL stored procedure parameters of type in
)
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: the user variable @id is passed in as 10, After executing the stored procedure, the internal value of 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" parameters: pass values ​​from the stored procedure to the caller.In the stored procedure, the initial value of this parameter is null, regardless of whether the caller sets a value for the stored procedure parameter.
The code is as follows:

drop procedure if exists pr_param_out;
create procedure pr_param_out
(
out id int
)
begin
select id as id_inner_1;-id 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 |
+--------+
It can be seen that although we set After the user-defined variable @id is 10, after passing @id to the stored procedure, the initial value of id is always null (id_inner_1) inside the stored procedure.The final 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, and can pass values ​​to the caller from within the stored procedure.The difference is: the caller can also pass the value to the stored procedure through the inout parameter.
The code is as follows:

drop procedure if exists pr_param_inout;
create procedure pr_param_inout
(
inout id int
)
begin
select id as id_inner_1;-id 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 |
+--------+
It can be seen from the result: after we pass @id(10) to the stored procedure, the stored procedure finally calculates the value of 11 (Id_inner_3) is passed back to the caller.The behavior of inout parameters of MySQL stored procedures is similar to that of passing by value in C language functions.

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

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+