oracle练习2024.08.15（2）

--10.创建一个名为‘FUNC_DEPT_MAXSAL’的函数 ，以部门编号为参数，返回该部门最高工资：
create or replace function FUNC_DEPT_MAXSAL (
    p_dept_id in employees.department_id%type
) return employees.salary%type
is
    v_maxs employees.salary%type;
begin
    select max(salary)
    into v_maxs
    from employees
    where department_id = p_dept_id;
    return v_maxs;
exception
    when no_data_found then
        return null;
    when others then
        raise;
end;
select FUNC_EMP_DEPT_AVGSAL(101) from dual 
--11.创建名为‘FUNC_EMP_SALARY’的函数，以员工编号为参数，返回员工的工资：
create or replace function FUNC_EMP_SALARY (
    p_emp_id in employees.employee_id%type
) return employees.salary%type
is
    v_s employees.salary%type;
begin
    select salary
    into v_s
    from employees
    where employee_id = p_emp_id;
    return v_s;
exception
    when no_data_found then
        return null;
    when others then
        raise;
end;
select * from employees
--12.创建名为‘FUNC_EMP_DEPT_AVGSAL’的函数，以员工编号为参数，返回该员工所在部门的平均工资：
create or replace function FUNC_EMP_DEPT_AVGSAL (
    p_emp_id in employees.employee_id%type
) return employees.salary%type
is
    v_s employees.salary%type;
begin
    select avg(salary)
    into v_s
    from employees
    where department_id = (select department_id from employees where employee_id=p_emp_id);
    return v_s;
exception
    when no_data_found then
        return null;
    when others then
        raise;
end;