DB2 SQL 存儲過程實戰：按 Task_ID 聚合員工姓名與工時的游標邏輯實現

業務需求：行轉列聚合

源表 FROMTABLE 記錄任務明細：

目標表 TOTABLE 需聚合為：

現代寫法：LISTAGG / XMLAGG 一句搞定 (DB2 9.7+)

-- 方案 1: LISTAGG (最簡潔，需 DB2 10.1+ 或啟用兼容性向量)
INSERT INTO TOTABLE (ID, NAME, SUM)
SELECT TASK_ID,
       LISTAGG(EMPLOYEE_NAME, ',') WITHIN GROUP (ORDER BY EMPLOYEE_NAME),
       SUM(STEPWORKTIME)
FROM FROMTABLE
GROUP BY TASK_ID;

-- 方案 2: XMLAGG + REPLACE (通用，DB2 9.7+)
INSERT INTO TOTABLE (ID, NAME, SUM)
SELECT TASK_ID,
       REPLACE(REPLACE(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(',', EMPLOYEE_NAME))) AS VARCHAR(10000)), '<', ''), '>', ''),
       SUM(STEPWORKTIME)
FROM FROMTABLE
GROUP BY TASK_ID;

教學版：游標循環邏輯 (原文存儲過程重構)

適用於不支持聚合函數的老版本，或需在循環中執行複雜業務邏輯的場景。

表結構

CREATE TABLE FROMTABLE (ID VARCHAR(10), NAME VARCHAR(200), STEPWORKTIME DECIMAL(5,2));
CREATE TABLE TOTABLE (ID VARCHAR(100), NAME VARCHAR(5000), SUM DECIMAL(10,2));

存儲過程：雙層游標 + ROW_NUMBER 定位

CREATE PROCEDURE ADMINISTRATOR.PROC_CONCAT_NAME ()
LANGUAGE SQL
BEGIN
    -- 外層游標：按 TASK_ID 分組計數
    DECLARE C1 CURSOR FOR
        SELECT ID, COUNT(*) AS CNT
        FROM FROMTABLE
        GROUP BY ID
        ORDER BY ID;

    DECLARE V_ID VARCHAR(100);
    DECLARE V_CNT INT;
    DECLARE V_IDX INT;
    DECLARE V_NAMES VARCHAR(5000);
    DECLARE V_SUM DECIMAL(10,2);
    DECLARE AT_END INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 1;

    OPEN C1;
    OUTER_LOOP: LOOP
        FETCH C1 INTO V_ID, V_CNT;
        IF AT_END = 1 THEN LEAVE OUTER_LOOP; END IF;

        -- 內層：按 ROW_NUMBER 逐行提取拼接
        SET V_IDX = 1;
        SET V_NAMES = '';
        SET V_SUM = 0;

        INNER_LOOP: LOOP
            IF V_IDX > V_CNT THEN LEAVE INNER_LOOP; END IF;

            -- 子查詢定位第 V_IDX 行 (同一 TASK_ID 內)
            SET V_NAMES = COALESCE(V_NAMES || ',', '') || (
                SELECT NAME FROM (
                    SELECT ROW_NUMBER() OVER() AS RN, NAME
                    FROM FROMTABLE WHERE ID = V_ID
                ) T WHERE RN = V_IDX
            );

            SET V_SUM = V_SUM + (
                SELECT STEPWORKTIME FROM (
                    SELECT ROW_NUMBER() OVER() AS RN, STEPWORKTIME
                    FROM FROMTABLE WHERE ID = V_ID
                ) T WHERE RN = V_IDX
            );

            SET V_IDX = V_IDX + 1;
        END LOOP INNER_LOOP;

        INSERT INTO TOTABLE (ID, NAME, SUM) VALUES (V_ID, V_NAMES, V_SUM);
    END LOOP OUTER_LOOP;

    CLOSE C1;
END

關鍵點解析

• ROW_NUMBER() OVER() 在子查詢中為同一 TASK_ID 內行生成遞增序號，實現「按位置取值」
• 雙層循環：外層遍歷分組，內層遍歷組內行
• 字符串拼接用 COALESCE(V_NAMES || ',', '') 避免首個前綴逗號
• 數值類型用 DECIMAL 保留小數 (原文用 INT 導致 1.5 截斷為 1)

執行驗證

CALL ADMINISTRATOR.PROC_CONCAT_NAME();
SELECT * FROM TOTABLE;
-- 預期輸出:
-- 6262 | 張三,李四,王二 | 5.5
-- 6265 | 成某,趙某,錢某 | 5.5

性能對比與選型建議

結語

行轉列聚合是經典場景，**現代 DB2 首選 LISTAGG/XMLAGG 集合函數**，單語句完成、優化器最優、無上下文切換開銷。游標循環僅作為教學演示或極舊版本兜底。遷移老代碼時，務必識別此類模式並重構為集合操作。