#3.2 分支结构之CASE
#举例
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
#演示1:case...when...then...
/*
declare var int default 2;
case var
when 1 then select 'var=1';
when 2 then select 'var=2';
when 3 then select 'var=3';
else select'other value';
end case;
*/
#演示2:case when ... then...
DECLARE var1 INT DEFAULT 10;
CASE
WHEN var1>=100 THEN SELECT '三位数';
WHEN var1>=10 THEN SELECT'两位数';
ELSE SELECT '个位数';
END CASE;
END//
DELIMITER;
#调用
CALL test_case();
DROP PROCEDURE test_case;
#举例3:举例3:声明存储过程“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_sal DOUBLE;#记录员工工资
DECLARE bonus DOUBLE;#记录奖金
#局部变量赋值
SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id=emp_id;
CASE
WHEN emp_sal<9000 THEN UPDATE employees SET salary=9000 WHERE employee_id=emp_id;
WHEN emp_sal<10000 AND bonus 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(105);
SELECT * FROM employees
WHERE employee_id IN(103,104,105);
#举例4:声明存储过程update_salary_by_eid5,定义IN参数emp_id,输入员工编号。
#判断该员工的入职年限,如果是0年,薪资涨50;如果是1年,薪资涨100;如果是2年,薪资涨200;
#如果是3年,薪资涨300;如果是4年,薪资涨400;其他的涨薪500。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
#局部变量声明
DECLARE hire_year DOUBLE;#记录员工入职公司的总时间(单位:年)
#赋值
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 ;
#调用
CALL update_salary_by_eid5(101);
SELECT employee_id,salary,ROUND(DATEDIFF(CURDATE(),hire_date)/365)
FROM employees ;
DROP PROCEDURE update_salary_by_eid5;
4.2分支结构之case#3.2 分支结构之CASE#举例DELIMITER //CREATE PROCEDURE test_case()BEGIN #演示1:case...when...then... /* declare var int default 2; case var when 1 then select 'var=1'; when 2 then select 'var=2'; when 3 then select 'var=3'; else selec