預存程序：工作原理及使用方法

內部網路 2026 年 7 月 30 日應用程式現代化, 影響分析軟體

每個資料庫驅動的應用程式最終都會遇到這樣的問題：散佈在應用程式程式碼各處的 SQL 查詢會成為維護難題。同一個複雜的連線操作會出現在三個不同的服務中。本應屬於資料庫層的業務邏輯洩漏到了應用程式層。本應在資料層強制執行的安全策略，卻被應用程式程式碼以不一致的方式執行，而這些策略很容易被繞過。預存程序透過將可重複使用、安全敏感且效能關鍵的 SQL 邏輯移至資料庫中來解決此類問題。在資料庫中，這些邏輯可以獨立於呼叫它們的應用程式進行管理、版本控制、安全保護和最佳化。

預存程序是一組已命名、預先編譯的 SQL 語句，儲存在資料庫中並作為一個整體執行。它接受參數、包含邏輯，並且可以傳回結果、輸出參數或狀態碼。與應用程式程式碼發送的臨時查詢不同，預存程序只需解析和編譯一次，其執行計劃就會被快取並在每次後續呼叫中重複使用，從而消除了重複動態 SQL 所產生的編譯開銷。本指南涵蓋了預存程序的定義、何時使用它們、它們如何增強安全性、它們如何影響效能，以及如何隨著它們發展成為跨越整個資料庫環境的依賴項而進行管理。

每次資料庫變更運行前都要進行範圍界定。

SMART TS XL 同時映射 SQL、COBOL、Java 和 Python 之間的預存程序相依性。

更多資訊

什麼是預存程序？

預存程序是儲存在關聯式資料庫中的預編譯例程，可透過名稱和可選參數呼叫。資料庫引擎只需編譯一次，快取執行計劃，並在每次後續呼叫中重複使用該計劃，從而避免了即席 SQL 查詢每次執行時都需要進行的解析-編譯-最佳化循環。

SQL

-- SQL Server: basic stored procedure
CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    ORDER BY o.OrderDate DESC;
END;
GO

-- Calling the procedure
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

該應用程式從不直接編寫 SQL。它調用 GetCustomerOrders 帶參數。資料庫會處理其餘部分。

預存程序、視圖和函數

有三個資料庫物件經常被混淆。下表對它們進行了區分：

對象	退換貨條款	接受參數	可以修改數據	執行計劃已緩存	最適合
存儲過程	結果集、輸出參數、回傳程式碼	可以	可以	可以	複雜邏輯、DML 操作、安全強制
查看	單一結果集（例如表格）	沒有	不（通常情況下）	局部的	簡化 SELECT 查詢，列級安全性
標量函數	單值	可以	沒有	沒有	SELECT 清單中重複使用的計算
表值函數	結果集	可以	沒有	局部的	參數化視圖，傳回集合的計算

關鍵差異：需要可重複使用的 SELECT 抽象時，請使用視圖。需要參數、條件邏輯、資料修改或安全措施時，請使用預存程序。需要計算並傳回值或表，且必須與其他 SQL 語句組合時，請使用函數。

四大核心優勢

效能：預編譯和計劃緩存

當 SQL Server、PostgreSQL 或 Oracle 收到預存程序呼叫時，它會檢查該程序是否存在快取的執行計劃。如果存在，則立即執行。如果不存在，則會編譯該過程，產生執行計劃，將其緩存，然後執行。在所有後續呼叫中，都會重複使用快取的執行計劃。

根據資料庫和查詢結構的不同，從應用程式程式碼發送的即席 SQL 查詢（即字串拼接查詢）每次呼叫時都可能需要重新編譯。對於每秒調用數千次的高頻查詢，這種編譯開銷非常顯著。

SQL

-- Demonstrating plan reuse in SQL Server
-- Check if a plan exists for a procedure
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, 1, 100) AS procedure_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.execution_count DESC;

減少網路流量是第二個性能優勢。應用程式不再每次呼叫資料庫時都會傳送複雜的 30 行 SQL 查詢，而是傳送一個簡短的過程呼叫。網路負載極低。資料庫在伺服器端完成繁重的計算，只回傳結果集。

