如果参数为null，则忽略WHERE子句中的列，否则使用in子句筛选WHERE子句中列

Question

我需要编写一个查询，如果相应的参数为Null，我应该忽略该列上的任何筛选器，但如果所述参数不为Null，则应该使用IN子句进行筛选。我正在尝试使用以下查询，但无法使其工作。这是Oracle 11 XE中的HR DB和Employees表，我正试图将Job ID作为参数传递，但此参数可能为null，也可能包含多个值。

到目前为止我所做的事情-

SELECT * FROM HR.EMPLOYEES
WHERE
CASE WHEN NVL(:PARAM_JOB_ID,'NONE')= 'NONE' THEN 'NONE' ELSE JOB_ID END IN NVL(:PARAM_JOB_ID,'NONE');

请引导。

Answer 1

我在这里使用IS NULL逻辑：

SELECT *
FROM HR.EMPLOYEES
WHERE JOB_ID IN (:PARAM_JOB_ID) OR :PARAM_JOB_ID IS NULL;

Answer 2

您必须将:PARAM_JOB_ID中的值拆分为多行。类似这样的东西(Scott的示例模式及其EMP表(：

select job, ename
from emp
where (   job in (select trim(regexp_substr(:param_job_id, '[^,]+', 1, level))
from dual
connect by level <= regexp_count(:param_job_id, ',') + 1
)
or :param_job_id is null
)
order by job, ename;                   

SQL*Plus:演示

SQL> select job, ename
2  from emp
3  where (   job in (select trim(regexp_substr('&&param_job_id', '[^,]+', 1, level))
4                    from dual
5                    connect by level <= regexp_count('&&param_job_id', ',') + 1
6                   )
7         or '&&param_job_id' is null
8        )
9  order by job, ename;
Enter value for param_job_id:                 --> empty parameter returns all rows
JOB       ENAME
--------- ----------
ANALYST   FORD
ANALYST   SCOTT
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER
CLERK     SMITH
MANAGER   BLAKE
MANAGER   CLARK
MANAGER   JONES
PRESIDENT KING
SALESMAN  ALLEN
SALESMAN  MARTIN
SALESMAN  TURNER
SALESMAN  WARD
14 rows selected.

---

SQL> undefine param_job_id
SQL> /
Enter value for param_job_id: CLERK, PRESIDENT
JOB       ENAME
--------- ----------
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER
CLERK     SMITH
PRESIDENT KING
SQL>

Answer 3

以下是我的作品-

WHERE
(column IN COALESCE(:list_var, column) OR column IN (:list_var))

如果该值为null，它将不会越过or条件，并且将与跳过此子句的所有行匹配。如果值存在，则条件的第一部分将匹配其中一个结果(基本上是COALESCE首先能够找到的值，子句的后面部分将处理其余部分。

希望这能帮助那些陷入类似问题的人。