## 存储过程 - 流程控制
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();
