Cursoare in PL/SQL

Practic, intr-un cursor se stocheaza rezultatul unei interogari. Prin el se pot itera mai usor rezultatele, rand cu rand.

Exemple...

Intr-un tabel "student" cu coloanele [ matricol | nume | grupa | medie | bursa | obs ] vrem sa:

1) parcurgem lista de studenti si sa afisam studentii cu media > media grupei (folosind un cursor, cu instructiunea FETCH)

2) actualizam bursa studentilor cu media > 7, dupa formula bursa=media*100, plus bonusuri pentru "sef_grupa" (500) sau "sef_promotie" (1000)

3) listam toti studentii dintr-o grupa dupa bursa, descrescator, cu grupa citita de la tst si data la parametru pt cursor. Listarea cuprinde: [ Nume | Grupa | Medie | Bursa | Poz. in grupa | Poz. in facultate ]

Rezolvari...

1)

set serveroutput on;

declare

CURSOR my_cursor IS

select s.nume, s.grupa, s.medie

from student s

where s.medie > (select avg(medie) from student where s.grupa=grupa group by grupa);

var my_cursor%ROWTYPE;

BEGIN

OPEN my_cursor;

dbms_output.put_line(rpad('Nume',10,' ') || ' ' || rpad('Grupa', 5, ' ') || ' ' || rpad('Medie',5, ' '));

LOOP

FETCH my_cursor into var;

EXIT when my_cursor%NOTFOUND;

dbms_output.put_line (rpad(var.nume,10,' ') || rpad(var.grupa, 5, ' ') || ' ' || rpad(var.medie,5, ' '));

END LOOP;

CLOSE my_cursor;

END;

/

-------------------------------------------------------------------------------

2)

declare

CURSOR my_cursor IS

select matricol, obs

from student

where medie > 7;

var my_cursor%ROWTYPE;

adaos number:=0;

BEGIN

FOR var IN my_cursor

LOOP

EXIT WHEN my_cursor%NOTFOUND;

adaos := 0;

IF var.obs = 'sef_grupa' THEN adaos := 500; END IF;

IF var.obs = 'sef_promotie' THEN adaos := 1000; END IF;

UPDATE student SET bursa=medie*100+adaos WHERE var.matricol=matricol;

END LOOP;

END;

/

------------------------------------------------------------------------

3) (nu foarte eficient)

set serveroutput on;

declare

CURSOR myc (p_grupa varchar2) IS

select matricol,nume,grupa, bursa,medie

from student

where grupa=p_grupa

order by bursa DESC;

var myc%ROWTYPE;

CURSOR cursor2 IS

select matricol

from student

order by bursa DESC;

var2 cursor2%ROWTYPE;

poz_grupa number:=1;

poz_fac number:=0;

contor number:=1;

param varchar2(5);

BEGIN

dbms_output.put_line(rpad('Nume', 20, ' ') || ' ' || rpad('Grupa', 6, ' ') || ' ' || rpad('Bursa',6, ' ') || ' ' || rpad('Medie', 7, ' ') || rpad('Poz grupa',9,' ') || ' ' || rpad('Poz fac', 7, ' '));

param := &readparam;

FOR var IN myc(param)

LOOP

EXIT WHEN myc%NOTFOUND;

contor:=1;

FOR var2 IN cursor2

LOOP

IF var2.matricol = var.matricol THEN poz_fac := contor; END IF;

contor:= contor+1;

EXIT WHEN cursor2%NOTFOUND;

END LOOP;

dbms_output.put_line(rpad(var.nume,20, ' ') || ' ' || rpad(var.grupa, 6, ' ') || ' ' || rpad(var.bursa, 6, ' ') || ' ' || rpad(var.medie, 7, ' ') || ' ' || rpad(poz_grupa, 9, ' ') || ' ' || rpad(poz_fac, 7, ' '));

poz_grupa:=poz_grupa+1;

END LOOP;

END;

/