04 - Functions




PL/SQL Functions


What is a Function in PL/SQL?
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

General Syntax to create a function is

CREATE [OR REPLACE] FUNCTION function_name [parameters] 
RETURN return_datatype;  
IS  
Declaration_section  
BEGIN  
Execution_section 
Return return_variable;  
EXCEPTION  
exception section  
Return return_variable;  
END; 

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func
2>    RETURN VARCHAR(20);
3> IS 
5>    emp_name VARCHAR(20); 
6> BEGIN 
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> / 

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2. 
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?
A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name :=  employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.




Shembull 1

a- Ndertoni nje funksion qe merr si argument kodin e nje departamenti dhe kthen numrin e punonjesve qe ka ai departament

create or replace function punonjes_ne_dept
(
dep_id number
)
return number
is
punonjes number;
begin

select count(*) into punonjes
from employees 
where department_id=dep_id;

return punonjes;
end;

b- Duke perdorur funksioni e krijuar afishoni per cdo departament vetem per departamentet qe kane me shume se zero punonjes.

 Kodi Emri_Departamentit Numri_punonjesve
   


select 
department_id as Kodi,
department_name as Emri_Departamentit,
punonjes_ne_dept_endri(department_id) as Numri_Punonjesve
from departments
where punonjes_ne_dept_endri(department_id)>0;



c- Duke perdorur funksioni e krijuar afishoni perbri cdo departamenti departamentet qe kane me shume punonjes se ai, si me poshte

Hint: Funksionet ne kushtin e JOIN

 Kodi Emri Departamentit Numri punonjesve  Kodi  Emri Departamentit Numri punonjesve 
   

   


select 
te_vegjel.department_id as Kodi,
te_vegjel.department_name as Emri_Departamentit,
punonjes_ne_dept_endri(te_vegjel.department_id) as Numri_Punonjesve,
te_medhenj.department_id as Kodi2,
te_medhenj.department_name as Emri_Departamentit2,
punonjes_ne_dept_endri(te_medhenj.department_id) as Numri_Punonjesve2
from departments te_vegjel inner join departments te_medhenj
on punonjes_ne_dept_endri(te_vegjel.department_id)<
  punonjes_ne_dept_endri(te_medhenj.department_id)
  where punonjes_ne_dept_endri(te_vegjel.department_id)>0  ;


Shembull 2

Te ndertojme nje funksion qe merr si argument kodin e nje punonjesi dhe kthen numrin e hereve qe ai ka ndryshuar pune, pa llogaritur pozicioni korrent.

create or replace function ndryshim_pune
(
eid number
)
return number
is
here number;
begin
select count(*) into here
from job_history 
where employee_id=eid
group by employee_id;
return here;
end;


Versioni i permiresuar i funsionit qe merr parasysh edhe vlerat NULL

create or replace function ndryshim_pune
(
eid number
)
return number
is
here number;
begin
select NVL(count(*),0) into here
from job_history 
where employee_id=eid
;
return here;
end;


Sqarime

Funksioni NVL(shprehje,vlere) kthen shprehje nese shprehja nuk eshte NULL ose vlere nese shprehja eshte NULL

IF shprehje IS NULL THEN 
--return vlere
ELSE
--return shprehje
END IF


a.
Duke perdorur funksionin e krijuar afishoni listen e punonjesve qe nuk kane ndryshuar pune asnjehere.

select First_name,ndryshim_pune(employee_id)
from employees
where ndryshim_pune(employee_id)=0;


b.
Duke perdorur funksionin e krijuar afishoni perbri cdo punonjesi ata punonjes qe kane ndryshuar pune me shpesh se ai. Kerkesa shtrohet per punonjesit qe kane ndryshuar pune te pakten nje here

select stabel.First_name,stabel.Last_name,ndryshim_pune(stabel.employee_id),
jo_stabel.First_name,JO_STABEL.LAST_NAME,ndryshim_pune(jo_stabel.employee_id)
from employees stabel inner join employees jo_stabel
on ndryshim_pune(stabel.employee_id)<ndryshim_pune(jo_stabel.employee_id)
where ndryshim_pune(stabel.employee_id)>0;

c.
Duke perdorur funksionin e krijuar afishoni sa here punonjesit kane ndryshuar pune si me poshte:

 Here Punonjes
 1 100
 ... ...


select ndryshim_pune(employee_id),count(*) as Punonjes
from employees group by ndryshim_pune(employee_id);


Ushtrim 1

A) Ndertoni nje funksion me emrin ndryshime_strukturore i cili merr si argument kodin e nje departamenti dhe kthen ndryshimet qe ka pesuar ai ne numerin e punonjesve.

Zgjidhje


create or replace function ndryshime_strukturore
(
  dep_id number
)
return number IS
total number(4) :=0;
ka number(4):=0;
ka_patur number(4):=0;
BEGIN

SELECT count(*) INTO ka
FROM Employees
where department_id=dep_id;



SELECT count(*) INTO ka_patur
FROM job_history
WHERE department_id=dep_id;


total:=ka-ka_patur;


return total;
END;

b) Afishoni nje liste me departamentet dhe me ndryshimet e tyre strukturore si me poshte

 Kodi Emer_Departamenti Ndryshime_Nr_Punonjes
   

Zgjidhje





Ushtrim 2 (*)

