【SQL 學習】函式之DECODE（）

SQL> -- decode 函式 (value，search_value,result ,default_value) 將value的值與search_value 的值比較如果相同，就返回resualt 。否則返回default_value    
SQL> select decode (1,1,2,3) from dual;--- 1 與1 相同 返回2
DECODE(1,1,2,3)
---------------
              2

SQL> select decode (1,3,2,3) from dual; 1 與 3 比較，不同，返回3
DECODE(1,3,2,3)
---------------
              3

已用時間:  00: 00: 00.50 

--------其他的例子
SQL> select prd_id ,available,
  2  decode(available,'Y','Product is available', 'Product is not available')
  3  from more_products;
    PRD_ID A DECODE(AVAILABLE,'Y','PR
---------- - ------------------------
         1 Y Product is available
         2 Y Product is available
         3 N Product is not available
         4 N Product is not available
         5 Y Product is available

已用時間:  00: 00: 00.01
SQL> select product_id,product_type_id,
  2  decode (product_type_id,
  3  1,'BOOK',
  4  2,'VIDEO',
  5  3,'DVD',
  6  4,'CD',
  7  'Magazine')
  8  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        
已選擇12行。
已用時間:  00: 00: 00.03