169 Capitolul 8: SQL În acest capitol vor fi prezentate pe larg comanda de regăsire a datelor SELECT, comenzile de manipulare a datelor INSERT,… [629663]

169 Capitolul 8: SQL În acest capitol vor fi prezentate pe larg comanda de regăsire a datelor SELECT, comenzile de manipulare a datelor INSERT, UPDATE, DELETE, precum și comanda TRUNCATE. Exemplele din acest capitol sunt realizate cu ajutorul utilitarului SQL*Plus. 8.1 Comanda SELECT Regăsirea datelor stocate în baza de date este considerată cea mai importantă facilitate a unui SGBD. În SQL ea se realizează prin intermediul comenzii SELECT. Comanda SELECT este folosită doar pentru regăsirea datelor, ea neputându-le modifica. Așa cum am văzut mai înainte, comanda SELECT implementează toți operatorii algebrei relaționale. O instrucțiune SELECT cuprinde în mod obligatoriu cuvântul cheie FROM. Cu alte cuvinte, sintaxa minimală pentru comanda SELECT este: SELECT atribute FROM tabel După cuvântul cheie SELECT se specifică lista atributelor ce urmează a fi returnate ca rezultat al interogării, iar după cuvântul FROM se precizează obiectele (tabele, vederi, sinonime) din care se vor selecta aceste atribute. 8.1.1 Atributele comenzii SELECT În lista de atribute pot apărea: • toate coloanele din tabel sau vedere (în ordinea în care au fost definite în comanda CREATE TABLE / CREATE VIEW) prin utilizarea semnului * : SQL> SELECT * FROM profesor; COD NUME PRENUME DATA_NAST GRAD SEF SALARIU PRIMA COD_CATEDRA –– ––- –––– ––––– ––- –- –––– ––– ––––––– 100 GHEORGHIU STEFAN 11-AUG-46 PROF 3000 3500 10 101 MARIN VLAD 19-APR-45 PROF 100 2500 20 102 GEORGESCU CRISTIANA 30-OCT-51 CONF 100 2800 200 30 103 IONESCU VERONICA ASIST 102 1500 10 104 ALBU GHEORGHE LECT 100 2200 2500 20 105 VOINEA MIRCEA 15-NOV-65 ASIST 100 1200 150 10 106 STANESCU MARIA 05-DEC-69 ASIST 103 1200 600 20 • numele coloanelor separate prin virgulă. Acestea vor apărea în rezultatul interogării în ordinea în care sunt specificate: SQL> SELECT nume, prenume, salariu FROM profesor; NUME PRENUME SALARIU –––––––- ––––––– ––––- GHEORGHIU STEFAN 3000 MARIN VLAD 2500 GEORGESCU CRISTIANA 2800 IONESCU VERONICA 1500 ALBU GHEORGHE 2200 VOINEA MIRCEA 1200

170 STANESCU MARIA 1200 • atribute rezultate din evaluarea unor expresii. Aceste expresii pot conține nume de coloane, constante, operatori sau funcții. 8.1.2. Operatori aritmetici Operatorii aritmetici pot fi folosiți pentru a crea expresii având tipul de date numeric sau date calendaristice. Operatorii aritmetici sunt: + (adunare), – (scădere ), * (înmulțire), / (împărțire). Ordinea de precedență a operatorilor poate fi schimbată cu ajutorul parantezelor. De exemplu dacă în tabela PROFESOR ne interesează să calculăm impozitul aferent salariilor, știind că acesta este de 38%, putem scrie următoarea interogare: SQL> SELECT nume, salariu, salariu*0.38 FROM profesor; NUMES SALARIU SALARIU*0.38 ––––––– ––––––– ––––––– GHEORGHIU 3000 1140 MARIN 2500 950 GEORGESCU 2800 1064 IONESCU 1500 570 ALBU 2200 836 VOINEA 1200 456 STANESCU 1200 456 8.1.3. Aliasuri de coloane Pentru exemplul anterior observăm că în momentul afișării rezultatelor, SQL*Plus utilizează numele coloanelor ca antet. Când acest lucru poate face dificilă înțelegerea rezultatelor, se poate schimba antetul prin atribuirea altor nume coloanelor (numite ‘alias’-uri ale coloanei). Acest lucru se realizează specificând aliasul după numele coloanei. În cazul în care aliasul conține spații sau caractere speciale cum ar fi + sau -, acesta se va specifica între ghilimele. În exemplul de mai jos aliasul "DATA NASTERE" conține spații deci este specificat între ghilimele în timp ce aliasul IMPOZIT nu conține spații sau caractere speciale deci nu trebuie specificat obligatoriu între ghilimele. SQL> SELECT nume, data_nast "DATA NASTERE", salariu, salariu*0.38 IMPOZIT FROM profesor; NUME DATA NASTERE SALARIU IMPOZIT ––––––– –––––––– ––––– ––––- GHEORGHIU 11-AUG-46 3000 1140 MARIN 19-APR-45 2500 950 GEORGESCU 30-OCT-51 2800 1064 IONESCU 1500 570 ALBU 2200 836 VOINEA 15-NOV-65 1200 456 STANESCU 05-DEC-69 1200 456 8.1.4. Operatorul de concatenare

171 Operatorul de concatenare, notat ||, permite legarea coloanelor cu alte coloane, expresii aritmetice sau valori constante pentru a crea o expresie de tip șir de caractere. De exemplu, pentru a combina codul, numele si prenumele unui profesor, separate printr-un spațiu, se folosește următoarea interogare: SQL> SELECT cod || ' ' || nume || ' ' || prenume detalii_profesor FROM profesor; DETALII_PROFESOR ––––––––––––––- 100 GHEORGHIU STEFAN 101 MARIN VLAD 102 GEORGESCU CRISTIANA 103 IONESCU VERONICA 104 ALBU GHEORGHE 105 VOINEA MIRCEA 106 STANESCU MARIA 8.1.5 Convertirea valorilor Null cu ajutorul funcției NVL Dacă la o înregistrare pentru o anumită coloană valoarea este necunoscută sau neaplicabilă, atunci aceasta este Null. Această valoare nu trebuie confundată cu zero sau șirul de caractere format dintr-un spațiu. Așa cum am văzut în exemplele de până acum, dacă o anumită valoare este Null, SQL*Plus nu va afișa nimic. Pentru expresiile aritmetice, dacă una dintre valorile componente este Null, atunci și rezultatul expresiei este Null. De exemplu, pentru a calcula salariul total, ce reprezintă suma dintre coloanele salariu și prima putem folosi interogarea: SQL> SELECT nume, salariu, prima, salariu+prima “SALARIU TOTAL” FROM profesor; NUME SALARIU PRIMA SALARIU TOTAL ––––––– ––––– –––– –––––––– GHEORGHIU 3000 3500 6500 MARIN 2500 GEORGESCU 2800 200 3000 IONESCU 1500 ALBU 2200 2500 4700 VOINEA 1200 150 1350 STANESCU 1200 600 1800 Observăm că pentru acele înregistrări care au avut valoarea Null în câmpul prima expresia ce calculează salariul total returnează tot valoarea Null. Pentru a obține un rezultat diferit de Null, valorile Null trebuiesc convertite într-un număr (în cazul de față 0) înainte de a aplica operatorul aritmetic. Această convertire se poate realizează prin intermediul funcției NVL. Funcția NVL are două argumente. Dacă valoarea primului argument nu este Null, atunci NVL întoarce această valoare; dacă nu, ea întoarce valoarea celui de-al doilea argument. Cele două argumente pot avea orice tip de date. Dacă tipurile de date ale celor două argumente diferă, Oracle încercă să convertească al doilea argument la tipul de date al primului. De exemplu, pentru a putea calcula salariul total al tuturor cadrelor didactice, trebuie să convertim valoarea Null din coloana prima a tabelei PROFESOR în valoarea 0 folosind NVL(prima, 0): SQL> SELECT nume, salariu, prima, salariu+nvl(prima,0) “SALARIU TOTAL” FROM profesor;

172 NUME SALARIU PRIMA SALARIU TOTAL –––––– ––––- –––– –––––––– GHEORGHIU 3000 3500 6500 MARIN 2500 2500 GEORGESCU 2800 200 3000 IONESCU 1500 1500 ALBU 2200 2500 4700 VOINEA 1200 150 1350 STANESCU 1200 600 1800 8.1.6 Prevenirea selectării înregistrărilor duplicat O comanda SELECT care nu cuprinde cuvântul cheie DISTINCT va afișa toate înregistrările care rezultă din interogare, indiferent dacă una sau mai multe dintre ele sunt identice. De exemplu, interogarea de mai jos va returna următoarele rezultate: SQL> SELECT grad FROM profesor; GRAD –– PROF PROF CONF ASIST LECT ASIST ASIST În cazul folosirii cuvântului cheie DISTINCT înregistrările duplicat sunt eliminate, afișându-se numai prima apariție a valorilor câmpurilor specificate în lista de atribute. De exemplu: SQL> SELECT DISTINCT grad FROM profesor; GRAD –– ASIST CONF LECT PROF Dacă lista de atribute conține mai multe coloane, operatorul DISTINCT va afecta toate coloanele selectate. Următorul exemplu va afișa toate combinațiile de valori care sunt diferite pentru coloanele grad și cod_catedra. Exemplu: SQL> SELECT DISTINCT grad, cod_catedra FROM profesor; GRAD COD_CATEDRA ––– –––––––- ASIST 10 ASIST 20

173 CONF 30 LECT 20 PROF 10 PROF 20 8.1.7 Clauza ORDER BY În mod normal, în urma interogării înregistrările rezultate apar în aceeași ordine în care au fost introduse în baza de date. Pentru a modifica ordinea de afișare se utilizează clauza ORDER BY, care sortează înregistrările după valorile din una sau mai multe coloane. Această clauză este urmată de numele coloanelor după care se va face sortarea. De asemenea, este posibil să se identifice coloana dintr-o clauză ORDER BY folosind în locul numelui coloanei un număr ordinal ce reprezintă poziția coloanei în rezultat (de la stânga la dreapta). Această facilitate face posibilă ordonarea rezultatului interogării în funcție de un atribut al clauzei SELECT care poate fi o expresie complexă, fără a mai rescrie acea expresie. Nu există nici o limită a numărului de coloane în funcție de care se poate face sortarea. Nu este obligatoriu ca ordinea de sortare să se facă în funcție de o coloană care să fie afișată, dar în acest caz nu se mai poate folosi numărul de ordine al coloanei în loc de numele acesteia. Înregistrările vor fi sortate mai întâi în funcție de primul câmp specificat după clauza ORDER BY, apoi, înregistrările care au aceeași valoare în acest prim câmp sunt sortate în funcție de valoarea celui de-al doilea câmp specificat după clauza ORDER BY, ș.a.m.d. De exemplu, pentru a sorta ascendent înregistrările în funcție de impozitul pe salariu folosim interogarea: SQL> SELECT nume, salariu*0.38 FROM profesor ORDER BY salariu*0.38; care este echivalenta cu: SQL> SELECT nume, salariu*0.38 FROM profesor ORDER BY 2; NUME SALARIU*0.38 ––––––– ––––––- VOINEA 456 STANESCU 456 IONESCU 570 ALBU 836 MARIN 950 GEORGESCU 1064 GHEORGHIU 1140 Înregistrările sunt sortate în mod implicit în ordine ascendentă (opțiunea ASC), afișarea în ordine descendentă făcându-se prin utilizarea opțiunii DESC. Observați că în momentul sortării valoarea Null este considerată cea mai mare, deci dacă sortarea este ascendentă este trecută pe ultima poziție și dacă sortarea este descendentă este trecută pe prima poziție. De exemplu: SQL> SELECT grad, prima FROM profesor ORDER BY grad, prima DESC; GRAD PRIMA ––––- –––