A)Duam te dime sa punonjes ka patur nje departament ne nje date te caktuar.
Punonjesit e departamentit ne nje date te caktuar jane:
ata qe ndodhen ne tabelen employees me hire_date me te vogel se data per te cilen interesohemi
ata qe ndodhen ne tabelen job_history me kusht qe data per te cilen e interesohemi te jete midis start_date  dhe end_date
Ndertoni nje funksion qe merr si argument nje date dhe nje kod departamenti dhe kthen sa punonjes ka patur ai departament ne ate date.


b) Ndertoni nje query qe afishon sa punonjes ka patur cdo departament ne nje date x.





Zgjidhje



Ushtrim 3

A-- Ndertoni nje funksion qe merr si argument kodin e nje punonjesi dhe kthen numrin e viteve te punes qe ai ka na pozicionin aktual.

Sqarime:
funksioni MONTHS_Between(dt1,dt2) kthen numrin e muajve midis dy datave dt1,dt2. Nese pjesesohet me 12 atehere gjejme numrin e viteve si numer me presje.
Funksioni ROUND(shprehje,precizion)  ben rrumbullakosjen e nje numri me precizionin perkates. ROUND(shpreshje,0) e rrumbullakos ne numer te plote.

CREATE OR REPLACE FUNCTION vite_pune_tani
 (
 emp_id number
 )
RETURN number IS
   total number(4) := 0;
BEGIN
   SELECT round(months_between(sysdate,hire_date)/12,0) into total
   FROM employees where employee_id=emp_id;
   
   RETURN total;
END;




--B
Afishoni te dhenat e punonjesve dhe vjetersine ne vite pune qe ka sejcili ne pozicionin aktual te punes




Ushtrim 4

A-- Ndertoni nje funksion qe merr si argument kodin e nje punonjesi dhe nje date. Funksioni kthen numrin e viteve te punes qe ka punonjesi ne pozicionin e aktual te punes duke nisur llogaritjen nga data qe merr si argument.

CREATE OR REPLACE FUNCTION vite_pune_ne_date
 (
 emp_id number,
 deri_me date
 )
RETURN number IS
   total number(4) := 0;
BEGIN
   SELECT round(months_between(deri_me,hire_date)/12,0) into total
   FROM employees where employee_id=emp_id;
   
   RETURN total;
END;



--B
Funksioni vite_pune_ne_date duhet te ktheje vitet e punes deri ne daten korrente (sysdate) nese kur therrit nuk i jepet argumenti date

 CREATE OR REPLACE FUNCTION vite_pune_ne_date
   (
   emp_id number,
   deri_me date:=sysdate
   )
  RETURN number IS
     total number(4) := 0;
  BEGIN
     SELECT round(months_between(deri_me,hire_date)/12,0) into total
     FROM employees where employee_id=emp_id;
     
     RETURN total;
  END;




--C
Afishoni te dhenat e punonjesve dhe vjetersine ne vite pune qe ka sejcili ne pozicionin aktual te punes ne daten  1-JAN-2012.


select employee_id,first_name,last_name,vite_pune_ne_date(employee_id,'1-JAN-2001')
from employees;


--D
Afishoni te dhenat e punonjesve dhe vjetersine ne vite pune qe ka sejcili ne pozicionin aktual te punes ne daten  e sotme

select employee_id,first_name,last_name,vite_pune_ne_date(employee_id)
from employees;


Ushtrim 4

Ndertoni nje funksion qe merr si argument kodin e nje shteti (CHAR(2)) dhe kthen numrin e punonjesve qe punojne ne ate shtet.

Zgjidhje

create or replace function punonjes_ne_shtetin
(
shteti CHAR
)
return number
is
punonjes number;
begin

select count(*)  INTO punonjes
from employees e inner join departments d on e.department_id=d.department_id
inner join locations l on d.location_id=l.location_id
where country_id=shteti
group by country_id;


return punonjes;
end;






a. Afishoni per cdo shtet kodin,emrin dhe numrin e punonjesve ne ate shtet. Ne vend te vlerave nukk per punonjesit te vendoset 0.

 Country_id Country_Name Punonjes
 US United States of America 2

Zgjidhje

select country_id,COUNTRY_NAME,NVL(punonjes_ne_shtetin(country_id),0) as punonjes
from countries;




b. Afishoni per cdo shtet kodin,emrin dhe numrin e punonjesve ne ate shtet.
dhe perbri tij kodin emrin dhe numrin e punonjesve te shteteve qe kane me shume punonjes se ai.


 Country_id Country_Name Punonjes Country_id Country_Name Punonjes
 US United States of America 2

   


select small.country_id,small.COUNTRY_NAME,NVL(punonjes_ne_shtetin(small.country_id),0) as punonjes,
 big.country_id,big.COUNTRY_NAME,NVL(punonjes_ne_shtetin(big.country_id),0) as punonjes
from countries small inner join countries big on NVL(punonjes_ne_shtetin(small.country_id),0)<NVL(punonjes_ne_shtetin(big.country_id),0) ;

Ushtrim 5

Ndertoni nje funksion qe kthen 1 nese punonjesi eshte rikthyer ne pozicionin e punes dhe 0 nese jo.

zgjidhje

create or replace function rikthim
(
eid number
)
return number
is
rezultat number;
here number;
p varchar2(20);
begin


select job_id into  p
from employees where employee_id=eid;


select count(*) into here
from job_history 
where employee_id=eid and job_id=p;

if here>0 then
rezultat:=1;
else
rezultat:=0;
end if;

return rezultat;
end;









a. Afishoni per cdo punonjes kodin,emrin dhe 0 ose 1 nese eshte rikthyer ne pozicionin e meparshem

select employee_id,first_name,nvl(rikthim(employee_id),0) from employees
where nvl(rikthim(employee_id),0)>0 ;