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
<]
[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 =1AND 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];
[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=10then
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>=10THEN/*Last number - exit loop*/
LEAVE loop1;
ELSEIF MOD(i, 2)=0THEN/*Even number - try again*/        ITERATE loop1;
END IF;
SELECT CONCAT(i, " is an odd number");
mysql存储过程题目END LOOP loop1;
嵌套循环
代码
DECLARE i, j INT DEFAULT1;
outer_loop: LOOP
SET j=1;
inner_loop: LOOP
SELECT concat(i, " times ", j, " is ", i*j);
SET j=j+1;
IF j>12THEN
LEAVE inner_loop;
END IF;
END LOOP inner_loop;
SET i=i+1;
IF i>12THEN
LEAVE outer_loop;
END IF;
END LOOP outer_loop;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。