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.

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

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:

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

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.

{me shume https://sites.google.com/a/ictedu.info/oracle-1z0-051/home/chapter-05}

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.

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.

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 ;