BoomBleBee: PL/SQL

Se afișează postările cu eticheta PL/SQL. Afișați toate postările

16 decembrie 2011

Exercitii noi PL/SQL

I. Tabela student {matricol, nume, grupa, medie, bursa} si tabela note {matricol, data, nota}
a) un trigger pt fiecare insert/update/delete in tabela note care va actualiza corespunzator tabela student cu media care reiese;
Apoi se vor insera note diverse.
b) o procedura stocata care acorda burse unui student luat ca parametru (matricol) a.i. pt fiecare nota de 7,8 => add 100 la bursa, pt fiecare nota 9, 10 => add 200 la bursa
c) de apelat procedura intr-un cursor pt toti studentii intr-o grupa (grupa data de la tastatura)
d) afisare tabel {matricol, nume, grupa, medie, nr.note, bursa}

codul sursa AICI

II. Pachetul facultate care contine o functie bonus (cu parametru matricol) si intoarce o valoare egala cu 100*nr.notelor de 10. Pachetul mai are un cursor ce selecteaza studentii care au cel putin 3 note de 10 si media peste 9. Folosind cursorul si functia definita, se adauga bonusul la valoarea bursei studentului care respecta toate aceste conditii. Exceptie: pt studentul cu bursa > 1000, tratare prin setarea bursei la 1000.

// creare tabele

create table note (
matricol number(3),
cod_materie number(3),
nota number(2)
);

create table studenti (

matricol number(3),
bursa number(3)
);

// interfata pachet

CREATE OR REPLACE PACKAGE facultate AS

FUNCTION bonus (mymatricol IN number) RETURN number;

CURSOR smart IS
select S.matricol from studenti S
where S.matricol IN (
select N.matricol from note N
where (select count (*) from note where nota = 10 AND matricol = N.matricol) > 2
) AND (
select avg(nota)
from note
where matricol = S.matricol
) >= 9;

bursa_mare EXCEPTION;

END;

// implementare pachet

CREATE OR REPLACE PACKAGE BODY facultate AS

FUNCTION bonus (mymatricol IN number) RETURN number IS
numar number;
begin
numar:=0;
select count(*) into numar
from note
where matricol=mymatricol AND nota=10;
return 100*numar;
end bonus;

END facultate;

// programul principal

declare
vc facultate.smart%ROWTYPE;
valoare number;

BEGIN

open facultate.smart;
LOOP BEGIN
fetch facultate.smart into vc;
exit when facultate.smart%NOTFOUND;

valoare := facultate.bonus(vc.matricol);
IF valoare>1000 THEN raise facultate.bursa_mare; END IF;

update studenti set bursa=bursa+valoare
where matricol=vc.matricol;

EXCEPTION WHEN facultate.bursa_mare
THEN update studenti set bursa=1000 where matricol=vc.matricol;

END;
END LOOP;
close facultate.smart;
END;

12 decembrie 2011

Functii si pachete in PL/SQL

Acelasi tabel student {matricol, nume, grupa, medie, bursa} :

Functii

a) o functie nestocata care intoarce un mesaj: valideaza valoarea burselor primite de studenti dupa regulile:

1. studentii restantieri, cei cu media < 7 si cei cu media mai mica decat media grupei lor nu primesc burse
2. bursele nu pot depasi 1000 sau sa fie mai mici de 100
3. bursele nu pot fi > media*110

b) o functie stocata pe disc ce calculeaza bursa recomandata pt fiecare student, dupa regulile:

1. media peste 7 si media > media grupei
2. suma burselor studentilor din grupa sa nu depaseasca 2000
3. fiecare bursa sa fie cuprinsa intre 100 si 1000 si sa fie proportionala cu mediile din grupa (bursa=media*2000/suma_medii_grupa). De actualizat bursele cu aceste valori.

c) Pentru fiecare grupa calculat coeficientul grupei k = (nr_studenti cu media > media grupei)/nr total de studenti. Se vor da bonusuri studentilor proportional cu acest coeficient: bursa = bursa * (k+1). La coeficientul cel mai mic nu se da nimic, la coeficientul cel mai mare se da bonus inca o data valoarea bursei.

rezolvarile aici

Pachete

Au 2 parti: specificatia (un fel de interfata) si implementarea (functiilor, procedurilor).

In specificatie se pun variabilele globale, definitia tipurilor de date create, antetele functiilor si procedurilor, se denumesc exceptiile si se scriu cursoarele.

Obiectul creat se numeste facultate si are:

variabila globala nr_studenti
o functie: getter pentru nr_studenti
o procedura: setter pentru nr_studenti
definirea unui tip de date grupa cu campurile {nume, nr_studenti, medie}
o functie ce intoarce un obiect de tip grupa, cu parametru nr_matricol
o procedura care listeaza o grupa luata ca parametru, si afiseaza cele 3 campuri
un cursor care itereaza peste multimea de cei mai buni studenti pt fiecare grupa
o exceptie bursa_0

Se cere:

a) afisarea var globale nr_studenti

b) stergerea celui mai slab student din fiecare grupa, actualizarea var globale & reafisarea ei

c) folosind cursorul si functiile definite - listarea tuturor grupelor

d) folosind acelasi cursor, ridicarea exceptiei bursa_0 cand un student cu media cea mai mare din grupa are bursa 0; se trateaza cu actualizarea bursa=media*100.

rezolvarile aici

06 decembrie 2011

Triggeri in PL/SQL

Tabela student: {matricol, nume, grupa, medie, bursa}

