Oracle PL\SQL 空输入参数 WHERE 条件

Question

截至目前，我正在使用 IF ELSE 来处理这种情况，

IF INPUT_PARAM IS NOT NULL

    SELECT ... FROM SOMETABLE WHERE COLUMN = INPUT_PARAM
ELSE
    SELECT ... FROM SOMETABLE

有没有更好的方法可以在没有 IF ELSE 循环的单个查询中执行此操作。随着查询变得复杂，将会有更多这样的输入参数，并且所需的 IF ELSE 数量会太多。

Answer 1

一种方法是使用

 WHERE column = nvl(var, column)

There's 的变体，但是这里有两个陷阱：

1. 如果列可为空，则此子句将过滤空值，而在您的问题中，在第二种情况下您不会过滤空值。您可以修改此子句以将空值考虑在内，但它会变得很难看：

    WHERE nvl(列, 不可能的值) = nvl(var, 不可能的值)
   

   当然，如果以某种方式插入了impossible_value，您将遇到其他类型的（有趣）问题。

2. 优化器无法正确理解此类子句。它有时会生成一个包含 UNION ALL 的计划，但如果有多个 nvl，即使存在完全有效的索引，您也会得到完整扫描。

这就是为什么当有很多参数时（例如大表单中的多个搜索字段），我喜欢使用动态 SQL：

DECLARE
   l_query VARCHAR2(32767) := 'SELECT ... JOIN ... WHERE 1 = 1';
BEGIN
   IF param1 IS NOT NULL THEN
      l_query := l_query || ' AND column1 = :p1';
   ELSE 
      l_query := l_query || ' AND :p1 IS NULL';
   END IF;
   /* repeat for each parameter */
   ...
   /* open the cursor dynamically */
   OPEN your_ref_cursor FOR l_query USING param1 /*,param2...*/; 
END;

您还可以使用 EXECUTE IMMEDIATE l_query INTO l_result USING param1;

Answer 2

这应该有效

SELECT ... FROM SOMETABLE WHERE COLUMN = NVL( INPUT_PARAM, COLUMN )