MySQL Stored Procedure Control statements

1. variable Scope
   internal variables enjoy higher precedence within their scope when executed to end. variable, the internal variable disappears, and the variable is no longer visible at this time outside its scope, and should be storedthe declared variable cannot be found outside the procedure, but you can either pass out the parameter or assign its valueTo the session variable to save its value.
   

   MySQL > DELIMITER//MySQL >CREATE PROCEDURE proc3 ()-begin-Declare x1 varchar (5)default 'outer'; -begin-Declare x1 varchar (5)default 'Inner'; -SelectX1; -end; -SelectX1; -end; -//MySQL > DELIMITER;

2. conditional Statements
   • If-then-else Statement
     

     MySQL > DELIMITER//MySQL > CREATE PROCEDURE proc2 (in Parameterint)       -beginDeclarevar int; -Set var=parameter+1; -if var=0 ThenINSERT INTO T VALUES ( -); -Endif; -ifParameter=0 ThenUpdate TSets1=s1+1; -ElseUpdate TSets1=s1+2; -Endif; -end; -//MySQL > DELIMITER;

   • Case Statement
     

     MySQL > DELIMITER//MySQL > CREATE PROCEDURE proc3 (inchParameterint)       -beginDeclarevar int; -Set var=parameter+1; - Case varWhen0 ThenINSERT INTO T VALUES ( -); When1 ThenINSERT INTO T VALUES ( -); -ElseINSERT INTO T VALUES ( +); -End Case; -end; -//MySQL > DELIMITER;

3. Looping Statements
   • while End While statement
     

     MySQL > DELIMITER//MySQL >CREATE PROCEDURE proc4 ()-beginDeclarevar int; -Set var=0; - while var<6  DoINSERT INTO T VALUES (var); -Set var=var+1; -End while; -end; -//MySQL > DELIMITER;

   • repeat end Repeat statements
     

      mysql > DELIMITER // mysql > CREATE PROCEDURE proc5 () -> begin , declare v int  ; -set  v=0  ; -> repeat -> insert into t values (v)       ; -set  v=v+1  ;       -until V>=5 -> end repeat;       -> end; -// mysql > DELIMITER; 

     It checks the results after performing the operation, while the while is checked before execution.

   • Loop End Loop statement
     

     MySQL > DELIMITER//MySQL >CREATE PROCEDURE proc6 ()-begin-Declare Vint; -Setv=0; -Loop_lable:loop-INSERT into T values (v); -Setv=v+1; -ifV >=5 Then-leave loop_lable; -Endif; -end Loop; -end; -//MySQL > DELIMITER;

     Loop loops do not require initial conditions, which are similar to while loops and do not require an end condition like repeat loops, and the meaning of the leave statement is to leave the loop.

5. LABLES Identification
   LABLES (for example, loop_lable:) can be used before the begin repeat while or LOOP statement, and the statement designator can only be used before a legitimate statement. You can jump out of the loop so that the run instruction reaches the final step of the compound statement.
   
6. Iterate Iteration
   

   MySQL > DELIMITER//MySQL >CREATE PROCEDURE proc10 ()-begin-Declare Vint; -Setv=0; -Loop_lable:loop-ifv=3 Then-Setv=v+1; -iterate loop_lable; -Endif; -INSERT into T values (v); -Setv=v+1; -ifv>=5 Then-leave loop_lable; -Endif; -end Loop; -end; -//MySQL > DELIMITER;

   To start a compound statement from scratch by referencing the label of a compound statement

MySQL Stored Procedure Control statements