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

  12. SELECT FIRST_NAME,LAST_NAME INTO emri,mbiemri

  13. FROM EMPLOYEES

  14. WHERE Employee_id=eid;

  15. SELECT department_name INTO departamenti

  16. from Departments d JOIN Employees e

  17. on d.department_id=e.department_id

  18. where e.employee_id=eid;

  19. SELECT COUNT(*) INTO here

  20. FROM Job_History

  21. where employee_id=eid;

  22. 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;