Introduction to cursor looping for mysql stored procedures

  • 2020-05-14 05:05:46
  • OfStack

Mysql's stored procedures have only been supported since version 5, so they can be used for anything currently used in 1. Today I would like to share my knowledge and understanding of the Mysql stored procedure.

Simple calls and syntax rules are not covered here, but there are many examples on the web. Here we mainly talk about the commonly used cursor plus loop nested use.
First, the classification of cycles is introduced:
(1)WHILE ... END WHILE
(2)LOOP ... END LOOP
(3)REPEAT ... END REPEAT
(4)GOTO
There are three standard loops: the WHILE loop, the LOOP loop, and the REPEAT loop. There is also a non-standard way of looping: GOTO(no introduction).
(1)WHILE ... END WHILE
 
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 how WHILE loops. It is similar to the IF statement, which USES "SET v = 0;" Statement to prevent a common error, if not initialized, the default variable value is NULL, while NULL and any value operation result is NULL.
(2)REPEAT ... END REPEAT
 
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 the REPEAT loop, which has the same functionality as the previous WHILE loop 1. The difference is that it checks the results after execution, whereas WHILE checks before execution. Similar to the do while statement. Notice that there is no semicolon at the end of the UNTIL statement, so you don't have to put a semicolon here, but it's better if you add an extra semicolon.
(3)LOOP ... END LOOP
 
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 the LOOP loop. The LOOP loop does not require an initial condition, similar to the WHILE loop, and it does not require an end condition like the REPEAT loop 1.
ITERATE iteration
If the target is an ITERATE (iteration) statement, the LEAVE statement must be used
 
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 (iteration) statement, like LEAVE statement 1, is also a circular reference inside the loop. It is a bit like "Continue" in C language.
The above is an introduction to several cases of loops. This is followed by the introduction of an example with a cursor to explain in detail.
 
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 fee;// statement 1 Vernier variables  
declare continue handler for not found set done=1;// The bit that declares 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;// Inserts 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 fee 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_schemeMoney 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_schemeMoney; 
end if; 
insert into total_summary values(p_feeCode,p_feeName,p_year,p_billMoney,p_totalCompeleteRate); 
commit; 
end LOOP; 
close feeCodeCursor;// You need to close the cursor when the loop ends  
end 

The above is just a simple example to illustrate how to use, we do not need to pay attention to the specific business logic, only need to pay attention to the change of the value of the logo, when the loop has left. And how the cursor is declared and used, as for the specific operations inside it, there is not much difference from the normal sql statement. In this case, a layer 1 loop is used. For complex business needs, two layers and three layers are required, so you can continue nesting in the same way. The following shows a double-layer nested loop, again you just need to focus on the nested structure.
 
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 fee; 
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 1 Layer nesting begins  
fetch feeCodeCursor into p_feeCode; 
select feeName into p_feeName from fee 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// The first 2 Layer nesting begins  
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_projectSchemeMoney 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_projectSchemeMoney; 
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_projectSchemeMoney; 
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 

Related articles: