## 存儲過程 - 流程控制
IF 語句，語法如下：
IF expression THEN
statements;
END IF;
IF ELSE 語句，語法如下：
IF expression THEN
statements;
ELSE
else-statements;
END IF;
IF ELSEIF ELSE 語句，語法如下：
IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF;
根據客戶編號獲取客戶級別：
```
DELIMITER $$
DROP PROCEDURE IF EXISTS get_customer_level$$
CREATE PROCEDURE get_customer_level(
IN p_customer_id int(11),
OUT p_customer_level varchar(10) charset utf8
)
BEGIN
DECLARE p_credit_limit int;
SELECT credit_limit INTO p_credit_limit FROM customer WHERE customer_id = p_customer_id;
IF p_credit_limit > 50000 THEN
SET p_customer_level = '黃金';
ELSEIF (p_credit_limit <= 50000 AND p_credit_limit >= 10000) THEN
SET p_customer_level = '白銀';
ELSE
SET p_customer_level = '青銅';
END IF;
END$$
DELIMITER ;
```
調用存儲過程：
set @p_customer_id = 1001;
call get_customer_level(@p_customer_id, @p_customer_level);
select @p_customer_id, @p_customer_level;
簡單 CASE 語句，語法如下：
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE;
獲取客戶貨運時間：
```
DELIMITER $$
DROP PROCEDURE IF EXISTS get_customer_shipping$$
CREATE PROCEDURE get_customer_shipping(
IN p_customer_id int(11),
OUT p_shipping varchar(30) charset utf8
)
BEGIN
DECLARE p_province varchar(15) charset utf8;
SELECT province INTO p_province FROM customer WHERE customer_id = p_customer_id;
CASE p_province
WHEN '廣東' THEN
SET p_shipping = '2天貨運時間';
WHEN '上海' THEN
SET p_shipping = '3天貨運時間';
ELSE
SET p_shipping = '5天貨運時間';
END CASE;
END$$
DELIMITER ;
```
調用存儲過程：
SET @p_customer_id = 1020;
SELECT province into @p_province FROM customer WHERE customer_id = @p_customer_id;
call get_customer_shipping(@p_customer_id, @p_shipping);
SELECT @p_customer_id AS Customer, @p_province AS Province, @p_shipping AS Shipping;
搜索 CASE 語句，語法如下：
CASE
WHEN condition_1 THEN commands
WHEN condition_2 THEN commands
...
ELSE commands
END CASE;
根據客戶編號獲取客戶級別：
```
DELIMITER $$
DROP PROCEDURE IF EXISTS get_customer_level2$$
CREATE PROCEDURE get_customer_level2 (
IN p_customer_id int(11),
OUT p_customer_level varchar(10) charset utf8
)
BEGIN
DECLARE p_credit_limit int;
SELECT credit_limit INTO p_credit_limit FROM customer WHERE customer_id = p_customer_id;
CASE
WHEN p_credit_limit > 50000 THEN
SET p_customer_level = '黃金';
WHEN (p_credit_limit <= 50000 AND p_credit_limit >= 10000) THEN
SET p_customer_level = '白銀';
ELSE
SET p_customer_level = '青銅';
END CASE;
END$$
DELIMITER ;
```
調用存儲過程：
set @p_customer_id = 1001;
call get_customer_level2(@p_customer_id, @p_customer_level);
select @p_customer_id, @p_customer_level;
WHILE 語句，語法如下：
WHILE expression DO
statements
END WHILE
WHILE 循環示例：
```
DELIMITER $$
DROP PROCEDURE IF EXISTS mysql_while_loop$$
CREATE PROCEDURE mysql_while_loop()
BEGIN
DECLARE x int;
DECLARE str varchar(255);
SET x = 1;
SET str = '';
WHILE x <= 10 DO
SET str = CONCAT(str, x, ',');
SET x = x + 1;
END WHILE;
SELECT str;
END$$
DELIMITER ;
```
調用存儲過程：
call mysql_while_loop();
