exemplu de instructiuni sql

8/13/2019 Exemplu de instructiuni SQL

1/8

--creare tabelecreate table parteneri (id_partener number,denumire varchar2(100),cui number,adresa varchar2(200),telefon number,con_bancar varchar2(50),constraint parteneri_pk primary key(id_partener));

create table curs (id_curs number,denumire varchar2(200),durata number,sala varchar2(10),id_sustinator number,id_departament_organizator number,constraint curs_pk primary key(id_curs));

create table cursuri_efectuate (id_curs_efectuat number,id_curs number,

id_angajat number,data_finalizare date,constraint cursuri_efectuate_pk primary key(id_curs_efectuat));

create table cursuri_programate (id_curs_programat number,id_curs number,id_angajat number,data_incepere date,constraint cursuri_programate_pk primary key(id_curs_programat));

create table departament (id_departament number,denumire varchar(150),constraint departament_pk primary key(id_departament));

create table post (id_post number,id_departament number,denumire varchar2(150),salariu number,constraint post_pk primary key(id_post));

create table sindicat (id_sindicat number,denumire varchar2(200),adresa varchar2(150),telefon number,persoana_contact varchar2(150),constraint sindicat_pk primary key(id_sindicat));


2/8

create table angajat (id_angajat number,id_sindicat number,id_post number,id_departament number,nume varchar2(150),cnp number,adresa varchar2(200),telefon number,data_nastere date,data_angajare date,constraint angajat_pk primary key(id_angajat));

create table servicii (id_serviciu number,denumire varchar2(200),pret number,id_departament number,constraint servicii_pk primary key(id_serviciu));

create table servicii_pe_factura (id_serviciu number,

id_factura number,constraint servicii_pe_factura_pk primary key(id_serviciu, id_factura));

create table factura(id_factura number,data_emitere date,id_destinatar number,id_angajat number,suma_de_plata number,constraint factura_pk primary key (id_factura));

create table mod_de_plata (id_mod_plata number,id_factura number,id_angajat number,id_emitent number,id_destinatar number,cont_emitent varchar2(50),cont_destinatar varchar2(50),numar number,data_emitere date,valoare number,constraint mod_de_plata_pk primary key(id_mod_plata));

create table comenzi(id_comanda number,id_partener number,id_factura number,data_emitere date,status varchar2(10),constraint comenzi_pk primary key(id_comanda));

create table servicii_pe_comanda(


3/8

id_comanda number,id_serviciu number,constraint servicii_pe_comanda_pk primary key(id_comanda, id_serviciu));

create table utilizator (id_utilizator number,id_legatura number,nume_utilizator varchar2(200),parola varchar2(200),rol number,constraint utilizator_pk primary key(id_utilizator));

create table meniu(id_meniu int primary key,nume varchar(200),adresa varchar(200),rol number);

-- adaugare constrangeri de cheie externa

alter table comenzi add(constraint comenzi_fk_partener foreign key (id_partener) references parteneri(id

_partener),constraint comenzi_fk_factura foreign key (id_factura) references factura(id_factura) );

alter table servicii_pe_comanda add(constraint servicii_comanda_fk_comanda foreign key(id_comanda) references comenzi(id_comanda),constraint servicii_comanda_fk_servicii foreign key(id_serviciu) references servicii(id_serviciu) );

alter table servicii_pe_factura add(constraint servicii_factura_fk_factura foreign key(id_factura) references factura(id_factura),

constraint servicii_factura_fk_servicii foreign key(id_serviciu) references servicii(id_serviciu) );

alter table factura add(constraint factura_fk_angajat foreign key(id_angajat) references angajat(id_angajat),constraint factura_fk_destinatar foreign key (id_destinatar) references parteneri(id_partener) );

alter table utilizator add(constraint intern_fk_angajat foreign key(id_legatura) references angajat(id_angajat),

constraint extern_fk_partener foreign key(id_legatura) references parteneri(id_partener) );

alter table curs add(constraint curs_fk_angajat foreign key(id_sustinator) references angajat(id_angajat),constraint curs_fk_departament foreign key (id_departament_organizator) references departament(id_departament) );


4/8

alter table cursuri_efectuate add(constraint cursuri_efectuate_fk_curs foreign key(id_curs) references curs(id_curs),constraint cursuri_efectuate_fk_angajat foreign key(id_angajat) references angajat(id_angajat) );

alter table cursuri_programate add(constraint cursuri_programate_fk_curs foreign key(id_curs) references curs(id_curs),constraint cursuri_programate_fk_angajat foreign key(id_angajat) references angajat(id_angajat) );

