proiect sgbd final (1)
Post on 18-Jan-2016
64 Views
Preview:
DESCRIPTION
TRANSCRIPT
Academia de Studii Economice Cibernetică, Statistică şi Informatică Economică
GESTIUNEA ACTIVITĂŢII UNEI
BĂNCI
Cărbune Diana-Mihaela
Grupa 1025, Seria A
1
Cuprins
Descrierea temei ................................................................................................................................. 2
Legaturile intre entitati ....................................................................................................................... 3
Schema bazei de date .......................................................................................................................... 4
Interactiunea cu serverul Oracle prin intermediul comenzilor SQL .................................................... 5
Structuri alternative si repetitive ...................................................................................................... 18
Exceptii .............................................................................................................................................. 23
Gestionarea cursorilor ....................................................................................................................... 27
Functii si proceduri ............................................................................................................................ 32
Declansatori ....................................................................................................................................... 40
Machete de intrare (videoformate) .................................................................................................. 42
Machete de iesire (rapoarte) ............................................................................................................ 43
2
Descrierea temei
Astăzi, mai mult ca niciodată, desfăşurarea oricărei activităţi bancare nu se poate imagina fără un puternic suport informaţional care să asigure avantajul concurenţial în raport cu ceilalţi competitori de pe piaţă.O societate bancară, în condiţiile actuale ale pieţei privind concurenţa extraordinară a băncilor străine care şi-au deschis sucursale şi filiale în România, nu este viabilă dacă sistemul informatic care operează nu este viabil.Sistemul informatic reprezintă „motorul” care asigură suportul informaţiilor necesare bunei funcţionări, dar şi accesul rapid şi securizat la aceste informaţii.Pentru aceasta, am realizat baza de date care gestionează activitatea unei bănci comerciale.
Scopul bazei de date este de a ţine evidenţa clienţilor unei bănci, conturilor deţinute de aceştia, precum şi a tranzacţiilor efectuate, dar şi o legătură a angajaţilor şi a conturilor deschise de către aceştia.
Structura bazei de date Tabelele ce alcătuiesc baza de date sunt descrise mai jos.
LOCAŢII
#ID_LOCATIE
ORAS
STRADA
NUMAR
Tabela conţine informaţii despre adresele diferitelor sucursale ale băncii.
SUCURSALE
#COD_SUCURSALA
DENUMIRE_SUCURSALA
ID_LOCATIE
Tabela conţine informaţii despre diferitele sucursale ale băncii. Sucursala este caracterizată
prin denumire şi locaţie.
ANGAJATI
#ID_ANGAJAT NUME PRENUME TELEFON SALARIU DATA_ANG COD_SEF COD_ SUCURSALA
Tabela conţine informaţii despre angajaţii unei sucursale: nume, prenume, telefon, salariu,
data angajării si angajatul în subordinea căruia se află.
CONTURI
#NR_CONT TIP_CONT DATA_DESCHIDERE COD_SUCURSALA ID_ANGAJAT ID_CLIENT
3
Tabela conţine informaţii despre conturile deschise la o anumită sucursală a băncii şi angajaţii
care s-au ocupat de deschiderea acestora.
TRANZACŢII
#ID_TRANZACTIE DATA SUMA_TRANZACTIONATA NR_CONT
Tabela conţine informaţii despre tranzacţiile efectuate pe un anumit cont.
CLIENTI
#ID_CLIENT NUME PENUME TELEFON DATA_NASTERE NIVEL_VENITURI
Tabela conţine datele de contact ale fiecărui client al băncii.
LEGATURILE DINTRE ENTITATI
O sucursală se află într-o singură locaţie, o locaţie poate conţine mai multe sucursale.(1:M)
O sucursală are mai mulţi angajaţi, un angajat lucrează într-o singură sucursală. (1:M)
O sucursală are mai multe conturi deschise, un cont este deschis la o singură sucursală.(1:M)
Prin intermediul unui cont se pot face mai multe tranzacţii, o tranzacţie este facută pe un
singur cont.(1:M)
Un client poate avea mai multe conturi deschise, un cont poate fi deţinut de un singur client
(1:M).
4
SCHEMA BAZEI DE DATE
5
Interactiunea cu serverul Oracle prin intermediul comenzilor SQL
COMENZI DE DEFINIRE A DATELOR
I.CREATE 1.Să se creeze tabela LOCAŢII in cadrul unui bloc PL/SQL
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE LOCATII
(
ID_LOCATIE NUMBER(4) CONSTRAINT pk_locatie PRIMARY KEY,
COD_POSTAL VARCHAR2(12) NOT NULL,
ORAS VARCHAR2(30) NOT NULL,
STRADA VARCHAR2(25),
NUMAR NUMBER(3)
)';
END;
/
2.Să se creeze tabela SUCURSALE in cadrul unui bloc PL/SQL.
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE SUCURSALE
(
COD_SUCURSALA NUMBER(5) CONSTRAINT pk_sucursala PRIMARY KEY,
DENUMIRE VARCHAR2(30),
ID_LOCATIE NUMBER(4) CONSTRAINT fk_loc REFERENCES
LOCATII(id_locatie)
)
';
END;
/
6
3.Să se creeze tabela ANGAJATI in cadrul unui bloc PL/SQL prin intermediul unei variabile de tip
VARCHAR2.
DECLARE
v_sir VARCHAR2(400);
BEGIN
v_sir:=
'CREATE TABLE ANGAJATI
(
ID_ANGAJAT NUMBER(6) CONSTRAINT pk_angajati PRIMARY KEY,
NUME VARCHAR2(20) NOT NULL,
PRENUME VARCHAR2(20) NOT NULL,
TELEFON VARCHAR2(10),
SALARIU NUMBER(6),
DATA_ANGAJARE DATE,
COD_SUCURSALA NUMBER(5) CONSTRAINT fk_sucursala REFERENCES
SUCURSALE(COD_SUCURSALA),
COD_SEF NUMBER(6)
)
';
EXECUTE IMMEDIATE v_sir;
END;
/
4.Să se creeze tabela CLIENTI in cadrul unui bloc PL/SQL. DECLARE
v_sir VARCHAR2(400);
BEGIN
v_sir:=
' CREATE TABLE CLIENTI
(
ID_CLIENT NUMBER(6) CONSTRAINT pk_client PRIMARY KEY,
NUME VARCHAR2(20) not null,
PRENUME VARCHAR2(20) not null,
TELEFON VARCHAR2(10) CONSTRAINT uq_telefon UNIQUE,
7
DATA_NASTERE DATE,
NIVEL_VENITURI VARCHAR2(20)
)
';
EXECUTE IMMEDIATE v_sir;
END;
/
5.Să se creeze tabela CONTURI in cadrul unui bloc PL/SQL. DECLARE
v_sir VARCHAR2(400);
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE CONTURI
(
NUMAR_CONT VARCHAR2(20) CONSTRAINT pk_cont PRIMARY KEY,
TIP_CONT VARCHAR2(20),
DATA_DESCHIDERE DATE,
COD_SUCURSALA NUMBER(5) CONSTRAINT fk_cont_sucursala REFERENCES
SUCURSALE(COD_SUCURSALA),
ID_ANGAJAT NUMBER(6) CONSTRAINT fk_ang_cont REFERENCES
ANGAJATI(ID_ANGAJAT),
ID_CLIENT NUMBER(6) CONSTRAINT fk_client_cont REFERENCES
CLIENTI(ID_CLIENT))
';
END;
/
8
6.Să se creeze tabela TRANZACTII in cadrul unui bloc PL/SQL. DECLARE
v_sir VARCHAR2(400);
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE TRANZACTII
(
ID_TRANZACTIE NUMBER(8) CONSTRAINT pk_trz PRIMARY KEY,
DATA TIMESTAMP(6) NOT NULL,
SUMA_TRANZACTIONATA NUMBER(8,2),
NUMAR_CONT VARCHAR2(20) CONSTRAINT fk_trz_cont REFERENCES
CONTURI(NUMAR_CONT)
)
';
END;
/
II.ALTER. DROP 1.Să se adauge următoarele restricţii asupra tabelei ANGAJATI: câmpul telefon va lua valori unice,
iar câmpul salariu va lua valori cuprinse între 1800 şi 5000.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ANGAJATI
ADD CONSTRAINT ck_salariu CHECK (salariu>1800 and salariu<5000)';
END;
/
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ANGAJATI
ADD CONSTRAINT uq_tel UNIQUE(TELEFON)';
END;
/
9
2.Să se adauge tabelei CONTURI următoarea restricţie: câmpul TIP_CONT poate lua valori doar din
lista (lei,valută);
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE CONTURI
ADD CONSTRAINT ck_tipcont CHECK (TIP_CONT in ('Lei','Valuta'))';
END;
/
3.Să se marcheze ca neutilizate coloanele STRADA si NUMAR din tabela LOCATII.
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE LOCATII
SET UNUSED COLUMN STRADA';
EXECUTE IMMEDIATE 'ALTER TABLE LOCATII
SET UNUSED COLUMN NUMAR';
END;
/
4.Să se stergă coloanele marcate ca neutilizate din tabela LOCATII.
BEGIN
EXECUTE IMMEDIATE
'ALTER TABLE LOCATII
DROP UNUSED COLUMNS';
END;
/
10
COMENZI DE MANIPULARE A DATELOR
I.INSERT 1.Să se insereze date în tabela LOCATII.
BEGIN
insert into LOCATII (ID_LOCATIE, COD_POSTAL, ORAS)
values (100, '00989', 'Slobozia');
insert into LOCATII (ID_LOCATIE, COD_POSTAL, ORAS)
values (110, '10934', 'Fetesti');
insert into LOCATII (ID_LOCATIE, COD_POSTAL, ORAS)
values (120, '1689', 'Bucuresti');
insert into LOCATII (ID_LOCATIE, COD_POSTAL, ORAS)
values (130, '6823', 'Cluj');
commit;
END;
/
2.Să se insereze date în tabela SUCURSALE.
BEGIN
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (10,'BCR Unic',100);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (20,'BCR Sala Palatului',120);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (30,'BCR Piata Revolutiei',120);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (40,'BCR Matei Basarab',130);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (50,'BCR Ionel Perlea',100);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (60,'BCR Unirii',130);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (70,'BCR Marasti',110);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (80,'BCR Piata Garii',110);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values (90,'BCR Patria',120);
insert into SUCURSALE(COD_SUCURSALA, DENUMIRE,ID_LOCATIE)
values(100,'BCR Carol',120);
commit;
END;
/
11
3. Să se insereze date în tabela ANGAJATI.
BEGIN
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(200,'Dumitru','Mihai','0765347234',3200,to_date('21-05-
1998', 'dd-mm-yyyy'),70,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(210,'Georgescu','Petrica','0723198734',2500,to_date('12-09-
1999', 'dd-mm-yyyy'),70,200);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(220,'Popescu','Ion','0789176387',2550,to_date('11-02-2002',
'dd-mm-yyyy'),70,200);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(230,'Ciopor','Florin','0744512389',2000,to_date('28-11-
2000', 'dd-mm-yyyy'),70,220);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(240,'Popa','Dan','0789128733',3000,to_date('21-03-2005',
'dd-mm-yyyy'),20,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(250,'Ionescu','Victor','0723487654',2900,to_date('17-09-
2007', 'dd-mm-yyyy'),20,240);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(260,'Vladescu','Maria','0722143765',3100,to_date('25-07-
2000', 'dd-mm-yyyy'),50,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(270,'Anton','Maria','0785432678',3500,to_date('13-05-2000',
'dd-mm-yyyy'),60,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
12
values(280,'Rosu','Cristina','0745349876',2000,to_date('11-09-
1999', 'dd-mm-yyyy'),60,270);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(290,'Vladu','Ioana','0721389734',1900,to_date('25-06-2010',
'dd-mm-yyyy'),80,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(300,'Iacob','Petre','0721675834',2300,to_date('11-09-2008',
'dd-mm-yyyy'),90,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(310,'Fieraru','Nicoleta','0865478654',2200,to_date('28-04-
1999', 'dd-mm-yyyy'),10,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(320,'Anghel','Marian','0786548654',2500,to_date('20-04-
2005', 'dd-mm-yyyy'),40,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(330,'Stroe','Andra','0722389712',2430,to_date('28-10-2002',
'dd-mm-yyyy'),30,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(340,'Saru','Nadia','076178954',3400,to_date('17-11-2002',
'dd-mm-yyyy'),30,330);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(350,'Tatu','Elena','0785123468',3250,to_date('23-01-2005',
'dd-mm-yyyy'),100,NULL);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(360,'Dumitru','Cecilia','0765234675',3210,to_date('12-09-
2008', 'dd-mm-yyyy'),40,320);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(370,'Luca','Ionica','0783123876',3100,to_date('19-06-2007',
'dd-mm-yyyy'),30,330);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(380,'Neagu','Alexandra','0721657543',2400,to_date('14-07-
2011', 'dd-mm-yyyy'),10,310);
insert into
ANGAJATI(ID_ANGAJAT,NUME,PRENUME,TELEFON,SALARIU,DATA_ANGAJARE,COD
_SUCURSALA,COD_SEF)
values(390,'Tatu','Elena','0789654789',2000,to_date('27-02-2011',
'dd-mm-yyyy'),10,310);
commit;
commit;
END;
/
13
4. Să se insereze date în tabela CLIENTI.
BEGIN
insert into
CLIENTI(ID_CLIENT,NUME,PRENUME,TELEFON,DATA_NASTERE,NIVEL_VENITURI
)
values(400,'MIU','COSMIN','0756479764',TO_DATE('28-09-1980', 'DD-
MM-YYYY'),'20000');
insert into
CLIENTI(ID_CLIENT,NUME,PRENUME,TELEFON,DATA_NASTERE,NIVEL_VENITURI
)
values(410,'POPA','VLADIMIR','0765897534',TO_DATE('30-05-1990',
'DD-MM-YYYY'),'134087');
insert into
CLIENTI(ID_CLIENT,NUME,PRENUME,TELEFON,DATA_NASTERE,NIVEL_VENITURI
)
values(420,'DIACONU','IOANA','0789543768',TO_DATE('02-12-1992',
'DD-MM-YYYY'),'187900');
insert into
CLIENTI(ID_CLIENT,NUME,PRENUME,TELEFON,DATA_NASTERE,NIVEL_VENITURI
)
values(430,'TOADER','STEFANIA','0724567890',TO_DATE('15-10-1987',
'DD-MM-YYYY'),'234000');
insert into
CLIENTI(ID_CLIENT,NUME,PRENUME,TELEFON,DATA_NASTERE,NIVEL_VENITURI
)
14
5.Sa se insereze date in tabela CONTURI.
BEGIN
insert into
CONTURI(NUMAR_CONT,TIP_CONT,DATA_DESCHIDERE,COD_SUCURSALA,ID_ANGAJ
AT,ID_CLIENT)
values('RO68TREZ00420010101X','Lei',to_date('21-04-2006', 'dd-mm-
yyyy'),30,330,410);
insert into
CONTURI(NUMAR_CONT,TIP_CONT,DATA_DESCHIDERE,COD_SUCURSALA,ID_ANGAJ
AT,ID_CLIENT)
values('RO25TREZ00420110104X','Lei',to_date('13-08-2005', 'dd-mm-
yyyy'),30,340,430);
insert into
CONTURI(NUMAR_CONT,TIP_CONT,DATA_DESCHIDERE,COD_SUCURSALA,ID_ANGAJ
AT,ID_CLIENT)
values('RO22TREZ00420120106X','Valuta',to_date('7-03-2000', 'dd-
mm-yyyy'),70,200,400);
insert into
CONTURI(NUMAR_CONT,TIP_CONT,DATA_DESCHIDERE,COD_SUCURSALA,ID_ANGAJ
AT,ID_CLIENT)
values('RO41TREZ00420120101X','Lei',to_date('18-03-2002', 'dd-mm-
yyyy'),70,210,420);
commit;
END;
/
5. Să se insereze date în tabela TRANZACTII.
BEGIN
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(500,to_timestamp('14-03-2007 06:59:43.666320', 'dd-mm-yyyy
hh24:mi:ss.ff'),2000,'RO68TREZ00420010101X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(510,to_timestamp('18-08-2009 07:49:43.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),3400,'RO68TREZ00420010101X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(520,to_timestamp('15-07-2008 04:34:43.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),2800,'RO68TREZ00420010101X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(530,to_timestamp('15-09-2012 02:30:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),3800,'RO25TREZ00420110104X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(540,to_timestamp('25-11-2011 01:20:20.648420', 'dd-mm-yyyy
hh24:mi:ss.ff'),4100,'RO22TREZ00420120106X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(550,to_timestamp('28-10-2010 01:30:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),2800,'RO22TREZ00420120106X');
15
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(560,to_timestamp('22-11-2009 07:24:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),3700,'RO25TREZ00420110104X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(570,to_timestamp('23-12-2008 09:45:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),2900,'RO22TREZ00420120106X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(580,to_timestamp('21-09-2009 02:30:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),3800,'RO41TREZ00420120101X');
insert into
TRANZACTII(ID_TRANZACTIE,DATA,SUMA_TRANZACTIONATA,NUMAR_CONT)
values(590,to_timestamp('20-10-2010 02:30:20.656420', 'dd-mm-yyyy
hh24:mi:ss.ff'),4200,'RO41TREZ00420120101X');
commit;
END;
/
II.UPDATE 1.Să se mărească cu 10% salariul angajatilor angajaţi înainte de anul 2000.
BEGIN
UPDATE ANGAJATI
SET SALARIU=SALARIU*1.1
WHERE EXTRACT (YEAR FROM DATA_ANGAJARE)<2000;
commit;
END;
/
16
2.Să se actualizeze tipul de cont-Valuta- pentru contul cu numarul RO25TREZ00420110104X.
BEGIN
UPDATE CONTURI
SET TIP_CONT='Valuta'
WHERE NUMAR_CONT='RO25TREZ00420110104X';
commit;
END;
/
3. Să se modifice numele angajatilor care lucrează la sucursala BCR Marasti
BEGIN
UPDATE ANGAJATI
SET NUME='POPESCU'
WHERE COD_SUCURSALA in (SELECT COD_SUCURSALA FROM SUCURSALE WHERE
DENUMIRE='BCR Marasti');
commit;
END;
/
17
4.Sa se mareasca cu 20% suma tranzactionata de conturile deschise la sucursala BCR Piata
Revolutiei.
BEGIN
UPDATE TRANZACTII
SET SUMA_TRANZACTIONATA=1.2*SUMA_TRANZACTIONATA
WHERE NUMAR_CONT in (SELECT NUMAR_CONT FROM CONTURI WHERE
COD_SUCURSALA in (SELECT COD_SUCURSALA FROM SUCURSALE WHERE
DENUMIRE='BCR Piata Revolutiei'));
commit;
END;
/
III.DELETE 1.Să se şteargă din tabela LOCATII locatia cu id-ul 100.
BEGIN
DELETE FROM LOCATII
WHERE ID_LOCATIE=100;
commit;
END;
/
18
Structuri alternative si repetitive
STRUCTURI ALTERNATIVE 1. Sa se afiseze pe ecran mesajul „DA” daca nivelul veniturilor clientului cu id-ul introdus de la tastatura este mai mic decat nivelul mediu al veniturilor, in caz contrar sa se afiseze „NU”.
SET SERVEROUTPUT ON
ACCEPT s_id PROMPT 'Introduceti id-ul clientului!'
DECLARE
v_id NUMBER:=&s_id;
v_mediu NUMBER;
v_nivel NUMBER;
BEGIN
SELECT ROUND(AVG(NIVEL_VENITURI),2) into v_mediu FROM CLIENTI;
DBMS_OUTPUT.PUT_LINE('Nivelul mediu al veniturilor
este:'||v_mediu);
SELECT nivel_venituri into v_nivel FROM CLIENTI where
id_client=v_id;
IF v_nivel<v_mediu THEN DBMS_OUTPUT.PUT_LINE('DA!');
ELSE DBMS_OUTPUT.PUT_LINE('NU!');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Clientul cu id-ul
introdus nu exista!');
END;
/
19
2. Sa se afiseze id-ul, numele, prenumele, salariul si salariul nou al angajatului cu id-ul introdus de la
tastatura astfel:
Dacă angajatul lucreaza in sucursala cu codul 70, salariul creşte cu 6% Dacă angajatul lucreaza in sucursala cu codul 10, salariul creşte cu 9% Afltfel, salariul creşte cu 4%;
SET SERVEROUTPUT ON
ACCEPT s_id PROMPT 'Introduceti id-ul angajatului!'
DECLARE
v_id NUMBER:=&s_id;
rec_ang ANGAJATI%ROWTYPE;
v_salnou ANGAJATI.SALARIU%TYPE;
BEGIN
SELECT * into rec_ang from angajati where id_angajat=v_id;
CASE
WHEN rec_ang.cod_sucursala=70 then v_salnou:=rec_ang.salariu*1.06;
WHEN rec_ang.cod_sucursala=10 then v_salnou:=rec_ang.salariu*1.09;
ELSE v_salnou:=rec_ang.salariu*1.04;
END CASE;
DBMS_OUTPUT.PUT_LINE(rec_ang.id_angajat||' '||rec_ang.nume||'
'||rec_ang.prenume||' '||rec_ang.salariu||' '||v_salnou);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Angajatul nu
exista!');
END;
/
20
STRUCTURI REPETITIVE
1. Sa se afiseze tranzactiile cu valoarea mai mare decat valoarea medie a tranzactiilor si efectuate in
anul introdus de la tastatura.
SET SERVEROUTPUT ON
ACCEPT s_an PROMPT 'Introduceti anul tranzactiei'
DECLARE
v_an NUMBER:=&s_an;
v_medie NUMBER;
v_min TRANZACTII.ID_TRANZACTIE%TYPE;
v_max TRANZACTII.ID_TRANZACTIE%TYPE;
rec_t TRANZACTII%ROWTYPE;
v_count NUMBER;
BEGIN
SELECT AVG(suma_tranzactionata),min(id_tranzactie),
max(id_tranzactie) INTO v_medie, v_min, v_max
FROM TRANZACTII;
DBMS_OUTPUT.PUT_LINE('Valoarea medie tranzactionata
este:'||v_medie);
SELECT COUNT(id_tranzactie) into v_count FROM TRANZACTII WHERE
extract(year from data)=v_an;
IF (v_count=0) THEN DBMS_OUTPUT.PUT_LINE('In anul'||' '||v_an||'
'||'nu s-a efectuat nicio tranzactie!');
END IF;
WHILE v_min<=v_max
LOOP
SELECT COUNT(id_tranzactie) INTO v_count FROM TRANZACTII WHERE
id_tranzactie=v_min;
IF (v_count<>0) THEN SELECT * INTO rec_t FROM TRANZACTII WHERE
id_tranzactie=v_min;
IF (rec_t.suma_tranzactionata>v_medie) THEN
IF (extract(year from rec_t.data)=v_an) THEN
DBMS_OUTPUT.PUT_LINE(rec_t.id_tranzactie||'
'||rec_t.data||' '||rec_t.suma_tranzactionata||'
'||rec_t.numar_cont);
END IF;
END IF;
END IF;
v_min:=v_min+1;
END LOOP;
END;
/
21
2.Sa se afiseze informatii despre angajatii care lucreaza in sucursala cu codul introdus de la tastatura.
SET SERVEROUTPUT ON
ACCEPT s_cod PROMPT 'Introduceti codul sucursalei'
DECLARE
v_cod NUMBER:=&s_cod;
v_min ANGAJATI.id_angajat%type;
v_max ANGAJATI.id_angajat%type;
v_count NUMBER;
rec_a ANGAJATI%ROWTYPE;
BEGIN
SELECT min(id_angajat), max(id_angajat) into v_min,v_max FROM
ANGAJATI;
SELECT COUNT(id_angajat) INTO v_count FROM ANGAJATI WHERE
cod_sucursala=v_cod;
IF (v_count=0) THEN DBMS_OUTPUT.PUT_LINE('Sucursala cu codul'||'
'||v_cod||' '||'nu exista!');
END IF;
LOOP
SELECT COUNT(id_angajat)INTO v_count FROM ANGAJATI WHERE
id_angajat=v_min;
IF (v_count<>0) THEN SELECT * INTO rec_a FROM ANGAJATI WHERE
id_angajat=v_min;
IF(rec_a.cod_sucursala=v_cod) THEN DBMS_OUTPUT.PUT_LINE
(rec_a.id_angajat||' '||rec_a.nume||' '||rec_a.prenume||'
'||rec_a.data_angajare);
END IF;
END IF;
v_min:=v_min+1;
EXIT WHEN v_min>v_max;
END LOOP;
END;
/
22
3. Sa se creeze un bloc PL/SQL prin care sa se afiseze angajatii care nu au superior.
SET SERVEROUTPUT ON
DECLARE
rec_a ANGAJATI%ROWTYPE;
v_min ANGAJATI.id_angajat%type;
v_max ANGAJATI.id_angajat%type;
v_count NUMBER;
i NUMBER;
BEGIN
SELECT min(id_angajat),max(id_angajat) into v_min,v_max FROM
ANGAJATI;
FOR i IN v_min..v_max LOOP
SELECT COUNT(id_angajat) into v_count FROM ANGAJATI where
id_angajat=i;
IF (v_count<>0) THEN SELECT * INTO rec_a FROM ANGAJATI WHERE
id_angajat=i;
IF rec_a.cod_sef IS NULL then
DBMS_OUTPUT.PUT_LINE(rec_a.id_angajat||' '||rec_a.nume||'
'||rec_a.prenume||' '||rec_a.salariu||' '||rec_a.data_angajare||'
'||rec_a.cod_sucursala);
END IF;
END IF;
END LOOP;
END;
/
23
EXCEPTII
1. Creati un bloc PL/SQL care sa afiseze informatii despre angajatii din sucursala al carui cod este
precizat de la tastatura. Tratati exceptiile care pot aparea astfel:
daca interogarea nu returneaza nicio valoarea, atunci se va afisa mesajul “Atentie! In
sucursala aceasta nu lucreaza angajati!
daca interogarea returneaza mai multe rezultate, atunci se va afisa mesajul “ Atentie! In
sucursala aceasta lucreaza mai multi angajati!”
daca interogarea returneaza o singura valoare, atunci se vor afisa informatii despre angajatul
respectiv.
SET SERVEROUTPUT ON
ACCEPT s_cod PROMPT 'Introduceti codul sucursalei'
DECLARE
v_cod ANGAJATI.cod_sucursala%type:=&s_cod;
rec_a ANGAJATI%ROWTYPE;
BEGIN
SELECT * INTO rec_a FROM ANGAJATI WHERE cod_sucursala=v_cod;
DBMS_OUTPUT.PUT_LINE(rec_a.id_angajat||' '||rec_a.nume||'
'||rec_a.prenume);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Atentie! In
sucursala'||' '||v_cod||' '||'nu lucreaza angajati!');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Atentie! In
sucursala'||' '||v_cod||' '||'lucreaza mai multi angajati!');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Atentie! A aparut o alta
eroare!');
END;
/
24
2. Sa se creeze un bloc PL/SQL prin care sa se afiseze informatii despre tranzactia cu id-ul introdus de
la tastatura. Tratati cazul in care nu exista tranzactia cu acest id.
SET SERVEROUTPUT ON
ACCEPT s_id PROMPT 'Introduceti codul sucursalei'
DECLARE
v_id TRANZACTII.id_tranzactie%type:=&s_id;
rec_t TRANZACTII%ROWTYPE;
BEGIN
SELECT * INTO rec_t FROM TRANZACTII WHERE ID_TRANZACTIE=v_id;
DBMS_OUTPUT.PUT_LINE(rec_t.id_tranzactie||' '||rec_t.data||'
'||rec_t.suma_tranzactionata||' '||rec_t.numar_cont);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Atentie! Nu exista
tranzactia cu acest id!');
END;
/
3. Sa se insereze in tabela LOCATII o noua locatie, fara a preciza denumirea acesteia, cu id-ul egal cu
(maxim+10) din id-urile introduse pana acum. În acest caz va apare o eroarea cu codul ORA-01400
prin care programatorul este avertizat de încălcarea unei restricţii de integritate. Sa se trateze
aceasta exceptie.
SET SERVEROUTPUT ON
DECLARE
v_max LOCATII.id_locatie%type;
INSERT_EXCEPT EXCEPTION;
PRAGMA EXCEPTION_INIT(INSERT_EXCEPT,-01400);
BEGIN
SELECT max(id_locatie) into v_max from locatii;
DBMS_OUTPUT.PUT_LINE(v_max);
INSERT INTO LOCATII VALUES(v_max+10,6897,NULL);
EXCEPTION
WHEN INSERT_EXCEPT THEN DBMS_OUTPUT.PUT_LINE('Nu ati precizat
denumirea noii locatii!');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
25
4. Sa se insereze in tabela CONTURI un nou cont de tipul „CURENT”, ceea ce va genera eroarea cu
codul ORA-2290 prin care programatorul este avertizat de incalcarea restrictiei de tip CHECK. Tratati
exceptia aparuta.
SET SERVEROUTPUT ON
DECLARE
CHECK_EXCEPT EXCEPTION;
PRAGMA EXCEPTION_INIT(CHECK_EXCEPT,-2290);
BEGIN
INSERT INTO CONTURI
VALUES('RO21TREZ00420520117X','Curent’,to_date(‘21-05-2005’,’dd-
mm-yyyy’),30,340,400);
EXCEPTION
WHEN CHECK_EXCEPT THEN DBMS_OUTPUT.PUT_LINE(‘Tip cont gresit!
Valorile ce pot fi luate sunt LEI sau VALUTA!’);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
5. Sa se numere cati clienti sunt si sa se trateze cazul in care sunt mai putin de 30.
SET SERVEROUTPUT ON
DECLARE
clienti_except EXCEPTION;
v_count NUMBER;
BEGIN
SELECT COUNT(id_client)INTO v_count FROM CLIENTI;
IF (v_count<30) THEN RAISE clienti_except;
ELSE DBMS_OUTPUT.PUT_LINE('Numar de clienti ai bancii este de:
'||v_count);
END IF;
EXCEPTION
WHEN clienti_except THEN DBMS_OUTPUT.PUT_LINE('Numarul de clienti
ai bancii este mai mic de 30!');
END;
/
26
6. Sa se afiseze informatii despre seful angajatului cu id-ul introdus de la tastatura. Sa se trateze
cazul in care acesta are functia de manager, atribuind exceptiei un cod si un mesaj de eroare.
SET SERVEROUTPUT ON
ACCEPT s_id PROMPT 'Introduceti id-ul angajatului!'
DECLARE
v_id ANGAJATI.id_angajat%type:=&s_id;
v_cod ANGAJATI.cod_sef%type;
rec_a ANGAJATI%ROWTYPE;
manager_except EXCEPTION;
PRAGMA EXCEPTION_INIT(manager_except,-20999);
BEGIN
SELECT cod_sef INTO v_cod FROM ANGAJATI WHERE id_angajat=v_id;
IF (v_cod IS NULL) THEN RAISE_APPLICATION_ERROR(-20999,'Nu are
sef!');
ELSE SELECT * INTO rec_a FROM ANGAJATI WHERE id_angajat=v_cod;
DBMS_OUTPUT.PUT_LINE(rec_a.id_angajat||' '||rec_a.nume||'
'||rec_a.prenume||' '||rec_a.cod_sucursala);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Angajatul cu id-ul
introdus nu exista!');
WHEN manager_except THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Angajatul cu id-ul introdus are functia de
manager!');
END;
/
27
GESTIONAREA CURSORILOR
Cursor implicit
1. Creati un bloc PL/SQL prin care sa mariti cu 15% salariul angajatilor , in cazul in care acesta este
mai mic de 2500. Contorizati si afisati numarul de modificari efectuate.
SET SERVEROUTPUT ON
BEGIN
UPDATE ANGAJATI
SET SALARIU=SALARIU*1.15
WHERE SALARIU<2500;
IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu
salariul mai mic de 1500!');
ELSE DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' '||' modificari
efectuate!');
END IF;
END;
/
2. Sa se inlocuiasca id-ul angajatului care s-a ocupat de conturile in VALUTA deschise in anul 2005 cu
valoarea 260. In cazul in care comanda UPDATE produce modificari afisati numarul acestora, in caz
contrar invocati o exceptie.
SET SERVEROUTPUT ON
DECLARE
update_except EXCEPTION;
BEGIN
UPDATE CONTURI
SET id_angajat=260
WHERE (TIP_CONT='Valuta') AND (EXTRACT(year from
data_deschidere)=2005);
IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' modificari
efectuate!');
ELSE RAISE update_except;
END IF;
EXCEPTION
WHEN update_except THEN DBMS_OUTPUT.PUT_LINE('Nu exista conturi
deschise in anul 2005 de tipul VALUTA!');
END;
/
28
3. Sa se stearga din tabela SUCURSALE sucursala cu id-ul locatiei introdus de la tastatura. Sa se
afiseze numarul de stergeri efectuate. Tratati exceptiile aparute.
SET SERVEROUTPUT ON
DECLARE
del_except EXCEPTION;
PRAGMA EXCEPTION_INIT(del_except,-2292);
BEGIN
DELETE FROM SUCURSALE WHERE ID_LOCATIE=&ID_LOC;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' stergeri efectuate!');
EXCEPTION
WHEN del_except THEN DBMS_OUTPUT.PUT_LINE('Nu puteti sterge
sucursala!');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
29
Cursor explicit
1. Sa se afiseze primele 3 sucursale cu cei mai multi angajati.
SET SERVEROUTPUT ON
DECLARE
CURSOR c IS SELECT a.cod_sucursala, COUNT(a.id_angajat)as
numar_angajati, s.denumire
FROM ANGAJATI a, SUCURSALE s
WHERE a.cod_sucursala=s.cod_sucursala
GROUP BY a.cod_sucursala, s.denumire
ORDER BY NUMAR_ANGAJATI DESC;
BEGIN
FOR rec_c in c LOOP
DBMS_OUTPUT.PUT_LINE(rec_c.cod_sucursala||'
'||rec_c.numar_angajati||' '||rec_c.denumire);
EXIT WHEN c%ROWCOUNT=3 or c%NOTFOUND;
END LOOP;
END;
/
2.Sa se afiseze informatii despre conturile deschise la sucursala BCR Marasti in anul precizat de la
tastatura.
SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT c.numar_cont,c.tip_cont,c.data_deschidere,c.id_angajat,
c.id_client
FROM CONTURI c, SUCURSALE s
WHERE c.cod_sucursala=s.cod_sucursala AND extract(year from
c.data_deschidere)=&AN AND s.denumire='BCR Marasti';
rec_c c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO rec_c;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec_c.numar_cont||' '||rec_c.tip_cont||'
'||rec_c.data_deschidere||' '||rec_c.id_angajat||'
'||rec_c.id_client);
END LOOP;
CLOSE c;
END;
/
30
3. Sa se afiseze pentru fiecare sucursala angajatii din cadrul acesteia.
SET SERVEROUTPUT ON
DECLARE
CURSOR s IS SELECT * FROM SUCURSALE
WHERE cod_sucursala IN (SELECT cod_sucursala FROM
ANGAJATI);
CURSOR a(x NUMBER) IS SELECT * FROM ANGAJATI
WHERE cod_sucursala=x;
rec_a a%ROWTYPE;
rec_s s%ROWTYPE;
BEGIN
OPEN s;
LOOP
FETCH s INTO rec_s;
EXIT WHEN s%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Sucursala '|| rec_s.denumire);
OPEN a(rec_s.cod_sucursala);
LOOP
FETCH a INTO rec_a;
EXIT WHEN a%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' -'||rec_a.nume||' '||rec_a.prenume);
END LOOP;
CLOSE a;
END LOOP;
CLOSE s;
END;
/
31
4. Sa se afiseze toti angajatii care s-au ocupat de deschiderea conturilor, precum si conturile deschise
de acestia.
SET SERVEROUTPUT ON
DECLARE
CURSOR a IS SELECT * FROM ANGAJATI WHERE id_angajat IN (SELECT
id_angajat FROM CONTURI);
CURSOR c(x NUMBER) IS SELECT * FROM CONTURI WHERE id_angajat=x;
rec_a a%ROWTYPE;
rec_c c%ROWTYPE;
BEGIN
OPEN a;
LOOP
FETCH a INTO rec_a;
EXIT WHEN a%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Angajatul '||rec_a.nume||'
'||rec_a.prenume);
OPEN c(rec_a.id_angajat);
LOOP
FETCH c INTO rec_c;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' -'||rec_c.numar_cont||'
'||rec_c.tip_cont||' '||rec_c.data_deschidere||'
'||rec_c.cod_sucursala||' '||rec_c.id_client);
END LOOP;
CLOSE c;
END LOOP;
CLOSE a;
END;
/
32
FUNCTII, PROCEDURI
Functii
1. Sa se creeze o functie ce primeste ca parametru id-ul unui angajat si returneaza numarul de
angajati cu salariul mai mic, angajati in acelasi an.
CREATE OR REPLACE FUNCTION numar_ang(p_id
ANGAJATI.id_angajat%type)
RETURN NUMBER
AS
p_sal ANGAJATI.salariu%type;
p_data NUMBER;
p_count NUMBER;
BEGIN
SELECT salariu, extract(year from data_angajare) into p_sal,p_data
from angajati where id_angajat=p_id;
SELECT COUNT(id_angajat)into p_count FROM ANGAJATI WHERE
salariu<p_sal AND to_number(to_char(data_angajare,'yyyy'))=p_data;
RETURN p_count;
EXCEPTION
WHEN NO_DATA_FOUND then return -1;
END;
/
SHOW ERRORS
SET SERVEROUTPUT ON
ACCEPT s_id PROMPT 'Introduceti id-ul angajatului!'
DECLARE
v_id ANGAJATI.id_angajat%type:=&s_id;
v_nr NUMBER;
BEGIN
v_nr:=numar_ang(v_id);
CASE
WHEN v_nr=-1 THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajatul cu
acest id!');
WHEN v_nr=0 THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu
salariul mai mic si angajati in acelasi an cu angajatul cu id-ul
'||v_id);
ELSE DBMS_OUTPUT.PUT_LINE('Numarul de angajati cu salariul mai mic
si angajati in acelasi an cu angajatul cu id-ul '||v_id||'
'||'este '||v_nr);
END CASE;
END;
/
33
2. Sa se creeze o functie care primeste ca parametru codul unei sucursale si un an si returneaza
salariul mediu al angajatilor din sucursala respectiva, angajati in acel an.
CREATE OR REPLACE FUNCTION salariu_mediu(p_cod
ANGAJATI.cod_sucursala%type, p_an NUMBER)
RETURN NUMBER
AS
p_sal NUMBER;
p_count NUMBER;
BEGIN
SELECT COUNT(ID_ANGAJAT),AVG(SALARIU)INTO p_count,p_sal FROM
ANGAJATI WHERE cod_sucursala=p_cod AND extract(year from
data_angajare)=p_an;
IF p_count=0 then return -1;
ELSE return p_sal;
END IF;
END;
/
SHOW ERRORS
SET SERVEROUTPUT ON
DECLARE
v_cod ANGAJATI.cod_sucursala%type:=&s_cod;
v_an NUMBER:=&s_an;
v_sal NUMBER;
BEGIN
v_sal:=salariu_mediu(v_cod,v_an);
IF v_sal=-1 THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajati in
sucursala cu codul '||v_cod||' '||'angajati in anul '||v_an);
ELSE
DBMS_OUTPUT.PUT_LINE('Salariul mediu al angajatilor din sucursala
cu codul '||v_cod||' '||'angajati in anul '||v_an||' este
'||v_sal);
END IF;
END;
/
34
3. Sa se creeze o functie ce returneaza suma minima tranzactionata de un cont deschis la sucursala
BCR Piata Revolutiei.
CREATE OR REPLACE FUNCTION suma_tranz
RETURN NUMBER
AS
p_count NUMBER;
p_min TRANZACTII.SUMA_TRANZACTIONATA%type;
BEGIN
SELECT COUNT(t.id_tranzactie), MIN(t.suma_tranzactionata)INTO
p_count, p_min
FROM CONTURI co, SUCURSALE s, TRANZACTII t
WHERE t.numar_cont=co.numar_cont AND
co.cod_sucursala=s.cod_sucursala AND s.denumire='BCR Piata
Revolutiei';
IF p_count=0 THEN RETURN -1;
ELSE RETURN p_min;
END IF;
END;
/
SHOW ERRORS
BEGIN
IF suma_tranz=-1 THEN DBMS_OUTPUT.PUT_LINE('Nu exista tranzactii
efectuate pe conturi deschise la sucursala BCR Piata
Revolutiei!');
ELSE DBMS_OUTPUT.PUT_LINE('Suma minima tranzactionata de un cont
deschis la sucursala BCR Piata Revolutiei este: '||suma_tranz);
END IF;
END;
/
35
Proceduri
1. Sa se scrie o procedura care afiseaza primii 5 angajati cu vechimea cea mai mare.
CREATE OR REPLACE PROCEDURE vechime_ang
AS
CURSOR c IS SELECT id_angajat,nume,prenume,TRUNC((SYSDATE-
data_angajare)/365,2) as vechime
FROM ANGAJATI
ORDER BY vechime DESC;
rec_c c%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c into rec_c;
EXIT WHEN c%ROWCOUNT>5 OR c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec_c.id_angajat||' '||rec_c.nume||'
'||rec_c.prenume||' '||rec_c.vechime);
END LOOP;
CLOSE c;
END;
/
SHOW ERRORS
EXECUTE vechime_ang;
36
2. Sa se creeze o procedura ce returneaza data incheierii si valoarea celei mai recente tranzactii prin
intermediul a doi parametrii de iesire.
CREATE OR REPLACE PROCEDURE tranzactie_recenta(p_val OUT
TRANZACTII.SUMA_TRANZACTIONATA%type,p_data OUT
TRANZACTII.DATA%type)
AS
CURSOR c IS SELECT * FROM TRANZACTII ORDER BY data DESC;
rec_c c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO rec_c;
p_val:=rec_c.suma_tranzactionata;
p_data:=rec_c.data;
CLOSE c;
END;
/
show errors
SET SERVEROUTPUT ON
DECLARE
v_val TRANZACTII.SUMA_TRANZACTIONATA%type;
v_data TRANZACTII.DATA%type;
BEGIN
tranzactie_recenta(v_val,v_data);
DBMS_OUTPUT.PUT_LINE('Valoarea celei mai recente tranzactii este
'||v_val||', incheiata la data de '||v_data);
END;
/
3. Sa se creeze o procedura ce afiseaza numarul de angajati pentru fiecare sucursala cu exceptia
sucursalelor BCR Unirii si BCR Marasti si doar in cazul in care depasesc 2 angajati.
CREATE OR REPLACE PROCEDURE nr_ang
AS
CURSOR c IS SELECT
s.cod_sucursala,s.denumire,COUNT(a.id_angajat)as nr_angajati
FROM ANGAJATI a, SUCURSALE s
WHERE a.cod_sucursala=s.cod_sucursala AND s.denumire
NOT IN('BCR Unirii','BCR Marasti')
GROUP BY s.cod_sucursala,s.denumire
ORDER BY nr_angajati DESC;
BEGIN
FOR rec_c IN c LOOP
IF rec_c.nr_angajati>2 THEN
DBMS_OUTPUT.PUT_LINE(rec_c.cod_sucursala||' '||rec_c.denumire||'
'||rec_c.nr_angajati);
END IF;
END LOOP;
END;
/
37
SHOW ERRORS
EXECUTE nr_ang;
Pachet de subprograme
CREATE OR REPLACE PACKAGE my_pack AS
FUNCTION numar_ang(p_id ANGAJATI.id_angajat%type)
RETURN NUMBER;
FUNCTION salariu_mediu(p_cod ANGAJATI.cod_sucursala%type, p_an
NUMBER)
RETURN NUMBER;
FUNCTION suma_tranz
RETURN NUMBER;
PROCEDURE vechime_ang;
PROCEDURE tranzactie_recenta(p_val OUT
TRANZACTII.SUMA_TRANZACTIONATA%type,p_data OUT
TRANZACTII.DATA%type);
PROCEDURE nr_ang;
p_sal ANGAJATI.salariu%type;
p_dataang NUMBER;
p_count NUMBER;
p_min TRANZACTII.SUMA_TRANZACTIONATA%type;
CURSOR c IS SELECT id_angajat,nume,prenume,TRUNC((SYSDATE-
data_angajare)/365,2) as vechime
FROM ANGAJATI
ORDER BY vechime DESC;
CURSOR c1 IS SELECT * FROM TRANZACTII ORDER BY data DESC;
CURSOR c2 IS SELECT
s.cod_sucursala,s.denumire,COUNT(a.id_angajat)as nr_angajati
FROM ANGAJATI a, SUCURSALE s
WHERE a.cod_sucursala=s.cod_sucursala AND s.denumire
NOT IN('BCR Unirii','BCR Marasti')
GROUP BY s.cod_sucursala,s.denumire
ORDER BY nr_angajati DESC;
rec_c c%ROWTYPE;
rec_c1 c1%ROWTYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pack AS
FUNCTION numar_ang(p_id ANGAJATI.id_angajat%type)
RETURN NUMBER
AS
BEGIN
SELECT salariu, extract(year from data_angajare) into
p_sal,p_dataang from angajati where id_angajat=p_id;
38
SELECT COUNT(id_angajat)into p_count FROM ANGAJATI WHERE
salariu<p_sal AND
to_number(to_char(data_angajare,'yyyy'))=p_dataang;
RETURN p_count;
EXCEPTION
WHEN NO_DATA_FOUND then return -1;
END;
FUNCTION salariu_mediu(p_cod ANGAJATI.cod_sucursala%type, p_an
NUMBER)
RETURN NUMBER
AS
BEGIN
SELECT COUNT(ID_ANGAJAT),AVG(SALARIU)INTO p_count,p_sal FROM
ANGAJATI WHERE cod_sucursala=p_cod AND extract(year from
data_angajare)=p_an;
IF p_count=0 then return -1;
ELSE return p_sal;
END IF;
END;
FUNCTION suma_tranz
RETURN NUMBER
AS
BEGIN
SELECT COUNT(t.id_tranzactie), MIN(t.suma_tranzactionata)INTO
p_count, p_min
FROM CONTURI co, SUCURSALE s, TRANZACTII t
WHERE t.numar_cont=co.numar_cont AND
co.cod_sucursala=s.cod_sucursala AND s.denumire='BCR Piata
Revolutiei';
IF p_count=0 THEN RETURN -1;
ELSE RETURN p_min;
END IF;
END;
PROCEDURE vechime_ang
AS
BEGIN
OPEN c;
LOOP
FETCH c into rec_c;
EXIT WHEN c%ROWCOUNT>5 OR c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(rec_c.id_angajat||' '||rec_c.nume||'
'||rec_c.prenume||' '||rec_c.vechime);
END LOOP;
CLOSE c;
END;
PROCEDURE tranzactie_recenta(p_val OUT
TRANZACTII.SUMA_TRANZACTIONATA%type,p_data OUT
TRANZACTII.DATA%type)
AS
BEGIN
OPEN c1;
FETCH c1 INTO rec_c1;
p_val:=rec_c1.suma_tranzactionata;
p_data:=rec_c1.data;
CLOSE c1;
END;
PROCEDURE nr_ang
AS
39
BEGIN
FOR rec_c2 IN c2 LOOP
IF rec_c2.nr_angajati>2 THEN
DBMS_OUTPUT.PUT_LINE(rec_c2.cod_sucursala||' '||rec_c2.denumire||'
'||rec_c2.nr_angajati);
END IF;
END LOOP;
END;
END;
/
SHOW ERRORS
APEL:
EXECUTE my_pack.vechime_ang;
EXECUTE my_pack.nr_ang;
40
DECLANSATORI
1. Sa se creeze un declansator care sa nu permita adaugarea unui nivel al veniturilor mai mic de
15000 in tabela CLIENTI.
CREATE OR REPLACE TRIGGER nivel_ven
BEFORE
INSERT
ON CLIENTI
FOR EACH ROW
DECLARE
insert_except EXCEPTION;
BEGIN
IF :new.nivel_venituri<15000 THEN RAISE insert_except;
END IF;
EXCEPTION
WHEN insert_except THEN DBMS_OUTPUT.PUT_LINE('Nu se poate
introduce o valoare mai mica de 15000!');
END;
/
SHOW ERRORS
APEL:
INSERT INTO CLIENTI VALUES
(440,'ION','NITESCU','0789675218',TO_DATE('26-05-2013','DD-MM-
YYYY'),13000);
2. Sa se creeze un declansator care sa permita adaugarea, modificarea sau stergerea din tabela
TRANZACTII doar intre orele 10 si 18.
CREATE OR REPLACE TRIGGER tranzactie
BEFORE
INSERT OR UPDATE OR DELETE
ON TRANZACTII
FOR EACH ROW
DECLARE
exc EXCEPTION;
BEGIN
IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))>18 OR
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))<10 THEN RAISE exc;
END IF;
EXCEPTION
WHEN exc THEN DBMS_OUTPUT.PUT_LINE('Atentie! Nu se pot realiza
actualizari! Operatiuni permise doar in timpul programului!');
41
END;
/
SHOW ERRORS
INSERT INTO TRANZACTII VALUES (600,TO_DATE('23-05-2001','DD-MM-
YYYY'),2346,'RO41TREZ00420120101X');
3. Sa se creeze un declansator care sa actualizeze id-ul angajatului si in tabela CONTURI atunci cand
acesta este actualizat in tabela ANGAJATI.
CREATE OR REPLACE TRIGGER id_ang
BEFORE UPDATE OF id_angajat
ON ANGAJATI
FOR EACH ROW
BEGIN
UPDATE CONTURI
SET id_angajat=:new.id_angajat
WHERE id_angajat=:old.id_angajat;
END;
/
SHOW ERRORS
UPDATE ANGAJATI
SET ID_ANGAJAT=400
WHERE ID_ANGAJAT=330;
42
MACHETE DE INTRARE
1. Sa se creeze un videoformat ce prezinta conturile deschise si titularii acestora.
Se creeaza butonul ADAUGA, la apasarea acestuia (WHEN-BUTTON-PRESSED) adaugandu-se inregistrari (create_record). Pentru ca videoformatul sa aiba inregistrari, se creeaza triggerul WHEN-NEW-FORM-INSTANCE (execute_query);
2. Sa se creeze un videoformat ce prezinta tranzactiile efectuate si conturile pe care se efectueaza acestea.
43
MACHETE DE IESIRE
1. Sa se creeze un raport ce evidentiaza sucursalele bancii precum si angajatii din cadrul lor,
salariul mediu, minim si maxim al acestora.
44
2. Sa se creeze un raport in care sa se evidentieze clientii bancii precum si conturile asociate
fiecaruia.
top related