在已给的数据库rj01上创建存储过程 存储函数和触发器

Question

1，创建存储过程，返回某员工所在部门的编号和工资。
2，创建存储函数，返回某员工所在部门的人数。
3，创建触发器，在删除某员工的信息之后，将删除的记录插入到另一张数据表。

Answer 1

第一题
CREATE PROCEDURE get_employee_department_info(  
   IN employee_name VARCHAR(50),  
   OUT department_id INT,  
   OUT department_salary DECIMAL(10, 2)  
)
BEGIN  
   SELECT department_id, department_salary  
   FROM employees  
   WHERE employee_name = employee_name;  
   UPDATE employees SET department_id = department_id + 1 WHERE employee_name = employee_name;  
END;  
第二题
CREATE FUNCTION get_department_count(  
   IN employee_name VARCHAR(50),  
   OUT count INT  
)
BEGIN  
   SELECT COUNT(*)  
   FROM departments  
   WHERE department_name = employee_name;  
   END;  
第三题
CREATE TRIGGER delete_employee_info_insert_into_another_table  
AFTER DELETE ON employees  
FOR EACH ROW  
BEGIN  
   INSERT INTO another_table (employee_name, department_id, salary)  
   VALUES (old.employee_name, old.department_id, old.salary);  
END;