IF 语句
sh
# 准备工作:
# 1.导入atguigudb数据库
# 2.建立dbtest08数据库
CREATE DATABASE dbtest08 CHARACTER SET utf8;
# 3.切换当前数据库为dbtest08
USE dbtest08;
# 4.建立表
CREATE TABLE emps
AS
SELECT * FROM atguigudb.employees;
CREATE TABLE depts
AS
SELECT * FROM atguigudb.departments;
SELECT * FROM emps;
SELECT * FROM depts;
# ----------IF语句---------
# 例1:编写一个过程get_absolution,求一个数的绝对值,并用IN参数num接收输入的数,用OUT参数res输出该数的结果。
DELIMITER //
CREATE PROCEDURE get_absolution(IN num DOUBLE, OUT res DOUBLE)
BEGIN
IF num >= 0 THEN
SET res = num;
ELSE
SET res = -num;
END IF;
END //
DELIMITER ;
CALL get_absolution(-3.14, @res);
SELECT @res;
# 例2:编写一个过程get_bigger_number,求三个整数的最大值,并用IN参数num1,num2,num3接收输入的三个整数,用OUT参数res输出三个数的最大值。
DELIMITER //
CREATE PROCEDURE get_bigger_number(IN num1 INT, IN num2 INT, IN num3 INT, OUT res INT)
BEGIN
SET res = num1;
IF res < num2 THEN
SET res = num2;
END IF;
IF res < num3 THEN
SET res = num3;
END IF;
END //
DELIMITER ;
CALL get_bigger_number(32, 5, 8, @res);
SELECT @res;
# 例3:编写一个过程get_grade,实现以下功能,并用IN参数score接收输入的成绩,用OUT参数grade输出该成绩的等级。
90~100 优秀
80~89 良好
70~79 中等
60~69 及格
0~59 不及格
DELIMITER //
CREATE PROCEDURE get_grade(IN score FLOAT, OUT grade VARCHAR(5))
BEGIN
IF score >= 90 THEN
SET grade = '优秀';
ELSEIF score >= 80 THEN
SET grade = '良好';
ELSEIF score >= 70 THEN
SET grade = '中等';
ELSEIF score >= 60 THEN
SET grade = '及格';
ELSE
SET grade = '不及格';
END IF;
END //
DELIMITER ;
CALL get_grade(20, @res);
SELECT @res;
# 例4:声明存储过程“update_salary_by_eid1”,定义IN参数emp_id,输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年,就涨薪500元;否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
DECLARE emp_sal, work_years DOUBLE;
#根据员工号得到员工的工资emp_sal和入职年限work_years
SELECT salary, DATEDIFF(CURDATE(), hire_date) / 365 INTO emp_sal, work_years
FROM emps
WHERE employee_id = emp_id;
#如果emp_sal低于8000元并且入职时间work_years超过5年,就涨薪500元;否则就不变
IF emp_sal < 8000 AND work_years > 5 THEN
UPDATE emps SET salary = salary + 500 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
SELECT * FROM emps WHERE employee_id = 104;
CALL update_salary_by_eid1(104);
SELECT * FROM emps WHERE employee_id = 104;
SELECT * FROM emps WHERE employee_id = 100;
CALL update_salary_by_eid1(100);
SELECT * FROM emps WHERE employee_id = 100;
# 例5:声明存储过程“update_salary_by_eid2”,定义IN参数emp_id,输入员工编号。判断该员工薪资如果低于9000元并且入职时间超过5年,就涨薪500元;否则就涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT)
BEGIN
DECLARE emp_sal, work_years DOUBLE;
#根据员工号得到员工的工资emp_sal和入职年限work_years
SELECT salary, DATEDIFF(CURDATE(), hire_date) / 365 INTO emp_sal, work_years
FROM emps
WHERE employee_id = emp_id;
#如果emp_sal低于9000元并且入职时间work_years超过5年,就涨薪500元;否则就涨薪100元
IF emp_sal < 9000 AND work_years > 5 THEN
UPDATE emps SET salary = salary + 500 WHERE employee_id = emp_id;
ELSE
UPDATE emps SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
SELECT * FROM emps WHERE employee_id = 104;
CALL update_salary_by_eid2(104);
SELECT * FROM emps WHERE employee_id = 104;
SELECT * FROM emps WHERE employee_id = 100;
CALL update_salary_by_eid2(100);
SELECT * FROM emps WHERE employee_id = 100;
# 例6:声明存储过程“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_sal, emp_comm DOUBLE;
#根据员工号得到员工的工资emp_sal,员工的奖金emp_comm
SELECT salary, commission_pct INTO emp_sal, emp_comm
FROM emps
WHERE employee_id = emp_id;
#判断该员工薪资如果低于9000元,就更新薪资为9000元;薪资如果大于等于9000元且低于10000的,但是奖金
#比例为NULL的,就更新奖金比例为0.01;其他的涨薪100元
IF emp_sal < 9000 THEN
UPDATE emps SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_sal < 10000 AND emp_comm IS NULL THEN
UPDATE emps SET commission_pct = 0.01 WHERE employee_id = emp_id;
ELSE
UPDATE emps SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
SELECT * FROM emps WHERE employee_id = 102;
CALL update_salary_by_eid3(102);
SELECT * FROM emps WHERE employee_id = 102;
SELECT * FROM emps WHERE employee_id = 104;
CALL update_salary_by_eid3(104);
SELECT * FROM emps WHERE employee_id = 104;
# 例7:创建存储过程test_if_pro(),传入工资值,如果工资值<3000,则删除工资为此值的员工,如果3000 <= 工资值 <= 5000,则修改此工资值的员工薪资涨1000,否则涨工资500
DELIMITER //
CREATE PROCEDURE test_if_pro(IN emp_sal DOUBLE)
BEGIN
IF emp_sal < 3000 THEN
DELETE FROM emps WHERE salary = emp_sal;
ELSEIF emp_sal <= 5000 THEN
UPDATE emps SET salary = salary + 1000 WHERE salary = emp_sal;
ELSE
UPDATE emps SET salary = salary + 500 WHERE salary = emp_sal;
END IF;
END //
DELIMITER ;
SELECT * FROM emps WHERE employee_id = 150;
CALL test_if_pro(10000);
SELECT * FROM emps WHERE employee_id = 150;