MySQL Stored Procedure Parameter usage in, out, inout

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

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) beginif (ID is not null) thenset 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 | + -------- +
 
 
We can see that the input value of user variable @ ID is 10. After the stored procedure is executed, the internal value of user variable @ ID is 11 (id_inner), but the external variable value is still 10 (id_out ).
 
Ii. MySQL stored procedure parameters (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) beginselect ID as id_inner_1; -- the initial id value is nullif (ID is not null) thenset id = ID + 1; select ID as id_inner_2; elseselect 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> 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 null (id_inner_1) within the stored procedure ). The last id value (id_out = 1) is returned to the caller.
 
Iii. MySQL stored procedure parameters (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) beginselect ID as id_inner_1; -- the id value is the value passed in by the caller. If (ID is not null) thenset id = ID + 1; select ID as id_inner_2; elseselect 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> call pr_param_inout (@ ID ); + ------------ + | bytes | + ------------ + | 10 | + ------------ + | bytes | + ------------ + | 11 | + ------------ ++ ------------ + | id_inner_3 | + ------------ + | 11 | + ------------ + MySQL> my SQL> select @ ID as id_out; + -------- + | id_out | + -------- + | 11 | + -------- + 
 
 The result shows that: after @ ID (10) is passed to the stored procedure, the stored procedure finally returns the calculated result value 11 (id_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 want to return values from the MySQL stored procedure, the "out" type parameter is used. If you need to pass data to the MySQL stored procedure, you need to compress the data before sending it back to us. In this case, you need to use the "inout" type parameter.