MySQL存储过程之代码块、条件控制、迭代

1，代码块
代码顺序

 

1 ，变量和条件声明   
2 ，Cursor声明   
3 ，Handler声明   
4 ，程序代码  

 

可以给代码块加lebel，这样END匹配比较直观，还可以用LEAVE语句来终结代码块:

[ label: ]   BEGIN    
    varaiable  and  condition declarations   
     cursor  declarations   
    handler declarations   
  
    program code   
END   [ label ] ;  

 

代码块可以嵌套:

代码

CREATE   PROCEDURE  nested_blocks()   
BEGIN    
     DECLARE  my_variable  varchar ( 20 );   
     SET  my_variable = ' This value was set in the outer block ' ;   
     BEGIN    
         SET  my_variable = ' This value was set in the inner block ' ;   
     END ;   
     SELECT  my_variable,  ' Changes in the inner block are visible in the outer block ' ;   
END ; 

 

LEAVE的例子:

 

代码

CREATE   PROCEDURE  nested_blocks()   
outer_block:  BEGIN    
     DECLARE  l_status  int ;   
     SET  l_status = 1 ;   
    inner_block:  BEGIN    
         IF  (l_status = 1 )  THEN    
            LEAVE inner_block;   
         END   IF ;   
         SELECT   ' This statement will never be executed ' ;   
     END  inner_block;   
     SELECT   ' End of program ' ;   
END  outer_block;  

 

2，条件控制
IF:

IF  expression  THEN  commands   
     [ ELSEIF expression THEN commands ]    
     [ ELSE commands ]    
END   IF ; 

 

例子:

代码

IF  (sale_value  >   200 )  THEN    
    CALL free_shipping(sale_id);     /* Free shipping */   
     IF  (customer_status = ' PLATINUM ' )  THEN    
        CALL apply_discount(sale_id, 20 );  /*  20% discount  */   
    ELSEIF (customer_status = ' GOLD ' )  THEN    
        CALL apply_discount(sale_id, 15 );  /*  15% discount  */   
    ELSEIF (customer_status = ' SILVER ' )  THEN    
        CALL apply_discount(sale_id, 10 );  /*  10% discount  */   
    ELSEIF (customer_status = ' BRONZE ' )  THEN    
        CALL apply_discount(sale_id, 5 );  /*  5% discount */   
     END   IF ;   
END   IF ;  

 

CASE:

CASE    
     WHEN  condition  THEN    
        statements   
     [ WHEN condition THEN   
        statements... ]    
     [ ELSE   
        statements ]    
END   CASE ;  

 

例子:

代码

CASE    
     WHEN  (sale_value > 200 )  THEN    
        CALL free_shipping(sale_id);   
         CASE  customer_status   
             WHEN   ' PLATINUM '   THEN    
                CALL apply_discount(sale_id, 20 );   
             WHEN   ' GOLD '   THEN    
                CALL apply_discount(sale_id, 15 );   
             WHEN   ' SILVER '   THEN    
                CALL apply_discount(sale_id, 10 );   
             WHEN   ' BRONZE '   THEN    
                CALL apply_discount(sale_id, 5 );   
         END   CASE ;   
END   CASE ;  

 

CASE与SELECT语句结合的妙用:

SELECT  ( CASE   WHEN  (t.a  =   1   AND  t.b  =   0 )  THEN  t.c  ELSE   ' N/A '   END )  AS  result  FROM  test t  order   by  result  asc   

 

3，迭代
LOOP

[ label: ]  LOOP   
    statements   
END  LOOP  [ label ] ;  

 

REPEAT...UNTIL

[ label: ]  REPEAT   
    statements   
UNTIL expression   
END  REPEAT  [ label ]   

 

WHILE

[ label: ]   WHILE  expression DO   
    statements   
END   WHILE   [ label ]   

 

LEAVE语句

SET  i = 1 ;   
myloop: LOOP   
     SET  i = i + 1 ;   
     IF  i = 10   then    
        LEAVE myloop;   
     END   IF :   
END  LOOP myloop;   
SELECT   ' I can count to 10 ' ;  

 

ITERATE语句

代码

SET  i = 0 ;   
loop1: LOOP   
     SET  i = i + 1 ;   
     IF  i >= 10   THEN                   /* Last number - exit loop */   
        LEAVE loop1;   
    ELSEIF MOD(i,  2 ) = 0   THEN         /* Even number - try again */   
        ITERATE loop1;   
     END   IF ;   
  
     SELECT  CONCAT(i, "  is  an odd  number ");   
END  LOOP loop1;  

 

嵌套循环

代码

DECLARE  i, j  INT   DEFAULT   1 ;   
outer_loop: LOOP   
     SET  j = 1 ;   
    inner_loop: LOOP   
         SELECT  concat(i, " times ", j, "  is  ", i * j);   
         SET  j = j + 1 ;   
         IF  j > 12   THEN    
            LEAVE inner_loop;   
         END   IF ;   
     END  LOOP inner_loop;   
     SET  i = i + 1 ;   
     IF  i > 12   THEN    
        LEAVE outer_loop;   
     END   IF ;   
END  LOOP outer_loop;