mysql存储过程之代码块条件控制迭代.docxVIP

[label:] BEGIN [label:] BEGIN MySQL 存储过程之代码块、条件控制、迭代 1，代码块 代码顺序 Java 代码 1，变量和条件声明2，Cursor 声明3，Handler 声明4，程序代码 1， 变量和条件声明 2， Cursor 声明 3， Handler 声明 4， 程序代码 1， 变量和条件声明 2， Cursor 声明 3， Handler 声明 4，程序代码可以给代码块加 lebel ，这样 END 4， 观，还可以用 LEAVE 语句来终结代码块 : Java 代码 varaiable and condition declarations cursor declarations BEGIN BEGIN handler declarations program code END [label]; [label:] BEGIN varaiable and condition declarations cursor declarations handler declarations program code END [label]; 代码块可以嵌套 : Java 代码 CREATE PROCEDURE nested_blocks() BEGIN DECLARE my_variable varchar(20); SET my_variable=This value was set in the outer block; SET my_variable=This value was set in the inner block; outer_block: BEGIN outer_block: BEGIN END; SELECT my_variable, Changes in the inner block are visible in the outer block; END; 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 的例子 : Java 代码 CREATE PROCEDURE nested_blocks() 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; 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 ，条件控制 CALL apply_discount(sale_id,20); CALL apply_discount(sale_id,20); /* 20% IF: Java 代码 IF expression THEN commands [ELSEIF expression THEN commands] [ELSE commands] END IF; IF expression THEN commands [ELSEIF expression THEN commands] [ELSE commands] END IF; 例子: Java 代码 IF (sale_value gt; 200) THEN /*Free shipping*/CALL free_shipping(sale_id); /*Free shipping*/ IF (customer