首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >decode函数的妙用

decode函数的妙用

作者头像
bisal
发布2020-04-10 11:42:03
1K0
发布2020-04-10 11:42:03
举报

如下这张表,包含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;
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2020-04-09 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档