如何使用 SQL 存儲過程簡化複雜的操作

本文介紹什麼是 SQL 存儲過程，為什麼要使用存儲過程，如何使用存儲過程，以及創建和使用存儲過程的基本語法。一、存儲過程迄今為止，我們使用的大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單，經常會有一些複雜的操作需要多條語句才能完成，例如以下的情形。為了處理訂單，必須 ...

一、存儲過程
二、為什麼要使用存儲過程
三、執行存儲過程
四、創建存儲過程
五、小結

本文介紹什麼是 SQL 存儲過程，為什麼要使用存儲過程，如何使用存儲過程，以及創建和使用存儲過程的基本語法。

一、存儲過程

迄今為止，我們使用的大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單，經常會有一些複雜的操作需要多條語句才能完成，例如以下的情形。

為了處理訂單，必須核對以保證庫存中有相應的物品。
如果物品有庫存，需要預定，不再出售給別的人，並且減少物品數據以反映正確的庫存量。
庫存中沒有的物品需要訂購，這需要與供應商進行某種交互。
關於哪些物品入庫（並且可以立即發貨）和哪些物品退訂，需要通知相應的顧客。

這顯然不是一個完整的例子，它甚至超出了本文的範圍，但足以表達我們的意思了。執行這個處理需要針對許多表的多條 SQL 語句。

此外，需要執行的具體 SQL 語句及其次序也不是固定的，它們可能會根據物品是否在庫存中而變化。

那麼，怎樣編寫代碼呢？可以單獨編寫每條 SQL 語句，並根據結果有條件地執行其他語句。在每次需要這個處理時（以及每個需要它的應用中），都必須做這些工作。

可以創建存儲過程。簡單來說，存儲過程就是為以後使用而保存的一條或多條 SQL 語句。可將其視為批文件，雖然它們的作用不僅限於批處理。

說明：不適用於 SQLite

SQLite 不支持存儲過程。

說明：還有更多內容

存儲過程很複雜，全面介紹它需要很大篇幅。

市面上有專門講存儲過程的書。

本文不打算講解存儲過程的所有內容，只給出簡單介紹，讓讀者對它們的功能有所瞭解。

因此，這裡給出的例子只提供 Oracle 和 SQL Server 的語法。

二、為什麼要使用存儲過程

我們知道了什麼是存儲過程，那麼為什麼要使用它們呢？理由很多，下麵列出一些主要的。

通過把處理封裝在一個易用的單元中，可以簡化複雜的操作（如前面例子所述）。
由於不要求反覆建立一系列處理步驟，因而保證了數據的一致性。如果所有開發人員和應用程式都使用同一存儲過程，則所使用的代碼都是相同的。
上一點的延伸就是防止錯誤。需要執行的步驟越多，出錯的可能性就越大。防止錯誤保證了數據的一致性。
簡化對變動的管理。如果表名、列名或業務邏輯（或別的內容）有變化，那麼只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。
上一點的延伸就是安全性。通過存儲過程限制對基礎數據的訪問，減少了數據訛誤（無意識的或別的原因所導致的數據訛誤）的機會。
因為存儲過程通常以編譯過的形式存儲，所以 DBMS 處理命令所需的工作量少，提高了性能。
存在一些只能用在單個請求中的 SQL 元素和特性，存儲過程可以使用它們來編寫功能更強更靈活的代碼。

換句話說，使用存儲過程有三個主要的好處，即簡單、安全、高性能。顯然，它們都很重要。不過，在將 SQL 代碼轉換為存儲過程前，也必須知道它的一些缺陷。

不同 DBMS 中的存儲過程語法有所不同。事實上，編寫真正的可移植存儲過程幾乎是不可能的。

不過，存儲過程的自我調用（名字以及數據如何傳遞）可以相對保持可移植。

因此，如果需要移植到別的 DBMS，至少客戶端應用代碼不需要變動。
一般來說，編寫存儲過程比編寫基本 SQL 語句複雜，需要更高的技能，更豐富的經驗。

