createorreplacefunction davg(dno emp.deptno%type)return number
is
f_avg emp.sal%type;beginselectavg(sal)into f_avg from emp where deptno=dno;return f_avg;end;declarebegin
dbms_output.put_line(davg(10));end;
写一个函数,传入员工编号,返回所在部门名称
createorreplacefunction redname(eno emp.empno%type)return varchar2
is
dname dept.dname%type;beginselect dname into dname from dept,emp where dept.deptno=emp.deptno and empno=eno;return dname;end;declarebegin
dbms_output.put_line(redname(7788));end;
写一个函数,可以查询某个员工的年收入,包括奖金
createorreplacefunction asal(eno emp.empno%type)return emp.sal%typeis
years emp.sal%type;beginselect(nvl2(comm,sal+comm,sal))*12into years from emp where empno=eno;--nvl()函数:实现空值的转换,其语法格式如下:nvl(a,b); 如果a为null,则返回b;反之,返回a;--nvl2()函数:nvl2(a,b,c); 如果a为null,则返回c;若不为null,则返回breturn years;end;--select * from emp;declare
v_empno emp.empno%type:=&aa;begin
dbms_output.put_line(v_empno||'的年收入是'||asal(v_empno));end;
定义函数,输入部门编号,查询出该部门的员工总数。
createorreplacefunction ac(dno emp.deptno%type)return number
is
dc number;beginselectcount(empno)into dc from emp where deptno=dno;return dc;end;declare
v_dno emp.deptno%type:=&aa;begin
dbms_output.put_line(v_dno||'部门的人数是'||ac(v_dno));end;
定义一个函数,用于计算emp表中某个部门的平均工资。create or replace function davg(dno emp.deptno%type) return numberisf_avg emp.sal%type;begin select avg(sal) into f_avg from emp where deptno=dno; return f_avg;end;declare begin dbms_output.put_line(davg(10));end;.