174 ASIST ASIST 600 ASIST 150 CONF 200 LECT 2500 PROF PROF 3500 Se observă că în exemplul de mai sus înregistrările au fost mai întâi sortate ascendent (specificație implicită) în funcție de gradul didactic. Înregistrările cu același grad au fost apoi ordonate în funcție de cel de-al doilea criteriu de sortare, adică în funcție de prima primită cu specificația explicită de sortare descendentă. 8.1.8 Clauza WHERE Clauza WHERE se folosește pentru a regăsi înregistrări ce corespund unei anumite condiții evaluată cu valoarea de adevăr TRUE, adică pentru a realiza anumite restricții de selecție. Astfel, clauza WHERE corespunde restricțiilor operatorilor din algebra relațională. Cu alte cuvinte, dacă o clauză ORDER BY este o clauză de sortare, clauza WHERE este o clauză de filtrare. Dacă nu se specifică o clauză WHERE, interogarea va întoarce ca rezultat toate rândurile din tabel. Împreună cu clauza SELECT și FROM care sunt obligatorii, WHERE este cea mai folosită clauză din SQL. Din punct de vedere sintactic clauza WHERE este opțională, dar atunci când este introdusă urmează întotdeauna imediat după clauza FROM: SELECT nume coloană FROM tabel WHERE condiție Datorită existenței valorii Null, în SQL o condiție poate lua atât valorile TRUE și FALSE cât și valoarea Necunoscut (despre acest lucru vom discuta mai în detaliu în secțiunile următoare). O comandă SELECT cu clauza WHERE va returna toate înregistrările pentru care condiția are valoarea TRUE. Condiția clauzei WHERE poate cuprinde numele unor coloane, constante, operatori de comparație sau operatori logici (NOT, AND, OR). Operatorii de comparație se pot împarți în două categorii: operatori relaționali și operatori SQL. Toți acești operatori sunt trecuți în revistă în continuare. 8.1.9 Operatori relaționali Operatorii relaționali sunt: = egal > mai mare >= mai mare sau egal < mai mic <= mai mic sau egal <> și != diferit Cele două valori care sunt comparate trebuie să aparțină unor tipuri de date compatibile. De exemplu, pentru a selecta toate cadrele didactice care nu aparțin catedrei cu codul 10 folosim următoarea interogare: SQL> SELECT nume, prenume FROM profesor WHERE cod_catedra <>10;

175 NUME PRENUME –––––––- ––––– MARIN VLAD GEORGESCU CRISTIANA ALBU GHEORGHE STANESCU MARIA Șirurile de caractere și data calendaristică trebuiesc incluse între apostrofuri. De exemplu, pentru a selecta numai acele cadre didactice care au gradul didactic de profesor vom utiliza următoarea interogare: SQL> SELECT nume, prenume FROM profesor WHERE grad ='PROF'; NUME PRENUME ––––––– ––––– GHEORGHIU STEFAN MARIN VLAD În cazul șirurilor de caractere, literele mici sunt diferite de literele mari. De exemplu, următoarea interogare nu va returna nici o înregistrare: SQL> SELECT nume, prenume FROM profesor WHERE grad='prof'; Toți operatorii de comparație pot fi folosiți atât pentru valori numerice cât și pentru șiruri de caractere sau date calendaristice. De exemplu, pentru a afla toate cadrele didactice care s-au născut înainte de 1 Ianuarie 1960 folosim interogarea: SQL> SELECT nume, prenume, data_nast FROM profesor WHERE data_nast < '01-JAN-65'; NUME PRENUME DATA_NAST –––––––– –––––– –––––– GHEORGHIU STEFAN 11-AUG-46 MARIN VLAD 19-APR-45 GEORGESCU CRISTIANA 30-OCT-51 În cazul șirurilor de caractere ordonarea se face după codul ASCII al acestora. De exemplu, pentru a afla toate cadrele didactice ale căror nume sunt în ordinea alfabetică după litera ‘M’ se poate folosi interogarea. SQL> SELECT nume, prenume FROM profesor WHERE nume >= 'M'; NUME PRENUME ––––––– ––––– MARIN VLAD VOINEA MIRCEA STANESCU MARIA Observați că interogarea de mai sus este corectă numai în cazul în care numele angajaților începe cu o literă mare, literele mici fiind în spatele celor mari.

176 Există posibilitatea de a compara pentru o înregistrare valoarea unei coloane cu valoarea altei coloane. De exemplu, dacă dorim să selectăm acele cadre didactice care au primit primă mai mare decât salariul de bază vom avea: SQL> SELECT nume, prenume, salariu, prima FROM profesor WHERE salariu<prima; NUME PRENUME SALARIU PRIMA –––- –––- ––– ––– GHEORGHIU STEFAN 3000 3500 ALBU GHEORGHE 2200 2500 8.1.10 Operatori SQL Există patru tipuri de operatori SQL care pot opera cu toate tipurile de date: 1. BETWEEN…AND… 2. IN 3. LIKE 4. IS NULL Operatorul BETWEEN…AND… Operatorul BETWEEN…AND…permite specificarea unui domeniu mărginit de două valori între care trebuie să se afle valoarea testată. Domeniul de valori specificat este un interval închis iar limita inferioară trebuie specificată prima. Astfel, dacă dorim selectarea acelor cadre didactice care au salariul între 2000 și 3000 vom folosi comanda: SQL> SELECT nume, prenume, salariu FROM profesor WHERE salariu BETWEEN 2000 AND 3000; NUME PRENUME SALARIU –––- –––- ––– GHEORGHIU STEFAN 3000 MARIN VLAD 2500 GEORGESCU CRISTIANA 2800 ALBU GHEORGHE 2200 Operatorul IN Operatorul IN permite specificarea unei liste de valori, valoarea testată trebuind să se afle printre valorile acestei liste. De exemplu, dacă dorim selectarea cadrelor didactice care au gradul de conferențiar, lector sau asistent vom utiliza comanda: SQL> SELECT nume, prenume, grad FROM profesor WHERE grad IN ('CONF', 'LECT', 'ASIST'); NUME PRENUME GRAD

177 –––- –––- –– GEORGESCU CRISTIANA CONF IONESCU VERONICA ASIST ALBU GHEORGHE LECT VOINEA MIRCEA ASIST STANESCU MARIA ASIST Operatorul LIKE Operatorul LIKE permite specificarea unui anumit model de șir de caractere cu care trebuie să se potrivească valoarea testată. Acest operator se folosește în mod special atunci când nu se știe exact valoarea care trebuie căutată. Pentru a construi modelul după care se face căutarea pot fi folosite două simboluri: % semnifică orice secvență de zero sau mai multe caractere _ semnifică orice caracter (care apare o singură dată) De exemplu, următoarea comandă SELECT va returna toate cadrele didactice al căror nume începe cu litera ‘G’: SQL> SELECT nume, prenume FROM profesor WHERE nume LIKE 'G%'; NUME PRENUME –––- –––- GHEORGHIU STEFAN GEORGESCU CRISTIANA Dacă dorim selectarea acelor cadre didactice al căror nume are litera ‘O’ pe a doua poziție, indiferent de lungimea cuvântului, vom avea: SQL> SELECT nume, prenume FROM profesor WHERE nume LIKE '_O%'; NUME PRENUME –––- –––- IONESCU VERONICA VOINEA MIRCEA O problemă intervine atunci când șirul conține caracterele % sau _ (de exemplu șirul ‘J_James’) deoarece aceste caractere au semnificație predefinită. Pentru a schimba interpretarea acestor caractere se folosește opțiunea ESCAPE. De exemplu, pentru a căuta toate titlurile de carte care încep cu caracterele ‘J_’ se poate folosi interogarea: SQL> SELECT titlu FROM carte WHERE titlu LIKE 'J/_%' ESCAPE '/'; În exemplul de mai sus opțiunea ESCAPE identifică caracterul ‘/‘ ca fiind caracterul “escape”. Deoarece în modelul folosit pentru LIKE acest caracter precede caracterul ‘_’ acesta din urmă va fi interpretat ca o simplă literă, fără altă semnificație. Avantajul unei viteze mari de regăsire ca urmare a indexării este pierdut în momentul în care se caută un șir de caractere care începe cu “_” sau “%” într-o coloană indexată.

178 Operatorul IS NULL Operatorul IS NULL testează dacă valorile sunt Null. Pentru a vedea utilitatea acestui operator să considerăm următoarele interogări: SQL> SELECT nume, prenume FROM profesor WHERE prima = NULL; SQL> SELECT nume, prenume FROM profesor WHERE prima <> NULL; Amândouă aceste interogări nu vor returna nici o înregistrare. Aceste lucru pare surprinzător la prima vedere deoarece ne-am fi așteptat ca prima interogare să returneze toate cadrele didactice care nu au primit primă, iar a doua toate cadrele didactice care au primit primă. În SQL însă, orice condiție care este formată dintr-un operator de comparație care are unul dintre termeni valoarea Null va avea ca rezultat valoarea Necunoscut, diferită de valoarea TRUE (pentru care se face filtrarea). Pentru compararea cu Null se folosește operatorul special IS NULL. Deci pentru a afla cadrele didactice care nu au primit primă se folosește interogarea: SQL> SELECT nume, prenume FROM profesor WHERE prima IS NULL; NUME PRENUME –––- –––- MARIN VLAD IONESCU VERONICA La fel, pentru a afla cadrele didactice ale căror dată de naștere nu se cunoaște vom folosi următoarea interogare: SQL> SELECT nume, prenume FROM profesor WHERE data_nast IS NULL; NUME PRENUME –––- –––- IONESCU VERONICA ALBU GHEORGHE 8.1.11. Operatorii logici Negarea operatorilor În unele cazuri sunt mai ușor de căutat înregistrările care nu îndeplinesc o anumită condiție. Acest lucru se poate realiza folosind operatorul NOT. Operatorul NOT se poate folosi pentru negarea unei expresii logice (de exemplu expresii de tipul NOT coloana = ….) sau pentru negarea operatorilor SQL în modul următor: NOT BETWEEN NOT IN NOT LIKE IS NOT NULL. De exemplu, pentru a selecta cadrelor didactice al căror nume nu începe cu litera ‘G’ se folosește interogarea: SQL> SELECT nume, prenume FROM profesor WHERE nume NOT LIKE 'G%';

