decode函数_decode函数的妙用

如下这张表&＃xff0c;包含id和name两列&＃xff0c;其中id是主键&＃xff0c;name允许为空&＃xff0c;存在两条记录&＃xff0c;一条是(id&＃61;1,name&＃61;&＃39;a&＃39;)&＃xff0c;另一条是(id&＃61;2,name&＃61;&＃39;&＃39;)&＃xff0c;

SQL> create table emp(id number primary key, name varchar2(25));Table created.SQL> select * from emp; ID NAME---- -------   1  a 2

我的问题是&＃xff0c;给定具体的id和name值作为检索条件的前提下&＃xff0c;如何写出一条通用的SQL同时满足name为空和不为空的场景&＃xff1f;

可能很容易想到这条SQL&＃xff0c;

SQL> select * from emp where id&＃61;:id and name&＃61;:name;

如果针对(id&＃61;1,name&＃61;&＃39;a&＃39;)的记录&＃xff0c;这条SQL是对的&＃xff0c;

SQL> variable id numberSQL> variable name varchar2(25)SQL> exec :id :&＃61; 1; :name :&＃61; &＃39;a&＃39;;          PL/SQL procedure successfully completed.SQL> select * from emp where id&＃61;:id and name&＃61;:name;                     ID  NAME---- -------   1  a

但是针对(id&＃61;2,name&＃61;&＃39;&＃39;)的记录&＃xff0c;这条SQL是错的&＃xff0c;原因就是在Oracle中null&＃61;null返回的是false&＃xff0c;判断空值&＃xff0c;需要使用is null或者is not null&＃xff0c;

SQL> exec :id :&＃61; 2; :name :&＃61; &＃39;&＃39;;PL/SQL procedure successfully completed.SQL> select * from emp where id&＃61;:id and name&＃61;:name;no rows selected

因此按照理解&＃xff0c;改写SQL&＃xff0c;此时能同时满足这两种场景&＃xff0c;如果:name参数不为空&＃xff0c;则会使用name&＃61;:name条件&＃xff0c;如果:name参数为空&＃xff0c;则会使用name is null and :name is null条件&＃xff0c;限定检索字段name为空&＃xff0c;同时参数:name为空&＃xff0c;

SQL> exec :id :&＃61; 1; :name :&＃61; &＃39;a&＃39;;PL/SQL procedure successfully completed.SQL> select * from emp where id&＃61;:id        and (name&＃61;:name or (name is null and :name is null)); ID NAME---- ------- 1 aSQL> exec :id :&＃61; 2; :name :&＃61; &＃39;&＃39;;PL/SQL procedure successfully completed.SQL> select * from emp      where id&＃61;:id    and (name&＃61;:name or (name is null and :name is null));  ID  NAME---- ------- 2

其实&＃xff0c;Tom大叔和Darl的经典著作《Oracle编程艺术-深入理解数据库体系结构》中提到了一种更为简单的操作&＃xff0c;使用decode函数&＃xff0c;

如果decode函数中expr和search相等&＃xff0c;则Oracle返回result&＃xff0c;如果expr和search不等&＃xff0c;则Oracle返回default&＃xff0c;若未指定default&＃xff0c;则返回空值。

改写SQL&＃xff0c;我们看到&＃xff0c;无论是(id&＃61;1,name&＃61;&＃39;a&＃39;)的记录&＃xff0c;还是(id&＃61;2,name &＃61;&＃39;&＃39;)的记录&＃xff0c;都可以通过该语句得到&＃xff0c;

SQL> exec :id :&＃61; 1; :name :&＃61; &＃39;a&＃39;;PL/SQL procedure successfully completed.SQL> select * from emp  where id&＃61;:id and decode(name, :name, 1)&＃61;1; ID NAME----- ------- 1 a SQL> exec :id :&＃61; 2; :name :&＃61; &＃39;&＃39;;PL/SQL procedure successfully completed.SQL> select * from emp      where id&＃61;:id and decode(name, :name, 1)&＃61;1; ID NAME----- ------- 2

他的精髓就在于&＃xff0c;decode函数中&＃xff0c;Oracle会认为两个空值是等价的&＃xff0c;官方文档的介绍如下&＃xff0c;这就解决了(null&＃61;null)问题&＃xff0c;

In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

但是要注意的是&＃xff0c;为这条SQL选择索引&＃xff0c;只能对id列创建&＃xff0c;不能对decode函数创建&＃xff0c;因为Oracle不能基于未知的用户输入创建索引数据&＃xff0c;

SQL> select * from emp      where id&＃61;:id and decode(name, :name, 1)&＃61;1;

近期热文&＃xff1a;

《公众号600篇文章分类和索引》

《Oracle ACE&＃xff0c;一段不可思议的旅程》

《Oracle 19c之RPM安装》

《应用执行慢的问题排查路径》

《ACOUG年会感想》

《千万级表数据更新的需求》

《探寻大表删除字段慢的原因》

《一次Oracle bug的故障排查过程思考》

《新增字段的一点一滴技巧》

《对recursive calls的深刻理解》

《《Oracle Concept》第三章 - 12》

《一次惊心动魄的问题排查》

《英超梦幻之行》

《藤子不二雄博物馆之行》

《传控Tiki-Taka战术解惑》