計實戰(zhàn)：從批量結(jié)算卡死到四類過程組織)
簡介這份資源面向SQL Server數(shù)據(jù)庫初學(xué)者與需要鞏固存儲過程編寫的開發(fā)人員圍繞存儲過程這一核心主題提供可直接參考的實例代碼幫助讀者理解預(yù)編譯SQL語句集合在性能優(yōu)化、代碼復(fù)用與權(quán)限控制方面的實際用法。壓縮包共3個文件均為sql腳本整體約4KB體積輕量便于快速查閱其中既有供應(yīng)鏈管理場景下的報表類存儲過程也包含用于生成下一批次號的用戶自定義函數(shù)覆蓋參數(shù)傳遞、結(jié)果返回等典型寫法。資源已有2023人學(xué)習(xí)下載說明其在入門與查閱場景中具有一定參考價值。讀者可從中獲得存儲過程與自定義函數(shù)的完整定義范例對照學(xué)習(xí)CREATE PROCEDURE、EXEC調(diào)用及輸入?yún)?shù)的使用方式并借鑒業(yè)務(wù)報表與序列號生成兩類實際場景的編寫思路為后續(xù)掌握事務(wù)控制、異常處理等進階內(nèi)容打下基礎(chǔ)。1. 從一次批量結(jié)算卡死說起為什么存儲過程值得單獨設(shè)計某公司做內(nèi)部結(jié)算系統(tǒng)月初跑批時一條更新語句鎖了整張明細表前臺查詢?nèi)颗抨犞蛋嗤露⒅顒颖O(jiān)視器等了三分鐘才等到它自己解開。事后復(fù)盤發(fā)現(xiàn)問題不在 SQL 寫得對不對而在于業(yè)務(wù)邏輯散落在應(yīng)用層幾十條語句各自開事務(wù)、各自提交中間任何一步失敗都留下半截數(shù)據(jù)。這類場景正是存儲過程的主場把一組需要原子完成的數(shù)據(jù)庫操作收進一個命名單元由數(shù)據(jù)庫自己保證執(zhí)行順序和事務(wù)邊界。這份 SQLSERVER 存儲過程例子面向的讀者很明確——正在做數(shù)據(jù)庫課程設(shè)計、需要交一份完整數(shù)據(jù)庫方案的學(xué)生以及要給內(nèi)部系統(tǒng)補一套可維護數(shù)據(jù)訪問層的從業(yè)者。它不教你 SQL 語法入門而是給出一套能直接照著搭、照著改的存儲過程組織方式。2. 需求拆解與總體設(shè)計四類存儲過程覆蓋一套業(yè)務(wù)閉環(huán)2.1 先把需求翻譯成存儲過程清單拿到“存儲過程例子”這個題目最容易翻車的做法是上來就寫代碼寫完發(fā)現(xiàn)增刪改查各寫了一個彼此之間沒有關(guān)系答辯時被問“你的業(yè)務(wù)閉環(huán)在哪”就答不上來。我一般的做法是先列業(yè)務(wù)動作再把動作映射成過程。以一個典型的訂單與庫存場景為例動作可以拆成四類類別典型動作對應(yīng)存儲過程關(guān)鍵約束查詢類按條件分頁查訂單、查庫存余量usp_Order_Query、usp_Stock_Get只讀、不鎖表、支持分頁參數(shù)寫入類下單、扣庫存usp_Order_Create單事務(wù)、庫存不足即回滾批處理類月度結(jié)算、超時訂單關(guān)閉usp_Settle_Monthly分批提交、可重入維護類清理日志、重建索引usp_Maint_CleanLog限定時段、帶執(zhí)行記錄這張表就是整個方案的骨架。四類過程職責(zé)不同寫法也不同查詢類追求執(zhí)行計劃穩(wěn)定寫入類追求事務(wù)邊界清晰批處理類追求可中斷可續(xù)跑維護類追求可審計。把這張表先定下來后面每個過程該長什么樣基本就定了不會出現(xiàn)“寫了一堆過程但說不清各自定位”的尷尬。2.2 命名規(guī)范與參數(shù)約定命名不統(tǒng)一是后期維護的血淚來源。方案里統(tǒng)一采用usp_模塊_動作的前綴式命名usp表明是用戶存儲過程模塊名對應(yīng)業(yè)務(wù)域動作名用動詞開頭。參數(shù)上定三條硬規(guī)矩輸入?yún)?shù)一律帶前綴且類型與列類型嚴(yán)格一致輸出參數(shù)統(tǒng)一用RowsAffected INT OUTPUT回傳影響行數(shù)所有過程第一個可執(zhí)行語句之前先做參數(shù)校驗。類型不一致看著是小事實際會讓索引失效——比如列是INT而參數(shù)傳了BIGINTSQL Server 可能走隱式轉(zhuǎn)換執(zhí)行計劃直接從 Seek 退化成 Scan數(shù)據(jù)量一大就是災(zāi)難。2.3 分層結(jié)構(gòu)過程只做編排不做展示總體結(jié)構(gòu)上把存儲過程定位成“數(shù)據(jù)訪問層的編排者”而不是“什么活都干的萬金油”。應(yīng)用層負(fù)責(zé)參數(shù)組裝和結(jié)果展示存儲過程負(fù)責(zé)事務(wù)、約束校驗和批量操作視圖和函數(shù)負(fù)責(zé)可復(fù)用的查詢片段。這樣分層的好處是業(yè)務(wù)規(guī)則變更時只改過程不動應(yīng)用代碼查詢邏輯復(fù)用時抽成內(nèi)聯(lián)表值函數(shù)過程里直接JOIN調(diào)用比復(fù)制粘貼一段 SQL 可靠得多。方案里通常還會約定一個統(tǒng)一的錯誤碼表過程內(nèi)部用THROW拋出帶編號的錯誤應(yīng)用層按編號做提示避免把數(shù)據(jù)庫原始報錯直接甩給用戶。3. 核心存儲過程實現(xiàn)從單條寫入到批量結(jié)算3.1 寫入類過程事務(wù)、校驗與錯誤處理三件套下單扣庫存是最能體現(xiàn)存儲過程價值的一段。它要同時完成“插訂單頭、插訂單明細、扣庫存、寫流水”四步任何一步失敗都必須整體回滾。下面這段是方案里的核心實現(xiàn)CREATE OR ALTER PROCEDURE usp_Order_Create OrderNo VARCHAR(32), SkuId INT, Qty INT, RowsAffected INT OUTPUT AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 任何運行時錯誤自動回滾整個事務(wù) -- 參數(shù)校驗前置避免帶著臟數(shù)據(jù)進事務(wù) IF Qty IS NULL OR Qty 0 THROW 50001, 下單數(shù)量必須大于 0, 1; BEGIN TRY BEGIN TRANSACTION; -- 條件更新扣庫存庫存不足時影響行數(shù)為 0直接判定失敗 UPDATE dbo.Stock SET Qty Qty - Qty WHERE SkuId SkuId AND Qty Qty; IF ROWCOUNT 0 THROW 50002, 庫存不足或商品不存在, 1; INSERT INTO dbo.OrderHeader (OrderNo, SkuId, Qty, CreatedAt) VALUES (OrderNo, SkuId, Qty, SYSDATETIME()); INSERT INTO dbo.StockFlow (SkuId, ChangeQty, RefOrderNo, CreatedAt) VALUES (SkuId, -Qty, OrderNo, SYSDATETIME()); COMMIT TRANSACTION; SET RowsAffected 1; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRANSACTION; THROW; -- 原樣拋出保留錯誤號供應(yīng)用層判斷 END CATCH END;這段代碼有三個設(shè)計點值得說清楚。第一SET XACT_ABORT ON讓嚴(yán)重級別 16 以上的錯誤自動回滾省去在 CATCH 里逐條判斷的麻煩但 CATCH 里的XACT_STATE()判斷仍然保留因為有些錯誤不會自動回滾屬于兜底。第二扣庫存用“條件更新 判斷ROWCOUNT”而不是“先 SELECT 再判斷再 UPDATE”后者在并發(fā)下存在檢查與執(zhí)行之間的窗口兩個會話可能同時讀到足夠庫存然后都扣成功這是最經(jīng)典的并發(fā)翻車點。第三THROW不帶分號結(jié)尾在部分版本會報語法錯寫的時候養(yǎng)成加分號的習(xí)慣。3.2 查詢類過程分頁與參數(shù)嗅探的平衡查詢類過程最容易寫成一坨動態(tài)拼接的字符串參數(shù)一多就拼出注入風(fēng)險。方案里統(tǒng)一用參數(shù)化分頁排序字段通過白名單映射不直接拼進 SQLCREATE OR ALTER PROCEDURE usp_Order_Query PageIndex INT 1, PageSize INT 20, SkuId INT NULL, SortKey VARCHAR(20) CreatedAt AS BEGIN SET NOCOUNT ON; -- 排序字段白名單杜絕把用戶輸入直接拼進 ORDER BY DECLARE OrderBy VARCHAR(40) CASE SortKey WHEN Qty THEN Qty DESC WHEN CreatedAt THEN CreatedAt DESC ELSE CreatedAt DESC END; SELECT OrderNo, SkuId, Qty, CreatedAt FROM dbo.OrderHeader WHERE (SkuId IS NULL OR SkuId SkuId) ORDER BY CASE WHEN OrderBy Qty DESC THEN Qty END DESC, CASE WHEN OrderBy CreatedAt DESC THEN CreatedAt END DESC OFFSET (PageIndex - 1) * PageSize ROWS FETCH NEXT PageSize ROWS ONLY; END;這里用CASE表達式實現(xiàn)動態(tài)排序代價是執(zhí)行計劃可能不如直接拼字符串精確但換來的是計劃緩存可復(fù)用、無注入風(fēng)險。數(shù)據(jù)量在百萬級以內(nèi)這個折中完全夠用如果表更大通常的做法是把排序字段拆成幾個獨立分支每個分支一條靜態(tài) SQL讓優(yōu)化器各自生成計劃。另外(SkuId IS NULL OR SkuId SkuId)這種寫法在參數(shù)為 NULL 時可能引起全表掃描方案里建議對高頻查詢單獨建一個不帶該條件的版本用應(yīng)用層判斷走哪條別指望一個過程包打天下。3.3 批處理類過程分批提交與可重入月度結(jié)算這類過程動輒處理幾十萬行一次性大事務(wù)會把日志撐爆、鎖持有時間過長。方案里的做法是循環(huán)分批每批固定行數(shù)批間提交并用一張執(zhí)行記錄表標(biāo)記進度保證中斷后能續(xù)跑CREATE OR ALTER PROCEDURE usp_Settle_Monthly BatchSize INT 5000 AS BEGIN SET NOCOUNT ON; DECLARE Done INT 0; WHILE 1 1 BEGIN BEGIN TRANSACTION; UPDATE TOP (BatchSize) dbo.OrderHeader SET Status Settled WHERE Status Pending AND CreatedAt DATEADD(DAY, -30, SYSDATETIME()); SET Done ROWCOUNT; COMMIT TRANSACTION; IF Done 0 BREAK; -- 沒有待處理數(shù)據(jù)退出循環(huán) -- 批間短暫讓出降低對在線查詢的影響 WAITFOR DELAY 00:00:00.050; END END;TOP (BatchSize)配合變量在 SQL Server 里是合法的但要注意它和UPDATE ... FROM的寫法差異寫錯會變成更新全表。WAITFOR DELAY那 50 毫秒不是玄學(xué)是給其他會話搶鎖的機會批處理跑在業(yè)務(wù)時段時這點讓步很值?？芍厝氲年P(guān)鍵在于“按狀態(tài)篩選”而不是“按時間范圍篩選”中斷后重跑只會處理仍是 Pending 的行不會重復(fù)結(jié)算。4. 避坑與排查存儲過程最容易翻車的五個點4.1 參數(shù)嗅探導(dǎo)致計劃跑偏現(xiàn)象是同一個過程平時幾百毫秒某天突然變成幾十秒重啟或重編譯后又恢復(fù)正常。原因是 SQL Server 緩存了首次執(zhí)行時的參數(shù)生成的計劃如果首次傳入的是極端值比如某個 SKU 只有 3 條記錄后續(xù)傳入大范圍參數(shù)時復(fù)用了不合適的計劃。解決辦法是在過程內(nèi)對關(guān)鍵語句加OPTION (RECOMPILE)或者用OPTIMIZE FOR指定代表性參數(shù)值。我一般對批處理和報表類過程直接加RECOMPILE代價是每次編譯開銷換來的是計劃穩(wěn)定。4.2 嵌套事務(wù)的假提交現(xiàn)象是內(nèi)層過程調(diào)用了COMMIT外層以為還能回滾結(jié)果數(shù)據(jù)已經(jīng)落庫。原因是 SQL Server 的嵌套事務(wù)只有最外層COMMIT才真正提交內(nèi)層COMMIT只是把TRANCOUNT減一。解決辦法是約定“只有最外層過程管事務(wù)”被調(diào)用的子過程不寫B(tài)EGIN TRANSACTION或者用SAVE TRANSACTION做保存點。方案里統(tǒng)一規(guī)定跨過程調(diào)用時事務(wù)由入口過程負(fù)責(zé)子過程只做業(yè)務(wù)邏輯。4.3 SET NOCOUNT ON 漏寫引發(fā)的行數(shù)誤判現(xiàn)象是應(yīng)用層拿到的受影響行數(shù)總是 -1 或者對不上。原因是過程里每條 DML 都會向客戶端發(fā)送“n 行受影響”的消息客戶端驅(qū)動可能把它當(dāng)成結(jié)果集處理。解決辦法是每個過程開頭統(tǒng)一SET NOCOUNT ON需要回傳行數(shù)時用輸出參數(shù)顯式傳遞別依賴驅(qū)動去數(shù)消息。4.4 動態(tài) SQL 拼接帶來的注入與計劃緩存污染現(xiàn)象是安全掃描報注入風(fēng)險或者動態(tài) SQL 每次執(zhí)行都重新編譯。原因是直接EXEC(sql)拼接用戶輸入。解決辦法是能用靜態(tài) SQL 就不用動態(tài)必須動態(tài)時用sp_executesql加參數(shù)化把變量作為參數(shù)傳入而不是拼進字符串。這樣既防注入又能復(fù)用計劃緩存。4.5 權(quán)限授予過寬現(xiàn)象是應(yīng)用賬號能執(zhí)行所有過程包括維護類。原因是圖省事直接給了db_owner。解決辦法是按過程粒度授權(quán)GRANT EXECUTE ON dbo.usp_Order_Create TO app_user維護類過程單獨授權(quán)給運維賬號。這一條在課程設(shè)計里常被忽略但答辯時被問到“你的權(quán)限模型是什么”就是加分項。5. 驗收與答辯怎么證明這套存儲過程真的能用驗收不是把過程跑一遍不報錯就完事。我通常按三層來驗功能層、并發(fā)層、異常層。功能層用一組固定輸入跑出預(yù)期輸出比如庫存 10 扣 3 應(yīng)剩 7扣 11 應(yīng)報錯且?guī)齑娌蛔儾l(fā)層開兩個會話同時扣同一 SKU驗證不會超賣異常層故意在事務(wù)中途制造錯誤驗證回滾干凈、無半截數(shù)據(jù)。這三層各準(zhǔn)備三到五個用例寫進驗收表答辯時直接展示。驗收項用例預(yù)期結(jié)果判定方式正?？蹨p庫存 10扣 3庫存 7流水 1 條查表比對庫存不足庫存 10扣 11報錯 50002庫存仍 10捕獲錯誤號并發(fā)扣減兩會話各扣 6僅一個成功庫存 4查最終庫存中途失敗插明細時人為報錯訂單頭不落庫查訂單表為空批處理續(xù)跑結(jié)算跑到一半中斷再跑不重復(fù)結(jié)算比對狀態(tài)計數(shù)答辯時最容易被追問的是“為什么用存儲過程而不是在應(yīng)用層寫”?；卮鹨涞骄唧w點上事務(wù)邊界在數(shù)據(jù)庫側(cè)更短、批量操作減少網(wǎng)絡(luò)往返、權(quán)限可以按過程收斂。別泛泛說“性能好”要能說出“一次結(jié)算從 N 次往返變成 1 次調(diào)用”這種可量化的差別。另一個高頻問題是“過程里能不能寫業(yè)務(wù)邏輯”我的觀點是校驗和編排可以復(fù)雜計算和展示邏輯不該進來邊界劃清楚比一刀切更有說服力。有個習(xí)慣我保持了幾年每次交付前把每個過程的SET選項、事務(wù)寫法、錯誤處理三處逐行過一遍這三處出問題的概率遠高于業(yè)務(wù) SQL 本身。早期我吃過一次虧一個過程漏了SET XACT_ABORT ON測試環(huán)境一切正常上線后遇到死鎖直接留下臟數(shù)據(jù)排查了大半天才定位到。從那以后我每次交方案都強制走一遍這份檢查清單寧可多花二十分鐘也不留后悔藥。希望這套結(jié)構(gòu)能幫到你照著改改就能變成你自己的方案。本文還有配套的精品資源點擊獲取 SQL Server存儲過程設(shè)計實戰(zhàn)：從批量結(jié)算卡死到四類過程組織
簡介這份資源面向SQL Server數(shù)據(jù)庫初學(xué)者與需要鞏固存儲過程編寫的開發(fā)人員圍繞存儲過程這一核心主題提供可直接參考的實例代碼幫助讀者理解預(yù)編譯SQL語句集合在性能優(yōu)化、代碼復(fù)用與權(quán)限控制方面的實際用法。壓縮包共3個文件均為sql腳本整體約4KB體積輕量便于快速查閱其中既有供應(yīng)鏈管理場景下的報表類存儲過程也包含用于生成下一批次號的用戶自定義函數(shù)覆蓋參數(shù)傳遞、結(jié)果返回等典型寫法。資源已有2023人學(xué)習(xí)下載說明其在入門與查閱場景中具有一定參考價值。讀者可從中獲得存儲過程與自定義函數(shù)的完整定義范例對照學(xué)習(xí)CREATE PROCEDURE、EXEC調(diào)用及輸入?yún)?shù)的使用方式并借鑒業(yè)務(wù)報表與序列號生成兩類實際場景的編寫思路為后續(xù)掌握事務(wù)控制、異常處理等進階內(nèi)容打下基礎(chǔ)。1. 從一次批量結(jié)算卡死說起為什么存儲過程值得單獨設(shè)計某公司做內(nèi)部結(jié)算系統(tǒng)月初跑批時一條更新語句鎖了整張明細表前臺查詢?nèi)颗抨犞蛋嗤露⒅顒颖O(jiān)視器等了三分鐘才等到它自己解開。事后復(fù)盤發(fā)現(xiàn)問題不在 SQL 寫得對不對而在于業(yè)務(wù)邏輯散落在應(yīng)用層幾十條語句各自開事務(wù)、各自提交中間任何一步失敗都留下半截數(shù)據(jù)。這類場景正是存儲過程的主場把一組需要原子完成的數(shù)據(jù)庫操作收進一個命名單元由數(shù)據(jù)庫自己保證執(zhí)行順序和事務(wù)邊界。這份 SQLSERVER 存儲過程例子面向的讀者很明確——正在做數(shù)據(jù)庫課程設(shè)計、需要交一份完整數(shù)據(jù)庫方案的學(xué)生以及要給內(nèi)部系統(tǒng)補一套可維護數(shù)據(jù)訪問層的從業(yè)者。它不教你 SQL 語法入門而是給出一套能直接照著搭、照著改的存儲過程組織方式。2. 需求拆解與總體設(shè)計四類存儲過程覆蓋一套業(yè)務(wù)閉環(huán)2.1 先把需求翻譯成存儲過程清單拿到“存儲過程例子”這個題目最容易翻車的做法是上來就寫代碼寫完發(fā)現(xiàn)增刪改查各寫了一個彼此之間沒有關(guān)系答辯時被問“你的業(yè)務(wù)閉環(huán)在哪”就答不上來。我一般的做法是先列業(yè)務(wù)動作再把動作映射成過程。以一個典型的訂單與庫存場景為例動作可以拆成四類類別典型動作對應(yīng)存儲過程關(guān)鍵約束查詢類按條件分頁查訂單、查庫存余量usp_Order_Query、usp_Stock_Get只讀、不鎖表、支持分頁參數(shù)寫入類下單、扣庫存usp_Order_Create單事務(wù)、庫存不足即回滾批處理類月度結(jié)算、超時訂單關(guān)閉usp_Settle_Monthly分批提交、可重入維護類清理日志、重建索引usp_Maint_CleanLog限定時段、帶執(zhí)行記錄這張表就是整個方案的骨架。四類過程職責(zé)不同寫法也不同查詢類追求執(zhí)行計劃穩(wěn)定寫入類追求事務(wù)邊界清晰批處理類追求可中斷可續(xù)跑維護類追求可審計。把這張表先定下來后面每個過程該長什么樣基本就定了不會出現(xiàn)“寫了一堆過程但說不清各自定位”的尷尬。2.2 命名規(guī)范與參數(shù)約定命名不統(tǒng)一是后期維護的血淚來源。方案里統(tǒng)一采用usp_模塊_動作的前綴式命名usp表明是用戶存儲過程模塊名對應(yīng)業(yè)務(wù)域動作名用動詞開頭。參數(shù)上定三條硬規(guī)矩輸入?yún)?shù)一律帶前綴且類型與列類型嚴(yán)格一致輸出參數(shù)統(tǒng)一用RowsAffected INT OUTPUT回傳影響行數(shù)所有過程第一個可執(zhí)行語句之前先做參數(shù)校驗。類型不一致看著是小事實際會讓索引失效——比如列是INT而參數(shù)傳了BIGINTSQL Server 可能走隱式轉(zhuǎn)換執(zhí)行計劃直接從 Seek 退化成 Scan數(shù)據(jù)量一大就是災(zāi)難。2.3 分層結(jié)構(gòu)過程只做編排不做展示總體結(jié)構(gòu)上把存儲過程定位成“數(shù)據(jù)訪問層的編排者”而不是“什么活都干的萬金油”。應(yīng)用層負(fù)責(zé)參數(shù)組裝和結(jié)果展示存儲過程負(fù)責(zé)事務(wù)、約束校驗和批量操作視圖和函數(shù)負(fù)責(zé)可復(fù)用的查詢片段。這樣分層的好處是業(yè)務(wù)規(guī)則變更時只改過程不動應(yīng)用代碼查詢邏輯復(fù)用時抽成內(nèi)聯(lián)表值函數(shù)過程里直接JOIN調(diào)用比復(fù)制粘貼一段 SQL 可靠得多。方案里通常還會約定一個統(tǒng)一的錯誤碼表過程內(nèi)部用THROW拋出帶編號的錯誤應(yīng)用層按編號做提示避免把數(shù)據(jù)庫原始報錯直接甩給用戶。3. 核心存儲過程實現(xiàn)從單條寫入到批量結(jié)算3.1 寫入類過程事務(wù)、校驗與錯誤處理三件套下單扣庫存是最能體現(xiàn)存儲過程價值的一段。它要同時完成“插訂單頭、插訂單明細、扣庫存、寫流水”四步任何一步失敗都必須整體回滾。下面這段是方案里的核心實現(xiàn)CREATE OR ALTER PROCEDURE usp_Order_Create OrderNo VARCHAR(32), SkuId INT, Qty INT, RowsAffected INT OUTPUT AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 任何運行時錯誤自動回滾整個事務(wù) -- 參數(shù)校驗前置避免帶著臟數(shù)據(jù)進事務(wù) IF Qty IS NULL OR Qty 0 THROW 50001, 下單數(shù)量必須大于 0, 1; BEGIN TRY BEGIN TRANSACTION; -- 條件更新扣庫存庫存不足時影響行數(shù)為 0直接判定失敗 UPDATE dbo.Stock SET Qty Qty - Qty WHERE SkuId SkuId AND Qty Qty; IF ROWCOUNT 0 THROW 50002, 庫存不足或商品不存在, 1; INSERT INTO dbo.OrderHeader (OrderNo, SkuId, Qty, CreatedAt) VALUES (OrderNo, SkuId, Qty, SYSDATETIME()); INSERT INTO dbo.StockFlow (SkuId, ChangeQty, RefOrderNo, CreatedAt) VALUES (SkuId, -Qty, OrderNo, SYSDATETIME()); COMMIT TRANSACTION; SET RowsAffected 1; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRANSACTION; THROW; -- 原樣拋出保留錯誤號供應(yīng)用層判斷 END CATCH END;這段代碼有三個設(shè)計點值得說清楚。第一SET XACT_ABORT ON讓嚴(yán)重級別 16 以上的錯誤自動回滾省去在 CATCH 里逐條判斷的麻煩但 CATCH 里的XACT_STATE()判斷仍然保留因為有些錯誤不會自動回滾屬于兜底。第二扣庫存用“條件更新 判斷ROWCOUNT”而不是“先 SELECT 再判斷再 UPDATE”后者在并發(fā)下存在檢查與執(zhí)行之間的窗口兩個會話可能同時讀到足夠庫存然后都扣成功這是最經(jīng)典的并發(fā)翻車點。第三THROW不帶分號結(jié)尾在部分版本會報語法錯寫的時候養(yǎng)成加分號的習(xí)慣。3.2 查詢類過程分頁與參數(shù)嗅探的平衡查詢類過程最容易寫成一坨動態(tài)拼接的字符串參數(shù)一多就拼出注入風(fēng)險。方案里統(tǒng)一用參數(shù)化分頁排序字段通過白名單映射不直接拼進 SQLCREATE OR ALTER PROCEDURE usp_Order_Query PageIndex INT 1, PageSize INT 20, SkuId INT NULL, SortKey VARCHAR(20) CreatedAt AS BEGIN SET NOCOUNT ON; -- 排序字段白名單杜絕把用戶輸入直接拼進 ORDER BY DECLARE OrderBy VARCHAR(40) CASE SortKey WHEN Qty THEN Qty DESC WHEN CreatedAt THEN CreatedAt DESC ELSE CreatedAt DESC END; SELECT OrderNo, SkuId, Qty, CreatedAt FROM dbo.OrderHeader WHERE (SkuId IS NULL OR SkuId SkuId) ORDER BY CASE WHEN OrderBy Qty DESC THEN Qty END DESC, CASE WHEN OrderBy CreatedAt DESC THEN CreatedAt END DESC OFFSET (PageIndex - 1) * PageSize ROWS FETCH NEXT PageSize ROWS ONLY; END;這里用CASE表達式實現(xiàn)動態(tài)排序代價是執(zhí)行計劃可能不如直接拼字符串精確但換來的是計劃緩存可復(fù)用、無注入風(fēng)險。數(shù)據(jù)量在百萬級以內(nèi)這個折中完全夠用如果表更大通常的做法是把排序字段拆成幾個獨立分支每個分支一條靜態(tài) SQL讓優(yōu)化器各自生成計劃。另外(SkuId IS NULL OR SkuId SkuId)這種寫法在參數(shù)為 NULL 時可能引起全表掃描方案里建議對高頻查詢單獨建一個不帶該條件的版本用應(yīng)用層判斷走哪條別指望一個過程包打天下。3.3 批處理類過程分批提交與可重入月度結(jié)算這類過程動輒處理幾十萬行一次性大事務(wù)會把日志撐爆、鎖持有時間過長。方案里的做法是循環(huán)分批每批固定行數(shù)批間提交并用一張執(zhí)行記錄表標(biāo)記進度保證中斷后能續(xù)跑CREATE OR ALTER PROCEDURE usp_Settle_Monthly BatchSize INT 5000 AS BEGIN SET NOCOUNT ON; DECLARE Done INT 0; WHILE 1 1 BEGIN BEGIN TRANSACTION; UPDATE TOP (BatchSize) dbo.OrderHeader SET Status Settled WHERE Status Pending AND CreatedAt DATEADD(DAY, -30, SYSDATETIME()); SET Done ROWCOUNT; COMMIT TRANSACTION; IF Done 0 BREAK; -- 沒有待處理數(shù)據(jù)退出循環(huán) -- 批間短暫讓出降低對在線查詢的影響 WAITFOR DELAY 00:00:00.050; END END;TOP (BatchSize)配合變量在 SQL Server 里是合法的但要注意它和UPDATE ... FROM的寫法差異寫錯會變成更新全表。WAITFOR DELAY那 50 毫秒不是玄學(xué)是給其他會話搶鎖的機會批處理跑在業(yè)務(wù)時段時這點讓步很值?？芍厝氲年P(guān)鍵在于“按狀態(tài)篩選”而不是“按時間范圍篩選”中斷后重跑只會處理仍是 Pending 的行不會重復(fù)結(jié)算。4. 避坑與排查存儲過程最容易翻車的五個點4.1 參數(shù)嗅探導(dǎo)致計劃跑偏現(xiàn)象是同一個過程平時幾百毫秒某天突然變成幾十秒重啟或重編譯后又恢復(fù)正常。原因是 SQL Server 緩存了首次執(zhí)行時的參數(shù)生成的計劃如果首次傳入的是極端值比如某個 SKU 只有 3 條記錄后續(xù)傳入大范圍參數(shù)時復(fù)用了不合適的計劃。解決辦法是在過程內(nèi)對關(guān)鍵語句加OPTION (RECOMPILE)或者用OPTIMIZE FOR指定代表性參數(shù)值。我一般對批處理和報表類過程直接加RECOMPILE代價是每次編譯開銷換來的是計劃穩(wěn)定。4.2 嵌套事務(wù)的假提交現(xiàn)象是內(nèi)層過程調(diào)用了COMMIT外層以為還能回滾結(jié)果數(shù)據(jù)已經(jīng)落庫。原因是 SQL Server 的嵌套事務(wù)只有最外層COMMIT才真正提交內(nèi)層COMMIT只是把TRANCOUNT減一。解決辦法是約定“只有最外層過程管事務(wù)”被調(diào)用的子過程不寫B(tài)EGIN TRANSACTION或者用SAVE TRANSACTION做保存點。方案里統(tǒng)一規(guī)定跨過程調(diào)用時事務(wù)由入口過程負(fù)責(zé)子過程只做業(yè)務(wù)邏輯。4.3 SET NOCOUNT ON 漏寫引發(fā)的行數(shù)誤判現(xiàn)象是應(yīng)用層拿到的受影響行數(shù)總是 -1 或者對不上。原因是過程里每條 DML 都會向客戶端發(fā)送“n 行受影響”的消息客戶端驅(qū)動可能把它當(dāng)成結(jié)果集處理。解決辦法是每個過程開頭統(tǒng)一SET NOCOUNT ON需要回傳行數(shù)時用輸出參數(shù)顯式傳遞別依賴驅(qū)動去數(shù)消息。4.4 動態(tài) SQL 拼接帶來的注入與計劃緩存污染現(xiàn)象是安全掃描報注入風(fēng)險或者動態(tài) SQL 每次執(zhí)行都重新編譯。原因是直接EXEC(sql)拼接用戶輸入。解決辦法是能用靜態(tài) SQL 就不用動態(tài)必須動態(tài)時用sp_executesql加參數(shù)化把變量作為參數(shù)傳入而不是拼進字符串。這樣既防注入又能復(fù)用計劃緩存。4.5 權(quán)限授予過寬現(xiàn)象是應(yīng)用賬號能執(zhí)行所有過程包括維護類。原因是圖省事直接給了db_owner。解決辦法是按過程粒度授權(quán)GRANT EXECUTE ON dbo.usp_Order_Create TO app_user維護類過程單獨授權(quán)給運維賬號。這一條在課程設(shè)計里常被忽略但答辯時被問到“你的權(quán)限模型是什么”就是加分項。5. 驗收與答辯怎么證明這套存儲過程真的能用驗收不是把過程跑一遍不報錯就完事。我通常按三層來驗功能層、并發(fā)層、異常層。功能層用一組固定輸入跑出預(yù)期輸出比如庫存 10 扣 3 應(yīng)剩 7扣 11 應(yīng)報錯且?guī)齑娌蛔儾l(fā)層開兩個會話同時扣同一 SKU驗證不會超賣異常層故意在事務(wù)中途制造錯誤驗證回滾干凈、無半截數(shù)據(jù)。這三層各準(zhǔn)備三到五個用例寫進驗收表答辯時直接展示。驗收項用例預(yù)期結(jié)果判定方式正?？蹨p庫存 10扣 3庫存 7流水 1 條查表比對庫存不足庫存 10扣 11報錯 50002庫存仍 10捕獲錯誤號并發(fā)扣減兩會話各扣 6僅一個成功庫存 4查最終庫存中途失敗插明細時人為報錯訂單頭不落庫查訂單表為空批處理續(xù)跑結(jié)算跑到一半中斷再跑不重復(fù)結(jié)算比對狀態(tài)計數(shù)答辯時最容易被追問的是“為什么用存儲過程而不是在應(yīng)用層寫”?；卮鹨涞骄唧w點上事務(wù)邊界在數(shù)據(jù)庫側(cè)更短、批量操作減少網(wǎng)絡(luò)往返、權(quán)限可以按過程收斂。別泛泛說“性能好”要能說出“一次結(jié)算從 N 次往返變成 1 次調(diào)用”這種可量化的差別。另一個高頻問題是“過程里能不能寫業(yè)務(wù)邏輯”我的觀點是校驗和編排可以復(fù)雜計算和展示邏輯不該進來邊界劃清楚比一刀切更有說服力。有個習(xí)慣我保持了幾年每次交付前把每個過程的SET選項、事務(wù)寫法、錯誤處理三處逐行過一遍這三處出問題的概率遠高于業(yè)務(wù) SQL 本身。早期我吃過一次虧一個過程漏了SET XACT_ABORT ON測試環(huán)境一切正常上線后遇到死鎖直接留下臟數(shù)據(jù)排查了大半天才定位到。從那以后我每次交方案都強制走一遍這份檢查清單寧可多花二十分鐘也不留后悔藥。希望這套結(jié)構(gòu)能幫到你照著改改就能變成你自己的方案。本文還有配套的精品資源點擊獲取