因此，許多資料庫管理員把限制存儲過程的創建作為安全措施（主要受上一條缺陷的影響）。

儘管有這些缺陷，存儲過程還是非常有用的，並且應該使用。事實上，多數 DBMS 都帶有用於管理資料庫和表的各種存儲過程。更多信息請參閱具體的 DBMS 文檔。

說明：不會寫存儲過程？你依然可以使用

大多數 DBMS 將編寫存儲過程所需的安全和訪問許可權與執行存儲過程所需的安全和訪問許可權區分開來。

這是好事情，即使你不能（或不想）編寫自己的存儲過程，也仍然可以在適當的時候執行別的存儲過程。

三、執行存儲過程

存儲過程的執行遠比編寫要頻繁得多，因此我們先介紹存儲過程的執行。

執行存儲過程的 SQL 語句很簡單，即 EXECUTE。

EXECUTE 接受存儲過程名和需要傳遞給它的任何參數。

請看下麵的例子（你無法運行這個例子，因為 AddNewProduct 這個存儲過程還不存在）：

EXECUTE AddNewProduct('JTS01',
                      'Stuffed Eiffel Tower',
                      6.49,
                      'Plush stuffed toy with the text La Tour Eiffel in red white and blue');

這裡執行一個名為 AddNewProduct 的存儲過程，將一個新產品添加到 Products 表中。

AddNewProduct 有四個參數，分別是：供應商 ID（Vendors 表的主鍵）、產品名、價格和描述。

這 4 個參數匹配存儲過程中 4 個預期變數（定義為存儲過程自身的組成部分）。此存儲過程將新行添加到 Products 表，並將傳入的屬性賦給相應的列。

我們註意到，在 Products 表中還有另一個需要值的列 prod_id 列，它是這個表的主鍵。

為什麼這個值不作為屬性傳遞給存儲過程？要保證恰當地生成此 ID，最好是使生成此 ID 的過程自動化（而不是依賴於最終用戶的輸入）。

這也是這個例子使用存儲過程的原因。以下是存儲過程所完成的工作：

驗證傳遞的數據，保證所有 4 個參數都有值；
生成用作主鍵的唯一 ID；
將新產品插入 Products 表，在合適的列中存儲生成的主鍵和傳遞的數據。

這就是存儲過程執行的基本形式。對於具體的 DBMS，可能包括以下的執行選擇。

參數可選，具有不提供參數時的預設值。
不按次序給出參數，以“參數=值”的方式給出參數值。
輸出參數，允許存儲過程在正執行的應用程式中更新所用的參數。
用 SELECT 語句檢索數據。
返回代碼，允許存儲過程返回一個值到正在執行的應用程式。

四、創建存儲過程

正如所述，存儲過程的編寫很重要。為了獲得感性認識，我們來看一個簡單的存儲過程例子，它對郵件發送清單中具有郵件地址的顧客進行計數。

下麵是該過程的 Oracle 版本：

