萬盛學電腦網

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

SQL Server 動態游標建立與應用實戰:以預存程序封裝 SELECT 陳述式

動態游標的應用場景

在 SQL Server 開發中,常需根據執行時期才能確定的 SELECT 陳述式建立游標。由於 T-SQL 靜態游標要求編譯時期即知晓查詢語法,動態游標成為解決「查詢語句由參數傳入」問題的關鍵技術。

核心設計思路

本方案採用「臨時表中轉」策略:

  1. 解析傳入的 SELECT 陳述式,提取欄位清單與資料表清單
  2. 依欄位中繼資料動態建立臨時表結構
  3. 以 INSERT INTO #TempTable EXEC(@DynamicSQL) 將查詢結果填入臨時表
  4. 對臨時表宣告靜態游標並輸出

完整預存程序實作

CREATE PROCEDURE Usp_CreateCursor
    @Select_Command VARCHAR(8000),
    @Cursor_Return CURSOR VARYING OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Select_Command_Temp VARCHAR(8000),
            @Table_List VARCHAR(255),
            @Column_List VARCHAR(8000),
            @From_Pos INT, @Where_Pos INT, @Having_Pos INT,
            @Groupby_Pos INT, @Orderby_Pos INT, @Temp_Pos INT;

    /* 1. 正規化 SELECT 陳述式 */
    IF LEFT(LOWER(LTRIM(@Select_Command)), 6) <> 'select'
        SET @Select_Command = 'SELECT ' + @Select_Command;

    SET @Select_Command_Temp = LOWER(LTRIM(@Select_Command));
    IF LEFT(@Select_Command_Temp, 6) = 'select'
        SET @Select_Command_Temp = RIGHT(@Select_Command_Temp, LEN(@Select_Command_Temp) - 7);

    /* 2. 定位關鍵字位置,切割 TABLE 與 WHERE 子句 */
    SELECT @From_Pos = CHARINDEX(' from ', @Select_Command_Temp),
           @Where_Pos = CHARINDEX(' where ', @Select_Command_Temp),
           @Having_Pos = CHARINDEX(' having ', @Select_Command_Temp),
           @Groupby_Pos = CHARINDEX(' group by ', @Select_Command_Temp),
           @Orderby_Pos = CHARINDEX(' order by ', @Select_Command_Temp);

    SET @Temp_Pos = CASE 
        WHEN @Where_Pos > 0 THEN @Where_Pos
        WHEN @Having_Pos > 0 AND (@Temp_Pos = 0 OR @Having_Pos < @Temp_Pos) THEN @Having_Pos
        WHEN @Groupby_Pos > 0 AND (@Temp_Pos = 0 OR @Groupby_Pos < @Temp_Pos) THEN @Groupby_Pos
        WHEN @Orderby_Pos > 0 AND (@Temp_Pos = 0 OR @Orderby_Pos < @Temp_Pos) THEN @Orderby_Pos
        ELSE 0 END;

    IF @Temp_Pos > 0
        SET @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos + 6, @Temp_Pos - @From_Pos - 6);
    ELSE
        SET @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos + 6, LEN(@Select_Command_Temp) - @From_Pos - 5);

    /* 3. 解析欄位清單,處理 * 與 別名 */
    SET @Select_Command_Temp = LEFT(@Select_Command_Temp, @From_Pos - 1);
    WHILE LEN(@Select_Command_Temp) > 0
    BEGIN
        /* 以逗號分隔欄位,查詢 sys.columns 取得型別資訊 */
        /* ... 此處省略詳細解析迴圈,核心邏輯為:
           - 分割欄位名稱
           - 識別 TABLE.COLUMN 或 * 格式
           - 透過 sys.columns / sys.types 取得資料型別、長度、精度
           - 組裝 CREATE TABLE #Temp 所需欄位定義語法 */
    END

    /* 4. 動態建立臨時表並填入資料 */
    DECLARE @CreateTempSQL NVARCHAR(MAX) = 'CREATE TABLE #DynamicCursorTemp (' + @Column_List + ')';
    EXEC sp_executesql @CreateTempSQL;

    DECLARE @InsertSQL NVARCHAR(MAX) = 'INSERT INTO #DynamicCursorTemp ' + @Select_Command;
    EXEC sp_executesql @InsertSQL;

    /* 5. 宣告並輸出游標 */
    SET @Cursor_Return = CURSOR FORWARD_ONLY STATIC FOR
        SELECT * FROM #DynamicCursorTemp;
    OPEN @Cursor_Return;
END;

關鍵技術點解析

INSERT ... EXEC 模式

INSERT INTO #Temp EXEC(@SQL) 是將動態查詢結果物質化的核心技巧,繞過了「無法直接對字串變數宣告游標」的限制。

系統目錄查詢

利用 sys.columns、sys.types 取得欄位完整定義,確保臨時表結構與查詢結果完全對應,避免型別不符錯誤。

游標屬性選擇

FORWARD_ONLY STATIC 組合提供:唯讀、僅向前捲動、結果集在開啟時固定,效能最佳且避免並發衝突。

使用範例

DECLARE @MyCursor CURSOR;
EXEC Usp_CreateCursor 
    'SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate > ''2023-01-01''',
    @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor;  -- 正常使用游標
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

注意事項與限制

  • 臨時表生命週期隨 Session 結束自動清理,無殘留物件
  • 動態 SQL 需防範注入,生產環境建議加入參數化處理
  • 大量資料時考慮分頁或批次處理,避免 TempDB 壓力過大
  • SQL Server 2016+ 可改用 OPENJSON / STRING_SPLIT 簡化解析邏輯
copyright © 萬盛學電腦網 all rights reserved