PL/SQL函数

作者：--    发布时间：2019-11-20

在本章中，我们将讨论和学习pl/sql中的函数。函数与过程(也叫程序)相同，只不过函数有返回一个值，而过程没有返回值。 因此，上一章中所有有关存储过程的内容也适用于函数。

1. 创建函数

使用create function语句创建独立函数。create or replace procedure语句的简化语法如下：

create [or replace] function function_name 
[(parameter_name [in | out | in out] type [, ...])] 
return return_datatype 
{is | as} 
begin 
   < function_body > 
end [function_name];

其中，

• function-name是指定要创建的函数的名称。
• [or replace]选项指示是否允许修改现有的函数。
• 可选参数列表包含参数的名称，模式和类型。 in表示将从外部传递的值，out表示将用于返回过程外的值的参数。
• 函数必须包含一个返回(return)语句。
• return子句指定要从函数返回的数据类型。
• function-body包含可执行部分。
• 使用as关键字代替is关键字，用来创建独立的函数。

示例

以下示例说明如何创建和调用独立函数。此函数返回客户表(customers)中的总数。

我们将使用在pl/sql变量章节中创建的customers表 -

create table customers( 
   id   int not null, 
   name varchar (20) not null, 
   age int not null, 
   address char (25), 
   salary   decimal (18, 2),        
   primary key (id) 
);  

-- 数据
insert into customers (id,name,age,address,salary) 
values (1, 'ramesh', 32, 'ahmedabad', 2000.00 );  

insert into customers (id,name,age,address,salary) 
values (2, 'khilan', 25, 'delhi', 1500.00 );  

insert into customers (id,name,age,address,salary) 
values (3, 'kaushik', 23, 'kota', 2000.00 );

insert into customers (id,name,age,address,salary) 
values (4, 'chaitali', 25, 'mumbai', 6500.00 ); 

insert into customers (id,name,age,address,salary) 
values (5, 'hardik', 27, 'bhopal', 8500.00 );  

insert into customers (id,name,age,address,salary) 
values (6, 'komal', 22, 'mp', 4500.00 );

基于上面表和数据记录，创建一个函数：totalcustomers 来计算总客户数量。参考以下代码 -

create or replace function totalcustomers 
return number is 
   total number(2) := 0; 
begin 
   select count(*) into total 
   from customers; 

   return total; 
end; 
/

当使用sql提示符执行上述代码时，它将产生以下结果 -

2. 调用函数

在创建一个函数时，我们给出一个定义函数的语句以及实现的功能。 要使用一个函数，必须调用该函数来执行定义的任务。当程序调用一个函数时，程序控制被传递给被调用的函数。

被调用的函数执行定义的任务，当执行返回语句或达到最后一个结束语句时，它将程序控制返回到主程序。

如果调用一个函数，只需要传递所需的参数和函数名，如果函数返回一个值，那么可以存储返回的值。 以下程序演示如何从匿名块调用函数totalcustomers -

set serveroutput on size 99999;
declare 
   c number(2); 
begin 
   c := totalcustomers(); 
   dbms_output.put_line('当前客户的总数为: ' || c); 
end; 
/

执行上面示例代码，得到以下结果 -

示例

以下示例演示声明，定义和调用一个简单的pl/sql函数，该函数计算并返回两个值中的最大值。

set serveroutput on size 99999;
create or replace function findmax(x in number, y in number)  
return number 
is 
    z number; 
begin 
   if x > y then 
      z:= x; 
   else 
      z:= y; 
   end if;  
   return z; 
end;
/
declare 
   a number; 
   b number; 
   c number; 
begin 
   a:= 23; 
   b:= 45;  
   c := findmax(a, b); 
   dbms_output.put_line('两个数：23,45 之中的最大值是: ' || c); 
end; 
/

执行上面示例代码，得到以下结果 -

两个数：23,45 之中的最大值是: 45

3. pl/sql递归函数

我们在前面已经看到程序或子程序可能会调用另一个子程序。当子程序调用自身时，它被称为递归调用，该过程称为递归。

为了更好地说明递归这个概念，让我们来看看计算一个给定数字的阶乘示例。 数字n的因子被定义为 -

n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
      ... 
   = n*(n-1)*(n-2)*(n-3)... 1

以下过程是通过递归调用本身来计算给定数字的阶乘 -

set serveroutput on size 99999;
declare 
   num number; 
   factorial number;  

function fact(x number) 
return number  
is 
   f number; 
begin 
   if x=0 then 
      f := 1; 
   else 
      f := x * fact(x-1); 
   end if; 
return f; 
end;  

begin 
   num:= 10; 
   factorial := fact(num); 
   dbms_output.put_line(' 数字 '|| num || ' 的阶乘积是： ' || factorial); 
end; 
/

当上述代码在sql提示符下执行时，它会产生以下结果 -

数字 10 的阶乘积是： 3628800

pl/sql 过程已成功完成。