萬盛學電腦網

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

SQL Server 儲存程序完整指南:建立、參數、返回值、權限與效能優勢

儲存程序是 SQL Server 封裝業務邏輯、提升效能、強化安全的核心物件。本文系統講解語法結構、參數模式(IN/OUT/INOUT)、返回值、動態 SQL、權限模型與效能優勢,附完整實戰範例。

什麼是儲存程序?

預先編譯、儲存在資料庫中的 T-SQL 批次,接受參數、執行邏輯、返回結果/狀態碼。類比程式語言的「方法/函數」,但運行在資料庫引擎內部。

核心優勢

優勢說明
效能執行計畫快取重用、減少網路往返、批次處理減少編譯開銷
安全用戶無需表直接權限,僅授予 EXECUTE 權限;防 SQL 注入(參數化)
完整性封裝多步操作為原子單元,配合事務保證一致性
維護業務邏輯集中資料庫層,應用層解耦,便於版本控制與熱修復
網路只傳呼叫指令與參數/結果集,大幅降低頻寬

三類儲存程序

  • 系統儲存程序:sp_ 前綴,儲於 master,所有資料庫可見(如 sp_help, sp_who, sp_configure)
  • 擴展儲存程序:xp_ 前綴,呼叫外部 DLL(如 xp_cmdshell),高風險、慎用
  • 使用者自定義:業務邏輯主力,建議 usp_ 或 proc_ 前綴

常用系統儲存程序速查

EXEC sp_databases;                    -- 列出資料庫
EXEC sp_tables;                       -- 列出表
EXEC sp_columns 'table_name';         -- 列出欄位
EXEC sp_helpindex 'table_name';       -- 列出索引
EXEC sp_helpconstraint 'table_name';  -- 列出約束
EXEC sp_helptext 'proc_name';         -- 查看程序定義
EXEC sp_stored_procedures;            -- 列出所有程序
EXEC sp_rename 'old_tbl', 'new_tbl';  -- 重命名表
EXEC sp_renamedb 'old_db', 'new_db';  -- 重命名資料庫
EXEC sp_defaultdb 'login', 'db';      -- 修改登入預設資料庫
EXEC sp_helpdb;                       -- 資料庫資訊
EXEC sp_attach_db / sp_detach_db;     -- 附加/分離資料庫

建立語法與參數模式

CREATE PROCEDURE [schema.]proc_name
    @param1 datatype [= default] [OUTPUT],
    @param2 datatype [= default] [OUTPUT]
AS
BEGIN
    SET NOCOUNT ON;  -- 抑制 DONE_IN_PROC 訊息,提升效能
    -- T-SQL 陳述式
    RETURN int_status;  -- 可選,僅 INT 型別
END;

參數模式實戰

-- 1. 僅輸入參數
CREATE PROC usp_GetOfficeName @Id INT
AS SELECT Name FROM Office_Info WHERE Id = @Id;
GO
EXEC usp_GetOfficeName 2;

-- 2. 輸出參數
CREATE PROC usp_GetOfficeNameOut @Id INT, @Name VARCHAR(50) OUTPUT
AS SELECT @Name = Name FROM Office_Info WHERE Id = @Id;
GO
DECLARE @n VARCHAR(50);
EXEC usp_GetOfficeNameOut 4, @n OUTPUT;
SELECT @n;

-- 3. 輸入輸出參數
CREATE PROC usp_IncCounter @Counter INT OUTPUT
AS SET @Counter = @Counter + 1;
GO
DECLARE @c INT = 10;
EXEC usp_IncCounter @c OUTPUT;  -- @c 變 11

-- 4. 返回值 (僅 INT)
CREATE PROC usp_CheckExists @Id INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Office_Info WHERE Id = @Id) RETURN 1;
    RETURN -1;
END;
GO
DECLARE @ret INT;
EXEC @ret = usp_CheckExists 2;
PRINT @ret;

動態 SQL 與預處理

CREATE PROC usp_DynamicQuery @TableName SYSNAME, @Col SYSNAME, @Val VARCHAR(100)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@TableName) + 
                                 N' WHERE ' + QUOTENAME(@Col) + N' = @val';
    EXEC sp_executesql @sql, N'@val VARCHAR(100)', @val = @Val;
    -- sp_executesql 參數化 → 計畫重用、防注入
END;

權限模型

  • GRANT EXECUTE ON usp_Proc TO [user/role]; — 僅需執行權限
  • 擁有者鏈:若程序與參考物件同擁有者,呼叫者無需底層物件權限
  • EXECUTE AS CALLER/SELF/OWNER/'user' 控制執行上下文

效能最佳實踐

  1. SET NOCOUNT ON:減少網路封包
  2. 避免 SELECT *,明確列出欄位
  3. 使用參數化(sp_executesql)而非字串拼接
  4. 適度使用 OPTION (RECOMPILE) 解決參數嗅探問題
  5. 大批次操作分批提交,避免長事務鎖升級
  6. 定期 sp_recompile 或 DBCC FREEPROCCACHE 更新計畫

除錯與監控

-- 查看程序定義
EXEC sp_helptext 'usp_ProcName';
-- 或
SELECT OBJECT_DEFINITION(OBJECT_ID('usp_ProcName'));

-- 監控執行統計
SELECT OBJECT_NAME(object_id) AS ProcName, 
       execution_count, 
       total_elapsed_time/execution_count AS AvgMs,
       last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY AvgMs DESC;

儲存程序不是「過時技術」,而是**資料庫層服務化**的基石。合理使用能構建安全、高效、可維護的資料存取層。新專案建議:核心寫入路徑全走儲存程序,複雜查詢視圖/內聯函數/應用層 ORM 共存,各司其職。

copyright © 萬盛學電腦網 all rights reserved