以员工号为参数,返回该员工所在部门的平均工资
create or replace function fun_sal(p_empno emp.empno%type)
return emp.sal%type
as v_sal emp.sal%type;
begin
select avg(sal) into v_sal from emp where deptno=
(select deptno from emp where empno=p_empno);
return v_sal;
end
begin
dbms_output.put_line (fun_sal (7844));
end;
扩展资料
例1:创建一个存储过程,以员工号为参数,输出该员工的工资。
create or replace procedure showsal(p_empno emp.empno%type) as v_sal emp.sal%type; begin
select sal into v_sal from emp where empno=p_empno; dbms_output.put_line(v_sal); end; begin showsal(7844); end;
例2:创建一个函数,以部门号为参数,返回该部门的平均工资;
create or replace function fun_avgsal(p_deptno emp.deptno%type) return emp.sal%type as v_sal emp.sal%type; begin
select avg(sal) into v_sal from emp where deptno=p_deptno; return v_sal; end; begin dbms_output.put_line (fun_avgsal(10)); end;
创建一个函数,以部门号为参数,返回该部门的平均工资。
create or replace function f_sxt6(v_deptno emp.deptno%type) return emp.sal%type is
vr_sal emp.sal%type;
begin
select avg(sal) into vr_sal from emp where deptno = v_deptno;
return vr_sal;
end;
执行
select f_sxt6(20) 部门平均工资 from dual;
创建一个函数,以员工号为参数,返回该员工所在的部门的平均工资。
create or replace function f_sxt7(v_empno emp.empno%type) return emp.sal%type is
vr_sal emp.sal%type;
begin
select avg(sal) into vr_sal from emp where deptno = (select deptno from emp where empno = v_empno);
return vr_sal;
end;
执行:
select f_sxt7(7369) from dual;
本回答被网友采纳