Executii ciclice in PL/SQL

Se da un tabel student cu campurile:

nr_mat | nume | grupa | medie | bursa

Numerele matricole vor fi introduse consecutiv.

1) Setare bursa pt toti studentii dupa formula:

media < 6 => bursa = 0

media < 7 => bursa = media*100

media < 8 => bursa = media*120

media < 9 => bursa = media*150

media < 10 => bursa = media*200

media = 10 => bursa = media*250

declare

nr_mat_s number(2);

media_s number(10,2);

bursa_s number(4,2);

contor number(3):=1;

begin

LOOP

select nr_mat, medie, bursa into nr_mat_s, media_s, bursa_s

from student

where nr_mat = contor;

if media_s < 6 then

update student set bursa = 0 where nr_mat = nr_mat_s;

elsif media_s < 7 then

update student set bursa = medie*100 where nr_mat = nr_mat_s;

elsif media_s < 8 then

update student set bursa = medie*120 where nr_mat = nr_mat_s;

elsif media_s < 9 then

update student set bursa = medie*150 where nr_mat = nr_mat_s;

elsif media_s < 10 then

update student set bursa = medie*200 where nr_mat = nr_mat_s;

else

update student set bursa = medie*250 where nr_mat = nr_mat_s;

end if;

contor:=contor+1;

EXIT WHEN CONTOR = 11;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND then

dbms_output.put_line('Nicio modificare');

END;

/

2) Pt fiecare grupa, adaugati 10% la bursa studentilor cu media >= 8, dar numai daca media grupei din care fac parte >= 7.60

declare

nr_mat_s number(2);

media_s number(10,2);

bursa_s number(4,2);

medie_grupa number(10,2);

grupa_s varchar2(5);

contor number(3):=1;

begin

LOOP

select nr_mat, medie into nr_mat_s, media_s

from student

where medie>=8 AND nr_mat = contor AND grupa IN

(select tabel.g

from

(select grupa g, avg(medie) a

from student

group by grupa) tabel

where tabel.a > 8

);

update student set bursa = bursa*1.1 where nr_mat = nr_mat_s;

contor:=contor+1;

EXIT WHEN CONTOR = 11;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND then

dbms_output.put_line('Nicio modificare');

END;

/

3) Pentru fiecare student, afisati o linie de raport cu coloanele:

nr_mat | nume | grupa | medie | medie_grupa | bursa | obs

obs:

medie < 5 => obs = "Repetent"

medie = 10 => obs = "Sef de promotie"

set serveroutput on;

declare

nr_mat_s number(2);

medie_s number(10,2);

bursa_s number(15,2);

medie_grupa number(10,2);

grupa_s varchar2(5);

nume_s varchar2(30);

avg_s number(10,4);

obs varchar2(30);

contor number(3):=1;

begin

dbms_output.put_line(

rpad('Nr_mat',6) || rpad('Nume', 20) || rpad('Grupa', 5) || rpad('Medie', 10) ||

rpad('Medie_grupa', 11) || rpad('Bursa', 10) || rpad('Obs', 20)

);

LOOP

select s.nr_mat, s.nume, s.grupa, s.medie, tabel.a, s.bursa into nr_mat_s, nume_s, grupa_s, medie_s, avg_s, bursa_s

from student s, (select grupa g, avg(medie) a

from student

group by grupa) tabel

where s.grupa = tabel.g AND contor = s.nr_mat;

if medie_s < 5 then obs := 'Repetent';

elsif medie_s = 10 then obs := 'Sef promotie';

else obs := '-';

end if;

dbms_output.put_line(

rpad(to_char(nr_mat_s), 6) || ' ' || rpad(nume_s, 20) || ' ' || rpad(grupa_s, 5) || ' ' ||

rpad(to_char(medie_s), 10) || ' ' || rpad(to_char(avg_s), 11) || ' ' || rpad(to_char(bursa_s), 10) || ' ' ||

rpad(obs,20)

);

contor := contor + 1;

EXIT WHEN CONTOR = 11;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND then

dbms_output.put_line('Nicio modificare');

END;

/