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 number
SQL> 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  a


SQL> 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战术解惑》