1,代码块
代码顺序
1
,变量和条件声明
2 ,Cursor声明
3 ,Handler声明
4 ,程序代码
2 ,Cursor声明
3 ,Handler声明
4 ,程序代码
可以给代码块加lebel,这样END匹配比较直观,还可以用LEAVE语句来终结代码块:
[
label:
]
BEGIN
varaiable and condition declarations
cursor declarations
handler declarations
program code
END [ label ] ;
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 ;
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;
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 ;
[ 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 ;
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 ;
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 ;
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 ] ;
statements
END LOOP [ label ] ;
REPEAT...UNTIL
[
label:
]
REPEAT
statements
UNTIL expression
END REPEAT [ label ]
statements
UNTIL expression
END REPEAT [ label ]
WHILE
[
label:
]
WHILE
expression DO
statements
END WHILE [ label ]
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 ' ;
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;
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;
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;