alter table angajat add(constraint angajat_fk_sindicat foreign key(id_sindicat) references sindicat(id_sindicat),constraint angajat_fk_post foreign key(id_post) references post(id_post),constraint angajat_fk_departament foreign key(id_departament) references departament(id_departament));

alter table mod_de_plata add(constraint mod_plata_fk_factura foreign key(id_factura) references factura(id_factura),

constraint mod_plata_fk_angajat foreign key(id_angajat) references angajat(id_angajat),constraint mod_plata_fk_partener_e foreign key(id_emitent) references parteneri(id_partener),constraint mod_plata_fk_partener_d foreign key(id_destinatar) references parteneri(id_partener));

alter table post add(constraint post_fk_departament foreign key(id_departament) references departament(id_departament) );

alter table servicii add(

constraint servicii_fk_departament foreign key(id_departament) references departament(id_departament) );

-- crearea secventelor pentru cheia primara

create sequence add_partenerminvalue 1start with 1increment by 1nocyclenocache;

create sequence add_utilizatorminvalue 1start with 1increment by 1nocyclenocache;

create sequence add_curs_programatminvalue 1start with 1


5/8


6/8


7/8

select add_curs_efectuat.nextval into :new.id_curs_efectuat from dual; end if;end;

create or replace trigger adauga_cursuri_programatebefore insert on cursuri_programate for each rowbegin if :new.id_curs_programat is null then select add_curs_programat.nextval into :new.id_curs_programat from dual; end if;end;

create or replace trigger adauga_cursuribefore insert on curs for each rowbegin if :new.id_curs is null then select add_curs.nextval into :new.id_curs from dual; end if;end;

create or replace trigger adauga_serviciibefore insert on servicii for each rowbegin if :new.id_serviciu is null then

select add_serviciu.nextval into :new.id_serviciu from dual; end if;end;

create or replace trigger adauga_mod_platabefore insert on mod_de_plata for each rowbegin if :new.id_mod_plata is null then select add_mod_de_plata.nextval into :new.id_mod_plata from dual; end if;end;

create or replace trigger adauga_comenzi

before insert on comenzi for each rowbegin if :new.id_comanda is null then select add_comanda.nextval into :new.id_comanda from dual; end if;end;

create or replace trigger adauga_facturabefore insert on factura for each rowbegin if :new.id_factura is null then select add_factura.nextval into :new.id_factura from dual; end if;

end;

create or replace procedure adauga_partener(p_denumire in parteneri.denumire%type,p_cui in parteneri.cui%type,p_adresa in parteneri.adresa%type,p_telefon in parteneri.telefon%type,p_cont_bancar in parteneri.cont_bancar%type,p_nume_utilizator in utilizator.nume_utilizator%type,


8/8

p_parola in utilizator.parola%type)isbegin

insert into parteneri(id_partener, denumire, cui, adresa, telefon, cont_bancar) values( add_partener.nextval, p_denumire, p_cui, p_adresa, p_telefon, p_cont_bancar );insert into utilizator(id_utilizator, id_legatura, nume_utilizator, parola, rol ) values(add_utilizator.nextval, add_partener.currval, p_nume_utilizator, p_parola, 3 );commit;

end adauga_partener;/

create or replace procedure adauga_angajat(p_id_sindicat in angajat.id_angajat%type,p_id_post in angajat.id_post%type,

p_id_departament in angajat.id_departament%type,p_nume in angajat.nume%type,p_cnp in angajat.cnp%type,p_adresa in angajat.adresa%type,p_telefon in angajat.telefon%type,p_data_nastere in angajat.data_nastere%type,p_data_angajare in angajat.data_angajare%type,p_nume_utilizator in utilizator.nume_utilizator%type,p_parola in utilizator.parola%type,p_rol in utilizator.rol%type)isbegin

insert into angajat(id_angajat, id_sindicat, id_post, id_departament, nume, cnp, adresa, telefon, data_nastere, data_angajare ) values(add_angajat.nextval, p_id_sindicat, p_id_post, p_id_departament, p_nume, p_cnp, p_adresa, p_telefon, p_data_nastere, p_data_angajare);

insert into utilizator(id_utilizator, id_legatura, nume_utilizator, parola, rol ) values(add_utilizator.nextval, add_angajat.currval, p_nume_utilizator, p_parola, p_rol);commit;end adauga_angajat;/

exemplu de instructiuni sql

Documents