#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);
4.1分支结构之 if#3.1分支结构之 if#举例1DELIMITER//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 PROCED