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

---

在上一篇文章里已經介紹了SQL Server里的存儲過程標題，Joe會繼續談下存儲過程內容的話題。在這篇文章里，他會概況談下作為過程化語言的T-SQL的局限性，當決定如何使用它們時要記住那些。

在第一篇到第四篇，我們創建了表，架構的基礎和可視化。但我們還沒結束，因為架構不止這些。在一個真正的數據庫里，有更多的結構需要考慮。在這些其它架構層級外的東西是：游標，觸發器和存儲過程。還有其它像核對，翻譯，特權（collations, translations, privileges）和像這樣的東西。我只談這三個東西——游標，觸發器和存儲過程——我只用最常規的方式命名。5-SQL和其它產品可以有更高的專利，不管ANSI/ISO標準。理由很簡單：這些東西是建立在早期SQL產品使用的現有文件系統之上。這些過程化的結構是用來彌補早期產品聲明式代碼的缺陷。供應商有鎖在“代碼博物館”里的用戶，不會放棄他們的客戶基礎。

在第5篇，對于存儲過程，我們討論存儲過程標題有什么和它是如何工作的，就像一個黑盒子。在第6篇，我們到黑盒子里面看看。

過程化SQL

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

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

T-SQL是個一次通過的編譯器。這是你必須前置本地變量，使用@（@標志，“蝸牛（snail）”或“小蝸牛（petite escargot）”）的參數，@@是系統級的變量，#（井號）和##是臨時表。多通道編譯器創建符號表，然后用每個通道探索程序對象的東西。當它第一次找到它時，一次通過編譯器需要用新的符號來告知做什么。一旦它們傳過來，因此@表示“為我分配本地存儲”，@@表示“在程序外找我，對它我是全局的”，#表示“對于當前會話在tempdb里創建我”，##表示“在臨時表里創建并保持我”。其它的一起是假定在DDL里定義。

SQL不計算

你不能期望T-SQL來做過程化代碼的優化。那需要多通道。例如，大部分FORTRAN編譯器使用代數學寫入來進行計算上的優化。來自IBM的F和G系列可以給學生完整錯誤信息的快速編譯器，慢但可以為產成品優化性能。

1960年期間一個經典的IT故事是，IBM的國防部（DoD (Department of Defense) ）測試和通用計算機的FORTRAN編譯器。IBM編譯器運行了很長時間，生成一個壓縮的可執行模塊，當運行的時候，很快得出正確答案。通用編譯器運行了很長時間，生成了很小的可執行模塊，包含一個WRITE語句，立即打印出正確的答案。啥問題？問題是涉及函數和它們的取消退出逆轉。關鍵是浮點取整錯誤。通用FORTRAN編譯器成功配對函數和它們的逆向，完成代數并并以常量生成答案。

在T-SQL里避免浮點數和實數。在T-SQL里有同樣的數據類型，但在標準SQL里沒有。問題是浮點數需要特定來處理避免取整錯誤和比較。有個它們需要調用近似數字數據類型的特定原因。這個特定處理需要要么是軟件內建的，要么是硬件的一部分，這就說你需要浮點處理器。同樣，你的老板不會為你的桌面安裝游戲顯卡。商業應用服務器通常不需要這些昂貴的功能，不管你在工作的時候花多少時間在玩Halo或Doom游戲上。

即使芯片便宜，你也不能合理期望期望T-SQL來做數學上優化的事。SQL是個數據檢索和管理語言，不是用來計算的。你想要的是寫出傳給統計軟件包來獲得數據的好查詢，一個報表或其他特定工具。

如果需要十進制的地方，那么就使用DECIMAL數據類型。它們可以很好處理。竅門就是給你自己足夠的十進制控件來獲得正確的整數。那意味著你需要知道你行業的標準。尤其是，如果你用歐元，你需要知道“歐元三角（euro triangulation）”，貨幣轉換和記賬規則。

