Decode函数的默认值陷阱

JeeStudy

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

1、Decode语句实例和语法

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

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 1：ori_value='T'

  3                'E','EXIT', --condition 2：ori_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 1：ori_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 1：ori_value='T'

  3                'E','EXIT' --condition 2：ori_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的时候最好还是设置默认值，避免这种情况的发生。