在本章中，我們將討論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);