Oracle WHERE 子句中的案例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8090234/
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
Case in Oracle WHERE clause
提问by Justin Samuel
Following oracle query complies and works fine:
以下 oracle 查询符合并正常工作：
SELECT
Employee.EmployeeId,
Employee.EmployeeName,
Employee.Description,
Employee.IsFrozen
FROM
employee, employeerole, roledef
WHERE
employee.employeeid = employeerole.employeeid
AND employeerole.roleid = roledef.roleid
AND rolename IN (
CASE
WHEN (1 < 2) THEN ('Owner Role')
WHEN (2 < 1) THEN ('Eval Owner Role')
END);
Now in my case I would like to add in second when ie (2 < 1) two rolename('Owner Role' and 'Eval Owner Role'). Kindly suggest how will the above query change.
现在，在我的情况下，我想添加第二个，即 (2 < 1) 两个角色名称（'所有者角色'和'评估所有者角色'）。请建议上述查询将如何更改。
Thanks in advance.
提前致谢。
-Justin Samuel
-贾斯汀·塞缪尔
回答by Justin Cave
Why use a CASE? Why not simply
为什么使用CASE? 为什么不简单
AND ( ( (1 < 2) and rolename IN ('Owner Role', 'Eval Owner Role') )
OR ( (2 < 1) and rolename IN ('Eval Owner Role') ) )
I am assuming that you don't actually have predicates that are hard-coded to evaluate to TRUE (1 < 2) or FALSE (2 < 1) and that those are actually bind variables in your actual code.
我假设您实际上没有硬编码来评估为 TRUE (1 < 2) 或 FALSE (2 < 1) 的谓词，并且这些谓词实际上是您实际代码中的绑定变量。
If you really want to use a CASEstatement, you could code
如果你真的想使用一个CASE语句，你可以编码
AND( CASE WHEN (1 < 2) and rolename IN ('Owner Role', 'Eval Owner Role')
THEN 1
WHEN (2 < 1) and rolename IN ('Eval Owner Role')
THEN 1
ELSE 0
END) = 1
but that is going to be much more difficult for the optimizer to deal with and much less clear for the developer that has to maintain it.
但这对于优化器来说将更难处理，而对于必须维护它的开发人员来说则更不清楚。
