oracle decode函數(shù)的用法

oracle decode函數(shù)，不管查詢的結(jié)果是否滿足‘PRIMARY’，參數(shù)中的select語句還是會(huì)執(zhí)行的,通過查看執(zhí)行計(jì)劃就知道

set autotrace traceonly

select decode(database_role ,'PRIMARY', 
(select
trunc((size_MB-free_MB)*100/maxsize_MB) "retvalue"
from (
select
tablespace_name,case when sum(decode(autoextensible,'YES',1,0))>0 then 'YES'
else 'NO' end ext,
trunc(sum(bytes)/1024/1024) SIZE_MB,
trunc(sum(case when autoextensible='YES' then maxbytes else bytes
end)/1024/1024) MAXSIZE_MB
from dba_data_files
group by tablespace_name
) t1,
(
select tablespace_name,trunc(sum(bytes)/1024/1024) FREE_MB
from dba_free_space
group by tablespace_name
) t2,
(select tablespace_name
from dba_tablespaces
where contents='PERMANENT') t3
where t1.tablespace_name=t2.tablespace_name and t3.tablespace_name=t1.tablespace_name and t3.tablespace_name='SYSTEM'),0) as retvalue from v$database;

decode函數(shù)等價(jià)于cash when語句，只不過當(dāng)條件比較多時(shí)，decode函數(shù)書寫起來比較簡(jiǎn)潔一點(diǎn)而已

select case 
           when database_role  = 'PRIMARY' then  (select tname from tab where rownum=1)
           else '0' 
       end as retvalue 
from  v$database;