注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

Using the DECODE() Function & CASE  

2008-09-12 11:16:33|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Oracle Database 11g SQL

DECODE(value, search_value, result, default_value) compares value with
search_value. If the values are equal, DECODE() returns result; otherwise, default_
value is returned. DECODE() allows you to perform if-then-else logic in SQL without having
to use PL/SQL. Each of the parameters to DECODE() can be a column, a literal value, a function,
or a subquery.
Chapter 7: Advanced Queries 193
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID DECODE(P
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine
Notice that
■If product_type_id is 1, Book is returned.
■If product_type_id is 2, Video is returned.
■If product_type_id is 3, DVD is returned.
■If product_type_id is 4, CD is returned.
■If product_type_id is any other value, Magazine is returned.



Using the CASE Expression
The CASE expression performs if-then-else logic in SQL and is supported in Oracle Database 9i
and above. The CASE expression works in a similar manner to DECODE(), but you should use
CASE because it is ANSI-compliant and forms part of the SQL/92 standard. In addition, the CASE
expression is easier to read.
There are two types of CASE expressions:
■Simple case expressions, which use expressions to determine the returned value
■Searched case expressions, which use conditions to determine the returned value
You’ll learn about both of these types of CASE expressions next.
Using Simple CASE Expressions
Simple CASE expressions use embedded expressions to determine the value to return. Simple
CASE expressions have the following syntax:
CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END
where
■search_expression is the expression to be evaluated.
■expression1, expression2, ..., expressionN are the expressions to be
evaluated against search_expression.
■result1, result2, ..., resultN are the returned results (one for each possible
expression). If expression1 evaluates to search_expression, result1 is
returned, and similarly for the other expressions.
■default_result is returned when no matching expression is found.
The following example shows a simple CASE expression that returns the product types as
names:
SELECT product_id, product_type_id,
  CASE product_type_id
    WHEN 1 THEN 'Book'
    WHEN 2 THEN 'Video'
    WHEN 3 THEN 'DVD'
    WHEN 4 THEN 'CD'
    ELSE 'Magazine'
  END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine
Using Searched CASE Expressions
Searched CASE expressions use conditions to determine the returned value. Searched CASE
expressions have the following syntax:
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  WHEN conditionN THEN resultN
  ELSE default_result
END

where
■condition1, condition2, ..., conditionN are the expressions to be evaluated.
■result1, result2, ..., resultN are the returned results (one for each possible
condition). If condition1 is true, result1 is returned, and similarly for the other
expressions.
■default_result is returned when there is no condition that returns true.
The following example illustrates the use of a searched CASE expression:
SELECT product_id, product_type_id,
  CASE
    WHEN product_type_id = 1 THEN 'Book'
    WHEN product_type_id = 2 THEN 'Video'
    WHEN product_type_id = 3 THEN 'DVD'
    WHEN product_type_id = 4 THEN 'CD'
    ELSE 'Magazine'
  END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1 Book
         2               1 Book
         3               2 Video
         4               2 Video
         5               2 Video
         6               2 Video
         7               3 DVD
         8               3 DVD
         9               4 CD
        10               4 CD
        11               4 CD
        12                 Magazine
You can use operators in a searched CASE expression, as shown in the following example:
SELECT product_id, price,
  CASE
    WHEN price > 15 THEN 'Expensive'
    ELSE 'Cheap'
  END
FROM products;
PRODUCT_ID      PRICE CASEWHENP
---------- ---------- ---------
         1      19.95 Expensive
         2         30 Expensive
         3      25.99 Expensive
         4      13.95 Cheap
         5      49.99 Expensive
         6      14.95 Cheap
         7      13.49 Cheap
         8      12.99 Cheap
         9      10.99 Cheap
        10      15.99 Expensive
        11      14.99 Cheap
        12      13.49 Cheap

  评论这张
 
阅读(633)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016