SQL 存儲過程入門到精通：核心概念、類型、語法、參數、返回值與實戰案例全解析

什麼是存儲過程？

預編譯、存儲於數據庫中的 SQL 代碼塊，接受參數、執行業務邏輯、返回結果。核心優勢：

• 性能：創建時編譯，執行復用計劃，減少解析優化開銷。
• 事務封裝：多表增刪改查打包為原子操作，配合事務保證一致性。
• 復用性：應用層僅調用接口，邏輯集中維護。
• 安全性：權限粒度控制至 EXECUTE，隱藏表結構，防注入。

存儲過程分類

標準創建語法

CREATE PROCEDURE [schema.]procedure_name
    [ @parameter_name data_type [ = default ] [ OUTPUT ] ] [ ,...n ]
    [ WITH { RECOMPILE | ENCRYPTION | EXECUTE AS clause } ]
AS
BEGIN
    -- SQL 语句块
    RETURN integer_return_code
END

關鍵選項：

• OUTPUT：標記輸出參數，調用端可讀取返回值。
• WITH RECOMPILE：每次執行重新編譯，適用參數嗅探導致計劃不穩定場景。
• WITH ENCRYPTION：加密定義文本，防止直接查看（非高強度加密）。

三種返回值機制對比

實戰案例演示

案例 1：基礎查詢存儲過程

CREATE PROC usp_QueryBooks
AS
    SELECT BookID, Title, Price FROM Books;
GO
EXEC usp_QueryBooks;

案例 2：帶輸入/輸出參數、加密、事務的插入過程

CREATE PROC usp_InsertBook
    @BookID CHAR(10),
    @Title VARCHAR(20),
    @Price MONEY,
    @TotalPrice MONEY OUTPUT
WITH ENCRYPTION
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT INTO Books (BookID, Title, Price) VALUES (@BookID, @Title, @Price);
        SELECT @TotalPrice = SUM(Price) FROM Books;
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        RETURN ERROR_NUMBER();
    END CATCH
END
GO

-- 調用示例
DECLARE @Total MONEY;
EXEC usp_InsertBook '003', 'Delphi 控件開發指南', 100, @Total OUTPUT;
PRINT '總金額: ' + CAST(@Total AS VARCHAR);

案例 3：多表關聯生成臨時表報表

CREATE PROC usp_SalesSummary
AS
BEGIN
    SELECT 
        p.ProductID, p.ProductName,
        o.CustomerName, o.Deposit,
        p.OrderQty * o.Deposit AS TotalAmount
    INTO #TempSales
    FROM Products p
    INNER JOIN Orders o ON p.ProductID = o.ProductID;
    
    IF @@ERROR = 0
        PRINT 'Success';
    ELSE
        PRINT 'Failed';
    
    SELECT * FROM #TempSales; -- 返回結果集
END

進階建議

• 命名規範：usp_[Module_]Action[ByCondition]（如 usp_Sales_GetOrdersByDateRange）。
• 參數類型與表列嚴格一致，避免隱式轉換導致索引失效。
• 業務邏輯盡量下沉，應用層保持輕薄。
• 大型過程拆分為主過程 + 子過程，便於單元測試與復用。