MySQL stored procedures-use of Process Control

#五. Use of Process Control

#1. IF Use

Create PROCEDURE Iftest1 ()
BEGIN
DECLARE a int DEFAULT 10;
--IF (a>1 &&a<20) Then
IF (a>1 and a<20) then
SELECT ' OK ';
ELSE SELECT ' No ';
END IF;
END;
Call Iftest ();

#2. Case use

Create PROCEDURE Casetest ()
BEGIN
DECLARE a int DEFAULT 10;
Case A
When a>10 then SELECT ' no ';
When ten then SELECT ' OK ';
ELSE SELECT ' Nono ';
End case;
END;
Call Casetest ();
#3. Loop use

CREATE PROCEDURE looptest ()
BEGIN
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
My_loop:loop
Set a=a+1;
If a>10 then LEAVE my_loop;
END if;
Set b=a;

END LOOP My_loop;
SELECT b;

End

Call Looptest ();

#4. LEAVE use jump out of the current loop to see the previous test case looptest equivalent to break in the Java loop

#5. Iterate use, jump to the loop head execution, equivalent to continue,iterate in the Java loop can only be used in the loop

CREATE PROCEDURE iteratetest ()
BEGIN
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
My_loop:loop
Set a=a+1;
If a>10 then LEAVE my_loop;
ELSEIF a>5 then iterate my_loop;
END if;
Set b=a;

END LOOP My_loop;
SELECT b;

End

Call Iteratetest ();

#6. REPEAT Loop with conditions

CREATE PROCEDURE repeattest ()
BEGIN
DECLARE a int DEFAULT 0;
REPEAT
Set a=a+1;
UNTIL a>10
END REPEAT;
SELECT A;
END;

Call Repeattest ();
#7. While with conditional loops

CREATE PROCEDURE whiletest ()
BEGIN
DECLARE a int DEFAULT 0;

While a<10 do
Set a=a+1;
END while;
SELECT A;

END;

Call Whiletest ()

MySQL stored procedures-use of Process Control