對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO SQL標準委員會工作了10年，為SQL-89和SQL-92標準做出了傑出貢獻。

 Joe Celko會談下資料庫設計里存儲過程和它的位置。他所寫的是令人深思的東西，即使是有經驗的資料庫開發人員。

在第一篇，我們因它們是什麼並區分它們命名數據元。在第二篇，我們用SQL里給我們的數據類型和簡單的行或列約束來模型化數據元。在第三篇，我們把這些行放入表成為實體，關係和輔助數據。在第四篇，我們有了基礎表，把它們連接在一起，增加從索引，並創建了視圖。

這應該會給你萬事俱備，卻是錯誤的感覺。在一個非常簡單的資料庫里，這是對的。但在一個真正的資料庫有更多的架構來考慮。在這些架構外的其他東西事：游標（cursors）、觸發器（triggers）和存儲過程（stored procedures）。有更多像整理、翻譯，特權和諸如此類的東西。我將只處理這3個東西——游標（cursors）、觸發器（triggers）和存儲過程（stored procedures）——我只用通常的方式命名。儘管ANS/ISO標準里，T-SQL和其他產品可以獲得更高的占有欲。理由很簡單：這些東西建立在早期SQL產品使用的現有文件系統上。這些程式結構是用來彌補在早期產品里缺少申明式代碼。這些供應商有鎖在今天“代碼博物館”不能地址他們客戶群的用戶。

存儲過程名稱（Procedure Headers）

SQL允許存儲過程代碼模塊在架構里保存。同時在標準SQL里有SQL/PSM語言，你會使用像T-SQL的專門語言。這些語言通常是Algol家族的成員；那就是說他它們有IF-THEN-ELSE，WHILE迴圈和有BEGIN-END作用域的代碼塊。

這些專用語言的大多數從未想用做程式開發。對於T-SQL的首要規則（The rules of thumb）是不寫超過50行的的過程，且不使用PRINT。但事實上，你可以避免所有的面向過程，每個表像文件和代碼一樣對待，好像資料庫是個過程化的文件系統。如果你喜歡疼痛，大可敲個釘子到你身體，所以不用糾結。

存儲過程的目的更像個視圖。它們授予所有用戶在所有時間會用同樣的方式做同樣的工作。視圖封裝了一個查詢並給它一個名稱，因此創建了一個虛擬表。存儲過程用同樣的方式封裝了UPDATE，INSERT，DELETE和SELECT，但增加了參數。

在存儲過程名稱里一個參數（parameter ）就是個“持有人（place holder）”，參數值（argument ）是傳給存儲過程的實際值。T-SQL參數過去只限制於簡單的標量值。現在，它們可以是表值和XML字元。讓我們從簡單的標量參數開始。

編譯器讀取參數值，並檢查數據類型，範圍和確保它是有效的其他事項。編譯器會做比你想象還多的事。看下這個T-SQL：

