decode函数的妙用

- 日理万妓 2023-07-24 08:13 59阅读 0赞

如下这张表，包含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函数，

![format_png][]

如果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篇文章分类和索引][600]》

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

《[Oracle 19c之RPM安装][Oracle 19c_RPM]》

《[应用执行慢的问题排查路径][Link 1]》

《[ACOUG年会感想][ACOUG]》

《[千万级表数据更新的需求][Link 2]》

《[探寻大表删除字段慢的原因][Link 3]》

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

《[新增字段的一点一滴技巧][Link 4]》

《[对recursive calls的深刻理解][recursive calls]》

《[《Oracle Concept》第三章 - 12][Oracle Concept_ - 12]》

《[一次惊心动魄的问题排查][Link 5]》

《[英超梦幻之行][Link 6]》

《[藤子不二雄博物馆之行][Link 7]》

《[传控Tiki-Taka战术解惑][Tiki-Taka]》

[format_png]: /images/20230528/3967dcee88ae4c83850cb15b7edffff5.png
[600]: https://blog.csdn.net/bisal/article/details/104548761
[Oracle ACE]: https://blog.csdn.net/bisal/article/details/94386471
[Oracle 19c_RPM]: https://blog.csdn.net/bisal/article/details/100909708
[Link 1]: https://blog.csdn.net/bisal/article/details/102480420
[ACOUG]: https://blog.csdn.net/bisal/article/details/103675746
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814beaddf6c2fcd242591e4892a3ac21a272f3307b9771a8ca9b76ef96b1a7bd8249c564fa&idx=1&mid=2247487001&scene=21&sn=15394f15aedee7f8f261fa7294df08a5#wechat_redirect
[Link 3]: https://blog.csdn.net/bisal/article/details/86635285
[Oracle bug]: https://blog.csdn.net/bisal/article/details/101729688
[Link 4]: https://blog.csdn.net/bisal/article/details/103626212
[recursive calls]: https://blog.csdn.net/bisal/article/details/100074863
[Oracle Concept_ - 12]: https://blog.csdn.net/bisal/article/details/100788405
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8149e8ddf6c0fea92181efe658b69c6acd52b691cd562e830f0e63a565215a136105272d27&idx=1&mid=2247486491&scene=21&sn=584c4ac132b69a1e319b2a2bedfd49e2#wechat_redirect
[Link 6]: https://blog.csdn.net/bisal/article/details/103044518
[Link 7]: https://blog.csdn.net/bisal/article/details/103379370
[Tiki-Taka]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814e5cddf6c74a763ac9fac98f94e538bca009fdcff3795533026269686983ceae190fe883&idx=1&mid=2247486383&scene=21&sn=d4c54d7033fc9a21d622e5937cddf64e#wechat_redirect