FUNCTION dalam ORACLE

Dibawah ini adalah beberapa contoh function.

cara memanggil FUNCTION *
select jumlah(10,20) from dual;

1. FUNCTION untuk penjumlahan

CREATE OR REPLACE FUNCTION jumlah
(x in number, y in number)
RETURN number
IS
Begin
return x+y;
end;

declare
a number(10);
b number(10);
hasil number(10);
begin
a:=&nilai_a;
b:=&nilai_b;
hasil:=jumlah(a,b);
dbms_output.put_line('Output diluar procedure : ' ||hasil);
end;

2. FUNCITION untuk menentukan gajinya besar ataukecil

CREATE OR REPLACE FUNCTION gaji
(g in number)
RETURN varchar
IS

Begin
if g > 5000 then
RETURN ' Gaji Besar';
else
RETURN ' Gaji Kecil';

end if;
end;

Cara menjalankan FUNCTIONNYA
SELECT gaji(10000) from dual;
SELECT first_name, salary, gaji(salary) "keterangan" from employees;


3. FUNCTION untuk menghitung jumlah rekan kerja

create or replace function NO2
(id employees.department_id%type)

idkar employees.department_id%type;
nama employees.first_name%type;
jml number(10);
IS
select first_name
into nama
from employees
where employee_id = (select count(department_id)
into jml
from employees
where department_id=idkar);
dbms_output.put_line(' Nama Karyawan : '||nama);
dbms_output.put_line(' Rekan Kerja : '||jml);

end;
Cara Memanggilnya
select first_name, NO2(employee_id) from employees;

3. contoh function

CREATE OR REPLACE FUNCTION JMLSA
(id in countries.country_id%type)
RETURN number
IS
sa locations.street_address%type;
BEGIN
select count(street_address)
into sa
from locations JOIN countries ON (locations.country_id=countries.country_id)
where locations.country_id=id;
return(sa);
END;
/

CREATE OR REPLACE FUNCTION UPDT
(id IN employees.employee_id%type)
RETURN number
IS
gaji employees.salary%type;
BEGIN
update employees
set salary = gaji
where id = id;
return(gaji);
END;
/