1 SELECT ISDATE ('2010-01-01'); -- TRUE
2 SELECT CAST ('2010-01-01' AS DATE); -- no problem
3 SELECT ISDATE ('010-01-01'; --TRUE
4 SELECT CAST ('010-01-01' AS DATE); -- error

同事，浮點表示法有點意思。如果你以DECIMAL或FLOAT轉化指數計數法，肯定沒問題：

1 SELECT CAST (62.3E8 AS DECIMAL(18,5)); -- returns 6230000000.00000
2 SELECT CAST (62.3E8 AS FLOAT); -- returns 6230000000

但現在把字元串的指數計數法，嘗試轉化它為DECIMAL或FLOAT，你肯定會出現問題：

1 SELECT CAST ('62.3E8' AS DECIMAL(18,5)); -- error
2 SELECT CAST ('62.3E8' AS FLOAT); -- returns 6230000000

如果轉化字元，你要用這樣的指數代碼：

1 SELECT CAST(CAST ('62.3E8' AS FLOAT) AS DECIMAL(18,5)); -- 6230000000.00000

現在嘗試傳這些測試值作為參數，看看它們的結果：

1 CREATE PROCEDURE Test
2 (@in_test_date DATE,
3  @in_test_decimal DECIMAL(18,5))
4 AS
5 BEGIN
6 SELECT @in_test_date, @in_test_decimal;
7 END;

EXEC Test '2010-01-01', 62.3E8;

返回值（2010-01-01, 6230000000.00000）和預期的一樣。

你不能傳表達式作為參數值，但你可以傳本地變數，它是表達式值預先設置的。這就是說這個會報錯：

EXEC Test '2010-01-01', 62.3 * POWER(10, 8) ;

但這個會成功執行：

1 BEGIN
2 DECLARE @local_decimal DECIMAL (18,5);
3 SET @local_decimal = 62.3 * POWER(10, 8);
4 EXEC Test '2010-01-01', @local_decimal ;
5 END;

你會看到新的SQL編程會嘗試傳遞XML或CSV（逗號分割值（Comma Separated Values））列表字元作為參數值。它們增加瞭解析器的負擔（XML解析器或任何寫出來用來分割CSV字元的任何代碼）並讓它們的數據完整性受到危險。SQL Server可以處理超過2000的參數值，對於現實中的情況已經足夠能應付。

在參數列表裡另一個未使用的功能是預設值。這個語法非常簡單。參數聲明後一個“=”和一個合適的定值。

如果參數值沒有提供，就會使用預設值。

CREATE PROCEDURE Test
(@in_test_date DATE = '2010-01-01',
 @in_test_decimal DECIMAL(18,5) = 0.00000
)
AS
BEGIN
SELECT @in_test_date, @in_test_decimal;
END;

除非你特別分配參數值到參數，它們是從左到右的順序分配。

EXEC Test; -- returns (2010-01-01, 0.00000)
EXEC Test '2010-12-25' -- returns (2010-12-25, 0.00000)
EXEC Test @in_test_date = 789; -- returns (2010-01-01, 789.00000)

最後，參數可以用作輸出。這就是說它需要在調用的模塊里有個本地變數，這樣的話，返回值才有地方可去。這是展示這個語法的例子：

 1 CREATE PROCEDURE Test
 2 (@in_test_date DATE OUTPUT,
 3  @in_test_decimal DECIMAL(18,5) OUTPUT)
 4 AS
 5 BEGIN
 6 SET @in_test_date = '2010-12-25';
 7 SET @in_test_decimal = 789;
 8 END; 
 9 
10 BEGIN
11 DECLARE @local_date DATE;
12 DECLARE @local_decimal DECIMAL(18,5);
13 SELECT @local_date, @local_decimal; --returns (NULL, NULL)
14 EXEC Test @local_date OUTPUT, @local_decimal OUTPUT
15 SELECT @local_date, @local_decimal; -- returns (2010-12-25, 789.00000)
16 END;

軟體工程基礎

關於存儲過程標題就講這些；那存儲過程的具體內容呢？嗯，我們現在暫時不講。我們先講下原則讓，然後再看看特定的工具。我們需要一個高度來看如何編寫代碼——軟體工程（Software Engineering）。

軟體工程的基礎不在SQL里修改。但現實完全不一樣。我們大多數（從學LISP,APL,FP,Haskell或其它外來語言學起，對這些程式員例外）學過從Algol-60進化而來的結構化編程語言。適用於過程化語言的原則同樣適用於SQL存儲過程。

在近1970年，我們發現我們可以在程式里寫出更好（更快，正確，更易維護的）的代碼，在代碼里有本地代碼塊規則和代碼模塊，都是一個入口一個出口。我們避免GO TO語句，並使用簡單的一系列控制結構。這是結構化編程的進步。

內聚度（Cohesion）

內聚度是一個模塊做且只做一件事會很好：那是邏輯上的內聚性。模塊應該高內聚。模塊的命名格式應該是“<動作><對象>”，這裡“<對象>”是數據模型里特定的邏輯單元，“<動作>”是單一明確的行動。有很多內聚類型。我們從最差到最好對它們排名。

1. 偶然內聚(coincidental cohesion)是模塊部分任意分組。一個偶然模塊無關行為的火車殘骸。它是“Lady GaGa，魷魚和汽車”模塊，它們的描述會是複合且複雜的句子。這個在SQL里的最佳例子是OTLT（查詢表（One True Lookup Table））設計缺陷。這是它裡面有對整個實體架構的查詢表。
2. 邏輯性內聚(logical cohesion)是模塊部分按它們邏輯上做同樣的事進行分組，即使本質上它們是不同的。在SQL里最常見的例子是在任何表上進行更新、插入或刪除的通用模塊——在“Lady GaGa，魷魚和汽車”上。用動態SQL，XML和CLR進行實現。
   
3. 時間性內聚(temporal cohesion)是模塊部分按它們處理時分組。例如，對於整個系統，進行所有初始化工作的模塊。
   
4. 過程性內聚(procedural cohesion)是模塊部分是因為它們總是緊跟特定操作順序。例如，當一個用戶登錄到資料庫，我們會檢查用特權可並記錄登錄。不把這些事放在各個模塊里，在上層的控制下，我們寫集成的一塊來完成這一切。
5. 通信性內聚(communicational cohension)是模塊部分因它們在同樣的資料庫元上操作進行分組。例如在一個存儲過程里，UPDATE的一系列語句影響同樣列，基於先前模塊做的事。
   
6. 順序性內聚(sequential cohesion)是模塊部分因為從一個部分的輸出是另一個部分的輸入，像流水線一樣。在SQL里，可以看下臨時表的使用，在磁帶文件系統里用來替換。
   
7. 功能性內聚(functional cohesion)是模塊只做一件定義明確的工作，像一個數學函數。這才是我們模塊里想要的，它是功能性編程語言的基礎。沒有副作用或外部數據。
   

如果在你的軟體工程課程里錯過這些，你可以網上找下它們的具體定義。

耦合度（Coupling）

耦合度是模塊之間的相互獨立性。如果你的模塊需要特定的順序執行，它們是強耦合度。如果它們之間可以獨立運行，可以像樂高一樣堆積，它們是鬆散或弱耦合的。耦合有好幾類，從低到高排序是：

1. 內容耦合（Content coupling）是一個模塊的修改依賴於另一個模塊的內部操作。控制模塊的跳入與調出。在SQL里，用戶自定義函數（UDF）的使用和CLR是最好的例子。直到運行的時候你才知道會發生什麼。
2. 公共耦合(Common coupling)是兩個模塊共用同樣的全局數據。這聽起來像SQL里的表，但它指的是在公共數據上有2個做同樣工作的方法。做同樣的工作很快變成幾乎一樣的工作。
3. 外部耦合（External coupling）是兩個模塊共用一個外部強加的數據格式，通信協議，或設備介面。理想的，我們不需要擔心外部環境。這是在資料庫里，分層架構不做任何形式或包裝。
4. 控制耦合（Control coupling）是一個模塊通過傳遞標記控制執行。直到它的主模塊告訴它，你不知道這個模塊會做什麼。這也是在SQL編程里，使用BIT標記不好的一個原因。
5. 標記耦合（Stamp coupling）是模塊共用複合數據結構，且只使用它的一部分。在SQL里可以使用視圖實現。
6. 數據耦合（Data coupling）是模塊共用簡單數據元。想下傳遞參數；這是只共用的數據元。
7. 非直接耦合（Message Coupling）是最寬鬆的耦合類型。模塊不相互依賴，它們使用公共介面來交換少參數的信息。這更多是面向對象的方法，但你會在觸發器、異常處理和其他SQL特性里看到它。

這個在我的《SQL 編程風格》書里關於存儲過程編寫的章節里會有簡單的介紹。同時，你也可以閱讀下DeMarco, Yourdon, Constantine, Myers或其它軟體工程先驅。這已經不是簡單的SQL編程了。在你寫任何語言的代碼前，這些都是你應該知道的。

好的SQL存儲過程

一般而言，好的存儲過程是高內聚，低耦合，它不使用控制結構的缺陷，除非是必須的。對於過程化開發人員，這個是意外。理由是儘可能多的把“編程的元素”放入單純的SQL，這樣優化器可以更好的處理代碼。

如何實現？下篇會告訴你。

原文鏈接：

http://www.sqlservercentral.com/articles/Stairway+Series/Procedures+in+Database+design/70891/