java - 甲骨文 : sql select query if condition parameter is null then ignore the parameter

Question

假设我有一个oracle查询

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

很可能参数 (?) 中的任何 1 个或 2 个或 3 个可以为空或 null。
那么我应该怎么做才能让where子句中的空参数完全“忽略”，只在表中搜索非空参数。
我怎样才能做到这一点

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

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 在评论中提到的，第二个版本不会显示空值。请使用第一个版本。