输入的部门编号(idepartment_id )，返回该id对应的部门名称和部门平均工资(oavgsalary)

已知工资计算公式为：工资=基本工资+职级工资+职级绩效
根据tbl_employees、tbl_departments和tbl_rank_salary创建存储过程：salarybydepartment(IN idepartment_id CHAR(6), OUT odepartment VARCHAR(20), OUT oavgsalary DECIMAL(10,2))
要求：根据输入的部门编号(idepartment_id )，返回该id对应的部门名称和部门平均工资(oavgsalary)。
测试数据：查询IN参数值110002的信息，OUT参数的返回值分别为经管学院 9500.00。

MySQL数据库创建存储过程如下，在创建存储过程时，需要使用不同的语句分隔符以区分普通SQL语句：

DELIMITER $$
CREATE PROCEDURE salarybydepartment(
    IN idepartment_id CHAR(6),
    OUT odepartment VARCHAR(20),
    OUT oavgsalary DECIMAL(10,2)
)
BEGIN
    -- 声明变量
    DECLARE total_salary DECIMAL(10,2);
    DECLARE total_employees INT;
    -- 获取部门名称
    SELECT department INTO odepartment
    FROM tbl_departments
    WHERE department_id = idepartment_id;
    -- 计算总工资
    SELECT SUM(base_salary + rank_salary + performance_pay) INTO total_salary
    FROM tbl_employees
    INNER JOIN tbl_rank_salary ON tbl_employees.rank_id = tbl_rank_salary.rank_id
    WHERE tbl_employees.department_id = idepartment_id;
    -- 计算员工数量
    SELECT COUNT(*) INTO total_employees
    FROM tbl_employees
    WHERE department_id = idepartment_id;
    -- 计算平均工资
    SET oavgsalary = total_salary / total_employees;
END$$
DELIMITER ;

测试：

SET @department_id = '110002';
CALL salarybydepartment(@department_id, @department, @avgsalary);
SELECT @department, @avgsalary;