decode函数_decode函数的妙用

如下这张表，包含id和name两列，其中id是主键，name允许为空，存在两条记录，一条是(id=1,name='a')，另一条是(id=2,name='')，

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

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

可能很容易想到这条SQL，

SQL> select * from emp where id=:id and name=:name;

如果针对(id=1,name='a')的记录，这条SQL是对的，

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

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

SQL> exec :id := 2; :name := '';PL/SQL procedure successfully completed.SQL> select * from emp where id=:id and name=:name;no rows selected

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

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

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

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

改写SQL，我们看到，无论是(id=1,name='a')的记录，还是(id=2,name ='')的记录，都可以通过该语句得到，

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

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

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.

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

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

近期热文：

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

《Oracle ACE，一段不可思议的旅程》

《Oracle 19c之RPM安装》

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

《ACOUG年会感想》

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

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

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

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

《对recursive calls的深刻理解》

《《Oracle Concept》第三章 - 12》

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

《英超梦幻之行》

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

《传控Tiki-Taka战术解惑》