MySQL stored procedure parameter usage in, out, inout_MySQL

MySQL stored procedure parameter usage in, out, inout bitsCN.com
There are three types of MySQL Stored Procedure parameters: in, out, and inout. What are their respective functions and features?
I. MySQL stored procedure parameter (in) MySQL stored procedure "in" parameter: similar to the value transfer of function parameters in C language, MySQL stored procedure may modify this parameter internally, however, the modification to the in type parameter is invisible to the caller (caller ). 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 | + -------- + you can see that the user changes The input value of volume @ id is 10. after the stored procedure is executed, the internal value of the process is 11 (id_inner), but the external variable value is still 10 (id_out ). II. MySQL stored procedure parameter (out) MySQL stored procedure "out" parameter: Pass the 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 | + -------- + we can see that although we have set the user-defined variable @ id to 10, after passing @ id to the stored procedure, in the stored procedure, the initial value of id is always null (id_inner_1 ). The last id value (id_out = 1) is returned to the caller. III. MySQL Stored Procedure parameters (inout) The inout parameter of MySQL stored procedure is similar to the out parameter, and can be passed 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) beginselect id as id_inner_1; -- the id value is the value passed 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); + ------------ + | id_inner_1 | +- ----------- + | 10 | + ------------ ++ ------------ + | bytes | + ------------ + | 11 | + ------------ ++ ------------ + | id_inner_3 | + ------------ + | 11 | + ------------ + mysql> mysql> select @ id as id_out; + -------- + | id_out | + -------- + | 11 | + -------- + the result shows that after passing @ id (10) to the stored procedure, the stored procedure finally returns the calculation result 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 return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. in this case, you need to use the "inout" type parameter.
Author: crocodilebitsCN.com