179 NUME PRENUME –––- –––- MARIN VLAD IONESCU VERONICA ALBU GHEORGHE VOINEA MIRCEA STANESCU MARIA Pentru a selecta cadrelor didactice care au primit prima se folosește interogarea: SQL> SELECT nume, prenume FROM profesor WHERE prima IS NOT NULL; NUME PRENUME –––- –––- GHEORGHIU STEFAN GEORGESCU CRISTIANA ALBU GHEORGHE VOINEA MIRCEA STANESCU MARIA Observație: Negarea unei expresii logice care are valoarea Necunoscut va avea tot valoare necunoscut. De exemplu, o expresie de genul NOT coloana = NULL va avea valoarea necunoscut, următoarea interogare nereturnând nici o înregistrare: SQL> SELECT nume, prenume FROM profesor WHERE NOT prima = NULL; Condiții multiple de interogare (operatorii AND și OR) Operatorii AND și OR pot fi utilizați pentru a realiza interogări ce conțin condiții multiple. Condiția ce conține operatorul AND este adevărată atunci când ambele condiții sunt adevărate iar condiția ce conține operatorul OR este adevărată atunci când cel puțin una din condiții este adevărată. În aceeași expresie logică se pot combina operatorii AND și OR dar operatorul AND are o precedență mai mare decât operatorul OR deci este evaluat mai întâi. În momentul evaluării unei expresii, se calculează mai întâi operatorii în ordinea precedenței, de la cel cu precedența cea mai mare până la cel cu precedența cea mai mică. Dacă operatorii au precedență egală atunci ei sunt calculați de la stânga la dreapta. Precedența operatorilor, pornind de la cea mai mare la cea mai mică este următoarea: • toți operatorii de comparație și operatorii SQL: >,<,<=,>=,=,<>, BETWEEN…AND…, IN, LIKE, IS NULL; • operatorul NOT; • operatorul AND; • operatorul OR. Pentru a schimba prioritatea operatorilor se folosesc parantezele. În exemplele de mai jos se observă modul de evaluare al expresiei în funcție de precedența operatorilor, precum și modul în care parantezele pot schimba acest lucru.

180 SQL> SELECT nume, prenume, salariu, cod_catedra FROM profesor WHERE salariu>2000 AND cod_catedra=10 OR cod_catedra=20; este echivalentă cu: SQL> SELECT nume, prenume, salariu, cod_catedra FROM profesor WHERE (salariu>2000 AND cod_catedra=10) OR cod_catedra=20; NUME PRENUME SALARIU COD_CATEDRA –––- –––- ––– –––– GHEORGHIU STEFAN 3000 10 MARIN VLAD 2500 20 ALBU GHEORGHE 2200 20 STANESCU MARIA 1200 20 SQL> SELECT nume, prenume, salariu, cod_catedra FROM profesor WHERE salariu>2000 AND (cod_catedra=10 OR cod_catedra=20); NUME PRENUME SALARIU COD_CATEDRA –––- –––- ––– –––– GHEORGHIU STEFAN 3000 10 MARIN VLAD 2500 20 ALBU GHEORGHE 2200 20 8.1.12 Funcții Funcțiile sunt o caracteristică importantă a SQL și sunt utilizate pentru a realiza calcule asupra datelor, modifica date, manipula grupuri de înregistrări, schimba formatul datelor sau pentru a converti diferite tipuri de date. Funcțiile se clasifică în două tipuri: 1. Funcții referitoare la o singură înregistrare: – funcții caracter; – funcții numerice; – funcții pentru data calendaristică și oră; – funcții de conversie; – funcții diverse. 2. Funcții referitoare la mai multe înregistrări: – funcții totalizatoare sau funcții de grup. Diferența dintre cele două tipuri de funcții este numărul de înregistrări pe care acționează. Funcțiile referitoare la o singură înregistrare returnează un singur rezultat pentru fiecare rând al tabelului, pe când funcțiile referitoare la mai multe înregistrări returnează un singur rezultat pentru fiecare grup de înregistrări din tabel. O observație importantă este faptul că dacă se apelează o funcție SQL ce are un argument egal cu valoarea Null, atunci în mod automat rezultatul va avea valoarea Null. Singurele funcții care nu respectă această regulă sunt: CONCAT, DECODE, DUMP, NVL și REPLACE. În continuare vom exemplifica și prezenta la modul general cele mai importante funcții, pentru sintaxă urmând să consultați Anexa 6. 8.1.13 Funcții referitoare la o singură înregistrare

181 Sunt funcții utilizate pentru manipularea datelor individuale. Ele pot avea unul sau mai multe argumente și returnează o valoare pentru fiecare rând rezultat în urma interogării. Funcții caracter Aceste funcții au ca argumente date de tip caracter și returnează date de tip VARCHAR2, CHAR sau NUMBER. Cele mai importante funcții caracter sunt: – CONCAT- returnează un șir de caractere format prin concatenarea a două șiruri; – LOWER – modifică toate caracterele în litere mici; – UPPER – modifică toate caracterele în litere mari; – LENGTH – returnează numărul de caractere dintr-un anumit câmp; – REPLACE – caută într-un șir de caractere un subșir iar dacă îl găsește îl va înlocui cu un alt șir de caractere; – SUBSTR – returnează un subșir de caractere având o anumită lungime începând cu o anumită poziție; – TRANSLATE – caută într-un șir de caractere un caracter iar dacă îl găsește îl va înlocui cu un alt caracter; Exemplu de utilizare a funcției LENGTH: SQL> SELECT LENGTH (nume) FROM profesor; LENGTH(NUME) –––– 9 5 9 7 4 6 8 Spre deosebire de alte funcții, funcțiile caracter pot fi imbricate până la orice adâncime. Dacă funcțiile sunt imbricate ele sunt evaluate din interior spre exterior. Pentru a determina, de exemplu, de câte ori apare caracterul ‘A’ în câmpul nume vom folosi interogarea: SQL>SELECT nume, LENGTH (nume)-LENGTH (TRANSLATE (nume,’DA’,’D’)) “‘A’” FROM profesor; NUME 'A' –––- ––– GHEORGHIU 0 MARIN 1 GEORGESCU 0 IONESCU 0 ALBU 1 VOINEA 1 STANESCU 1

182 Observație: În exemplul de mai sus, funcția TRANSLATE (nume,’DA’,’D’) va căuta în coloana nume primul caracter (caracterul ‘D’) din cel de-al doilea argument al funcției (șirul de caractere ‘DA’) și îl va înlocui cu primul caracter (adică tot cu caracterul ‘D’) din cel de-al treilea argument al funcției (șirul de caractere ‘D’), apoi va căuta cel de-al doilea caracter, adică caracterul ‘A’, și îl va șterge din câmpul nume deoarece acesta nu are caracter corespondent în cel de-al treilea argument al funcției. Am folosit acest artificiu deoarece șirul de caractere vid este echivalent cu valoarea Null deci funcția TRANSLATE (nume,’A’,’’) ar fi înlocuit toate valorile câmpului nume cu valoarea Null. Funcții numerice sau aritmetice Aceste funcții au ca argumente date numerice și returnează tot valori numerice. Marea majoritate a acestor funcții au o precizie de 38 de zecimale (COS, EXP, LN, LOG, SIN SQRT, TAN au însă o precizie de 36 de zecimale). Dintre cele mai importante funcții amintim: – ROUND – rotunjește valorile la un anumit număr de poziții zecimale; – TRUNC – trunchiază valorile un anumit număr de poziții zecimale; – CEIL – returnează cel mai mic întreg mai mare sau egal cu o anumită valoare; – FLOOR – returnează cel mai mare întreg mai mic sau egal cu o anumită valoare; – SIGN – returnează valoarea -1 dacă valoarea argumentului primit este mai mică decât 0, 1 dacă valoarea argumentului primit este mai mare decât 0 și 0 dacă valoarea argumentului primit este egală cu 0; – SQRT – returnează rădăcina pătrată a argumentului primit; – ABS – returnează valoarea absolută a argumentului primit; – POWER – returnează valoarea unui număr ridicat la o anumită putere; – MOD – returnează restul împărțirii a două numere; – alte funcții matematice cum ar fi: LOG, SIN, TAN, COS, EXP, LN. Funcții pentru dată calendaristică și oră În Oracle datele de tip dată calendaristică sunt reprezentate sub un format numeric reprezentând: ziua, luna, anul, ora, minutul, secunda și secolul. Oracle poate manevra date calendaristice de la 1 ianuarie 4712 BC până la 31 decembrie 4712 AD. Modul predefinit de afișare și introducere este sub forma: DD-MON-YY (ex. 31-Dec-99). Aceasta categorie de funcții operează pe valori de tip dată calendaristică, rezultatul returnat fiind tot de tip dată calendaristică, excepție făcând funcția MONTHS_BETWEEN care returnează o valoare numerică. Cele mai des întâlnite funcții sunt: – ADD_MONTH – returnează o dată calendaristică formată prin adăugarea la data calendaristica specificata a unui anumit număr de luni; – LAST_DAY – întoarce ca rezultat ultima zi a unei luni specificate; – MONTHS_BETWEEN – returnează numărul de luni dintre două date calendaristice specificate; – NEXT_DAY – returnează o dată ulterioară datei calendaristice specificate; – SYSDATE – întoarce ca rezultat data calendaristică a sistemului. Asupra datelor calendaristice se pot realiza operații aritmetice, cum ar fi scăderea sau adunarea, modul lor de funcționare fiind ilustrat în tabelul de mai jos: Tip operand Operație Tip operand Tip Rezultat Descriere data +/- Număr Data Adaugă/scade un număr de zile la o dată calendaristică data +/- număr/24 Data Adaugă/scade un număr de ore la o dată calendaristică

183 data +/- număr/1440 Data Adaugă/scade un număr de minute la o dată calendaristică data +/- număr/86400 Data Adaugă/scade un număr de secunde la o dată calendaristică data – Data Număr zile Scade două date calendaristice rezultând diferența în număr de zile. Dacă al doilea operand este mai mare decât primul numărul de zile rezultat este reprezentat de o valoare negativă. De asemenea mai există funcțiile ROUND și TRUNC care rotunjesc, respectiv trunchiază data calendaristică. Aceste funcții sunt foarte folositoare atunci când se dorește compararea datelor calendaristice care au ora diferită. Exemplul următor rotunjește data de naștere a cadrelor didactice în funcție de an: SQL> SELECT ROUND (data_nast,'YEAR') “DATA” FROM profesor; DATA ––– 01-JAN-47 01-JAN-45 01-JAN-52 01-JAN-66 01-JAN-70 Funcții de conversie În general expresiile nu pot conține valori aparținând unor tipuri de date diferite. De exemplu, nu se poate înmulți 3 cu 7 și apoi aduna “ION”. Prin urmare se realizează anumite conversii care pot fi implicite sau explicite. Conversiile implicite se realizează în următoarele cazuri: – atribuiri de valori unei coloane (folosind comenzile INSERT sau UPDATE) sau atribuirilor de valori unor argumente ale unei funcții; – evaluări de expresii. Pentru atribuiri, programul Oracle efectuează în mod implicit următoarele conversii de tip: – VARCHAR2 sau CHAR la NUMBER – VARCHAR2 sau CHAR la DATE – VARCHAR2 sau CHAR la ROWID – NUMBER, DATE sau ROWID la VARCHAR2 Conversia la atribuire reușește în cazul în care Oracle poate converti tipul valorii atribuite la tipul destinației atribuirii. Pentru evaluarea expresiilor, se realizează în mod implicit următoarele conversii de tip: – VARCHAR2 sau CHAR la NUMBER – VARCHAR2 sau CHAR la DATE – VARCHAR2 sau CHAR la ROWID De exemplu, pentru următoarea interogare se realizează conversia în mod implicit a constantei de tip CHAR ‘10’ la tipul NUMBER. SQL> SELECT salariu + '10' FROM profesor;

