Category: Oracle Database

NVL, NVL2 or COALESCE?

5 Comments

刚看完没什么感觉的春晚，闲来无事，再翻译一篇Eddie Awad的blog。

NVL, NVL2 or COALESCE?

跟别的RDBMS系统一样，Oracle中的空值(nulls)也应该被特殊对待。在Oracle中，有3个（也许更多？）函数可以用来处理空值，NVL, NVL2 和 COALESCE。我想要问你们一个问题，但是首先我自己得先温习一下。

NVL ( expr1 , expr2 )
如果expr1是空，那么NVL返回expr2。如果expr1不是空，那么NVL返回expr1。

NVL2 ( expr1 , expr2 , expr3 )
如果expr1是空，那么NVL2返回expr3。如果expr1不是空，那么NVL返回expr2。

COALESCE(expr[,expr]…)
返回参数列表中的第一个非空值。

当然我也可以使用CASE或者DECODE函数来处理空值。

下面左右的查询都返回相同的结果：

使用NVL:

HR@XE> select nvl(commission_pct,0)
2 from employees
3 where commission_pct is null
4 and rownum = 1;

NVL(COMMISSION_PCT,0)
———————
0

使用NVL2:

HR@XE> select nvl2(commission_pct,commission_pct,0)
2 from employees
3 where commission_pct is null
4 and rownum = 1;

NVL2(COMMISSION_PCT,COMMISSION_PCT,0)
————————————-
0

使用COALESCE:

HR@XE> select COALESCE(commission_pct,0)
2 from employees
3 where commission_pct is null
4 and rownum = 1;

COALESCE(COMMISSION_PCT,0)
————————–
0

使用CASE:

HR@XE> select
2 case
3 when commission_pct is null then 0
4 else commission_pct
5 end commission_pct
6 from employees
7 where commission_pct is null
8 and rownum = 1;

COMMISSION_PCT
————–
0

使用DECODE:

HR@XE> select
2 decode(commission_pct,null, 0,
3 commission_pct) commission_pct
4 from employees
5 where commission_pct is null
6 and rownum = 1;

COMMISSION_PCT
————–
0

我通常都是使用NVL函数来检查空值的，但是，看上去COALESCE函数更加普遍（可以检查多个参数）并且COALESCE是一个所有关系型数据库系统都支持的标准函数。

那么是不是我该改变一下自己使用NVL的习惯转而使用COALESCE呢？各位有什么建议？

请使用这个链接进行函数使用率的投票。

全文完

译者：实际上吸引我的倒不是这篇文章，因为它太简单，呵呵。但是Eddie在文章的最后有一个投票的界面，还挺美观的，按照链接进去看了一下，dPolls，一个很实用的网上投票系统，并且提供了脚本用以将投票界面显示在自己的网页中，看来个人中心越来越受到服务提供商的重视了，无论是Flickr, Del.icio.us, 还是Planzo都提供了这样的脚本，网络中各种服务对于特定用户的体验都允许集中到这个用户自己的网页上，是个很好的现象，对吧。

注册dPolls很简单，注册完毕以后可以创建自己的投票，创建完毕以后dPolls会给出一段代码，将这段代码粘贴在自己的文章中就OK了，下面是我模仿Eddie作的一个完全相同的函数使用率投票，只是show一下界面，大家不要在我这里投，请使用上面的那个Eddie创建的投票链接。


Create polls and vote for free. dPolls.com

Cool SQL function: DUMP

4 Comments

征求了Eddie Awad的意见，计划以后不定期地翻译一些他的文章，这次先来一个比较简单的，也是Eddie昨天刚写的 – Cool SQL function: DUMP。

Cool SQL function：DUMP

我注意到在Tom Kyte的新书中，他多次使用了DUMP函数来获得一些内部的存储样式以及指定表达式的大小。老实说，我不怎么常使用这个函数，但是在某些情况下这个函数确实很有用。

语法摘自Oracle documentation

DUMP(expr[, return_fmt
            [, start_position [, length ] ]
         ]
    )

DUMP函数返回一个包含了数据类型，字节长度和内部存储形式的VARCHAR2值。返回值通常是符合自己数据库的字符集类型的。

比如：


HR@XE> select dump('hello') from dual;

DUMP('HELLO')
---------------------------------
Typ=96 Len=5: 104,101,108,108,111

返回值告诉我们”hello”字串的长度是5ge字节，内部存储格式是”104,101,108,108,111″，数据类型是96，表示是字符串。Oracle文档给出了完整的类型代码以及说明。

默认是10进制格式，我们当然也可以转化成八进制，十六进制，甚至转成单个字符的样式。

比如，转成8进制

HR@XE> select dump('hello',8) from dual;

DUMP('HELLO',8)
---------------------------------
Typ=96 Len=5: 150,145,154,154,157

转成16进制：

HR@XE> c/8/16
  1* select dump('hello',16) from dual
HR@XE> /

DUMP('HELLO',16)
----------------------------
Typ=96 Len=5: 68,65,6c,6c,6f

转成单个字符：

HR@XE> c/16/17
  1* select dump('hello',17) from dual
HR@XE> /

DUMP('HELLO',17)
-----------------------
Typ=96 Len=5: h,e,l,l,o

默认情况下，返回值不包含字符集信息，如果我们想获得字符集信息的话，可以在转化字上加1000。比如，1017（用以替换17）表示返回单个字符样式并且同时返回字符集信息。

HR@XE> c/17/1017
  1* select dump('hello',1017) from dual
HR@XE> /

DUMP('HELLO',1017)
-------------------------------------------------
Typ=96 Len=5 CharacterSet=WE8MSWIN1252: h,e,l,l,o

只要指定开始位置和长度，你也同样可以要求Oracle只返回内部存储值的一部分。

HR@XE> select dump('hello',17,1,3) from dual;

DUMP('HELLO',17,1,3
-------------------
Typ=96 Len=5: h,e,l

注意返回值里面的长度仍然是5，也就是说只有内部存储值这个部分才被截取了。

有些好奇，又没有人在自己的产品环境中使用了DUMP函数，并且整个程序的逻辑都是根据这个函数的返回值来运行的呢？

— 全文完

译者注：Eddie的blog上提供了多种语言的翻译，使用的应该是一个WP的插件，但是实际上翻译效果是让人忍俊不禁的，比如翻成中文，那么这篇blog的题目就变成了“凉快 SQL 作用: 转储”，OMG，凉快的作用…… 我倒是建议Eddie去掉这个功能算了。

另外，Eddie原创的几个Firefox Search Plugin for Oracle还是比较方便的，用Firefox的Oracle DBA们可以去下载来尝试一下。

China Oracle Blogger

7 Comments

eygle在Frappr上创建了一个China Oracle Blogger的组，短短的几天，已经有将近50个成员了，好些熟悉的面孔都在里面。

Frappr是个很有趣的应用，很早以前就收在自己的delicious中，遗憾的是只有在美国，这个地域的服务才有更加实用的前景，因为地图更精细，而对于中国来说，仅仅是一个大大的雄鸡，标注地域也只能是好玩一下了吧。

Frappr像很多Web2.0应用一样，提供了大量的脚本，方便各个blogger将内容嵌入到自己的blog中，比如说像下面这个滚动的组内图片。

最后，欢迎所有在国内的Oracle DBA都来这个地图上标记一下，看看到底能有多少人。或许我们也可以用这个来统计国内的OCP人数，OCM人数吧，呵呵。