SGBD

 

Studenti

Imprumut

Catalog

Achizitie

create table studenti

( codstudent number(5) constraint pk_stud primary key,

nume varchar2(32) not null,

prenume varchar2(32) not null,

cnp varchar2(32) check (length(cnp)=13),

adresa varchar2(128));

create table achizitie

(nr_achizitie number(5) constraint pk_achizitie primary key,

titlu varchar2(32),

autor varchar2(32),

furnizor varchar2(32),

pret number(3));

create table catalog

( nrinv number(5) constraint pk_inv primary key,

titlu varchar2(32),

autor varchar2(32) ,

aparut la varchar2(32),

editura varchar2(32),

note varchar2(32));

create table imprumut

(marca number(5) constraint pk_imprumut primary key,

codstudent number(5),

nrinv varchar2(32) ,

datai date,

datar date,

intarziere number(3));

constraint fk_imprumut_studenti foreign key(codstudent) references studenti;

constraint fk_imprumut_catalogare foreign key(nrinv) references catalogare;

create sequence cod_stud

INCREMENT BY 1

START WITH 0

NOMAXVALUE

NOCACHE

NOCYCLE;

insert into studenti

values(cod_stud.NEXTVAL,'Andrei Nichifor','a','1870416459934','Independentei nr

39,Bucuresti');

insert into studenti

values(cod_stud.NEXTVAL,'Mihaela Androne','a','2850422957400','Tincani nr

26,Bucuresti');

insert into studenti

values(cod_stud.NEXTVAL,'Mihai Albu','a','1880909650087','Unirii nr 9,Bucuresti');

insert into studenti

values(cod_stud.NEXTVAL,'Oana Rosu','a','2700521986534','Mihail Moxa nr

23,Bucuresti');

insert into studenti

values(cod_stud.NEXTVAL,'Ioana Benea','a','2800712865650','Dejului nr

55,Bucuresti');

insert into studenti

values(cod_stud.NEXTVAL,'Ion Bratu','a','1860312865650','Grivitei nr 6,Bucuresti','');

select * from studenti;

insert into catalog

values(1,'Algoritmi in programare','Ghilic-Micu Bogdan, coord. Rosca Ion Gh.','Bucuresti,2002','Editura ASE','');

insert into catalog

values(2,'Statistica','Voineagu Vergil ,Isaic-Maniu Alexandru','Bucuresti,2002','Cison','');

insert into catalog

values(3,'Baze de date : organizare, proiectare si implementare','Lungu Ion , Bodea Constanta','Bucuresti,1995','Editura_All','');

insert into catalog

values(4,'Teoria probabilitatilor si statistica matematica','Cenuse Gheorghe','Bucuresti,1999','Editura ASE','');

select * from catalog;

insert into imprumut

values(1,2,1,to_date('20 12 2007', 'dd mm yyyy'),to_date('07 01 2008','dd mm yyyy'),'');

insert into imprumut

values(2,1,4,to_date('18 12 2007', 'dd mm yyyy'),to_date('07 01 2008','dd mm yyyy'),'');

insert into imprumut

values(3,3,2,to_date('10 12 2007', 'dd mm yyyy'),to_date('20 12 2007','dd mm yyyy'),'');

insert into imprumut

values(4,5,3,to_date('15 11 2007', 'dd mm yyyy'),to_date('25 12 2007','dd mm yyyy'),'');

insert into imprumut

values(5,4,1,to_date('15 11 2007', 'dd mm yyyy'),to_date('25 12 2007','dd mm yyyy'),'','nereturnat');

insert into imprumut

values(6,2,2,to_date('15 12 2007', 'dd mm yyyy'),to_date('25 01 2008','dd mm yyyy'),'','nereturnat');

select * from imprumut;

insert into achizitie

values(1,'A-Z management and finance:the definitive guide to tools,terms and techniques','Koch Richard','Prior Books',65);

insert into achizitie

values(2,'Sistemul conturilor nationale','Anghelache Constantin,Isaic-Maniu Alexandru','Humanitas',25);

select * from achizitie;

LDD

--adaugati o cheie externa care face legatura intre tabela achizitii si tabela catalogare

begin

execute immediate 'alter table achizitie modify(constraint fk_nrinv foreign key(nrinv) references catalogare)';

end;

cresteti dimensiunea titlului din tabela achizitie de la 128 la 164

begin
execute immediate 'alter table achizitie modify titlu varchar2(164)';
end;

--adaugati o noua coloana in tabela 'cataloage' care sa contina codul ISBN begin
execute immediate 'alter table cataloage add ISBN number(10) default 0';
end;

LMD

--Sa se stabileasca pretul maxim platit pentru o carte

declare
p achizitie.pret%TYPE;
titlu_c achizitie.titlu%TYPE;
begin
select max(pret) into p
from achizitie;

select titlu into titlu_c
from achizitie
where pret=p;

dbms_output.put_line('cartea cu cel mai mare pret de achizitie este'||titlu_c);

end;

--sa se afiseze studentul care a returnat cartea dupa ca mai luga perioada

Declare

v_nume studenti.nume%type;

v number(5);

v_cod studenti.codstudent%type;

begin

update imprumut set intarziere=to_number(to_char(datar-datai));

select

max(intarziere) into v

from imprumut;

select codstudent

into v_cod

from imprumut where intarziere=v;

select nume

into v_nume

from studenti where codstudent=v_cod;

dbms_output.put_line(v_nume);

end;

Functii,proceduri,cursori

--creaza o procedura care marste nr de exeplare cu un nr dat n pentru cartea cu nr de inv dat

Create or replace procedure nr_exemplare(n IN number,nr_inv IN catalog.nrinv%type) is

Begin

Update catalog set nr_ex=nr_ex+n where nrinv=nr_inv;

Exception

when no_data_found then dbms_output.put_line('nu exista cartea cu nr de inv'||n);

End;

--o procedura care afiseaza cartile care au mai mult de p_nr exemplare

Create or replace procedure afisare_carti(p_nr IN number) is

CURSOR c_carti (nr NUMBER) IS

SELECT nrinv, titlu, autor, editura,nr_ex

FROM catalog

WHERE nr_ex > nr

ORDER BY nr_ex desc;

rec_carti c_carti%rowtype;

BEGIN

OPEN c_carti;

DBMS_OUTPUT.PUT_LINE('Produsele al caror stoc este mai mare decat '|| p_nr);

LOOP

FETCH c_carti into rec_carti;

EXIT WHEN c_carti%notfound;

DBMS_OUTPUT.PUT_LINE('cartea cu'||rec_carti.nrinv|| ||rec_carti.titlu||rec_carti.autor||rec_carti.editura||'are '||rec_carti.nr_ex||'exemplare');

END LOOP;

CLOSE c_carti;

END;

--afisati numele si numarul  studentilor care au imprumutat cartea X

Create or replace function f_imprumut(i catalog.nrinv%type) return number

Is

v_rez number(5);

Cursor c_stud is

Select codstudent from imprumut where nrinv=i;

v_stud c_stud%type;

BEGIN

OPEN c_stud;

LOOP

FETCH c_stud IN v_stud;

Select nume in v_nume from studenti where codstudent=v_stud;

Dbms_output.put_line(v_nume);

EXIT when c_stud%NOTFOUND;

v_rez:=SQL%ROWCOUNT

close c_stud;

RETURN v_rez;

END;

--o functie care returneaza true/false daca pretul de achizitie al unei carti este mai mare,mai mic sau egal cu media preturilor de achizitie;

Create or replace function f_pret(nr achizitii.nr_achizitie%type) return Boolean is

p_mediu number(20);

v_pret number(20);

begin

select pret into v_pret from achizitie where nrinv=nr;

select avg(pret) into p_mediu from achizitie where nrinv=nr;

if v_pret>p_mediu return true;

elseif v_pret=mediu return true;

else return false;

Exception

when no_data_found return NULL;

end;

--o functie care returneaza val TVA din pretul de achizitie

Create or replace function f_tva(nr achizitii.nr_achizitie%type) return number is

tva number(10);

v_pret number(10);

begin

select pret into v_pret from achizitii where nr_achizitie=nr;

tva=0.19*v_pret/1.19;

return tva;

exception

when no_data_found return NULL;

end;

Triggeri

--sa se creeze un trigger care sa nu permita inserarea unei data a imprumutului mai mare decat data sistemului;

Create or replace trigger t_data
before insert on imprumut
for each row
begin
if (:new.datai>sysdate) then
raise_application_error(-20001,'Data nu este valida!!!');
end if;

end

--sa se creeze un trigger care sa actualizeze nr de inventar in tabelele imprumut si achizitii daca este modificat in tabela catalog

Create or replace trigger t2_actualizare
before update of nrinv on catalog
for each row
begin

update imprumut
set nrinv = :new.nrinv
where nrinv = :old.nrinv;

update achizitii

set nrinv =:new.nrinv
where nrinv =nrinv;

end;

Exceptii

--sa se stearga toate imprumuturile;

DECLARE
exceptie EXCEPTION

pragma excecption_init(exceptie, -2292);
begin
delete from imprumuturi;

exception
when exceptie then
dbms_output.put_line('Nu puteti sterge imprumuturile ');
dbms_output.put_line('Exista studenti care au imprumutat carti!');
end;

Sa se trateze eventualele erori generate de comnda insert apelata in blocul pl/sql:

declare
insert_err EXCEPTION;
pragma exception_init(insert_err,-1400);

begin
insert into studenti(codstudent,nume) values(15,NULL);
exception
when insert_err then

DBMS_OUTPUT.PUT_LINE('Nu ati specificat numele studentului!');

end;

--sa se modifice numele studentului al carui cod este citit de la tastatura sa se trateze eventualele exceptii

accept a prompt 'Introduceti codul studentului '
DECLARE

cod studenti.codstudent%type:=&a;

invalid_stud EXCEPTION;

begin

update studenti

set nume='Lica Scandurica'

where codstudent=cod;

if sql%notfound then raise invalid_stud;

end if;

exception

when invalid_stud then

dbms_output.put_line('Nu exista produsul cu acest cod');

end;

Pachete

create or replace package pachet is

procedure nr_exemplare(n IN number,nr_inv IN catalog.nrinv%type);

procedure afisare_carti(p_nr IN number);

function f_imprumut(i catalog.nrinv%type);

function f_pret(nr achizitii.nr_achizitie%type);

function f_tva(nr achizitii.nr_achizitie%type);

end;

create or replace package body pachet is

procedure nr_exemplare(n IN number,nr_inv IN catalog.nrinv%type) is

Begin

Update catalog set nr_ex=nr_ex+n where nrinv=nr_inv;

Exception

when no_data_found then dbms_output.put_line('nu exista cartea cu nr de inv'||n);

End;

procedure afisare_carti(p_nr IN number) is

CURSOR c_carti (nr NUMBER) IS

SELECT nrinv, titlu, autor, editura,nr_ex

FROM catalog

WHERE nr_ex > nr

ORDER BY nr_ex desc;

rec_carti c_carti%rowtype;

BEGIN

OPEN c_carti;

DBMS_OUTPUT.PUT_LINE('Produsele al caror stoc este mai mare decat '|| p_nr);

LOOP

FETCH c_carti into rec_carti;

EXIT WHEN c_carti%notfound;

DBMS_OUTPUT.PUT_LINE('cartea cu'||rec_carti.nrinv|| ||rec_carti.titlu||rec_carti.autor||rec_carti.editura||'are '||rec_carti.nr_ex||'exemplare');

END LOOP;

CLOSE c_carti;

END;

function f_imprumut(i catalog.nrinv%type) return number

Is

v_rez number(5);

Cursor c_stud is

Select codstudent from imprumut where nrinv=i;

v_stud c_stud%type;

BEGIN

OPEN c_stud;

LOOP

FETCH c_stud IN v_stud;

Select nume in v_nume from studenti where codstudent=v_stud;

Dbms_output.put_line(v_nume);

EXIT when c_stud%NOTFOUND;

v_rez:=SQL%ROWCOUNT

close c_stud;

RETURN v_rez;

END;

function f_pret(nr achizitii.nr_achizitie%type) return Boolean is

p_mediu number(20);

v_pret number(20);

begin

select pret into v_pret from achizitie where nrinv=nr;

select avg(pret) into p_mediu from achizitie where nrinv=nr;

if v_pret>p_mediu return true;

elseif v_pret=mediu return true;

else return false;

Exception

when no_data_found return NULL;

end;

function f_tva(nr achizitii.nr_achizitie%type) return number is

tva number(10);

v_pret number(10);

begin

select pret into v_pret from achizitii where nr_achizitie=nr;

tva=0.19*v_pret/1.19;

return tva;

exception

when no_data_found return NULL;

end;