In-depth explanation of MySQL stored procedures (in, out, inout)

1. Introduction

It has been supported since version 5.0. It is a set of SQL statements (encapsulation) to complete specific functions. It is faster and more efficient than traditional SQL.

Advantages of stored procedures

1. After executing once, the generated binary code will be stored in the buffer (for the next execution) to improve execution efficiency

2. A collection of SQL statements plus control statements, highly flexible

3. Store on the server side and reduce network load when the client calls

4. Can be called repeatedly and modified at any time without affecting the client call

5. All database operations can be completed and the information access rights of the database can be controlled

Why use stored procedures?

1. Reduce network load; 2. Increase security

2. Create a stored procedure

2.1 Creating a Basic Process

Use the create procedure statement to create a stored procedure

The main part of a stored procedure is called the procedure body; it starts with begin and ends with end$$

#Declaration statement terminator, can be customized:
delimiter $$
#Declare a stored procedure create procedure stored procedure name (in parameter name parameter type)
begin
#define variable declare variable name variable type #assign variable value set variable name = value sql statement 1;
 sql statement 2;
 ...
end$$
#Restore to the original statement terminator delimiter; (with space)

Examples:

mysql> delimiter $$
mysql> create procedure text()
 -> begin
 -> select * from stu.a_player;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;

Calling a stored procedure

call stored procedure name (actual parameters);
mysql> call text;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 88 |
| 2 | lisi | 89 |
| 3 | wangwu | 67 |
| 4 | zhaoliu | 90 |
| 5 | xuli | 80 |
| 6 | keke | 75 |
+----+----------+-------+
6 rows in set (0.00 sec)

Deleting a stored procedure

mysql> drop procedure text;

2.2 Parameters of stored procedures

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three types of parameters: IN, OUT, and INOUT. The forms are as follows:

CREATEPROCEDURE stored procedure name ([[IN | OUT | INOUT ] parameter name data type ...])

IN input parameter: indicates that the caller passes a value to the procedure (the passed value can be a literal or a variable)

OUT Output parameter: indicates that the procedure passes a value to the caller (can return multiple values) (the output value can only be a variable)

INOUT Input and output parameters: It indicates that the caller passes a value to the procedure, and the procedure passes a value to the caller (the value can only be a variable)

Example of passing parameters:

mysql> create procedure test1(in in_id int(2))
 -> begin
 -> select * from stu.a_player where id=in_id;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;

# Pass 4 to the in_id variable and execute the transaction mysql> call test1(4); 
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 4 | zhaoliu | 90 |
+----+---------+-------+
1 row in set (0.00 sec)

# Pass 6 to the in_id variable and execute the transaction mysql> call test1(6);
+----+------+-------+
| id | name | score |
+----+------+-------+
| 6 | keke | 75 |
+----+------+-------+
1 row in set (0.00 sec)

OUT

mysql> delimiter $$
mysql> create procedure test2(out aa int) 
 -> begin
 -> select aa;
 -> set aa=2;
 -> select aa;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;
#Pass the @aa variable to test2 transaction mysql> call test2(@aa);
+------+
| aa |
+------+
| NULL |
+------+
#out outputs parameters to the caller and does not receive input parameters, so aa is null
1 row in set (0.00 sec)
+------+
| aa |
+------+
| 2 |
+------+
The transaction sets the aa variable to 2 (globally), and then outputs 1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @aa;
+------+
| @aa |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
#Query variable outside transaction, has been modified

IN, OUT, INOUT comparison

mysql> delimiter //
mysql> create procedure test3(in num1 int, out num2 int, inout num3 int)
 -> begin
 -> select num1,num2,num3;
 -> set num1=10,num2=20,num3=30;
 -> select num1,num2,num3;
 -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;
mysql> call test3(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1 | NULL | 3 |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 10 | 20 | 30 |
+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

The in and inout parameters pass the value of the global variable into the stored procedure, while the out parameter does not pass the value of the global variable into the stored procedure. When using a stored procedure, the parameter values in, out, and inout will change.

mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
| 1 | 20 | 30 |
+-------+-------+-------+
1 row in set (0.00 sec)

After calling the stored procedure, it is found that the in parameter will not cause changes to the value of the global variable, while the out and inout parameters will change the value of the global variable after calling the stored procedure, and the value referenced by the stored procedure will be assigned to the global variable.

The in parameter assignment type can be a variable or a fixed value, while the out and inout parameter assignment type must be a variable.

Summarize

This is the end of this article about MySQL stored procedures (in, out, inout). For more information about MySQL stored procedures (in, out, inout), please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: