oracle 如何在where子句中使用Oracle的解码功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30709048/
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 to use Oracle's decode function in where clause
提问by Yehuda N
I have a query with few filter criteria, one of them is get to the query as integer parameter. I want to use this filter only if this integer is > 0
I can't use NVL as it will never be null. How can I use DECODEin such case?
我有一个过滤条件很少的查询，其中之一是作为整数参数获取查询。我只想在这个整数 > 0 时使用这个过滤器我不能使用 NVL，因为它永远不会为空。DECODE在这种情况下我该如何使用？
SELECT (columns list)
FROM
AGREEMENT A
WHERE
A.ACCOUNT = 545
AND A.GRP_ID = NVL(?,A.GRP_ID)
The parameter ?I get is an Integer
?我得到的参数是一个整数
采纳答案by GolezTrol
You can use a case:
您可以使用一个案例：
SELECT (columns list)
FROM
AGREEMENT A
WHERE
A.ACCOUNT = 545
AND A.GRP_ID = CASE ? WHEN 0 THEN A.GRP_ID ELSE ? END
And Decode works in a similar fashion, although I think it's less readable.
和解码以类似的方式工作，尽管我认为它的可读性较差。
SELECT (columns list)
FROM
AGREEMENT A
WHERE
A.ACCOUNT = 545
AND A.GRP_ID = DECODE(?, 0, A.GRP_ID, ?)
But given the use case, making the parameter NULL would be a little better. After all, 0 is a value which you want to treat as a different value, while NULL semantically makes more sense for specifying 'no filter'.
但考虑到用例，将参数设为 NULL 会好一点。毕竟，0 是一个您希望将其视为不同值的值，而 NULL 在语义上更适合指定“无过滤器”。
回答by Gordon Linoff
Don't use decode(). It really is very old-fashioned. You can use caseor just put the right logic as in:
不要使用decode(). 它真的很老式。您可以使用case或仅放置正确的逻辑，如下所示：
where a.account = 545 and
(? = 0 or a.grp_id = ?)
Of course, this requires using the parameter twice. However, that would also be necessary with decode().
当然，这需要两次使用该参数。但是，这对于decode().