184 SALARIU+'10' –––– 3010 2510 2810 1510 2210 1210 1210 Pentru conversiile explicite de tip, SQL pune la dispoziție mai multe funcții de conversie, de la un anumit tip de dată la altul, după cum este arătat în tabelul de mai jos. CHAR NUMBER DATE RAW ROWID CHAR – TO_NUMBER TO_DATE HEXTORAW CHARTOROWID NUMBER TO_CHAR – TO_DATE(nr,‘J’) DATE TO_CHAR TO_DATE(dată,‘J’) – RAW RAWTOHEX – RAWID RAWIDTOCHAR – Cele mai uzuale funcții sunt: – TO_CHAR – convertește un număr sau o dată calendaristică într-un șir de caractere; – TO_NUMBER – convertește un șir de caractere alcătuit din cifre la o valoare numerică; – TO_DATE – convertește un șir de caractere sau un număr ce reprezintă o dată calendaristică la o valoare de tip dată calendaristică. De asemenea poate converti o dată calendaristică la un număr ce reprezintă data calendaristică Iuliană. Pentru a realiza conversia aceste funcții folosesc anumite măști de format. Pentru mai multe amănunte vezi Anexa 6. Următorul exemplu va prelua data și ora curentă a sistemului din funcția SYSDATE și o va formata într-o dată scrisă pe litere ce va conține și ora în minute și secunde: SQL> SELECT TO_CHAR(SYSDATE,'DD MONTH YYYY HH24:MI:SS') Data FROM dual; DATA ––––––––– 17 MAY 2000 17:03:38 Funcții diverse Acestea sunt în general funcții care acceptă ca argumente orice tip de dată. Cele mai utilizate sunt: – DECODE – Aceasta este una dintre cele mai puternice funcții SQL. Ea practic facilitează interogările condiționate, acționând ca o comanda ‘if-then-else’ sau ‘case’ dintr-un limbaj procedural. Pentru fiecare înregistrare se va evalua valoarea din coloana testată și se va compara pe rând cu fiecare valoare declarată în cadrul funcției. Dacă se găsesc valori egale, atunci funcția va returna o valoare aferentă acestei egalități, declarată tot în cadrul funcției. Se poate specifica ca, în cazul în care nu se găsesc valori egale, funcția să întoarcă o anumită valoare. Dacă acest lucru nu se specifică funcția va întoarce valoarea Null.

185 – GREATEST – returnează cea mai mare valoare dintr-o listă de valori; – LEAST – returnează cea mai mică valoare dintr-o listă de valori; – VSIZE – returnează numărul de bytes pe care este reprezentată intern o anumită coloană; – USER – returnează numele utilizatorului curent al bazei de date; – DUMP – returnează o valoare ce conține codul tipului de dată, lungimea în bytes, precum și reprezentarea internă a unei expresii. Exemplul următor utilizează funcția DECODE pentru a returna o creștere a salariului cadrelor didactice cu grad de profesor, conferențiar și lector, restul salariilor rămânând nemodificate: SQL> SELECT nume, grad, salariu, DECODE (grad, ‘PROF’,salariu*1.2, ’CONF’ , salariu*1.15, ’LECT’, salariu*1.1, salariu) “Salariu modificat” FROM profesor; NUME GRAD SALARIU Salariu modificat –––- –– ––– –––––– GHEORGHIU PROF 3000 3600 MARIN PROF 2500 3000 GEORGESCU CONF 2800 3220 IONESCU ASIST 1500 1500 ALBU LECT 2200 2420 VOINEA ASIST 1200 1200 STANESCU ASIST 1200 1200 8.14. Funcții referitoare la mai multe înregistrări Aceste funții se mai numesc și funcții totalizatoare sau funcții de grup. Spre deosebire de funcțiile referitoare la o singură înregistrare, funcțiile de grup operează pe un set de mai multe înregistrări și returnează un singur rezultat pentru fiecare grup. Dacă nu este utilizată clauza GROUP BY, ce grupează înregistrările după un anumit criteriu, tabela este considerată ca un singur grup și se va returna un singur rezultat. – COUNT – determină numărul de înregistrări care îndeplinesc o anumită condiție; – MAX – determină cea mai mare valoare dintr-o coloană; – MIN – determină cea mai mică valoare dintr-o coloană; – SUM – returnează suma tuturor valorilor dintr-o coloană; – AVG – calculează valoarea medie a unei coloane; – STDDEV – determină abaterea sau deviația standard a unei coloane numerice; – VARIANCE – returnează dispersia, adică pătratul unei deviații standard pentru o coloană numerică. Exemplu: SQL> SELECT MIN(salariu), MAX(salariu), AVG(salariu), COUNT(*) FROM profesor; Toate funcțiile de mai sus, cu excepția funcției COUNT, operează asupra unei coloane sau unei expresii, care este specificată ca parametru al funcției. În cazul funcției COUNT, argumentul acesteia nu contează, de obicei utilizându-se ca argument simbolul ‘*’. MIN(SALARIU) MAX(SALARIU) AVG(SALARIU) COUNT(*) –––– –––– –––– ––– 1200 3000 2057.1429 7

186 Observație: Toate funcțiile de mai sus ignoră valorile Null, excepție făcând funcția COUNT. Pentru a include în calcule și înregistrările cu valoarea Null se poate folosi funcția NVL. Dacă nu este utilizată clauza GROUP BY, în lista de SELECT nu pot apare funcții de grup alături de nume de coloane sau alte expresii care iau valori pentru fiecare înregistrare în parte. De exemplu, următoarea interogare va genera o eroare: SQL> SELECT nume, MIN(salariu) FROM profesor; ERROR at line 1: ORA-00937: not a single-group group function 8.1.15 Pseudo-coloana ROWNUM ROWNUM este o pseudo-coloană care numerotează rândurile selectate de o interogare. Astfel, pentru primul rând selectat pseudo-coloana ROWNUM are valoarea 1, pentru al doilea rând are valoarea 2, ș.a.m.d. De exemplu, pentru a limita rândurile selectate de o interogare la maxim 5, se folosește următoarea comandă: SELECT * FROM profesor WHERE ROWNUM < 6; Deoarece pseudo-coloana ROWNUM numerotează rândurile selectate, valorile sale vor trebui să înceapă tot timpul cu 1. Deci dacă în exemplul de mai sus condiția ar fi ROWNUM > 6 sau ROWNUM = 6 interogarea nu ar selecta nici un rând deoarece în acest caz condiția ar fi întotdeauna falsă. Pentru primul rând accesat de interogare ROWNUM va avea valoarea 1, condiția nu este îndeplinită și deci rândul nu va fi selectat. Pentru al doilea rând accesat de interogare ROWNUM va avea din nou valoarea 1, condiția nu este îndeplinită nici în acest caz și deci nici acest rând nu va fi selectat. Prin urmare nici unul din rândurile accesate nu vor satisface condiția conținută de interogare. Pseudo-coloana ROWNUM se poate folosi atât în condiția unor comenzi UPDATE sau DELETE cât și pentru a atribui valori unice unei coloane, ca în exemplul de mai jos: UPDATE vanzari SET cod = ROWNUM; Valoarea pseudo-coloanei ROWNUM este atribuită unui rând înainte ca acesta să fie sortat datorită unei clauze ORDER BY, de aceea valorile pseudo-coloanei nu reprezintă ordinea de sortare. De exemplu SQL> SELECT nume, prenume, ROWNUM FROM profesor ORDER BY salariu; NUME PRENUME ROWNUM –––- –––- ––– VOINEA MIRCEA 6 STANESCU MARIA 7 IONESCU VERONICA 4 ALBU GHEORGHE 5 MARIN VLAD 2 GEORGESCU CRISTIANA 3 GHEORGHIU STEFAN 1

187 8.1.16 Clauza GROUP BY Clauza GROUP BY este utilizată pentru a împărți din punct de vedere logic un tabel în grupuri de înregistrări. Fiecare grup este format din toate înregistrările care au aceeași valoare în câmpul sau grupul de câmpuri specificate în clauza GROUP BY. Unele înregistrări pot fi excluse folosind clauza WHERE înainte ca tabelul să fie împărțit în grupuri. Clauza GROUP BY se folosește de obicei împreună cu funcțiile de grup, acestea returnând valoarea calculată pentru fiecare grup în parte. În cazul folosirii clauzei GROUP BY, toate expresiile care apar în lista de SELECT trebuie să aibă o valoare unică pentru fiecare grup, de aceea orice coloană sau expresie din această listă care nu este o funcție de grup trebuie să apară în clauza GROUP BY. Prezentăm în continuare câteva exemple: SQL> SELECT grad, AVG (salariu) FROM profesor GROUP BY grad; GRAD AVG(SALARIU) –– –––– ASIST 1300 CONF 2800 LECT 2200 PROF 2750 SQL> SELECT grad, MAX(salariu) FROM profesor WHERE prima is not NULL GROUP BY grad; GRAD MAX(SALARIU) –– –––– ASIST 1200 CONF 2800 LECT 2200 PROF 3000 Următoarea interogare va genera o eroare deoarece în lista de SELECT există o coloană (nume) care nu apare în clauza GROUP BY: SQL> SELECT nume, MIN(salariu) FROM profesor GROUP BY grad; ERROR at line 1: ORA-00979: not a GROUP BY expression Comanda de mai sus este invalidă deoarece coloana nume are valori individuale pentru fiecare înregistrare, în timp ce MIN(salariu) are o singură valoare pentru un grup. Clauza GROUP BY permite apelarea unei funcții de grup în altă funcție de grup. În exemplul următor, funcția AVG returnează salariul mediu pentru fiecare grad didactic, iar funcția MAX returnează maximul dintre aceste salarii medii. SQL> SELECT MAX(AVG(salariu)) from profesor GROUP BY grad;

188 MAX(AVG(SALARIU)) –––––– 2800 8.1.17 Clauza HAVING Clauza HAVING este tot o clauză de filtrare ca și clauza WHERE. Totuși, în timp ce clauza WHERE determină ce înregistrări vor fi selecționate dintr-un tabel, clauza HAVING determină care dintre grupurile rezultate vor fi afișate după ce înregistrările din tabel au fost grupate cu clauza GROUP BY. Cu alte cuvinte, pentru a exclude grupuri de înregistrări se folosește clauza HAVING iar pentru a exclude înregistrări individuale se folosește clauza WHERE. Clauza HAVING este folosită numai dacă este folosită și clauza GROUP BY. Expresiile folosite într-o clauză HAVING trebuie să aibă o singură valoare pe grup. Atunci când se folosește clauza GROUP BY, clauza WHERE se utilizează pentru eliminarea înregistrărilor ce nu se doresc a fi grupate. Astfel, următoarea interogare este invalidă deoarece clauza WHERE încearcă să excludă grupuri de înregistrări și nu anumite înregistrări: SQL> SELECT grad, AVG(salariu) FROM profesor WHERE AVG(salariu)>2000 GROUP BY grad; ERROR: ORA-00934: group function is not allowed here Pentru a exclude gradul didactic pentru care media de salariu nu este mai mare decât 2000 se folosește următoarea comandă SELECT cu clauza HAVING: SQL> SELECT grad, AVG(salariu) FROM profesor GROUP BY grad HAVING AVG(salariu)>2000; GRAD AVG(SALARIU) –– –––– CONF 2800 LECT 2200 PROF 2750 Următoarea interogare exclude întâi cadrele didactice care nu au salariu mai mare decât 2000 după care exclude gradul didactic pentru care media de salariu nu este mai mare decât 2500. SQL> SELECT grad, AVG(salariu) FROM profesor WHERE salariu > 2000 GROUP BY grad HAVING AVG(salariu) > 2500; GRAD AVG(SALARIU) –– –––– CONF 2800 PROF 2750 8.1.18 Regăsirea datelor din două sau mai multe tabele O joncțiune este o interogare care regăsește înregistrări din două sau mai multe tabele. Capacitatea de a realiza o joncțiune între două sau mai multe tabele reprezintă una dintre cele mai puternice facilități ale unui sistem relațional. Legătura dintre înregistrările tabelelor se realizează prin

