Post on 18-Oct-2019
1
ACADEMIA DE STUDII ECONOMICE BUCUREŞTIFACULTATEA DE CIBERNETICĂ, STATISTICĂ ŞI INFORMATICĂ ECONOMICĂ
Admitere 2019
BUCUREŞTI
Iulie 2019
Conf.univ.dr. Iuliana BOTHA
Tematica de concurs CSIE3
BAZE DE DATE RELAȚIONALE
2
Tematica de concurs CSIE3
NR.CRT
TEMATICA
REFERINŢA
BIBLIOGRAFICĂ
PAGINAŢIE
1. Baze de date
relaţionale
Modelul relaţional: structura relaţională a datelor, algebra şi calculul relaţional,
restricţii de integritate. Exemplificări în Oracle
[1] pag. 103-121
pag. 129-143
Realizarea bazelor de date relaţionale: analiza statică, dinamică şi funcţională;
proiectarea structurii conceptuale, logice şi fizice; normalizarea datelor
[1] pag. 144-186
pag. 197-203
2. Programarea în
limbajul SQL
Actualizarea structurii bazei de date: crearea obiectelor, modificarea
proprietăţilor şi ştergerea acestora
[3] pag. 101-122
Actualizarea datelor: adăugarea de înregistrări, modificarea valorilor, ştergerea înregistrărilor
[3] pag. 123-126
Interogarea datelor: condiţionarea datelor, utilizarea joncţiunilor şi a funcţiilor SQL, gruparea datelor, gestiunea subcererilor
[3] pag. 127-172
3. Programarea în
limbajul PL/SQL
Elemente de programare procedurală [2] pag. 9-52
Mecanismul de cursor [2] pag. 53-77
Gestiunea subprogramelor: proceduri și funcții [2] pag. 103-118
NR. CRT. REFERINŢA BIBLIOGRAFICĂ
[1] Lungu I., Bâra A., Bodea C., Botha I., Diaconiţa
V., Florea A., Velicanu A.
Tratat de baze de date. Vol I. Baze de date. Organizare, proiectare şi implementare, Editura ASE,
Bucureşti, 2011, ISBN 978-606-505-472-1, ISBN volum 978-606-505-481-3
[2] Bâra A., Botha I., Diaconiţa V., Lungu I., Velicanu
A.
Baze de date. Limbajul PL/SQL, Editura ASE, Bucureşti, 2009, ISBN 978-606-505-263-5
[3] Lungu I. Baze de date Oracle. Limbajul SQL, Editura ASE, Bucureşti, 2005, ISBN 973-594-684-X
Bibliografie
3
Agenda
◼ Modelul relaţional
Structura relațională datelor
Operatorii relaţionali
Restricţiile de integritate
◼ Realizarea bazelor de date relaţionale
Analiza statică, dinamică, funcţională
Proiectarea structurii conceptuale, logice, fizice
Normalizarea datelor
◼ Exemple de teste grilă **
Teste grilă propuse la examenele de admitere din sesiunile anterioare
Baze de date relaţionale
Modelul relaţional
Modelul de date relaţional
• Domeniu
• Relaţie
• Atribut
• Tuplu
• Cheie
• Schemă
Structura relațională a
datelor
•Operatori din algebra relaţională:
•Operatori de bază:
• universali: reuniunea, diferenţa, produsul cartezian
• specifici: proiecţia, selecţia şi joncţiunea
•Operatori derivaţi: intersecţia şi diviziunea
•Extensii ale algebrei relaţionale standard:
• complementarea unei relaţii, spargerea unei relaţii şi închiderea tranzitivă.
•Operatori din calculul relaţional:
• conective: conjuncţia, disjuncţia, negaţia
• cuantificatori: existenţial, universal
Operatorii relaționali
•Restricţii structurale (minimale):
•De unicitate a cheii
•Referenţială
•Entităţii
•Restricţii de comportament
Restricțiile de integritate
Operatorii relaționali
SELECT DISTINCT functia, salariu
FROM angajati;
SELECT *
FROM angajati
WHERE salariu>2000 or nume=‘Mircea’
Proiecţie
Selecţie
SELECT *
FROM angajati, comenzi;
Produs
cartezian
2
Operatorii relaționali
SELECT nume FROM angajati
UNION [ALL]
SELECT nume FROM clienti;
SELECT id_client FROM clienti
MINUS
SELECT id_client FROM comenzi;
Reuniune
Diferenţa
IntersecţieSELECT id_client FROM clienti
INTERSECT
SELECT id_client FROM comenzi;
Operatorii relaționali
SELECT *
FROM angajati a, comenzi c
WHERE a.id_ang=c.id_ang;
SELECT *
FROM angajati a JOIN comenzi c
ON a.id_ang=c.id_ang;
SELECT *
FROM angajati a JOIN comenzi c
USING (id_Ang);
SELECT *
FROM angajati a NATURAL JOIN comenzi c;
Joncțiune Operatorii relaționali
◼ Internă (inner join)
De egalitate
Naturală
De neegalitate
◼ Externă (outer join)
La stânga
La dreapta
Completă
Tipuri de joncțiuni:
Restricţiile de integritatesuportate în SQL-Oracle
◼ NOT NULL nu permite valori NULL (nespecificate) în coloanele unei tabele;
◼ UNIQUE nu permite valori duplicat în coloanele unei tabele;
◼ PRIMARY KEY nu permite valori duplicate sau NULL în coloana sau coloanele
definite astfel;
◼ FOREIGN KEY presupune ca fiecare valoare din coloana sau setul de coloane
definit astfel să aibă o valoare corespondentă identică în tabela de
legătură, tabelă în care coloana corespondentă este definită cu
restricţia UNIQUE sau PRIMARY KEY;
◼ CHECK elimină valorile care nu satisfac anumite cerinţe (condiţii) logice.
Baze de date relaţionale
Realizarea bazelor de date relaţionale
Definiţie BDR
◼ O bază de date relaţională (BDR)
reprezintă un ansamblu de relaţii (tabele)
de date împreună cu legăturile dintre ele.
3
Etape de realizare a unei BDR
◼ Analiza de sistem
◼ Proiectarea bazei de date
◼ Implementarea bazei de date
◼ Punerea în funcţiune şi exploatarea bazei de
date
◼ Întreţinerea bazei de date
14
Tehnica normalizării
◼ Normalizarea BD - trecerea succesivă a relaţiilor unei BDR
prin formele normale cunoscute, până la aducerea lor în
forma normală stabilită ca fiind optimă în contextul analizat
◼ Obiectivul normalizării îl constituie optimizarea structurii
BDR prin:
Eliminarea anomaliilor de actualizare a datelor;
Înlăturarea redundanţei datelor.
◼ Anomalia de ştergere = stergând un tuplu dintr-o
tabelă, pe lângă informaţiile şterse, se pierd şi
informaţiile utile existente în tuplul respectiv;
◼ Anomaliile de adăugare = nu pot fi incluse noi
informaţii necesare într-o tabelă deoarece nu se
cunosc şi alte informaţii utile;
◼ Anomalia de modificare = este dificil de modificat o
valoare a unui atribut atunci când ea apare în mai
multe tupluri.
Tehnica normalizăriiAnomaliile de actualizare
16
Tehnica normalizării
Forma normală unu (FN1)
◼ BDR se află în FN1 dacă toate relaţiile componente sunt în
FN1.
◼ O relaţie este în FN1 dacă valorile asociate atributelor se
află la nivel elementar (atomic) şi dacă nu există atribute
generatoare de valori repetitive.
17
Tehnica normalizării
Forma normală doi (FN2)
◼ BDR se află în FN2 dacă toate relaţiile componente sunt în
FN2.
◼ O relaţie este în FN2 dacă este în FN1 şi oricare dintre
atributele non-cheie este dependent funcţional complet de
atributele care formează cheia primară a relaţiei.
◼ FN2 interzice manifestarea unor dependenţe funcţionale
parţiale între atributele non-cheie şi cele care formează cheia
primară a relaţiei.
18
Tehnica normalizării
Forma normală trei (FN3)
◼ BDR se află în FN3 dacă toate relaţiile componente sunt în
FN3.
◼ O relaţie este în FN3 dacă este în FN2 şi atributele non-cheie
nu sunt dependente tranzitiv de cheia primară a relaţiei.
◼ FN3 interzice manifestarea dependenţelor funcţionale
tranzitive în cadrul relaţiei.
4
19
Precizați ce formă normală este încălcată:
PROD (denumire_produs, unitate_măsură,
caracteristici_tehnice, preț)
Precizați ce formă normală este încălcată:
PROD (denumire_produs, unitate_măsură, culoare,
categorie, preț, furnizor, localitate_furnizor)
Precizați ce formă normală este încălcată:
PROD (denumire_produs, unitate_măsură, culoare,
categorie, descriere_categorie, preț)
Tehnica normalizării
Exemple de teste grilă
21
Exemple de teste grilă
22
Exemple de teste grilă
23
Exemple de teste grilă
24
Exemple de teste grilă
5
25
Exemple de teste grilă
26
Exemple de teste grilă
ACADEMIA DE STUDII ECONOMICE BUCUREŞTIFACULTATEA DE CIBERNETICĂ, STATISTICĂ ŞI INFORMATICĂ ECONOMICĂ
Admitere 2019
BUCUREŞTI
Iulie 2019
Conf.univ.dr. Iuliana BOTHA
Tematica de concurs CSIE3
PROGRAMARE ÎN LIMBAJUL SQL
28
Tematica de concurs CSIE3
NR. CRT. REFERINŢA BIBLIOGRAFICĂ
[1] Lungu I., Bâra A., Bodea C., Botha I., Diaconiţa
V., Florea A., Velicanu A.
Tratat de baze de date. Vol I. Baze de date. Organizare, proiectare şi implementare, Editura ASE,
Bucureşti, 2011, ISBN 978-606-505-472-1, ISBN volum 978-606-505-481-3
[2] Bâra A., Botha I., Diaconiţa V., Lungu I., Velicanu
A.
Baze de date. Limbajul PL/SQL, Editura ASE, Bucureşti, 2009, ISBN 978-606-505-263-5
[3] Lungu I. Baze de date Oracle. Limbajul SQL, Editura ASE, Bucureşti, 2005, ISBN 973-594-684-X
Bibliografie
NR.
CRTTEMATICA
REFERINŢA
BIBLIOGRAFICĂ
PAGINAŢIE
1. Baze de date
relaţionale
Modelul relaţional: structura relaţională a datelor, algebra şi calculul relaţional,
restricţii de integritate. Exemplificări în Oracle
[1] pag. 103-121
pag. 129-143
Realizarea bazelor de date relaţionale: analiza statică, dinamică şi funcţională;
proiectarea structurii conceptuale, logice şi fizice; normalizarea datelor
[1] pag. 144-186
pag. 197-203
2. Programarea în
limbajul SQL
Actualizarea structurii bazei de date: crearea obiectelor, modificarea
proprietăţilor şi ştergerea acestora
[3] pag. 101-122
Actualizarea datelor: adăugarea de înregistrări, modificarea valorilor, ştergerea înregistrărilor
[3] pag. 123-126
Interogarea datelor: condiţionarea datelor, utilizarea joncţiunilor şi a funcţiilor SQL, gruparea datelor, gestiunea subcererilor
[3] pag. 127-172
3. Programarea în
limbajul PL/SQL
Elemente de programare procedurală [2] pag. 9-52
Mecanismul de cursor [2] pag. 53-77
Gestiunea subprogramelor: proceduri și funcții [2] pag. 103-118
29
Agenda
◼ Introducere în limbajul SQL
◼ Actualizarea structurii bazei de date: crearea obiectelor,
modificarea proprietăţilor şi ştergerea acestora
◼ Actualizarea datelor: adăugarea de înregistrări,
modificarea valorilor, ştergerea înregistrărilor
◼ Interogarea datelor: condiţionarea datelor, utilizarea
joncţiunilor şi a funcţiilor SQL, gruparea datelor,
gestiunea subcererilor
◼ Exemple de teste grilă **
Teste grilă propuse la examenele de admitere din sesiunile anterioare30
SQL (Structured Query Language)
◼ limbaj de descriere şi manipulare acceptat de toate sistemele de
gestiune a bazelor de date relaţionale
◼ standard pentru limbajele de interogare a bazelor de date
relaţionale
Limbajul SQL
6
31
Comandă SQL Descriere
CREATE creează structura unui obiect al BD
ALTER modifică structura unui obiect existent al BD
DROP şterge un obiect al BD
Limbajul SQL-OracleActualizarea structurii bazei de date
32
◼ Restricţiile de integritate în Oracle:
➢ PRIMARY KEY
➢ FOREIGN KEY
➢ UNIQUE
➢ NOT NULL
➢ CHECK
Limbajul SQL-Oracle.Crearea tabelelor
33
Ex. de creare cu indicarea restricţiilor de integritate la nivel de tabelă
CREATE TABLE angajati (
marca NUMBER(4),
nume VARCHAR2(20),
prenume VARCHAR2(20),
email VARCHAR2(20),
data_angajare DATE DEFAULT SYSDATE,
salariu NUMBER(8,2),
id_departament NUMBER(3),
CONSTRAINT pkAng PRIMARY KEY (marca),
CONSTRAINT uqMail UNIQUE (email),
CONSTRAINT ckMail CHECK (email LIKE '%@%.%'),
CONSTRAINT nnNume CHECK (nume IS NOT NULL),
CONSTRAINT nnPrenume CHECK (prenume IS NOT NULL),
CONSTRAINT fkDep FOREIGN KEY(id_departament) REFERENCES
departamente (id_departament) ON DELETE CASCADE );
Limbajul SQL-Oracle.Crearea tabelelor
34
◼ Modificarea structurii unei tabele constă din:
adăugarea unor coloane noi într-o tabelă existentă (eventual cu
indicarea de restricţii sau de valori implicite)
modificarea coloanelor unei tabele
specificarea unor restricţii pentru coloane existente
activarea, dezactivarea sau suprimarea unor restricţii de integritate
redenumiri ale coloanelor sau redenumirea tabelei
Limbajul SQL-Oracle.Modificarea structurii tabelelor
35
◼ Ştergerea unei tabele presupune:
ştergerea definiţiei sale din dicţionarul BD
ştergerea indecşilor asociaţi tabelei
ştergerea privilegiilor conferite în legătură cu tabela
eliberarea spaţiului de memorie ocupat
invalidarea funcţiilor, procedurilor, tabelelor virtuale, sinonimelor
referitoare la tabelă
◼ Ştergerea unei tabele este ireversibilă.
Limbajul SQL-Oracle.Ştergerea tabelelor
36
Comandă Descriere
INSERT adaugă o înregistrare nouă într-o tabelă
UPDATE modifică valori asociate coloanelor unei tabele
MERGErealizează fie modificări ale datelor, fie adăugări de înregistrări
dintr-o altă tabelă, în funcţie de o condiţie de potrivire
DELETE şterge înregistrări dintr-o tabelă
Limbajul SQL-OracleActualizarea datelor
7
37
Limbajul SQL-OracleInterogarea datelor
Clauze în comanda SELECT Descriere
WHERE condiţierestricţionează liniile care se returnează pe baza unui
criteriu specificat în condiţia de selecţie
clauze de ordonare ierarhicăstructurează rezultatul într-o manieră ierarhică
(asemănător cu o organigramă)
GROUP BY coloane_grupare
grupează liniile în scopul identificării valorilor comune
grupurilor (valori agregate calculate prin funcţii de
grup)
HAVING condiţierestricţionează grupurile create prin clauza GROUP
BY pe baza unei condiţii asupra funcţiilor de grup
ORDER BY coloane_ordonare sortează liniile (implicit ascendent)
Comandă SQL Descriere
SELECT regăseşte date din una sau mai multe tabele
38
Limbajul SQL-OraclePrecedenţa operatorilor
Operator
* , / , - , +
= , >= , > , <= , < , <> , != , IS , LIKE , IN
BETWEEN
NOT
AND
|| , OR
39
Limbajul SQL-OracleFuncţii SQL
Sintaxă Rezultat
UPPER (s) / LOWER (s) şir de caractere
SUBSTR (s,poz,n) şir de caractere
CONCAT (s1,s2) şir de caractere
Ex. de funcţii SQL care manipulează şiruri de caractere
Ex. de funcţii SQL care manipulează valori numerice
Sintaxă Rezultat
ROUND (n,i) număr
TRUNC (n,i) număr
MOD (n1,n2) număr
40
Limbajul SQL-OracleFuncţii SQL
Sintaxă Rezultat
SYSDATE dată calendaristică
ROUND (d,i) dată calendaristică
TRUNC (d,i) dată calendaristică
MONTHS_BETWEEN (d1,d2) număr
EXTRACT (DAY FROM d)
EXTRACT (MONTH FROM d)
EXTRACT (YEAR FROM d)număr
Ex. de funcţii SQL care manipulează date calendaristice
41
Limbajul SQL-OracleFuncţii SQL
Sintaxă
NVL (e1,e2)
NULLIF (e1,e2)
DECODE (e,expresii_de_căutare,d)
CASE expr WHEN cond THEN rez ...
ELSE rez END
Ex. de funcţii SQL speciale Ex. de funcţii SQL de conversie
între tipuri de date
Sintaxă
TO_NUMBER(s,format)
TO_CHAR (n,format)
TO_CHAR (d,format)
TO_DATE (s,format)
TO_TIMESTAMP (s,format)
42
Limbajul SQL-OracleFuncţii SQL
Sintaxă
COUNT(e)
SUM(e)
MIN(e)
MAX(e)
AVG(e)
Funcţii SQL de grup
8
43
Limbajul SQL-OracleAgregarea datelor
Clauze în SELECT Descriere
GROUP BY coloane_gruparegrupează liniile în scopul identificării valorilor comune
grupurilor (valori agregate calculate prin funcţii de grup)
HAVING condiţierestricţionează grupurile create prin clauza GROUP BY
pe baza unei condiţii asupra funcţiilor de grup
44
Limbajul SQL-OracleJoncţiuni
Oracle Standard SQL
Joncţiune de egalitate
SELECT t1.a, t2.c
FROM tabela1 t1, tabela2 t2
WHERE t1.b=t2.b;
SELECT t1.a, t2.c
FROM tabela1 t1 JOIN tabela2 t2
ON t1.b=t2.b;
45
Limbajul SQL-OracleJoncţiuni
Oracle Standard SQL
Joncţiune externă stânga
SELECT t1.a, t2.c
FROM tabela1 t1, tabela2 t2
WHERE t1.b=t2.b (+);
SELECT t1.a, t2.c
FROM tabela1 t1 LEFT JOIN tabela2 t2
ON t1.b=t2.b;
Joncţiune externă dreapta
SELECT t1.a, t2.c
FROM tabela1 t1, tabela2 t2
WHERE t1.b (+)=t2.b;
SELECT t1.a, t2.c
FROM tabela1 t1 RIGHT JOIN tabela2 t2
ON t1.b=t2.b;
46
Limbajul SQL-OracleJoncţiuni
Oracle Standard SQL
Joncţiune externă completă
SELECT t1.a, t2.c
FROM tabela1 t1, tabela2 t2
WHERE t1.b=t2.b (+)
UNION
SELECT t1.a, t2.c
FROM tabela1 t1, tabela2 t2
WHERE t1.b (+)=t2.b;
SELECT t1.a, t2.c
FROM tabela1 t1 FULL JOIN tabela2 t2
ON t1.b=t2.b;
47
Limbajul SQL-OracleInterogarea datelor. Subcereri.
Subcerere (cerere imbricată) – comandă SELECT inclusă într-o altă
comandă SQL, care poate returna una sau mai multe linii.
SELECT id_angajat, nume
FROM angajati
WHERE id_functie = (SELECT id_functie FROM angajati
WHERE UPPER(nume)='KING')
AND UPPER(nume)!='KING';
ORA-01427: single-row subquery returns more than one row
SELECT id_angajat, nume
FROM angajati
WHERE id_functie IN (SELECT id_functie FROM angajati
WHERE UPPER(nume)='KING')
AND UPPER(nume)!='KING'; 48
Limbajul SQL-OracleInterogarea datelor. Subcereri.
Operatori Descriere
IN• compară cu o listă de valori
• verifică dacă valoarea căutată se regăseşte în listă
NOT • utilizat cu operatorul IN
ANY
• utilizat în combinaţie cu operatorii de comparaţie (=, <)
• verifică dacă valoarea căutată îndeplineşte condiţia de comparaţie
cu oricare dintre liniile returnate de subcerere
SOME • la fel ca operatorul ANY
ALL
• utilizat în combinaţie cu operatorii de comparaţie (=, <)
• verifică dacă valoarea căutată îndeplineşte condiţia de comparaţie
cu toate liniile returnate de subcerere
Operatori de comparaţie utilizaţi în cadrul subcererilor multiple-row:
9
• ANY compară valoarea cu oricare valoare returnată de
interogare
• ALL compară valoarea cu toate valorile returnate de
interogare
Semnificaţia operatorilor în combinaţie cu operatorii de
comparație:
<ANY() – mai mic decât maximul
>ANY() – mai mare decât minimul
=ANY() – echivalent cu operatorul IN
>ALL() – mai mare decât maximul
<ALL() – mai mic decât minimul
49
Limbajul SQL-OracleInterogarea datelor. Subcereri.
Exemple de teste grilă
Rezolvaţi!
51
Rezolvaţi!
52
Rezolvaţi!
53
Rezolvaţi!
54
11
Rezolvaţi!
61
Rezolvaţi!
62
ACADEMIA DE STUDII ECONOMICE BUCUREŞTIFACULTATEA DE CIBERNETICĂ, STATISTICĂ ŞI INFORMATICĂ ECONOMICĂ
Admitere 2019
BUCUREŞTI
Iulie 2019
Conf.univ.dr. Iuliana BOTHA
Tematica de concurs CSIE3
PROGRAMARE ÎN LIMBAJUL PL/SQL
64
Tematica de concurs CSIE3
NR.
CRTTEMATICA
REFERINŢA
BIBLIOGRAFICĂ
PAGINAŢIE
1. Baze de date
relaţionale
Modelul relaţional: structura relaţională a datelor, algebra şi calculul relaţional,
restricţii de integritate. Exemplificări în Oracle
[1] pag. 103-121
pag. 129-143
Realizarea bazelor de date relaţionale: analiza statică, dinamică şi funcţională;
proiectarea structurii conceptuale, logice şi fizice; normalizarea datelor
[1] pag. 144-186
pag. 197-203
2. Programarea în
limbajul SQL
Actualizarea structurii bazei de date: crearea obiectelor, modificarea
proprietăţilor şi ştergerea acestora
[3] pag. 101-122
Actualizarea datelor: adăugarea de înregistrări, modificarea valorilor, ştergerea înregistrărilor
[3] pag. 123-126
Interogarea datelor: condiţionarea datelor, utilizarea joncţiunilor şi a funcţiilor SQL, gruparea datelor, gestiunea subcererilor
[3] pag. 127-172
3. Programarea în
limbajul PL/SQL
Elemente de programare procedurală [2] pag. 9-52
Mecanismul de cursor [2] pag. 53-77
Gestiunea subprogramelor: proceduri și funcții [2] pag. 103-118
NR. CRT. REFERINŢA BIBLIOGRAFICĂ
[1] Lungu I., Bâra A., Bodea C., Botha I., Diaconiţa
V., Florea A., Velicanu A.
Tratat de baze de date. Vol I. Baze de date. Organizare, proiectare şi implementare, Editura ASE,
Bucureşti, 2011, ISBN 978-606-505-472-1, ISBN volum 978-606-505-481-3
[2] Bâra A., Botha I., Diaconiţa V., Lungu I., Velicanu
A.
Baze de date. Limbajul PL/SQL, Editura ASE, Bucureşti, 2009, ISBN 978-606-505-263-5
[3] Lungu I. Baze de date Oracle. Limbajul SQL, Editura ASE, Bucureşti, 2005, ISBN 973-594-684-X
Bibliografie
65
Agenda
◼ Introducere în limbajul PL/SQL
◼ Elemente de programare procedurală
◼ Mecanismul de cursor în SGBD Oracle
◼ Gestiunea subprogramelor în SGBD Oracle
◼ Exemple de teste grilă *
*Teste grilă propuse la examenele de admitere din sesiunile anterioare
66
Introducere în limbajul PL/SQL
◼ PL/SQL este un limbaj de programare procedurală care extinde
limbajul descriptiv SQL şi care conţine următoarele elemente:
Blocuri anonime
Proceduri
Funcţii
Pachete care grupează funcţii şi proceduri
DeclanşatoriStructura generală a unui bloc PL/SQL
optimizarea cererii de regăsire a datelor
DECLARE --opţional
--secţiunea declarativă a blocului;
--cuprinde declaraţii de variabile, cursori, excepţii...;
BEGIN --obligatoriu
--secţiunea executabilă a blocului;
--cuprinde comenzi descriptive SQL, comenzi procedurale şi
structuri de programare PL/SQL;
EXCEPTION --opţional
--secţiunea de tratare a excepţiilor;
--cuprinde acţiuni care se execută în momentul apariţiei unei
excepţii sau erori;
END; --obligatoriu
12
Blocuri PL/SQL
Bloc fără secțiunile declarativă şi de tratare a excepțiilor:BEGIN
DBMS_OUTPUT.PUT_LINE('SGBD Oracle');
END;
Bloc cu secțiune declarativă, dar fără secțiune de tratare aexcepțiilor:DECLARE
v_data DATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Data curentă: '|| v_data);
END;
67
Blocuri PL/SQL
Bloc cu toate cele trei secţiuni:DECLARE
x NUMBER := &p_x; --valoarea lui x este citită de la tastatură
prin variabila de substituție p_x
y NUMBER := &p_y;
BEGIN
DBMS_OUTPUT.PUT_LINE(x/y);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(Impartire la 0!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Alta eroare!');
END;
/68
◼ În cadrul instrucţiunilor SQL sunt suportate toate tipurile de funcţii
SQL (inclusiv funcţiile de grup în cadrul instrucţiunii SELECT);
◼ Instrucţiunile PL/SQL:
suportă funcţii la nivel de înregistrare (single-row): numerice,
caracter, data, de conversie etc.;
NU suportă funcţii de grup (SUM, MIN, MAX, AVG, COUNT,
STDDEV) sau funcţia DECODE.
Funcţii SQL suportate
Comenzi SQL care pot fi utilizate direct în PL/SQL
❑ LMD
✓ SELECT
✓ INSERT, UPDATE, DELETE, MERGE
❑ LCT
✓ COMMIT, SAVEPOINT, ROLLBACK
Comenzi SQL care NU pot fi utilizate direct în PL/SQL
❑ LDD (CREATE, ALTER, DROP)
❑ LCD (GRANT, REVOKE)
Notă: acestea se pot folosi în cadrul blocurilor numai dacă sunt utilizate în cadrul
comenzii PL/SQL: EXECUTE IMMEDIATE
Exemplu: BEGIN
EXECUTE IMMEDIATE 'DROP TABLE produse';
END;
/
Comenzi SQL în PL/SQL Blocuri PL/SQL
BEGIN
DELETE FROM clienti WHERE id_client=110;
INSERT INTO
clienti(id_client,prenume_client,nume_client)
VALUES (100,'&prenume','&nume');
UPDATE clienti
SET limita_credit = 25000
WHERE id_client = 222;
COMMIT;
END;
/
71
Blocuri PL/SQL
DECLARE
v_nume VARCHAR2(20);
v_prenume VARCHAR2(20);
BEGIN
SELECT nume, prenume
INTO v_nume, v_prenume
FROM angajati
WHERE id_angajat=101;
DBMS_OUTPUT.PUT_LINE(v_nume ||' '|| v_prenume);
END;
/
72
13
Blocuri PL/SQL
DECLARE
a NUMBER(7);
b NUMBER(7);
BEGIN
SELECT SUM(salariul),MAX(salariul)
INTO a,b
FROM angajati;
DBMS_OUTPUT.PUT_LINE('a=' ||a);
DBMS_OUTPUT.PUT_LINE('b=' ||b);
END;
/
73 74
Elemente de programare proceduralăStructuri fundamentale de programare
Structura alternativă
IF conditie_1 THEN
secventa_comenzi_1;
ELSE
secventa_comenzi_2;
END IF;IF conditie_1 THEN
secventa_comenzi_1;
ELSIF conditie_2 THEN
secventa_comenzi_2;
ELSIF conditie_3 THEN
secventa_comenzi_3;
---------
ELSIF conditie_n THEN
secventa_comenzi_n;
ELSE
secventa_comenzi_n+1;
END IF;
CASE [selector]
WHEN expresie_1 THEN
actiune_1;
WHEN expresie_2 THEN
actiune_2;
--------
WHEN expresie_n THEN
actiune_n;
[ELSE actiune_n+1];
END CASE;
Structura
alternativă
variabila:=
CASE [selector]
WHEN expresie_1 THEN rezultat_1
WHEN expresie_2 THEN rezultat_2
--------
WHEN expresie_n THEN rezultat_n
[ELSE rezultat_n+1]
END; 75
Structura repetitivă
LOOP
secventa_comenzi;
EXIT [WHEN conditie];
END LOOP;
WHILE conditie LOOP
secventa_comenzi_1;
secventa_comenzi_2;
[EXIT [WHEN conditie]];
END LOOP;
FOR n IN [REVERSE]
min..max LOOP
secventa_comenzi;
[EXIT [WHEN conditie]];
END LOOP;
Elemente de programare proceduralăStructuri fundamentale de programare
76
◼ În PL/SQL se utilizează două tipuri de cursori:
implicit: declarat pentru toate instrucţiunile PL/SQL de tip LMD
(INSERT/UPDATE/DELETE/SELECT care returnează o singură
linie);
explicit: declarat şi gestionat de programator pentru a procesa
individual fiecare linie returnată de o instrucţiune SELECT care
returnează mai multe înregistrări.
Mecanismul de cursor în SGBD ORACLE
77
◼ Atributele cursorului implicit, prin care se testează modul de execuţie a
comenzilor LMD:
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
Mecanismul de cursor în SGBD ORACLECursorul implicit
78
BEGIN
UPDATE angajati
SET comision=0.2*salariul
WHERE salariul < 2000;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Nu exista angajati cu salariul mai
mic decat limita indicata');
ELSE
DBMS_OUTPUT.PUT_LINE('S-a modificat comisionul pentru '||
SQL%ROWCOUNT || ' angajati');
END IF;
COMMIT;
END;
/
Mecanismul de cursor în SGBD ORACLECursorul implicit
14
79
◼ Prelucrarea cursorului explicit presupune parcurgerea următoarelor etape:
1. Declararea variabilelor
2. Declararea cursorului, specificând fraza SELECT
3. Deschiderea cursorului
4. Încărcarea liniei curente din cursor în variabile
5. Închiderea cursorului
◼ Atributele cursorului explicit:
nume_cursor%ROWCOUNT
nume_cursor%FOUND
nume_cursor%NOTFOUND
nume_cursor%ISOPEN
Mecanismul de cursor în SGBD ORACLECursorul explicit
CURSOR nume_cursor IS SELECT .....;
OPEN nume_cursor;
FETCH nume_cursor INTO var1, var2, ...;
CLOSE nume_cursor;
80
DECLARE
CURSOR c IS SELECT id_angajat, nume, salariul FROM
angajati WHERE id_departament=60;
v_id angajati.id_angajat%TYPE;
v_nume angajati.nume%TYPE;
v_sal angajati.salariul%TYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO v_id, v_nume, v_sal;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Salariatul '||v_nume||' are
salariul: '||v_sal);
END LOOP;
CLOSE c;
END;
/
Mecanismul de cursor în SGBD ORACLECursorul explicit
81
Gestiunea subprogramelor în SGBD ORACLEProceduri
CREATE OR REPLACE PROCEDURE modifica_pret
( p_id IN produse.id_produs%TYPE, p_nr IN NUMBER )
AS
v_nr_prod NUMBER;
BEGIN
SELECT COUNT(*) INTO v_nr_prod
FROM produse WHERE id_produs=p_id;
IF v_nr_prod = 1 THEN
UPDATE rand_comenzi
SET pret = pret * p_nr
WHERE id_produs=p_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
END;
/
EXECUTE modifica_pret (100, 2);
82
Gestiunea subprogramelor în SGBD ORACLEFuncții
CREATE OR REPLACE FUNCTION vechime (p_id angajati.id_angajat%TYPE)
RETURN NUMBER
IS
v_vechime NUMBER;
BEGIN
SELECT ROUND((SYSDATE-data_angajare)/365) INTO v_vechime
FROM angajati WHERE id_angajat=p_id;
RETURN v_vechime;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN -1;
END;
/
DECLARE
v_vechime NUMBER;
BEGIN
v_vechime:=vechime(100);
IF v_vechime>0 THEN DBMS_OUTPUT.PUT_LINE(v_vechime);
ELSIF v_vechime=0 THEN DBMS_OUTPUT.PUT_LINE('Nu exista angajatul!');
ELSE DBMS_OUTPUT.PUT_LINE('Eroare! - '||SQLERRM);
END IF;
END;
/83
◼ Atenţie! Funcţiile utilizate în expresii SQL nu trebuie să conţină comenzi
LMD (update, delete, insert), comenzi LDD (create, alter, drop), comenzi
pentru controlul tranzacţiilor (commit, rollback) şi nici nu trebuie să apeleze
alte subprograme care să încalce aceste restricţii.
Gestiunea subprogramelor în SGBD ORACLEFuncţii
84
Exemple de teste grilă
15
85
Exemple de teste grilă
86
Exemple de teste grilă
87
Exemple de teste grilă
88
Exemple de teste grilă Rezolvaţi!
89 90
Exemple de teste grilă