Oracle 的函数 NVL 和 DECODE 解析

昝灵旋

　　
SQL语句的DECODE和NVL()函数
　　
SELECTDECODE(WP01.ONDO_KBN,0,'??象外'1,'常温',2,'保冷') AS ONDO_KBN
　　
FROM  WP01_S_HAITOTAL WP01
　　
//分析: 当WP01.ONDO_KBN=0时,将"??象外"赋值
　　
当WP01.ONDO_KBN=1时,将"常温"赋值
　　
当WP01.ONDO_KBN=2时,将"保冷"赋值
　　

　　
NVL()函数:
　　
NVL(ARG,VALUE)达标如果前面的ARG值为NULL那么返回的值为后面的VALUE二者结合使用:
　　

　　
DECODE(NVL(M01.NINUSI_NM,''),'','-',M01.NINUSI_NM)
　　
//分析:
　　
首先判断--若M01.NINUSI_NM是否为空.若为空赋给空值[NVL函数]
　　
其次,判断取出的字段是否为空,如果是则赋给'-'
　　
最后的默认处理是M01.NINUSI.NM。
　　

　　
DECODE函数
　　
DECODE是Oracle公司独家提供的功能，它是一个功能很强的函数。它虽然不是SQL的 标准，但对于性能非常有用。到目前，其他的数据库供应商还不能提供类似DECODE的功能，甚至有的数据库的供应商批评Oracle的SQL不标准。实际 上，这种批评有些片面或不够水平。就象有些马车制造商抱怨亨利。福特的“马车”不标准一样。
　　
1 DECODE 中的if-then-else逻辑
　　
在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：
　　
DECODE(value, if1,then1, if2,then2, if3,then3, . . . else )
　　
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
　　
需要注意的是，这里的if、then及else 都可以是函数或计算表达式。
　　
含义解释：
　　
DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
　　

　　
该函数的含义如下：
　　
IF 条件=值1 THEN
　　
RETURN(翻译值1)
　　
ELSIF 条件=值2 THEN
　　
RETURN(翻译值2)
　　
......
　　
ELSIF 条件=值n THEN
　　
RETURN(翻译值n)
　　

　　
ELSE
　　
RETURN(缺省值)
　　
END IF
　　

　　
2 DECODE 的简单例子
　　
Oracle系统中就有许多数据字典是使用decode 思想设计的，比如记录会话信息的V$SESSION数据字典视图就是这样。我们从《Oracle8i/9iReference》资料中了解到，当用户登录成功后在V$SESSION中就有该用户的相应记录，但用户所进行的命令操作在该视图中只记录命令的代码 （0—没有任何操作，2—Insert…），而不是具体的命令关键字。因此，我们需要了解当前各个用户的名字及他们所进行的操作时，要用下面命令才能得到 详细的结果：
　　
selectsid,serial#,username,
　　
DECODE(command,
　　
0,’None’,
　　
2,’Insert’,
　　
3,’Select’,
　　
6,’Update’,
　　
7,’Delete’,
　　
8,’Drop’,
　　
‘Other’) cmmand
　　
from v$sessionwhere username is not null;
　　
3 DECODE实现表的转置
　　
数据库中的表是由列和行构成的一个二维表。一般列在任何数据库中都是有限的数量，而行的变化较大，如果表很大，行的数量可能大上千万行。同一列的不同行可能有不同的值，而且不是预先定义的。
　　
例:住房公积金报表置换实例：
　　
1.各个单位在本地经办行进行开户，开户就是将单位的基本信息和职工信息的进行登记；
　　
2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金，系统记录有每个职工的交缴明细并在每条记录上记录有经办行的代码；
　　
3.每月、季、半年及年终都要求将经办行 变为“列”给出个月的明细报表：
　　
经办行：城西区 城东区
　　
月份：
　　
2001.01 xxxx1.xxxxxxx2.xx
　　
2001.02 xxxx3.xxxxxxx4.xx
　　
。 。 。 。 。 。
　　
原来的数据顺序是：
　　
城西区2001.01 xxxxx1.xx
　　
城东区2001.01 xxxxx2.xx
　　
城西区2001.02 xxxxx3.xx
　　
城东区2001.02 xxxxx4.xx
　　
住房公积金系统记录职工的每月交缴名细的pay_lst表结构是：
　　
bank_codevarchar2(6)NOT NULL, -- 经办行代码
　　
acc_no varchar2(15)not null, -- 单位代码(单位帐号)
　　
emp_acc_novarchar2(20) not null, -- 职工帐号
　　
tran_date date notnull, -- 交缴日期
　　
tran_valNumber(7,2) not null, -- 交缴额
　　
sys_date datedefault sysdate, --系统日期
　　
oper_idvarchar2(10) --操作员代码
　　
这样的表结构，一般按照将经办行作为行(row)进行统计是很容易的，但是如果希望将经办行变为列(column)这样的格式来输出就有困难。如果用DECODE函数来处理则变得很简单：
　　
我们创建一个视图来对目前的pay_lst表进行查询。将经办行代码变为一些具体的经办行名称即可：
　　
CREATE OR REPLACEVIEW bank_date_lst AS
　　
Selectto_char(tran_date,’yyyy.mm’),
　　
SUM( DECODE (bank_code,’001’, tran_val,0 )) 城西区，
　　
SUM( DECODE (bank_code,’002’, tran_val,0 )) 城南区，
　　
SUM( DECODE (bank_code,’003’, tran_val,0 )) 城东区
　　
FROM pay_lst
　　
GROUP BYto_char(tran_date,’yyyy.mm’);
　　
建立视图后，可直接对该视图进行查询就可按照列显示出结果。
　　

　　
NVL
　　
   NVL函数完成了一个简单但有用的功能。任何时候给它一个空值，它都返回一个你所选择的值。这种能够自动替换空值的能力有助于提供看上去更为完善的输出。NVL函数的语法如下：
　　
