ORACLE CASE YAPISI

Case ifadesi if-then-else ifadesinin başka bir alternatifidir.Kullanım şekli :

CASE [ ifade]
WHEN durum1 THEN sonuc1
WHEN durum1 THEN sonuc2
…
WHEN drumn THEN sonucn
ELSE baskasonuc
END

Ifade ve else alanı zorunlu değildir, isteğe bağlıdır.Oracle burda 255 tane koşula kadar izin verir.Hangi WHEN….THEN koşulu geçerli ise ona göre sonuc return edilir.Hiçbir koşul gerçekleşmezse NULL döndürülür.



select table_name,
CASE owner
WHEN ‘SYS’ THEN ‘The owner is SYS’
WHEN ‘SYSTEM’ THEN ‘The owner is SYSTEM’
ELSE ‘The owner is another value’
END
from all_tables;

select table_name,
CASE
WHEN owner=’SYS’ THEN ‘The owner is SYS’
WHEN owner=’SYSTEM’ THEN ‘The owner is SYSTEM’
ELSE ‘The owner is another value’
END
from all_tables;

Aşağıdaki, yukardaki 2 örneğin if-then-else yapısıdır.

IF owner = ‘SYS’ THEN
result := ‘The owner is SYS’;

ELSIF owner = ‘SYSTEM’ THEN
result := ‘The owner is SYSTEM”;

ELSE
result := ‘The owner is another value’;

END IF;

Örnekler:

select table_name,
CASE owner
WHEN ‘SYS’ THEN ‘The owner is SYS’
WHEN ‘SYSTEM’ THEN ‘The owner is SYSTEM’
END
from all_tables;



select
CASE
WHEN a < b THEN ‘hello’
WHEN d < e THEN ‘goodbye’
END
from suppliers;

İki koşul için case kullanımı örneği:

select supplier_id,
CASE
WHEN supplier_name = ‘IBM’ and supplier_type = ‘Hardware’ THEN ‘North office’
WHEN supplier_name = ‘IBM’ and supplier_type = ‘Software’ THEN ‘South office’
END
from suppliers;

Share: