Oracle PL\SQL Null 输入参数 WHERE 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5014620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL\SQL Null Input Parameter WHERE condition
提问by Aseem Gautam
As of now I am using IF ELSE to handle this condition
截至目前，我正在使用 IF ELSE 来处理这种情况
IF INPUT_PARAM IS NOT NULL
SELECT ... FROM SOMETABLE WHERE COLUMN = INPUT_PARAM
ELSE
SELECT ... FROM SOMETABLE
Is there any better way to do this in a single query without IF ELSE loops. As the query gets complex there will be more input parameters like this and the amount of IF ELSE required would be too much.
有没有更好的方法在没有 IF ELSE 循环的单个查询中做到这一点。随着查询变得复杂，将会有更多这样的输入参数，并且所需的 IF ELSE 数量会太多。
回答by Vincent Malgrat
One method would be to use a variant of
一种方法是使用
WHERE column = nvl(var, column)
There are two pitfalls here however:
然而，这里有两个陷阱：
if the column is nullable, this clause will filter null values whereas in your question you would not filter the null values in the second case. You could modify this clause to take nulls into account but it turns ugly:
WHERE nvl(column, impossible_value) = nvl(var, impossible_value)Of course if somehow the
impossible_valueis ever inserted you will run into some other kind of (fun) problems.- The optimizer doesn't understand correctly this type of clause. It will sometimes produce a plan with a UNION ALL but if there are more than a couple of
nvl, you will get full scan even if perfectly valid indexes are present.
如果该列可以为空，则此子句将过滤空值，而在您的问题中，您不会在第二种情况下过滤空值。您可以修改此子句以将空值考虑在内，但它变得丑陋：
WHERE nvl(column, impossible_value) = nvl(var, impossible_value)当然，如果以某种方式
impossible_value插入，您将遇到其他类型的（有趣的）问题。- 优化器不能正确理解这种类型的子句。它有时会生成一个带有 UNION ALL 的计划，但如果有多个
nvl，即使存在完全有效的索引，您也会获得完整扫描。
This is why when there are lots of parameters (several search fields in a big form for example), I like to use dynamic SQL:
这就是为什么当有很多参数（例如一个大格式的几个搜索字段）时，我喜欢使用动态 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;
You can also use EXECUTE IMMEDIATE l_query INTO l_result USING param1;
你也可以使用 EXECUTE IMMEDIATE l_query INTO l_result USING param1;
回答by Anand
This should work
这应该工作
SELECT ... FROM SOMETABLE WHERE COLUMN = NVL( INPUT_PARAM, COLUMN )
