Mysql Stored Procedure experiences

Mysql Stored Procedure experiences
Question: What is the significance of the mysql stored procedure?
The stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution. The stored procedures pre-compile SQL statements on the database server, which improves the data query speed and reduces the pressure on the client.
Fast. Especially for complex business logic, this reduces the consumption of inter-network traffic.
Stored procedures can be reused to reduce the workload of database developers.

High security. You can set that only one user has the right to use the specified stored procedure.
When performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. If these operations are completed by a program, they become SQL statements that may need to be connected to the database multiple times. Instead of storage, you only need to connect to the database once.
Q: When is the mysql stored procedure used?

It is suitable for a single business to query multiple tables.
Complex data processing, such as report processing
When multiple conditions and multiple tables are queried, such as Paging
Mysql Stored Procedure tutorial

1. Create a stored procedure

2. Create a storage function

3. Define the Handler

Method 1: capture sqlstate_value
Declare continue handler for sqlstate '42s02 'SET @ info = 'could not find ';
Method 2: capture mysql_error_code
Declare continue handler for 1146 SET @ info = 'Con not find ';
Method 3: define conditions and then call
Declare can_not_find condition for 1146;
Declare continue handler for can_not_find set @ info = 'can not find ';
Method 4: Use SQLWARNING
Declare exit handler for sqlwarning set @ info = 'error ';
Method 5: Use not found
Declare exit handler for not found set @ info = 'can not find ';
Method 6: Use SQLEXCEPTION
Declare exit handlerfor sqlexception set @ info = 'error ';

4. Define the cursor

Declare cur_name varchar (20 );
Declare cur_address varchar (20 );
Declare cursor
Declare cur_student cursor for select name, address from student;
Open cursor
OPEN cur_student
Use cursor
FETCH cur_student into cur_name, cur_address;
Close the cursor
Close cur_student
5. Cyclic Control Process

Cyclic Control Process

L IF statement
If age = 10 then
Elseif age = 20 then
Else
End if;
L Case statement
CASE
WHEN THEN
WHER THEN
ELSE
End case;

L LOOP statement and LEAVE statement LEAVE are the end labels of the LOOP add_num
Add_num LOOP
SET @ count = @ count + 1;
If @ count = 100 then
LEAVE add_num;
End loop add_num;

L The ITERATE statement ends this loop
Add_num LOOP
SET @ count = @ count + 1;
If @ count = 100 then
ITERATE add_num;
Select * from student;
End loop add_num;
L Repeat statement
Repeat
Set @ count = @ count + 1;
Unitl @ count = 100;
END Repeat;
L While statement
While @ count & lt; 100 DO
SET @ count = @ count + 1;
End while;
6. Run the show command to view the stored procedure and stored function information.

7. modify stored procedures and stored Functions

8. Integrated instances
Now there is a student table. Please create a stored procedure (input parameter 1: Start age, input parameter 2: end age, output parameter: Total number of students that meet the requirements)