安全性：限制直接表訪問

這正是SC資料專門尋找的優勢：「如何使用預存程序來限制直接資料存取並增強資料庫安全性」。該機制簡單而強大。

授予應用程式使用者對預存程序的執行權限。禁止直接存取底層表。使用者可以呼叫預存程序，但不能直接查詢、插入、更新或刪除表中的資料。

SQL

-- Create a role for application users
CREATE ROLE AppReadRole;

-- Grant execute on the procedure
GRANT EXECUTE ON GetCustomerOrders TO AppReadRole;

-- Deny direct table access
DENY SELECT ON Orders TO AppReadRole;
DENY SELECT ON Customers TO AppReadRole;

-- The user can now:
--   EXEC GetCustomerOrders @CustomerId=123, ... -> works
--   SELECT * FROM Orders WHERE ... -> ACCESS DENIED

防止 SQL 注入是第二個安全優勢。使用參數化輸入而非動態 SQL 建構的預存程序本身就具有防止 SQL 注入的保護。參數值被視為字面值，而不是 SQL 程式碼。

SQL

-- VULNERABLE: dynamic SQL built from user input
DECLARE @sql NVARCHAR(500);
SET @sql = 'SELECT * FROM Customers WHERE Name = ''' + @UserInput + '''';
EXEC(@sql);
-- An attacker can inject: '; DROP TABLE Customers; --

-- SAFE: parameterized stored procedure
CREATE PROCEDURE GetCustomerByName
    @CustomerName NVARCHAR(100)
AS
BEGIN
    SELECT CustomerId, Name, Email
    FROM Customers
    WHERE Name = @CustomerName;  -- @CustomerName is a literal, not SQL
END;

小心： 一個在內部建構動態 SQL 的預存程序 EXEC() or sp_executesql 使用字串拼接使用者輸入與應用層動態 SQL 一樣容易受到攻擊。參數化必須擴展到過程內部建構的任何動態 SQL。

可維護性：一次更改，所有應用程式更新

當業務邏輯發生變化時，例如稅費計算規則、折扣等級或合規性要求的資料轉換，預存程序會將這些邏輯集中到一個地方。每個呼叫該預存程序的應用程式都會自動接收更新後的行為，無需重新部署。

SQL

-- Before: discount logic duplicated in three application services
-- After: centralized in one stored procedure
CREATE PROCEDURE CalculateOrderTotal
    @OrderId INT,
    @DiscountedTotal DECIMAL(10,2) OUTPUT
AS
BEGIN
    DECLARE @Subtotal DECIMAL(10,2);
    DECLARE @CustomerTier NCHAR(1);

    SELECT @Subtotal     = SUM(li.Quantity * li.UnitPrice),
           @CustomerTier = c.Tier
    FROM OrderLineItems li
    JOIN Orders o ON o.OrderId = li.OrderId
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE li.OrderId = @OrderId
    GROUP BY c.Tier;

    -- Business rule: Gold customers get 15%, Silver get 8%, Standard get 0%
    SET @DiscountedTotal = @Subtotal * CASE @CustomerTier
        WHEN 'G' THEN 0.85
        WHEN 'S' THEN 0.92
        ELSE 1.00
    END;
END;

集中更改折扣百分比。所有三個呼叫應用程式服務 CalculateOrderTotal 立即反映新的費率。

帶輸出參數和錯誤處理的封裝

預存程序透過輸出參數傳回多個值，並透過傳回程式碼傳遞處理狀態，從而實現比簡單的 SELECT 更豐富的互動模式。

SQL

CREATE PROCEDURE InsertOrder
    @CustomerId  INT,
    @OrderDate   DATE,
    @NewOrderId  INT OUTPUT,
    @StatusCode  INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO Orders (CustomerId, OrderDate, Status)
        VALUES (@CustomerId, @OrderDate, 'PENDING');

        SET @NewOrderId = SCOPE_IDENTITY();
        SET @StatusCode = 0;  -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @StatusCode = ERROR_NUMBER();
        SET @NewOrderId = -1;
    END CATCH;
END;

-- Usage
DECLARE @OrderId INT, @Status INT;
EXEC InsertOrder
    @CustomerId = 5001,
    @OrderDate  = '2026-07-15',
    @NewOrderId = @OrderId OUTPUT,
    @StatusCode = @Status OUTPUT;

IF @Status <> 0
    PRINT 'Insert failed with error: ' + CAST(@Status AS VARCHAR);
ELSE
    PRINT 'Created order: ' + CAST(@OrderId AS VARCHAR);

效能優化：執行計劃能告訴你什麼

執行計劃是資料庫引擎記錄如何執行查詢的文檔，包括使用了哪些索引、選擇了哪種連接演算法以及每一步預估的行數。對於每天被呼叫數千次的預存程序，執行計劃是診斷效能問題的主要工具。

SQL

-- Enable actual execution plan in SQL Server
-- Then run:
EXEC GetCustomerOrders
    @CustomerId = 12345,
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30';

-- Check for parameter sniffing issues
-- (cached plan was optimized for a different parameter distribution)
EXEC GetCustomerOrders
    @CustomerId = 99999,  -- rare customer with very few orders
    @StartDate  = '2026-01-01',
    @EndDate    = '2026-06-30'
WITH RECOMPILE;  -- forces fresh plan for this call

參數嗅探是預存程序效能中最常見的問題。 SQL Server 會快取首次呼叫該程序時所使用的參數集所產生的執行計劃。如果後續呼叫使用了截然不同的參數值（例如，一個客戶有 50,000 個訂單，而另一個客戶只有 2 個訂單），那麼快取的執行計劃對於這些參數值可能並非最優。

緩解策略： OPTIMIZE FOR 提示：最佳化以獲得具代表性的參數值； WITH RECOMPILE 在流程層面，每次呼叫都會產生新的計劃（成本較高，但當參數分佈變化很大時有效）；在流程開始時進行局部變數賦值，以防止嗅探：

SQL

CREATE PROCEDURE GetCustomerOrders
    @CustomerId INT,
    @StartDate  DATE,
    @EndDate    DATE
AS
BEGIN
    -- Local variable trick: prevents parameter sniffing
    DECLARE @LocalCustomerId INT = @CustomerId;
    DECLARE @LocalStart      DATE = @StartDate;
    DECLARE @LocalEnd        DATE = @EndDate;

    SELECT o.OrderId, o.OrderDate, o.TotalAmount
    FROM Orders o
    WHERE o.CustomerId = @LocalCustomerId
      AND o.OrderDate BETWEEN @LocalStart AND @LocalEnd;
END;

最佳實務：工作清單

這些並非一般原則，而是使預存程序能夠大規模維護的實踐方法：

命名和組織

使用一致的命名規則： usp_ 用戶預存程序前綴 sp_ 保留用於系統程式
程式名稱由動詞+名詞構成： GetCustomerOrders, InsertPaymentRecord, UpdateInventoryCount
模式中與流程相關的分組過程： Sales.GetCustomerOrders, Inventory.UpdateStock

程式碼結構

所有操作均以…開始 SET NOCOUNT ON 抑制客戶端可能誤解的行計數訊息
使用 BEGIN TRY / BEGIN CATCH 包含顯式程式碼區塊 BEGIN TRANSACTION / COMMIT / ROLLBACK
避免使用遊標進行集合運算，盡可能將其重寫為基於集合的 SQL。
不要使用 SELECT *為該過程傳回的每一列命名

安全性

授予對過程的執行權限；拒絕應用程式角色直接存取表的權限
避免在預存程序中根據使用者輸入建立動態 SQL
使用 sp_executesql 如果動態 SQL 不可避免，則可以使用參數化查詢。

性能

檢查大型資料表的資料表掃描執行計劃，必要時新增索引。
使用代表性的參數值對易受嗅探的程式進行測試
監控器 sys.dm_exec_procedure_stats 適用於高執行率或高持續時間的程序

文档

為每個過程添加頭部註釋：用途、參數、返回值、作者、最後修改時間
文件中應記錄編碼在過程邏輯中的業務規則，不僅要說明 SQL 執行了什麼，還要說明為什麼。

管理儲存過程依賴關係

預存程序並非孤立存在。一個預存程序需要從五個表中讀取數據，調用另外兩個預存過程，並被十幾個應用程式服務調用，因此它是一個具有複雜依賴關係的元件，這種依賴關係體現在三個方面：它依賴哪些存儲過程，哪些存儲過程依賴它，以及它與其他存儲過程共享哪些存儲過程。

當表格列類型變更時，所有引用該列的預存程序都需要進行測試。當預存程序的輸出格式變更時，所有呼叫者都需要進行驗證。當考慮修改預存程序時，所有呼叫者共同決定了修改的範圍以及所需的迴歸測試。

重要的依賴類型：

對象依賴關係： 該過程引用的表、視圖、函數和其他過程
呼叫方相依性： 應用程式程式碼、其他預存程序和呼叫此程序的排程作業
模式依賴關係： 過程的參數類型和 SELECT 清單必須與表格和列定義相符。
事務依賴關係： 與呼叫者或彼此共享交易範圍的程序

在一個只有十個預存程序的小型資料庫中，這些依賴關係可以手動追蹤。但在擁有數百個預存程序的資料庫環境中（這種情況在企業環境中很常見，因為預存程序封裝了多年的業務邏輯），手動追蹤依賴關係會導致依賴關係映射不完整，並引發與變更相關的事件。

SMART TS XL 管理企業級儲存過程依賴關係

SMART TS XL“ 靜態程式碼分析它能夠解析 SQL 預存程序以及與相同資料庫互動的 COBOL 程式、Java 服務、Python 管道和其他元件。這種統一的分析方法能夠產生跨語言的結構模型：不僅包含資料庫內部的 SQL 依賴關係，還包含從應用程式程式碼經由預存程序到底層表以及傳回的完整依賴鏈。

應用程式依賴關係映射功能建立完整的呼叫者圖：哪些 COBOL 程式使用嵌入式 SQL 從預存程序擁有的表中讀取數據，哪些 Java 服務透過 JDBC 呼叫預存程序，哪些 JCL 批次作業呼叫執行預存程序的資料庫公用程式。當預存程序的簽章或行為發生變化時，依賴關係圖會顯示所有語言的所有呼叫者，以及在變更上線之前需要測試的完整範圍。

这影響分析此功能使此依賴關係圖可用於變更規劃：提出變更建議 CalculateOrderTotal 並收到一份枚舉列表，其中列出了調用它的每個組件、它讀取和寫入的每個表以及它調用的每個下游過程。這使得「這會破壞什麼？」這個問題不再依賴經驗之談，而是轉化為一份結構化的、基於證據的範圍報告。

这企業搜索此功能使完整的依賴關係模型可查詢：尋找從以下位置讀取資料的每個預存程序 Orders每個來電者 GetCustomerOrders任何修改特定列的過程，只需幾秒鐘，即可在任意規模的資料庫環境中完成。

對於進行遺產現代化有些程式中儲存的過程編碼了數十年的業務邏輯，這些邏輯必須在遷移過程中保留下來。 SMART TS XL的分析提供了結構文檔，使得邏輯可提取，遷移順序可規劃。

資料庫層發揮其應有的作用

預存程序並非早期資料庫時代的遺物。它們才是放置那些本應存在於資料庫中的邏輯的正確位置：無論哪個應用程式存取數據，都必須保持一致的安全策略；受益於快取執行計劃的效能敏感型查詢；以及應該更新一次並在所有地方生效的業務規則。

陷阱不在於使用預存過程，而是放任它們發展成一個缺乏文件記錄、依賴關係未映射的網絡，最終導致無人能完全理解。一個執行關鍵業務計算的預存過程，如果沒有文檔化的呼叫者、沒有解釋其用途的頭部註釋，也沒有在修改前進行影響分析，那麼無論它編寫得多麼出色，都將是一個隱患。管理依賴關係圖與管理 SQL 本身同等重要。兩者都需要係統性的分析，而非經驗之談。

資料現代化影響分析影響分析軟體靜態程式碼分析工具