Oracle SQL-如何使用可选搜索参数构建where子句 [英] Oracle SQL - How to build where clause with optional search parameters

问题描述

页面上有四个字段可以说

There are four field in the page lets say

EMPLOYEE ID
DEPT
LOCATION
UNIT:

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

User might enter any of the field values all are optional, if he enter EMPLOYEE ID then the query should return rows related to that EMPLOYEE ID. If he enters only LOCATION then the query should return all the employees of that location. How to write the where clause condition with optional parameters.

推荐答案

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

Oracle will likely build a well-optimized query if you use NVL in your predicates:

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不是真的.

The above code is mostly equivalent to LeoLozes's answer. Although his answer is more readable, in this case the cryptic version may run much faster. One important difference is that the above code will not work if the column is NULL. If you have nullable columns you'll need to use something like LeoLoze's answer, since null = null is not true.

Oracle习惯了NVL技巧，并且可以使用FILTER操作将该静态查询自动转换为动态查询.执行计划将同时具有FULL TABLE SCAN和INDEX RANGE SCAN，并将在运行时根据绑定变量的值选择适当的扫描.请参阅我的答案此处，以获取一些演示其工作原理的示例代码.

Oracle is used to the NVL trick, and can automatically convert this static query into a dynamic query using a FILTER operation. The execution plan will have both a FULL TABLE SCAN and and INDEX RANGE SCAN, and will pick the appropriate one at run-time, depending on the value of the bind variable. See my answer here for some sample code demonstrating how this works.

这篇关于Oracle SQL-如何使用可选搜索参数构建where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！