萬盛學電腦網

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

SQL Server 動態交叉表 (Crosstab) 存儲過程:游標遍歷屬性列轉行實現

動態交叉表 (動態樞紐) 常用於 **屬性值模式 (EAV)** 或不定欄位報表。本文解析一個使用**游標遍歷屬性定義表、動態拼接 `CASE WHEN` 聚合列、執行動態 SQL** 的存儲過程實作,並提供現代化改寫方案 (無游標、參數化、防注入)。

一、 原始存儲過程邏輯解析

CREATE PROCEDURE P_GetSeriesProductDetail
    @series_guid VARCHAR(40),
    @condition   VARCHAR(1000) = ''
AS
BEGIN
    DECLARE @ParamNo NVARCHAR(5), @SQL NVARCHAR(4000) = N'';
    
    -- 1. 遊標遍歷該系列所有屬性參數編號
    DECLARE P_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT param_no FROM V_product_params WHERE series_guid = @series_guid;
    OPEN P_cursor;
    FETCH NEXT FROM P_cursor INTO @ParamNo;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 2. 動態拼接每個屬性的 CASE WHEN 聚合列
        -- MAX(CASE param_no WHEN 'P1' THEN param_value ELSE '' END) AS F_P1
        SET @SQL += N',MAX(CASE param_no WHEN ' + @ParamNo + N' THEN param_value ELSE N'''' END) AS F' + @ParamNo + NCHAR(13);
        FETCH NEXT FROM P_cursor INTO @ParamNo;
    END
    CLOSE P_cursor; DEALLOCATE P_cursor;
    
    -- 3. 組裝完整查詢
    SET @SQL = N'SELECT type_guid, series_guid, product_no, product_name' + @SQL + N'
        FROM V_product_params 
        WHERE series_guid = ' + QUOTENAME(@series_guid, N'''') + 
        CASE WHEN LTRIM(@condition) <> N'' THEN N' AND ' + @condition ELSE N'' END + N'
        GROUP BY type_guid, series_guid, product_no, product_name';
    
    PRINT @SQL;  -- 除錯用
    EXEC sp_executesql @SQL;  -- 執行動態 SQL
END

二、 關鍵技術點與風險點

三、 現代化重寫方案 (SQL Server 2017+ / 2016+ SP2)

方案 A:STRING_AGG 產生列清單 (無游標、集合操作)

CREATE OR ALTER PROCEDURE dbo.usp_GetSeriesProductDetail_Modern
    @series_guid   VARCHAR(40),
    @condition     NVARCHAR(MAX) = N''  -- 建議改為結構化參數 (JSON/Table-Valued Parameter)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 安全驗證 @condition (示例:僅允許簡單列=值 模式,生產需完整解析器)
    IF @condition <> N'' AND @condition NOT LIKE N'%[^a-zA-Z0-9_@=.,()'']%' -- 極簡示例,實際需嚴格
        THROW 50001, '查詢條件包含非法字元', 1;
    
    -- 2. 動態生成聚合列清單 (使用 STRING_AGG,SQL 2017+)
    DECLARE @pivotCols NVARCHAR(MAX);
    SELECT @pivotCols = STRING_AGG(
        N'MAX(CASE param_no WHEN ' + QUOTENAME(CAST(param_no AS NVARCHAR(10)), N'''') + 
        N' THEN param_value ELSE N'''' END) AS ' + QUOTENAME(N'F_' + CAST(param_no AS NVARCHAR(10))), 
        N',' + NCHAR(13)
    ) WITHIN GROUP (ORDER BY param_no)
    FROM V_product_params 
    WHERE series_guid = @series_guid;
    
    -- 3. 組裝最終查詢
    DECLARE @finalSql NVARCHAR(MAX) = N'
        SELECT type_guid, series_guid, product_no, product_name, ' + @pivotCols + N'
        FROM V_product_params
        WHERE series_guid = @series_guid 
          ' + CASE WHEN @condition <> N'' THEN N' AND ' + @condition ELSE N'' END + N'
        GROUP BY type_guid, series_guid, product_no, product_name;';
    
    -- 4. 參數化執行 (系列 GUID 參數化,條件部分仍為動態拼接 - 風險點)
    EXEC sp_executesql @finalSql, N'@series_guid VARCHAR(40)', @series_guid;
END

方案 B:靜態 PIVOT (若屬性集合固定/可預測)

SELECT type_guid, series_guid, product_no, product_name,
       MAX(CASE WHEN param_no = 'P1' THEN param_value END) AS F_P1,
       MAX(CASE WHEN param_no = 'P2' THEN param_value END) AS F_P2,
       ...
FROM V_product_params
WHERE series_guid = @series_guid
GROUP BY type_guid, series_guid, product_no, product_name;

方案 C:應用層轉置 (推薦)

  • 資料庫只回傳**正規化長表** (`product_no, param_no, param_value`)。
  • 應用層 (C#/Java/Python/前端 JS) 使用 `Pivot` / `DataTable` / `pandas.pivot_table` / `reduce` 進行轉置。
  • **優勢**:SQL 簡單、計畫穩定、無注入風險、應用層擴展性強、支援多級標頭/複雜格式化。

四、 EAV 模式索引優化建議

視圖 `V_product_params` 底層表建議索引:

-- 核心查詢模式:WHERE series_guid = ? GROUP BY product_no
CREATE INDEX IX_V_product_params_Series_Product 
ON V_product_params (series_guid, product_no) INCLUDE (param_no, param_value, type_guid, product_name);

結語

動態交叉表是 EAV 模式的典型應用痛點。**游標+動態 SQL 拼接** 是舊時代產物,現代 SQL Server 應優先採用 **`STRING_AGG` 生成列清單 + `sp_executesql` 參數化執行**,或更徹底地 **下推轉置邏輯至應用層**。若必須在 DB 層實現,**務必對動態拼接部分 (`@condition`) 實施嚴格輸入驗證/白名單/參數化**,杜絕 SQL 注入隱患。

copyright © 萬盛學電腦網 all rights reserved
技術點說明風險/缺陷
游標逐行處理屬性定義行**效能差**、佔用資源、鎖定系統表、難除錯。現代 SQL 可用 `STRING_AGG` / `FOR XML PATH` 替代。
動態 SQL 拼接構建 `CASE WHEN` 列清單**SQL 注入風險** (`@condition` 直接拼接);`NVARCHAR(4000)` 易溢位 (屬性多時);`PRINT` 截斷 8000 字元難除錯。
`QUOTENAME` 缺失`@series_guid` 用 `QUOTENAME` 防注入,但 `@condition` 完全裸奔高危!`@condition` 應改為參數化或嚴格白名單驗證。
`MAX(CASE...)` 模式將行屬性轉為列,`GROUP BY` 產品主鍵標準關係型轉置寫法,效能尚可 (單掃描)。
隱式轉換`param_no` 可能為數值型,拼接字串隱式轉換建議顯式 `CAST(@ParamNo AS NVARCHAR(10))`。