3.6.2　存储函数

存储函数也是一种存储程序，它被创建后便存储在数据库中，用户可以直接调用。存储函数与存储过程的区别在于，存储函数必须向调用环境返回一个执行结果。一般情况我们是把存储函数作为一个表达式来使用的，它可用于普通表达式能够使用的场合，这是因为每个函数都有一个返回值，在调用存储函数时，这个返回值便是存储函数的执行结果。例如，可以将存储函数赋给一个变量，或者将这个函数与另一个表达式进行计算等。

创建存储函数的语法格式为：

CREATE OR REPLACE FUNCTION 函数名（参数1，参数2……）RETURN 返回类型

AUTHID CURRENT_USER|DEFINER

AS

声明部分

BEGIN

可执行部分

RETURN 表达式；

EXCEPTION

异常处理部分

END；

可以看出，创建存储函数的格式与创建存储过程的格式大致相同，只有三个不同的地方，第一，用FUNCTION关键字代替了PROCEDURE关键字，以表明创建的对象是存储函数；第二，在参数列表之后用RETURN关键字规定了存储函数返回值的类型；第三，在存储函数的可执行部分至少有一条RETURN语句，将执行结果返回给调用者。

在存储函数的可执行部分中，可能会出现多条RETURN语句，用于向调用者返回不同的数据，但是经过逻辑处理后，只能有一条RETURN语句被执行，保证从存储函数中返回一个确定的数据，这样就符合了程序的“单出口”的原则。

如果用户要在自己的模式中创建存储函数，需要具有CREATE FUNCTION的系统权限，如果要在其他模式中创建存储函数，则需要具有CREATE ANY FUNCTION的系统权限。

例如，下面的存储函数用来计算每个员工的总收入。这个函数有两个参数，即工资和奖金，它的功能是求出工资和奖金之和，然后将结果返回。创建这个函数的语句为：

CREATE OR REPLACE FUNCTION total_income（sal number, comm number）

RETURN number

AS

result number：=0；

BEGIN

result：=sal+nvl（comm，0）；

RETURN result；

END；

如果要利用这个存储函数求员工的总收入，可以将这个函数用在SELECT语句中，作为SELECT语句的一个表达式，并且向它传递实际参数，最后得到它的计算结果。例如：

SQL＞SELECT ename, total_income（sal, comm）as total FROM emp；

再比如，下面的存储函数用于计算员工应缴的个人所得税，这个函数以部门号为参数，计算该部门中全部员工的所得税总和。假设税率为3%，该函数用SUM函数计算全体员工的工资总和，然后乘以3%，并将最后的结果返回。函数的代码如下所示：

CREATE OR REPLACE FUNCTION tax_per_depart（dno integer）RETURN number

AS

result number：=0；

BEGIN

SELECT sum（sal）*0.03 INTO result FROM emp

WHERE deptno=dno

GROUP BY deptno；

RETURN result；

END；

与其他存储函数一样，这个函数可以用在SELECT语句中，也可以在其他匿名块、存储过程、存储函数中调用执行。例如，在下面的匿名块中调用了该函数，计算部门20的所得税。

DECLARE

dno integer；

total_tax number；

BEGIN

dno：=20；

total_tax：=tax_per_depart（dno）；

dbms_output.put_line（'Total tax of department'||dno||'is：'||total_tax）；

END；

每个用户都可以直接调用自己创建的存储函数，如果要调用其他用户的存储函数，则需要具有对相应存储函数的EXECUTE权限。为此，存储函数的所有者要将EXECUTE权限授予适当的用户。授予EXECUTE权限的语句格式为：

GRANT EXECUTE ON函数名TO用户

例如，存储函数total_income的所有者要将它的执行权限授予用户lxj，则可以执行下面的SQL语句：

SQL＞GRANT EXECUTE ON total_income TO lxj

如果要删除一个存储函数，可以执行DROP命令，这个命令的格式为：

DROP FUNCTION 过程名

总之，存储过程和存储函数都是存储程序，它们的区别在于存储过程没有返回值，只能被单独调用执行，在功能上类似于一条命令，而存储函数有返回值，可以用在SELECT语句和运算表达式中，它的作用相当于一个普通的表达式。在存储过程和存储函数中都可以定义子程序，这里把重点放在了存储过程和存储函数本身的使用上，对子程序在存储过程和存储函数中的用法没有进行描述，实际上这也是很简单的，详细的使用方法请参阅3.5节的相关内容。