PL/SQL函式 - tw511教學網

在本章中，我們將討論和學習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 過程已成功完成。