-fonksiyonlar sadece tek değer döndürürler.

create [or replace] function fonksiyon_ismi return veritipi is

create or replace function func_no(number,number) return number is

—————————FONKSİYON ÖRNEK-1————————–

CREATE OR REPLACE FUNCTION deneme RETURN VARCHAR2 IS

BEGIN
RETURN ‘deneme fonksiyon’;
END deneme;
/

select deneme from dual;

—————————-FONKSİYON ÖRNEK-2————————

CREATE OR REPLACE FUNCTION date_fark (max_date DATE, min_date DATE)
RETURN PLS_INTEGER IS
BEGIN
RETURN max_date – min_date;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END date_fark;
/

SELECT date_fark(’30/11/2007 23:59:59′,’01/11/2007 00:00:00′) FROM dual;

—————————-FONKSİYON ÖRNEK-3———————–

CREATE OR REPLACE FUNCTION fonk_klinik_ad (vklinik_id number)
return varchar2 IS
vklinik_ad varchar2(20);

cursor cur_klinik_ad is
select klinik_adi from klinik where klinik_id = vklinik_id;

BEGIN
open cur_klinik_ad;
fetch cur_klinik_ad into vklinik_ad;
close cur_klinik_ad;
return vklinik_ad;
END;
/

select * from klinik where klinik_adi=fonk_klinik_ad(1);

——————————FONKSİYON ÖRNEK-4———————

CREATE OR REPLACE FUNCTION fonk_klinik_ad2 (vklinik_id number)
return varchar2 IS
vklinik_ad varchar2(20);

begin

select klinik_adi into vklinik_ad from klinik where klinik_id = vklinik_id;

return(vklinik_ad);

exception when others then
return(”);
end;
/

select * from klinik where klinik_adi=fonk_klinik_ad2(1);

——————————FONKSİYON ÖRNEK-5————————

CREATE OR REPLACE FUNCTION fonk_hasta_ad_bul (no in number)
return varchar2 is
hasta_ad varchar2(20);
begin

if no is not null then
BEGIN
SELECT hasta_adi
INTO hasta_ad
FROM hastalar h
WHERE h.hasta_no =no;
return hasta_ad;
EXCEPTION
WHEN OTHERS THEN return null;
END;
else
return null;
end if;
end;
/
select * from hastalar;
insert into hastalar(hasta_id,hasta_no,hasta_adi,klinik_id) values(75,”,’orhan’,1);
select * from hastalar;
select * from hastalar where hasta_adi=fonk_hasta_ad_bul(‘100′);
select * from hastalar where hasta_adi=fonk_hasta_ad_bul(”);

—————————–FONKSİYON ÖRNEK-6————————-

CREATE OR REPLACE FUNCTION eski_lira (tutar number)
RETURN number IS

sonuc number(25,5);
bolum number;
carpan number;
tamsayi integer;
carpim number;

BEGIN

if nvl(tutar,0) = 0 then return(0);
else
carpan := 1000000;
carpim := tutar *carpan;
sonuc := carpim;
return(sonuc);

end if;
END;
/

not: NVL(KOLON,DEGER) : Kolonun aldığı değer null ise değer alanında belirtilen değeri verir.

select hasta_adi,eski_lira(maas) as lira from hastalar; //maaş alanı yoksa ekle.

—————————FONKSİYON ÖRNEK-7———————

CREATE OR REPLACE FUNCTION yas_hesapla (dtar date)
return number is
begin

RETURN(TRUNC((sysdate – dtar)/365.25));

end yas_hesapla;
/

select yas_hesapla(’20/02/1982′) from dual;

————————-FONKSİYON ÖRNEK-8——————-

CREATE OR REPLACE FUNCTION turkce_ingilizce
(kelime varchar2) Return varchar2 IS
uzun number;
deger varchar2(32760);
yeni varchar2(32760);

begin

if kelime is null then
return(null);
end if;

uzun:= length(kelime);
deger := kelime;

for k in 1..uzun
loop

if substr(deger,k,1) =’Ç’ then
yeni := yeni || ‘C’;
elsif substr(deger,k,1) =’Ğ’ then
yeni := yeni || ‘G’;
elsif substr(deger,k,1) =’İ’ then
yeni := yeni || ‘I’;
elsif substr(deger,k,1) =’Ö’ then
yeni := yeni || ‘O’;
elsif substr(deger,k,1) =’Ü’ then
yeni := yeni || ‘U’;
elsif substr(deger,k,1) =’Ş’ then
yeni := yeni || ‘S’;
elsif substr(deger,k,1) =’ç’ then
yeni := yeni || ‘c’;
elsif substr(deger,k,1) =’ı’ then
yeni := yeni || ‘i’;
elsif substr(deger,k,1) =’ö’ then
yeni := yeni || ‘o’;
elsif substr(deger,k,1) =’ü’ then
yeni := yeni || ‘u’;
elsif substr(deger,k,1) =’ğ’ then
yeni := yeni || ‘g’;
elsif substr(deger,k,1) =’ş’ then
yeni := yeni || ‘s’;
else
yeni := yeni || substr(deger,k,1);
end if;
end loop;

return(yeni);
end;
/

select turkce_ingilizce(‘çağatay’) from dual;
select turkce_ingilizce(hasta_adi) from hastalar;

———————-FONKSİYON ÖRNEK-9——————
(TÜRKÇE KARAKTERE ÇEVİRME-2)

CREATE OR REPLACE FUNCTION STRING_DUZENLE(str_ VARCHAR2) RETURN VARCHAR2
IS
ret_ VARCHAR2(4000);
BEGIN
ret_ := replace(replace(str_,’ç’,’c’),’Ç’,’C’);
ret_ := replace(replace(ret_,’ğ’,’g’),’Ğ’,’G’);
ret_ := replace(replace(ret_,’ı’,’i’),’İ’,’I’);
ret_ := replace(replace(ret_,’ö’,’o’),’Ö’,’O’);
ret_ := replace(replace(ret_,’ş’,’s’),’Ş’,’S’);
ret_ := replace(replace(ret_,’ü’,’u’),’Ü’,’U’);
ret_ := replace(replace(replace(replace(replace(replace(ret_,’,’,’.’),’-‘,’ ‘),’:’,’.’),’/’,’ ‘),’\’,’ ‘),’;’,’.’);
RETURN ret_;
END;
/

select STRING_DUZENLE(‘ÇAĞATAY’) from dual ;

————————-FONKSİYON ÖRNEK-10——————-

CREATE OR REPLACE FUNCTION password_function (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN AS
BEGIN
IF LENGTH(password) < 8 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END password_function;
/

————————ÖRNEK FONKSİYON -11—————-

CREATE OR REPLACE FUNCTION fonk_min_tarih (hastaid NUMBER)
RETURN DATE IS
tarih DATE;
BEGIN
BEGIN
SELECT MIN (son_update)
INTO tarih
FROM hastalar
WHERE hasta_id = hastaid;
EXCEPTION
WHEN OTHERS THEN
tarih := ”;
END;

RETURN tarih;
END;
/

select fonk_min_tarih(10) from dual;