oracle的case、nvl、decode、&符号

1)oracle的if else then

select  CASE when filelength is null then '0' else filelength end filelength from bs13.t_mediasource where filelength is null 

2)oracle的if else then

select nvl(wg38,0) from   bs13.jngl

3)oracle的decode

select wg35, decode(nvl(wg38,'0'),'0','优秀','orther') from bs13.jngl


select id, decode(sign(score-85),1,'优秀',0,'优秀',-1, 
decode(sign(score-70),1,'良好',0,'良好',-1, 
decode(sign(score-60),1,'及格',0,'及格',-1,'不及格'))) 
from student




select sid,serial#,username,
　　DECODE(command,
　　0,'None',
　　2,'Insert',
　　3,'Select',
　　6,'Update',
　　7,'Delete',
　　8,'Drop',
　　'Other') cmmand
　　from v$session where username is not null;

3)oracle的case when where  用法

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
                   T1.SOME_TYPE LIKE 'NOTHING%'
                THEN 1
              WHEN T2.COMPARE_TYPE != 'A' AND
                   T1.SOME_TYPE NOT LIKE 'NOTHING%'
                THEN 1
              ELSE 0
           END) = 1

set define off;
Select 'Tom&Jerry' from dual; 
Select 'Tom'||'&'||'Jerry' from dual;