SQL Server 儲存程序深度解析（三）：加密選項與游標整合應用

本篇揭示 WITH ENCRYPTION 如何隱藏程式碼邏輯，並示範儲存程序搭配游標進行資料逐列處理的進階模式。

WITH ENCRYPTION：原始碼混淆保護

加入 WITH ENCRYPTION 選項後，CREATE PROCEDURE 文字會被轉換為模糊格式：

• sys.sql_modules.definition 回傳 NULL。
• sp_helptext 顯示：「The text for object '...' is encrypted.」

IF OBJECT_ID('yangyang8848.OneGoods') IS NOT NULL
    DROP PROC yangyang8848.OneGoods;
GO

CREATE PROC yangyang8848.OneGoods
    @GoodsCode2 varchar(10) OUT,
    @GoodsCode varchar(10) = '0011'
WITH ENCRYPTION
AS
    SELECT * FROM Master_Goods WHERE GoodsCode = @GoodsCode;
    SET @GoodsCode2 = '0005';
GO

安全性提醒： 此加密非軍事級，擁有 DAC 連線權限的系統管理員或附加偵錯器的使用者仍可從記憶體還原原始碼。建議搭配模組簽署或 CLR 組件進行更強保護。

儲存程序與游標整合實戰

場景：一個程序負責開啟游標並回傳給呼叫端，另一個程序負責迭代游標列印結果。

步驟 1：建立回傳游標的程序 (VARYING OUTPUT)

IF OBJECT_ID('dbo.GetMasterGoods') IS NOT NULL
    DROP PROC dbo.GetMasterGoods;
GO

CREATE PROC GetMasterGoods
    @MyCursor CURSOR VARYING OUTPUT
WITH ENCRYPTION
AS
    SET @MyCursor = CURSOR FOR
        SELECT GoodsCode, GoodsName FROM Master_Goods;
    OPEN @MyCursor;
GO

• CURSOR VARYING OUTPUT：宣告此參數為游標資料型別且可輸出，VARYING 為游標參數必備關鍵字。
• 程序內建立、開啟游標，呼叫端即可直接 FETCH。

步驟 2：建立消費游標的程序

CREATE PROC GetAllGoodsIDAndName
AS
    DECLARE @GoodsCode varchar(18), @GoodsName nvarchar(20);
    DECLARE @MasterGoodsCursor CURSOR;

    -- 取得游標
    EXEC GetMasterGoods @MasterGoodsCursor OUTPUT;

    -- 迭代處理
    FETCH NEXT FROM @MasterGoodsCursor INTO @GoodsCode, @GoodsName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @GoodsCode + ':' + @GoodsName;
        FETCH NEXT FROM @MasterGoodsCursor INTO @GoodsCode, @GoodsName;
    END

    -- 清理資源
    CLOSE @MasterGoodsCursor;
    DEALLOCATE @MasterGoodsCursor;
GO

執行結果

EXEC GetAllGoodsIDAndName;
-- 輸出範例：
0003:品0003
0004:品0004
0005:123123
...

此模式實現「資料存取邏輯封裝於加密程序」與「業務迭代邏輯分離」的架構，提升安全性與維護性。