Practice Procedures



Ushtrim 1

Ndertoni nje procedure qe merr si argument dy numra dhe kthen maksimumin e tyre.

  1. CREATE or replace PROCEDURE findmax(x IN number, y IN number, z OUT number) IS
  2. BEGIN
  3. IF x > y THEN
  4. z:= x;
  5. ELSE
  6. z:= y;
  7. END IF;
  8. END; 

Ta perdorim proceduren

  1. SET SERVEROUTPUT ON
  2. DECLARE
  3. numri1 number;
  4. numri2 number;
  5. m number;
  6. BEGIN
  7. numri1:=&n1;
  8. numri2:=&n2;
  9. findmax(numri1,numri2,m);
  10. dbms_output.put_line(' max midis '||numri1||' dhe '||numri2||' eshte : ' ||m);
  11. END;


Ushtrim 2

Ndertoni nje procedure qe ngre nje numer ne rrenje katrore. {Procedur me nje argument hyres dhe dales}


  1. CREATE OR REPLACE PROCEDURE squareNum
  2. (
  3. x IN OUT number
  4. ) 
  5. IS
  6. BEGIN
  7. x := x * x;
  8. END;



Ta perdorim proceduren


  1. set serveroutput on
  2. DECLARE
  3. n number;
  4. BEGIN
  5. n:=&numri;
  6. squareNum(n);
  7. dbms_output.put_line('numri i ngritur ne rrenje katrore eshte: '||n);
  8. END;



Ushtrim 3 

Ndertoni nje procedure qe mer si argument kodin e nje punonjesi dhe kthen emrin dhe mbiemrin e tij ne dy variabla dales


Zgjidhje


  1. CREATE OR REPLACE PROCEDURE pun
  2. (
  3. eid IN number,
  4. emri out varchar2,
  5. mbiemri OUT varchar2
  6. )
  7. IS
  8. --seksioni i deklarimit
  9. BEGIN
  10. select first_name,last_name into emri,mbiemri
  11. from employees where employee_id=eid;
  12. END;


Ta perdorim

  1. SET SERVEROUTPUT ON
  2. DECLARE
  3. eid number;
  4. emri varchar2(50);
  5. mbiemri varchar2(50);
  6. BEGIN
  7. eid:=&n;
  8. pun(eid,emri,mbiemri);
  9. dbms_output.put_line(' emri i '||eid||' eshte '||emri||' mbiemri : ' ||mbiemri);
  10. END;





Ushtrim 4

Ndertoni nje procedure qe merr si argument kodin e nje punonjesi dhe kthen emrin,mbiemrin,emrin e departamentit ku ai punon, emeretiin e pozicionit te punes, dhe numrin e hereve qe ka ndryshuar pozicion pune.

  1. create or replace procedure proc1
  2. (
  3. eid IN number,
  4. emri OUT varchar(50),
  5. mbiemri OUT varchar(50),
  6. departamenti OUT varchar(50),
  7. puna OUT varchar,
  8. here OUT number
  9. )
  10. IS
  11. BEGIN

  13. SELECT FIRST_NAME,LAST_NAME INTO emri,mbiemri
  14. FROM EMPLOYEES
  15. WHERE Employee_id=eid;

  17. SELECT department_name INTO departamenti
  18. from Departments d JOIN Employees e 
  19. on d.department_id=e.department_id
  20. where e.employee_id=eid;

  22. SELECT COUNT(*) INTO here
  23. FROM Job_History 
  24. where employee_id=eid;

  26. END;





Ushtrim 5

Ndertoni nje procedure qe merr si argument te dhenat e nje punonjesi {si ne prototipin me poshte} , e shton ate ne tabelen e punonjesve {Employees} dhe kthen kodin qe iu caktua ketij punonjesi


Prototipi
CREATE OR REPLACE PROCEDURE shtopun
(
emri in varchar2,
mbiemri in varchar2,
email in varchar2,
hire_date in date:=sysdate,
job_id in varchar2,
kodi out number
)


Zgjidhje


