在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的时候最好还是设置默认值,避免这种情况的发生。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-696824/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-696824/