Oracle SQL实现页面项为空忽略条件、非空时应用条件的需求

直接修改查询语句的WHERE子句，通过逻辑判断即可实现动态条件控制，无需额外组件，具体方案如下：

优化后的查询语句 #

WITH Emp_Leaves AS
    (select ID,
           EMPLOYEE_ID,
           LEAVE_YEAR,
           LEAVE_TYPE,
           LEAVE_DAYS,
           REMAINING_DAYS,
           CLOSED
    from LEAVES_PERSONEL
    where "EMPLOYEE_ID" = :P2136_EMPLOYEE_ID
    and (LEAVE_TYPE = :P2136_LEAVE_TYPE OR :P2136_LEAVE_TYPE IS NULL)
    and REMAINING_DAYS > 0 
    and CLOSED = 0
    order by LEAVE_YEAR, LEAVE_TYPE)

select ID,
       EMPLOYEE_ID,
       LEAVE_YEAR,
       LEAVE_TYPE,
       LEAVE_DAYS,
       REMAINING_DAYS,
       CLOSED
FROM Emp_Leaves;

逻辑说明 #

• 当页面项P2136_LEAVE_TYPE为空时，OR :P2136_LEAVE_TYPE IS NULL条件成立，整个括号内的判断等价于TRUE，不会对休假类型进行过滤，和初始查询逻辑完全一致。
• 当用户修改P2136_LEAVE_TYPE使其非空时，LEAVE_TYPE = :P2136_LEAVE_TYPE生效，自动过滤出对应休假类型的数据，满足需求中的动态条件效果。

补充方案（Oracle APEX专属） #

如果使用Oracle APEX框架，也可以通过动态SQL区域实现：

1. 创建动态SQL区域，用PL/SQL代码拼接查询语句：

DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    v_sql := 'WITH Emp_Leaves AS (select ID, EMPLOYEE_ID, LEAVE_YEAR, LEAVE_TYPE, LEAVE_DAYS, REMAINING_DAYS, CLOSED from LEAVES_PERSONEL where "EMPLOYEE_ID" = :P2136_EMPLOYEE_ID and REMAINING_DAYS > 0 and CLOSED = 0';
    
    IF :P2136_LEAVE_TYPE IS NOT NULL THEN
        v_sql := v_sql || ' and LEAVE_TYPE = :P2136_LEAVE_TYPE';
    END IF;
    
    v_sql := v_sql || ' order by LEAVE_YEAR, LEAVE_TYPE) select ID, EMPLOYEE_ID, LEAVE_YEAR, LEAVE_TYPE, LEAVE_DAYS, REMAINING_DAYS, CLOSED FROM Emp_Leaves';
    
    RETURN v_sql;
END;

2. 设置区域的返回类型为SQL查询，并绑定对应的页面项参数。

这种方式适合更复杂的动态条件场景，但第一种直接修改WHERE子句的方法更简洁高效，推荐优先使用。

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