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

  7. OPEN e_c;

  8. FETCH e_c into emp;

  9. while e_c%FOUND LOOP

  10. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||' mbiemri: '||emp.last_name);

  11. FETCH e_c into emp;

  12. END LOOP;

  13. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');

  14. close e_c;

  15. 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:

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 ;

  13. begin

  14. OPEN e_c;

  15. FETCH e_c into p;

  16. while e_c%FOUND LOOP

  17. dbms_output.put_line('kodi: '||p.kodi||' emri: '||p.emri||

  18. ' mbiemri: '||p.mbiemri||'salary: '||p.paga);

  19. FETCH e_c into p;

  20. END LOOP;

  21. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');

  22. close e_c;

  23. 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

  7. OPEN e_c;

  8. FETCH e_c into emp;

  9. while e_c%FOUND LOOP

  10. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||

  11. ' mbiemri: '||emp.last_name||'salary: '||emp.salary);

  12. update employees set salary=nvl(salary,0)+9 where employee_id=emp.employee_id;

  13. commit;

  14. dbms_output.put_line('kodi: '||emp.employee_id||' emri: '||emp.first_name||

  15. ' mbiemri: '||emp.last_name||'salary: '||emp.salary);

  16. FETCH e_c into emp;

  17. END LOOP;

  18. dbms_output.put_line('U afishuan gjithsej: '||e_c%ROWCOUNT||' punonjes;');

  19. close e_c;

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