WHERE 子句中的 Oracle PL SQL CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22846107/
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 CASE in WHERE clause
提问by SomeRandomDeveloper
I know i could probably accomplish this by easily putting two whole statements in a CASE statement itself, but i'm new to SQL and trying to learn to write this the most efficient and intelligent way without duplicating any logic, and so i figure maybe there is a way i can turn a where condition line on or off basically.
我知道我可以通过轻松地将两个完整的语句放在 CASE 语句本身中来实现这一点，但我是 SQL 的新手并试图学习以最有效和最智能的方式编写它而不复制任何逻辑，所以我想也许有是一种我可以基本上打开或关闭 where 条件线的方法。
I have the following statement(which doesn't work btw, missing a keyword it says):
我有以下语句（顺便说一句，它不起作用，缺少它所说的关键字）：
OPEN O_CURSOR FOR
SELECT S.STORE_NO_4_DIGIT AS STORE_NO, S.STORE_NAME
FROM RFS.RF_STORE S
WHERE S.TYPE_CODE != 'W'
AND
CASE I_CAN_BE_CLOSED
WHEN 0 THEN
S.CLOSE_DATE IS NULL
END
ORDER BY S.STORE_NO_4_DIGIT;
There is two cases based on the input variable I_CAN_BE_CLOSED, values 0 or 1.
有两种情况基于输入变量 I_CAN_BE_CLOSED，值为 0 或 1。
In one case i want to allow it to return stores with only NULL CLOSE_DATES, and in the other case i want it to return either(both closing and non-closing stores).
在一种情况下，我希望允许它返回只有 NULL CLOSE_DATES 的商店，而在另一种情况下，我希望它返回（关闭和非关闭商店）。
回答by Justin Cave
It sounds like you just want to ORtogether a couple of predicates
听起来您只想OR将几个谓词组合在一起
AND ((i_can_be_closed = 0 and s.close_date IS NULL) or
(i_can_be_closed = 1))
回答by Kombajn zbo?owy
CASE expression cannot return boolean in Oracle SQL. This should be equivalent, provided that I_CAN_BE_CLOSED is never NULL:
CASE 表达式不能在 Oracle SQL 中返回布尔值。这应该是等效的，前提是 I_CAN_BE_CLOSED 从不为 NULL：
OPEN O_CURSOR FOR
SELECT S.STORE_NO_4_DIGIT AS STORE_NO, S.STORE_NAME
FROM RFS.RF_STORE S
WHERE S.TYPE_CODE != 'W'
AND (I_CAN_BE_CLOSED != 0 OR S.CLOSE_DATE IS NULL)
ORDER BY S.STORE_NO_4_DIGIT;
Edit:
编辑：
In fact both answers given here are correct... Easiest way to verify is to write down every case in a truth-false table:
事实上，这里给出的两个答案都是正确的......最简单的验证方法是在真假表中写下每个案例：
I_CAN_BE_CLOSED S.CLOSE_DATE [1] [2]
0 NULL FALSE OR TRUE = TRUE (TRUE AND TRUE) OR FALSE = TRUE
0 NOT NULL FALSE OR FALSE = FALSE (TRUE AND FALSE) OR FALSE = FALSE
1 NULL TRUE OR TRUE = TRUE (FALSE AND TRUE) OR TRUE = TRUE
1 NULL TRUE OR FALSE = TRUE (FALSE AND FALSE) OR TRUE = TRUE
[1] == "I_CAN_BE_CLOSED != 0 OR S.CLOSE_DATE IS NULL"
[2] == "((i_can_be_closed = 0 and s.close_date IS NULL) or (i_can_be_closed = 1))"
