分布式DBA：SQL存儲(chǔ)過程知識(shí)總結(jié)

作者：Roger E. Sanders 2009-01-16 15:00:42

數(shù)據(jù)庫

分布式 本文將幫助您總體復(fù)習(xí)存儲(chǔ)過程，特別是 SQL 存儲(chǔ)過程。此外，還向您演示如何編寫 SQL 存儲(chǔ)過程以及如何從 DB2 命令行處理器(CLP)和嵌入的 SQL 應(yīng)用程序調(diào)用 SQL 存儲(chǔ)過程。

成都創(chuàng)新互聯(lián)公司主營玉泉街道網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,app開發(fā)定制,玉泉街道h5微信小程序定制開發(fā)搭建,玉泉街道網(wǎng)站營銷推廣歡迎玉泉街道等地區(qū)企業(yè)咨詢

DB2 存儲(chǔ)過程：基礎(chǔ)知識(shí)

您在客戶端工作站上對(duì)遠(yuǎn)程服務(wù)器和位于該服務(wù)器上的數(shù)據(jù)庫進(jìn)行分類的任何時(shí)候，都存在一個(gè)簡單的 DB2 客戶端/服務(wù)器環(huán)境。在這種環(huán)境中，每次對(duì)遠(yuǎn)程服務(wù)器上的數(shù)據(jù)庫執(zhí)行 SQL 語句時(shí)，語句本身通過網(wǎng)絡(luò)從客戶端發(fā)送到服務(wù)器上的數(shù)據(jù)庫。然后數(shù)據(jù)庫處理語句，結(jié)果通過網(wǎng)絡(luò)發(fā)送回客戶端。這意味著，對(duì)于每條被執(zhí)行的 SQL 語句，兩條消息都必須經(jīng)過網(wǎng)絡(luò)。因此，執(zhí)行大量 SQL 操作的應(yīng)用程序?qū)a(chǎn)生大量網(wǎng)絡(luò)通信。

存儲(chǔ)過程是編寫并直接存儲(chǔ)在數(shù)據(jù)庫中的命名 SQL 語句組(在一些情況中，包括操作系統(tǒng)調(diào)用)。存儲(chǔ)過程提供下列優(yōu)勢(shì)：

減少網(wǎng)絡(luò)通信量。對(duì)于編碼在存儲(chǔ)過程中的 SQL 語句，消息不通過網(wǎng)絡(luò)發(fā)送。如果存儲(chǔ)過程設(shè)計(jì)正確，那么只有客戶端應(yīng)用程序需要的數(shù)據(jù)才通過網(wǎng)絡(luò)發(fā)送。

提高服務(wù)器密集型工作的性能。因?yàn)檩^少數(shù)據(jù)通過網(wǎng)絡(luò)發(fā)送，并且因?yàn)樘幚碓诜?wù)器上完成，所以復(fù)雜查詢和其它服務(wù)器密集型的工作可以更快地執(zhí)行。

業(yè)務(wù)邏輯的分離和重用。當(dāng)業(yè)務(wù)規(guī)則被合并到存儲(chǔ)過程中時(shí)，可以僅僅根據(jù)需求調(diào)用存儲(chǔ)過程來多次重用邏輯。此外，保證相同的業(yè)務(wù)規(guī)則邏輯在所有使用它的應(yīng)用程序中一致實(shí)施。如果業(yè)務(wù)規(guī)則改變，那么只需要改變存儲(chǔ)過程中的邏輯;不需要更改調(diào)用存儲(chǔ)過程的應(yīng)用程序。

訪問服務(wù)器功能。因?yàn)榇鎯?chǔ)過程在服務(wù)器工作站上直接運(yùn)行，所以它們可以利用任何額外的內(nèi)存、更快的處理器或數(shù)據(jù)庫服務(wù)器可能具備的其它資源。另外，存儲(chǔ)過程可以執(zhí)行許多 DB2 的管理命令，這些命令只能在服務(wù)器上運(yùn)行。最后，因?yàn)榇鎯?chǔ)過程不僅僅限于執(zhí)行數(shù)據(jù)庫的活動(dòng)，所以它們的優(yōu)點(diǎn)是可以利用已經(jīng)安裝在服務(wù)器上的任何附加軟件。

但是，有兩個(gè)需要注意的地方。第一，所有輸入數(shù)據(jù)都必須在調(diào)用時(shí)從應(yīng)用程序傳遞到存儲(chǔ)過程。第二，存儲(chǔ)過程生成的結(jié)果數(shù)據(jù)集只有在存儲(chǔ)過程完成執(zhí)行后才返回給應(yīng)用程序。換句話說，在存儲(chǔ)過程運(yùn)行期間，應(yīng)用程序和存儲(chǔ)過程之間不能發(fā)生任何交互。

