Oracle 19C入门到精通之函数

函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。函数的调用是表达式的一部分，而过程的调用是一条PL/SQL语句。函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用，只不过调用函数时要用表达式，而不像过程只需要调用过程名。另外，函数必须要有一个返回值，过程则没有。

1. 创建函数

函数的创建语法与存储过程比较类似，也是一种存储在数据库中的命名程序块。函数可以接收零或多个输入参数，并且必须有返回值（这一点存储过程是没有的）。定义函数的语法格式如下：

CREATE [OR REPLACE] FUNCTION fun_name[(parameter1[,parameter2]…) RETURN data_type IS [inner_variable]

BEGIN

plsql_ sentence;

[EXCEPTION]

[dowith _ sentences;]

END [fun_name];

fun_name：函数名称，如果数据库中已经存在了此名称，则可以指定OR REPLACE关键字，这样新的函数将覆盖原来的函数。

parameter1：函数的参数，这是一个可选项，因为函数可以没有参数。

data_type：函数的返回值类型，这是一个必选项。前面要使用RETURN关键字来标明。

inner_variable：函数的内部变量，它有别于函数的参数，这是一个可选项。

plsql_ sentence：PL/SQL语句，它是函数主要功能的实现部分，也就是函数的主体。

dowith _ sentences：异常处理代码，也是PL/SQL语句，这是一个可选项。

由于函数有返回值，因此在函数主体部分（即BEGIN部分）必须使用RETURN语句返回函数值，并且要求返回值的类型要与函数声明时的返回值类型（即data_type）相同。

定义一个函数，用于计算emp表中指定部门的平均工资，代码如下：

create or replace function get_avg_pay(num_deptno number) return number is

num_avg_pay number;  --保存平均工资的内部变量

begin

select avg(sal) into num_avg_pay from emp where deptno=num_deptno; --某个部门的平均工资

return(round(num_avg_pay,2));  --返回平均工资

exception

when no_data_found then    --若此部门编号不存在

dbms_output.put_line('该部门编号不存在');

return(0);    --返回平均工资为0

end;

/

2. 调用函数

由于函数有返回值，因此在调用函数时，必须使用一个变量来保存函数的返回值，这样函数和这个变量就组成了一个赋值表达式。

调用函数get_avg_pay，计算部门编号为10的员工的平均工资并输出，代码如下：

set serveroutput on

declare

avg_pay number;   --定义变量，存储函数返回值

begin

avg_pay:=get_avg_pay(10);  --调用函数，并获取返回值

dbms_output.put_line('平均工资是：'||avg_pay);  --输出返回值，即员工平均工资

end;

/

3. 删除函数

删除函数的操作比较简单，使用DROP FUNCTION命令，其后面跟着要删除的函数名称，其语法格式如下：

DROP FUNCTION fun_name;

参数fun_name表示要删除的函数名称。

使用DROP FUNCTION命令删除函数get_avg_pay，代码如下：

drop function get_avg_pay;

当一个函数已经过时，想重新定义时，也不必先删除再创建，同样只需要在CREATE语句后面加上OR REPLACE关键字即可，代码如下:

CREATE OR REPLACE FUNCTION fun_name;