oracle数据库sql语句11 函数

--函数
set serveroutput on;


--显示错误
show error;


--根据部门编号，输出该部门最大的工资
create or replace function fun_maxsal(dno in emp.deptno%type)
return emp.sal%type
as
maxsal emp.sal%type;
begin
select max(sal) into maxsal from emp where deptno=dno;
return maxsal;
end;
/


--执行
select * from emp where sal < fun_maxsal(10);






--根据部门编号，返回部门平均工资 ,参数返回最高工资，最低工资，
create or replace function fun_sal(dno in emp.deptno%type, maxsal out emp.sal%type, minsal out emp.sal%type)
return emp.sal%type
as
avgsal emp.sal%type;
begin
select max(sal),min(sal),avg(sal) into maxsal, minsal, avgsal from emp where deptno=dno;
return avgsal;
end;
/


--执行
declare
avgsal emp.sal%type;
maxsal emp.sal%type;
minsal emp.sal%type;
begin
avgsal:=fun_sal(10, maxsal, minsal);
dbms_output.put_line('avgsal:'||avgsal);
dbms_output.put_line('maxsal:'||maxsal);
dbms_output.put_line('minsal:'||minsal);
end;
/