萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2005 執行預存程序三大核心優勢

預存程序是 SQL Server 開發的基石。SQL Server 2005 強化了預存程序的效能與功能,以下三大優勢是選擇它的核心理由。

優勢一:批次執行多條 SQL,減少網路往返

單次呼叫可執行複雜的多語句邏輯(DML、DDL、控制流),客戶端只發送一條 EXEC 命令,大幅降低網路延遲與封包開銷。

優勢二:封裝複雜商業邏輯,提升安全與維護性

  • 資料存取邏輯集中在資料庫層,應用程式僅呼叫介面
  • 可授權 EXECUTE 權限而不給底層資料表權限,實現資料層安全隔離
  • 修改邏輯無需重新編譯部署應用程式

優勢三:執行計畫快取與參數嗅探優化

  • 首次執行產生計畫並快取,後續重複使用避免重複編譯
  • 參數嗅探讓優化器依首次參數值產生最佳計畫(需注意參數敏感度問題)
  • 比動態 SQL 字串拼接執行更穩定、可預測

建立預存程序語法結構

CREATE PROCEDURE [schema].[ProcedureName] 
    @Param1 DataType = DefaultValue [OUTPUT],
    @Param2 DataType = DefaultValue
AS
BEGIN
    SET NOCOUNT ON;  -- 抑制 DONE_IN_PROC 訊息
    
    -- T-SQL 邏輯
    SELECT ...; 
    INSERT ...;
    UPDATE ...;
    
    RETURN @ReturnCode;  -- 可選整數回傳碼
END;

關鍵字說明

關鍵字說明
OUTPUT標記輸出參數,呼叫端可接收回傳值
Number (選用)同名程序分組編號,DROP PROCEDURE Name;Number 可一次刪除整組
@@ERROR上一條語句錯誤碼,0 表成功
@@IDENTITY最後插入的識別欄位值 (跨表/觸發器影響)
SCOPE_IDENTITY()當前範圍最後插入的識別值 (推薦使用)

實戰範例:帶輸出參數的寫入程序

CREATE TABLE [dbo].[Col] (
    [Col01] VARCHAR(50) NOT NULL PRIMARY KEY,
    [Col02] VARCHAR(50) NOT NULL
);

CREATE PROCEDURE [dbo].[Cp_WriteCol]
    @Col01 VARCHAR(10) = 'default',
    @Col02 VARCHAR(10) = 'default',
    @Ret BIT = 1 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO [dbo].[Col] (Col01, Col02) VALUES (@Col01, @Col02);
    INSERT INTO [dbo].[Col] (Col01, Col02) VALUES (@Col01, @Col02);
    
    IF @@ERROR <> 0
        SET @Ret = 0;
END;
GO

-- 呼叫測試
DECLARE @Result BIT;
EXEC [dbo].[Cp_WriteCol] @Col01 = 'A', @Col02 = 'B', @Ret = @Result OUTPUT;
SELECT @Result AS ReturnCode;  -- 1 成功, 0 失敗

進階建議

  • 使用 TRY...CATCH 替代 @@ERROR 處理錯誤 (SQL 2005+)
  • 避免過度使用預存程序導致「資料庫變應用伺服器」
  • 善用計畫指南、查詢存儲 (Query Store) 監控計畫穩定性
  • 適度模組化:單一職責、命名規範、版本控管

預存程序不只是「存放 SQL 的地方」,更是資料庫可程式化、安全化、高效能的核心載體。

copyright © 萬盛學電腦網 all rights reserved