189 existența unor câmpuri comune caracterizate prin domenii de definiție compatibile (chei primare sau străine). Pentru realizarea unei joncțiuni se folosește clauza SELECT, precizând în clauza FROM numele tabelelor utilizate, iar în clauza WHERE criteriul de compunere. Produsul a două sau mai multe tabele. În cazul în care în interogare se specifică mai multe tabele și nu este inclusă o clauză WHERE, interogarea va genera produsul cartezian al tabelelor. Acesta va conține toate combinațiile posibile de înregistrări din tabelele componente. Astfel, produsul cartezian a două tabele care conțin 100, respectiv 50 de înregistrări va avea dimensiunea de 5.000 de înregistrări. De exemplu, să considerăm tabela CATEDRA cu următoarele 4 înregistrări: COD_CATEDRA NUME PROFIL –––– –––– ––– 10 INFORMATICA TEHNIC 20 ELECTRONICA TENHIC 30 AUTOMATICA TENHIC 40 FINANTE ECONOMIC Atunci următoarea interogare va genera produsul cartezian al tabelelor, adică va avea ca rezultat 7 x 4 = 28 de rânduri ce vor conține toate combinațiile posibile de înregistrări din cele două tabele: SQL> SELECT * FROM profesor, catedra; Dacă în lista de SELECT sunt specificate coloanele selectate, atunci numele acestora trebuie să fie unic în cadrul tuturor tabelelor. Dacă există un nume de coloană care apare în mai mult de un tabel, atunci, pentru evitarea ambiguității, trebuie specificat și tabelul din care face parte coloana în cauză. De exemplu, în următoarea interogare pentru coloanele cod_catedra și nume trebuie specificate tabelele din care fac parte: SQL> SELECT profesor.nume, prenume, catedra.cod_catedra, catedra.nume FROM profesor, catedra; NUME PRENUME COD_CATEDRA NUME –––- –––- –––– –––– GHEORGHIU STEFAN 10 INFORMATICA MARIN VLAD 10 INFORMATICA GEORGESCU CRISTIANA 10 INFORMATICA IONESCU VERONICA 10 INFORMATICA ALBU GHEORGHE 10 INFORMATICA VOINEA MIRCEA 10 INFORMATICA STANESCU MARIA 10 INFORMATICA GHEORGHIU STEFAN 20 ELECTRONICA MARIN VLAD 20 ELECTRONICA GEORGESCU CRISTIANA 20 ELECTRONICA IONESCU VERONICA 20 ELECTRONICA ALBU GHEORGHE 20 ELECTRONICA VOINEA MIRCEA 20 ELECTRONICA STANESCU MARIA 20 ELECTRONICA GHEORGHIU STEFAN 30 AUTOMATICA MARIN VLAD 30 AUTOMATICA

190 GEORGESCU CRISTIANA 30 AUTOMATICA IONESCU VERONICA 30 AUTOMATICA ALBU GHEORGHE 30 AUTOMATICA VOINEA MIRCEA 30 AUTOMATICA STANESCU MARIA 30 AUTOMATICA GHEORGHIU STEFAN 40 FINANTE MARIN VLAD 40 FINANTE GEORGESCU CRISTIANA 40 FINANTE IONESCU VERONICA 40 FINANTE ALBU GHEORGHE 40 FINANTE VOINEA MIRCEA 40 FINANTE STANESCU MARIA 40 FINANTE În general, pentru a scurta textul comenzii, în astfel de cazuri se folosesc de obicei aliasuri pentru numele tabelelor, care pot fi folosite în interogare. Astfel interogarea de mai sus se mai poate scrie: SQL> SELECT p.nume, prenume, c.cod_catedra, c.nume FROM profesor p, catedra c; În general, produsul cartezian este rar folosit, având o utilitate practică redusă. Joncțiuni Pentru a realiza o joncțiune între două sau mai multe tabele se utilizează clauza WHERE a interogărilor pe aceste tabele. În funcție de criteriul de compunere, se disting mai multe tipuri de joncțiuni: 1. joncțiuni echivalente (EQUI -JOIN) sau joncțiuni interne (INNER JOIN) 2. joncțiuni neechivalente 3. joncțiuni externe (OUTER JOIN) 4. auto-joncțiuni 1. Joncțiunile echivalente O echi-joncțiune conține operatorul egalitate (=) în clauza WHERE, combinând înregistrările din tabele care au valori egale pentru coloanele specificate. De exemplu, pentru a afișa cadrele didactice și numele catedrei din care fac parte se combină înregistrările din cele două tabele pentru care codul catedrei este același. SQL> SELECT p.nume, p.prenume, c.nume FROM profesor p, catedra c WHERE p.cod_catedra=c.cod_catedra; NUME PRENUME NUME –––- –––- –––– GHEORGHIU STEFAN INFORMATICA IONESCU VERONICA INFORMATICA VOINEA MIRCEA INFORMATICA MARIN VLAD ELECTRONICA STANESCU MARIA ELECTRONICA ALBU GHEORGHE ELECTRONICA GEORGESCU CRISTIANA AUTOMATICA 2. Joncțiuni neechivalente

191 Joncțiunile neechivalente sunt acelea care nu folosesc în clauza WHERE operatorul egal. Operatorii cei mai utilizați în cazul joncțiunilor neechivalente sunt: <, >, <=, >= ,<>, BETWEEN…AND…. Pentru a exemplifica un astfel de tip de joncțiune considerăm tabela GRADSAL ce conține pragul minim și pragul maxim al salariului dintr-un anumit grad de salarizare: GRAD_SALARIZARE PRAG_MIN PRAG_MAX ––––– ––– ––– 1 500 1500 2 1501 2000 3 2001 2500 4 2501 3500 5 3501 10000 Evident, între tabelele PROFESOR și GRADSAL nu are sens definirea unei joncțiuni echivalente deoarece nu există o coloană din tabela PROFESOR căreia să-i corespundă o coloană din tabela GRADSAL. Exemplul următor ilustrează definirea unei joncțiuni neechivalente care evaluează gradul de salarizare a cadrelor didactice, prin încadrarea salariului acestora într-un interval stabilit de pragul minim și pragul maxim: SQL> SELECT p.nume, p.grad, p.salariu, g.grad_salarizare FROM profesor p, gradsal g WHERE p.salariu BETWEEN g.prag_min AND g.prag_max; NUME GRAD SALARIU GRAD_SALARIZARE –––- –– ––– ––––– IONESCU ASIST 1500 1 VOINEA ASIST 1200 1 STANESCU ASIST 1200 1 MARIN PROF 2500 3 ALBU LECT 2200 3 GHEORGHIU PROF 3000 4 GEORGESCU CONF 2800 4 3. Joncțiuni externe Dacă într-o joncțiune de tipul celor prezentate până acum una sau mai multe înregistrări nu satisfac condiția de compunere specificată în clauza WHERE, atunci ele nu vor apărea în rezultatul interogării. Aceste înregistrări pot apare însă dacă se folosește joncțiunea externă. Joncțiunea externă returnează toate înregistrările care satisfac condiția de joncțiune plus acele înregistrări dintr-un tabel ale căror valori din coloanele după care se face legătura nu se regăsesc în coloanele corespunzătoare ale nici unei înregistrări din celalalt tabel. Pentru a realiza o joncțiune externă între tabelele A și B ce returnează toate înregistrările din tabela A se utilizează semnul (+) în dreapta tabelului B. Pentru fiecare înregistrare din tabela A care nu satisface condiția de compunere pentru nici o înregistrare din tabela B se va crea în tabela B o înregistrare nulă care va fi compusă cu înregistrarea din tabela A. Invers, pentru a realiza o joncțiune externă între tabelele A și B ce returnează toate înregistrările din tabela B, se utilizează semnul (+) în dreapta tabelului A.

192 În interogarea utilizată pentru a exemplifica joncțiunea echivalentă, se observă că au fost selectate numai catedrele în care există cadre didactice. Pentru a afișa toate catedrele, indiferent dacă ele cuprind sau nu cadre didactice, se folosește următoarea interogare: SQL> SELECT p.nume, p.prenume, c.nume FROM profesor p, catedra c WHERE p.cod_catedra(+)=c.cod_catedra; NUME PRENUME NUME –––- –––- –––– GHEORGHIU STEFAN INFORMATICA IONESCU VERONICA INFORMATICA VOINEA MIRCEA INFORMATICA MARIN VLAD ELECTRONICA STANESCU MARIA ELECTRONICA ALBU GHEORGHE ELECTRONICA GEORGESCU CRISTIANA AUTOMATICA FINANTE Se observă că ultima înregistrare (ce corespunde catedrei de finanțe care nu are în componență nici un cadru didactic) va avea coloanele corespunzătoare primului tabel completate cu Null. Folosirea operatorului de joncțiune externă are următoarele restricții: 1. Operatorul (+) poate fi plasat în oricare parte a condiției din clauza WHERE, însă nu în ambele părți. Tabelul de partea căruia este amplasat acest operator va crea înregistrări nule care vor fi compuse cu înregistrările din celălalt tabel care nu satisfac condiția de compunere. 2. Daca tabelele A și B au condiții multiple de joncțiune, atunci operatorul (+) trebuie utilizat în toate aceste condiții; 3. Într-o singură interogare nu se poate realiza o joncțiune externă a unui tabel cu mai multe tabele. 4. O condiție care conține operatorul (+) nu poate fi combinată cu o altă condiție ce utilizează operatorul IN. 5. O condiție care conține operatorul (+) nu poate fi combinată cu o altă condiție prin operatorul OR. 4. Auto-joncțiuni Auto-joncțiunea reprezintă joncțiunea unui tabel cu el însuși. Pentru ca rândurile dintr-un tabel să poată fi compuse cu rânduri din același tabel, în clauza FROM a interogării numele tabelului va apare de mai multe ori, urmat de fiecare dată de un alias. De exemplu, pentru a selecta toate cardele didactice care au un șef direct și numele acestui șef se folosește următoarea auto-joncțiune: SQL> SELECT p.nume, p.prenume, s.nume, s.prenume FROM profesor p, profesor s WHERE p.sef=s.cod; NUME PRENUME NUME PRENUME –––- –––- –––- –––- MARIN VLAD GHEORGHIU STEFAN GEORGESCU CRISTIANA GHEORGHIU STEFAN ALBU GHEORGHE GHEORGHIU STEFAN VOINEA MIRCEA GHEORGHIU STEFAN IONESCU VERONICA GEORGESCU CRISTIANA