CREATE OR REPLACE PROCEDURE shtopun
(
emri in varchar2,
mbiemri in varchar2,
email in varchar2,
hire_date date:=sysdate,
job_id varchar2,
kodi out number
)
IS
--seksioni i deklarimit
BEGIN
insert into employees(Employee_id,First_name,Last_name,email,hire_date,job_id)
values (EMPLOYEES_SEQ.NEXTVAL,emri,mbiemri,email,hire_date,job_id);
kodi:=EMPLOYEES_seq.CURRVAL;
commit;
END;


Ta perdorim proceduren per te shtuar punonjes

SET SERVEROUTPUT ON
DECLARE
kodi number;
emri varchar2(50);
mbiemri varchar2(50);
BEGIN

shtopun('&emri','&mbiemri','&email_unik',sysdate,'IT_PROG',kodi);

dbms_output.put_line(' punonjesi i shtua me kodin: '||kodi);
END;


Ushtrim 6


Rishkruani proceduren me siper duke kontrolluar qe nuk shtohen punonjes me shume se sa 10 per ate pozicion pune.


CREATE OR REPLACE PROCEDURE shtopun
(
emri in varchar2,
mbiemri in varchar2,
email in varchar2,
hire_date date:=sysdate,
pozicioni varchar2,
kodi out number
)
IS
--seksioni i deklarimit
tani number(4);
BEGIN


select count(*) into tani 
from employees where job_id=pozicioni;


if tani<10 then
insert into employees(Employee_id,First_name,Last_name,email,hire_date,job_id)
values (EMPLOYEES_SEQ.NEXTVAL,emri,mbiemri,email,hire_date,pozicioni);
kodi:=EMPLOYEES_seq.CURRVAL;
commit;
else
kodi:=0;
end if;
END;


Ushtrim 7

Ndertoni nje procedure qe shton nje departament. Procedura nuk duhet te shtoje departament ne nje lokacion ku ka me shume se 2 departamente.

Zgjidhje


create or replace procedure shto_departament_endri
(
emri varchar2,
vend_ndodhja number,
kodi out number
)
is
tani number;
BEGIN
select count(*) into tani from departments where location_id=vend_ndodhja;


IF tani<3 THEN


insert into departments(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)
VALUES(dept_deptid_seq.NEXTVAL,emri,vend_ndodhja);

kodi:=dept_deptid_seq.CURRVAL;
commit; 
ELSE
kodi:=0;
END IF;


END;



Ushtrim 8


Ndertoni nje procedure e cila merr dy argumenta hyres:
- numrin e viteve te punes (vite psh 10)
- koeficientin e rritjes se pages (rritje psh 1.1)
dhe nje argument dales:
- punonjes
procedura rrit pagat e punonjesve qe kane me shume se vite vite pune ne pozicionin aktual te punes me koeficientin rritje ( salary*rritje)
Procedura kthen numrin e punonjesve te cileve iu be nje rritje page


Zgjidhje


create or replace procedure rritje_page
(
vite IN number,
rritje IN number,
punonjes OUT number
)
IS
BEGIN 
update employees 
set salary=salary*rritje
where 
(round(months_between(sysdate,hire_date)/12,0) > vite);

select count(*) into punonjes
from employees
where 
(round(months_between(sysdate,hire_date)/12,0) > vite);

commit;
END;



Ushtrim 8A

Ndertoni nje procedure e cila merr dy argumenta hyres:
- numrin e viteve te punes (vite psh 10) 
- koeficientin e rritjes se pages (rritje psh 1.1)
- daten , ne te cilen do te llogaritet vjetersia e punes
dhe nje argument dales:
- punonjes
procedura rrit pagat e punonjesve qe kane me shume se vite vite pune ne pozicionin aktual te punes ne daten e dhene si argument me koeficientin rritje ( salary*rritje)
Procedura kthen numrin e punonjesve te cileve iu be nje rritje page


Zgjidhje


create or replace procedure rritje_page_endri
(
vite IN number,
rritje IN number,
data_llogaritjes IN date :=sysdate,
punonjes OUT number
)
IS
BEGIN 
update employees 
set salary=salary*rritje
where 
(round(months_between(data_llogaritjes,hire_date)/12,0) > vite);