創(chuàng)建 SQL 存儲(chǔ)過程

對(duì)于 Linux、Unix 和 Windows 平臺(tái)上的 DB2，可以使用三種不同類型的存儲(chǔ)過程：SQL 的、外部的和來源(sourced)的存儲(chǔ)過程。正如名稱所示，SQL 存儲(chǔ)過程完全由 SQL 語句和 SQL PL 對(duì)象組成。相反，外部存儲(chǔ)過程使用 C、C++、Java 或 COBOL 等高級(jí)編程語言構(gòu)成。來源存儲(chǔ)過程是基于其它 SQL 或外部存儲(chǔ)過程的存儲(chǔ)過程。

存儲(chǔ)過程通過執(zhí)行 CREATE PROCEDURE SQL 語句創(chuàng)建。這個(gè)語句存在三種形式(每種類型的存儲(chǔ)過程有一種);用來創(chuàng)建 SQL 存儲(chǔ)過程的形式的基本語法如下： 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 標(biāo)識(shí)指定給存儲(chǔ)過程的名稱。

ParamType 指示 ParamName 標(biāo)識(shí)的參數(shù)是輸入?yún)?shù)(IN)、輸出參數(shù)(OUT)或兩者(INOUT)都是。

ParamName 標(biāo)識(shí)指定給存儲(chǔ)過程參數(shù)的名稱。

DataType 標(biāo)識(shí)存儲(chǔ)過程期望為 ParamName 標(biāo)識(shí)的參數(shù)接收和/或發(fā)送的數(shù)據(jù)類型。

SpecificName 標(biāo)識(shí)指定給存儲(chǔ)過程的專用名。當(dāng)一個(gè)專用名被指定給存儲(chǔ)過程時(shí)，可以通過在特殊形式的 DROP SQL 語句(DROP SPECIFIC PROCEDURE [SpecificName])中引用專用名來刪除存儲(chǔ)過程。但是，如果沒有指定任何專用名，那么必須同時(shí)提供存儲(chǔ)過程名稱和存儲(chǔ)過程簽名(也就是每個(gè)存儲(chǔ)過程參數(shù)使用的數(shù)據(jù)類型的列表)來作為 DROP 語句的輸入。專用名不能用來調(diào)用存儲(chǔ)過程。

NumResultSets 指示存儲(chǔ)過程返回結(jié)果數(shù)據(jù)集并標(biāo)識(shí)返回多少數(shù)據(jù)集。

ProcedureBody 標(biāo)識(shí)調(diào)用存儲(chǔ)過程時(shí)要執(zhí)行的單個(gè) SQL 語句或者一個(gè)或多個(gè)復(fù)合 SQL 語句。

注意：方括號(hào)([])中顯示的參數(shù)或選項(xiàng)必填寫;尖括號(hào)(<>)中顯示的參數(shù)/選項(xiàng)不是必填的?？梢栽?DB2 9 SQL Reference - 卷 2(參見參考資料，第 51 頁)中找到 CREATE PROCEDURE 語句的完整語法。 子句用來標(biāo)識(shí)編碼在存儲(chǔ)過程體中的 SQL 語句的類型?？捎玫闹等缦拢?/p>

CONTAINS SQL。存儲(chǔ)過程體包含既不讀取數(shù)據(jù)也不修改數(shù)據(jù)的可執(zhí)行 SQL 語句。

READS SQL DATA。存儲(chǔ)過程體包含讀取數(shù)據(jù)但不修改數(shù)據(jù)的可執(zhí)行 SQL 語句。

MODIFIES SQL DATA。存儲(chǔ)過程體包含既讀取數(shù)據(jù)也修改數(shù)據(jù)的可執(zhí)行 SQL 語句。

子句用來標(biāo)識(shí)當(dāng)傳遞相同(DETERMINISTIC)或不同(NOT DETERMINISTIC)的參數(shù)值時(shí)存儲(chǔ)過程是否始終返回相同的結(jié)果。例如，對(duì)傳遞給它的任何值增加 15% 的存儲(chǔ)過程將被視為 DETERMINISTIC，而使用 TIMESTAMP_ISO() 函數(shù)生成唯一 ID 的存儲(chǔ)過程將被視為 NOT DETERMINISTIC。

最后， 子句指示存儲(chǔ)過程即使在為一個(gè)或多個(gè)輸入?yún)?shù)提供空值進(jìn)行調(diào)用時(shí)仍然被調(diào)用。

清單 1 顯示一個(gè)由簡單的 SQL 存儲(chǔ)過程構(gòu)成的 CREATE PROCEDURE 語句，設(shè)計(jì)用來將溫度從華氏溫度轉(zhuǎn)換成攝氏溫度。