CREATE PROCEDURE MailingListCount (
  ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
END;

這個存儲過程有一個名為 ListCount 的參數。此參數從存儲過程返回一個值而不是傳遞一個值給存儲過程。

關鍵字 OUT 用來指示這種行為。Oracle 支持 IN（傳遞值給存儲過程）、OUT（從存儲過程返回值，如這裡）、INOUT（既傳遞值給存儲過程也從存儲過程傳回值）類型的參數。

存儲過程的代碼括在 BEGIN 和 END 語句中，這裡執行一條簡單的 SELECT 語句，它檢索具有郵件地址的顧客。然後用檢索出的行數設置 ListCount（要傳遞的輸出參數）。

調用 Oracle 例子可以像下麵這樣：

var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

這段代碼聲明瞭一個變數來保存存儲過程返回的任何值，然後執行存儲過程，再使用 SELECT 語句顯示返回的值。

下麵是該過程的 SQL Server 版本。

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

此存儲過程沒有參數。調用程式檢索 SQL Server 的返回代碼提供的值。

其中用 DECLARE 語句聲明瞭一個名為 @cnt 的局部變數（SQL Server 中所有局部變數名都以 @ 起頭）；

然後在 SELECT 語句中使用這個變數，讓它包含 COUNT() 函數返回的值；最後，用 RETURN @cnt 語句將計數返回給調用程式。

調用 SQL Server 例子可以像下麵這樣：

DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;

這段代碼聲明瞭一個變數來保存存儲過程返回的任何值，然後執行存儲過程，再使用 SELECT 語句顯示返回的值。

下麵是另一個例子，這次在 Orders 表中插入一個新訂單。此程式僅適用於 SQL Server，但它說明瞭存儲過程的某些用途和技術：

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 為訂單號聲明一個變數
DECLARE @order_num INTEGER
-- 獲取當前最大訂單號
SELECT @order_num=MAX(order_num)
FROM Orders
-- 決定下一個訂單號
SELECT @order_num=@order_num+1
-- 插入新訂單
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- 返回訂單號
RETURN @order_num;

此存儲過程在 Orders 表中創建一個新訂單。

它只有一個參數，即下訂單顧客的 ID。訂單號和訂單日期這兩列在存儲過程中自動生成。

代碼首先聲明一個局部變數來存儲訂單號。接著，檢索當前最大訂單號（使用 MAX() 函數）並增加 1（使用 SELECT 語句）。

然後用 INSERT 語句插入由新生成的訂單號、當前系統日期（用 GETDATE() 函數檢索）和傳遞的顧客 ID 組成的訂單。

最後，用 RETURN @order_num 返回訂單號（處理訂單物品需要它）。

請註意，此代碼加了註釋，在編寫存儲過程時應該多加註釋。

說明：註釋代碼

應該註釋所有代碼，存儲過程也不例外。增加註釋不影響性能，因此不存在缺陷（除了增加編寫時間外）。

註釋代碼的好處很多，包括使別人（以及你自己）更容易地理解和更安全地修改代碼。

對代碼進行註釋的標準方式是在之前放置 --（兩個連字元）。

有的 DBMS 還支持其他的註釋語法，不過所有 DBMS 都支持 --，因此在註釋代碼時最好都使用這種語法。

下麵是相同 SQL Server 代碼的一個很不同的版本：

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 插入新訂單
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- 返回訂單號
SELECT order_num = @@IDENTITY;

此存儲過程也在 Orders 表中創建一個新訂單。這次由 DBMS 生成訂單號。

大多數 DBMS 都支持這種功能；SQL Server 中稱這些自動增量的列為標識欄位（identity field），而其他 DBMS 稱之為自動編號（auto number）或序列（sequence）。

傳遞給此過程的參數也是一個，即下訂單的顧客 ID。

訂單號和訂單日期沒有給出，DBMS 對日期使用預設值（GETDATE() 函數），訂單號自動生成。

怎樣才能得到這個自動生成的 ID？在 SQL Server 上可在全局變數 @@IDENTITY 中得到，它返回到調用程式（這裡使用 SELECT 語句）。

可以看到，藉助存儲過程，可以有多種方法完成相同的工作。不過，所選擇的方法受所用 DBMS 特性的制約。

五、小結

本文介紹了什麼是存儲過程，為什麼使用存儲過程。我們介紹了執行和創建存儲過程的語法，使用存儲過程的一些方法。

存儲過程是個相當重要的主題，一篇文章無法全部涉及。

各種 DBMS 對存儲過程的實現不一，你使用的 DBMS 可能提供了一些這裡提到的功能，也有其他未提及的功能，更詳細的介紹請參閱具體的 DBMS 文檔。

原文鏈接：https://www.developerastrid.com/sql/sql-stored-procedures/

（完）