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 过程已成功完成。

原文链接：https://www.yiibai.com/plsql/plsql_functions.html