这里使用IS NULL逻辑：

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

您必须将: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>

以下为我工作-

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

如果该值为null，则它不会越过or条件，并且将匹配所有行，从而有效地跳过该子句。如果value存在，条件的第一部分将匹配结果之一（基本上是COALESCE首先能够找到的任何值，子句的后面部分将处理其余部分。
希望这能帮助那些遇到类似问题的人。