最好親自做下代數，讓算法盡可能簡單。這樣建議也適用于字符和時間數據。

T-SQL有基于C的函數庫。這是為什么可以使用%來代替標準mod函數的原因。

SQL不用來顯示

再次強調，SQL是數據檢索和管理語言，不是用來做前端顯示的。在SQL數據類型里一起拿到數據，把它們“FQ（over the wall）”傳給前端程序，例如報表編輯器和圖形包，這樣看起來會更好。

但是因為過程語言是焊接到它們的文件，程序員寫單片程序成長起來。COBOL只是字符串和顯示模板。FORTRAN有它自己的格式化語句。BASIC版本有使用#和其它符號的圖片選項。即使像C的低級語言，在它的printf函數里有精確的格式化選項！

長期的過程化語言編程后，對于很多程序員，分層的概念非常困難。事實上，在現在，你還是可以聽到抗議：“在數據庫我就可以完整這個并節約時間”。

有時候拿是對的。但大多時候，這不會節約。顯示格式化會從在基本列上使用索引阻止優化器。前端然后會拆回格式化列到它們的源數據或另一個格式。比起在它們的列里有基本數據類型的簡單列，真正的損失是這更難維護。

讓我給你2個常見的例子。使用專門的CONVERT()函數把時間數據轉為字符來顯示。讓程序為你做這個；它們有函數庫來做這個。你不用擔心國家設置或正確的取整（可以是通過程序設計決定的程序）。當你有DATEPART（）和CAST（）時，CONVERT（）的最壞使用是對字符處理。可以看下兩個日期轉為字符串，然后比較字符串。

第2個常見例子是從姓和名組合為姓名。這會阻止在姓列上的索引使用，會給前端帶來可用空間和規則的重格式化問題。你會看到翻轉名字順序（名，姓）的前端代碼

基本聲明式編程啟發法（Basic Declarative Programming Heuristics）

結構化編程實際上有修正性的數學證據。你可以且應該看下Dijkstra, Wirth and Manna。這實際上是會幫你編程的理論。聲明式編程還沒到那個點。但可以給你寫啟發。當你看到一個特定情形時可以嘗試些事情；它們不是宇宙法則，就像精明投資者的押注。

關于這個話題有2個系列（看下下面參考文章）可以給你過程化的例子，半過程化和聲明式編程風格。但現在，讓我給你有幫助的“高水平提示”的快速清單。

傾向一句頂多句

在一個沒有使用T-SQL流程控制的一個SQL語句里，你可以做的更多工作，代碼越好，工作越順。因此，如果你的存儲過程主體有兩個或更多引用到同個表，你大可以組合它們并一次訪問那個表。

你可以使用CASE表達式來避免很多的IF-THEN-ELSE控制邏輯。在CASE表達式前，是應用邏輯到SQL的表達式。經典的例子是多年來Sybase/SQL服務器類一部分的UPDATE語句。你有個書店，想修改書的價格。超過25美元的書上漲10%（這個會做廣告），低于25美元打85折（這個不會做廣告）。

經典的在偽代碼里，結構化編程的答案如下：

BEGIN OPEN FILE (Bookstore); READ (price) FROM Bookstore; WHILE NOT EOF (bookstore) DO BEGINIF price < 25.00THEN UPDATE Books SET price = price * 1.10ELSE UPDATE Books SET price = price * 0.85;FETCH NEXT Bookstore;END IF; END WHILE; CLOSE FILE (Bookstore); END:

很容易把偽代碼準換為游標。純粹的SQL語句會如下:

BEGIN UPDATE BooksSET price = price * 1.10WHERE price < 25.00; UPDATE BooksSET price = price * 0.85WHERE price >= 25.00; END;

但這不對！如果一本書現在售價是24.95美元。當第一個UPDATE語句執行后，會是27.45美元。但當我們執行第2個UPDATE時，最后的價格會是23.33美元。這不是我們想要的。交換下UPDATE語句也沒用；在頂部的書會更新2次。

