Oracle 中的 if(条件, then, else)
MySQL/MSSQL 有一个简洁的小内联 if 函数,您可以在查询中使用它来检测空值,如下所示。
SELECT
...
foo.a_field AS "a_field",
SELECT if(foo.bar is null, 0, foo.bar) AS "bar",
foo.a_field AS "a_field",
...
我现在遇到的问题是这段代码在 Oracle 数据库上运行不安全,因为它似乎不支持这种内联 if 语法。
Oracle 中有类似的吗?
MySQL/MSSQL has a neat little inline if function you can use within queries to detect null values, as shown below.
SELECT
...
foo.a_field AS "a_field",
SELECT if(foo.bar is null, 0, foo.bar) AS "bar",
foo.a_field AS "a_field",
...
The problem I'm running into now is that this code is not safe to run on an Oracle database, as it seems not to support this inline if syntax.
Is there an equivalent in Oracle?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
为了补充此处的其余答案,这些答案主要处理 NULL 值和 COALESCE/NVL/NVL2:
CASE 语句显然不是那么简洁,但它们面向灵活性。当您的条件不基于 NULL 时,这特别有用。
To supplement the rest of the answers here, which deal primarily with NULL values and COALESCE/NVL/NVL2:
CASE statements are not as succinct, obviously, but they are geared towards flexibility. This is particularly useful when your conditions are not based on NULL-ness.
使用标准 COALESCE 函数:
或者使用 Oracle 自己的 NVL 函数来执行相同的操作。
Use the standard COALESCE function:
Or use Oracle's own NVL function that does the same.
您想要使用 NVL 或 NVL2
You want to use NVL, or NVL2
是的, NVL 应该可以解决问题。
Yup, NVL should do the trick.
您需要 nvl 函数: nvl(foo.bar, 0)
Oracle 也支持各种 if 和 case。
You want the nvl function: nvl(foo.bar, 0)
Oracle does support various ifs and cases as well.
只需执行
nvl(foo.bar,0)
即可,无需执行Just do
nvl(foo.bar,0)
, you don't have to do