查看: 5326|回复: 20

【话题讨论】有关NVL,DECODE,CASE WHEN,COALESCE疑问

[复制链接]
论坛徽章:
737
发表于 2014-5-3 18:59 | 显示全部楼层 |阅读模式
对于decode,case when,COALESCE这三个函数,Oracle Database uses short-circuit evaluation
即采用短路运算
有没有一个好点的方法,能够说明nvl是非短路运算,而另外三个采用的是短路运算。
如:
SELECT NVL((SELECT 'X' FROM DUAL WHERE 1 = 1),
           (SELECT 'Y' FROM DUAL WHERE 1 = 2))
  FROM DUAL;--假如第一个查询不空,如何知道第二个查询有没有执行
SELECT DECODE((SELECT 'X' FROM DUAL WHERE 1 = 1),
              'X',
              '1',
              (SELECT 'Y' FROM DUAL WHERE 1 = 2),
              'NO',
              'NULL')
  FROM DUAL;--同样,如果第一个查询结果为X,怎么知道第二个查询有没有执行做运算

看计划没看出来,我也短路了,没有想到测试方法,求指点。。。



论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2014-5-3 21:01 | 显示全部楼层
记得好像nvl只有一个参数

使用道具 举报

回复
论坛徽章:
525
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-5-3 23:12 | 显示全部楼层
CREATE OR REPLACE FUNCTION F1 RETURN NUMBER
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('F1 Called');
   RETURN 1;
END;
/

CREATE OR REPLACE FUNCTION F2 RETURN NUMBER
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('F2 Called');
   RETURN 2;
END;
/

SELECT NVL(F1(),F2()) FROM DUAL;

NVL(F1(),F2())
--------------
             1

1 row selected.

F1 Called
F2 Called
Elapsed: 00:00:00.64

使用道具 举报

回复
论坛徽章:
737
 楼主| 发表于 2014-5-4 06:04 来自手机 | 显示全部楼层
谢谢newkid !
我居然思维定势,只往sql 语句那个方向想了,其实一个简单的表达式就行

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2014-5-4 06:22 来自手机 | 显示全部楼层
pl=漂亮

使用道具 举报

回复
论坛徽章:
737
 楼主| 发表于 2014-5-4 13:09 | 显示全部楼层
SQL> set serveroutput on;
SQL> SELECT NVL(F1(), F2()) FROM DUAL;

NVL(F1(),F2())
--------------
             1

F1 Called
F2 Called
结论:无论F1()是否为空,F2()都是要进行处理的;

使用道具 举报

回复
论坛徽章:
737
 楼主| 发表于 2014-5-4 13:10 | 显示全部楼层
本帖最后由 oracle_cj 于 2014-5-4 13:10 编辑

SQL> SELECT decode(F1(),'1','f1 be handled','2', F2()) FROM DUAL;

DECODE(F1(),'1','F1BEHANDLED',
----------------------------------------
f1 be handled

F1 Called

结论:如果对F1()处理后满足条件,那么后面的表达式(sql,函数等)是不需要处理的;

使用道具 举报

回复
论坛徽章:
737
 楼主| 发表于 2014-5-4 13:18 | 显示全部楼层
本帖最后由 oracle_cj 于 2014-5-4 13:19 编辑

从上面的结论可以看到:
1.如果只有两个参数的情形,用COALESCE代替NVL是不错的选择
2.短路原则其实也提供了对sql的优化,比如COALESCE,如果有三个以上的参数需要做判断
SELECT  COALESCE(SQL1,SQL2,SQL3,...) FROM DUAL;
这样写虽然看着繁琐,但是比起下面的这种写法,效率要高
EXECUTE sql1,
EXECUTE sql2,
EXECUTE sql3,
...
IF ... THEN
  ...
END IF;
当然,COALESCE,DECODE,case WHEN 都是采用短路计算的方式。

借此抛砖一块,请各位大神指导

使用道具 举报

回复
认证徽章
论坛徽章:
169
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2014-5-4 14:57 | 显示全部楼层
newkid 发表于 2014-5-3 23:12
CREATE OR REPLACE FUNCTION F1 RETURN NUMBER
IS
BEGIN

需要这么麻烦吗?


select nvl(1,1/0) from dual;
select nvl(1,0) from dual;

使用道具 举报

回复
认证徽章
论坛徽章:
169
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
发表于 2014-5-4 14:59 | 显示全部楼层

select COALESCE(1,1/0,0/0) from dual;
select decode(1,1,1,1/0,0) from dual;
select case when 1=1 then 1 else 1/0 end from dual;

很好奇你想干嘛。不过短路算法可以提升性能倒是真的。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表