NVL（input_source，result_if_input_value_is_null）   其中input_source一般是一个列名。result_if_input_value_is_null 可以是任何值：直接值（即硬编码）、对其他列的引用或者表达式
　　

　　
注意：NVL函数实际上并不是更新表中的值。原数据仍保持不变。
　　

　　
NVL 有一个怪癖，它要求input_source和result_if_input_value_is_null的数据类型是相同的；如果希望此函数在发现空 值时显示流行的“N/A”就会出现问题。因为“N/A”是文本，如果input_value是一个文本列，就没有问题了。但是，如果在一个日期或数值列中 查找空值，将需要对input_value列应用TO_CHAR函数，以便使input_value也成为文本。　　
　　

　　
NVL是如果NULL， 则取指定值
　　
例：nvl(yanlei777,0) > 0
　　
NVL(yanlei777, 0) 的意思是 如果 yanlei777 是NULL， 则取 0值
　　
通过查询获得某个字段的合计值，如果这个值为null将给出一个预设的默认值
　　
例如：
　　
selectnvl(sum(t.dwxhl),1)
　　
from tb_jhde t
　　
就表示如果sum(t.dwxhl) = NULL 就返回 1
　　
另一个有关的有用方法
　　
declare i integer
　　
selectnvl(sum(t.dwxhl),1) into i from tb_jhde t where zydm=-1这样就可以把获得的合计值存储到变量
　　
i中，如果查询的值为null就把它的值设置为默认的1
　　
orcale中:
　　
select nvl(rulescore,0)from zwjc_graderule where rulecode='FWTD';
　　
如果记录中不存在rulecode ='FWTD'的数据.则查不出数据.
　　
selectnvl(rulescore,0) into rule_score from zwjc_graderule where rulecode='FWTD';会报查不到数据的错
　　
selectnvl(sum(rulescore),0) from zwjc_graderule where rulecode='FWTD';
　　
如果记录中不存在rulecode ='FWTD'的数据.还是可以得到一行列名为nvl(rulescore,0),值为0的数据.
　　
selectnvl(sum(rulescore),0) into rule_score from zwjc_graderule whererulecode='FWTD'; 不会报错