Gestiunea caminelor studentesti
Scarlat Simona Ileana, grupa 1034, seria B, Informatica economica
A. Descrierea problemei și prezentarea schemei conceptuale Tema proiectului este reprezentata de gestiunea unei baze de date care contine evidenta caminelor studentesti oferite de toate facultatile din cadrul unei universitati. Tabela Universitate contine informatii referitoare la nume, adresa si telefon. Tabela Facultate contine informatii despre numele fiecarei facultati, orasul, numarul de studenti si locurile in camin pe care le ofera. O facultate nu poate apartine mai multor universitati. Tabela Student contine informatii despre numele si prenumele studentului, anul de studiu, localitatea, sexul si media. Fiecare student apartine unei singure facultati.
Tabela Camine contine informatii despre numele caminului si se specifica daca acel camin are baie in camera. O facultate ofera mai multe camine. Tabela Administrator contine informatii despre numele si prenumele administratorilor caminelor oferite de universitate, precum si, salariul si numarul de telefon al fiecaruia. Tabela Repartizari contine informatii despre numele caminului si numarul de locuri pe care il ofera fiecare facultate a unei universitati. Tabela Camera contine informatii despre studentii care au primit camera in camin, ce fel de camera este si cate locuri au mai ramas libere in camera respectiva.
Schema tabelei UNIVERSITATE Cod_universitate
Nume
Adresa
Telefon
FACULTATE Cod_faculta te
Cod_universit ate
Nume
Oras
Numar_stud enti
Locuri_cami n
STUDENT Cod_stud ent
Cod_universi tate
Cod_facult ate
Num e
Prenu me
An_stu diu
Localit ate
Se x
Medi a
REPARTIZARI
Cod_repartiz are
Cod_universit ate
Cod_facult ate
Cod_cami n
Nume_cam in
Numar_loc uri
CAMINE Cod_camin
Nume
Baie_in_camera
ADMINISTRATOR Cod_administr ator
Cod_camin
Nume
Prenume
Telefon
Salariu
CAMERA Cod_camera
Cod_camin
Cod_student
Tip_camera
Locuri_libere
Schema conceptuala a bazei de date
B.Interacțiunea cu serverul Oracle prin intermediul comenzilor SQL Blocuri anonime 1. Sa se afiseze facultatea studentului cu codul 5009, precum si camera in care a fost repartizat folosind un bloc anonim, iar daca nu exista studentul cu acest cod sa se trateze ca si exceptie. set serveroutput on DECLARE v_nume_facultate facultate.Nume%TYPE; v_cod_student student.Cod_student%TYPE; v_camera camera.cod_camera%TYPE; BEGIN SELECT f.nume,s.cod_student INTO v_nume_facultate,v_cod_student FROM Facultate f,Student s where s.cod_student=5009 and f.cod_facultate=s.cod_facultate; SELECT Cod_camera INTO v_camera FROM Camera WHERE Cod_student=5009; DBMS_OUTPUT.PUT_LINE('Studentul al carui cod este '||v_cod_student|| ' invata in facultatea '||v_nume_facultate||' si a fost repartizat in camera cu codul '||v_camera|| '.'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Nu exista studentul cu codul 50009'); END; / //Facultatea CSIE ofera in caminul Belvedere A6 56 locuri pentru studenti.
2. Sa se afiseze numele administratorului caminului Moxa, precum si adresa acestuia stocat intr-o variabila de mediu. Adresa este 'Str. Mihail Moxa nr. 11'. set serveroutput on VARIABLE g_adresa varchar2(50)
DECLARE v_nume_adm Administrator.Nume%TYPE; v_prenume_adm Administrator.Prenume%TYPE; BEGIN SELECT Nume,Prenume INTO v_nume_adm,v_prenume_adm FROM Administrator WHERE Cod_camin=(SELECT Cod_camin FROM Camine WHERE Nume='Moxa D'); :g_adresa:='Str. Mihail Moxa nr. 11'; DBMS_OUTPUT.PUT_LINE('Administratorul din caminul Moxa D se numeste: '|| v_nume_adm||' '||v_prenume_adm); DBMS_OUTPUT.PUT_LINE('Adresa caminului este: '); END; / PRINT g_adresa //Administratorul din caminul Moxa D se numeste: Stanciu Ionut Adresa caminului este: G_ADRESA ----------------------Str. Mihail Moxa nr. 11
3. Sa se afiseze Facilitatile caminului Belvedere A6 intr-o variabila de mediu, facilitatile sunt 'Sală de lectură și oficiu (bucătărie) la fiecare etaj', numele administratorului si salariul acestuia si sa se introduca de la tastatura, adresa acestui camin si sa se afiseze pe ecran. Str. Chibzuinței nr. 2 set serveroutput on VARIABLE g_facilitati varchar ACCEPT adresa PROMPT 'Introduceti adresa caminului Belvedere A6' DECLARE v_nume_adm Administrator.Nume%TYPE; v_prenume_adm Administrator.Prenume%TYPE; v_salariu Administrator.Salariu%TYPE; adresa varchar2(50); BEGIN
SELECT Nume,Prenume,Salariu INTO v_nume_adm,v_prenume_adm,v_salariu FROM Administrator WHERE Cod_camin=(SELECT Cod_camin FROM Camine WHERE Nume='Belvedere A6'); DBMS_OUTPUT.PUT_LINE('Numele administratorului din Belvedere A6 este: '|| v_nume_adm||' '||v_prenume_adm|| ' si are un salariu de '||v_salariu); adresa:='&adresa'; DBMS_OUTPUT.PUT_LINE('Adresa caminului Belvedere A6 este: '||adresa); :g_facilitati:='Sala de lectură si oficiu (bucătărie) la fiecare etaj'; DBMS_OUTPUT.PUT_LINE('Caminul are urmatoarele facilitati: '); END; /
PRINT g_facilitati Numele administratorului din Belvedere A6 este: Popovici Cristian Ionel si are un salariu de 2500 Adresa caminului Belvedere A6 este: str Chibzuintei nr2 Caminul are urmatoarele facilitati: G_FACILITATI ----------------------------------------------------Sala de lectura si oficiu (bucatarie) la fiecare etaj
4. Sa se afiseze numele studentului al carei cod al facultatii e introdus de la tastatura, si nr de locuri ocupate din camera de camin in care a fost repartizat. set serveroutput on ACCEPT Cod_stud PROMPT 'Introduceti codul studentului' DECLARE v_nume Student.Nume%TYPE; v_prenume Student.Prenume%TYPE; v_locuri_ocupate number(1); Cod_stud Student.Cod_student%TYPE; BEGIN
SELECT Nume,Prenume INTO v_nume,v_prenume FROM Student WHERE Cod_student=&Cod_stud; DBMS_OUTPUT.PUT_LINE('Numele studentului care are codul ' ||&Cod_stud|| ' se numeste' ||v_nume|| ' '||v_prenume); SELECT Tip_camera-Locuri_libere INTO v_locuri_ocupate FROM Camera WHERE Cod_student=&Cod_stud; DBMS_OUTPUT.PUT_LINE('Camera in care sta studentul cu codul '||&Cod_stud|| ' are '|| v_locuri_ocupate|| ' locuri ocupate'); end; / Numele studentului care are codul 5009 se numeste MOLOFEI CONSTANTIN Camera in care sta studentul cu codul 5009 are 3 locuri ocupate
5. Sa se mareasca salariul administratorului caminului Tei C1 cu un procent dat de la tastatura. set serveroutput on VARIABLE g_salariu_marit number ACCEPT p_procent PROMPT 'Introduceti procentul' DECLARE p_procent number(3,2); v_salariu Administrator.Salariu%TYPE; BEGIN SELECT salariu INTO v_salariu FROM Administrator WHERE Cod_camin=(SELECT Cod_camin from Camine WHERE nume='Tei C1'); DBMS_OUTPUT.PUT_LINE('Salariul administratorului caminului Tei C1 este ' ||v_salariu); p_procent:=&p_procent; DBMS_OUTPUT.PUT_LINE('Salariul administratorului caminului Tei C1 s-a marit cu procentul '||p_procent*100|| '%'); :g_salariu_marit:=v_salariu+v_salariu*p_procent; DBMS_OUTPUT.PUT_LINE('Noul salariu al administratorului caminului Tei C1 este: '); END; / PRINT g_salariu_marit Salariul administratorului caminului Tei C1 este 2800
Salariul administratorului caminului Tei C1 s-a marit cu procentul 20% Noul salariu al administratorului caminului Tei C1 este: G_SALARIU_MARIT ---3360
6. Sa se creeze tabela Adrese pt facultati si sa se insereze adresa 'Bulevardul Dacia'in cazul in care codul maxim al facultatilor este 110, afisandu-se numele facultatii cu codul maxim. set serveroutput on BEGIN EXECUTE IMMEDIATE 'CREATE TABLE Adrese(Cod_facultate number(3), CONSTRAINT FK_Cod_facultate_A FOREIGN KEY(Cod_facultate) REFERENCES Facultate(Cod_facultate), Adresa varchar2(70))'; END; / set serveroutput on DECLARE v_cod Facultate.Cod_facultate%TYPE; v_nume Facultate.Nume%TYPE; v_adresa varchar2(50); BEGIN select max(Cod_facultate) INTO v_cod FROM Facultate; select Nume INTO v_nume FROM Facultate WHERE Cod_facultate=v_cod; DBMS_OUTPUT.PUT_LINE(v_cod||v_nume); IF v_cod=110 THEN v_adresa:='Bulevardul Dacia,nr 41'; DBMS_OUTPUT.PUT_LINE('Adresa facultatii ' ||v_nume||' este '||v_adresa); INSERT INTO Adrese VALUES(v_cod,v_adresa); ELSE DBMS_OUTPUT.PUT_LINE('Informatiile nu corespund'); END IF;
END; / 110 Relatii economice internationale Adresa facultatii Relatii economice internationale este Bulevardul Dacia,nr 41
7 . Stergeti tabela Adrese si creati din nou tabela Adresa_facultate care sa contina coloana Cod_facultate din tabela Facultate si sa se adauge ulterior coloana Adresa si sa se insereze in coloana Adresa valoarea Calea Serban Voda, nr22-24 pt codul 101. set serveroutput on BEGIN EXECUTE IMMEDIATE 'DROP TABLE Adrese'; EXECUTE IMMEDIATE 'CREATE TABLE Adresa_facultate AS SELECT Cod_facultate FROM Facultate'; EXECUTE IMMEDIATE 'ALTER TABLE Adresa_facultate ADD Adresa varchar2(50)'; END; / select * from Adresa_facultate; set serveroutput on BEGIN UPDATE Adresa_facultate SET Adresa='Calea Serban Voda 22-24' WHERE Cod_facultate=101; END; / 8. Sa se modifice salariul administratorilor astfel, pt caminul Belvedere A1 cu un procent dat de la tastatura,Belvedere A6 cu un procent mai mare cu 10% fata de primul introdus de la tastatura, iar pt P6 cu 30%. set serveroutput on ACCEPT p_proc_A1 PROMPT 'Procent pentru Belvedere A1' DECLARE p_proc_A1 number(3,2); v_salariu_A1 Administrator.Salariu%TYPE; v_salariu_A6 Administrator.Salariu%TYPE; v_salariu_P6 Administrator.Salariu%TYPE;
p_proc_A6 number(3,2); BEGIN SELECT Salariu INTO v_salariu_A1 FROM Administrator WHERE Cod_camin=(SELECT Cod_camin FROM Camine WHERE nume='Belvedere A1'); DBMS_OUTPUT.PUT_LINE('Administratorul caminului Belvedere A1 avea un salariu de '|| v_salariu_A1); p_proc_A1:=&p_proc_A1; v_salariu_A1:=v_salariu_A1+v_salariu_A1*p_proc_A1; DBMS_OUTPUT.PUT_LINE('Salariul administratorului caminului Belvedere A1 s-a modificat cu procentul '||p_proc_A1*100||'%.'||' Salariul marit este ' ||v_salariu_A1); SELECT Salariu INTO v_salariu_A6 FROM Administrator WHERE Cod_camin=(SELECT Cod_camin FROM Camine WHERE nume='Belvedere A6'); DBMS_OUTPUT.PUT_line('Administratorul caminului Belvedere A6 avea un salariu de '|| v_salariu_A6); p_proc_A6:=p_proc_A1+0.1; v_salariu_A6:=v_salariu_A6+v_salariu_A6*p_proc_A6; DBMS_OUTPUT.PUT_LINE('Salariul administratorului caminului Belvedere A6 s-a modificat cu procentul '||p_proc_A6*100|| ' Salariul marit este: '||v_salariu_A6); SELECT Salariu INTO v_salariu_P6 FROM Administrator WHERE Cod_camin=(SELECT Cod_camin FROM Camine WHERE nume='P6'); v_salariu_P6:=v_salariu_P6+v_salariu_P6*0.3; DBMS_OUTPUT.PUT_LINE(v_salariu_P6); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Caminul P6 nu e inregistrat in baza de date'); END; / Administratorul caminului Belvedere A1 avea un salariu de 2000 Salariul administratorului caminului Belvedere A1 s-a modificat cu procentul 20%. Salariul marit este 2400 Administratorul caminului Belvedere A6 avea un salariu de 2500 Salariul administratorului caminului Belvedere A6 s-a modificat cu procentul 30 Salariul marit este: 3250
Caminul P6 nu e inregistrat in baza de date
Variabile RECORD 9. Folosind un tip de data record de acelasi tip cu tabela camine, sa se afiseze numele caminului cu codul 2000. set serveroutput on DECLARE v_Camine Camine%ROWTYPE; BEGIN SELECT * INTO v_Camine from Camine WHERE cod_camin=2000; DBMS_OUTPUT.PUT_LINE('Caminul cu codul '||v_Camine.Cod_camin||' se numeste '|| v_camine.Nume||'. Baie in camera: '||v_camine.Baie_in_camera); end; / Caminul cu codul 2000 se numeste Belvedere A1. Baie in camera: NU
10. Sa se afiseze camerele ale caror coduri sunt cuprinse intre 700 si 800, folosind o variabila de tip RECORD si care mai mult de un loc liber. set serveroutput on DECLARE vrec_camera camera%ROWTYPE; BEGIN FOR i IN 700..800 LOOP SELECT * INTO vrec_camera FROM Camera WHERE cod_camera=i; IF vrec_camera.locuri_libere>=2 THEN DBMS_OUTPUT.PUT_LINE('Camera cu codul '||vrec_camera.cod_camera|| ' este o camera de '||vrec_camera.tip_camera||' persoane si mai are '||vrec_camera.locuri_libere||' locuri libere'); END IF; END LOOP; END; / Camera cu codul 701 este o camera de 3 persoane si mai are 2 locuri libere Camera cu codul 703 este o camera de 3 persoane si mai are 2 locuri libere
Camera cu codul 705 este o camera de 4 persoane si mai are 3 locuri libere
C. Structuri alternativa si repetitive 1. Sa se verifice daca codul facultatii cibernetica respectiv marketing este 109, respectiv 108, iar daca este adevarat, sa se afiseze procentul studentilor care ocupa locuri in camine. set serveroutput on DECLARE v_cod_C Facultate.Cod_facultate%TYPE; v_cod_M Facultate.Cod_facultate%TYPE; v_proc1 number(4,2); v_proc2 number(4,2); BEGIN SELECT Cod_facultate,Locuri_camin/Numar_studenti INTO v_cod_C,v_proc1 FROM Facultate WHERE nume LIKE '_Cibernetica%'; SELECT Cod_facultate,Locuri_camin/Numar_studenti INTO v_cod_M,v_proc2 FROM Facultate WHERE nume LIKE 'Mark%'; IF v_cod_C=109 THEN v_proc1:=v_proc1*100; DBMS_OUTPUT.PUT_LINE('Procentul studentilor din facultatea Cibernetica care ocupa locuri in camine este de '||v_proc1||'%'); ELSE DBMS_OUTPUT.PUT_LINE('Codul pt Cibernetica nu corespunde'); END IF; IF v_cod_M=108 THEN v_proc2:=v_proc2*100; DBMS_OUTPUT.PUT_LINE('Procentul studentilor din facultatea Marketing care ocupa locuri in camine este de '||v_proc2||'%'); ELSE
DBMS_OUTPUT.PUT_LINE('Codul pt Marketing nu corespunde'); END IF; END; /
Procentul studentilor din facultatea Cibernetica care ocupa locuri in camine este de 17% Procentul studentilor din facultatea Marketing care ocupa locuri in camine este de 12%
2. Daca nr de locuri oferite de facultatea Cibernetica este mai mic decat media tuturor locurilor oferite de ASE in camine atunci sa se adauge inca 10%, iar daca este mai mare decat media, atunci sa se adauge 5%. set serveroutput on DECLARE v_locuri_camin Facultate.Locuri_camin%TYPE; v_medie number(5,2); v_locuri_actualizat number(4); BEGIN SELECT avg(Locuri_camin) INTO v_medie FROM Facultate WHERE nume LIKE '_Cib%'; SELECT Locuri_camin INTO v_locuri_camin FROM Facultate WHERE nume LIKE '_Cib%'; IF v_locuri_camin
3. Sa se mareasca Locurile in camin pt o anumita facultate al carei cod e introdus de la tastatura, astfel, daca nr_studenti intre 1000-2000 sa se mareasca locurile cu 20, daca e intre 2000-3000 cu 50, daca este mai mare de 3000 cu 100.
set serveroutput on ACCEPT v_cod PROMPT 'Introduceti codul facultatii' DECLARE v_cod Facultate.Cod_facultate%TYPE; v_nr_stud Facultate.Numar_studenti%TYPE; v_locuri Facultate.Locuri_camin%TYPE; v_locuri_camin number(5); BEGIN v_cod:=&v_cod; SELECT Numar_studenti INTO v_nr_stud FROM Facultate WHERE Cod_facultate=v_cod; v_locuri:= CASE WHEN v_nr_stud between 1000 and 2000 THEN 10 WHEN v_nr_stud between 2000 and 3000 THEN 50 WHEN v_nr_stud>3000 THEN 100 END; SELECT Locuri_camin INTO v_locuri_camin FROM Facultate WHERE Cod_facultate=v_cod; v_locuri_camin:=v_locuri_camin+v_locuri; DBMS_OUTPUT.PUT_LINE('Locurile au fost suplimentate cu '||v_locuri||'. Facultatea cu codul '||v_cod|| ' va avea '||v_locuri_camin||' locuri pentru studenti in caminele ASE'); END; / Locurile au fost suplimentate cu 100. Facultatea cu codul 103 va avea 591
locuri pentru studenti in caminele ASE 4. Sa se afiseze facultatile care au codurile cuprinse intre 105-110 atata timp cat numar_studenti media se iese din bucla. set serveroutput on DECLARE v_medie number(5,1);
v_nume Facultate.Nume%TYPE; i number(3):=104; v_nr_stud Facultate.Numar_studenti%TYPE; BEGIN SELECT AVG(Numar_studenti) INTO v_medie FROM Facultate; loop select Nume into v_nume from Facultate where cod_facultate=i; select numar_studenti into v_nr_stud FROM Facultate WHERE Cod_facultate=i; exit when i>110 or v_nr_stud>v_medie; dbms_output.put_line('Facultatea cu codul '||i|| ' se numeste '||v_nume); i:=i+1; end loop; END; / Facultatea cu codul 104 se numeste Economie Agroalimentara si a Mediului Facultatea cu codul 105 se numeste Economie Teoretica si Aplicata
5. Sa se afiseze numele primilor studenti cu codurile cuprinse 5000 si studentul cu codul 5023, care au media >8. while conditie... loop ...end loop. set serveroutput on DECLARE i number(4):=5000; v_nume Student.Nume%TYPE; v_medie Student.Media%TYPE; BEGIN WHILE i<=5023 loop SELECT media,nume INTO v_medie,v_nume FROM Student WHERE Cod_student=i; EXIT WHEN v_medie<8; DBMS_OUTPUT.PUT_LINE('Studentul care are codul '||i||' cu media '||v_medie|| 'se numeste '||v_nume);
i:=i+1; END LOOP; END; / Studentul care are codul 5000 cu media 9,87se numeste MUTICA Studentul care are codul 5001 cu media 8,53se numeste VRINCEANU Studentul care are codul 5002 cu media 9,9se numeste SERBAN Studentul care are codul 5003 cu media 9,13se numeste STANCIU
6. Sa se afiseze primii studentii care au media <9 care au codurile cuprinse intre 5005015, cand s-a gasit primul student care nu indeplineste conditia sa se iasa din bucla. For... LOOP...END LOOP.. set serveroutput on ACCEPT v_inf PROMPT 'Introduceti limita inferioara' ACCEPT v_sup PROMPT 'Introduceti limita superioara' DECLARE v_inf number(4):=&v_inf; v_sup number(4):=&v_sup; v_nume Student.Nume%TYPE; v_medie Student.Media%TYPE; BEGIN FOR i IN v_inf..v_sup loop SELECT Nume INTO v_nume FROM Student WHERE Cod_student=i; SELECT Media INTO v_medie FROM Student WHERE Cod_student=i; EXIT WHEN v_medie>9; DBMS_OUTPUT.PUT_LINE('Studentul cu codul '||i||' are media '||v_medie||' si se numeste '|| v_nume); end loop; end; / Studentul cu codul 5009 are media 6,82 si se numeste MOLOFEI Studentul cu codul 5010 are media 5,93 si se numeste CODREANU
Studentul cu codul 5011 are media 5,5 si se numeste BALAN
7. Sa se afiseze studentii care au media <9 care au codurile cuprinse intre 500-5015 si sa nu se intrerupa acel loop cand s-a gasit primul student care are media >9. set serveroutput on ACCEPT v_inf PROMPT 'Introduceti limita inferioara' ACCEPT v_sup PROMPT 'Introduceti limita superioara' DECLARE v_inf number(4):=&v_inf; v_sup number(4):=&v_sup; v_nume Student.Nume%TYPE; v_medie Student.Media%TYPE; BEGIN FOR i IN v_inf..v_sup loop SELECT Nume INTO v_nume FROM Student WHERE Cod_student=i; SELECT Media INTO v_medie FROM Student WHERE Cod_student=i; IF v_medie<9 THEN DBMS_OUTPUT.PUT_LINE('Studentul cu codul '||i||' are media '||v_medie||' si se numeste '|| v_nume); END IF; end loop; end; / Studentul cu codul 5009 are media 6,82 si se numeste MOLOFEI Studentul cu codul 5010 are media 5,93 si se numeste CODREANU Studentul cu codul 5011 are media 5,5 si se numeste BALAN Studentul cu codul 5014 are media 8,2 si se numeste SORESCU Studentul cu codul 5019 are media 8,63 si se numeste COTRUTA Studentul cu codul 5020 are media 8,37 si se numeste DANCIU
D. Tratarea excepțiilor 1. Sa se introduca in tabela Universitate VALUES (1000,'Politehnica')
INSERT INTO Universitate(Cod_universitate,Nume) VALUES (1000,'Politehnica');
Sa se creeze o tabela unde sa se memoreze utilizatorul, codul de eroare si mesajul corespunzator. CREATE TABLE erori (utilizator VARCHAR2(40), data DATE, cod_eroare NUMBER(10), mesaj_eroare VARCHAR2(255) ); set serveroutput on DECLARE cod NUMBER; mesaj VARCHAR2(255); except_1 EXCEPTION; PRAGMA EXCEPTION_INIT(except_1, -0001); BEGIN INSERT INTO Universitate(Cod_universitate,Nume) values(1000,'Politehnica'); EXCEPTION WHEN except_1 THEN dbms_output.put_line('Codul pe care vreti sa-l inserati exista deja pt o alta Universitate'); cod:=SQLCODE; mesaj:=SQLERRM; INSERT INTO erori VALUES(USER, SYSDATE, cod, mesaj); end;/
2. Sa se afiseze studentul de la facultatea cu codul 109.
SET SERVEROUTPUT ON DECLARE v_nume student.nume%TYPE; BEGIN select nume into v_nume from student where cod_facultate=109; DBMS_OUTPUT.PUT_LINE('Studentul se numeste: '||v_nume); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Exista mai multi studenti cu acest cod'); END; / 3. Afisati numele studentului cu codul 5000. set serveroutput on declare v_nume student.cod_student%TYPE; begin select nume into v_nume from student where cod_student=5000; exception when value_error then dbms_output.put_line('Ai declarat gresit tipul de data pt nume si nu se poate face conversie'); end; / 4. Sa se modifice numele studentului care are codul 12. set serveroutput on DECLARE invalid_cod EXCEPTION; BEGIN UPDATE student SET nume='Ionescu' WHERE cod_student=12;
IF SQL%NOTFOUND THEN RAISE invalid_cod; END IF; EXCEPTION WHEN invalid_cod THEN DBMS_OUTPUT.PUT_LINE('Nu exista studentul cu acest cod'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('A aparut o eroare! Nu se poate actualiza numele studentului!'); END; /
E.Gestionarea cursorilor: impliciți și expliciți (cu şi fără parametri) 1. Sa se stearga din tabela Adresa_facultate toate randurile care au codurile cuprinse intre 105-110 si sa se contorizeze nr de randuri sterse. set serveroutput on DECLARE --declaram o variabila care va tine minte cate randuri au fost sterse v_nr number(2); v_contor number(2); BEGIN v_contor:=0; FOR i IN 105..110 LOOP DELETE FROM Adresa_facultate WHERE Cod_facultate=i; v_nr:=SQL%ROWCOUNT; v_contor:=v_contor+v_nr; END LOOP; DBMS_OUTPUT.PUT_LINE('S-au sters '||v_contor||' randuri'); END; / S-au sters 6 randuri.
2. Sa se modifice telefonul universitatii cu codul 1001. set serveroutput on BEGIN UPDATE Universitate SET Telefon='0770563389' WHERE Cod_universitate=1001; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Nu exista nici o universitate cu acest cod'); END IF; END; / Nu exista nici o universitate cu acest cod
3. Sa se creeze tabela Facultate_duplicat pe baza tabelei Facultate, folosind o variabila record sa se afiseze primele numele si codurile primelor 3 facultati, iar apoi sa se stearga inregistrarea corespunzatoare codului facultatii introdus de la tastatura. Sa se stearga si urmatoarele 2 inregistrari si sa se contorizeze numarul inregistrarilor sterse. set serveroutput on BEGIN EXECUTE IMMEDIATE 'CREATE TABLE Facultate_duplicat AS SELECT * FROM Facultate'; END; / select * from facultate_duplicat; set serveroutput on ACCEPT v_cod PROMPT 'Introduceti codul facultatii' DECLARE --variabila record de tipul tabelei Facultate_duplicat vrec_facultate Facultate_duplicat%ROWTYPE; v_cod number(3):=&v_cod; v_sters number(2); v_contor number(2):=0; BEGIN FOR i IN 101..103 LOOP SELECT * INTO vrec_facultate FROM Facultate_duplicat WHERE Cod_facultate=i;
DBMS_OUTPUT.PUT_LINE(vrec_facultate.Cod_facultate||' '||vrec_facultate.Nume); END LOOP; DELETE FROM Facultate_duplicat WHERE Cod_facultate=v_cod; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('S-a sters inregistrarea corespunzatoare codului '||v_cod); v_contor:=v_contor+1; ELSE DBMS_OUTPUT.PUT_LINE('Codul introdus nu este eligibil'); END IF; FOR i IN v_cod+1..v_cod+2 LOOP DELETE FROM Facultate_duplicat WHERE Cod_facultate=i; v_sters:=SQL%ROWCOUNT; v_contor:=v_contor+v_sters; END LOOP; DBMS_OUTPUT.PUT_LINE('S-au sters '||v_contor||' randuri din tabela Facultate_duplicat'); END; / 101 Administratie Publica si Management 102 Business si Turism 103 Contabilitate si Informatica de Gestiune S-a sters inregistrarea corespunzatoare codului 102 S-au sters 3 randuri din tabela Facultate_duplicat
4. Sa se afiseze lista caminelor oferite de facultatea care are codul 109 folosind un cursor explicit. set serveroutput on DECLARE --declaram cursorul si mentionam ce sa selecteze CURSOR c1_cursor is select nume_camin FROM Repartizari WHERE Cod_facultate=109; --declaram fiecare coloana pe care o selecteaza cursorul c1_nume Repartizari.Nume_camin%TYPE;
BEGIN DBMS_OUTPUT.PUT_LINE('Caminele oferite de facultatea cu codul 109 sunt:'); --deschidem cursorul open c1_cursor; loop --se incarca linia curenta in cursor fetch c1_cursor INTO c1_nume; EXIT WHEN c1_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Facultatea cu codul 109 ofera caminul'||c1_nume); END LOOP; close c1_cursor; END; / Caminele oferite de facultatea cu codul 109 sunt: Facultatea cu codul 109 ofera caminulBelvedere A1 Facultatea cu codul 109 ofera caminulBelvedere A2 Facultatea cu codul 109 ofera caminulBelvedere A3 Facultatea cu codul 109 ofera caminulBelvedere A4 Facultatea cu codul 109 ofera caminulBelvedere A6 Facultatea cu codul 109 ofera caminulBelvedere A7 Facultatea cu codul 109 ofera caminulBelvedere A8 Facultatea cu codul 109 ofera caminulC1 Agronomie Facultatea cu codul 109 ofera caminulC2 Agronomie Facultatea cu codul 109 ofera caminulMoxa Facultatea cu codul 109 ofera caminulTei C1
5. Sa se afiseze studentii de la facultatea Cibernetica folosind un cursor explicit. set serveroutput on DECLARE --declaram cursorul care ne selecteaza numele si prenumele studentilor de la facultatea Cibernetica CURSOR stud_cursor is SELECT Nume, Prenume FROM Student WHERE Cod_facultate=(SELECT Cod_facultate FROM Facultate
WHERE Nume LIKE '_Cib%'); --declaram coloanele care sunt de tipul coloanelor tabelei Studenti stud_nume Student.Nume%TYPE; stud_prenume Student.Prenume%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Studentii de la Cibernetica,Statistica si Informatica economica sunt :'); --deschidem cursorul open stud_cursor; LOOP --punem fiecare linie in cursor FETCH stud_cursor INTO stud_nume,stud_prenume; EXIT WHEN stud_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(stud_nume||' '||stud_prenume); END LOOP; END; / Studentii de la Cibernetica,Statistica si Informatica economica sunt : DIMA ANDREEA STANCIU LUIZA ELENA BOTOFEI MIHAI VICTOR BODNARESCU CIPRIAN COTRUTA ADELINA DANCIU SIMONA ENACHE FLORENTINA DANIELA GODEA TEODORA Sandu Mihaela
6. Afisati numele, prenumele si media studentilor din facultatea al carei cod e introdus de la tastatura. set serveroutput on ACCEPT v_cod PROMPT 'Introduceti codul facultatii'
DECLARE v_cod Facultate.Cod_facultate%TYPE; --declaram cursorul CURSOR stud_c1 is select nume,prenume, media FROM Student WHERE Cod_facultate=&v_cod; --folosim o variabila record pt incarcarea valorilor cursorului stud_info stud_c1%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Studentii de la facultatea cu codul '||&v_cod); --deschidem cursorul open stud_c1; LOOP --punem linia curenta in cursor FETCH stud_c1 INTO stud_info; EXIT WHEN stud_c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(stud_info.nume||' '||stud_info.prenume||' '||stud_info.media); END LOOP; close stud_c1; END; / Studentii de la facultatea cu codul 101 STANCIU IONUT MARIUS 9,13 DOBRE ANDREI CATALIN 5,47 OITA DIANA 6,37
7. Sa se afiseze primii 4 studenti care au media cea mai mare. set serveroutput on DECLARE --declaram un cursor care sa contina numele, prenumele si media studentilor in ordinea descrescatoare a mediei CURSOR stud_top is select nume,prenume,media FROM Student order by media desc; --ne folosim de o variabila record in care sa retina informatia
stud_info stud_top%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('Cei mai buni 4 studenti: '); --daca nu a fost deschis cursorul, il deschidem folosind atributul isopen if not stud_top%ISOPEN THEN open stud_top; end if; loop fetch stud_top INTO stud_info; EXIT WHEN stud_top%NOTFOUND OR stud_top%ROWCOUNT>4; DBMS_OUTPUT.PUT_LINE(stud_info.nume||' '||stud_info.prenume||' '||stud_info.media); END LOOP; close stud_top; END; / Cei mai buni 4 studenti: DIMA ANDREEA 9,93 SERBAN RODICA-IOANA 9,9 MUTICA LAURA- ANDREEA 9,87 STANCIU LUIZA ELENA 9,87
//o alta modalitate este folosind FOR set serveroutput on DECLARE --declaram un cursor care sa contina numele, prenumele si media studentilor in ordinea descrescatoare a mediei CURSOR stud_top is select nume,prenume,media FROM Student order by media desc; BEGIN DBMS_OUTPUT.PUT_LINE('Cei mai buni 4 studenti: '); --nu mai e nevoie de deschidem cursorul pt ca se va deschide automat in clauza for for stud_info in stud_top loop
--se declara automat o variabila record stud_info de tipul cursorului EXIT WHEN stud_top%NOTFOUND OR stud_top%ROWCOUNT>4; DBMS_OUTPUT.PUT_LINE(stud_info.nume||' '||stud_info.prenume||' '|| stud_info.media); END LOOP; END; / 8. Sa se afiseze numarul de locuri oferit de fiecare facultate. --folosim un cursor pe care nu-l mai declaram set serveroutput on BEGIN DBMS_OUTPUT.PUT_LINE('Total nr locuri in camin oferit de fiecare facultate'); --nu mai declaram cursorul pt ca el este reprezentat de interogarea SELECT FOR total_rec IN (SELECT cod_facultate ID_facultate, SUM(numar_locuri) Total FROM Repartizari group by cod_facultate order by cod_facultate ASC) LOOP DBMS_OUTPUT.PUT_LINE('Facultatea cu ID-ul '||total_rec.ID_facultate ||' ofera '|| total_rec.Total|| ' locuri in camin'); END LOOP; END; /
Total nr locuri in camin oferit de fiecare facultate Facultatea cu ID-ul 101 ofera 240 locuri in camin Facultatea cu ID-ul 102 ofera 421 locuri in camin Facultatea cu ID-ul 103 ofera 491 locuri in camin Facultatea cu ID-ul 104 ofera 304 locuri in camin Facultatea cu ID-ul 105 ofera 284 locuri in camin Facultatea cu ID-ul 106 ofera 467 locuri in camin Facultatea cu ID-ul 107 ofera 420 locuri in camin Facultatea cu ID-ul 108 ofera 387 locuri in camin Facultatea cu ID-ul 109 ofera 500 locuri in camin
Facultatea cu ID-ul 110 ofera 509 locuri in camin
9. Sa se afiseze media studentilor inregistrati per facultate doar daca aceasta medie e mai mare decat o valoare primita ca parametru 7. set serveroutput on --declaram cursorul care va contine numele si prenumele lui, numele facultatii de la care provine si media si are -- ca si parametru p_medie DECLARE CURSOR c1(p_medie number) is select avg(media) Medie,Cod_facultate Cod FROM Student group by cod_facultate HAVING avg(media)>p_medie order by cod_facultate ASC; --declaram o variabila care sa retina valoarea pe care o dam ca parametru v_medie number(1); --ne folosim de o variabila record in care sa punem informatiile retinute de cursor v_rec c1%ROWTYPE; BEGIN --initializam variabila care retine valoarea parametrului v_medie:=7; DBMS_OUTPUT.PUT_LINE('Urmatoarele facultati au media pe facultate mai mare decat '|| v_medie); open c1(v_medie); LOOP FETCH c1 INTO v_rec; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Facultatea care are codul '||v_rec.Cod||' are media pe facultate de '||v_rec.Medie); END LOOP; close c1; END; / Urmatoarele facultati au media pe facultate mai mare decat 7 Facultatea care are codul 102 are media pe facultate de 9,285
Facultatea care are codul 103 are media pe facultate de 8,21 Facultatea care are codul 106 are media pe facultate de 9,77 Facultatea care are codul 107 are media pe facultate de 9,03 Facultatea care are codul 108 are media pe facultate de 8,2 Facultatea care are codul 109 are media pe facultate de 8,90 Facultatea care are codul 110 are media pe facultate de 9,43
10. Sa se adauge in tabela Adresa_facultate o coloana numita Sector si sa se completeze cu valoarea 2 daca facultatea are codul mai mic de 105 si cu 3 in rest, dupa care sa se afiseze sectorul pt fiecare cod. set serveroutput on BEGIN EXECUTE IMMEDIATE 'ALTER TABLE Adresa_facultate ADD (Sector number(1))'; END; / set serveroutput on DECLARE CURSOR c1 is select cod_facultate,adresa,Sector FROM Adresa_facultate FOR UPDATE OF Sector NOWAIT; BEGIN --il folosim pe FOR ca sa se creeze automat o variabila de tip record in care sa punem informatiile retinute de cursor FOR vrec_c1 IN c1 LOOP UPDATE Adresa_facultate SET Sector=2 WHERE Cod_facultate<105; UPDATE Adresa_facultate SET Sector=3 WHERE Cod_facultate>104; end loop; for vrec_c1 in c1 loop DBMS_OUTPUT.PUT_LINE('Facultatea cu codul '||vrec_c1.cod_facultate|| ' se afla in sectorul '||vrec_c1.sector); END LOOP; end; / Facultatea cu codul 101 se afla in sectorul 2
Facultatea cu codul 102 se afla in sectorul 2 Facultatea cu codul 103 se afla in sectorul 2 Facultatea cu codul 104 se afla in sectorul 2 Facultatea cu codul 105 se afla in sectorul 3 Facultatea cu codul 106 se afla in sectorul 3 Facultatea cu codul 107 se afla in sectorul 3 Facultatea cu codul 108 se afla in sectorul 3 Facultatea cu codul 109 se afla in sectorul 3 Facultatea cu codul 110 se afla in sectorul 3
F. Funcții, proceduri,pachete Proceduri Parametri IN 1. Sa se creeze procedura modifica_salariu care sa majoreze salariilor administratorilor si primeste ca si parametri codul si procentul. --valoarea variabilei actuale e transferata in variabila definita de procedura --parametrul IN apare mereu in partea dreapta --parametrul nu se modifica, e implicit set serveroutput on
CREATE OR REPLACE PROCEDURE modifica_salariul(p_cod IN Administrator.Cod_administrator%TYPE , p_proc IN number) IS v_salariu Administrator.Salariu%TYPE; BEGIN SELECT salariu INTO v_salariu FROM Administrator WHERE cod_administrator=p_cod; DBMS_OUTPUT.PUT_LINE('Administratorul avea un salariu de '||v_salariu); UPDATE Administrator SET Salariu=salariu*(1+p_proc/100) WHERE cod_administrator=p_cod; SELECT salariu into v_salariu FROM Administrator WHERE cod_administrator=p_cod; DBMS_OUTPUT.PUT_LINE('Administratorul are acum un salaru de '||v_salariu); END; /
EXECUTE modifica_salariul(402, 50) Administratorul avea un salariu de 2000 Administratorul are acum un salaru de 3000
2. Sa se creeze o procedura care primeste ca parametru de tip IN codul facultatii si returneaza in parametrii de tip OUT numele facultatii si locuri_camin suplimentate cu 10. CREATE OR REPLACE PROCEDURE modifica_locuri (p_cod_fac IN Facultate.Cod_facultate %TYPE,p_nume OUT Facultate.Nume%TYPE, p_locuri_camin OUT number) IS BEGIN SELECT Locuri_camin,Nume INTO p_locuri_camin,p_nume FROM Facultate WHERE Cod_facultate=p_cod_fac; DBMS_OUTPUT.PUT_LINE('Facultatea se numeste '||p_nume||' si oferea '||p_locuri_camin||' locuri in camin'); UPDATE Facultate SET Locuri_camin=Locuri_camin+10 WHERE Cod_facultate=p_cod_fac; SELECT Locuri_camin INTO p_locuri_camin FROM Facultate WHERE Cod_facultate=p_cod_fac; DBMS_OUTPUT.PUT_LINE('Facultatea se numeste '||p_nume||' si ofera acum '|| p_locuri_camin||' locuri in camin'); END; / SET SERVEROUTPUT ON DECLARE v_nume Facultate.Nume%TYPE; v_locuri_camin Facultate.Locuri_camin%TYPE; BEGIN modifica_locuri(109,v_nume,v_locuri_camin); END; / Facultatea se numeste Cibernetica,Statistica si Informatica economica si oferea 510 locuri in camin Facultatea se numeste Cibernetica,Statistica si Informatica economica si ofera acum 520 locuri in camin
3. Sa se calculze media mediilor studentilor inregistrati folosind procedura get_medie. set serveroutput on create or replace procedure get_medie (p_medie OUT Student.Media%TYPE) IS
BEGIN SELECT avg(media) INTO p_medie FROM Student; DBMS_OUTPUT.PUT_LINE('Media mediilor studentilor inregistrati este '||p_medie); END; / set serveroutput on DECLARE v_medie number(3,2); BEGIN get_medie(v_medie); END; / Media mediilor studentilor inregistrati este 8,45
Functii 4.Sa se creeze o noua coloana pt tabela Student : Tip_student. Sa se creeze o functie care primeste ca parametru codul studentului si media mediilor tuturor studentilor inregistrati , iar daca media lui este mai mare decat media mediilor sa se returneze true, false daca este mai mica si null daca nu s-a gasit codul studentului. CREATE OR REPLACE FUNCTION verifica_stud (p_cod IN student.cod_student%type, p_medie IN number) RETURN BOOLEAN IS v_medie student.media%type; BEGIN SELECT media into v_medie from student where cod_student=p_cod; IF v_medie > p_medie then return true; ELSE return false; end if; exception when no_data_found then
return NULL; end; / --apelam procedura care calculeaza media mediilor tuturor studentilor inregistrati set serveroutput on declare v_medie student.Media%TYPE; begin get_medie(v_medie); --apelam functia verifica_stud pt studentul al carui cod este 5002 IF(verifica_stud(5002,v_medie) IS NULL) THEN DBMS_OUTPUT.PUT_LINE('Nu exista nici un student cu codul 5002'); ELSIF (verifica_stud(5002,v_medie)) THEN DBMS_OUTPUT.PUT_LINE('Studentul are media mai mare decat media tuturor=> este foarte bun'); else DBMS_OUTPUT.PUT_LINE('Studentul are media mai mica decat media tuturor=> este bun'); end if; end; / Media mediilor studentilor inregistrati este 8,45 Studentul are media mai mare decat media tuturor=> este foarte bun 5. Sa se realizeze o functie care afiseaza numarul de studenti care nu prind camin, pt un anumit cod al facultatii introdus ca si parametru. set serveroutput on create or replace function studenti_fara_camin (numar_stud IN number,loc_camin IN number) RETURN number is begin return (numar_stud-loc_camin);
end studenti_fara_camin; / --apelul funcţiei SELECT cod_facultate, numar_studenti, locuri_camin, studenti_fara_camin(numar_studenti, locuri_camin) as fara_camin FROM facultate;
6. Sa se creeze un pachet care contine o procedura modifica_locuri modifica numele facultatii. CREATE or replace package actualizare_pachet is PROCEDURE modifica_facultate (p_cod_fac Facultate.Cod_facultate%TYPE,p_nume Facultate.Nume%TYPE); function exista_cod(p_cod_fac Facultate.cod_facultate%TYPE) return boolean; end; / create or replace package body actualizare_pachet is procedure modifica_facultate (p_cod_fac facultate.cod_facultate%TYPE, p_nume facultate.nume%type) is begin if exista_cod(p_cod_fac) then update facultate set nume=p_nume where cod_facultate=p_cod_fac; dbms_output.put_line('S-a modificat numele facultatii'); else
dbms_output.put_line('Nu exista nici o facultate cu acest nume'); end if; end; function exista_cod(p_cod_fac facultate.Cod_facultate%type) return boolean is v_unu number; begin select 1 into v_unu from facultate where cod_facultate=p_cod_fac; return true; exception when no_data_found then return false; end; END; /
G. Declanșatori la nivel de instrucțiune și de rând 1. Sa se creeze un trigger care se declanseaza inainte de fiecare stergere din tabela adresa_facultate; la nivel de instructiune CREATE OR REPLACE TRIGGER adresa_trigger BEFORE delete on adresa_facultate BEGIN dbms_output.put_line('triggerul s-a executat'); END;
/ delete from adresa_facultate where cod_facultate=110; 2. Sa se creeze un trigger care se declanseaza inainte de fiecare modificare in tabela adresa_facultate. CREATE OR REPLACE TRIGGER adresa_trigger BEFORE update on adresa_facultate BEGIN dbms_output.put_line('triggerul s-a executat'); END; /
update adresa_facultate set adresa='o adresa' where cod_facultate=105; select * from adresa_facultate;
la nivel de rand 3. Sa se creeze un trigger care nu permite introducerea unui salariu mai mare decat valoarea maxima pt un nume specificat. CREATE OR REPLACE TRIGGER restrict_salariu BEFORE INSERT or UPDATE on administrator FOR EACH ROW DECLARE v_sal_max number; BEGIN select max(salariu) into v_sal_max from administrator where nume=:new.nume; IF :new.salariu>v_sal_max then RAISE_APPLICATION_ERROR (-20202, 'Nu se poate depasi salariul maxim pentru numele dat'); end if; END; / update administrator
set salariu =6000 where cod_administrator=400; 4. Sa se creeze un trigger la nivel de rand care nu permite introducerea unei medii mai mari de media maxima a studentilor. CREATE OR REPLACE TRIGGER restrict_media BEFORE INSERT on student FOR EACH ROW DECLARE v_medie number; BEGIN select max(media) into v_medie from student where nume=:new.nume; IF :new.media>v_medie then RAISE_APPLICATION_ERROR (-20202, 'Nu se poate depasi salariul maxim'); end if; END; /
insert into student(cod_student,cod_universitate,cod_facultate,nume,media) values (123,1000,105,'Ionel',10);
APEX https://apex.oracle.com/pls/apex/f? p=4000:1:7107209493841::NO:RP:FB_FLOW_ID,F4000_P1_FLOW,P0_FLOWPAGE,R ECENT_PAGES:63428,63428,63428
1. Creati un raport si o macheta de intrare pentru tabela Universitate.
https://apex.oracle.com/pls/apex/f?p=82972:1:11078923647092:::::
2. Creati un raport si o macheta de intrare pentru tabela Facultate. https://apex.oracle.com/pls/apex/f?p=70780:1:116760987977346:::::
https://apex.oracle.com/pls/apex/f?p=83606:2:100561836927136:::::
