Oracle 和 where 子句中的可选存储过程参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17852534/
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 and optional stored procedure parameter in where clause
提问by Doc
I have a stored procedure with an optional parameter like this
我有一个带有这样的可选参数的存储过程
create or replace
PROCEDURE "my_stored_procedure" (param1 int, param2 int default null)
IS
BEGIN
[select some fields from some tables]
...
I need a whereclause with a if on the default parameter (if it is set), but I don't even know if it's possible...
我需要一个where在默认参数上带有 if的子句（如果已设置），但我什至不知道是否可能......
Something like
就像是
where param1=123 and (if param2 is not null then some_value != param2)
The selectclause it's pretty long and complex, so I'll prefer to have a "flexible" WHERErather than a structure like
该select条款是相当漫长和复杂的，所以我希望有一个“灵活” WHERE，而不是像一个结构
if param2 is not null then
[long and complex select] where param1=123 and some_value != param2
else
[long and complex select] where param1=123
Is this possible?
这可能吗？
回答by A.B.Cade
In this case you can do:
在这种情况下，您可以执行以下操作：
where param1=123 and (param2 is null or param2 != some_value)
If param2 is not null - then it's true only if param2 != some_value- as expected
If param2 is null - then it returns true no matter what some_valueis
如果参数2不为空-那么这是真的只有当param2 != some_value-预期
如果参数2为空-那么它返回true不管some_value是
回答by Ian
Initially we used this syntax:
最初我们使用这个语法：
WHERE (pParameter = COLUMN OR pParameter IS NULL)
until a database tuning specialist found out that this causes Oracle to perform full table scans and ignore the indexes, because of using OR.
直到数据库调优专家发现这会导致 Oracle 执行全表扫描并忽略索引，因为使用了 OR。
Now we use
现在我们用
WHERE decode(pParameter,
null, 1, --if parameter is null, then return 1
COLUMN, 1, -- if parameter matches the value in the column, then return 1
0) --else return 0
= 1
this construct allows an easy and readable way to add special handling for special values
此构造允许以简单易读的方式为特殊值添加特殊处理
WHERE decode(pParameter,
null, 1, --if parameter is null, then allow the row
'abc', 1, --if parameter is 'abc', then always allow the row
'xyz', 1, --if parameter is 'xyz', then always reject the row
COLUMN, 1, -- if parameter matches the value in the column, then allow
0) --else return 0 to reject the row
= 1
Alternatively, you can rewrite this with COALESCE or with CASE:
或者，您可以使用 COALESCE 或 CASE 重写它：
WHERE COALESCE(pParameter, COLUMN, 'ok') = COALESCE(COLUMN, 'ok')
or, sample using CASE:
或者，使用 CASE 进行示例：
THEN (
case
when pParameteris null then 1
when pParameter= COLUMN then 1
else 0
end
) = 1
