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 internally by the MySQL stored procedure, but the modification of the in type parameter is not visible to the caller (caller) (not visible).
Copy Code code as follows:
drop procedure if exists pr_param_in;
CREATE PROCEDURE Pr_param_in
(
in ID INT--type of 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 |
+--------+
You can see that the user variable @id pass in the value of 10, after executing the stored procedure, the value within the procedure is: one (Id_inner), but the external variable value is still: ten (id_out).
Second, MySQL stored procedure parameters (out)
MySQL stored procedure "out" parameter: passes the value to the caller from within the stored procedure. Within a stored procedure, the initial value of this parameter is NULL, regardless of whether the caller sets a value for the stored procedure parameter.
Copy Code code as follows:
drop procedure if exists pr_param_out;
CREATE PROCEDURE Pr_param_out
(
Out ID int
)
Begin
Select ID as id_inner_1; --The 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 |
+--------+
As can be seen, although we 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)
MySQL stored procedures inout parameters are similar to out, and can be passed from inside stored procedures to the caller. The difference is that the caller can also pass the value to the stored procedure through the InOut parameter.
Copy Code code 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 value is the value that the caller passed in
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 returns the computed value (id_inner_3) back to the caller. The behavior of the MySQL stored procedure inout parameter is similar to the reference value in the C language function.
With the above example: if you just want to pass data to the MySQL stored procedure, 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 pass some calculations back to us, To use the "inout" type parameter.