1) creati un trigger care la fiecare inserare in tabela va popula bursa acelor stud cu medii > 7 , bursa = medie * 100; dupa acest trigger, insert 10 inreg in tabela; la inserare bursa=0

2) creati un nou trigger care se va asigura k la fiecare update al coloanei bursa , bursa nu se va da decat stud cu medii >= 7 si bursa e in intervalul 700..1000 (0 la medie<7, 700 la mai mic, 100 la mai mare); trigger pe toti studentii dintr-o grupa

3) folosind un view (proiectie a tabelei student), creati un trigger pe acest view care se executa la comanda delete si in momentul stergerii unui rand din view sa redistribuie bursa studentului respectiv celorlalti studenti din grupa sa; dar daca studentul este cel mai bun din grupa atunci stergerea este anulata;

create table studenti (

matricol number(3),

nume varchar2(40),

grupa varchar2(5),

medie number(2),

bursa number(5)

);

CREATE OR REPLACE TRIGGER inserare

BEFORE INSERT ON studenti FOR EACH ROW

WHEN (new.bursa = 0 AND new.medie > 7)

BEGIN

:new.bursa := :new.medie * 100;

END;

insert into studenti values (100, 'ion', '341C1', 8, 0);

insert into studenti values (101, 'gheo', '341C1', 10, 0);

insert into studenti values (102, 'vasi', '341C1', 7, 0);

insert into studenti values (103, 'delia', '341C1', 5, 0);

insert into studenti values (104, 'diana', '341C1', 9, 0);

insert into studenti values (105, 'liviu', '341C5', 9, 0);

insert into studenti values (106, 'xena', '341C5', 8, 0);

insert into studenti values (107, 'raul', '341C5', 10, 0);

insert into studenti values (108, 'costin', '341C5', 9, 0);

insert into studenti values (109, 'daria', '341C5', 7, 0);

pentru celelalte exercitii rezolvate accesati (aici)

18 noiembrie 2011

Proceduri PL/SQL

Arhiva conține rezolvări pentru exercițiile următoare:

(pe un tabel studenti cu câmpuri pentru matricol, nume, grupa, medie, bursa)

1. procedură nestocată cu parametru un număr fond_pentru_burse care va seta bursele studenților cu media > 7. Bursele sunt proporționale cu media, fondul trebuie consumat integral.

2. procedură nestocată care calculează pentru o grupă dată ca parametru: nr de studenți, media mediilor și suma burselor acordate. Apelată pentru toate grupele din tabelă

3. procedură stocată pe server ce include un cursor explicit, primește ca parametri 2 numere: medie și bonus. Iterează pe mai mulți studenți cu media > medie și adaugă bonusul la valoarea bursei lor.

09 noiembrie 2011

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

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)

Rezolvari...

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;

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

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;

03 noiembrie 2011

Exceptii in PL/SQL

drop table stud;

create table stud (

matricol number(4) primary key,

nume varchar2(30) unique not null,

grupa varchar2(5),

medie number(10,2) check (medie>1 AND medie<=10),

bursa number(3)

);

insert into stud values (1, 'elena', '333CA', 8.7, 0);

insert into stud values (2, 'geanina', '331CA', 5.7, 0);

insert into stud values (3, 'simona', '332CA', 8.2, 0);

insert into stud values (4, 'cornel', '335CA', 9.5, 0);

insert into stud values (5, 'mihai', '334CA', 10, 0);

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

Exceptie cand numele inserat nu este unic:

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

set serveroutput on;

declare

smat number(4);

snume varchar2(30);

sgrupa varchar2(5);

smedie number(10,2);

sbursa number(3);

begin

smat := &matricol;

snume := &nume;

sgrupa := &grupa;

smedie := &medie;

sbursa := &bursa;

insert into stud values (smat, snume, sgrupa, smedie, sbursa);

dbms_output.put_line('inserat cu succes');

exception

when DUP_VAL_ON_INDEX then

dbms_output.put_line(' Nu s-a putut insera: numele nu este unic');

end;

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

Exceptie cand bursa nenula pt student cu media<6

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

set serveroutput on;

declare

smat number(4);

snume varchar2(30);

sgrupa varchar2(5);

smedie number(10,2);

sbursa number(3);

bursa_not_allowed exception;

begin

smat := &matricol;

snume := &nume;

sgrupa := &grupa;

smedie := &medie;

sbursa := &bursa;

if smedie<6 AND sbursa>0 then

raise bursa_not_allowed;

end if;

insert into stud values (smat, snume, sgrupa, smedie, sbursa);

dbms_output.put_line('inserat cu succes');

exception

when bursa_not_allowed then

dbms_output.put_line(' Nu s-a putut insera: bursa nenula pt student cu media sub 6');

end;

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

Idem primul exemplu - dar altfel

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

set serveroutput on;

declare

smat number(4);

snume varchar2(30);

sgrupa varchar2(5);

smedie number(10,2);

sbursa number(3);

default_name varchar2(30);

null_exc exception;

pragma exception_init (null_exc, -1400);

begin

smat := 17;

snume := null;

sgrupa := '321cc';

smedie := 8;

sbursa := 0;

default_name := 'default4';

insert into stud values (smat, snume, sgrupa, smedie, sbursa);

dbms_output.put_line('inserat cu succes');

exception

when null_exc then

dbms_output.put_line('Eroare: numele trebuie sa fie nenul!');

insert into stud values (smat, default_name, sgrupa, smedie, sbursa);

dbms_output.put_line('Am inserat: ' || smat || ' ' || default_name || ' ' || sgrupa || ' ' || smedie || ' ' || sbursa);

end;

28 octombrie 2011

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:

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