`

Oracle 的函数 NVL 和 DECODE 解析

阅读更多

SQL语句的DECODENVL()函数

SELECT DECODE(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函数

DECODEOracle公司独家提供的功能,它是一个功能很强的函数。它虽然不是SQL 标准,但对于性能非常有用。到目前,其他的数据库供应商还不能提供类似DECODE的功能,甚至有的数据库的供应商批评OracleSQL不标准。实际 上,这种批评有些片面或不够水平。就象有些马车制造商抱怨亨利。福特的“马车”不标准一样。

1 DECODE 中的if-then-else逻辑

在逻辑编程中,经常用到If Then Else 进行逻辑判断。在DECODE的语法中,实际上就是这样的逻辑处理过程。它的语法如下:

DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试,如果value的值为if1Decode 函数的结果是then1;如果value等于if2Decode函数结果是then2;等等。事实上,可以给出多个if/then 配对。如果value结果不等于给出的任何配对时,Decode 结果就返回else

需要注意的是,这里的ifthenelse 都可以是函数或计算表达式。

含义解释:

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/9i Reference》资料中了解到,当用户登录成功后在V$SESSION中就有该用户的相应记录,但用户所进行的命令操作在该视图中只记录命令的代码 0—没有任何操作,2Insert…),而不是具体的命令关键字。因此,我们需要了解当前各个用户的名字及他们所进行的操作时,要用下面命令才能得到 详细的结果:

select sid,serial#,username,

DECODE(command,

0,’None’,

2,’Insert’,

3,’Select’,

6,’Update’,

7,’Delete’,

8,’Drop’,

Other’) cmmand

from v$session where username is not null;

3 DECODE实现表的转置

数据库中的表是由列和行构成的一个二维表。一般列在任何数据库中都是有限的数量,而行的变化较大,如果表很大,行的数量可能大上千万行。同一列的不同行可能有不同的值,而且不是预先定义的。

:住房公积金报表置换实例:

1.各个单位在本地经办行进行开户,开户就是将单位的基本信息和职工信息的进行登记;

2.每月各个单位的会计到经办行交缴本单位的所有职工的住房公积金,系统记录有每个职工的交缴明细并在每条记录上记录有经办行的代码;

3.每月、季、半年及年终都要求将经办行 变为“列”给出个月的明细报表:

经办行:城西区 城东区

月份:

2001.01 xxxx1.xx xxxxx2.xx

2001.02 xxxx3.xx xxxxx4.xx

原来的数据顺序是:

城西区2001.01 xxxxx1.xx

城东区2001.01 xxxxx2.xx

城西区2001.02 xxxxx3.xx

城东区2001.02 xxxxx4.xx

住房公积金系统记录职工的每月交缴名细的pay_lst表结构是:

bank_code varchar2(6)NOT NULL, -- 经办行代码

acc_no varchar2(15) not null, -- 单位代码(单位帐号)

emp_acc_no varchar2(20) not null, -- 职工帐号

tran_date date not null, -- 交缴日期

tran_val Number(7,2) not null, -- 交缴额

sys_date date default sysdate, --系统日期

oper_id varchar2(10) --操作员代码

这样的表结构,一般按照将经办行作为行(row)进行统计是很容易的,但是如果希望将经办行变为列(column)这样的格式来输出就有困难。如果用DECODE函数来处理则变得很简单:

我们创建一个视图来对目前的pay_lst表进行查询。将经办行代码变为一些具体的经办行名称即可:

CREATE OR REPLACE VIEW bank_date_lst AS

Select to_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 BY to_char(tran_date,’yyyy.mm’);

建立视图后,可直接对该视图进行查询就可按照列显示出结果。

NVL

NVL函数完成了一个简单但有用的功能。任何时候给它一个空值,它都返回一个你所选择的值。这种能够自动替换空值的能力有助于提供看上去更为完善的输出。NVL函数的语法如下:

NVLinput_sourceresult_if_input_value_is_null 其中input_source一般是一个列名。result_if_input_value_is_null 可以是任何值:直接值(即硬编码)、对其他列的引用或者表达式

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

NVL 有一个怪癖,它要求input_sourceresult_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将给出一个预设的默认值

例如:

select nvl(sum(t.dwxhl),1)

from tb_jhde t

就表示如果sum(t.dwxhl) = NULL 就返回 1

另一个有关的有用方法

declare i integer

select nvl(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'的数据.则查不出数据.

select nvl(rulescore,0) into rule_score from zwjc_graderule where rulecode='FWTD';会报查不到数据的错

select nvl(sum(rulescore),0) from zwjc_graderule where rulecode='FWTD';

如果记录中不存在rulecode ='FWTD'的数据.还是可以得到一行列名为nvl(rulescore,0),值为0的数据.

select nvl(sum(rulescore),0) into rule_score from zwjc_graderule where rulecode='FWTD'; 不会报错

分享到:
评论

相关推荐

    oracle中nvl、decode、trim、rtrim的常见用法

    通过以上对NVL、DECODE、TRIM、RTRIM的介绍和示例,我们可以看到这些函数在Oracle数据库中的强大功能和实用性。它们不仅可以帮助我们处理NULL值,还可以灵活地进行条件判断以及字符串的格式化操作,从而大大提高了...

    oracle 函数大全 参考函数 手册 速查 chm格式

    Oracle函数是数据库操作中的核心组成部分,它们允许开发人员和DBA执行各种计算、转换、查询和数据处理任务。手册中可能涵盖了以下几类函数: 1. 数学函数:如ABS(取绝对值)、MOD(取模)、ROUND(四舍五入)等,...

    SQL语句的DECODE和NVL

    SQL 语句的 DECODE 和 NVL 函数 在 SQL 语句中,DECODE 和 NVL 函数是两个非常重要和常用的函数,它们可以帮助我们实现复杂的逻辑判断和数据处理。本文将详细介绍 DECODE 和 NVL 函数的使用方法和实践应用。 一、...

    oracle函数大全(分类成9个word文档)

    2. "oracle函数介绍(2) 非著名函数之单值函数.doc":这部分可能会介绍一些不那么常用但同样重要的单值函数,如DECODE()(根据条件返回不同值)、NVL()(处理NULL值)和TO_CHAR()(将数据转换为字符格式)。...

    oracle函数大全.doc

    只有少数几个函数可以处理 NULL 值,例如 CONCAT、DECODE、DUMP、NVL、REPLACE。 NVL 函数 NVL 函数是处理 NULL 值最重要的函数,它可以直接处理 NULL 值。NVL 函数有两个参数:NVL(x1,x2),x1 和 x2 都是表达式,...

    ORACLE函数大全与整理

    "函数整理.doc"和"ORACLE函数大全.doc"这两个文档应该包含了Oracle函数的详细说明、示例和应用场景,是学习和查阅Oracle函数的宝贵资料。通过深入学习这些函数,开发者可以更高效地编写SQL查询,优化数据库操作,...

    ORACLE 列转行 DECODE函数用法

    在Oracle数据库中,DECODE函数是一个非常实用的工具,它允许你在SQL查询中进行条件判断,将特定的值转换为其他值。DECODE函数在处理列转行的问题时,尤其适用于将多列数据合并到一行中,使得数据展示更加简洁明了。...

    Oracle中Decode()函数使用技巧

    Oracle中的Decode()函数...综上所述,Oracle的Decode()函数是一个强大而灵活的工具,能有效地处理简单的条件判断,提高代码可读性和效率。在理解其基本用法的同时,也需要根据具体需求和性能考虑来选择合适的应用场景。

    ORACLE函数大全CHM

    6. **数学函数**:包括统计和概率函数,如`RANK()`、`DENSE_RANK()`和`ROW_NUMBER()`用于排序,`DECODE()`条件判断,`NVL()`和`NVL2()`处理空值。 7. **位操作函数**:处理二进制数据,如`BITAND()`进行位与运算,`...

    oracle函数大全.rar

    另外,`IFNULL()`和`NVL()`处理空值(NULL)情况,`DECODE()`则允许基于多个条件进行值的查找和替换。 最后,除了以上这些,Oracle还提供了一些**其他特殊函数**,比如`ROWNUM`生成行号,`GREATEST()`和`LEAST()`...

    oracle函数大全(分类显示).zip

    "Oracle函数大全(分类显示)"这个资源显然是为了帮助开发者更好地理解和使用Oracle中的各种函数。下面,我们将详细介绍Oracle数据库中的一些主要函数类别及其应用场景。 1. **数值函数**: - `ROUND`:对数字进行四...

    oracle函数大全中文

    通过"Oracle函数大全(分类显示).chm"这个文档,你可以全面了解并掌握这些函数的使用方法和应用场景,从而更好地在Oracle数据库中进行数据操作和查询。这个资源对于数据库管理员、开发人员和数据分析者来说都是非常...

    三思笔记之oracle函数

    通过阅读“三思笔记”中的相关内容,DBA们可以系统地学习Oracle函数的用法,从而更好地应对日常的数据库挑战。无论是处理数据转换、分析大量数据还是解决特定问题,这些函数都能提供强大的工具支持。因此,深入理解...

    Oracle10G函数大全(chm格式的,比较全面)

    Oracle 10G是Oracle数据库的一个重要版本,它包含了丰富的函数和特性,为数据库管理和开发提供了强大的支持。本文将深入探讨Oracle 10G中的一些关键函数,旨在帮助你更好地理解和利用这些工具。 1. **数学函数**: ...

    oracle函数大全-数字处理函数

    Oracle数据库系统是世界上最广泛使用的数据库管理系统之一,它提供了丰富的内置函数来处理各种数据类型,包括数字和字符串。本文将深入探讨Oracle中的数字处理函数,帮助你更好地理解和利用这些功能强大的工具。 1....

    Oracle函数大全

    在Oracle函数大全中,我们涵盖了各种类型的功能,包括数学、字符串处理、日期时间操作、逻辑判断以及转换函数等,这些都是数据库管理员和开发人员日常工作中不可或缺的工具。 1. **数学函数**: - `ABS()`: 返回一...

    oracle函数大全

    Oracle函数大全涵盖了大量的内置函数,这些函数在数据库管理和查询中起着至关重要的作用。Oracle的函数分为两类:单行函数和组函数。本篇重点讨论单行函数及其使用。 单行函数是应用于单行数据的函数,它们可以用于...

    Oracle 函数大全

    Oracle数据库系统提供了大量的内置函数,这些函数可用于数据处理、数据查询、条件判断等多种场景。根据提供的文件内容,可以总结出Oracle中部分...掌握这些函数的使用方法是Oracle数据库开发者和管理员必备的技能之一。

    oracle 函数文档

    在Oracle函数文档中,我们通常会找到关于如何利用这些函数进行数据操作、查询和分析的详细信息。这篇文档可能涵盖了从基本的算术运算到复杂的字符串处理、日期时间操作、逻辑判断以及聚合函数等多个方面。 1. **...

    oracle最常用的函数或方法总结

    在日常的数据操作和分析中,掌握一些常用的Oracle函数和方法是至关重要的。以下是对"Oracle最常用的函数或方法总结"的详细阐述: 1. **字符串处理函数** - `CONCAT()`:用于连接两个或多个字符串。 - `SUBSTR()`...

Global site tag (gtag.js) - Google Analytics