《高效能資料庫》第四講 程式設計細節（二）：預存程序、函數、交易、觸發器觀點

第四講 程式設計細節（下）

涵蓋：觸發器、游標、函數、預存程序、交易。作者決定先講預存程序（sp）再講函數（fn）。

4. 預存程序

預存程序是資料庫程式設計最重要表現形式。作者強烈建議用 sp 與級聯取代觸發器。SQL 2000 提供級聯刪除/修改後，觸發器失去存在理由，僅作為向下相容技術。觸發器缺點：

• 「地下」運行：除錯、管理環境差，調試耗時。
• 類似 goto：一表可寫多個同事件觸發器，維護極難，嵌套/遞歸觸發器極易造成死循環或非預期結果。

預存程式撰寫範本

CREATE PROC spBuyBook(
  @iBookID int,     -- 書本 ID
  @iOperatorID int  -- 操作員 ID
)
-------------------------------------------------------
-- Name    : spBuyBook
-- Func    : 購買一本書的業務邏輯
-- Return  : 0 正確; -1 找不到書; -2 更新 Book 表出錯 ...
-- Use     : spDoSomething, spDoSomething2 ...
-- User    : 懶蟲
-- Author  : 懶蟲 # SapphireStudio (www.chair3.com)
-- Date    : 2003-5-4
-- Memo    : 臨時範例，未調試
-------------------------------------------------------
AS
BEGIN
  BEGIN TRAN
  EXEC spDoSomething
  IF @@ERROR <> 0
  BEGIN
    ROLLBACK TRAN
    RAISERROR ('SQL Server,spBuyBook: 呼叫 spDoSomething 發生錯誤。', 16, 1) WITH LOG
    RETURN -1
  END
  -- 更多程式碼
  COMMIT TRAN
END

預存程序關鍵要素

• 參數：輸入、輸出、預設值。範例：CREATE PROC spTest(@i int=0, @o int OUTPUT) AS SET @o=@i*2；呼叫：DECLARE @o int; EXEC spTest 33, @o OUTPUT; SELECT @o（結果 66）。
• 變量：DECLARE @i int。
• 陳述式：T-SQL 擴充標準 SQL，含賦值（SET）、迴圈（WHILE）、分支（IF、CASE）。
• 返回值：CREATE PROC spTest2 AS RETURN 22；呼叫：DECLARE @i int; EXEC @i=spTest2; SELECT @i。
• 管理：CREATE/ALTER/DROP PROC。

進階程式設計要素

系統預存程序、系統表、例外處理、暫存表、動態 SQL、擴充預存程序、DBCC 命令（後續課程講解）。

3. 函數

SQL 2000 新功能。關鍵差異：fn 可嵌入 SELECT 陳述式，sp 不可。作者基本拋棄游標改用 fn（游標太耗資源）。fn 限制較多：

• 僅能呼叫確定性函數（不可用 GETDATE 等）。
• 不可用動態 SQL（EXEC、sp_executesql）。
• 不可呼叫擴充預存程序。
• 不可用 UPDATE 修改表。
• 不可在函數內建立/修改表。
• 凡不可預測後果、無法回傳結果者皆不可用。

函數範例

CREATE FUNCTION fnTest(@i int) RETURNS bit
AS BEGIN
  DECLARE @b bit
  IF (CAST(@i AS FLOAT)/2) = (@i/2) SET @b=1 ELSE SET @b=0
  RETURN @b
END

呼叫範例

CREATE TABLE #TT(fd1 int)
DECLARE @i int; SET @i=0
WHILE @i<=20 BEGIN INSERT #TT VALUES(@i); SET @i=@i+1 END
SELECT fd1, '是否雙數'=dbo.fnTest(fd1) FROM #TT
DROP TABLE #TT

5. 交易

交易將多步驟視為單一單位：要么全成功，要么全失敗回滾。銀行存款範例：交錢→填單→簽字→輸入電腦，任一步驟失敗全數回滾。

CREATE PROC sp我去存款(@M Money, @iOperator Int)
AS BEGIN
  DECLARE @i int
  BEGIN TRAN
  EXEC @i=sp交錢 @M,@iOperator
  IF @i<>0 BEGIN ROLLBACK TRAN; RAISERROR('...',16,1) WITH LOG; RETURN -1 END
  EXEC @i=sp填單 @M,@iOperator
  IF @i<>0 BEGIN ROLLBACK TRAN; RAISERROR('...',16,1) WITH LOG; RETURN -2 END
  EXEC @i=sp簽字 @M
  IF @i<>0 BEGIN ROLLBACK TRAN; RAISERROR('...',16,1) WITH LOG; RETURN -3 END
  EXEC @i=sp輸入電腦 @M,@iOperator
  IF @i<>0 BEGIN ROLLBACK TRAN; RAISERROR('...',16,1) WITH LOG; RETURN -4 END
  COMMIT TRAN
  RETURN 0
END

交易關鍵：BEGIN TRAN、@@ERROR、ROLLBACK TRAN、COMMIT TRAN。支援巢狀交易（需命名）。