Oracle 在 WHERE 子句中使用 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43994835/
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 with CASE Statement in WHERE clause
提问by Morgs
Below is my attempt to include a CASE STATEMENTin a WHEREclause, it's not working. Anyone knows how I can correct this?
下面是我CASE STATEMENT在WHERE子句中包含 a 的尝试，它不起作用。任何人都知道我该如何纠正这个问题？
PROCEDURE GetBatchTotals(pEntityName VARCHAR DEFAULT NULL) IS
BEGIN
-- Sample Query
SELECT
e.ColumnName
FROM
Schema.TableName e
WHERE
(
CASE
WHEN pEntityName IS NULL THEN
e.ColumnName != '' -- Just to select everything in the table
ELSE
e.ColumnName = pEntityName
END
);
END GetBatchTotals;
I cannot seem to make sense of the examples I've seen already posted. Here's another attempt without using a CASE STATEMENT but returns no result:
我似乎无法理解已经发布的示例。这是不使用 CASE STATEMENT 但不返回任何结果的另一种尝试：
SELECT
e.ColumnName
FROM
Schema.TableName e
WHERE
(pEntityName IS NULL AND e.ColumnName != '') OR
(pEntityName IS NOT NULL AND e.ColumnName = pEntityName);
Anyway, here's the true sample query that returns no result:
无论如何，这是不返回任何结果的真实示例查询：
And then here's the table with data:
Thanks in advance.
然后是包含数据的表格：
提前致谢。
回答by nop77svk
Never forget that in Oracle SQL the ''literal (empty string) is the same as NULL, hence your predicate e.ColumnName != ''is equivalent to e.ColumnName != nullwhich always evaluates to NULL.
永远不要忘记，在 Oracle SQL 中，''文字（空字符串）与 相同NULL，因此您的谓词e.ColumnName != ''等价于e.ColumnName != nullwhich 始终计算为NULL。
Second problem is that you are trying output a boolean value from your CASE. No, Oracle can't use boolean expressions as results from functions. (Let's consider CASEto be a function, as I cannot remember a more proper name for these language constructs now.)
第二个问题是您正在尝试从CASE. 不，Oracle 不能使用布尔表达式作为函数的结果。（让我们考虑CASE一个函数，因为我现在不记得这些语言结构的更合适的名称。）
Try it this way:
试试这个方法：
PROCEDURE GetBatchTotals(pEntityName VARCHAR DEFAULT NULL) IS
BEGIN
-- Sample Query
SELECT ColumnName
FROM TableName e
WHERE
(pEntityName IS NULL AND e.ColumnName is not null) OR
(pEntityName IS NOT NULL AND e.ColumnName = pEntityName);
END GetBatchTotals;
There's a few more issues with your code, let's have them fixed:
您的代码还有一些问题，让我们修复它们：
(1)
Don't use varchar, use varchar2instead. A strange "enhancement" of Oracle's, don't ask.
(1)不要用varchar，用varchar2。Oracle 的一个奇怪的“增强”，别问了。
(2)
For equality predicates with non-NULLvalues you may leave off the predicate pEntityName is not nullfrom one of the AND-s, it will be implied.
(2) 对于具有非NULL值的相等谓词，您可以pEntityName is not null从AND-s之一中省略谓词，这将是隐含的。
(3)
Predicate e.ColumnName is not nullreally filters out the rows where e.ColumnNameis NULL, which is something different from your original idea to query all rows regardless of their e.ColumnNamevalue.
(3) Predicatee.ColumnName is not null真的过滤掉了 where e.ColumnNameis的行NULL，这和你原本想查询所有行而不考虑其e.ColumnName值的想法有所不同。
(4)
Either loop through the query results or bulk fetch them to a collection or return them as a ref cursor. In PL/SQL you mustput the query results into something! Let's loop through the query results and spool the ColumnNamecontents to server output...
(4) 遍历查询结果或将它们批量提取到集合中或将它们作为引用游标返回。在 PL/SQL 中你必须把查询结果放入一些东西中！让我们循环查询结果并将ColumnName内容假脱机到服务器输出...
PROCEDURE GetBatchTotals(pEntityName VARCHAR2 DEFAULT NULL) IS
BEGIN
for rec in (
SELECT ColumnName
FROM TableName e
WHERE pEntityName IS NULL -- Just to select everything in the table
OR e.ColumnName = pEntityName
) loop
dbms_output.put_line(rec.ColumnName);
end loop;
END GetBatchTotals;
回答by Ditto
I liked nop77svk answer, however, he never really clarified the issue with CASE (ie how to write the logic using CASE properly). So for completeness, here's how you handle that logic using CASE.
我喜欢 nop77svk 的回答，但是，他从未真正澄清过 CASE 的问题（即如何正确使用 CASE 编写逻辑）。因此，为了完整起见，以下是您如何使用 CASE 处理该逻辑。
PROCEDURE GetBatchTotals(pEntityName VARCHAR2 DEFAULT NULL) IS
BEGIN
-- Sample Query
SELECT
e.ColumnName
FROM
Schema.TableName e
WHERE
1 = CASE
WHEN pEntityName IS NULL AND e.ColumnName IS NOT NULL
THEN 1
WHEN e.ColumName = pEntityName
THEN 1
ELSE 0
END
);
END GetBatchTotals;
In this particular situation, nop77svk solution is likely the slightly easier/cleaner code :)
在这种特殊情况下，nop77svk 解决方案可能是稍微更简单/更干净的代码:)
回答by Ychdziu
Try this structure in your sql worksheet with your metadata:
使用元数据在 sql 工作表中尝试此结构：
declare
l_object_name varchar2(20) := null;-- your objects name
begin
for l_rec in (
select
e.object_name
from
all_objects e
where
(l_object_name is null and
1=1) or
(l_object_name is not null and
e.object_name = l_object_name))
loop
dbms_output.put_line('Object found: '||l_rec.object_name);
end loop;
end;
Test it.
测试一下。
