Practice Cursors




Ushtrim 1

Ndertoni nje kursor qe afishon emrin dhe mbiemrin e cdo punonjesi duke perdoror employees%rowtype;

Zgjdhje

  1. set serveroutput on;
  2. Declare
  3. cursor e_c is select *
  4. from employees order by employee_id ;
  5. emp employees%rowtype;
  6. begin

  8. OPEN  e_c;
  9. FETCH e_c into emp;
  10. while e_c%FOUND LOOP
  11. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||' mbiemri: '||emp.last_name);
  12. FETCH e_c into emp;
  13. END LOOP;
  14. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');
  15. close e_c;

  17. end;


Cfare afishon kodi ne rreshtin 14?
Provoni ta spostoni kodin ne rreshtin 14  ne rreshtin 16 dhe riekzekutoni skriptin. Shpjegoni rezultatin.
Provoni ta spostoni kodin ne rreshtin 14  ne rreshtin 7 dhe riekzekutoni skriptin. Shpjegoni rezultatin.


Ushtrim 1(B)

Deklaroni nje tipe rekordi punonjes me fushat e meposhtme:

 KodiEmri  Mbiemri salary
 number varchar(50) varchar(50)number 

Duke perdorur tipin e rekordit punonjes ,ndertoni nje kursor qe afishon kodin, emrin, mbiemrin dhe pagen e cdo punonjesi.


zgjidhje

  1. set serveroutput on;
  2. Declare
  3. TYPE punonjes IS RECORD
  4. (
  5. kodi number,
  6. emri varchar(50),
  7. mbiemri varchar(50),
  8. paga number
  9. );
  10. p punonjes;
  11. cursor e_c is select employee_id,first_name,last_name,salary
  12. from employees order by employee_id ;

  14. begin

  16. OPEN  e_c;
  17. FETCH e_c into p;
  18. while e_c%FOUND LOOP
  19. dbms_output.put_line('kodi: '||p.kodi||' emri: '||p.emri||
  20. ' mbiemri: '||p.mbiemri||'salary: '||p.paga);

  22. FETCH e_c into p;
  23. END LOOP;
  24. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');
  25. close e_c;

  27. end;




Ushtrim 2

Te afishojme sa here ka nderruar pune sejcili nga punonjesit.

SET SERVEROUTPUT ON
DECLARE 
 emp_rec employees%rowtype;
 here number:=0;
  CURSOR emp_cur IS 
 SELECT *
   FROM Employees;
  -- WHERE salary > 10; 
 BEGIN 
    OPEN emp_cur; 
    LOOP
    FETCH emp_cur INTO emp_rec; 
   EXIT WHEN emp_cur%NOTFOUND;  
   select count(*) into here 
   from job_history where employee_id=emp_rec.employee_id  ;
      dbms_output.put_line (emp_rec.first_name || '  ' || emp_rec.last_name||here); 
      END LOOP;
   CLOSE emp_cur; 
 END; 





Ushtrim 3


Ndertoni nje skript qe perdor kursoret per te kapur rreshtat e punonjesve nje e nga nje. 

Per cdo punonjes skripti:
  1. afishon kodin,emrin,mbiemrin,pagen.
  2. Modifikon pagen duke i shtuar 9 euro 
  3. Ben commit veprimin
  4. afishon kodin,emrin,mbiemrin,pagen

si ne formatin me poshte:
kodi: 414 emri: Steven mbiemri: Kingsalary: 18
kodi: 414 emri: Steven mbiemri: Kingsalary: 18
kodi: 415 emri: Steven mbiemri: Kingsalary: 18
kodi: 415 emri: Steven mbiemri: Kingsalary: 18
kodi: 416 emri: Steven mbiemri: Kingsalary: 18
kodi: 416 emri: Steven mbiemri: Kingsalary: 18

Zgjidhje

  1. set serveroutput on;
  2. Declare
  3. cursor e_c is select *
  4. from employees order by employee_id ;
  5. emp employees%rowtype;
  6. begin

  8. OPEN  e_c;
  9. FETCH e_c into emp;
  10. while e_c%FOUND LOOP
  11. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||
  12. ' mbiemri: '||emp.last_name||'salary: '||emp.salary);
  13. update employees set salary=nvl(salary,0)+9 where employee_id=emp.employee_id;
  14. commit;
  15. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||
  16. ' mbiemri: '||emp.last_name||'salary: '||emp.salary);
  17. FETCH e_c into emp;
  18. END LOOP;
  19. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');
  20. close e_c;

  22. end;

A afishohet paga e modifikuar ne kursor apo jo? Pse?





Ushtrim 4

Ndertoni nje cursor qe lexon tabelen e punonjesve me kollonat employee_id,first_name,last_name te renditur sipas employee_id.
Kursori afishon rreshtat nje e nga nje. Kursori afishon gjithashtu hapesirat midis id te punonjesve aty ku i gjen ato.
Ne fund kodi afishon totalin e boshlleqeve ne ID e punonjesve si me poshte:

kodi: 197 emri: Kevin mbiemri: Feeney
gap :197->199 gap=1
kodi: 199 emri: Douglas mbiemri: Grant
kodi: 200 emri: Jennifer mbiemri: Whalen
kodi: 201 emri: Michael mbiemri: Hartstein

Zgjidhje

set serveroutput on;
Declare
cursor e_c is select employee_id,first_name,last_name 
from employees order by employee_id ;
kodi NUMBER;
k1 number;
k2 number;
step number;
gaps number:=0;
emri varchar2(50);
mbiemri varchar2(50);
begin
OPEN  e_c;
FETCH e_c into KODI,EMRI,mbiemri;
k1:=kodi;
k2:=kodi;
while e_c%FOUND LOOP
dbms_output.put_line('kodi: '||kodi||' emri: '||emri||' mbiemri: '||mbiemri);
FETCH e_c into KODI,EMRI,mbiemri;
k2:=kodi;
step:=k2-k1-1;
if(step>0) then
dbms_output.put_line('gap :'||k1||'->'||k2||' gap='||step);
end if;
k1:=kodi;
gaps:=gaps+step;
END LOOP;
close e_c;
dbms_output.put_line('gaps: '||gaps);
end;