一、条件判断语句

A-判断语句-IF语句

1、IF语句的语法结构

IF 表达式1 THEN 操作1 
[ELSEIF 表达式2 THEN 操作2]…… 
[ELSE 操作N] 
END IF

IF val IS NULL THEN
SELECT 'val is null';
ELSE SELECT 'val is not null';
END IF;

2、实战1

声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变

DELIMITER $
CREATE PROCEDURE update_salary_by_eid(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE DEFAULT 0;
    DECLARE hire_year INT DEFAULT 0;
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    SELECT TIMESTAMPDIFF(YEAR, hire_date, NOW()) INTO hire_year FROM employees WHERE employee_id = emp_id;

    IF emp_salary < 8000 AND hire_year > 5
    THEN
        UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
    END IF;
END $
DELIMITER ;

• 调用

CALL update_salary_by_eid(101);

3、实战2

声明存储过程“update_salary_by_eid2”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于9000元并且入职时间超过5年，就涨薪500元；否则就涨薪100元

DELIMITER $
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE;
    DECLARE hire_year INT;

    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    SELECT TIMESTAMPDIFF(YEAR, hire_date, NOW()) INTO hire_year FROM employees WHERE employee_id = emp_id;

    IF emp_salary < 9000 AND hire_year > 5
    THEN
        UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
    ELSE
        UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
    END IF;

END $
DELIMITER ;

• 调用

CALL update_salary_by_eid1(101);

4、实战3

声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且低于10000的，但是奖金
比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元

DELIMITER $
CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE;
    DECLARE emp_commission_pct DOUBLE;

    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    SELECT commission_pct INTO emp_commission_pct FROM employees WHERE employee_id = emp_id;

    IF emp_salary < 9000
    THEN
        UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
    ELSEIF emp_salary < 10000 AND emp_commission_pct IS NULL
    THEN
        UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
    ELSE
        UPDATE employees SET salary = salary + 100;
    END IF;
END $
DELIMITER ;

• 调用

CALL update_salary_by_eid3(106);

B-判断语句-CASE语句

1、CASE语句的语法结构

• 情况一：单值判断，类似于 switch

CASE 表达式 
    WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
    WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) .
.. 
    ELSE 结果n或语句n(如果是语句，需要加分号) 
    END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要）

• eg

CASE val WHEN 1 THEN
SELECT 'val is 1';
WHEN 2 THEN SELECT 'val is 2';
ELSE SELECT 'val is not 1 or 2';
END CASE;

• 情况二：逻辑判断，类似于多重 if

CASE 
    WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
    WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) .
.. 
    ELSE 结果n或语句n(如果是语句，需要加分号) 
    END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要）

• eg

CASE WHEN val IS NULL THEN
SELECT 'val is null';
WHEN val < 0 THEN SELECT 'val is less than 0';
WHEN val > 0 THEN SELECT 'val is greater than 0';
ELSE SELECT 'val is 0';
END CASE;

2、实战1

声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例
为NULL的，就更新奖金比例为0.01；其他的涨薪100元

DELIMITER $
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE;
    DECLARE emp_pct DECIMAL;

    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    SELECT commission_pct INTO emp_pct FROM employees WHERE employee_id = emp_id;

    CASE
        WHEN emp_salary < 9000
            THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
        WHEN emp_salary < 10000 AND emp_pct IS NULL
            THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
        ELSE UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
        END CASE;
END $
DELIMITER ;

• 调用

CALL update_salary_by_eid4(108);

3、实战2

DELIMITER $
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
    DECLARE emp_sal DOUBLE;
    DECLARE hire_year DOUBLE;
    SELECT salary
    INTO emp_sal
    FROM employees
    WHERE employee_id = emp_id;
    SELECT ROUND(DATEDIFF(CURDATE(), hire_date) / 365)
    INTO hire_year
    FROM employees
    WHERE employee_id = emp_id;
    CASE hire_year
        WHEN 0 THEN UPDATE employees SET salary=salary + 50 WHERE employee_id = emp_id;
        WHEN 1 THEN UPDATE employees SET salary=salary + 100 WHERE employee_id = emp_id;
        WHEN 2 THEN UPDATE employees SET salary=salary + 200 WHERE employee_id = emp_id;
        WHEN 3 THEN UPDATE employees SET salary=salary + 300 WHERE employee_id = emp_id;
        WHEN 4 THEN UPDATE employees SET salary=salary + 400 WHERE employee_id = emp_id;
        ELSE UPDATE employees SET salary=salary + 500 WHERE employee_id = emp_id;
        END CASE;
END $
DELIMITER ;