【Oracle 自定义函数】

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

语法

提示：Oracle 自定义函数语法如下：

    CREATE OR REPLACE FUNCTION 函数名(参数1[IN] 数据类型，参数2 [IN]数据类型……)
    RETURN 返回的数据类型  --不要带精度

    IS|AS ---IS 或者 AS 随便写一个都可以
       声明变量
    BEGIN
       函数的具体逻辑; 
    RETURN 声明变量;--里面必须要有一个RETURN子句
    ---异常处理
     EXCEPTION WHEN OTHERS THEN 
        ROLLBACK;---如果涉及到了对数据的 增 删 改
       DBMS_OUTPUT.put_line(SQLERRM); --将报错信息打印
    END;

• 参数的命名规则入参 是以 P_ 开头。
• 编译就是将写好的代码放在数据库里某个文件里存着，调用函数的时候，就会到数据库里执行存放的函数逻辑。

一、创建自定义 求和函数

步骤一：创建函数

示例：计算数字 1-N 的N项和。

create or replace function fun_sum(P_N number)
return number  --声明返回数据类型
is --as
s number:=0; --声明变量
begin
  for i in 1..N loop
    s:=s+i;
  end loop;
return s;  --返回所声明的变量

exception     --异常处理，出错则事务回滚
  when others then
    rollback;
end;

步骤二：函数调用

1. 用select 语句调用

语法：select 函数名(参数) from dual;

a 传参方式一：

参数直传
select fun_sum(100) from dual;

b 传参方式二：

使用 => 方式对参数进行赋值
select fun_sum(P_N=>100) from dual;

c 传参方式三：

通过弹窗的方式，动态输入参数值
select fun_sum(P_N=>&数字) from dual;

2. 用匿名块方式调用

declare  --声明部分
s number; --声明变量用以保存函数返回值
begin
  s:=fun_sum(100);
  dbms_output.put_line(s);  --变量方式保存
  dbms_output.put_line(fun_sum(100)); --直接调用输出
end;

二、创建一个自定义函数，输入员工编号，返回员工姓名

步骤一：创建函数

create or replace function select_ename(p_empno number)
return varchar2  --声明返回数据类型
is --as
emp_name varchar2(30);
begin
  select ename into emp_name from emp where empno = p_empno;
return emp_name;

exception     --异常处理，出错则事务回滚
  when others then
    rollback;
end;

步骤二：调用函数

--调用方式1
select select_ename(7499) from dual;
select select_ename(p_empno=>&员工编号) from dual;
--调用方式2
begin
  dbms_output.put_line('员工姓名： '||select_ename(&员工编号));
end;

三、创建一个函数，功能是根据传来的员工编号，返回这个员工所在部门的平均工资

步骤一：创建函数

create or replace function fun_dept_avg(p_empno number)
return varchar2  --声明返回数据类型
is --as
dept_avg number;
begin
  select round(avg(sal),2) into dept_avg from emp
  where deptno = (select deptno from emp where empno = p_empno);
return dept_avg;

exception  --异常处理，出错则事务回滚
  when others then
    rollback;
end;

步骤二：调用函数

--调用方式1
select fun_dept_avg(7499) from dual;
select fun_dept_avg(p_empno=>&员工编号) from dual;
--调用方式2
begin
  dbms_output.put_line('员工姓名： '||fun_dept_avg(&员工编号));
end;