Today, I will share my understanding and understanding of the Mysql stored procedure. Here I will mainly talk about the commonly used nested use of cursors and loops.
Today, I will share my understanding and understanding of the Mysql stored procedure. Here I will mainly talk about the commonly used nested use of cursors and loops.
Mysql stored procedures are supported from version 5. Therefore, stored procedures are generally used. Today, I will share my understanding about the Mysql stored procedure.
Some simple calls and syntax rules are not described here. There are many examples on the Internet. Here we will mainly talk about the commonly used nested use of cursors and loops.
First, we will introduce the classification of loops:
(1) WHILE... END WHILE
(2) LOOP... END LOOP
(3) REPEAT... END REPEAT
(4) GOTO
There are three standard LOOP methods: while loop, LOOP, and repeat loop. There is also a non-standard loop method: GOTO (not described ).
(1) WHILE... END WHILE
The Code is as follows:
Create procedure p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v <5 DO
Insert into t VALUES (v );
SET v = v + 1;
End while;
END;
This is a WHILE loop. Similar to the IF statement, the "SET v = 0;" statement is used to prevent a common error. IF Initialization is not performed, the default variable value is NULL, NULL and any value operation results in NULL.
(2) REPEAT... END REPEAT
The Code is as follows:
Create procedure p15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
Insert into t VALUES (v );
SET v = v + 1;
UNTIL v> = 5
End repeat;
END;
This is an example of a REPEAT loop. The function is the same as the previous WHILE loop. The difference is that it checks the results after execution, WHILE is the pre-execution check. Similar to the do while statement. Note that the UNTIL statement is not followed by a semicolon. You can leave the semicolon here. Of course, it is better to add an additional semicolon.
(3) LOOP... END LOOP
The Code is as follows:
Create procedure p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
Loop_label: LOOP
Insert into t VALUES (v );
SET v = v + 1;
IF v> = 5 THEN
LEAVE loop_label;
End if;
End loop;
END;
The above is an example of LOOP. LOOP loops do not require an initial condition, which is similar to a while loop. At the same time, it does not need an ending condition like a repeat loop.
ITERATE Iteration
If the target is an ITERATE (iterative) Statement, the LEAVE statement must be used.
The Code is as follows:
Create procedure p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
Loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
End if;
Insert into t VALUES (v );
SET v = v + 1;
IF v> = 5 THEN
LEAVE loop_label;
End if;
End loop;
END;
The ITERATE (iterative) Statement and the LEAVE statement are also referenced cyclically within the loop. It is a bit like the "Continue" in C, and can also appear in compound statements, the composite statement label is referenced. ITERATE (iteration) means to re-start the composite statement.
The above is an introduction to several situations of loops. Next we will introduce an example with a cursor for detailed explanation.
The Code is as follows:
Begin
Declare p_feeCode varchar (20 );
Declare p_feeName varchar (20 );
Declare p_billMoney float (12 );
Declare p_schemeMoney float (12 );
Declare allMoney float (10 );
Declare allUsedMoney float (10 );
Declare p_year varchar (50 );
Declare p_totalCompeleteRate float (12 );
Declare done int (10 );
Declare flag int (2 );
Declare feeCodeCursor cursor for select feeCode from cursor; // declare a cursor variable
Declare continue handler for not found set done = 1; // declare the flag of the end of the loop
Set done = 0;
Select date_format (now (), '% y') into p_year;
Open feeCodeCursor; // open the cursor
Loop_label: LOOP
Fetch feeCodeCursor into p_feeCode; // Insert the cursor into the declared variable
If done = 1 then
Leave loop_label;
Else
Set flag = 0;
End if;
Set p_schemeMoney = 0;
Set p_billMoney = 0;
Select feeName into p_feeName from nation where feeCode = p_feeCode;
Select sum (billMoney) into p_billMoney from bill_data where feeCode = p_feeCode and billDate like Concat (p_year, '% ');
Select schemeMoney into p_schemoney from total_scheme where feeCode = p_feeCode and schemeDate like Concat (p_year, '%') limit 1;
If flag = 0 then
Set done = 0;
End if;
If p_schemeMoney = 0 then
Set p_totalCompeleteRate =-1.0;
Else
Set p_totalCompeleteRate = (1.0 * p_billMoney)/p_schemoney;
End if;
Insert into total_summary values (p_feeCode, p_feeName, p_year, p_billMoney, p_totalCompeleteRate );
Commit;
End LOOP;
Close feeCodeCursor; // close the cursor after the loop ends.
End
The above is just a simple example to illustrate how to use it. You don't need to pay attention to the specific business logic. You only need to pay attention to the modification of the flag value and the time when it has been recycled. And how to declare and use the cursor. There is no big difference between the specific operations in the cursor and common SQL statements. A layer of loops is used here. As for complex services, two layers of layers are required. You can continue nesting in the same way. The following provides a double-layer nested loop. You only need to pay attention to the nested structure.
The Code is as follows:
Begin
Declare p_projectID varchar (20 );
Declare p_projectName varchar (20 );
Declare p_feeCode varchar (20 );
Declare p_feeName varchar (20 );
Declare p_projectSchemeMoney float (10 );
Declare p_projectMoney float (10 );
Declare p_billMoney float (10 );
Declare p_year varchar (50 );
Declare p_projectFeeCompeleteRate float (10 );
Declare done1 int (10 );
Declare done2 int (10 );
Declare flag int (2 );
Declare feeCodeCursor cursor for select feeCode from cursor;
Declare continue handler for not found set done1 = 1;
Set done1 = 0;
Select date_format (now (), '% y') into p_year;
Delete from project_fee_summary;
Open feeCodeCursor;
Repeat // the first layer of nesting begins.
Fetch feeCodeCursor into p_feeCode;
Select feeName into p_feeName from nation where feeCode = p_feeCode;
If not done1 then
Begin
Declare projectIDCursor cursor for select projectID from project;
Declare continue handler for not found set done2 = 1;
Set done2 = 0;
Open projectIDCursor;
Loop_label: LOOP // Layer 2 nesting
Fetch projectIDCursor into p_projectID;
Select projectName into p_projectName from project where projectID = p_projectID;
If done2 = 1 then
Leave loop_label;
Else
Set flag = 0;
End if;
If not done2 then
Set p_projectSchemeMoney = 0;
Select sum (billMoney) into p_billMoney from bill_data where feeCode = p_feeCode and projectID = p_projectID and billDate like Concat (p_year, '% ');
Select projectSchemeMoney into p_projectschemoney from project_scheme where feeCode = p_feeCode and projectID = p_projectID;
If flag = 0 then
Set done2 = 0;
End if;
If p_projectSchemeMoney = 0 then
Set p_projectFeeCompeleteRate =-1;
Else
Set p_projectFeeCompeleteRate = (1.0 * p_billMoney)/p_projectschemoney;
End if;
Insert into project_fee_summary values (p_feeCode, p_projectID, p_projectName, p_feeName, p_year, p_billMoney, p_projectFeeCompeleteRate, p_projectFeeCompeleteRate );
End if;
End LOOP;
Select sum (billMoney) into p_projectMoney from bill_data where feeCode = p_feeCode and billDate like Concat (p_year, '% ');
Set p_projectFeeCompeleteRate = (1.0 * p_projectMoney)/p_projectschemoney;
Insert into project_fee_summary values (p_feeCode, "total", "total", p_feeName, p_year, p_projectMoney, p_projectFeeCompeleteRate, p_projectFeeCompeleteRate );
Close projectIDCursor;
End;
End if;
Until done1
End repeat;
Close feeCodeCursor;
End
