分布式DBA：SQL預存程序知識總結(1)

DB2 預存程序：基礎知識

您在用戶端工作站上對遠程伺服器和位於該伺服器上的資料庫進行分類的任何時候，都存在一個簡單的 DB2 用戶端/伺服器環境。在這種環境中，每次對遠程伺服器上的資料庫執行 SQL 陳述式時，語句本身通過網路從用戶端發送到伺服器上的資料庫。然後資料庫處理語句，結果通過網路發送回用戶端。這意味著，對於每條被執行的 SQL 陳述式，兩條訊息都必須經過網路。因此，執行大量 SQL 操作的應用程式將產生大量網路通訊。

預存程序是編寫並直接儲存在資料庫中的命名 SQL 陳述式組(在一些情況中，包括作業系統調用)。預存程序提供下列優勢：

減少網路通訊量。對於編碼在預存程序中的 SQL 陳述式，訊息不通過網路發送。如果預存程序設計正確，那麼只有用戶端應用程式需要的資料才通過網路發送。

提高伺服器密集型工作的效能。因為較少資料通過網路發送，並且因為處理在伺服器上完成，所以複雜查詢和其它伺服器密集型的工作可以更快地執行。

商務邏輯的分離和重用。當商務規則被合并到預存程序中時，可以僅僅根據需求調用預存程序來多次重用邏輯。此外，保證相同的商務規則邏輯在所有使用它的應用程式中一致實施。如果商務規則改變，那麼只需要改變預存程序中的邏輯;不需要更改調用預存程序的應用程式。

訪問伺服器功能。因為預存程序在伺服器工作站上直接運行，所以它們可以利用任何額外的記憶體、更快的處理器或資料庫伺服器可能具備的其它資源。另外，預存程序可以執行許多 DB2 的管理命令，這些命令只能在伺服器上運行。最後，因為預存程序不僅僅限於執行資料庫的活動，所以它們的優點是可以利用已經安裝在伺服器上的任何附加軟體。

但是，有兩個需要注意的地方。第一，所有輸入資料都必須在調用時從應用程式傳遞到預存程序。第二，預存程序產生的結果資料集只有在預存程序完成執行後才返回給應用程式。換句話說，在預存程序運行期間，應用程式和預存程序之間不能發生任何互動。

建立 SQL 預存程序

對於 Linux、Unix 和 Windows 平台上的 DB2，可以使用三種不同類型的預存程序：SQL 的、外部的和來源(sourced)的預存程序。正如名稱所示，SQL 預存程序完全由 SQL 陳述式和 SQL PL 對象組成。相反，外部預存程序使用 C、C++、Java 或 COBOL 等進階程式設計語言構成。來源預存程序是基於其它 SQL 或外部預存程序的預存程序。

預存程序通過執行 CREATE PROCEDURE SQL 陳述式建立。這個語句存在三種形式(每種類型的預存程序有一種);用來建立 SQL 預存程序的形式的基本文法如下： CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType] ,...)

CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType] ,...) ＜SPECIFIC [SpecificName]＞ ＜DYNAMIC RESULT SETS 0 | DYNAMIC RESULT SETS [NumResultSets]＞ ＜CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA＞ ＜DETERMINISTIC | NOT DETERMINISTIC＞ ＜CALLED ON NULL INPUT＞ ＜LANGUAGE SQL＞ [ProcedureBody]

其中：

ProcedureName 標識指定給預存程序的名稱。

ParamType 指示 ParamName 標識的參數是輸入參數(IN)、輸出參數(OUT)或兩者(INOUT)都是。

ParamName 標識指定給預存程序參數的名稱。

DataType 標識預存程序期望為 ParamName 標識的參數接收和/或發送的資料類型。

SpecificName 標識指定給預存程序的專用名。當一個專用名被指定給預存程序時，可以通過在特殊形式的 DROP SQL 陳述式(DROP SPECIFIC PROCEDURE [SpecificName])中引用專用名來刪除預存程序。但是，如果沒有指定任何專用名，那麼必須同時提供預存程序名稱和預存程序簽名(也就是每個預存程序參數使用的資料類型的列表)來作為 DROP 語句的輸入。專用名不能用來調用預存程序。

NumResultSets 指示預存程序返回結果資料集並標識返回多少資料集。

ProcedureBody 標識調用預存程序時要執行的單個 SQL 陳述式或者一個或多個複合 SQL 陳述式。

注意：方括弧([])中顯示的參數或選項必填寫;角括弧(<>)中顯示的參數/選項不是必填的。可以在 DB2 9 SQL Reference - 卷 2(參見參考資料，第 51 頁)中找到 CREATE PROCEDURE 語句的完整文法。 子句用來標識編碼在預存程序體中的 SQL 陳述式的類型。可用的值如下：

CONTAINS SQL。預存程序體包含既不讀取資料也不修改資料的可執行 SQL 陳述式。

READS SQL DATA。預存程序體包含讀取資料但不修改資料的可執行 SQL 陳述式。

MODIFIES SQL DATA。預存程序體包含既讀取資料也修改資料的可執行 SQL 陳述式。

子句用來標識當傳遞相同(DETERMINISTIC)或不同(NOT DETERMINISTIC)的參數值時預存程序是否始終返回相同的結果。例如，對傳遞給它的任何值增加 15% 的預存程序將被視為 DETERMINISTIC，而使用 TIMESTAMP_ISO() 函數產生唯一 ID 的預存程序將被視為 NOT DETERMINISTIC。

最後， 子句指示預存程序即使在為一個或多個輸入參數提供空值進行調用時仍然被調用。

清單 1 顯示一個由簡單的 SQL 預存程序構成的 CREATE PROCEDURE 語句，設計用來將溫度從華氏溫度轉換成攝氏溫度。

清單 1. 建立簡單的 SQL 預存程序

CREATE PROCEDURE conv_temp.f_to_c(IN temp_f REAL, OUT temp_c REAL) DYNAMIC RESULT SETS 0 CONTAINS SQL DETERMINISTIC LANGUAGE SQL BEGIN DECLARE temp_value REAL; SET temp_value = (temp_f - 32); SET temp_c = (5 * temp_value) / 9; END