Decode函数的默认值陷阱
在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_VALUERES_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_VALUERES_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的语法结构图示： http://space.itpub.net/attachments/2011/05/17203031_201105312118221.jpg 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)ASselect 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 null4 ori_value --others：5 ) as results6from t7where id>5; ID ORI_VALUERESULTS---------- ---------- ---------- 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_VALUERES_VALUE---------- ---------- ---------- 1 T TEST 2 T TEST 3 M 4 E EXIT 5 L 6 E EXIT 7 7 rows selected 所以，为了从安全性角度考虑，使用decode的时候最好还是设置默认值，避免这种情况的发生。
页:
[1]