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

#p#

SQL 存儲過程格式

與單個 SQL 語句不同，大部分情況中，SQL 存儲過程體由一個或多個復合 SQL 語句組成。復合 SQL 語句只是由關鍵字 BEGIN 和 END 封裝的兩個或多個 SQL 語句或 SQL PL 對象，并且以分號結尾。一條 ATOMIC 復合 SQL 語句可以認為是單個的整體嗎?如果在其中產生任何未處理的錯誤條件，所有執行到該點的語句都被認為已經失敗，并且回滾對數據庫所做的任何更改。

當復合語句用來創建 SQL 存儲過程體時，它可以包含幾個邏輯部分。為了正確地開發一個 SQL 存儲過程，使用的每個部分都必須以非常特定的順序實現。每個邏輯部分必須依據的實現順序如下所示： <標簽：> BEGIN

變量聲明

條件聲明

游標聲明

條件處理程序聲明

賦值，流程控制，SQL語句和其它復合語句

END <標簽>

正如這個格式結構所示，可選的變量、條件和條件處理程序聲明必須在存儲過程邏輯(使用 SQL PL 流程控制語句實現)和 SQL 語句之前。游標可以在任何地方聲明，但是最好在任何條件處理程序聲明之前定義。

SQL 存儲過程可以由遵循此格式的一個或多個復合語句(或塊)組成，這些塊可以嵌套或依次執行。為了清晰地顯示流程控制，每個塊都可以加上標簽，從而可以包含許多 SQL 語句。這使進行控制轉移語句引用時更加容易實現精確性。

清單 2 顯示一個其存儲過程體由幾個嵌套復合 SQL 語句組成的 SQL 存儲過程，它們遵循剛才所述的格式。可以在 DB2 9 SQL Reference(卷 2)中的標題 “復合 SQL(存儲過程)” 下找到關于這種格式的更多信息，以及如何對每個部分進行編碼的詳細信息和例子。

清單 2. 飽含多個子句的存儲過程

CREATE PROCEDURE hr.adjust_salary 
　　(IN empid INTEGER, IN rating INTEGER, OUT msg VARCHAR(128)) 
　　DYNAMIC RESULT SETS 1 
　　MODIFIES SQL DATA 
　　DETERMINISTIC 
　　LANGUAGE SQL 
　　main: BEGIN 
　　DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; 
　　DECLARE SQLCODE INTEGER DEFAULT 0; 
　　DECLARE not_found CONDITION FOR SQLSTATE '02000'; 
　　DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM hr.employees; 
　　error_handler: BEGIN 
　　DECLARE EXIT HANDLER FOR not_found 
　　SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee ID not found'; 
　　work: BEGIN ATOMIC 
　　IF (rating = 1) THEN 
　　UPDATE hr.employees SET salary = salary * 1.10 
　　WHERE emp_id = empid; 
　　ELSEIF (rating = 2) THEN 
　　UPDATE hr.employees SET salary = salary * 1.05 
　　WHERE emp_id = empid; 
　　ELSEIF (rating = 3) THEN 
　　UPDATE hr.employees SET salary = salary * 1.03 
　　WHERE emp_id = empid; 
　　ELSE 
　　UPDATE hr.employees SET put_on_plan = 'Y' 
　　WHERE emp_id = empid; 
　　END IF; 
　　SET msg = 'Updated record for employee with ID = ' || CHAR(empid); 
　　END work; 
　　END error_handler; 
　　OPEN c1; 
　　END main

調用 SQL 存儲過程

創建 SQL 存儲過程之后，就可以從另一個 SQL 存儲過程或從一個客戶端應用程序交互式地調用它(使用命令行編輯器或 CLP 等工具)。通過執行 CALL 語句調用 SQL 存儲過程;這個語句的基本語法如下： CALL [ProcedureName] ( <[ParameterValue] | [OutputValue] | NULL> ,...)

其中：

ProcedureName 標識指定給要調用的存儲過程的名稱。記住，調用存儲過程時必須使用存儲過程名，而不是專用名。

ParameterValue 標識要傳遞給所調用的存儲過程的一個或多個參數值。

OutputValue 標識一個或多個接收由所調用存儲過程返回的值的參數標記或主機變量。

您可以從 CLP 調用清單 1 中所示的 SQL 存儲過程(通過連接到合適的數據庫和執行類似以下的 CALL 語句)： CALL conv_temp.f_to_c(98.6, ?)

當這個語句被執行時，值 98.6 通過名稱為 TEMP_F 的輸入參數傳遞給存儲過程，問號(?)被用作一個占位符，用于將通過名稱為 TEMP_C 的輸出參數所返回的值。

可以從嵌入的 SQL 應用程序使用如下的 CALL 語句來調用相同的存儲過程： EXEC SQL CALL conv_temp.f_to_c(98.6, :TempC)

在這種情況，TempC 是主機變量的名稱，該變量使用與 REAL DB2 數據類型兼容的特定于編程語言的數據類型來聲明。

效率和性能

SQL 存儲過程提供有效的方法將業務規則邏輯從應用程序移動到數據庫。通常，這種移動帶來極大的性能提升，因為在服務器上完成處理，并且必須通過網絡傳輸的消息更少。使用 SQL 存儲過程保證在訪問數據庫的所有應用程序中一致地實施業務規則。并且因為 SQL 存儲過程中的邏輯可以單獨修改，所以當業務規則改變時不必重新編寫應用程序。

不管是設計新的數據庫應用程序還是只想簡化日常操作，都可以尋找機會使用 SQL 存儲過程。如果您發現 SQL 存儲過程的開發和部署在您的工作中很有用，那么您可能想成為一名 IBM 認證的 DB2 9.5 SQL 存儲過程開發人員。

【編輯推薦】

1. 深入MySQL數據庫的索引
2. 了解MySQl數據庫目錄
3. Oracle中SQL語句的幾種用法