发布日期:2015-11-05 17:28 来源: 标签: 数据库 oracle教程 oracle函数 case和decode
本章我们主要学习Oracle中case和decode函数在用法上的区别及性能差异?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
在oracle世界,你可以使用:
1)case表达式或者
2)decode函数
来实现逻辑判断。Oracle的DECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE是9i以后提供的语法,这个语法更加的灵活,提供了IF THEN ELSE的功能。   

case表达式
case表达式,可分两种,简单和搜索,简单case后接表达式,如:

对于简单的case需要几点注意:
1)寻找when的优先级:从上到下
2)再多的when,也只有一个出口,即其中有一个满足了expr就马上退出case
3)不能把return_expr和else_expr指定为null,而且,expr、comparison_expr和return_expr的数据类型必须相同。

搜索case:
CASE WHEN condition THEN return_expr
[WHEN condition THEN return_expr]
 ...
ELSE else_expr
 END

例子:
[sql] view plaincopy
SELECT (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN  ' 0 - 3999'  
    WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
       WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
       WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END)  
    AS BUCKET, COUNT(*) AS Count_in_Group  
    FROM customers WHERE cust_city = 'Marshal' GROUP BY  
     (CASE WHEN cust_credit_limit BETWEEN  0 AND 3999 THEN ' 0 - 3999'  
     WHEN cust_credit_limit BETWEEN  4000 AND 7999 THEN ' 4000 - 7999'  
     WHEN cust_credit_limit BETWEEN  8000 AND 11999 THEN  ' 8000 - 11999'  
     WHEN cust_credit_limit BETWEEN  12000 AND 16000 THEN '12000 - 16000' END);  
      
BUCKET        COUNT_IN_GROUP  
    ------------- --------------  
     0 - 3999                  8  
     4000 - 7999               7  
     8000 - 11999              7  
    12000 - 16000              1  

    用decode可以违反第3NF(行不可再分,列不可再分,列不可重复):列重复
[sql] view plaincopy
    hr@ORCL> select * from a;  
      
            ID NAME  
    ---------- ----------  
             1 a  
             2 b  
             3 c  
             1 a  
      
    hr@ORCL> select sum(decode(id,1,1,0)) think,  
      2             sum(decode(id,2,2,0)) water,  
      3             sum(decode(id,3,3,0)) linshuibin  
      4        from a;  
      
         THINK      WATER LINSHUIBIN  
    ---------- ---------- ----------  
             2          2          3  

一个字段,decode函数可以完全改写简单case;
多个字段,需要复杂的case,方可。
语法:
DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返then1,...,如果不等于任何一个if值,则返回else。可以用函数或表达式来替代value,if,then,else从而作出一些更有用的比较。
来看看具体的运用:
1 假设我们想给百度职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15%
则:
 select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary) "revised_salary" from employee
2 表table_subject,有subject_name列。要求按照:语、数、外的顺序进行排序
则:
select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

decode和简单case的性能比较
Oracle的DECODE函数功能很强,灵活运用的话可以避免多次扫描,从而提高查询的性能。而CASE是9i以后提供的语法,这个语法更加的灵活,提供了IF THEN ELSE的功能。
对于很多情况,DECODE和CASE都能解决问题,个人更倾向于使用DECODE,一方面是从8i保留下来的习惯,另一方面是DECODE的语法更加的简洁,代码量要小一些。
不过今天在看Oracle9i的数据仓库手册时发现,Oracle在文档中提到CASE语句的效率会更高一些,尤其是CASE表达式 WHEN 常量 THEN的语法,效率要比CASE WHEN表达式 THEN的语法更高一些。对于后面这种说法倒是没有太多的疑问,对于CASE比DECODE效率高这种说法倒是第一次看到,印象中DECODE效率很高,应该不会比CASE的效率差。
到底效率如何,还是要具体的实例来说:
SQL> CREATE TABLE T AS
2 SELECT A.*
3 FROM DBA_OBJECTS A, DBA_MVIEWS;
Table created.

SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
6075760
下面检查DECODE和两种CASE语句的效率:
SQL> SET ARRAY 1000
SQL> SET TIMING ON
SQL> SET AUTOT TRACE 
SQL> SELECT DECODE(OWNER, 'SYSTEM', 'SYSTEM', 'SYS', 'SYSTEM', 'USER') 
2 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.24
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288564 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

SQL> SELECT CASE OWNER WHEN 'SYSTEM' THEN 'SYSTEM'
2 WHEN 'SYS' THEN 'SYSTEM' 
3 ELSE 'USER' END 
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.22
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288578 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

SQL> SELECT CASE WHEN OWNER = 'SYSTEM' THEN 'SYSTEM'
2 WHEN OWNER = 'SYS' THEN 'SYSTEM' 
3 ELSE 'USER' END 
4 FROM T;
6075760 rows selected.
Elapsed: 00:00:07.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4245K| 68M| 13828 (1)| 00:03:14 |
| 1 | TABLE ACCESS FULL| T | 4245K| 68M| 13828 (1)| 00:03:14 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47551 consistent gets
0 physical reads
0 redo size
46288585 bytes sent via SQL*Net to client
67317 bytes received via SQL*Net from client
6077 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6075760 rows processed

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。