P86 分支结构if的使用

4.1分支结构之 if

#3.1分支结构之 if

#举例1

DELIMITER//

CREATE PROCEDURE test_if()

BEGIN
	#声明局部变量
	DECLARE stu_name VARCHAR(15);
	
	IF stu_name IS NULL
		THEN SELECT'stu_name is null';
	END IF;
END//

DELIMITER;

#调用
CALL test_if();

#举例2：
DELIMITER//

CREATE PROCEDURE test_if1()

BEGIN
	#声明局部变量
	DECLARE email VARCHAR(25) DEFAULT 'aaa';
	
	IF email IS NULL
		THEN SELECT'email is null';
	ELSE 
		SELECT'email is not null';
	END IF;
END//

DELIMITER;

#调用
CALL test_if1();

DROP PROCEDURE test_if1;

#举例3：多选一

DELIMITER//

CREATE PROCEDURE test_if2()

BEGIN
	DECLARE age INT DEFAULT 20;
	
	IF age>40
		THEN SELECT'中老年';
	ELSEIF age>18
		THEN SELECT'青壮年';
	ELSEIF age>8
		THEN SELECT'青少年';
	ELSE
		SELECT'婴幼儿';
	END IF;
END//

DELIMITER;

#调用
CALL test_if2();

DROP PROCEDURE test_if2;

#举例2：声明存储过程“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 DOUBLE;
	DECLARE hire_year DOUBLE;
	
	#赋值
	SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
	
	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id=emp_id;
	
	#判断
	IF emp_sal < 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_eid1(104);

SELECT DATEDIFF(CURDATE(),hire_date)/365,employee_id,salary
FROM employees
WHERE salary<8000 AND DATEDIFF(CURDATE(),hire_date)/365>=5;

#举例3：声明存储过程“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 DOUBLE;
	DECLARE hire_year DOUBLE;
	
	#赋值
	SELECT salary INTO emp_sal FROM employees WHERE employee_id=emp_id;
	
	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id=emp_id;
	
	#判断
	IF emp_sal < 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_eid2(103);

#举例4：声明存储过程“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 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;
	
	#判断
	IF emp_sal<9000
		THEN UPDATE employees SET salary=9000 WHERE employee_id=emp_id;
	ELSEIF 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 IF;
END//

DELIMITER ;

#调用
CALL update_salary_by_eid3(102);
CALL update_salary_by_eid3(103);
CALL update_salary_by_eid3(104);

SELECT * 
FROM employees
WHERE employee_id IN(102,103,104);