這是對游標的經典異議。在那些日子里，對于這類問題，我們有各類可怕的多個表掃描存儲過程。現在，我們有了CASE表達式，它是聲明式，做一次表掃描。

UPDATE BooksSET price= CASEWHEN price < 25.00THEN price * 1.10ELSE price * 0.85END;

這個啟發式有個部分：

在多個語句里查找出現的同樣表；它們是可以組合一起的。

如果用IF-THEN-ELSE控制語句的話，可以在單個語句里用CASE表達式替換分支。

同樣的啟發式適用于INSERT INTO語句。這個的一個格式是插入初始的一些行，隨后是選擇的一些行。結構如下：

1 INSERT INTO Foobar (..) VALUES(..); 2 INSERT INTO Foobar SELECT .. FROM.. WHERE..;

也可以寫成這樣：

1 INSERT INTO Foobar (..) 2 (SELECT X.* FROM (VALUES (..)) AS X) 3 UNION ALL 4 SELECT .. FROM.. WHERE..;

當然CASE表達式也可以用在SELECT語句里。

或許這個啟發式的最佳例子是MERGE語句，可以讓你把INSERT和UPDATE組合為一個語句。這里我不會討論它，但強烈建議你看下它。

避免本地變量

T-SQL必須分配本地本地變量，它們經常是不需要的。一個常見的模式：

1 CREATE FUNCTION Foobar (..) 2 RETURNS <data type> 3 AS 4 BEGIN 5 DECLARE @local_return_holder <data type>; 6 SET @local_return_holder 7 = <scalar query>: 8 END;

可以更簡單：

1 CREATE FUNCTION Foobar (..) 2 RETURNS <data type> 3 AS 4 BEGIN 5 RETURN (<scalar query>); 6 END;

本地變量的其他缺點它們會從優化器隱藏表達式。

BEGIN SET @local_x = (<scalar query>); -- has to load local variable .. <statement using @local_x>; END;

可以是：

BEGIN .. <statement using (<scalar query>)>; --optimizes whole expression END;

你也可以嵌套調用函數，不用在本地變量里的直接值逐步處理。這個的最好例子是REPLACE（）的如下系列調用：

SET @x = REPLACE (@x, 'a', 'A'); SET @x = REPLACE (@x, 'B', 'b'); ETC

使用REPLACE (REPLACE..(REPLACE (@x, 'z', 'Z') ..))最多你可以32層。

對此概念有問題，你可以和LISP程序員談下。這個語言只有嵌套函數調用。

傾向JOIN非Loop

有很多其他技巧可以避免逐行處理。例如，不用說太多，for循環通常可以用join到系列表（Series table）來代替。系列表（Series table）是來一個到上限的一系列整數。

尋找應該在DDL里的東西

在存儲過程里IF-THEN邏輯的使用在運行時清理數據，這是你真的在DDL里需要CHECK（），在第一時間就阻止出錯。例如：

1 SET T.x = COALESCE (T.x, 0); 2 IF (x > 12)..;

這是你需要你在一些列上有默認值和約束的標志。在表里修改“x INTEGER”如下：

CREATE TABLE T (..x INTEGER DEFAULT 0 NOT NULLCHECK (x BETWEEN 0 AND 12), ..);

避免CLR和XML混用

保持外部語言在架構之外。不添加其他語言來混合的SQL已經很難維護。當你在語句里找到一個CLR模塊你不知道，你會怎么辦？它們不會遵循例如MOD（），SUBSTRING（）和算術取整等同樣的定義。最好的例子是C#和VB之間的區別，2個微軟專屬語言在布爾值表達上卻是一致的。

參考文章：

https://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programming-in-sql/

https://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programing-part-ii/

原文鏈接：

http://www.sqlservercentral.com/articles/Stairway+Series/70950/

總結

以上是生活随笔為你收集整理的数据库设计（6/9）：存储过程主体的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。