MySQL's stored procedures are supported from version 5, so the stored procedures are now available for general use. Share your knowledge and understanding of MySQL stored procedures today.
Some simple calls and grammar rules here is not to repeat, there are many examples on the web. This is mainly about the use of the commonly used cursor plus loop nesting.
First we introduce the classification of loops:
(1) While ... End While
(2) LOOP ... End LOOP
(3) REPEAT ... End REPEAT
(4) Goto
There are three standard loops: while loops, loop loops, and repeat loops. There is also a nonstandard way of looping: GOTO (no introduction).
(1) while ... End While
Copy Code code 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 the way of the while loop. It is similar to an if statement, using "SET v = 0;" Statement to prevent a common error, if not initialized, the default variable value is NULL, and null and any value operation result is null.
(2) REPEAT ... End REPEAT
Copy Code code 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, with the same function as the previous while loop. The difference is that it checks the results after execution, while the while is a pre-execution check. Similar to a Do while statement. Note that there is no semicolon after the until statement, where you can write a semicolon without a semi-colon, and of course you add an extra semicolon better.
(3) LOOP ... End LOOP
Copy Code code 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;
These are examples of loop loops. The loop loop does not require an initial condition, which is similar to a while loop, and it does not require an end conditions as well as a repeat loop.
Iterate iterations
If the target is a iterate (iterative) statement, you must use the Leave statement
Copy Code code 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 (iteration) statement, like the leave statement, is also a circular reference within the loop, a bit like "Continue" in C, which can also appear in compound statements, referencing compound statement labels, iterate (iteration) meaning to restart compound statements.
The above is a description of several cases of the cycle. This is followed by an example with a cursor to explain in detail.
Copy Code code 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 fee;//declares a cursor variable
Declare continue handler for not found set done=1;//declare a bit of the end of the loop
Set done=0;
Select Date_format (now (), '%Y ') into p_year;
Open feecodecursor;//Cursor
Loop_label:loop
Fetch feecodecursor into p_feecode;//insert a cursor into a 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 the 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, '% ') Li MIT 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;
Closing the cursor after the close feecodecursor;//loop
End
The above is just a simple example to illustrate how to use, we do not need to focus on the specific business logic, only need to focus on the changes in the flag bit value, has been the cycle to leave. And how cursors are declared, and how they are used, there's no big difference between the specifics of the operation and the ordinary SQL statements. This is a one-tier loop, and as complex business requires two-tier three-tier, you can continue nesting in the same way. The following gives a double nested loop, and you just need to focus on the nesting structure.
Copy Code code 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 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//First nesting start
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 second layer of 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 the P_billmoney from Bill_data where Feecode=p_feecode and Projectid=p_projectid are 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 the 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
