Plsql 简明教程

PL/SQL - Functions

在本章中，我们将讨论 PL/SQL 中的函数。函数与过程相同，只是函数会返回值。因此，前一章的所有讨论也适用于函数。

In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too.

Creating a Function

使用 CREATE FUNCTION 语句创建独立函数。 CREATE OR REPLACE PROCEDURE 语句的简化语法如下：

A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   < function_body >
END [function_name];

其中，

Where,

function-name specifies the name of the function.
[OR REPLACE] option allows the modification of an existing function.
The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
The function must contain a return statement.
The RETURN clause specifies the data type you are going to return from the function.
function-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone function.

Example

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

The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table.

我们将使用我们创建在 PL/SQL Variables 一章中的 CUSTOMERS 表 −

We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;

   RETURN total;
END;
/

当使用 SQL 提示执行以上代码时，它将会生成以下结果 −

When the above code is executed using the SQL prompt, it will produce the following result −

Function created.

Calling a Function

在创建函数时，你需要提供函数将要完成的功能的定义。要使用函数，你必须调用此函数执行已定义的任务。当程序调用函数时，对此被调用函数实施程序控制。

While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function.

一个被调用的函数执行定义的任务，当它的返回语句执行或 last end statement 达到时，它将程序控制权返回给主程序。

A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program.

要调用函数，你只需传回所需参数和函数名字，如果函数返回一个值，那么你便可以储存返回的值。下列程序从匿名代码组调用了函数 totalCustomers −

To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block −

DECLARE
   c number(2);
BEGIN
   c := totalCustomers();
   dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Total no. of Customers: 6

PL/SQL procedure successfully completed.

Example

以下的例子显示如何用 PL/SQL 函数宣布、定义和启动一个简单的 PL/SQL 函数，它能计算并返回两个值中的最大值。

The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values.

DECLARE
   a number;
   b number;
   c number;
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;
BEGIN
   a:= 23;
   b:= 45;
   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Maximum of (23,45): 45

PL/SQL procedure successfully completed.

PL/SQL Recursive Functions

我们已经看到，一个程序或子程序可能会调用另一个子程序。当一个子程序调用它自己，它称之为递归调用并且此过程称为 recursion 。

We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion.

为了说明这个概念，让我们计算一个数字的阶乘。一个数字 n 的阶乘被定义为 −

To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as −

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

以下的程序通过递归地调用它自己来计算一个给定数字的阶乘 −

The following program calculates the factorial of a given number by calling itself recursively −

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:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

当以上代码在 SQL 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

Factorial 6 is 720

PL/SQL procedure successfully completed.