Oracle 自定义函数

语法结构

CREATE [OR REPLACE] FUNCTION '定义的函数名称'('参数名1' '参数类型','参数名2' '参数类型', ...) RETURN '返回值类型'
AS/IS
返回值形参 形参类型实例化
BEGIN
方法体
（其中用到if判断的话，每一个if对应一个end if，出现几次if就会有几个end if;)
RETURN (接收过实参的)返回值形参
[EXCEPTION '异常处理部分']
END;

以SCOTT账户中的emp和dept两张表为例：

EMP表：

DEPT表：

例一：统计薪资大于等于3000的员工人数。

要求输出格式：人数为1时，输出'有一位员工薪资大于3000'；不止1人时，输出'有...位员工薪资大于3000'；一个也没有就输出'没有员工薪资大于3000'。

create or replace function empSal return varchar2
as
    result varchar2(32);
    temp number;
        begin
            select count(sal) count into temp from SCOTT.emp e where e.sal>=3000;
            if temp=1 then
                result:='有一位员工薪资大于3000';
                else if temp>1 then
                    result:='有'||temp||'位员工薪资大于3000';
                    else result:='没有员工薪资大于3000';
                end if;
            end if;
            return result;
        end;
    select empSal() from dual;

查询结果： 

例二：自定义代参函数，参数为部门名称，统计输入部门员工人数。

create or replace function depA2(dep varchar2) return number
as
    result number;
        begin
select t.count into result from (select count(e.deptno) count from SCOTT.dept d left join SCOTT.emp e on d.deptno=e.deptno where d.dname=dep) t;
            return result;
        end;
    select depA2('SALES') from dual;

查询结果：

例三：自定义代参函数，参数为部门名称，统计输入部门薪资大于等于3000的员工人数。

要求输出格式：人数为1时，输出'有一位员工薪资大于3000'；不止1人时，输出'有...位员工薪资大于3000'；一个也没有就输出'没有员工薪资大于3000'。

create or replace function highSaleAmount(dept varchar2) return varchar2
as
    result varchar2(64);
    temp number;
    amount number;
        begin
        select count(dname) into temp from SCOTT.dept d where d.dname in(dept);
        select count(sal) into amount from scott.emp e left join SCOTT.dept d on d.deptno=e.deptno where d.dname=dept and sal>=3000;
        if temp!=0 then
            if amount=1 then result:='该部门有一位员工薪资大于3000';
            else if amount>1 then result:='有'||amount||'位员工薪资大于3000';
            else result:='该部门没有人薪资大于3000';
        end if;
        end if;
            else
        result:='没有这个部门';
        end if;
            return result;
        end;
    select highSaleAmount('ACCOUNTING') from dual;

查询结果：