193 STANESCU MARIA IONESCU VERONICA Auto-joncțiunea poate fi folosită și pentru verificarea corectitudinii interne a datelor. De exemplu, este puțin probabil să existe două cadre didactice care au cod diferit dar în schimb au același nume, prenume și dată de naștere. Pentru a verifica dacă există astfel de înregistrări se folosește interogarea: SQL> SELECT a.nume, a.prenume FROM profesor a, profesor b WHERE a.nume=b.nume AND a.prenume=b.prenume AND a.data_nast=b.data_nast AND a.cod<>b.cod; 8.1.19. Operatorii pentru mulțimi Operatorii de mulțimi combină două sau mai multe interogări, efectuând operații specifice mulțimilor: reuniune, intersecție, diferență. Acești operatori se mai numesc și operatori verticali deoarece combinarea celor două interogări se face coloană cu coloană. Din acest motiv, numărul total de coloane și tipurile de date ale coloanelor corespondente din cele două interogări trebuie să coincidă. Există următorii operatori pentru mulțimi: 1. UNION – Returnează rezultatele a două sau mai multe interogări eliminând toate înregistrările duplicat; 2. UNION ALL – Returnează rezultatele a două sau mai multe interogări incluzând înregistrările duplicat; 3. INTERSECT – Returnează toate înregistrările distincte găsite în ambele interogări; 4. MINUS – Returnează toate înregistrările distincte care se găsesc în prima interogare dar nu și în a doua interogare. Să considerăm de exemplu următoarele interogări: SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10; GRAD SALARIU –– ––– PROF 3000 ASIST 1500 ASIST 1200 SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 20; GRAD SALARIU –– ––– PROF 2500 LECT 2200 ASIST 1200 În continuare exemplificăm fiecare dintre operatorii pentru mulțimi aplicați acestor interogări: SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10 UNION SELECT grad, salariu FROM profesor WHERE cod_catedra = 20;

194 GRAD SALARIU –– ––– ASIST 1200 ASIST 1500 LECT 2200 PROF 2500 PROF 3000 SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10 UNION ALL SELECT grad, salariu FROM profesor WHERE cod_catedra = 20; GRAD SALARIU –– ––– PROF 3000 ASIST 1500 ASIST 1200 PROF 2500 LECT 2200 ASIST 1200 SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10 INTERSECT SELECT grad, salariu FROM profesor WHERE cod_catedra = 20; GRAD SALARIU –– ––– ASIST 1200 SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10 MINUS SELECT grad, salariu FROM profesor WHERE cod_catedra = 20; GRAD SALARIU –– ––– ASIST 1500 PROF 3000 Există următoarele reguli de folosire a operatorilor pentru mulțimi: • interogările trebuie să conțină același număr de coloane; • coloanele corespondente trebuie să aibă același tip de dată; • în rezultat vor apărea numele coloanelor din prima interogare, nu cele din a doua interogare chiar dacă aceasta folosește alias-uri; de exemplu: SQL> SELECT cod FROM profesor MINUS SELECT sef FROM profesor; COD ––– 101 104

195 105 106 • clauza ORDER BY poate fi folosită o singură dată într-o interogare care folosește operatori de mulțimi; atunci când se folosește, ea trebuie poziționată la sfârșitul comenzii; de exemplu: SQL> SELECT grad, salariu FROM profesor WHERE cod_catedra = 10 UNION SELECT grad, salariu FROM profesor WHERE cod_catedra = 20 ORDER BY 2; GRAD SALARIU –– ––– ASIST 1200 ASIST 1500 LECT 2200 PROF 2500 PROF 3000 • operatorii pentru mulțimi pot fi utilizați în subinterogări; • pentru a modifica ordinea de execuție este posibilă utilizarea parantezelor; de exemplu: SQL> SELECT grad FROM profesor WHERE cod_catedra = 10 INTERSECT SELECT grad FROM profesor WHERE cod_catedra = 20 UNION SELECT grad FROM profesor WHERE cod_catedra = 30; GRAD –– ASIST CONF PROF SQL> SELECT grad FROM profesor WHERE cod_catedra = 10 INTERSECT (SELECT grad FROM profesor WHERE cod_catedra = 20 UNION SELECT grad FROM profesor WHERE cod_catedra = 30); GRAD –– ASIST PROF 8.1.20. Subinterogări și operatorii ANY, ALL, EXISTS

196 O subinterogare este o comandă SELECT inclusă în altă comandă SELECT. Rezultatele subinterogării sunt transmise celeilalte interogări și pot apărea în cadrul clauzelor WHERE, HAVING sau FROM. Subinterogările sunt utile pentru a scrie interogări bazate pe o condiție în care valoarea de comparație este necunoscută. Această valoare poate fi aflată folosind o subinterogare. De exemplu: SELECT coloane FROM tabel WHERE coloana = (SELECT coloane FROM tabel WHERE condiție) Subinterogarea, denumită și interogare interioară (inner query), generează valorile pentru condiția de căutare a instrucțiunii SELECT care o conține, denumită interogare exterioară (outer query). Instrucțiunea SELECT exterioară depinde de valorile generate de către interogarea interioară. În general, interogarea interioară se execută prima și rezultatul acesteia este utilizat în interogarea exterioară. Rezultatul interogării exterioare depinde de numărul valorilor returnate de către interogarea interioară. În acest sens, putem distinge: 1. Subinterogări care returnează un singur rând; 2. Subinterogări care returnează mai multe rânduri. Din punct de vedere al ordinii de evaluare a interogărilor putem clasifica subinterogările în: 1. Subinterogări simple – în care interogarea interioară este evaluată prima, independent de interogarea exterioară (interogarea interioară se execută o singură dată); 2. Subinterogări corelate – în care valorile returnate de interogarea interioară depind de valorile returnate de interogarea exterioară (interogarea interioară este evaluată pentru fiecare înregistrare a interogării exterioare). Subinterogările sunt îndeosebi utilizate atunci când se dorește ca o interogare să regăsească înregistrări dintr-o tabelă care îndeplinesc o condiție ce depinde la rândul ei de valori din aceeași tabelă. Observație: Clauza ORDER BY nu poate fi utilizată într-o subinterogare. Regula este că poate exista doar o singură clauză ORDER BY pentru o declarație SELECT și, dacă este specificată, trebuie să fie ultima clauză din comanda SELECT. Prin urmare, clauza ORDER BY nu poate fi specificată decât în interogarea cea mai din exterior. Subinterogări care returnează un singur rând În acest caz condiția, din clauza WHERE sau HAVING a interogării exterioare utilizează operatorii: =, <, <=, >, >=, <> care operează asupra unei subinterogări ce returnează o singură valoare. Interogarea interioară poate conține condiții complexe formate prin utilizarea condițiilor multiple de interogare cu ajutorul operatorilor AND și OR sau prin utilizarea funcțiilor agregat. Următoarea interogare selectează cadrele didactice care salariul minim. Salariul minim este determinat de o subinterogare ce returnează o singură valoare. SQL> SELECT nume, prenume, salariu FROM profesor WHERE salariu = (SELECT MIN(salariu) FROM profesor);

197 NUME PRENUME SALARIU –––- –––- ––– VOINEA MIRCEA 1200 STANESCU MARIA 1200 Procesul de evaluare al acestei interogări se desfășoară astfel: • se evaluează în primul rând interogarea interioară: Valoarea obținută este MIN(salariu)=1200 • rezultatul evaluării interogării interioare devine condiție de căutare pentru interogarea exterioară și anume: SELECT nume, prenume, salariu FROM profesor WHERE salariu =1200; În cazul în care interogarea interioară nu întoarce nici o înregistrare, interogarea exterioară nu va selecta la rândul ei nici o înregistrare. Observație: Dacă care se utilizează operatorii: =, <, <=, >, >=, <> în condiția interogării exterioare, atunci interogarea interioară trebuie în mod obligatoriu să returneze o singură valoare. În caz contrar va apărea un mesaj de eroare, ca în exemplul următor: SQL> SELECT nume, prenume, salariu FROM profesor WHERE salariu = (SELECT MIN (salariu) FROM profesor GROUP BY grad); ERROR: ORA-01427: single-row subquery returns more than one row Subinterogările pot fi folosite nu numai în clauza WHERE a interogării exterioare, ci și în clauza HAVING. Următoarea interogare afișează toate gradele didactice pentru care salariul minim este mai mare decât salariul mediu al tuturor cadrelor didactice. SQL> SELECT grad FROM profesor GROUP BY grad HAVING min(salariu) > (SELECT avg(salariu) FROM profesor); GRAD –– CONF LECT PROF Subinterogări care returnează mai multe rânduri În cazul când interogarea întoarce mai multe rânduri nu mai este posibilă folosirea operatorilor de comparație. În locul acestora se folosește operatorul IN, care așteaptă o listă de valori și nu doar una. Următoarea interogare selectează pentru fiecare grad didactic acele persoane care au salariul minim. Salariul minim pentru fiecare grad didactic este aflat printr-o subinterogare, care, evident, va întoarce mai multe rânduri: SQL> SELECT nume, salariu, grad FROM profesor WHERE (salariu, grad) IN (SELECT MIN (salariu), grad FROM profesor GROUP BY grad) ORDER BY salariu;

198 NUME SALARIU GRAD –––- ––– –– VOINEA 1200 ASIST STANESCU 1200 ASIST ALBU 2200 LECT MARIN 2500 PROF GEORGESCU 2800 CONF Observație: Spre deosebire de celelalte interogări de până acum, interogarea de mai sus compară perechi de coloane. În acest caz trebuiesc respectate următoarele reguli: • coloanele din dreapta condiției de căutare sunt în paranteze și fiecare coloană este separată prin virgulă; • coloanele returnate de interogarea interioară trebuie să se potrivească ca număr și tip cu coloanele cu care sunt comparate în interogarea exterioară; în plus, ele trebuie să fie în aceeași ordine cu coloanele cu care sunt comparate. Alături de operatorul IN, o subinterogare care returnează mai multe rânduri poate folosi operatorii ANY, ALL sau EXISTS. Operatorii ANY și ALL sunt prezentați în continuare, iar operatorul EXISTS va fi prezentat în secțiunea “Subinterogări corelate”. Operatorii ANY și ALL sunt folosiți în mod obligatoriu în combinație cu operatorii relaționali =, !=, <, >, <=, >=; operatorii IN și EXISTS nu pot fi folosiți în combinație cu operatorii relaționali, dar pot fi utilizați cu operatorul NOT, pentru negarea expresiei. Operatorul ANY Operatorul ANY (sau sinonimul său SOME) este folosit pentru a compara o valoare cu oricare dintre valorile returnate de o subinterogare. Pentru a înțelege modul de folosire a acestui operator să considerăm următorul exemplu ce afișează cadrele didactice ce câștigă mai mult decât profesorii care au cel mai mic salariu: SQL> SELECT nume, salariu, grad FROM profesor WHERE salariu > ANY (SELECT DISTINCT salariu FROM profesor WHERE grad=’PROF’); NUME SALARIU GRAD –––- ––– –- GHEORGHIU 3000 PROF GEORGESCU 2800 CONF Interogarea de mai sus este evaluată astfel: dacă salariul unui cadru didactic este mai mare decât cel puțin unul din salariile returnate de interogarea interioară, acea înregistrare este inclusă în rezultat. Cu alte cuvinte, >ANY însemnă mai mare decât minimul dintre valorile returnate de interogarea interioară, <ANY înseamnă mai mic ca maximul, iar =ANY este echivalent cu operatorul IN. Observație: opțiunea DISTINCT este folosită frecvent atunci când se folosește operatorul ANY pentru a preveni selectarea de mai multe ori a unor înregistrări. Operatorul ALL

