Oracle Decode and CASE Sample

最新推荐文章于 2024-08-14 13:17:14 发布

最新推荐文章于 2024-08-14 13:17:14 发布 · 147 阅读

文章标签：

#数据库

Oracle 专栏收录该内容

12 篇文章

订阅专栏

本文通过创建测试表并插入多条日期数据，使用DECODE与CASE WHEN进行条件判断，展示了这两种方法在SQL中的应用及性能差异。实操中，CASE WHEN在多数情况下表现出更优的性能。

create table testabc(
a Number,
b Date
);

insert into testabc(a,b) values(1, TO_DATE('1-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));
insert into testabc(a,b) values(1, TO_DATE('2-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));
insert into testabc(a,b) values(1, TO_DATE('3-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));
insert into testabc(a,b) values(1, TO_DATE('4-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));
insert into testabc(a,b) values(1, TO_DATE('5-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));
insert into testabc(a,b) values(1, TO_DATE('6-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI'));

select a,b,decode(sign(b - TO_DATE('3-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI')), -1, 'his', 1, 'cur', 'his') as type from testabc;
select a,b,case when b <= TO_DATE('3-MAY-2010/00:00', 'DD-MON-YYYY/HH24:MI') then 'his' else 'cur' end type from testabc;

drop table testabc;

In theory, case when will have a better performance.