如何在Oracle APEX中用IF语句结合单选组切换报表？

原代码的核心问题 #

• PL/SQL语法错误：Oracle PL/SQL中多分支条件判断需用ELSIF，而非else if。
• 列别名不一致：Daily分支用Day，Weekly分支用WEEK，报表会因列名不匹配导致展示异常。
• 结果集处理缺失：直接在PL/SQL块中写SELECT语句，没有将结果返回给报表的逻辑，无法正常输出数据。

方案一：使用动态SQL（适合APEX游标型报表） #

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    IF :P33_RG = 'Daily' THEN
        v_sql := 'SELECT project_name, 
                        TO_CHAR(date_sys, ''MM/DD/YYYY'') AS period,      
                        SUM(BATCH.RECORDS_NUMBER) AS Count_of_Documents
                 FROM BATCH
                 WHERE date_sys BETWEEN :P33_START_DATE AND :P33_END_DATE
                 GROUP BY project_name, TO_CHAR(date_sys, ''MM/DD/YYYY'')';
    ELSIF :P33_RG = 'Weekly' THEN
        v_sql := 'SELECT project_name, 
                        TO_CHAR(TRUNC(date_sys, ''IW''), ''MM/DD/YYYY'') AS period, 
                        SUM(BATCH.RECORDS_NUMBER) AS Count_of_Documents
                 FROM BATCH
                 WHERE date_sys BETWEEN :P33_START_DATE AND :P33_END_DATE
                 GROUP BY project_name, TRUNC(date_sys, ''IW'')';
    END IF;
    OPEN :P33_REPORT_CURSOR FOR v_sql USING :P33_START_DATE, :P33_END_DATE;
END;

• 统一列别名为period，保证报表列结构固定。
• 拼接动态SQL后打开游标，APEX中需创建对应的游标页面项（如P33_REPORT_CURSOR），并将报表源设置为基于该游标。

方案二：合并为单条SQL查询（更简洁易维护） #

SELECT project_name,
       CASE WHEN :P33_RG = 'Daily' THEN TO_CHAR(date_sys, 'MM/DD/YYYY')
            WHEN :P33_RG = 'Weekly' THEN TO_CHAR(TRUNC(date_sys, 'IW'), 'MM/DD/YYYY')
       END AS period,
       SUM(BATCH.RECORDS_NUMBER) AS Count_of_Documents
FROM BATCH
WHERE date_sys BETWEEN :P33_START_DATE AND :P33_END_DATE
GROUP BY project_name,
         CASE WHEN :P33_RG = 'Daily' THEN TO_CHAR(date_sys, 'MM/DD/YYYY')
              WHEN :P33_RG = 'Weekly' THEN TRUNC(date_sys, 'IW')
         END

• 通过CASE表达式根据单选组值动态处理日期分组和展示格式，无需PL/SQL分支。
• 直接作为APEX报表的SQL源使用，避免动态SQL的复杂性，同时保证性能。

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