Mysql Data Storage process parameter instance details, mysql Data Storage

Mysql Data Storage process parameter instance details, mysql Data Storage

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) 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   |+--------+

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) begin select id as id_inner_1; -- the initial value of id 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 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) begin select id as id_inner_1; -- the id value is the value passed 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   |+--------+

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 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.

Summary

The above is all the details about mysql Data Stored Procedure Parameter instances. I hope to help you understand MySQL. If you are interested, you can continue to refer to this site: resolve the problem of selecting the storage time and date type in MySQL, MySQL declaration variables, and stored procedure analysis. If you have any questions, you can leave a message at any time, the editor will reply to you in a timely manner. Thank you for your support!