ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Decode函数的默认值陷阱

Decode函数的默认值陷阱

原创 Linux操作系统 作者:realkid4 时间:2011-05-31 21:16:26 0 删除 编辑

 

Oracle的诸多SQL函数中,笔者认为最容易被忽视的强大功能函数就是decode。在Decode函数的帮助下,很多要使用case when字句或者要使用PL/SQL游标处理的需求,可以方便的在一句SQL中加以实现。本篇主要从介绍Decode语法入手,提示一个使用Decode的陷阱。

 

 

1Decode语句实例和语法

 

首先,我们先从一个例子看起,顺便准备环境。

 

 

SQL> select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

 

SQL> create table t (id number, ori_value varchar2(10), res_value varchar2(10));

Table created

 

//数据准备结果

SQL> select * from t;

 

        ID ORI_VALUE  RES_VALUE

---------- ---------- ----------

         1 T         

         2 T         

         3 M         

         4 E         

         5 L         

         6 E         

 

6 rows selected

 

 

SQL需求:对res_value字段赋值,如果ori_value为‘T’,则取值为‘TEST’;如果为‘E’,则取值为‘EXIT’。其他情况赋值为‘VALID’。借助decode函数,此类需求很容易实现。

 

 

SQL> update t set res_value=decode(ori_value, --条件表达式

  2                'T','TEST', --condition 1ori_value='T'

  3                'E','EXIT', --condition 2ori_value='E'

  4                'VALID'     --others

  5                );

 

6 rows updated

 

SQL> commit;

Commit complete

 

SQL> select * from t;

        ID ORI_VALUE  RES_VALUE

---------- ---------- ----------

         1 T          TEST

         2 T          TEST

         3 M          VALID

         4 E          EXIT

         5 L          VALID

         6 E          EXIT

 

6 rows selected

 

 

看似复杂的需求,通过简单的decode函数匹配处理,就在一个SQL中解决了。Decode函数是Oracle的一个集合处理函数。下面是Decode的语法结构图示:

 

 

 

Decode是一个不定参数个数的函数类型。通过search/result配对来进行条件匹配,匹配的数量不定。最后存在一个default值,如果都没有匹配上,就返回default值。注意,这个default值是可选的。

 

 

 

Decode函数在Oracle内部,也是普遍使用的一种函数类型。一个场景就是实现user/all/dba字典视图层与$数据字典表之间的映射。因为在XXX$数据字典表中,很多数字、字符类型,都需要翻译为易于理解的编码方式。如下面片段。

 

 

CREATE OR REPLACE VIEW DBA_OBJECTS

(owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary, namespace, edition_name)

AS

select u.name, o.name, o.subname, o.obj#, o.dataobj#,

       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',

                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',

                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

                      11, 'PACKAGE BODY', 12, 'TRIGGER',

 

 

该片段就是实现了从”_CURRENT_EDITION_OBJ”数据列向dba_objects数据表之间的映射。

 

 

还要一个细节需要注意,如果字段匹配中使用的是null值,可以在search中直接使用null值。如下例所示:

 

SQL> select id, ori_value,

  2         decode(ori_value, --条件表达式

  3                null,'TEST', --condition 1ori_value is null

  4                ori_value     --others

  5                ) as results

  6  from t

  7  where id>5;

 

        ID ORI_VALUE  RESULTS

---------- ---------- ----------

         6 E          E

         7            TEST

 

 

 

 

3、默认值default陷阱

 

Oracle官方文档中,包括了对Decode函数的解析,全文如下:

 

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

 

从上面的解释看,Decode是一个顺序匹配的过程。不断将表达式与search进行匹配,如果匹配上,返回结果。如果没有匹配上,就继续匹配。如果所有都没有成功匹配,就返回default值。注意:如果用户没有确定default值,且出现无法匹配的情况,函数返回null值。

 

这就存在一个风险,如果匹配出现了意想之外的取值,而书写者没有设置默认值,这样赋值就会出现空值的情况。

 

SQL> update t set res_value=decode(ori_value, --条件表达式

  2                'T','TEST', --condition 1ori_value='T'

  3                'E','EXIT' --condition 2ori_value='E'

  4                  --No Others, No Default

  5                );

 

7 rows updated

 

SQL> select * from t;

 

        ID ORI_VALUE  RES_VALUE

---------- ---------- ----------

         1 T          TEST

         2 T          TEST

         3 M         

         4 E          EXIT

         5 L         

         6 E          EXIT

         7           

 

7 rows selected

 

 

 

所以,为了从安全性角度考虑,使用decode的时候最好还是设置默认值,避免这种情况的发生。

 

 

 

未命名.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-696824/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Decode函数语法
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7677150