oracle - 如何使用可选搜索参数构建 where 子句

Question

页面中有四个字段，可以说

EMPLOYEE ID
DEPT
LOCATION
UNIT:

用户可以输入任何字段值，所有字段值都是可选的，如果他输入EMPLOYEE ID，则查询应返回与该EMPLOYEE ID相关的行。如果他仅输入LOCATION，则查询应返回该位置的所有员工。如何编写带有可选参数的 where 子句条件。

Answer 1

如果您在谓词中使用 NVL，Oracle 可能会构建一个优化良好的查询:

select *
  from employee
 where employee_id = nvl(:employee_id, employee_id)
   and dept = nvl(:dept, dept)
   and location = nvl(:location, location)
   and unit = nvl(:unit, unit)

上面的代码基本上等同于 LeoLozes 的答案。尽管他的答案更具可读性，但在这种情况下，神秘版本可能运行得更快。一个重要的区别是，如果列为 NULL，则上述代码将不起作用。如果您有可为空的列，则需要使用类似于 LeoLoze 的答案，因为 null = null 不是 true。

Oracle习惯了NVL技巧，并且可以使用FILTER操作自动将此静态查询转换为动态查询。执行计划将同时具有全表扫描和索引范围扫描，并将在运行时根据绑定(bind)变量的值选择适当的一个。看我的回答here一些示例代码演示了它是如何工作的。