如何循环实现SQL多DB列SUM(DECODE)求和查询直至无数据

你当前使用的是Oracle数据库（从DECODE函数语法可判断），静态SQL无法在运行时自动识别未知枚举值动态生成查询列，要实现自动遍历所有DBx格式的bin值做行转列聚合，无需手动逐个编写SUM(DECODE())片段，可通过动态SQL实现，以下是两种可直接使用的实现方式：

方案1：动态生成可直接执行的SQL文本 #

这个方案最轻量，执行PL/SQL块后会自动拼接好完整查询语句，复制输出结果即可直接运行，适合临时查询场景：

-- 执行前先开启服务器输出：SQL*Plus执行SET SERVEROUTPUT ON SIZE UNLIMITED，PL/SQL等工具打开DBMS Output面板
DECLARE
    v_full_sql CLOB;
    v_base_select CLOB := 'SELECT locations, lotid, loadseq, startdate';
BEGIN
    -- 遍历当前查询范围内所有存在的DB编号，自动拼接聚合逻辑
    FOR db_rec IN (
        SELECT DISTINCT bintype || binnum AS db_col
        FROM test_summary_bin
        WHERE bintype || binnum LIKE 'DB%'
          AND lotid = 'C659300' -- 限定查询范围，避免生成无数据的冗余列
        ORDER BY TO_NUMBER(REPLACE(bintype || binnum, 'DB', '')) -- 按DB后的数字升序排列，和手动写的列顺序一致
    ) LOOP
        v_base_select := v_base_select || 
            ', SUM(DECODE(bintype||binnum, '''||db_rec.db_col||''', binvalues)) AS '||LOWER(db_rec.db_col);
    END LOOP;

    -- 拼接查询的剩余部分
    v_full_sql := v_base_select || q'[
        FROM test_summary_bin
        WHERE lotid = 'C659300'
        GROUP BY locations, lotid, loadseq, startdate
        ORDER BY startdate
    ]';

    -- 输出拼接完成的SQL
    DBMS_OUTPUT.PUT_LINE(v_full_sql);
END;
/

执行后输出的SQL会自动包含所有存在数据的DB列，后续如果新增DB编号（比如DB3、DB4...），重新执行这个块生成新SQL即可，不需要手动修改代码加列。

方案2：存储过程直接返回结果集 #

如果需要频繁查询，可以写存储过程直接返回动态拼接后的结果，不需要每次复制生成的SQL：

CREATE OR REPLACE PROCEDURE query_db_bin_agg(
    p_input_lotid VARCHAR2,
    p_result_cursor OUT SYS_REFCURSOR
) AS
    v_full_sql CLOB;
    v_base_select CLOB := 'SELECT locations, lotid, loadseq, startdate';
BEGIN
    FOR db_rec IN (
        SELECT DISTINCT bintype || binnum AS db_col
        FROM test_summary_bin
        WHERE bintype || binnum LIKE 'DB%'
          AND lotid = p_input_lotid
        ORDER BY TO_NUMBER(REPLACE(bintype || binnum, 'DB', ''))
    ) LOOP
        v_base_select := v_base_select || 
            ', SUM(DECODE(bintype||binnum, '''||db_rec.db_col||''', binvalues)) AS '||LOWER(db_rec.db_col);
    END LOOP;

    v_full_sql := v_base_select || q'[
        FROM test_summary_bin
        WHERE lotid = :b_lotid
        GROUP BY locations, lotid, loadseq, startdate
        ORDER BY startdate
    ]';

    OPEN p_result_cursor FOR v_full_sql USING p_input_lotid;
END;
/

存储过程调用方式：

VAR res_cur REFCURSOR;
EXEC query_db_bin_agg('C659300', :res_cur);
PRINT res_cur;

补充说明：如果你的Oracle版本支持PIVOT语法，也可以把拼接部分的SUM(DECODE)换成PIVOT对应的聚合语法，逻辑本质一致，动态拼接的实现思路不变。

内容的提问来源于stack exchange，提问作者Krispol Kittinatkaiwat