199 Operatorul ALL este folosit pentru a compara o valoare cu toate valorile returnate de o subinterogare. Considerăm următorul exemplu ce afișează cadrele didactice care câștigă mai mult decât asistenții cu salariul cel mai mare: SQL> SELECT nume, salariu, grad FROM profesor WHERE salariu > ALL (SELECT DISTINCT salariu FROM profesor WHERE grad=’ASIST’); NUME SALARIU GRAD –––- ––– –– GHEORGHIU 3000 PROF MARIN 2500 PROF GEORGESCU 2800 CONF ALBU 2200 LECT Interogarea de mai sus este evaluată astfel: dacă salariul unui cadru didactic este mai mare decât toate valorile returnate de interogarea interioară, acea înregistrare este inclusă în rezultat. Cu alte cuvinte, >ALL înseamnă mai mare ca maximul dintre valorile returnate de interogarea interioară iar <ALL înseamnă mai mic ca minimul dintre acestea. Observație: operatorul ALL nu poate fi utilizat cu operatorul = deoarece interogarea nu va întoarce nici un rezultat cu excepția cazului în care toate valorile sunt egale, situație care nu ar avea sens. Subinterogările pot fi imbricate (utilizate cu alte subinterogări) până la 255 de nivele, indiferent de numărul de valori returnate de fiecare subinterogare. Pentru a selecta cadrele didactice care au salariul mai mare decât cel mai mare salariu al cadrelor didactice care aparțin catedrei de Electronică, vom folosi următoarea interogare: SQL> SELECT nume, prenume, salariu FROM profesor WHERE salariu > (SELECT MAX(salariu) FROM profesor WHERE cod_catedra = (SELECT cod_catedra FROM catedra WHERE nume='ELECTRONICĂ')); NUME PRENUME SALARIU –––- –––- ––– GHEORGHIU STEFAN 3000 GEORGESCU CRISTIANA 2800 Subinterogări corelate În exemplele considerate până acum interogarea interioară era evaluată prima, după care valoarea sau valorile rezultate erau utilizate de către interogarea exterioară. Subinterogările de acest tip sunt numite subinterogări simple. O altă formă de subinterogare o reprezintă interogarea corelată, caz în care interogarea exterioară transmite repetat câte o înregistrare pentru interogarea interioară. Interogarea interioară este evaluată de fiecare dată când este transmisă o înregistrare din interogarea exterioară, care se mai numește și înregistrare candidată. Subinterogarea corelată poate fi identificată prin faptul că interogarea interioară nu se poate executa independent ci depinde de valoarea transmisă de către interogarea exterioară. Dacă ambele interogări accesează aceeași tabelă, trebuiesc asigurate alias-uri pentru fiecare referire la tabela respectivă. Subinterogările corelate reprezintă o cale de a accesa fiecare înregistrare din tabel și de a compara anumite valori ale acesteia cu valori ce depind tot de ea.

200 Evaluarea unei subinterogări corelate se execută în următorii pași: 1. Interogarea exterioară trimite o înregistrare candidată către interogarea interioară; 2. Interogarea interioară se execută în funcție de valorile înregistrării candidate; 3. Valorile rezultate din interogarea interioară sunt utilizate pentru a determina dacă înregistrarea candidată va fi sau nu inclusă în rezultat; 4. Se repetă procedeul începând cu pasul 1 până când nu mai există înregistrări candidate. De exemplu pentru a regăsi cadrele didactice care câștigă mai mult decât salariul mediu din propria catedră, putem folosi următoarea interogare corelată: SQL> SELECT nume, prenume, salariu FROM profesor p WHERE salariu > (SELECT AVG(salariu) FROM profesor WHERE cod_catedra = p.cod_catedra); NUME PRENUME SALARIU –––––– ––––– –––– GHEORGHIU STEFAN 3000 MARIN VLAD 2500 ALBU GHEORGHE 2200 În exemplul de mai sus coloana interogării exterioare care se folosește în interogarea interioară este p.cod_catedra. Deoarece p.cod_catedra poate avea valoare diferită pentru fiecare înregistrare, interogarea interioară se execută pentru fiecare înregistrare candidată transmisă de interogarea exterioară. Atunci când folosim subinterogări corelate împreună cu clauza HAVING, coloanele utilizate în această clauză trebuie să se regăsească în clauza GROUP BY. În caz contrar, va fi generat un mesaj de eroare datorat faptului că nu se poate face comparație decât cu o expresie de grup. De exemplu, următoarea interogare este corectă, ea selectând gradele didactice pentru care media salariului este mai mare decât maximul primei pentru același grad: SQL> SELECT grad FROM profesor p GROUP BY grad HAVING AVG(salariu) > (SELECT MAX(prima) FROM profesor WHERE grad = p.grad); Operatorul EXISTS Operatorul EXISTS verifică dacă, pentru fiecare înregistrare transmisă de interogarea exterioară, există sau nu înregistrări care satisfac condiția interogării interioare, returnând interogării exterioare valoarea TRUE sau FALSE. Cu alte cuvinte, operatorul EXISTS cere în mod obligatoriu corelarea interogării interioare cu interogarea exterioară. Datorită faptului că operatorul EXISTS verifică doar existența rândurilor selectate și nu ia în considerare numărul sau valorile atributelor selectate, în subinterogare pot fi specificate orice număr de atribute; în particular, poate fi folosită o constantă și chiar simbolul ‘*’ (deși acest lucru nu este recomandabil din punct de vedere al eficienței). De altfel EXISTS este singurul operator care permite acest lucru. Următoarea interogare selectează toate cadrele didactice care au măcar un subordonat: SQL> SELECT cod, nume, prenume, grad FROM profesor p

201 WHERE EXISTS (SELECT '1' FROM profesor WHERE profesor.sef = p.cod) ORDER BY cod; COD NUME PRENUME GRAD –- –––- –––- –– 100 GHEORGHIU STEFAN PROF 102 GEORGESCU CRISTIANA CONF 103 IONESCU VERONICA ASIST La fel ca și operatorul IN, operatorul exists poate fi negat, luând forma NOT EXISTS. Totuși, o remarcă foarte importantă este faptul că pentru subinterogări NOT IN nu este la fel de eficient ca NOT EXISTS. Astfel dacă în lista de valori transmisă operatorului NOT IN există una sau mai multe valori Null, atunci condiția va lua valoarea de adevăr FALSE, indiferent de celelalte valori din listă. De exemplu, următoarea interogare încearcă să returneze toate cadrele didactice care nu au nici un subaltern: SQL> SELECT nume, grad FROM profesor WHERE cod NOT IN (SELECT sef FROM profesor); Această interogări nu va întoarce nici o înregistrare deoarece coloana sef conține și valoarea Null. Pentru a obține rezultatul corect trebuie să folosim următoarea interogare: SQL> SELECT nume, grad FROM profesor p WHERE NOT EXISTS (SELECT sef FROM profesor WHERE sef=p.cod); NUME GRAD –––- –– MARIN PROF ALBU LECT VOINEA ASIST STANESCU ASIST În general, operatorul EXISTS se folosește în cazul subinterogărilor corelate și este câteodată cel mai eficient mod de a realiza anumite interogări. Performanța interogărilor depinde de folosirea indecșilor, de numărul rândurilor returnate, de dimensiunea tabelei și de necesitatea creării tabelelor temporare pentru evaluarea rezultatelor intermediare. Tabelele temporare generate de Oracle nu sunt indexate, iar acest lucru poate degrada performanța subinterogărilor dacă se folosesc operatorii IN, ANY sau ALL. Subinterogările mai pot apărea și în alte comenzi SQL cum ar fi: UPDATE, DELETE, INSERT și CREATE TABLE. Așa cum am văzut, există în principal două moduri de realizare a interogărilor ce folosesc date din mai multe tabele: joncțiuni și subinterogări. Joncțiunile reprezintă forma de interogare relațională (în care sarcina găsirii drumului de acces la informație revine SGBD-ului) iar subinterogările forma procedurală (în care trebuie indicat drumul de acces la informație). Fiecare dintre aceste forme are avantajele sale, depinzând de cazul specific în care se aplică.

202 8.1.21 Operații pe tabele ce conțin informații de structură arborescentă O bază de date relațională nu poate stoca înregistrări în mod ierarhic, dar la nivelul înregistrării pot exista informații care determină o relație ierarhică între înregistrări. SQL permite afișarea rândurilor dintr-o tabelă ținând cont de relațiile ierarhice care apar între rândurile tabelei. Parcurgerea în mod ierarhic a informațiilor se poate face doar la nivelul unei singure tabele. Operația se realizează cu ajutorul clauzelor START WITH și CONNECT BY din comanda SELECT. De exemplu, în tabela PROFESOR există o relație ierarhică între înregistrări datorată valorilor din coloanele cod și sef. Fiecare înregistrare aferentă unui cadru didactic conține în coloana sef codul persoanei căreia îi este direct subordonat. Pentru a obține o situație ce conține nivelele ierarhice, vom folosi următoarea interogare: SQL> SELECT LEVEL, nume, prenume, grad FROM profesor CONNECT BY PRIOR cod=sef START WITH sef IS NULL; LEVEL NUME PRENUME GRAD ––– –––- –––- –– 1 GHEORGHIU STEFAN PROF 2 MARIN VLAD PROF 2 GEORGESCU CRISTIANA CONF 3 IONESCU VERONICA ASIST 4 STANESCU MARIA ASIST 2 ALBU GHEORGHE LECT 2 VOINEA MIRCEA ASIST Explicarea sintaxei și a regulilor de funcționare pentru exemplul de mai sus: • Clauza standard SELECT poate conține pseudo-coloana LEVEL ce indică nivelul înregistrării în arbore (cât de departe este de nodul rădăcină). Astfel, nodul rădăcină are nivelul 1, fiii acestuia au nivelul 2, ș.a.m.d.; • În clauza FROM nu se poate specifica decât o tabelă; • Clauza WHERE poate apărea în interogare pentru a restricționa vizitarea nodurilor (înregistrărilor) din cadrul arborelui; • Clauza CONNECT BY specifică coloanele prin care se realizează relația ierarhică; acesta este clauza cea mai importantă pentru parcurgerea arborelui și este obligatorie; • Operatorul PRIOR stabilește direcția în care este parcurs arborele. Dacă clauza apare înainte de atributul cod arborele este parcurs de sus în jos, iar dacă apare înainte de atributul sef arborele este parcurs de jos în sus; • Clauza START WITH specifică nodul (înregistrarea) de început al arborelui. Ca punct de start nu se poate specifica un anumit nivel (LEVEL), ci trebuie specificată valoarea; această clauză este opțională, dacă ea lipsește, pentru fiecare înregistrare se va parcurge arborele care are ca rădăcină această înregistrare. În sintaxa interogării de mai sus, pentru a ordona înregistrările returnate, poate apărea clauza ORDER BY, dar este recomandabil să nu o folosim deoarece ordinea implicită de parcurgere a arborelui va fi distrusă.

