Practice Procedures
Ushtrim 1
Ndertoni nje procedure qe merr si argument dy numra dhe kthen maksimumin e tyre.
CREATE or replace PROCEDURE findmax(x IN number, y IN number, z OUT number) IS
BEGIN
IF x > y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
Ta perdorim proceduren
SET SERVEROUTPUT ON
DECLARE
numri1 number;
numri2 number;
m number;
BEGIN
numri1:=&n1;
numri2:=&n2;
findmax(numri1,numri2,m);
dbms_output.put_line(' max midis '||numri1||' dhe '||numri2||' eshte : ' ||m);
END;
Ushtrim 2
Ndertoni nje procedure qe ngre nje numer ne rrenje katrore. {Procedur me nje argument hyres dhe dales}
CREATE OR REPLACE PROCEDURE squareNum
(
x IN OUT number
)
IS
BEGIN
x := x * x;
END;
Ta perdorim proceduren
set serveroutput on
DECLARE
n number;
BEGIN
n:=&numri;
squareNum(n);
dbms_output.put_line('numri i ngritur ne rrenje katrore eshte: '||n);
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
CREATE OR REPLACE PROCEDURE pun
(
eid IN number,
emri out varchar2,
mbiemri OUT varchar2
)
IS
--seksioni i deklarimit
BEGIN
select first_name,last_name into emri,mbiemri
from employees where employee_id=eid;
END;
Ta perdorim
SET SERVEROUTPUT ON
DECLARE
eid number;
emri varchar2(50);
mbiemri varchar2(50);
BEGIN
eid:=&n;
pun(eid,emri,mbiemri);
dbms_output.put_line(' emri i '||eid||' eshte '||emri||' mbiemri : ' ||mbiemri);
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.
create or replace procedure proc1
(
eid IN number,
emri OUT varchar(50),
mbiemri OUT varchar(50),
departamenti OUT varchar(50),
puna OUT varchar,
here OUT number
)
IS
BEGIN
SELECT FIRST_NAME,LAST_NAME INTO emri,mbiemri
FROM EMPLOYEES
WHERE Employee_id=eid;
SELECT department_name INTO departamenti
from Departments d JOIN Employees e
on d.department_id=e.department_id
where e.employee_id=eid;
SELECT COUNT(*) INTO here
FROM Job_History
where employee_id=eid;
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;