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