oracle 当我想在比较中使用它们时，我应该如何处理 PL/SQL 存储过程中的空参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4042496/
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
How should I deal with null parameters in a PL/SQL stored procedure when I want to use them in comparisons?
提问by Pip Falconer
I have a stored procedure with a parameter namewhich I want to use in a whereclause to match the value of a column i.e. something like
我有一个带有参数的存储过程name，我想在where子句中使用它来匹配列的值，即类似
where col1 = name
Now of course this fails to match nullto nullbecause of the way nullworks. Do I need to do
现在当然这不匹配null，null因为工作方式null。我需要做吗
where ((name is null and col1 is null) or col1 = name)
in situations like this or is there a more concise way of doing it?
在这种情况下还是有更简洁的方法？
回答by andr
回答by Jokke Heikkil?
I think your own suggestion is the best way to do it.
我认为你自己的建议是最好的方法。
回答by Tony Andrews
What you have done is correct. There is a more concise way, but it isn't really better:
你所做的是正确的。有一种更简洁的方法，但并不是更好：
where nvl(col1,'xx') = nvl(name,'xx')
The trouble is, you have to make sure that the value you use for nulls ('xx' is my example) couldn't actually be a real value in the data.
问题是，您必须确保用于空值的值（'xx' 是我的示例）实际上不能是数据中的真实值。
回答by Quassnoi
If col1is indexed, it would be best (performance-wise) to split the query in two:
如果col1被索引，最好（性能方面）将查询一分为二：
SELECT *
FROM mytable
WHERE col1 = name
UNION ALL
SELECT *
FROM mytable
WHERE name IS NULL AND col1 IS NULL
This way, Oraclecan optimize both queries independently, so the first or second part won't be actually executed depending on the namepassed being NULLor not.
这样，Oracle可以独立优化两个查询，因此第一或第二部分不会根据name传递的存在NULL与否而实际执行。
Oracle, though, does not index NULLvalues of fields, so searching for a NULLvalue will always result in a full table scan.
Oracle但是，不会索引NULL字段的值，因此搜索NULL值将始终导致全表扫描。
If your table is large, holds few NULLvalues and you search for them frequently, you can create a function-based index:
如果您的表很大，包含的NULL值很少并且您经常搜索它们，您可以创建一个基于函数的索引：
CREATE INDEX ix_mytable_col1__null ON mytable (CASE WHEN col1 IS NULL THEN 1 END)
and use it in a query:
并在查询中使用它：
SELECT *
FROM mytable
WHERE col1 = name
UNION ALL
SELECT *
FROM mytable
WHERE CASE WHEN col1 IS NULL THEN 1 END = CASE WHEN name IS NULL THEN 1 END
回答by Jon Heller
Keep it the way you have it. It's more intuitive, less buggy, works in any database, and is faster. The concise way is not always the best. See (PLSQL) What is the simplest expression to test for a changed value in an Oracle on-update trigger?
保持原样。它更直观，错误更少，适用于任何数据库，并且速度更快。简洁的方式并不总是最好的。请参阅(PLSQL) 在 Oracle 更新触发器中测试更改值的最简单表达式是什么？
回答by grokster
SELECT * FROM table
WHERE paramater IS NULL OR column = parameter;
