Oracle: 当查询条件参数为空时忽略该参数的SQL选择查询语句

Question

假设我有一个 Oracle 查询：

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = ?
    AND DESIGNATION = ?
    AND DISTRICT = ?
    AND CIRCLE = ?

很可能其中一个、两个或三个参数为空或为null。
那么在where语句中应该怎样处理空参数，以便只搜索表中非空参数而完全忽略空参数？
我该如何实现呢？

请帮忙... 查询必须兼容oracle 10g。谢谢。

- anikesh sharma

---

你可以根据参数值构建查询模板。 - Alexey R.

类似 where (c1 = :param or :param is null) and (c2 = ... 的东西。 - jarlh

使用OR代替AND，以便匹配任一条件。 - XING

@XING - 你认为AND和OR可以互换使用？真的吗？ - user5683823

@mathguy请给它点赞并继续前进。 - XING

Answer 1

你可以像这样重写查询：

select * 
  from EMPLOYEE 
  where (DEPARTMENT  = p1 or p1 is null)  
    and (DESIGNATION = p2 or p2 is null) 
    and (DISTRICT    = p3 or p3 is null)
    and (CIRCLE      = p4 or p4 is null)

或者：

select * 
  from EMPLOYEE 
  where DEPARTMENT  = nvl(p1, department)
    and DESIGNATION = nvl(p2, designation)
    and DISTRICT    = nvl(p3, district)
    and CIRCLE      = nvl(p4, circle)

正如 @mathguy 在评论中提到的，第二个版本不会显示空值。请使用第一个版本。

- Ponder Stibbons

---

如果基础表中的CIRCLE（例如）为空，第二个版本会怎么做？这是期望的行为吗？正确答案是您提出的第一个版本。 - user5683823

我进行了简短的测试，索引被用于“nvl”版本中。 - Ponder Stibbons

@mathguy 经常情况下，当使用函数来过滤查询时，这将阻止索引的使用（并需要基于函数的索引）- 但是查询解析器可能会以不同的方式处理 NVL 并将函数重写为类似于 ((p4 IS NOT NULL AND CIRCLE = p4) OR (p4 IS NULL AND CIRCLE = CIRCLE)) 的内容。 - MT0

@mathguy - 你是对的。第二个版本从输出中删除了这一行。而这并不是我们想要的。 - Ponder Stibbons

@MT0 - 我的观点是，“x = :param or :param is null” 也 阻止了索引的使用。第一个版本比第二个版本更好，但“能够使用索引”不是其中的原因之一。 - user5683823

Answer 2

NVL会是您的好朋友。

这个函数需要两个输入参数，如果第一个参数为NULL，则返回第二个参数；否则返回第一个参数。

以下代码可以正常运行：

SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = NVL(yourParam1,DEPARTMENT)
    AND DESIGNATION = NVL(yourParam2,DESIGNATION )
    AND DISTRICT = NVL(yourParam3,DISTRICT )
    AND CIRCLE = NVL(yourParam4,CIRCLE )

- Dessma

---

@UsagiMiyamoto - 为什么？ - user5683823

@mathguy NVL评估两侧，coalesce会一直找到找到值。基本上，在大型数据集上，NVL会更慢。-- 信息[在这里]（https://dev59.com/5XNA5IYBdhLWcg3wdtr5） - JohnHC

@JohnHC - 如果NVL的第二个参数需要任何形式的评估，那么这是相关的。在这种情况下，DESIGNATION已经被评估了。还要注意的是，NVL比COALESCE存在更长时间，在某些情况下它可能实际上更快。 - user5683823

@Dessma - 如果基础表中的CIRCLE（例如）为空，会发生什么？这是期望的结果吗？（很可能不是！） - user5683823

Answer 3

LNNVL - 描述lnnvl如何评估值。

!= - 这是正确的。我没有在这里犯错误。

为什么lnnvl比column_name = nvl(param,column_name)更好。

如果列具有空值并且参数具有空值。

null = null => false，此行将从结果中排除。

select * from table
where lnnvl(column_name1 != ?) 
  and lnnvl(column_name2 != ?)
  .
  .
  .

- Arkadiusz Łukasiewicz

Answer 4

使用 decode：

SELECT *
FROM EMPLOYEE
WHERE decode(p_DEPARTMENT, NULL, 1, DEPARTMENT, 1, 0) = 1
    AND decode(p_DESIGNATION, NULL, 1, DESIGNATION, 1, 0) = 1
    AND decode(p_DISTRICT, NULL, 1, DISTRICT, 1, 0) = 1
    AND decode(p_CIRCLE, NULL, 1, CIRCLE, 1, 0) = 1

- I3rutt

---

如果 p_CIRCLE = NULL，那么这不是会排除所有非-NULL的 CIRCLE 值，而不是作为可选参数并允许任何 CIRCLE 值吗？ - MT0

@MT0 添加更改 - I3rutt

Answer 5

使用 COALESCE：

SELECT *
FROM EMPLOYEE
WHERE COALESCE(DEPARTMENT,0) = COALESCE(:yourParam1,DEPARTMENT,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam2,DESIGNATION,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam3,DISTRICT,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam4,CIRCLE,0)

- Mohammad Osman Gani Faisal

Answer 6

使用Coalesce的答案真是太聪明了。但是，零需要用单引号括起来。特别是在这个例子中，参数很可能不是数字，它会扰乱SQL，因为实际字段是一个字符串，但没有引号的0是一个数字。我将其应用于我的代码，它像魔法一样工作。建议：

    SELECT *
    FROM EMPLOYEE
    WHERE COALESCE(DEPARTMENT,'0') = COALESCE(:yourParam1,DEPARTMENT,'0')
    AND COALESCE(DESIGNATION,'0') = COALESCE(:yourParam2,DESIGNATION,'0')
    AND COALESCE(DISTRICT,'0') = COALESCE(:yourParam3,DISTRICT,'0')
    AND COALESCE(CIRCLE,'0') = COALESCE(:yourParam4,CIRCLE,'0')

非常感谢你，Mohammad Osman Gani Faisal!!!

- Choua Yang

---

把它放在 NVL 中会起作用吗，例如 WHERE NVL(department , '0') = NVL(:yourParam1 , '0')？ - wins999