7/25/2019 Examen-sgbd

1/3

create table spectacol(id_spectacol number primary key,denumire varchar2(50),stagiune number,cod_sala number,constraint fk_spectacol_sala foreign key (cod_sala)references sala(id_sala));

create table sala(id_sala number primary key,denumire varchar2(50),numar_locuri number);

create table bilet(id_bilet number primary key,pret number,cod_spectacol number,data_spectacol date,rand number,loc number,constraint fk_bilet_spectacol foreign key(cod_spectacol) references spectacol(id_spectacol));

create table actor

(id_actor number primary key,nume varchar2(50),prenume varchar2(50),data_nasterii date);

create table joaca(cod_actor number,cod_spectacol number,rol varchar2(50),constraint fk_joaca_actor foreign key (cod_actor)references actor(id_actor),constraint fk_joaca_spectacol foreign key (cod_spectacol)references spectacol(id_spectacol),

constraint pk_joaca primary key (cod_actor,cod_spectacol));

--1create or replace function functie_ex1(p_rol varchar2)return number isv_rez number := 0;begin

select count(*) into v_rezfrom joaca join actor on cod_actor = id_actorwhere rol = p_roland (sysdate - data_nasterii)/365 > 35;

dbms_output.put_line ('adas');return v_rez;

exceptionwhen No_Data_Found then RAISE_APPLICATION_ERROR(-20202, 'Nu exista actori ');end;

--2create or replace procedure proced_ex2 (p_stagiune spectacol.stagiune%type)is

7/25/2019 Examen-sgbd

2/3

type tab_imbri is table of number;type dates is record (denumire varchar2(50), actori tab_imbri , suma number);type tab_index is table of dates index by pls_integer;lista tab_index;indx number;indx1 number;begin

for i in (select * from spectacol where stagiune = p_stagiune) loop lista(i.id_spectacol).denumire := i.denumire;end loop;

indx := lista.first;

while (indx

7/25/2019 Examen-sgbd

3/3

for i in (select * from bilet) loop

pachet.lista(i.cod_spectacol) := pachet.lista(i.cod_spectacol) + 1; end loop;end;

create or replace trigger trigg2_ex3before inserton biletfor each rowdeclarev_locuri number;begin

select numar_locuri into v_locurifrom sala join spectacolon sala.id_sala = spectacol.cod_salawhere id_spectacol = :new.cod_spectacol;

if (pachet.lista(:new.cod_spectacol) >= v_locuri) then raise_application_error(-20202,'Nu mai exista locuri');else pachet.lista(:new.cod_spectacol) := pachet.lista(:new.cod_spectacol) + 1;end if;end;