清單 1. 創(chuàng)建簡單的 SQL 存儲(chǔ)過程

 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 存儲(chǔ)過程格式

與單個(gè) SQL 語句不同，大部分情況中，SQL 存儲(chǔ)過程體由一個(gè)或多個(gè)復(fù)合 SQL 語句組成。復(fù)合 SQL 語句只是由關(guān)鍵字 BEGIN 和 END 封裝的兩個(gè)或多個(gè) SQL 語句或 SQL PL 對(duì)象，并且以分號(hào)結(jié)尾。一條 ATOMIC 復(fù)合 SQL 語句可以認(rèn)為是單個(gè)的整體嗎?如果在其中產(chǎn)生任何未處理的錯(cuò)誤條件，所有執(zhí)行到該點(diǎn)的語句都被認(rèn)為已經(jīng)失敗，并且回滾對(duì)數(shù)據(jù)庫所做的任何更改。

當(dāng)復(fù)合語句用來創(chuàng)建 SQL 存儲(chǔ)過程體時(shí)，它可以包含幾個(gè)邏輯部分。為了正確地開發(fā)一個(gè) SQL 存儲(chǔ)過程，使用的每個(gè)部分都必須以非常特定的順序?qū)崿F(xiàn)。每個(gè)邏輯部分必須依據(jù)的實(shí)現(xiàn)順序如下所示： <標(biāo)簽：> BEGIN

變量聲明

條件聲明

游標(biāo)聲明

條件處理程序聲明

賦值，流程控制，SQL語句和其它復(fù)合語句

END <標(biāo)簽>

正如這個(gè)格式結(jié)構(gòu)所示，可選的變量、條件和條件處理程序聲明必須在存儲(chǔ)過程邏輯(使用 SQL PL 流程控制語句實(shí)現(xiàn))和 SQL 語句之前。游標(biāo)可以在任何地方聲明，但是最好在任何條件處理程序聲明之前定義。

SQL 存儲(chǔ)過程可以由遵循此格式的一個(gè)或多個(gè)復(fù)合語句(或塊)組成，這些塊可以嵌套或依次執(zhí)行。為了清晰地顯示流程控制，每個(gè)塊都可以加上標(biāo)簽，從而可以包含許多 SQL 語句。這使進(jìn)行控制轉(zhuǎn)移語句引用時(shí)更加容易實(shí)現(xiàn)精確性。

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

清單 2. 飽含多個(gè)子句的存儲(chǔ)過程

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

調(diào)用 SQL 存儲(chǔ)過程

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

其中：

ProcedureName 標(biāo)識(shí)指定給要調(diào)用的存儲(chǔ)過程的名稱。記住，調(diào)用存儲(chǔ)過程時(shí)必須使用存儲(chǔ)過程名，而不是專用名。

ParameterValue 標(biāo)識(shí)要傳遞給所調(diào)用的存儲(chǔ)過程的一個(gè)或多個(gè)參數(shù)值。

OutputValue 標(biāo)識(shí)一個(gè)或多個(gè)接收由所調(diào)用存儲(chǔ)過程返回的值的參數(shù)標(biāo)記或主機(jī)變量。

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

當(dāng)這個(gè)語句被執(zhí)行時(shí)，值 98.6 通過名稱為 TEMP_F 的輸入?yún)?shù)傳遞給存儲(chǔ)過程，問號(hào)(?)被用作一個(gè)占位符，用于將通過名稱為 TEMP_C 的輸出參數(shù)所返回的值。

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

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

效率和性能

SQL 存儲(chǔ)過程提供有效的方法將業(yè)務(wù)規(guī)則邏輯從應(yīng)用程序移動(dòng)到數(shù)據(jù)庫。通常，這種移動(dòng)帶來極大的性能提升，因?yàn)樵诜?wù)器上完成處理，并且必須通過網(wǎng)絡(luò)傳輸?shù)南⒏?。使?SQL 存儲(chǔ)過程保證在訪問數(shù)據(jù)庫的所有應(yīng)用程序中一致地實(shí)施業(yè)務(wù)規(guī)則。并且因?yàn)?SQL 存儲(chǔ)過程中的邏輯可以單獨(dú)修改，所以當(dāng)業(yè)務(wù)規(guī)則改變時(shí)不必重新編寫應(yīng)用程序。

不管是設(shè)計(jì)新的數(shù)據(jù)庫應(yīng)用程序還是只想簡化日常操作，都可以尋找機(jī)會(huì)使用 SQL 存儲(chǔ)過程。如果您發(fā)現(xiàn) SQL 存儲(chǔ)過程的開發(fā)和部署在您的工作中很有用，那么您可能想成為一名 IBM 認(rèn)證的 DB2 9.5 SQL 存儲(chǔ)過程開發(fā)人員。

【編輯推薦】

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