Oracle 函数

1.函数：

函数与存储过程相似，也是数据库中存储的已命名PL/SQL程序块。函数的主要特征是它有且仅有一个返回值。通过return来指定函数的返回类型。在函数的任何地方可以通过return expression语句从函数返回，返回类型必须和声明的返回类型一致。

语法：

create or replace function function_name
[(parameter_list)]
return datatype
{is/as}
[declare_section]
begin
　　executable_statements; --也必须含有return语句
[exception_handlers;]
end;


function_name：函数名称。
parameter_list：函数列表，可选。
return datatype：指定函数的返回类型，不能指定大小。
declare_section：声明部分，用来声明变量，可选。
executable_statements：要执行的PL-SQL语句。
exception_handlers：异常处理，可选。
or repalce：是否覆盖，可选。

实例:

--读入两个值, 返回比较大的值
create or replace function function1(para1 in number, para2 in number)   
return number   
as   
begin  
  if para1 > para2 then  
     return para1;  
  else  
     return para2;   
  end if;  
end function1;  

--法一：
create or replace function sayhello return varcahr2 --varcahr2不能有长度
is
say varchar2(20); --这个地方是可以定义长度的。
begin
	say := 'hello world';
	return say;
end;

--法二：
create or replace function sayhello2 return varcahr2 --varcahr2不能有长度
is
begin
	return 'hello world';
end;

--调用：
select sayhello2 from dual;

2.函数和存储过程的优势：

1、共同使用的代码可以只需要被编写一次，而被需要该代码的任何应用程序调用（.net,c++,java，也可以使DLL库）；简化了应用程序的开发维护，提高了效率和性能。

2、这种模块化的方法使得一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写，因此程序的结构更加清晰，简单，也容易实现。

3、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。而且多个用户在调用同一个存储过程或函数时，只需要加载一次即可。

4、提高数据的安全性和完整性。通过把一些对数据的操作方到存储过程或函数中，就可以通过是否授予用户有执行该语句的权限，来限制某些用户对数据库进行这些操作。

3、函数和存储过程的区别：

1、存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据，函数中不能包含对数据库执行操作的语句，如DML语句（增删改查）。

2、存储过程可以没有返回值，而函数必须要有返回值。

3、sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

4、适用场合：

1、如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数。

2、存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以在SQL内部调用函数来完成复杂的计算问题，但不能调用存储过程。

5.自定义函数：

数据库中函数包含四个部分：声明、返回值、函数体和异常处理。

--没有参数的函数  
create or replace function get_user return varchar2 is  
  v_user varchar2(50);  
begin  
  select username into v_user from user_users;  
  return v_user;  
end get_user;  

--带有IN参数的函数  
create or replace function get_empname(v_id in number) return varchar2 as  
  v_name varchar2(50);  
begin  
  select name into v_name from employee where id = v_id;  
  return v_name;  
exception  
   when no_data_found then  
   raise_application_error(-20001, '你输入的ID无效！');  
end get_empname;  

6.函数调用：

--1.在SQL语句中直接调用 get_avgsal()、get_user是函数
select deptno,get_avgsal(10) from emp where deptno=10;
select get_avgsal(10) 平均值　from dual;
select get_user from dual;  

--2.使用输出语句调用函数
set serveroutput on
BEGIN
	  dbms_output.put_line(avgsal(10));
END ;

7.删除函数： 

drop function get_empname;

8.实例： 

1.定义一个函数，用于计算emp表中某个部门的平均工资

create or replace function get_avgsal(dno in emp.deptno%type) 
return number
is
	f_avgsal emp.sal%type;
begin
    select avg(sal) into f_avgsal from emp where deptno=dno;
    return f_avgsal;
end;
/
begin
    dbms_output.put_line(get_avgsal(10));
end;
/
select get_avgsal(10) from dual;

2. 写一个函数，传入员工编号，返回所在部门名称

create or replace function d_name(eno emp.empno%type)
return varchar2
is
    f_name dept.dname%type;
begin
    select dname into f_name from dept where deptno = (select deptno from emp where empno=eno);
    return f_name;
end;

select d_name(7369) from dual;

3.定义函数，输入部门编号，查询出该部门的员工总数。

create or replace function emp_sum(dno emp.deptno%type)
return number
is
	total number;
begin
    select count(*) into total from emp group by deptno having deptno=dno;
    return total;
end;
/
begin
    dbms_output.put_line('该部门总人数为：' || emp_sum(&a));
end;

4.定义函数，使用记录类型作为返回类型，根据指定的部门号返回其对应的部门信息

--方案一：
create or replace function s_dept(dno dept.deptno%type)
return dept%rowtype
is
d_message dept%rowtype;
begin
select * into d_message from dept where deptno=dno;
return d_message;
end;

declare
    v_no dept.deptno%type := &a;
begin
dbms_output.put_line(s_dept(v_no).deptno || '     ' || s_dept(v_no).dname || '     ' || s_dept(v_no).loc);
end;

--方案二：
create or replace function s_dept(dno in dept.deptno%type) return dept%rowtype
is
    d_message dept%rowtype;
begin
    select * into d_message from dept where deptno=dno; 
    return d_message;
end;
declare
    d_no dept.deptno%type:=&a;
    d_mess dept%rowtype:=s_dept(d_no);
begin
    dbms_output.put_line(d_mess.deptno||'   '||d_mess.dname||'    '||d_mess.loc);
end;

 参考：

1.Oracle 自定义函数_风神修罗使的博客-CSDN博客

2.oracle 函数的使用_爱睡觉的小馨的博客-CSDN博客_oracle函数调用