數(shù)據(jù)庫設(shè)計（6/9）：存儲過程主體

quasiceo 2016-07-06

展開全文

數(shù)據(jù)庫設(shè)計（6/9）：存儲過程主體

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

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

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

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

過程化SQL

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

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

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

SQL不計算

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

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

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

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

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

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

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

SQL不用來顯示

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

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

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

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

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

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

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

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

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

傾向一句頂多句

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

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

經(jīng)典的在偽代碼里，結(jié)構(gòu)化編程的答案如下：

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

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

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

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

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

 UPDATE Books
   SET price
       = CASE
         WHEN price < 25.00
         THEN price * 1.10
         ELSE price * 0.85
         END;

這個啟發(fā)式有個部分：

在多個語句里查找出現(xiàn)的同樣表；它們是可以組合一起的。
如果用IF-THEN-ELSE控制語句的話，可以在單個語句里用CASE表達式替換分支。

同樣的啟發(fā)式適用于INSERT INTO語句。這個的一個格式是插入初始的一些行，隨后是選擇的一些行。結(jié)構(gòu)如下：

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語句里。

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

避免本地變量

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

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;

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

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;

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

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

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

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

傾向JOIN非Loop

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

尋找應(yīng)該在DDL里的東西

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

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

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

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

避免CLR和XML混用

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