203 Pentru a elimina doar un anumit nod din arbore putem folosi clauza WHERE, iar pentru a elimina o întreagă ramură dintr-un arbore (o anumită înregistrare împreună cu fii acesteia) folosim o condiție compusă în clauza CONNECT BY. Următorul exemplu elimină doar înregistrarea cu numele ‘GEORGESCU’, dar nu și copii acesteia: SQL> SELECT LEVEL, nume, prenume, grad FROM profesor WHERE nume != 'GEORGESCU' CONNECT BY PRIOR cod=sef START WITH sef IS NULL; LEVEL NUME PRENUME GRAD –– –––- –––- –– 1 GHEORGHIU STEFAN PROF 2 MARIN VLAD PROF 3 IONESCU VERONICA ASIST 4 STANESCU MARIA ASIST 2 ALBU GHEORGHE LECT 2 VOINEA MIRCEA ASIST Pentru a elimina toată ramura care conține înregistrarea cu numele ‘GEORGESCU’ și înregistrările pentru subordonații acesteia se folosește următoarea interogare: SQL> SELECT LEVEL, nume, prenume, grad FROM profesor CONNECT BY PRIOR cod=sef AND nume != 'GEORGESCU' START WITH sef IS NULL; LEVEL NUME PRENUME GRAD –– –––- –––- –– 1 GHEORGHIU STEFAN PROF 2 MARIN VLAD PROF 2 ALBU GHEORGHE LECT 2 VOINEA MIRCEA ASIST 8.2 Comanda INSERT Această comandă este utilizată pentru adăugarea unor rânduri noi într-o tabelă creată anterior sau în tabelele de bază ale unei vederi. Comanda INSERT poate fi utilizată în două moduri: 1. Pentru introducerea datelor într-un tabel, câte o înregistrare la un moment dat. În acest caz sintaxa este următoarea: INSERT INTO tabela [(coloana1, coloana 2, ….)] VALUES (valoare1, valoare2, …); În momentul inserării datelor, trebuie respectate următoarele reguli: • coloanele pot fi specificate în orice ordine, însă trebuie asigurată corespondența între coloane și valorile furnizate (coloanei 1 îi corespunde valoarea 1, coloanei 2 îi corespunde valoarea 2, ș.a.m.d.) iar coloanelor nespecificate le va fi atașată valoarea Null; • în cazul în care coloanele nu sunt specificate explicit, se impune ca ordinea în care apar valorile în comanda INSERT să coincidă cu cea în care coloanele au fost definite la crearea tabelei

204 (dacă nu se cunoaște ordinea de declarare a coloanelor se poate folosi comanda DESCRIBE nume_tabela care va afișa lista coloanelor definite pentru tabela respectivă, tipul, lungimea și restricțiile de integritate); • valorile trebuie să aibă același tip de dată ca și câmpurile în care sunt adăugate; • dimensiunea valorilor introduse trebuie să fie mai mică sau cel mult egală cu dimensiunea coloanei (un șir de 20 de caractere nu poate fi adăugat într-o coloană cu dimensiunea de 15 caractere); • valorile introduse trebuie să respecte restricțiile de integritate definite la crearea tabelei (de exemplu, câmpuri definite ca NOT NULL sau UNIQUE). Atunci când se inserează valori de tip dată calendaristică în format predefinit (DD-MON-YY), sistemul presupune în mod automat secolul 20, ora 00:00:00 (miezul nopții) Următoarea instrucțiune exemplifică introducerea unei noi înregistrări în tabela profesor: SQL> INSERT INTO profesor (cod, nume, prenume, data_nast, sef, salariu, cod_catedra) VALUES (107, 'POPESCU', 'SERGIU', '09-DEC-71', 100, 1200, 20); Se poate observa că valorile coloanelor grad și primă, care nu au fost specificate, vor fi Null. În cazul în care nu se specifică implicit numele coloanelor, valorile trebuie introduse în ordinea în care au fost definite și nu se poate omite valoarea nici unei coloane. Următoarea instrucțiune va produce același efect ca cea de mai sus: SQL> INSERT INTO profesor VALUES (107, 'POPESCU', 'SERGIU', '09-DEC-71', NULL, 100, 1200, NULL, 20); 2. Pentru introducerea datelor într-un tabel, prin copierea mai multor înregistrări dintr-un alt tabel sau grup de tabele; aceste înregistrări sunt rezultatul unei comenzi SELECT. În acest caz sintaxa este următoarea: INSERT INTO tabela [(coloana1, coloana 2, ….)] comanda_select; Și în acest caz trebuie respectate regulile de inserare, singura diferență fiind faptul că valorile noi introduse sunt extrase cu ajutorul unei interogări, acest lucru creând posibilitatea de inserare a mai multor înregistrări în funcție de anumite condiții. De exemplu, pentru a insera în tabela nou_profesor, având coloanele cod, nume, prenume și data_nastere, înregistrările din tabela profesor care au gradul didactic de asistent se poate folosi următoarea instrucțiune: SQL> INSERT INTO nou_profesor (cod, nume, prenume, data_nastere) SELECT cod, nume, prenume, data_nast FROM profesor WHERE grad=’ASIST’; 8.3 Comanda UPDATE

205 Comanda UPDATE este folosită pentru a modifica valorile datelor existente într-un tabel sau în tabelele de bază ale unei vederi și are următoarea sintaxă generală: UPDATE tabela [alias] SET atribuire_coloane, [atribuire_coloane, …] [WHERE condiție]; unde atribuire_coloane poate avea una dintre următoarele forme: coloana = {expresie | (subinterogare)} sau (coloana [,coloana] …) = (subinterogare) Se observă că există două posibilități de modificare: • furnizarea în mod explicit a fiecărei valori sau expresii pentru câmpurile ce trebuiesc modificate; • obținerea valorilor cu ajutorul unei subinterogări. Comanda UPDATE modifică valorile înregistrărilor în funcție de condiția clauzei WHERE. În lipsa clauzei WHERE, vor fi actualizate toate înregistrările din tabelul dat. Expresia furnizată ca nouă valoare a unei coloane poate cuprinde valorile curente ale câmpurilor din înregistrarea care este actualizată. De exemplu, pentru a mări salariul cu 20% și prima cu 100 pentru cadrele didactice ce au gradul de asistent, se va folosi următoarea comandă: SQL> UPDATE profesor SET salariu=salariu*1.2, prima=prima+100 WHERE grad=’ASIST’; Pentru a exemplifica actualizarea datelor utilizând subinterogări presupunem că mai avem o tabelă numită PRIMA ce conține sumele de bani primite suplimentar de unele cadre didactice: COD PRIMA ––– ––––- 102 100 103 200 102 50 Pentru a modifica datele din tabela PROFESOR pe baza datelor din tabela PRIMA se poate folosi următoarea comandă care conține o subinterogare corelată și o subinterogare imbricată: SQL> UPDATE profesor SET prima=(SELECT SUM(prima) FROM prima a WHERE a.cod=profesor.cod) WHERE cod IN (SELECT cod FROM prima); O altă posibilitate este ca sumele suplimentare conținute în tabela PRIMA să fie adăugate la prima existentă în tabela PROFESOR:

206 SQL> UPDATE profesor SET prima=(SELECT SUM (prima) + profesor.prima FROM prima a WHERE a.cod=profesor.cod) WHERE cod IN (SELECT cod FROM prima); Să presupunem acum că toți asistenții sunt transferați la catedra din care face parte cadrul didactic cu codul 104 și vor primi același salariu cu acesta: SQL> UPDATE profesor SET (cod_catedra, salariu) = (SELECT cod_catedra, salariu FROM profesor WHERE cod= 104) WHERE grad = ‘ASIST’; 8.4 Comanda DELETE Comanda DELETE realizează ștergerea înregistrărilor dintr-o tabelă sau din tabelele de bază ale unei vederi în funcție de o anumită condiție și are următoarea sintaxă generală: DELETE FROM tabela [WHERE condiție]; Similar comenzii UPDATE, comanda DELETE șterge anumite înregistrări în funcție de condiția din clauza WHERE. În lipsa clauzei WHERE vor fi șterse toate înregistrările din tabelul dat. În această clauză pot fi incluse și subinterogări. De exemplu următoarea comandă șterge toate înregistrările pentru care gradul didactic este asistent SQL> DELETE FROM profesor WHERE grad=’ASIST’; Observații: Comanda DELETE nu poate fi folosită pentru ștergerea valorii unui câmp individual (pentru aceasta folosiți comanda UPDATE) ci șterge înregistrări complete dintr-un singur tabel. În plus, comanda DELETE șterge numai înregistrări din tabel nu și tabelul. Pentru a șterge un tabel se folosește comanda DROP TABLE. Un alt aspect important este faptul că, similar comenzilor INSERT și UPDATE, ștergerea înregistrărilor dintr-un tabel poate determina apariția unor probleme legate de integritatea referențială. Pentru a evita aceste probleme se pot defini constrângeri de integritate care împiedică operațiile de inserare, actualizare sau ștergere care ar distruge integritatea referențială a datelor. 8.5 Comanda TRUNCATE Pentru a șterge în mod rapid toate înregistrările dintr-o tabelă dau dintr-un cluster se poate folosi comanda TRUNCATE. Comanda TRUNCATE este mult mai rapidă decât comanda DELETE din următoarele motive: • Comanda TRUNCATE este o comandă DDL, prin urmare se execută dintr-o singură tranzacție și deci nu folosește segmentul de revenire. Comanda trebuie folosită cu precauție deoarece nu mai poate fi derulată înapoi.

207 • Comanda TRUNCATE nu declanșează triggerul DELETE (vezi secțiunea 9.16). Comanda are următoarea sintaxă generală: TRUNCATE {TABLE tabel | CLUSTER cluster} [ {DROP | REUSE} STORAGE] unde: • Clauza TABLE specifică numele unei tabele iar clauza CLUSTER specifică numele unui cluster. După cum se observă din sintaxă, aceste două opțiuni sunt alternative, deci nu se poate specifica într-o comandă TRUNCATE ștergerea rândurilor dintr-o tabelă și dintr-un cluster în același timp. În cazul în care se specifică clauza TABLE, tabela la care se referă această clauză nu poate face parte dintr-un cluster. Comanda TRUNCATE se poate executa și asupra tabelelor organizate pe index. La truncherea unei tabele, Oracle șterge automat datele din indecșii tabelei. În cazul în care se specifică clauza CLUSTER, clusterul la care se referă această clauză nu poate fi un cluster hash ci numai un cluster de index. De asemenea, la truncherea unui cluster, Oracle șterge automat datele din indecșii tabelelor clusterului. • Clauza DROP STORAGE eliberează spațiul alocat înregistrărilor șterse din tabel sau cluster. Clauza REUSE STORAGE păstrează spațiul alocat înregistrărilor șterse din tabel sau cluster. Acest spațiu care nu a fost dealocat poate fi reutilizat doar la operații de inserare sau modificare asupra tabelei sau clusterului. Aceste două opțiuni nu modifică efectul pe care îl are comanda TRUNCATE asupra spațiului eliberat de datele șterse din indecșii asociați. Opțiunea implicită este DROP STORAGE. Ștergerea înregistrărilor cu ajutorul comenzii TRUNCATE este mult mai avantajoasă decât eliminarea tabelului și recrearea lui ulterioară deoarece: • Eliminarea tabelului face ca obiectele dependente de acesta să devină invalide, pe când în cazul folosirii comenzii TRUNCATE nu se întâmplă acest lucru; • Comanda TRUNCATE nu necesită reacordarea de drepturi asupra tabelului așa cum se întâmplă dacă acesta a fost eliminat și apoi recreat; • Eliminarea tabelului necesită recrearea indecșilor, constrângerilor de integritate, declanșatoarelor, precum și specificarea parametrilor de stocare. De exemplu, dacă un utilizator execută comanda SELECT COUNT (*) FROM nume_tabel, iar această interogare returnează după un interval destul de îndelungat valoarea zero, se recomandă trunchierea tabelului.