網咖收費系統資料庫設計實戰：存儲過程、觸發器、視圖與函數綜合應用

專案背景

第三次重構網咖收費系統，負責資料存取層（DAL）、介面層與工廠層。需求已極度熟悉，資料庫設計得心應手。本次重構亮點在於大量採用預存程序封裝複雜查詢，並吸取教訓克制使用觸發器，改用「事務+預存程序」保障資料一致性。

核心設計模式

1. 預存程序封裝動態組合查詢

場景：UI 提供 3 條件行、每行含欄位、運算子、值、邏輯連接（AND/OR），共 12 參數，針對 3 張不同表。

做法：將共用 SQL 拼接邏輯封裝為單一預存程序 Proc_GroupQuery，DAL 僅傳參數並轉換回對應實體集合。

-- 關鍵片段：動態拼接 WHERE 子句
SET @Sql = 'SELECT * FROM ' + @tablename + ' WHERE ' + @ziduan1 + @fuhao1 + ''' + @tiaojian1 + '''
IF @zuhe1 != '' SET @Sql = @Sql + ' ' + @zuhe1 + ' ' + @ziduan2 + @fuhao2 + ''' + @tiaojian2 + '''
ELSE EXEC(@Sql)
-- 以此類推第三行條件
EXEC(@Sql)

優勢：消除 DAL 層大量重複 SQL 字串拼接，降低注入風險（參數化），修改邏輯只動一處。

2. 觸發器輔助結帳狀態同步（慎用）

場景：結帳時需同步更新 T_Card、T_Recharge、T_Cancelcard 三表的 IsCheck 欄位。

CREATE TRIGGER [dbo].[Check] ON [dbo].[T_Check] FOR INSERT AS
BEGIN
  UPDATE T_Card SET IsCheck='已結帳' WHERE Carddate=CONVERT(varchar(10),GETDATE(),120)
  UPDATE T_Recharge SET IsCheck='已結帳' WHERE Rechargedate=CONVERT(varchar(10),GETDATE(),120)
  UPDATE T_Cancelcard SET IsCheck='已結帳' WHERE Canceldate=CONVERT(varchar(10),GETDATE(),120)
END

教訓：早期濫用觸發器導致「充值 10 元卻加雙倍金額」的嚴重 Bug。觸發器隱蔽、難追蹤、易產生級聯副作用。原則：能用「事務+預存程序」顯式控制的，絕不寫觸發器。

3. 純量函數計算當日營收

利用子查詢與聚合函數單次計算：期初餘額 + 當日充值總額 - 當日退卡總額。

SELECT t1.remaincash + t2.alladdmoney - t3.allcancelcash
FROM (SELECT TOP 1 CAST(Allcash AS INT) remaincash FROM T_Check ORDER BY CheckID DESC) t1,
     (SELECT SUM(CAST(Addmoney AS INT)) alladdmoney FROM V_TodayRecharge) t2,
     (SELECT SUM(CAST(Cancelcash AS INT)) allcancelcash FROM V_TodayCancel) t3

4. 視圖簡化當日明細查詢

CREATE VIEW V_TodayRecharge AS
SELECT CardID, Addmoney, Rechargedate, Rechargetime, UserID
FROM T_Recharge
WHERE Rechargedate = CONVERT(varchar(10), GETDATE(), 120)

視圖封裝「當日」篩選邏輯，預存程序與報表直接查視圖，避免重複 WHERE 條件。

架構心得

1. 預存程序是業務邊界的守門員：將複雜 SQL、動態邏輯、權限控制收斂於資料庫層，應用層只呼叫介面。
2. 觸發器是「核武器」：威力大但不可控，僅限於跨表同步無法由應用發起的極少數場景，且必須嚴格測試。
3. 視圖與函數是可複用的查詢積木：將常用篩選、計算邏輯標準化，減少重複代碼與不一致風險。
4. 事務是數據一致性的底線：多表寫入必須包裹在顯式事務中，而非依賴觸發器隱式維護。

總結

資料庫設計不只是建表、加索引。善用預存程序封裝業務、克制觸發器、活用視圖/函數、堅持事務邊界，才能交付可維護、高效能、低 Bug的資料層。這第三次重構，終於把這些道理寫進代碼裡了。