Selasa, 09 Februari 2010

Fungsi NVL Oracle

Fungsi NVL digunakan untuk menggantikan null value dengan nilai lain nya.

Sintak:
NVL( value_in, replace_with )

NVL bekerja seperti kode PL/SQL berikut :

if (value_in is NULL) then
return replace_with;
else
return value_in;
end if;

Sample code:

select nvl(salary, 0)
from employees;

select nvl(ref_code,'Unknown')
from users;

encode sintak di PL/SQL Oracle

SELECT DECODE (value,if this value,return this value,
if this value,return this value,
....
otherwise this value)
FROM dual;

contoh:
SELECT program_id,
DECODE(customer_id,
'AAL', 'American Airlines',
'ILC', 'Intl. Leasing Corp.',
'NWO', 'Northwest Orient',
'SAL', 'Southwest Airlines',
'SWA', 'Sweptwing Airlines',
'USAF', 'United States Air Force',
'Not Known') AIRLINE,
delivered_date
FROM airplanes
WHERE ROWNUM <>