PL/SQL儲存過程 - tw511教學網

在本章中，我們將討論PL/SQL中的儲存過程。子程式是執行特定任務的程式單元/模組。這些子程式組合起來形成更大的程式。這種做法被稱為「模組化設計」。子程式可以被稱為呼叫程式的另一個子程式或程式呼叫。

可以在以下幾個地方中建立一個子程式 -

在模式(schema)級別中
一個程式包中
在PL/SQL塊中

在模式(schema)級別中，子程式是一個獨立的子程式。它是使用CREATE PROCEDURE或CREATE FUNCTION語句建立的。它儲存在資料庫中，可以使用DROP PROCEDURE或DROP FUNCTION語句進行刪除。

在包中建立的子程式是打包的子程式。它儲存在資料庫中，只有當使用DROP PACKAGE語句刪除程式包時，才能將其刪除。我們將在「PL/SQL程式包」一章中討論程式包的應用。

PL/SQL子程式被命名為可以使用一組引數呼叫的PL/SQL塊。 PL/SQL提供兩種子程式 -

函式 - 這些子程式返回單個值; 主要用於計算和返回值。
儲存過程(程式) - 這些子程式不直接返回值; 主要用於執行動作。

本章將主要介紹PL/SQL中的儲存過程。在下一章介紹討論PL/SQL函式。

PL/SQL子程式的部分

每個PL/SQL子程式都有一個名稱，也可能有一個引數列表。像匿名PL/SQL塊一樣，命名塊也將具有以下三個部分 -

編號	部分	描述
1	宣告部分	這是一個可選的部分。但是，子程式的宣告部分不以`DECLARE`關鍵字開頭。它包含型別，游標，常數，變數，異常和巢狀子程式的宣告。這些項是本子程式，當子程式完成執行時，它們將不復存在。
2	可執行部分	這是一個強制性部分(必須有)，並包含執行指定操作的語句。
3	例外處理	這是一個可選的部分。它包含處理執行時錯誤的程式碼。

建立儲存過程

可使用CREATE OR REPLACE PROCEDURE語句來建立一個儲存過程。 CREATE OR REPLACE PROCEDURE語句的簡化語法如下：

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

其中，

procedure-name是要建立的儲存過程的名稱。
[OR REPLACE]選項允許修改現有的過程。
可選引數列表包含引數的名稱，模式和型別。IN表示將從外部傳遞的值，OUT表示將用於返回過程外的值的引數。
procedure-body包含可執行部分。
使用AS關鍵字而不是IS關鍵字來建立儲存過程。

例子
以下範例演示如何建立一個簡單的儲存過程，執行時它只顯示字串「Hello World！」在螢幕上。

SET SERVEROUTPUT ON SIZE 99999;
CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

-- 執行儲存過程
exec greetings;
-- 或者
EXECUTE greetings;

當使用SQL提示符執行上述程式碼時，它將產生以下結果 -

執行獨立程式

獨立的儲存程式可以通過兩種方式呼叫 -

使用EXECUTE關鍵字
從PL/SQL塊呼叫過程的名稱

可以使用EXECUTE關鍵字呼叫名為「greetings」的儲存過程如下 -

EXECUTE greetings;

上述呼叫將顯示結果為 -

SQL> EXECUTE greetings;
Hello World!

PL/SQL 過程已成功完成。

SQL>

該過程也可以從另一個PL/SQL塊呼叫，例如 -

BEGIN 
   greetings; 
END; 
/

執行上面範例程式碼，得到以下結果 -

SQL> BEGIN
  2     greetings;
  3  END;
  4  /
Hello World!

PL/SQL 過程已成功完成。

SQL>

刪除獨立儲存過程

使用DROP PROCEDURE語句刪除獨立儲存過程。刪除程式的語法是 -

DROP PROCEDURE procedure-name;

可以使用以下語句刪除greetings儲存過程程式 -

DROP PROCEDURE greetings;

PL/SQL子程式中的引數模式

下表列出了PL/SQL子程式中的引數模式 -

編號	引數模式	描述
1	`IN`	`IN`引數允許將值傳遞給子程式。它是一個唯讀引數。在子程式中，`IN`引數的作用如常數，它不能被賦值。可以將常數，文字，初始化的變數或表示式作為`IN`引數傳遞。也可以將其初始化為預設值; 然而，在這種情況下，從子程式呼叫中省略它。它是引數傳遞的預設模式。引數通過參照傳遞。
2	`OUT`	`OUT`引數返回一個值給呼叫程式。在子程式中，`OUT`引數像變數一樣。可以更改其值並在分配該值後參照該值。實際引數必須是可變的，並且通過值傳遞。
3	`IN OUT`	`IN OUT`引數將初始值傳遞給子程式，並將更新的值返回給呼叫者。它可以分配一個值，該值可以被讀取。對應於`IN OUT`形式引數的實際引數必須是變數，而不是常數或表示式。正式引數必須分配一個值。實際引數(實參)通過值傳遞。

IN和OUT模式 - 範例1

假設以下儲存過程需要求出兩個值中的最小值。這裡，儲存過程兩個輸入的數位使用IN模式，並使用OUT模式引數返回最小值。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 12; 
   b:= 35; 
   findMin(a, b, c); 
   dbms_output.put_line('兩個數：12, 35中的最小值是 : ' || c); 
END; 
/

當上述程式碼在SQL提示符下執行時，它會產生以下結果 -

兩個數：12, 35中的最小值是 : 12

IN和OUT模式 - 範例2

此過程計算傳遞值的值的平方。此範例顯示了如何使用相同的引數來接受值，然後返回另一個結果。

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 11; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

當上述程式碼在SQL提示符下執行時，它會產生以下結果 -

傳遞引數的方法

實際引數(實參)可以通過三種方式傳遞 -

位置符號
命名符號
混合符號

位置符號

在位置符號中，可以呼叫儲存過程如下 -

findMin(a, b, c, d);

在位置符號中，第一個實際引數代替第一個形式引數; 第二個實際引數代替第二個形式引數，依此類推。因此，a代替x，b代替y，c代替z，d代替m。

命名符號

在命名符號中，實際引數與使用箭頭符號(=>)的形式引數相關聯。呼叫儲存過程如下所示 -

findMin(x => a, y => b, z => c, m => d);

混合符號

在混合符號表示中，可以在過程呼叫中混合使用符號; 然而，位置符號應在命名符號之前。

以下呼叫儲存過程的方式是合法的 -

findMin(a, b, c, m => d);

但是，以下這種是不合法的：

findMin(x => a, b, c, d);