select count(*) into punonjes
from employees
where 
(round(months_between(data_llogaritjes,hire_date)/12,0) > vite);

commit;
END;


Ushtrim 9

Ndertoni nje procedure e cila merr dy argumenta hyres:
- numrin e viteve te punes (vite psh 10)
- koeficientin e rritjes se pages (rritje psh 1.1)
dhe nje argument dales:
- kosto
procedura rrit pagat e punonjesve qe kane me shume se vite vite pune ne pozicionin aktual te punes me koeficientin rritje ( salary*rritje)
Procedura kthen impaktin financiar te kompanise per kete rritje page


Zgjidhje


create or replace procedure rritje_page_kosto
(
vite IN number,
rritje IN number,
kosto OUT number
)
IS
BEGIN 


select sum(salary*(rritje-1)) into kosto
from employees
where 
(round(months_between(sysdate,hire_date)/12,0) > vite);



update employees 
set salary=salary*rritje
where 
(round(months_between(sysdate,hire_date)/12,0) > vite);


commit;
END;




Ushtrimi 9A

Kerkesa si ne ushtrimin 8A

Zgjidhje





Ushtrim 10

Ndertoni nje procedure e cila merr dy argumenta hyres:
- numrin e viteve te punes (vite psh 10)
- koeficientin e rritjes se pages (rritje psh 1.1)
dhe nje argument dales:
- punonjes
procedura rrit pagat e punonjesve qe kane me shume se vite vite pune qe punojne ne kompani me koeficientin rritje ( salary*rritje)
Procedura kthen numrin e punonjesve te cileve iu be nje rritje page


Zgjidhje


Te krijojme paraprakisht nje funksion qe kthen vitet e punes qe ka nje punonjes




create or replace function vite_pune
(
eid number,
dt date
)
return number
IS
dt_fillimi date;
vite number;
BEGIN


select min(hire_date) INTO dt_fillimi
from 
(
select employee_id,hire_date from employees
union
select employee_id,start_date from job_history
) t 
where t.employee_id=eid;


vite:=round(months_between(dt,dt_fillimi)/12,0);


return vite;
END;


Procedura



create or replace procedure rritje_page_punonjes
(
vite IN number,
rritje IN number,
punonjes OUT number
)
IS
BEGIN 


select count(*) into punonjes
from employees
where 
(vite_pune_ne_date(employee_id,sysdate) > vite);



update employees 
set salary=salary*rritje
where 
(vite_pune_ne_date(employee_id,sysdate) > vite);


commit;
END;


Ushtrim 11

Ndertoni nje procedure e cila merr dy argumenta hyres:
- numrin e viteve te punes (vite psh 10)
- koeficientin e rritjes se pages (rritje psh 1.1)
dhe nje argument dales:
- punonjes
procedura rrit pagat e punonjesve qe kane me shume se vite vite pune qe punojne ne kompani me koeficientin rritje ( salary*rritje)
Procedura kthen impaktin financiar te kompanise per kete rritje page


Zgjidhje



create or replace procedure rritje_page_kosto
(
vite IN number,
rritje IN number,
kosto OUT number
)
IS
BEGIN 


select sum(salary*(rritje-1)) into kosto
from employees
where 
(vite_pune_ne_date(employee_id,sysdate) > vite);



update employees 
set salary=salary*rritje
where 
(vite_pune_ne_date(employee_id,sysdate) > vite);


commit;
END;


Ushtrim 14

Ndertoni nje procedure e cila merr nje argument:
- kodi i punonjesit (punonjesi psh 10)
Procedura vendos ne kete argument kodin e punonjesit qe eshte shef i drejtperdrejte i punonjesit me kodin punonjes


Zgjidhje


create or replace procedure shefi_proc
(
eid IN OUT number
)
is
shefi number;
begin
select manager_id into shefi
from employees where employee_id=eid;
eid:=shefi;
end


Ta perdorim proveduren ne nje kod shembull (psh per punonjesin me kodin 999)


SET SERVEROUTPUT ON
DECLARE
kod_punonjesi number(4);


BEGIN
kod_punonjesi:=&n;
shefi_proc(kod_punonjesi);
dbms_output.put_line(kod_punonjesi);
END;