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;
/
