【Oracle】函数

函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

语法

create [or replace] function func_name[(parameter1,[,parameter2]...)] return data_type is 
[inner_variable]
begin
  plsql_sentence;
[exception]
  [dowith_sentences;]
end [func_name];

参数说明：

func_name:函数名称，如果数据库中已经存在此名称，则可以指定[or replace]关键字，这样新的函数将覆盖原来的函数

parameter1:函数的参数，这个时可选项，因为函数可以没有参数

data_type:函数的返回值类型，这个是必选项，在返回值类型的前面要使用return关键字来说明

inner_variable:函数的内部变量，它与函数的参数不一样

plsql_sentence：PL/SQL语句，它是函数主要功能的实现部分，函数的主体

dowith_sentences:异常处理代码

 

环境准备

现在数据库中存在如下表数据TMP002

日期	编号	姓名	工资
20171231	CST001	侯亮平	100000
20171231	CST002	陆亦可	12000
20180101	CST001	侯亮平	102000
20180101	CST002	陆亦可	13000
20180101	CST003	祁同伟	320000

 

数据准备脚本

create table TMP002
(
  dtdate varchar2(10) not null,
  cst_no varchar2(10)not null,
  cst_name varchar2(100),
  sal number,
 constraint pk_tmp002 primary key(dtdate,cst_no)
);
insert into tmp002 values('20171231','CST001','侯亮平','100000');
insert into tmp002 values('20171231','CST002','陆亦可','12000');
insert into tmp002 values('20180101','CST001','侯亮平','102000');
insert into tmp002 values('20180101','CST002','陆亦可','13000');
insert into tmp002 values('20180101','CST003','祁同伟','320000');
COMMIT;

 

案例

案例一:创建函数求出20180101这天的平均工资

create or replace function func_get_avg_sal(v_dt varchar2) 
return number is
num_avg_sal number;
begin
  select avg(sal) into num_avg_sal from tmp002 where dtdate=v_dt;
  return(round(num_avg_sal,2));
  exception 
    when no_data_found then 
      dbms_output.put_line('日期不存在！');
      return(0);
end func_get_avg_sal;

调用函数

方法一：使用PL/SQL块

declare 
  avg_sal number;
begin
  avg_sal:=func_get_avg_sal('20180101');
  dbms_output.put_line('平均工资为:'||avg_sal);
end;

调用结果：

方法二：借助dual表

select func_get_avg_sal('20180101') avg_sal from dual;