CursSGBD

SISTEME DE GESTIUNE A BAZELOR DEDATE (MySQL, ORACLE)

IOLU Mihai-Stefan

15 octombrie 2014

ii

Cuprins

I MySQL 3

1 Prezentare generala 51.1 De ce MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51.2 Versiunea folosita . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.3 Arhitectura MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

1.3.1 Instalarea programului si rularea serverului . . . . . . . . . . . . . . . 71.3.2 Invocarea programelor client . . . . . . . . . . . . . . . . . . . . . . . 9

2 Interogarea si actualizarea datelor 112.1 Generalitati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

2.1.1 Literali ın MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.1.2 Expresii . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122.1.3 Asignarea de nume la coloane . . . . . . . . . . . . . . . . . . . . . . 132.1.4 Variabile utilizator si instructiunea SET . . . . . . . . . . . . . . . . 132.1.5 Variabilele sistem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132.1.6 Expresia CASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.1.7 Functii scalare . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.1.8 Castingul expresiilor . . . . . . . . . . . . . . . . . . . . . . . . . . . 162.1.9 Expresii scalare compuse . . . . . . . . . . . . . . . . . . . . . . . . . 16

2.2 Interogarea SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.2.1 Baza de date exemplu . . . . . . . . . . . . . . . . . . . . . . . . . . 182.2.2 Exemple de interogari . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2.3 Actualizarea datelor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302.3.1 Interogarea INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . 312.3.2 Interogarea REPLACE . . . . . . . . . . . . . . . . . . . . . . . . . . 322.3.3 Interogarea UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . 332.3.4 Interogarea DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . 342.3.5 Interogarea TRUNCATE . . . . . . . . . . . . . . . . . . . . . . . . . 35

3 Crearea obiectelor bazei de date 373.1 Crearea unei baze de date . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373.2 Tipuri de date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

3.2.1 Tipuri numerice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

iii

3.2.2 Tipuri pentru siruri de caractere . . . . . . . . . . . . . . . . . . . . . 393.2.3 Tipuri pentru lucrul cu date . . . . . . . . . . . . . . . . . . . . . . . 403.2.4 Alegerea eficienta a tipurilor de date . . . . . . . . . . . . . . . . . . 40

3.3 Crearea tabelelor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423.3.1 Crearea unor tabele simple . . . . . . . . . . . . . . . . . . . . . . . . 423.3.2 Copierea tabelelor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453.3.3 Crearea tabelelor temporare . . . . . . . . . . . . . . . . . . . . . . . 463.3.4 Optiuni ale tabelelor . . . . . . . . . . . . . . . . . . . . . . . . . . . 473.3.5 Tabelele si catalogul MySQL . . . . . . . . . . . . . . . . . . . . . . . 503.3.6 Specificarea constrangerilor de integritate . . . . . . . . . . . . . . . . 50

3.4 Stergerea tabelelor - DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . 523.5 Redenumirea tabelelor - RENAME TABLE . . . . . . . . . . . . . . . . . . 533.6 Schimbarea structurii tabelului - ALTER TABLE . . . . . . . . . . . . . . . 53

3.6.1 Schimbarea coloanelor . . . . . . . . . . . . . . . . . . . . . . . . . . 543.6.2 Schimbarea constrangerilor de integritate . . . . . . . . . . . . . . . . 55

3.7 Lucrul cu indecsi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563.7.1 Cum se retin datele? . . . . . . . . . . . . . . . . . . . . . . . . . . . 563.7.2 Ce este un index si cum functioneaza? . . . . . . . . . . . . . . . . . 573.7.3 Procesarea instructiunii SELECT . . . . . . . . . . . . . . . . . . . . 623.7.4 Crearea indecsilor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633.7.5 Definirea indecsilor ımpreuna cu tabela . . . . . . . . . . . . . . . . . 643.7.6 Stergerea indecsilor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643.7.7 Alegerea coloanelor pentru indecsi . . . . . . . . . . . . . . . . . . . . 65

3.8 Optimizarea performantei interogarilor . . . . . . . . . . . . . . . . . . . . . 653.8.1 Lucruri de baza relativ la executia interogarilor . . . . . . . . . . . . 67

3.9 Utilizarea view-urilor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683.9.1 Crearea de view-uri . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693.9.2 Optiuni ale view-urilor . . . . . . . . . . . . . . . . . . . . . . . . . . 703.9.3 Stergerea view-urilor . . . . . . . . . . . . . . . . . . . . . . . . . . . 713.9.4 Restrictii referitoare la actualizarea view-urilor . . . . . . . . . . . . . 713.9.5 De ce sa folosim view-urile? . . . . . . . . . . . . . . . . . . . . . . . 72

4 Crearea obiectelor procedurale 734.1 Proceduri stocate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

4.1.1 Transmiterea parametrilor . . . . . . . . . . . . . . . . . . . . . . . . 744.1.2 Corpul procedurilor stocate . . . . . . . . . . . . . . . . . . . . . . . 744.1.3 Variabile locale si variabile utilizator . . . . . . . . . . . . . . . . . . 744.1.4 Structuri de control . . . . . . . . . . . . . . . . . . . . . . . . . . . . 764.1.5 Apelarea procedurilor stocate . . . . . . . . . . . . . . . . . . . . . . 784.1.6 Interogarea datelor cu SELECT INTO . . . . . . . . . . . . . . . . . 794.1.7 Mesaje de eroare, handlere si conditii . . . . . . . . . . . . . . . . . . 804.1.8 Folosirea cursoarelor . . . . . . . . . . . . . . . . . . . . . . . . . . . 824.1.9 Specificarea anumitor caracteristici ale procedurilor stocate . . . . . . 84

iv

4.1.10 Stergerea procedurilor stocate . . . . . . . . . . . . . . . . . . . . . . 854.1.11 Securitatea procedurilor stocate . . . . . . . . . . . . . . . . . . . . . 854.1.12 Avantaje ale procedurilor stocate . . . . . . . . . . . . . . . . . . . . 85

4.2 Functii stocate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 864.3 Triggere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88

4.3.1 Sintaxa instructiunii CREATE TRIGGER . . . . . . . . . . . . . . . 894.3.2 Stergerea triggerelor . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

4.4 Event-uri . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 914.4.1 Crearea unui eveniment . . . . . . . . . . . . . . . . . . . . . . . . . 914.4.2 Proprietati ale evenimentelor . . . . . . . . . . . . . . . . . . . . . . . 95

4.5 Utilizatori si securitate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 964.5.1 Adaugarea si stergerea utilizatorilor . . . . . . . . . . . . . . . . . . . 964.5.2 Schimbarea numelor utilizatorilor si a parolelor . . . . . . . . . . . . 974.5.3 Acordarea de privilegii la nivel de coloane si tabele . . . . . . . . . . 984.5.4 Acordarea de privilegii la nivelul bazei de date . . . . . . . . . . . . . 994.5.5 Acordarea de privilegii la nivel de utilizator . . . . . . . . . . . . . . 1004.5.6 Dreptul de a da drepturi: optiunea WITH GRANT . . . . . . . . . . 1004.5.7 Restrictionarea privilegiilor . . . . . . . . . . . . . . . . . . . . . . . 1014.5.8 Revocarea privilegiilor . . . . . . . . . . . . . . . . . . . . . . . . . . 101

4.6 Tranzactii si lucrul intr-un mediu multiuser . . . . . . . . . . . . . . . . . . . 1024.6.1 Conceptul de tranzactie . . . . . . . . . . . . . . . . . . . . . . . . . 1024.6.2 Pornirea unei tranzactii . . . . . . . . . . . . . . . . . . . . . . . . . 1034.6.3 Lucrul cu savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . 1034.6.4 Probleme care pot aparea datorita accesului concurent . . . . . . . . 1044.6.5 Mecanismul de locking . . . . . . . . . . . . . . . . . . . . . . . . . . 1054.6.6 Nivelul de izolare . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1074.6.7 Momentul procesarii interogarilor . . . . . . . . . . . . . . . . . . . . 108

II Oracle 109

5 Prezentare generala 1115.1 Generalitati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1115.2 Arhitectura Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1125.3 Instalare Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

6 Interogarea si actualizarea datelor 1156.1 Generalitati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115

6.1.1 Tipuri de date ın Oracle . . . . . . . . . . . . . . . . . . . . . . . . . 1156.1.2 Functii predefinite ın Oracle . . . . . . . . . . . . . . . . . . . . . . . 1176.1.3 Functii numerice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1186.1.4 Functii pentru lucrul cu date si timp . . . . . . . . . . . . . . . . . . 118

6.2 Interogarea SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

v

6.2.1 Crearea bazei de date exemplu . . . . . . . . . . . . . . . . . . . . . . 1196.2.2 Exemple de interogari . . . . . . . . . . . . . . . . . . . . . . . . . . 124

7 Crearea obiectelor bazei de date 1317.1 Lucrul cu tabele . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1317.2 Lucrul cu secvente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1337.3 Lucrul cu indecsi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1347.4 Lucrul cu view-uri . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1357.5 Utilizatori si securitate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

8 Crearea obiectelor procedurale 1398.1 Programarea PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

8.1.1 Structuri conditionale si repetitive . . . . . . . . . . . . . . . . . . . . 1408.1.2 Cursoare . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141

8.2 Proceduri stocate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1428.3 Lucrul cu functii . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1438.4 Lucrul cu pachete . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1448.5 Lucrul cu triggere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1468.6 Tranzactii si lucrul ıntr-un mediu multiuser . . . . . . . . . . . . . . . . . . . 148

vi

Prefata

Sistemele de gestiune a bazelor de date relationale joaca un rol foarte important in ziuade astazi ın numeroase domenii precum: afaceri, cercetare, sisteme educationale, cautari peInternet etc.

Initial, alegerea unui sistem de gestiune a bazelor de date era foarte complicata deoareceea presupunea un pret foarte mare pentru aplicatia propriu-zisa cat si necesitatea existenteiunei infrastructuri foarte complexe. Mai nou, se poate spune ca sistemele de gestiune abazelor de date au ajuns sa poata fi folosite pe orice calculator. Bineinteles, exista si exceptiicum ar fi ın cazul unor sisteme de gestiune a bazelor de date foarte complexe, precum Oracle.

Dupa cum se stie, lumea sistemelor de gestiune a bazelor de date este o lume foartedinamica, ın care exista numerosi jucatori pe piata. Cei mai mari sunt Oracle, IBM (DB2),Microsoft (Sql Server), MySQL, Postgresql, SQLite, etc...

In cele ce urmeaza vom prezenta doua solutii, una care provine din lumea open-source,cum este cazul MySQL, precum si una care vine din lumea enterprise, cazul Oracle.

Voi prezenta, pe cat posibil, atat lucruri care sunt utile cu precadere pentru dezvoltatori(limbajul SQL cu extensiile sale corespunzatoare fiecarui SGBD) cat si lucruri care tin deadministrarea serverului de baze de date.

1

2

Partea I

MySQL

3

Capitolul 1

Prezentare generala

Unul din cele mai raspandite sisteme de gestiune a bazelor de date este MySQL. Dezvol-tarea lui a ınceput in 1979 cand compania TcX (prin Michael ”Monty” Widenius) a ınceputdezvoltarea unui instrument de lucru cu baze de date numit UNIREG. In 1994, companiaa ınceput munca pentru un SGBD care sa foloseasca SQL pentru a realiza comunicarea cuaplicatii Web.

Ei au avut nevoie de un nou SGBD deoarece cele existente deja (cum ar fi mSQL)prezentau prea putine facilitati.

In 1996 a fost lansat MySQL prin versiunea 3.11.11, versiune disponibila numai pentruLinux si Solaris. Intre timp, a aparut compania MySQL AB care s-a ocupat de dezvoltareaacestui produs. In 2008, aceasta a fost ınghitita de Sun Microsystems, care ıntre timp a fostcumparata de Oracle.

Initial, MySQL era un SGBD caruia ıi lipseau numeroase facilitati precum suportul pentrutranzactii sau procedurile stocate, dar ın ultima vreme, toate acestea si multe altele au fostincluse ın distributiile MySQL, astfel ıncat acesta a devenit un SGBD foarte complex, ıntrandın categoria enterprise.

Un alt mare avantaj pe care-l prezinta acest SGBD este faptul ca el ruleaza sub numeroaseplatforme precum Window, Linux, Solaris si multe altele.

1.1 De ce MySQL?

In momentul ın care dorim sa alegem un SGBD care sa ne coste cat mai putin si sa nunecesite foarte multe resurse, avem mai multe posibile optiuni: MySQL, PostgreSQL, SQLitesi multe altele.

MySQL prezinta mai multe avantaje:

1. MySQL este unul din cele mai rapide SGBD-uri de pe piata.

2. Prezinta performante foarte bune dar este mult mai usor de administrat si folosit decatalte SGBD-uri.

5

3. MySQL foloseste SQL (Structured Query Language), care este cel mai folosit standardın piata.

4. Are numeroase capabilitati: este multi-threading (ceea ce ınseamna ca mai multi clientise pot conecta simultan), poate fi folosit ın mai multe moduri (prin aplicatii clientconsola, aplicatii client grafice, aplicatii client web, prin diverse interfete de programarede catre limbaje precum C, C++, Java, PHP etc.).

5. Poate fi folosit ın retea si chiar accesat de pe Internet. Prezinta posibilitatea de a lucracu el folosind SSL (Secure Sockets Layer).

6. Este foarte portabil.

7. Are o dimensiune foarte mica.

8. Este un program open-source, care ın cele mai multe cazuri poate fi folosit free.

9. Este folosit pe scara larga, ceea ce face ca documentatia sa fie foarte usor de obtinut.

1.2 Versiunea folosita

De-a lungul timpului MySQL a cunoscut o dezvoltare continua. Au existat numeroaseversiuni, fiecare aducand ımbunatatiri mai mult sau mai putin consistente.

In cele ce urmeaza vom prezenta functionalitati prezente ın versiunile de la 5.0. Versiu-nea 5.0 a introdus proceduri si functii stocate, view-uri, trigger-e, ”strict input handling”,VARCHAR adevarat si INFORMATION SCHEMA.

Versiunea 5.1 a introdus event scheduler-ul precum si suport pentru XML.

Actuala versiune, 5.5, a introdus si ea numeroase ımbunatatiri:

• Introducerea unui plugin pentru un thread pool nou (ınainte pentru fiecare conexiunecu un client se folosea un nou fir de executie)

• Incepand cu versiunile comerciale de la 5.5.16 se introduc noi module de autentificare(PAM authentification - printr-o interfata standard, prin servicii Windows native)

• A fost ımbunatatita scalabilitatea pe mai multe CPU-uri

• Engine-ul de stocare implicit pentru tabele a devenit InnoDB, ın dauna lui MyISAM

• etc.

6

1.3 Arhitectura MySQL

MySQL ruleaza ıntr-un mediu bazat pe retea si are o arhitectura de tip client/ server. Cualte cuvinte, exista un program principal care ruleaza (serverul) si mai multe alte programecare se conecteaza la el, fac cereri si primesc raspunsuri numite programe client.

Atunci cand instalam MySQL, de fapt instalam urmatoarele componente:

• Serverul MySQL sau mysqld este aplicatia care raspunde la cereri si care face manage-mentul accesului la disk sau la memorie. Serverul este multi-threaded, ceea ce ınseamnaca suporta mai multi clienti simultan. Pentru managementul bazelor de date, el poatefolosi mai multe mecanisme (putem avea baze de date care sa suporte tranzactii si bazede date care nu suporta tranzactiile).

mysqld prezinta mai multe variante, care fiecare dintre ele aduc ceva nou fata devarianta simpla. mysqld-nt, de exemplu, ofera suport pentru lucrul cu named pipes, ıntimp ce mysqld-max ofera mai multe posibilitati pentru stocarea bazelor de date.

• Aplicatiile client sunt niste aplicatii pe care cei care au creat MySQL le-au ganditpentru ca noi sa putem accesa si prelucra datele din bazele de date. Practic, noifolosim aceste programe care la randul lor se conecteaza la server pentru fiecare dinoperatiile pe care noi le facem. Avem mai multe tipuri de astfel de aplicatii:

– MySQL Workbench este o aplicatie grafica care ne permite sa manipulam serverulde baze de date.

– mysql este o aplicatie ın linie de comanda care ne permite sa transmitem interogariserverului si sa vedem rezultatele acestora.

– Exista de asemenea si alte programe client precum: mysqlimport pentru impor-tarea datelor, mysqldump pentru backup-uri, mysqladmin pentru managementulserverului si mysqlcheck pentru verificarea integritatii bazelor de date.

• De asemenea, exista si programe care sunt independente de server si care fac fiecareanumite lucruri: myisamchk verifica tabele si face corectii daca e cazul, myisampackcare creeaza arhive read-only ale unor tabele MyISAM. Toate aceste utilitare nu seconecteaza niciodata la server, ele lucrand independent de acesta.

Dupa cum se poate usor banui nu este necesar ca aplicatia client si aplicatia server sa seafle pe acelasi calculator pentru a putea comunica. Mai mult, se poate ca ele sa se afle pesisteme de operare diferite si acest lucru este ın continuare posibil.

1.3.1 Instalarea programului si rularea serverului

In cele ce urmeaza voi prezenta pasii necesari pentru instalarea MySQL pe un calculatorcare are sistemul de operare Windows.

Intai se downloadeaza MySQL (versiunea 5.5.16) de la adresa de internet:http://dev.mysql.com/downloads/mysql/5.5.html#downloads.

7

Avem mai multe modalitati de a instala programul. Pentru ınceput vom alege ”Withoutinstaller” care presupune doar dezarhivarea programului. Dupa ce se termina download-ul,dezarhivam fisierul obtinut direct ın locul ın care se doreste instalarea.

In directorul corespunzator vom vedea existenta mai multor alte directoare, printre caresi directorul bin. In acest director se gasesc aplicatiile corespunzatoare serverului, clientilordar si altor utilitare.

Primul pas pe care dorim sa-l facem este pornirea serverului. Acest lucru se poate faceprin rularea programului mysqld. Pentru a putea fi siguri ca totul este ın regula si serverulruleaza, trebuie sa dam Ctrl+Alt+Del si sa cautam aplicatia mysqld.exe. Daca aceastaruleaza, atunci serverul este pornit.

Initial avem creat un user root care nu are parola. Putem folosi acest lucru pentru a neconecta la serverul de baze de date. Putem folosi aplicatia client mysql.exe ın felul urmator:

mysql -u root

Vom putea observa faptul ca ıncepand din acest moment avem un prompt care ne permiteintroducerea comenzilor. Prezentam ın continuare cateva comenzi:

show databases;

use mysql

show tables;

select host, user, password from user;

quit

Prima comanda permite afisarea tuturor bazelor de date. A doua selecteaza o baza dedate (mysql) ca baza activa, pentru ca din acest moment, comenzile sa se refere la aceastabaza de date. A treia comanda permite afisarea tabelelor din baza de date curenta. Ultimacomanda afiseaza informatiile despre utilizatorii ınregistrati. Se poate observa ca pentruutilizatorul root nu avem nici o parola. Ultima comanda are ca efect iesirea din promptulmysql.

Un lucru important care trebuie facut atunci cand se instaleaza MySQL este schimbareaparolei utilizatorului root. Acest lucru se poate face prin comanda, introdusa ın promptulMySQL:

set password for root@localhost=password(’parola’);

Urmatorul pas ar putea fi downloadul aplicatiilor client care au interfete grafice. Acestlucru se poate face de la adresahttp://dev.mysql.com/downloads/workbench/5.2.html,dupa care se poate dezarhiva fisierul obtinut. Acesta contine aplicatia MySQLWorkbench.

Aceasta aplicatie poate fi pornita, dar pentru ca ea sa poata rula, trebuie introduseanumite informatii precum calculatorul gazda (host - unde putem introduce ”localhost”daca serverul se afla pe acelasi calculator), precum si numele utilizatorului ın numele caruiane conectam.

8

Desi nu are legatura ın mod direct cu subiectul prezentat, trebuie mentionat ca exista odocumentatie foarte cuprinzatoare referitoare la modul de utilizare a MySQL-ului pe situldedicat de la urmatoarea adresa:http://dev.mysql.com/doc/

1.3.2 Invocarea programelor client

Programele client, dupa cum am mai spus pot fi de doua feluri:

• Programe client invocate din linie de comanda (mysql)

• Programe client de tip GUI (MySQL Workbench)

Cand se invoca un program client, ın general trebuie specificate optiuni care controleazacomportamentul lui. Acestea pot fi date fie ”live” fie ın fisiere de optiuni. Unele optiunispecifica cum se face conectarea, ın timp ce altele care este operatia propriu-zisa care sedoreste a fi executata.

Pentru clientii GUI aceste optiuni pot fi specificate si ın cadrul interfetei grafice.Voi prezenta ın continuare cu precadere modul de lucru cu aplicatia client mysql.Pentru a putea vedea optiunile disponibile, putem introduce:

mysql --help

Pentru a putea vedea versiunea serverului pe care lucram:

mysql --version

Pentru a ne putea conecta la un calculator aflat la o anumita adresa putem scrie instructiunide forma urmatoare. Toate cele 3 instructiuni sunt echivalente:

mysql --host=myhost.example.com

mysql -h myhost.example.com

mysql -hmyhost.example.com

Cel mai des ne conectam la un server MySQL folosind o instructiune de forma:

mysql -h host name -P port number -u user name -p

Aceasta comanda are ca efect conectarea la serverul aflat pe calculatorul host name,server care ruleaza pe portul port number, ın numele utilizatorului user name si dorim safim ıntrebati referitor la parola. Oricare dintre aceste optiuni poate lipsi.

De exemplu, daca nu specificam -P se considera ca lucram pe portul implicit 3306.Pentru a nu introduce optiunile de conectare de fiecare data, ın cazul ın care se lucreaza

mereu cu acelasi server, se pot crea fisiere de optiuni care sa fie refolosite ın cazul mai multorconectari succesive.

Optiunile din fisierele de optiuni sunt organizate pe grupuri precum [mysql], [mysqldump]sau [client]. In grupul [mysql] avem optiuni referitoare la aplicatia client mysql, ın timpce ın grupul [client] avem optiuni comune tuturor aplicatiilor client.

Un astfel de fisier ar putea arata astfel:

9

[client]

host = myhost.example.com

compress

[mysql]

safe-updates

Pe Windows, sunt cautate ın ordine fisierele my.ini si my.cnf ıntai ın directorul Windowssi apoi pe ”c:”.

Uneori, ın momentul ın care dorim sa rulam o aplicatie client, este bine sa ıi specificamsi baza de date pe care dorim sa lucreze ca ın exemplul urmator:

mysqldump -u mihai -p database name > file.sql

Aceasta comanda are ca efect crearea unui fisier file.sql care va cuprinde toate interogarileSQL necesare recreerii acestei baze de date, daca acest lucru s-ar dori.

Un astfel de fisier ar putea fi rerulat pe un alt calculator, pentru recreerea bazei de date,fie folosind aplicatia client mysql :

mysqldump database name < file.sql

fie direct din promptul mysql:

source file.sql;

Nu vom prezenta ın cele ce urmeaza lucrul cu aplicatii client grafice deoarece optiunilecare trebuie specificate ın cazul lor sunt aceleasi, doar ca ele se specifica ın ferestre de dialogdedicate.

10

Capitolul 2

Interogarea si actualizarea datelor

2.1 Generalitati

2.1.1 Literali ın MySQL

In multe din interogarile pe care le vom scrie de-a lungul timpului vom folosi literali.Acestia sunt de mai multe tipuri: numeric, alfanumeric, temporal, boolean si hexazecimal.

Fiecare literal are un tip de date, dar nu neaparat pentru fiecare tip de date exista literali.Prezentam niste exemple de interogari ın care am scris ıngrosat literalii care apar:

insert into products values( 1, ”televizor LCD”);select from products where productId = 1;

Nu vom prezenta toate tipurile de literali posibili ci doar pe cele care prezinta atributemai deosebite.

Literalii alfanumerici poti fi precizati folosind ghilimele duble (”) sau simple (’) laınceputul si sfarsitul literalului. Exista si caractere speciale care sunt prezentate ın tabelulurmator:

Caracterul special Ce produce\0 Caracterul ASCII 0\’ Caracterul ghilimea simpla\” Caracterul ghilimea dubla\b Caracterul backspace\n Caracterul linie noua\r Caracterul carriage return\t Caracterul tab\z Caracterul cu cod ASCII 26 (Ctrl+Z)

Literalii de tip data sunt cuprinsi ıntre ghilimele si au trei componente: an, luna si zi.Ca separatori se pot folosi caracterele ’-’, ’/’, ’@’ si ’%’.

Date corecte sunt: ’1980-12-13’ (13 dec 1980), ’1980/12/13’, ’1980@12@13’.Se poate si sa introducem date prin folosirea de numere ıntregi ca ın exemplele urmatoare:

19801208 (8 dec 1980), 991111(11 nov 1999).

11

Literalii pentru date trebuie sa fie cuprinsi ıntre 1 ianuarie 1000 si 31 decembrie 9999.Pentru ca sa putem fi siguri ca operatiile cu date vor functiona corect, trebuie ca dateleintroduse sa fie corecte.

Literalul de timp specifica un moment de timp din cadrul unei zile. Se specifica totıntre ghilimele, ca ın exemplele urmatoare: ’23:38:45’, ’14:00’, ’14’ (reprezinta ora 0:00:14),’10:00:34.000023’.

Se poate ca sa se foloseasca acest literal si pentru a retine durate de timp. O expresieprecum ’8 10:00:00’ reprezinta ’8 zile si 10 ore’.

Literalii datetime si timestamp sunt combinatii intre literalii pentru data si literalii pentrutimp. Un literal de acest tip arata ın felul urmator: ’1998-10-23 10:34:21.002345’.

Desi cei doi literali sunt aproape identici, exista si diferente care tin de intervalele pe carele pot retine. O variabila de tip timestamp poate retine date cu anii ıntre 1970 si 2037, pecand o variabila de tip datetime poate retine date cuprinse ıntre anii 1000 si 9999.

Literalul boolean este cel mai simplu literal deoarece poate avea doar doua valori: true

si false.Literalul hexazecimal este folosit pentru a retine numere ın baza 16. El poate avea o

varianta alfanumerica (x’41’, X’41’) sau o varianta numerica (0x23, 0xAF).Literalul de tip bit este un literal alfanumeric care are ın fata caracterul ’b’ sau ’B’,

urmat numai de valori de 0 si 1, ca ın exemplele urmatoare: b’1001’ (valoarea 9) sau B’101’(valoarea 5).

2.1.2 Expresii

Expresiile sunt combinatii de literali, nume de coloane, calcule complexe, operatori sifunctii care sunt evaluate ıntr-o anumita ordine si duc la un anumit rezultat.

In urmatoarea interogare avem 2 expresii:

select nume, venit* 120/100

from salarii

where salariu < @salariu_mediu

Fiecare expresie are ın urma evaluarii un anumit tip de date.Expresiile pot fi de mai multe tipuri:

• expresii scalare care au ca rezultat un numar sau o data.

• expresii care au ca rezultat o ınregistrare (row expressions). O astfel de expresie arataastfel:

(1, "John", "Romania")

• expresii care au ca rezultat o tabela (table expressions). Un exemplu de astfel deexpresie este:

((1, "Adi", "Romania"),

(2, "Maria", "France"))

12

2.1.3 Asignarea de nume la coloane

In MySQL, la fel ca si ın majoritatea SGBD-urilor putem da un nume nou unei coloaneın felul urmator:

select nume, prenume, venituri - datorii as profit

from persoane

Daca n-am fi specificat un nume pentru coloana a 3-a, automat MySQL i-ar fi dat numele’venituri-datorii’. Se poate specifica un nou nume de coloana si pentru coloanele care n-auexpresii compuse.

2.1.4 Variabile utilizator si instructiunea SET

Variabilele definite de utilizator pot fi initializate folosind expresii scalare.Inainte de a folosi o variabila aceasta treubuie definita si initializata. Daca ea nu este

initializata, automat are valoarea NULL.Pentru a defini si initializa o variabila putem folosi instructiunea SET:

SET @player = 2;

Nu trebuie specificat tipul variabilei deoarece acesta este dat de expresia cu care esteinitializat. Aceste variabile utilizator pot fi folosite ulterior ın interogari:

select user, userid

from users

where user_id < @player;

Pentru a putea vizualiza valoarea unei astfel de variabile utilizator putem folosi instructiuneaSELECT:

SELECT @player;

2.1.5 Variabilele sistem

Variabilele sistem, la fel ca si cele utilizator au un tip de date si o valoare. Diferentaconsta ın faptul ca MySQL introduce si initializeaza aceste variabile.

Ele se ımpart ın doua mari categorii:

• variabile sistem globale - Sunt initializate de fiecare data cand este pornit MySQL sisunt vazute ın toate sesiunile. Unele pot fi modificate prin folosirea instructiunii SET,ın timp ce altele precum VERSION sunt read-only.

Prezentam un exemplu:

select @@global.version;

13

• variabile sistem de sesiune - Sunt variabile care au, ın general un corespondent variabilaglobala, dar pe care daca le modificam noile valori se potrivesc numai sesiunii curente.

Iata si niste exemple:

select @@session.sql warnings;

set @@session.sql warnings=true;

set @@session.sql select limit=20;

select @@session.sql select limit;

Putem vedea faptul ca valoarea variabilei globale nu s-a schimbat si ca este o valoarefoarte mare prin instructiunea:

select @@global.sql select limit;

Daca nu se specifica nici un modificator, se subıntelege utilizarea lui SESSION (poate fiınlocuit cu LOCAL).

Variabilele globale de sistem pot fi initializate ın fisierele de configurare precum "my.ini".De asemenea, se pot seta ın momentul ın care se porneste serverul MySQL, ca ın exemplulurmator:

mysqld --SQL SELECT LIMIT=1000

Putem vedea o lista a tuturor variabilelor sistem (toate, globale si de sesiune) folosinduna din urmatoarele comenzi:

show variables;

show global variables;

show session variables;

Din dorinta ca MySQL sa respecte standarde la care mai multe produse au aderat,exista anumite variabile de sistem, pentru care nu mai trebuie folosite caracterele ”@@”(astfel de exemple sunt: CURRENT USER, CURRENT DATE, CURRENT TIME, CUR-RENT TIMESTAMP).

select current user;

select *

from persoane

where last used date = current date;

14

2.1.6 Expresia CASE

Expresia CASE se foloseste cu un sens asemanator celui pe care-l cunoastem ın cazulinstructiunii SWITCH din Java sau C++.

Iata un exemplu:

select nume, case sex

when ’f’ then ’Femeie’

else ’Barbat’

end as sex, oras

from persoane;

Aceasta interogare ar putea fi scrisa si astfel:

select nume, case sex

when ’f’ then ’Femeie’

when ’m’ then ’Barbat’

end as sex, oras

from persoane;

Daca nu se potriveste nici o expresie, atunci se va returna valoarea NULL.Putem construi si interogari mult mai complicate, prin folosirea de CASE-uri imbricate:

select idpersoana, nume, case sex

when ’m’ then

case when year(data nasterii)>2000 then ’Baietel’

else ’Barbat’

end

when ’f’ then

case when year(data nasterii)>2000 then ’Fetita’

else ’Femeie’

end

end as Gen

from persoane;

Evident, ın clauzele when putem pune si conditii, nu numai stringuri sau valori fixe. Cando expresie este evaluata ca fiind corecta, celelalte nu mai sunt evaluate.

Aceste expresii CASE pot fi folosite oriunde sunt permise expresii scalare, adica si ınclauzele HAVING sau WHERE.

2.1.7 Functii scalare

Functiile scalare sunt folosite pentru a efectua calcule si transformari.O astfel de functie primeste mai multi parametri scalari si are o valoare scalara. Avem

numeroase functii scalare pentru lucrul cu numere, siruri de caractere, date, etc.Pentru a consulta o lista completa a acestora este recomandata utilizarea documentatiei

MySQL care se gaseste la adresa:

15

http://dev.mysql.com/doc/refman/5.5/en/index.html

Iata si cateva exemple de utilizare a functiilor scalare:

select pow(3,5);

select nume, year(data_nasterii)

from persoane;

select ucase(’test’);

select replace(’maria’,’a’,’e’);

2.1.8 Castingul expresiilor

De multe ori, ın momentul ın care folosim un literal tipul de data al acestuia este evident.Sunt totusi situatii cand MySQL trebuie sa faca casturi sau cand dorim sa facem propriilenoastre cast-uri.

Putem preciza un cast prin expresii de genul urmator:

cast(’123’ as signed integer)

cast(’122035’ as time)

cast(123 as char)

Tipurile de date acceptate ıntr-o operatıe de cast sunt: binary(lungime), char(lungime),date, datetime, decimal(precizie, scala), [signed] integer, time, unsigned [integer].

2.1.9 Expresii scalare compuse

Evident, si ın MySQL se pot forma expresii compuse.In principiu, ın MySQL sunt valabili operatorii pentru numere pe care-i cunoastem din

Java sau C++, la care se adauga operatorul DIV.De asemenea, MySQL suporta exact aceeasi operatori pe biti.Lucruri mai interesante si foarte folositoare ıntalnim atunci cand vorbim despre lucrul cu

date. Se poate sa scriem interogari de genul:

SELECT *

FROM persoane

WHERE data nasterii > CURRENT DATE - INTERVAL 20 YEAR;

Aceasta interogare are ca efect returnarea tuturor datelor referitoare la persoane cares-au nascut ın ultimii 20 ani.

Exista astfel de notatii pentru: DAY, WEEK, MONTH, QUARTER, YEAR, YEAR MONTH.Intervalul poate fi scazut sau adunat.

Un lucru asemanator se poate face atunci cand este vorba de timp:

select nume, ora_incepere, ADDTIME(ora_incepere,’3:30:00’)

from evenimente

16

2.2 Interogarea SELECT

Una din cele mai complicate interogari ın MySQL este interogarea SELECT. Ea este ınacelasi timp, probabil, si cea mai folosita interogare.

Complexitatea ei poate fi usor observata din forma generala care poate fi regasita ındocumentatia MySQL si care este prezentata ın continuare.

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH PRIORITY]

[STRAIGHT JOIN]

[SQL SMALL RESULT] [SQL BIG RESULT] [SQL BUFFER RESULT]

[SQL CACHE | SQL NO CACHE] [SQL CALC FOUND ROWS]

select expr [, select expr ...]

[FROM table references

[WHERE where condition]

[GROUP BY col name | expr | position

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where condition]

[ORDER BY col name | expr | position

[ASC | DESC], ...]

[LIMIT [offset,] row count | row count OFFSET offset]

[PROCEDURE procedure name(argument list)]

[INTO OUTFILE ’file name’

[CHARACTER SET charset name]

export options

| INTO DUMPFILE ’file name’

| INTO var name [, var name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

In cele ce urmeaza nu vom intra chiar ın cele mai mici amanunte privitor la aceastasintaxa, dar vom ıncerca sa ıntelegem modul de functionare al acestei interogari.

Trebuie facute cateva precizari:

• O interogare SELECT trebuie sa contina macar clauza SELECT.

• Daca se folosesc WHERE, GROUP BY sau HAVING atunci este obligatorie si clauzaFROM.

• Ordinea clauzelor este fixa, ceea ce ınseamna ca mereu dupa SELECT, vom aveaFROM, WHERE, GROUP BY, HAVING si ORDER BY.

Sa presupunem ca avem urmatoarea interogare:

17

SELECT PLAYERNO

FROM PENALTIES

WHERE AMOUNT > 25

GROUP BY PLAYERNO

HAVING COUNT(*) > 1

ORDER BY PLAYERNO

Pentru o astfel de interogare, procesarea fiecarei clauze consta ıntr-o tabela intermediaracare consta din 0 sau mai multe ınregistrari, fiecare avand cel putin una sau mai multecoloane.

Se parcurg urmatorii pasi:

• Clauza FROM are ca date de intrare informatii din unul sau mai multe tabele, creındu-se un tabel temporar cu aceste informatii.

• Clauza WHERE sterge din acest tabel temporar acele ınregistrari care nu ındeplinescconditiile.

• Clauza GROUP BY are ca efect gruparea ınregistrarilor ın tabela temporara ın functiede campul specificat.

• Clauza HAVING se aplica asupra tabelei intermediare create dupa grupare. Astfel, ınacest exemplu se pastreaza acele numere de jucatori pentru care exista mai mult de oınregistrare.

• Clauza ORDER BY nu are efect asupra datelor care vor fi obtinute, ci doar le sorteazadupa cum este specificat.

2.2.1 Baza de date exemplu

Problema pentru care trebuie sa cream o baza de date suna ın felul urmator:Dorim sa creem o baza de date care sa permita retinerea notelor studentilor unei facultati.

Pentru un student se cunosc nrmatricol, numele, prenumele, sexul, data nasterii, codulnumeric personal, orasul, judetul si tara din care provine.

De asemenea, avem mai multe tipuri de cursuri.Ele pot fi obligatorii, optionale sau fa-cultative.

Pentru fiecare curs se cunosc: codul cursului, denumirea cursului, tipul cursului, numarulde credite aferent cursului precum si codul profesorului care tine acel curs.

Studentii, pe baza preferintelor lor se pot ınscrie la mai multe cursuri. Pentru fiecareastfel de ınscriere trebuie sa putem retine data la care studentul s-a ınscris.

Studentii primesc note la materiile la care s-au ınscris. Notele sunt cuprinse ıntre 1 si 10,nota de promovare fiind 5. Studentii pot da un examen din nou daca doresc sa-si mareascanota obtinuta. Nota care se ia ın considerare pentru fiecare student este ultima nota pe carea obtinut-o.

In cele ce urmeaza vom lucra pe urmatoarea baza de date, care permite retinerea notelorstudentilor:

18

studenti (nrmatricol, nume, prenume, sex, CNP, datan, oras,

judet, tara)

profesori (cod prof, nume, prenume, grad, sex, datan)

tipuri_curs (tip_curs, den_tip)

cursuri (cod_curs, den_curs, tip_curs , credite, cod_profesor )

inrolari (cod_inrolare, nrmatricol , cod_curs , data_inrolare)

note (cod_nota, cod_inrolare , nota, data)

Codul care poate crea si popula aceasta baza de date este urmatorul:

drop database if exists mydatabase;

create database mydatabase;

use mydatabase;

create table tipuri_curs(

tip_curs bigint primary key auto_increment,

den_tip varchar(30) not null

);

insert into tipuri_curs(den_tip) values

("obligatoriu"),

("optional"),

("facultativ");

create table profesori(

cod_prof bigint primary key auto_increment,

nume varchar(30) not null,

prenume varchar(30) not null,

grad varchar(30) not null,

sex char(1) not null,

datan date

);

insert into profesori values

(null, "Iolu","Mihai","lector","m",’1979-05-03’),

(null, "Bocu","Dorin","profesor","m",’1955-08-03’),

(null, "Ciurea","Eleonor","profesor","m",’1943-07-13’),

(null, "Sasu","Lucian","lector","m",’1977-08-02’),

(null, "Deaconu","Adrian","conferentiar","m",’1974-03-13’),

(null, "Ciupala", "Laura", "conferentiar", "f", ’1976-02-20’);

create table studenti(

nrmatricol bigint primary key not null,


19


CNP varchar(13) not null,

sex char(1)not null,

datan date not null,

oras varchar(30) not null,

judet varchar(30) not null,

tara varchar(30) not null

);

insert into studenti values

(101, "Florescu","Radu","1870409030182","m",’1987-04-09’,

"Bucuresti","Bucuresti","Romania"),

(102, "Ceausescu","Ionut","1880323020382","m",’1988-03-23’,

"Brasov","Brasov","Romania"),

(103, "Ciumarnean","Oana","2880520435434","f",’1988-05-20’,


(104, "Neagu","Andrei","1871115456537","m",’1987-11-15’,

"Sinaia","Prahova","Romania"),

(105, "Saracu","Ana-Maria","1880308345322","f",’1988-03-08’,

"Fagaras","Brasov","Romania"),

(106, "Ionita","Emanuel-Ionut","1881023345433","m",’1988-10-23’,

"Victoria","Brasov","Romania"),

(107, "Dumitrescu","Teodora","2881220030325","f",’1988-12-20’,

"Cluj-Napoca","Cluj","Romania"),

(108, "Radoi","Ovidiu","1871005325323","m",’1987-10-05’,

"Munchen","Bavaria","Germania"),

(109, "Tanase","Alexandra","2880301345436","f",’1988-03-01’,


(110, "Mocanu","Laura","2880402342344","f",’1987-04-02’,

"Brasov","Brasov","Romania");

create table cursuri(

cod_curs bigint primary key auto_increment,

den_curs varchar(30) not null,

tip_curs bigint not null,

credite int(2),

cod_profesor bigint not null);

insert into cursuri values

(null, "Design patterns",2, 6, 1),

(null, "SGBD (MySQL, Oracle)",1, 5, 1),

(null, "Inginerie software",1, 6, 2),

20

(null, "POO 2",1, 6, 2),

(null, "Algoritmica",1, 6, 3),

(null, "Algoritmica grafurilor",1, 4, 3),

(null, "Programare C#",1, 5, 4),

(null, "Inteligenta artificiala",1, 4, 4),

(null, "Programare procedurala",1, 6, 5),

(null, "Optimizare combinatorie",1, 3, 6);

create table inrolari(

cod_inrolare bigint primary key auto_increment,

nrmatricol bigint not null,

cod_curs bigint not null,

data_inrolare date not null);

insert into inrolari values

(null, 101, 7, ’2007-09-03’),

(null, 101, 1, ’2007-09-09’),

(null, 102, 8, ’2007-09-14’),

(null, 102, 2, ’2007-09-03’),

(null, 102, 9, ’2007-09-08’),

(null, 103, 3, ’2007-09-14’),

(null, 104, 8, ’2007-09-07’),

(null, 105, 2, ’2007-09-09’),

(null, 107, 1, ’2007-09-03’),

(null, 107, 3, ’2007-09-12’),

(null, 108, 2, ’2007-09-08’),

(null, 108, 9, ’2007-09-08’),

(null, 109, 8, ’2007-09-13’),

(null, 110, 7, ’2007-09-14’),

(null, 110, 4, ’2007-09-14’);

create table note(

cod_nota bigint primary key auto_increment,

cod_inrolare bigint not null,

nota int(2) not null,

data date not null);

insert into note values

(null, 1, 8, ’2008-01-10’),

(null, 1, 10, ’2008-01-20’),

(null, 2, 3, ’2008-01-12’),

(null, 3, 6, ’2008-01-14’),

21

(null, 4, 9, ’2008-01-16’),

(null, 6, 7, ’2008-01-12’),

(null, 7, 4, ’2008-01-14’),

(null, 7, 8, ’2008-01-12’),

(null, 8, 9, ’2008-01-10’),

(null, 10, 3, ’2008-01-14’),

(null, 10, 5, ’2008-01-16’),

(null, 12, 7, ’2008-01-13’),

(null, 13, 4, ’2008-01-8’),

(null, 14, 3, ’2008-01-10’),

(null, 14, 4, ’2008-01-20’);

2.2.2 Exemple de interogari

Sa se rezolve urmatoarele interogari:

1. Sa se afiseze toate informatiile despre toti studentii:

SELECT * FROM studenti;

2. Sa se afiseze pentru toti studentii numele, prenumele si locul din care provin:

SELECT nume, prenume, CONCAT(oras,", ",judet,", ",tara)

AS "Locul de provenienta"

FROM studenti;

3. Sa se afiseze numele, prenumele si orasul din care provin pentru studentii care sunt dinorasul ”Brasov”:

SELECT nume, prenume, oras

FROM studenti

WHERE oras="brasov";

4. Sa se afiseze numele, prenumele si data nasterii pentru studentii care s-au nascut ınaintede 1988.

SELECT nume, prenume, datan

FROM studenti

WHERE YEAR(datan) < 1988;


FROM studenti

WHERE datan < ’1988-01-01’;

22

5. Sa se afiseze numele si prenumele persoanelor care s-au nascut ın orasele Brassov sauBucuresti:


FROM studenti

WHERE oras=’brasov’ or oras=’bucuresti’;


FROM studenti

WHERE oras IN (’brasov’,’bucuresti’);

6. Sa se afiseze numele, prenumele, orasul si data nasterii pentru acei studenti care s-aunascut ın Brasov ın anul 1988.

SELECT nume, prenume, oras, datan

FROM studenti

WHERE oras=’Brasov’ and year(datan)=1988;

SELECT nume, prenume, oras, datan

FROM studenti

WHERE (oras,YEAR(datan)) = ("brasov", 1988);

7. Sa se afiseze numele, prenumele si data nasterii tuturor profesorilor pentru care datanasterii este nula:


FROM profesori

WHERE datan IS NULL;

Pentru a se verifica daca un camp este sau nu NULL, nu se foloseste operatorul = ci”IS NULL” sau ”IS NOT NULL”.

8. Sa se afiseze numele si prenumele tuturor studentilor ordonati alfabetic dupa nume siprenume:

SELECT nume, prenume

FROM studenti

ORDER BY nume, prenume;

9. Sa se afiseze numele, prenumele si orasul pentru studenti, ordonate ın functie de orascrescator si apoi ın functie de nume si prenume descrescator.

23


FROM studenti

ORDER BY oras, nume DESC, prenume DESC;

10. Sa se sorteze crescator profesorii dupa data nasterii:

SELECT *

FROM profesori

ORDER BY datan;

11. Sa se sorteze crescator profesorii dupa data nasterii, cei care nu au data cunoscutafiind asezati la sfarsit:

SELECT *

FROM profesori

ORDER BY IF(datan IS NULL, 1, 0), datan;

12. Sa afiseze numele si prenumele primilor cinci studenti:


FROM studenti

LIMIT 5;

13. Sa se afiseze numele si prenumele a 2 studenti, de dupa primii 3:


FROM studenti

LIMIT 3, 2;

14. Sa se afiseze aleator numele a 3 studenti:


FROM studenti

ORDER BY RAND() LIMIT 3;

15. Sa se afiseze pentru fiecare student, numele si prenumele concatenat precum si orasulsi tara concatenate, ordonate dupa nume si prenume:

SELECT CONCAT(nume,’ ’,prenume) AS Numele,

CONCAT(oras,’,’tara) AS Origine

FROM studenti


24

16. Sa se afiseze numele si prenumele studentilor care-si vor mai sarbatori ziua de nastereluna aceasta:


FROM studenti

WHERE MONTH(datan)=MONTH(CURDATE()) AND (DAY(datan)=

DAY(CURDATE());

17. Sa se afiseze pentru fiecare student numele, prenumele, data nasterii precum si varstalui, ordonate descrescator ın functie de varsta:

SELECT nume, prenume, datan, TIMESTAMPDIFF(YEAR, datan,

CURDATE()) AS Varsta

FROM studenti

ORDER BY Varsta DESC;

18. Sa se afiseze numele si prenumele studentilor al caror nume ıncepe cu litera ’C’.


FROM studenti

WHERE nume LIKE ’C%’;

Daca vrem sa consideram orice secventa de caractere atunci se foloseste caracterul ’%’,altfel, pentru o singura litera se foloseste caracterul ’ ’.

19. Sa se afiseze toate orasele distincte din care avem studenti:

SELECT DISTINCT oras

FROM studenti;

20. Sa se afiseze numarul de ınregistrari din tabela studenti:

SELECT COUNT(*)

FROM studenti;

21. Cati studenti din ’Brasov’ avem?

SELECT COUNT(*)

FROM studenti

WHERE oras=’Brasov’;

22. Pentru cati profesori se cunoaste data nasterii?

25

SELECT COUNT(datan)

FROM profesori;

SELECT COUNT(*)

FROM profesori

WHERE datan IS NOT NULL:

23. Sa se afiseze toate orasele distincte din care avem studenti:

SELECT (DISTINCT oras)

FROM studenti

ORDER BY oras;

24. Sa se afiseze cati barbati si cate femei avem studenti?

SELECT sex, COUNT(*)

FROM studenti

GROUP BY sex;

SELECT CASE sex WHEN ’f’ THEN ’feminin’

WHEN ’m’ THEN ’masculin’ END AS Sex,

COUNT(*) AS Numar

FROM studenti

GROUP BY Sex;

25. Sa se afiseze pentru fiecare luna cati studenti sunt nascuti ın acea luna:

SELECT MONTH(datan) AS Month, MONTHNAME(datan) AS Name,

COUNT(*) AS Apari¸tii

FROM studenti

GROUP BY Month

ORDER BY Month;

26. Sa se prezinte toate orasele care dau mai mult de un student, precum si numarul destudenti din orasele respective, orasele fiind ordonate descrescator ın functie de numarulde studenti:

SELECT oras, COUNT(*) as "numar studenti"

FROM studenti

GROUP BY oras

HAVING COUNT(*)>1

ORDER BY "numar studenti" DESC;

26

27. Sa se grupeze datele din tabela studenti ın functie de oras si luna ın care s-au nascutstudentii:

SELECT oras, MONTH(datan), COUNT(*)

FROM studenti

GROUP BY oras, MONTH(datan);

28. Sa se afiseze cati baieti si cate fete avem, precum si numarul total de studenti:

SELECT sex, COUNT(*) AS Numar

FROM studenti

GROUP BY sex

WITH ROLLUP;

29. Sa se afiseze orasele ın ordinea descrescatoare a mediilor varstelor persoanelor careprovin din acele orase:

SELECT oras, AVG(TIMESTAMPDIFF(YEAR,datan,CURDATE())) AS "Varsta medie"

FROM studenti

GROUP BY oras

ORDER BY "Varsta medie" DESC;

30. Sa se afiseze pentru fiecare student numele, prenumele si ınrolarile pe care le-a facut:

SELECT studenti.nrmatricol, nume, prenume, cod_inrolare

FROM studenti INNER JOIN inrolari

ON studenti.nrmatricol=inrolari.nrmatricol

ORDER BY student.nrmatricol;

SELECT s.nrmatricol, nume, prenume, cod_inrolare

FROM studenti s,inrolari i

WHERE s.nrmatricol=i.nrmatricol


31. Sa se afiseze toti studentii si ce ınrolari s-au facut pentru ei, inclusiv studentii care nuau facut nici o ınrolare:

SELECT student.nrmatricol, nume, prenume, cod_inrolare

FROM studenti LEFT JOIN inrolari

ON studenti.nrmatricol=inrolari.nrmatricol


27

SELECT student.nrmatricol, nume, prenume, cod_inrolare

FROM studenti LEFT JOIN inrolari

USING(nrmatricol)


32. Sa se afiseze pentru fiecare student, la cate cursuri s-a inscris.

SELECT nume, prenume, COUNT(*)

FROM studenti s LEFT JOIN inrolari i

USING (nrmatricol)

GROUP BY s.nrmatricol;

33. Sa se afiseze studentii care s-au ınrolat la vreun curs:

SELECT nrmatricol, nume, prenume

FROM studenti

WHERE nrmatricol IN

(SELECT DISTINCT nrmatricol

FROM inrolari)

SELECT studenti.nrmatricol, nume, prenume

FROM studenti INNER JOIN (

SELECT DISTINCT nrmatricol

FROM inrolari) t

ON studenti.nrmatricol=t.nrmatricol;

34. Sa se afiseze cea mai ın varsta persoana (persoane) din toti studentii:


FROM studenti

WHERE datan=

(SELECT MIN(datan)

FROM studenti)


FROM studenti

WHERE datan<= ALL(

SELECT datan

FROM studenti);

35. Sa se afiseze toti studentii care sunt din acelasi oras cu Oana Ciumarnean.

28


FROM studenti

WHERE oras =

(SELECT oras

FROM studenti

WHERE (nume, prenume)=(’Ciumarnean’,’Oana’));

36. Sa se afiseze studentii care au optat pentru anumite cursuri.


FROM studenti

WHERE EXISTS

(SELECT * FROM inrolari

WHERE inrolari.nrmatricol=studenti.nrmatricol);

37. Sa se afiseze pentru toate lunile ın care s-au nascut profesori sau studenti, cati profe-sori/studenti s-au nascut.

SELECT luna, COUNT(*)

FROM

(SELECT DISTINCT month(datan) AS luna FROM studenti

UNION ALL

SELECT DISTINCT month(datan) AS luna FROM profesori) AS t

GROUP BY luna

ORDER BY luna;

38. Sa se afiseze pentru fiecare judet, orasele pe care le avem pentru el, separate prinvirgula:

SELECT judet, GROUP_CONCAT(DISTINCT oras) AS Orase

FROM studenti

GROUP BY judet;

39. Sa se afiseze toate numele distincte de profesori si studenti:

SELECT prenume

FROM profesori

UNION

SELECT prenume

FROM studenti;

Diferenta ıntre UNION si UNION ALL este ca, ın cazul primei se elimina duplicateleın timp ce pentru a doua nu

29

40. Sa se afiseze pentru fiecare numar matricol, ultimul curs la care s-a ınscris.

SELECT i.nrmatricol, max_data, i.cod_curs

FROM inrolari i

JOIN

(SELECT nrmatricol, MAX(data_inrolare) AS max_data

FROM inrolari

GROUP BY nrmatricol) AS t

ON i.nrmatricol=t.nrmatricol AND i.data_inrolare=t.max_data

41. Sa se afiseze pentru fiecare student, ultimul curs la care s-a inscris.

SELECT nume, prenume, den_curs

FROM (studenti s join inrolari i using (nrmatricol)) join cursuri c using (cod_curs)

WHERE data_inrolare =

(SELECT MAX(data_inrolare)

FROM inrolari

WHERE inrolari.nrmatricol=s.nrmatricol);

sau

SELECT s.nume, s.prenume, max_data, c.den_curs

FROM studenti s JOIN

(

SELECT ii.nrmatricol, max_data, ii.cod_curs

FROM inrolari ii

JOIN

(SELECT nrmatricol, MAX(data_inrolare) AS max_data

FROM inrolari

GROUP BY nrmatricol) AS t

ON ii.nrmatricol=t.nrmatricol and ii.data_inrolare=max_data

) tt USING(nrmatricol)

JOIN cursuri c USING(cod_curs)

42. Sa se afiseze pentru fiecare curs, media notelor finale pe fiecare an.

43. Sa se afiseze mediile notelor acordate de profesorii barbati si profesorii femei.

2.3 Actualizarea datelor

Pentru actualizarea datelor vom folosi, ın principiu, una din urmatoarele interogari: IN-SERT, REPLACE, UPDATE, DELETE si TRUNCATE.

30

2.3.1 Interogarea INSERT

Pentru a introduce date ıntr-o tabela se foloseste instructiunea INSERT. Ea are maimulte forme:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES (expr | DEFAULT,...),(...),...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

sau

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name=expr | DEFAULT, ...


sau

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]


SELECT ...


Cele 3 forme realizeaza urmatoarele lucruri:

1. Inserarea uneia sau mai multor ınregistrari ıntr-o tabela, ın forma scurta.

2. Inserarea uneia sau mai multor ınregistrari ıntr-o tabela ın forma lunga.

3. Inserarea de ınregistrari pe baza datelor din alte tabele.

De exemplu pentru a insera un nou curs ın tabela cursuri putem proceda conformvariantei 1 ın mai multe feluri:

INSERT INTO cursuri

VALUES (NULL, "ADSOO",2,6,1);

sau

INSERT INTO cursuri(den_curs, tip_curs, credite, cod_profesor)

VALUES ("ADSOO",2,6,1);

Varianta a doua ar arata astfel:

INSERT INTO cursuri

SET den_curs="ADSOO",

tip_curs=2,

credite=6,

cod_profesor=1;

31

Se poate sa inseram si date din alt tabel ca ın exemplul urmator:

INSERT INTO studenti

SELECT FROM studenti_straini

In anumite cazuri dorim sa inseram date dintr-un fisier local. Datele ar trebui sa fie cu-prinse ıntr-un fisier care are cate o ınregistrare pe linie si care are ıntre valori caracterul TAB.Evident, campurile ar trebui sa fie prezente ın ordinea ın care sunt ın tabele. Instructiuneacare are ca efect citirea din fisier arata astfel:

LOAD DATA LOCAL INFILE ’cursuri.txt’ INTO TABLE cursuri;

2.3.2 Interogarea REPLACE

Interogarea REPLACE seamana foarte mult cu interogarea INSERT. Rolul ei este de ainsera ın tabela o noua ınregistrare si, daca deja exista o alta ınregistrare cu aceeasi cheieprimara sau cu aceeasi valoare pentru un camp unic, atunci ıntai sterge vechea ınregistraredupa care face inserarea.

Sintaxa acestei comenzi este aproape identica cu cea a lui INSERT:

REPLACE [LOW_PRIORITY | DELAYED]


VALUES (expr | DEFAULT,...),(...),...

sau


[INTO] tbl_name

SET col_name=expr | DEFAULT, ...

sau



SELECT ...

Pentru tabelele care nu au cheie primara sau campuri cu index unic, aceasta comandaeste identica cu INSERT.

Aceasta interogare este o extensie MySQL a limbajului SQL.In momentul ın care se doreste folosirea lui REPLACE, se parcurg urmatorii pasi:

1. se ıncearca inserarea obisnuita

2. daca inserarea obisnuita nu functioneaza:

• se sterge ınregistrarea veche care nu respecta conditia

• se insereaza noua ınregistrare

32

Un exemplu de folosire este urmatorul:

REPLACE INTO tipuri_curs

VALUES(2,"alta");

2.3.3 Interogarea UPDATE

Interogarea UPDATE are urmatoarea forma:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

sau

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1=expr1 [, col_name2=expr2 ...]


Dupa cum se poate vedea ın primul exemplu, sectiunea SET precizeaza care sunt coloanelea caror valoare se modifica, iar sectiunea WHERE pentru ce ınregistrari se aplica interogarea.

Chiar daca la prima vedere, sensul clauzei ORDER BY nu este asa de evident, ea sepoate folosi in situatia ın care se doreste modificarea valorilor unei coloane numerice, careeste si cheie primara. Daca s-ar produce modificarea ıntr-o ordine aleatoare ar putea apareaconflicte la modificarea valorilor (duplicate), pe cand daca se fac modificarile de la cea maimare valoare catre cea mai mica nu apare eroare.

Cea de-a doua varianta a lui UPDATE se foloseste ın cazul interogarilor pe mai multetabele.

Prezentam ın continuare cateva exemple:

• Sa se modifice toate notele mai mici decat 4, ın nota 4.

UPDATE note

SET nota=4

WHERE nota<4 AND nota>1;

• Sa se creasca toate numerele matricole din tabela studenti cu o unitate.

UPDATE studenti

SET nrmatricol=nrmatricol+1

ORDER BY nrmatricol DESC;

• Sa se creasca data ınrolarii cu o zi pentru acei studenti care sunt din Brasov.

33

UPDATE studenti, inrolari

SET data_inrolare=data_inrolare + INTERVAL 1 DAY

WHERE studenti.nrmatricol=inrolari.nrmatricol AND oras=’brasov’

sau

UPDATE inrolari

SET data_inrolare=data_inrolare + INTERVAL 1 DAY

WHERE nrmatricol IN

(

SELECT nrmatricol

FROM studenti

WHERE oras=’Brasov’

)

Dupa cum se poate observa, aceasta interogare modifica date din doar una din cele 2tabele. Totusi este posibil sa se modifice si colone din 2 tabele.

2.3.4 Interogarea DELETE

Interogarea DELETE are urmatoarea forma:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name


[ORDER BY ...]

[LIMIT row_count]

sau

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

tbl_name[.*] [, tbl_name[.*]] ...

FROM table_references


sau

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name[.*] [, tbl_name[.*]] ...

USING table_references


Prima forma a interogarii DELETE are ca efect stergerea acelor ınregistrari din tabelaspecificata, care respecta conditia din clauza WHERE. Daca clauza WHERE nu este speci-ficata, atunci se sterg toate ınregistrarile din tabela specificata.

Clauza LIMIT specifica numarul maxim de ınregistrari care se pot sterge. Clauza OR-DER BY specifica ordinea ın care se sterg ınregistrarile si are sens numai ın conjunctie cuLIMIT.

34

A doua forma a lui DELETE are ca efect stergerea de ınregistrari din unul sau mai multetabele, dar ın cooncordanta cu ınregistrari din alte tabele.

Acelasi lucru ıl face si formularea a treia, dupa cum vom putea observa ın exempleleurmatoare.

• Sa se stearga toate ınregistrarile din tabela note.

DELETE

FROM note;

• Sa se stearga informatiile despre totii studentii care sunt din Brasov.

DELETE

FROM studenti


• Sa se stearga toate ınrolarile care corespund unor studenti din Brasov.

DELETE inrolari

FROM inrolari, studenti

WHERE inrolari.nrmatricol=studenti.nrmatricol AND oras=’brasov’

DELETE

FROM inrolari

USING inrolari, studenti

WHERE inrolari.nrmatricol=studenti.nrmatricol AND oras=’brasov’

2.3.5 Interogarea TRUNCATE

Aceasta interogare are ca efect stergerea tuturor ınregistrarilor dintr-o tabela.Sintaxa ei este urmatoarea:

TRUNCATE [TABLE] tbl_name

Aceasta este o extensie Oracle care a fost adoptata si ın MySQL.Pentru anumite tipuri de stocare si anumite versiuni de MySQL TRUNCATE foloseste

de fapt DELETE. Pentru ultimele versiuni de MySQL si pentru anumite tipuri de stocare,TRUNCATE este mai rapida deoarece nu sterge ınregistrarile pe rand. Trebuie facute catevaobservatii:

• TRUNCATE sterge tabela si o recreeaza, acesta fiind motivul pentru care este mairapid

• Se reseteaza valorile contorului pentru auto increment

35

• Deoarece nu se invoca DELETE, nu se actioneaza triggerrul ON DELETE

• TRUNCATE functioneaza chiar daca datele sunt corupte sau daca fisierul de indexeste corupt.

Un exemplu, care realizeaza stergerea tuturor ınregistrarilor din tabela studenti este:

TRUNCATE TABLE studenti;

36

Capitolul 3

Crearea obiectelor bazei de date

3.1 Crearea unei baze de date

Pentru a crea o baza de date folosim instructiunea CREATE DATABASE. Baza de datepe care o folosim ın exemplele din acest curs se numeste “mydatabase“si a fost creata ınurma instructiunii urmatoare:

CREATE DATABASE mydatabase;

Instructiunea CREATE DATABASE are urmatoarea sintaxa:

CREATE DATABASE | SCHEMA [IF NOT EXISTS] db_name

[create_specification [create_specification] ...]

create_specification:

[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

Un set de caractere este o multime de simboluri si codari, ın timp ce termenul de “colla-tion“ se refera la seturi de reguli referitoare la compararea acestor caractere. Pentru detaliisuplimentare referitoare la utilizarea lor recomandam studierea documentatiei MySQL.

Daca vrem sa folosim baza de date creata anterior, trebuie ıntai sa o selectam folosindinstructiunea USE:

USE mydatabase;

Pentru a vedea ce tabele cuprinde baza de date introducem:

SHOW TABLES;

Inainte de a trece mai departe este important sa ıntelegem si cum este retinuta o baza dedate din punct de vedere fizic. O astfel de baza de date este retinuta sub forma unui directorcare contine mai multe fisiere. Acest director poarta numele bazei de date si este situat ın

37

directorul “data“ aflat ın directorul ın care avem instalata aplicatia MySQL. In functie deengine-ul folosit se poate ca modalitatile de stocare sa difere, motiv pentru care vom intraın detalii specifice cand vorbim de engine-uri.

Totusi, trebuie specificat ca pentru fiecare tabel se face un fisier cu extensia .FRM carecontine tabela si definitiile coloanelor. Indexul si datele pot fi stocate ın unul sau mai multefisiere auxiliare, depinzand de tipul engine-ului folosit. Fisierul .FRM este creat deasupraengine-urilor.

Odata creata baza de date, urmatorul pas pe care trebuie sa-l facem este sa cream tabele,dar pentru acest lucru trebuie sa stim ce tipuri de date ne pune la dispozitie MySQL. Acesteasunt prezentate ın sectiunea imediat urmatoare.

3.2 Tipuri de date

MySQL ofera mai multe categorii de tipuri de date care vor fi detaliate ın cele ce urmeaza:tipuri de date numerice, pentru siruri de caractere, pentru timp etc.

3.2.1 Tipuri numerice

In tabela 3.1 prezentam tipurile de date numerice care se pot folosi ın MySQL.

Tabela 3.1: Tipuri de date numerice

Tip Dimensiune SinonimeBIGINT 8 bytes

BITNUMERIC variaza DEC, DECIMAL

REAL 8 bytes DOUBLE, DOUBLE PRECISIONFLOAT 4 bytes

INT 4 bytes INTEGERMEDIUMINT 3 bytes

SMALLINT 2 bytesTINYINT 1 bytes

Pentru tipurile de date prezentate, se pot stabili mai multe caracteristici:

• AUTO INCREMENT - specifica daca acel camp trebuie sa fie generat automat. Sepoate folosi numai ın cazul tipurilor de date ıntregi.

• ZEROFILL - specifica daca la afisarea datelor din acel camp trebuie sa se foloseascazerouri la afisarea lui, daca are mai putine caractere decat numarul caracterelor caretrebuie afisate.

• UNSIGNED - poate retine numai numere pozitive, de doua ori mai mari decat numarul

38

maxim admis. Acest lucru este posibil deoarece bitul de semn se foloseste si el pentrua retine numarul.

• Pentru toate tipurile de date se poate specifica ın paranteza, pe cate spatii sa sereprezinte.

• Pentru tipurile de date care retin numere reale se poate specifica si numarul de cifrede dupa virgula.

Vom prezenta exemple de declarare a unor variabile ın sectiunea dedicata creeri tabelelor.Deoarece s-a constat ca este foarte folosit ın practica tipul BIGINT ca si cheie primara,

s-a definit si tipul de data SERIAL care este sinonim cu “BIGINT PRIMARY KEY NOTNULL AUTO INCREMENT“.

3.2.2 Tipuri pentru siruri de caractere

Pentru lucrul cu siruri de caractere sunt disponibile mai multe tipuri de date reprezentateın tabela 3.2.

Tabela 3.2: Tipuri de date pentru siruri de caractere

Tip Varianta Dimensiunebinara maxima

CHAR BINARY 255CHARACTER BINARY 255

NCHAR BINARY 255VARCHAR VARBINARY 65535

TINYTEXT TINYBLOB 255TEXT BLOB 65535

MEDIUMTEXT MEDIUMBLOB 16777215LONGTEXT LONGBLOB 4294967295

Diferenta ıntre tipurile CHAR si VARCHAR este ca pentru un tip CHAR se folosesteaceeasi zona de memorie pentru retinerea lui, indiferent de lungimea actuala a datei retinute,ın timp ce pentru VARCHAR se foloseste doar atata zona de memorie cat este nevoie. Acestlucru are un impact important asupra performantei bazei de date, ın sensul ın care, dacafolosim CHAR lungimea ınregistrarilor este constanta, ın timp ce cu VARCHAR lungimeaınregistrarilor este variabila si accesul direct la ınregistrari se face mai ıncet.

Observatie: Degeaba folosim mai multe coloane de tipul CHAR ıntr-o tabela daca avemmacar o coloana de tipul VARCHAR!

Diferenta ıntre tipurile normale si tipurile binare este ca la tipurile normale se lucreazacu caractere ın timp ce la tipurile binare lucram cu bytes.

39

3.2.3 Tipuri pentru lucrul cu date

Pentru lucrul cu date, avem disponibile tipurile de date reprezentate ın tabela 3.3. Putemusor observa ca avem tipuri care retin date calendaristice, timpul precum si date calendaris-tice combinate cu timpul.

Tabela 3.3: Tipuri de date pentru date si timp

Tip Dimensiune FormatDATE 3 bytes YYYY-MM-DD

DATETIME 8 bytes YYYY-MM-DD hh:mm:ssTIME 3 bytes hh:mm:ss

TIMESTAMP 4 bytes YYYY-MM-DD hh:mm:ssYEAR 1 byte YYYY

Un tip de date mai deosebit este TIMESTAMP. Daca ıntr-o coloana de tipul TIMES-TAMP se insereaza valoarea NULL, automat ın acea coloana se insereaza valoarea dateicurente.

Bineınteles ca pentru tipurile de date de tip data si timp exista numeroase functii carepot fi utilizate. Acestea vor fi prezentate ın capitolul dedicat functiilor MySQL.

3.2.4 Alegerea eficienta a tipurilor de date

In momentul ın care dorim sa alegem un tip de date, ar fi bine pentru optimizare, sarespectam urmatoarele reguli:

• Smaller is better - sa alegem tipuri potrivite, suficient de mari ca sa putem retinedatele dar cat mai mici posibil. Acest lucru eficientizeaza atat memoria pe disc cat simemoria din cache-ul CPU.

• Simple is good - numerele ıntregi se compara mai simplu decat sirurile de caractere (nuau character set sau collation).

• Avoid NULL if possible - pentru MySQL este mult mai greu sa lucreze cu campuri carepot avea valoarea NULL decat cu campuri care nu pot avea aceasta valoare. Acestlucru este cu atat mai evident cand coloana respectiva este indexata.

• Alegeti cu grija ıntre FLOAT, DOUBLE - DECIMAL - primele 2 au dezavantajul case fac calcule aproximative, dar mai repede; tipul DECIMAL face calcule exacte darmai ineficient.

• Alegeti cu grija ıntre CHAR si VARCHAR. Se prefera folosirea lui VARCHAR canduna sau mai multe conditii sunt adevarate:

– Dimensiunea maxima a coloanei este mult mai mare decat dimensiunea medie

40

– Actualizarile sunt destul de rare (daca ar fi dese ar conduce la fragmentari alememoriei)

– Cand avem character set complex, care retine fiecare litera pe un numar variabilde bytes.

Se prefera folosirea lui CHAR cand:

– Avem siruri de caractere mici

– Cand toate valorile au dimensiuni aproximativ egale - de exemplu cand se sto-cheaza parole MD5

– Cand facem foarte des actualizari ale datelor

• In anumite situatii tipul ENUM este mai eficient decat CHAR sau VARCHAR:

create table persoane

(

cod_persoana SERIAL,

nume VARCHAR(30) NOT NULL,

sex ENUM(’masculin’,’feminin’) NOT NULL

)

insert into persoane

values (null,’Ion’,’masculin’);

select sex+0

from persoane;

-- va afisa numere de 0 si 1

select *

from persoane

order by sex;

-- pune inainte persoanele de sex masculin

select * from persoane

order by field (sex,’feminin’,’masculin’);

Printre dezavantajele acestei abordari ar fi faptul ca numarul de valori permise e fix,precum si faptul ca la JOIN-uri trebuie facute foarte multe cautari pentru a facepotrivirile. Printre avantaje este faptul ca se foloseste mai putina memorie, inclusivpentru camp cat si pentru cheile care sunt retinute.

• Pentru retinerea unei adrese IP se poate folosi nu un VARCHAR(15) ci un INT pe 32biti, cu ajutorul functiilor INET ATON() si INET NTOA().

41

3.3 Crearea tabelelor

3.3.1 Crearea unor tabele simple

Iata ca a venit si momentul pentru a crea tabele. Acest lucru se realizeaza foarte simplu,cu ajutorul instructiunii CREATE TABLE, care primeste numele tabelului, ın parantezerotunde numele fiecarei coloane si caracteristicile ei, precum si tipul tabelei respective.

Sintaxa instructiunii CREATE TABLE este una din cele mai complicate, ea aratand ındocumentatia MySQL astfel:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_option ...]

sau:


[(create_definition,...)]

[table_option ...]

select_statement

sau:


LIKE old_tbl_name | (LIKE old_tbl_name)

create_definition:

column_definition

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

| INDEX|KEY [index_name] [index_type] (index_col_name,...)

| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

[index_name] [index_type] (index_col_name,...)

| FULLTEXT|SPATIAL [INDEX|KEY] [index_name] (index_col_name,...)

| [CONSTRAINT [symbol]] FOREIGN KEY

[index_name] (index_col_name,...) [reference_definition]

| CHECK (expr)

column_definition:

col_name data_type [NOT NULL | NULL] [DEFAULT default_value]

[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

[COMMENT ’string’] [reference_definition]

data_type:

BIT[(length)]

| TINYINT[(length)] [UNSIGNED] [ZEROFILL]

| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

42

| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

| INT[(length)] [UNSIGNED] [ZEROFILL]

| INTEGER[(length)] [UNSIGNED] [ZEROFILL]

| BIGINT[(length)] [UNSIGNED] [ZEROFILL]

| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]

| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]

| DATE

| TIME

| TIMESTAMP

| DATETIME

| YEAR

| CHAR(length)

[CHARACTER SET charset_name] [COLLATE collation_name]

| VARCHAR(length)


| BINARY(length)

| VARBINARY(length)

| TINYBLOB

| BLOB

| MEDIUMBLOB

| LONGBLOB

| TINYTEXT [BINARY]


| TEXT [BINARY]


| MEDIUMTEXT [BINARY]


| LONGTEXT [BINARY]


| ENUM(value1,value2,value3,...)


| SET(value1,value2,value3,...)


| spatial_type

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

43

USING BTREE | HASH

reference_definition:

REFERENCES tbl_name [(index_col_name,...)]

[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

[ON DELETE reference_option]

[ON UPDATE reference_option]

reference_option:

RESTRICT | CASCADE | SET NULL | NO ACTION

table_option:

ENGINE|TYPE [=] engine_name

| AUTO_INCREMENT [=] value

| AVG_ROW_LENGTH [=] value

| [DEFAULT] CHARACTER SET charset_name

| CHECKSUM [=] 0 | 1

| COLLATE collation_name

| COMMENT [=] ’string’

| CONNECTION [=] ’connect_string’

| DATA DIRECTORY [=] ’absolute path to directory’

| DELAY_KEY_WRITE [=] 0 | 1

| INDEX DIRECTORY [=] ’absolute path to directory’

| INSERT_METHOD [=] NO | FIRST | LAST

| MAX_ROWS [=] value

| MIN_ROWS [=] value

| PACK_KEYS [=] 0 | 1 | DEFAULT

| PASSWORD [=] ’string’

| ROW_FORMAT [=] DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT

| UNION [=] (tbl_name[,tbl_name]...)

select_statement:

[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)

O astfel de tabela (un exemplu mai simplu) se creeaza astfel:


nrmatricol bigint primary key not null,



CNP varchar(13) not null,


44


oras varchar(30) not null,

judet varchar(30) not null,

tara varchar(30) not null

)

Engine=InnoDB;

Dupa cum se poate vedea se specifica ın primul rand numele tabelei, dupa care pentrufiecare camp ın parte se specifica numele, tipul lui, daca poate sau nu sa ia valoarea NULL,daca este cheie primara, ın final stabilindu-se tipul de tabel.

Pentru fiecare coloana se mai pot preciza anumite informatii precum un mic comentariusi valoarea implicita (default).

Iata un exemplu:

CREATE TABLE pers

(

cod_pers BIGINT PRIMARY KEY AUTO_INCREMENT

COMMENT ’codul persoanei’,

nume VARCHAR(30) NOT NULL

COMMENT ’numele persoanei’,

data_inrolare DATE NOT NULL

DEFAULT NOW()

COMMENT ’data la care s-a inrolat persoana’

)

Valoarea implicita mai poate fi folosita si ın cadrul unor interogari UPDATE:

UPDATE studenti

SET data_inrolarii=DEFAULT

Se mai pot preciza si alte informatii la crearea unui tabel, referitoare la indecsi si cheistraine, dar acestea sunt prezentate ın sectiunile urmatoare.

3.3.2 Copierea tabelelor

Se poate crea o tabela care sa aiba exact aceeasi structura ca si o tabela existenta, dar laınceput sa fie goala. Acest lucru se poate face cu instructiunea CREATE TABLE ... LIKE.Iata si un mic exemplu care creaza o astfel de tabela:

CREATE TABLE studenti2 LIKE studenti;

Putem verifica faptul ca totul a functionat bine prin instructiunea urmatoare care neprezinta structura tabelei “autori2“:

DESCRIBE studenti2;

45

Trebuie remarcat, ca ın aceasta situatie, nu s-au copiat si informatiile despre indecsi siconstrangeri de integritate.

Daca vrem sa copiem atat structura cat si anumite date, putem proceda astfel:

CREATE TABLE studenti2 AS

(

SELECT *

FROM studenti

)

Se poate sa si luam structura unei tabele si sa modificam anumite lucruri:

CREATE TABLE studenti3

(

nume VARCHAR(30) NULL,

prenume VARCHAR(30) NULL

) AS

(SELECT * FROM studenti)

3.3.3 Crearea tabelelor temporare

Se poate sa creem tabele care sa nu reziste decat pe perioada unei sesiuni. Acestea poartanumele de tabele temporare. Ele pot fi, de asemenea, sterse si de utilizator.

Un astfel de exemplu, de creare a unei tabele temporare este urmatorul:

CREATE TEMPORARY TABLE studenti2

( studentid BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,

nume VARCHAR(20) NOT NULL

)

Trebuie facute cateva observatii importante:

• O astfel de tabela se poate folosi la fel ca si orice alta tabela.

• Numai utilizatorul care a creat-o o poate folosi.

• Putem avea o tabela temporara si una permanenta cu acelasi nume. Totusi, ın astfelde situatii, tabela temporara o ascunde pe cea permanenta.

• Ca si in cazul tabelelor normale, se poate folosi IF NOT EXISTS, pentru ca daca existadeja o tabela temporara cu acel nume sa nu ne dea eroare:

CREATE TEMPORARY TABLE test IF NOT EXISTS

(...)

46

3.3.4 Optiuni ale tabelelor

In momentul ın care creem tabele, putem specifica si alte informatii care nu au fostprezentate pana acum, precum: engine-ul folosit, cum se lucreaza cu valori auto increment,stabilirea de comentarii la nivel de tabele, lucruri legate de numarul de ınregistrari din tabeleetc.

Stabilirea ENGINE-ului folosit pentru un tabel

ENGINE-ul corespunzator unui tabel specifica modul ın care datele din tabel sunt stocate,cum pot fi accesate si cum sunt rezolvate tranzactiile.

Exista engine-uri care sunt mai bune la realizarea select-urilor, unele mai bune la update-uri sau altele care se potrivesc mai bine pentru tabele temporare.

Pentru a vedea o lista cu toate engine-urile disponibile putem executa:

SHOW ENGINES;

Doua dintre cele mai importante engine-uri sunt MyISAM si InnoDB (daca nu specificamaltceva, cel implicit este MyISAM).

Sintaxa pentru specificare este urmatoarea:

CREATE TABLE pers

(

persid BIGINT PRIMARY KEY NOT NULL,

nume VARCHAR(30) NOT NULL)

ENGINE=InnoDB

Pentru tabelele temporare este recomandabil ca engine-ul folosit sa fie MEMORY, careautomat distruge tabela ın momentul ın care serverul de baze de date este oprit.

Se poate afisa engine-ul implicit folosind urmatoarea comanda:

SELECT @@STORAGE_ENGINE;

Principalele engine-uri din MySQL sunt:

• MyISAM - pentru tabele care nu suporta tranzactii. Avem o viteza foarte mare lastocarea si obtinerea datelor si suporta full text searching capabilities

• MEMORY - tabele care sunt pastrate ın memoria RAM. Nu suporta tranzactii.

• MERGE - permit ca o colectie de mai multe tabele identice sa fie tratate ca unulsingur. Nici acestea nu suporta tranzactii.

• InnoDB - suporta tranzatii si chei straine (FOREIGN KEY)

• BDB - suporta tranzactii

47

• Example - nu se foloseste ın practica, este doar un exemplu simplu de cum s-ar imple-menta un engine nou pentru cei care ar fi interesati sa-si scrie propriul engine

• NDBCLUSTER (NDB) - pentru tabele care sunt distribuite pe mai multe calculatoare

• ARCHIVE - pentru tabele care contin cantitati foarte mari de date si care nu suportaindecsi.

• CSV - retine datele ın fisiere text, separate prin virgula (CSV - Comma SeparatedValues).

Engine-urile se ımpart ın doua mari categorii, cele care suporta tranzactii si cele care nusuporta.

Avantajele celor care nu suporta sunt urmatoarele:

• sunt mai rapide

• au nevoie de mai putin spatiu pe disk

• mai putina memorie pentru a realiza actualizari

Printre avantajele celor care suporta tranzactii:

• sunt mai sigure (ın caz de probleme hardware integritatea datelor poate fi pastrata maiusor)

• se poate sa combinam mai multe instructiuni si sa le acceptam pe toate odata cuCOMMIT

• se poate da ROLLBACK daca vrem sa refacem niste operatii

• putem stabili singuri nivelul de concurenta

Principalele caracteristici ale engine-ului MyISAM sunt urmatoarele:

• Fiecare tabela se retine ın 3 fisiere: .frm (formatul tabelei), .MYD (MyData - dateledin tabela) si .MYI (MyIndex - indecsii tabelei)

• Numarul maxim de ınregistrari dintr-o tabela este 232 (cu configuratii speciale 264)

• O tabela poate avea maxim 64 de indecsi ( cu configuratii speciale 128)

• Lungimea maxima a cheii este de 1000 bytes

• Cu optiunile DATA DIRECTORY si INDEX DIRECTORY se pot pune datele si in-decsii ın alte directoare decat cel ın care se retine fisierul .frm (pentru performante maibune)

Unele din principalele caracteristici ale engine-ului InnoDB sunt:

48

• Concurenta si performanta ımbunatatite ın mediile multiutilizator

• Limita maxima a unei baze de date este de 64 TB

• Retine tabelele si indecsii ıntr-un tabelspace, care se poate ımparti ın mai multe fisiere

• Pentru retinerea fisierelor foloseste formatul .frm plus un InnoDB data dictionary.Acesta este motivul pentru care nu se pot muta tabelele si bazele de date de pe unserver pe altul numai prin simpla mutare a directoarelor.

• O tabela poate avea maxim 1000 coloane

• Actiunile ın cascada legate de chei straine nu activeaza triggere

• A nu se converti tabelele sistem ale MySQL de la MyISAM la InnoDB, deoarece aceastaoperatie nu este suportata.

Stabilirea strategiei pentru auto increment

Dupa cum am putut vedea, daca avem coloane ın care avem numere care vrem sa segenereze automat, putem folosi optiunea AUTO INCREMENT. Automat, se genereaza toatenumerele ıncepand cu 1 si cu pasul 1.

Putem specifica o valoare de la care sa ınceapa AUTO INCREMENT-ul, ca ın exemplul:

CREATE TABLE studenti

(...)

AUTO_INCREMENT=100;

Comentarii pentru tabel

La fel cum putem sa stabilim niste comentarii pentru fiecare coloana, asemanator se potstabili comentarii si la nivel de tabel.

Acestea se pot stabili ca ın exemplul urmator:


(...)

COMMENT ’Retine informatii despre studenti’

Optiunile AVG ROW LENGTH, MAX ROWS si MIN ROWS

Pentru ca engine-ul sa functioneze mai bine ıi putem preciza care este lungimea medie aunei ınregistrari (ın bytes), numarul maxim de ınregistrari pe care banuim ca-l vom avea sinumarul minim.

Un exemplu este prezentat ın continuare:

49


(...)

AVG_ROW_LENGTH=20

MAX_ROWS=2000000

MIN_ROWS=1000000

3.3.5 Tabelele si catalogul MySQL

In versiunile mai noi de MySQL exista o baza de date care se numeste information schema

si care contine numeroase informatii precum cele despre tabele, coloanele lor etc.Tot aici se salveaza toate comentariile, informatiile despre engine-uri etc.Se recomanda studierea acestei baze de date pentru a ıntelege mai bine cum retine MySQL

informatiile referitoare la structura bazelor de date.

3.3.6 Specificarea constrangerilor de integritate

In momentul ın care lucram cu o baza de date, trebuie ca ın orice moment baza de datesa se afle ıntr-o stare valida.

Constrangerile de integritate sunt regulile pe care continutul bazei de date trebuie sa lerespecte tot timpul. Ele descriu ce actualizari asupra bazei de date sunt permise.

Astfel de constrangeri sunt NOT NULL ( a fost deja prezentata), PRIMARY KEY,FOREIGN KEY, UNIQUE.

Stabilirea unei chei primare (PRIMARY KEY)

O cheie primara este o coloana sau un grup de coloane care identifica ın mod unic oriceınregistrare. Dupa cum se stie deja, cheile primare nu pot avea valoarea NULL.

Cheia primara poate fi specificata astfel:

CREATE TABLE studenti(

cod_student BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,

....

)

sau

CREATE TABLE carti_autori(

cod_carte BIGINT NOT NULL,

cod_autor BIGINT NOT NULL,

....,

PRIMARY KEY(cod_carte, cod_autor)

)

Cea de-a doua varianta se foloseste atunci cand avem chei primare compuse din mai multecampuri.

50

Daca avem un camp care este cheie primara, automat se subıntelege ca el nu poate aveavaloarea NULL. Totusi este un lucru bun sa precizam acest lucru.

Cateva observatii:

• O tabela nu poate avea mai mult de o cheie primara.

• Teoria relationala spune ca ar trebui sa avem obligatoriu o cheie primara, dar MySQLnu ne obliga la acest lucru.

• Cheia primara trebuie sa fie alcatuita din cel mai mic set de coloane care ne asiguraunicitatea.

Automat, pentru fiecare cheie primara se creeaza un index. Daca vrem sa dam un numeacestei constrangeri, putem face acest lucru:

CREATE TABLE studenti (

...

PRIMARY KEY primary_key_studenti (cod_student)

)

Stabilirea cheilor alternative (UNIQUE)

Se poate ın MySQL sa stabilim si chei alternative folosind UNIQUE. Aceste chei alter-native sunt chei candidat care nu au fost alese chei primare.

O diferenta fata de cheile primare este faptul ca ele pot admite si valoarea NULL, darnu admit alte duplicate.

Stabilirea cheilor straine (FOREIGN KEYS)

In primul rand trebuie prentata notiunea de constrangere de integritate referentiala.O constrangere de integritate referentiala se refera la faptul ca valorile din anumite co-

loane trebuie neaparat sa-si gaseasca corespondentul ın niste valori din alta tabela.In situatia ın care engine-ul folosit este InnoDB, avem dreptul sa lucram cu chei straine

(foreign keys).De exemplu, am putea crea tabela inrolari astfel:

CREATE TABLE inrolari(

cod_inrolare BIGINT PRIMARY KEY AUTO_INCREMENT,

nrmatricol BIGINT NOT NULL,

cod_curs BIGINT NOT NULL,

data_inrolare DATE NOT NULL,

FOREIGN KEY(nrmatricol)

REFERENCES studenti(nrmatricol)

ON UPDATE CASCADE

ON DELETE CASCADE,

51

FOREIGN KEY (cod_curs)

REFERENCES cursuri(cod_curs)

)

ENGINE=InnoDB;

Cheile straine se pot referi numai la chei primare sau la coloane UNIQUE. Pot fi si grupuride coloane daca valoarea lor este garantata sa fie unica.

In mod implicit, nu se poate sterge, ın acest caz, nici o ınregistrare din tabela studenti

atata timp cat ın tabela inrolari exista ınregistrari care se refera la acea ınregistrare.Operatiile care pot produce probleme sunt UPDATE si DELETE, de aceea avem doua

situatii ın care trebuie sa specificam ce se ıntampla: ON UPDATE si ON DELETE.Avem 5 actiuni posibile:

• CASCADE (face ca actualizarea/ stergerea sa se faca ın cascada)

• RESTRICT (nu se poate actualiza/ sterge daca exista ınregistrari legate de aceasta)

• SET NULL (se ınlocuiesc toate aparitiile acelei valori cu NULL chiar daca acea coloananu ar accepta acest lucru)

• NO ACTION (este identic cu RESTRICT)

• SET DEFAULT (se ıncearca setarea valorii respective la valoarea ei DEFAULT)

Constrangerile de integritate se mai pot verifica si folosind CHECK, ca ın exempleleurmatoare:

CREATE TABLE studenti(

...

sex CHAR(1) NOT NULL CHECK(sex IN(’m’,’f’)),

datan DATE NOT NULL CHECK (datan>’1980-10-12’)

...,

CONSTRAINT verificare_tara

CHECK (tara IN (’Romania’,’Germania’))

)

Dupa cum am vazut ın ultimul exemplu se poate ca sa dam nume constrangerilor, lucrucare se va dovedi util un pic mai tarziu.

3.4 Stergerea tabelelor - DROP TABLE

Pentru a sterge tabele ıntregi (si date si structura si indecsi) putem folosi instructiuneaDROP TABLE(S) care are urmatoarea sintaxa:

52

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

O tabela poate fi stearsa numai daca nu are chei straine care pointeaza la ea.Altfel, pentru a putea sterge o astfel de tabela ıntai stergem constrangerile fie folosim

optiunea CASCADE. Aceasta din urma ar putea avea ca efect chiar si stergerea tuturortabelelor din baza de date, ın anumite circumstante.

3.5 Redenumirea tabelelor - RENAME TABLE

Se poate sa redenumim un tabel folosind comanda RENAME TABLE, care are urmatoareasintaxa:

RENAME TABLE tbl_name TO new_tbl_name

[, tbl_name2 TO new_tbl_name2] ...

Un exemplu ar arata astfel:

RENAME TABEL studenti TO students;

3.6 Schimbarea structurii tabelului - ALTER TABLE

Una din cele mai complexe comenzi SQL este ALTER TABLE, care are urmatoareasintaxa:

ALTER [IGNORE] TABLE tbl_name

alter_specification [, alter_specification] ...

alter_specification:

table_option ...

| ADD [COLUMN] column_definition [FIRST | AFTER col_name ]

| ADD [COLUMN] (column_definition,...)

| ADD INDEX|KEY [index_name] [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_name,...)


UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)


FOREIGN KEY [index_name] (index_col_name,...)

[reference_definition]

| ALTER [COLUMN] col_name SET DEFAULT literal | DROP DEFAULT

53

| CHANGE [COLUMN] old_col_name column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP INDEX|KEY index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name [, col_name] ...

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

index_col_name:


index_type:

USING BTREE | HASH

Prezentam cateva exemple de interogari care folosesc aceasta instructiune:

• Sa se redenumeasca tabela studenti ın students

ALTER TABLE studenti RENAME TO students

• Sa se modifice proprietatea auto increment si comentariul pentru tabela studenti

ALTER TABLE studenti

AUTO_INCREMENT=1000

COMMENT=’tabela studenti’

• Sa se schimbe engine-ul tabelei studenti ın InnoDB.


ENGINE=InnoDB

3.6.1 Schimbarea coloanelor

Prezentam exemple de interogari:

• Sa se insereze o noua coloana grupa, sir de caractere de 4 caractere, dupa campul sex.

54


ADD COLUMN grupa CHAR(4) AFTER sex

Automat toate valorile din noua coloana vor fi initializate cu NULL. Daca acea co-loana nu ar fi permis valoarea NULL, s-ar fi inserat valoarea implicita pentru campulrespectiv.

• Sa se stearga coloana grupa din tabela studenti.


DROP grupa

Se vor sterge toate obiectele care depind de aceasta coloana.

• Sa se schimbe numele coloanei sex ın gen.


CHANGE sex gen

• Sa se schimbe campul nume astfel ıncat sa retina un sir de caractere de 50 de caractere.


CHANGE nume nume VARCHAR(50) NOT NULL

• Sa se puna campul oras pe pozitia a doua.


CHANGE oras oras VARCHAR(30) AFTER nrmatricol


MODIFY oras VARCHAR(30) AFTER nrmatricol

• Sa se stearga valoarea implicita a campului datan.


ALTER datan DROP DEFAULT

3.6.2 Schimbarea constrangerilor de integritate

Pentru a ilustra cum se poate face acest lucru vom prezenta cateva exemple:

• Sa se faca legatura ıntre tabelele studenti si inrolari.

55

ALTER TABLE inrolari

ADD CONSTRAINT fk_studenti FOREIGN KEY nrmatricol

REFERENCES studenti(nrmatricol)

• Sa se elimine cheia primara a tabelei studenti.


DROP PRIMARY KEY

• Sa se stearga prima constrangere care a fost creata la ınceputul acestei sectiuni.

ALTER TABLE inrolari

DROP CONSTRAINT fk_studenti

3.7 Lucrul cu indecsi

In momentul ın care lucram cu baze de date, anumite operatii se pot face ın mai multemoduri. Dintre aceste moduri, unele sunt mai eficiente, altele mai putin eficiente.

Sunt unele operatii, precum CREATE TABLE sau GRANT care nu pot fi optimizate,timpul lor de executie fiind unul constant.

Alte operatii, precum SELECT, INSERT, UPDATE si DELETE pot fi foarte multinfluentate de modul ın care sunt scrise sau de modul ın care sunt retinute datele.

Un astfel de mijloc, prin care se pot optimiza anumite operatii este folosirea indecsilor.

3.7.1 Cum se retin datele?

Inregistrarile dintr-o baza de date sunt stocate ın fisiere. In functie de tipul de enginefolosit sau chiar de optiunea utilizatorului putem avea mai multe variante:

• Se poate ca pentru fiecare tabela ın parte sa avem cate un fisier (cazul MyISAM).

• Pentru toata baza de date sa avem un singur fisier (InnoDB - cazul implicit).

La randul lor, datele dintr-un tabel sunt organizate ın pagini de date, a caror dimensiunevariaza ın functie de engine-ul folosit si de sistemul de operare (dimensiuni uzuale sunt celede 2K, 4K, 8K, 32K etc.). Numarul de ınregistrari care se retin ıntr-o pagina de memorieeste dictat atat de dimensiunea paginii cat si de dimensiunea unei ınregistrari.

Atunci cand se adauga noi ınregistrari se adauga la sfarsitul ultimei pagini, sau dacaaceasta s-a umplut, se adauga ıntr-o pagina noua.

Motivul pentru care noile ınregistrari nu se pun ın zonele care deja au fost eliberate prinstergeri este ca, mai ales ın cazul tabelelor cu foarte multe ınregistrari, cautarea unei zonelibere poate dura foarte mult.

Un lucru foarte important de ınteles este ca datele se aduc de pe hard-disk pe pagini side abia dupa aceea ele se prelucreaza ın memoria interna (RAM).

56

3.7.2 Ce este un index si cum functioneaza?

Indecsii sunt folositi pentru a regasi anumite valori mult mai repede. Fara indecsi artrebui parcurse toate ınregistrarile pe rand, ceea ce ınseamna ca, cu cat este mai maretabela cu atat costurile sunt mai mari.

Atentie! Trebuie sa fim foarte atenti atunci cand folosim diverse tools-uri pentru agenera continutul unei baze de date sau cand folosim ORM-uri deoarece acestea foarte rarcreeaza indecsi cu toate ca rolul acestora este foarte important.

Atentie! Indexul contine valori dintr-o coloana specificata sau mai multe coloane. Or-dinea lor este foarte importanta deoarece cautarile se fac rapid numai dupa prefixe ale in-decsilor.

Atentie! Creearea unui index pe doua coloane nu este acelasi lucru cu a crea 2 indecsifiecare pe cate o coloana.

In principiu, pentru accesarea ınregistrarilor dintr-un tabel exista mai multe metode,dintre care, cele mai folosite sunt doua:

• metoda de accesare secventiala a datelor (cand se parcurg toate ınregistrarile tabeleipe rand, pentru a se efectua o anumita operatie - este o metoda care poate conduce lao parcurgere costisitoare ın situatii ın care s-ar putea adopta solutii mai bune. Este casi cum ın cartea de telefon am cauta un numar prin parcurgerea tuturor numelor dincarte.)

• metoda de accesare indexata - se foloseste un mecanism asemanator ıntr-o oarecaremasura celui folosit ın cartea de telefon.

In MySQL un index e construit ca un arbore care are mai multe noduri. Este posibil savrem sa facem parcurgeri de mai multe tipuri:

• gasirea unei ınregistrari cu o anumita valoare

• parcurgerea unei tabele pe baza unei coloane ordonate

• cand vrem sa obtinem mai multe valori

Uneori este util sa avem ınregistrarile ordonate fizic ın functie de o anumita cheie, lucrucare face parcurgerea mult mai eficienta.

Trebuie facute mai multe observatii:

• Fisierul index este mereu ın cooncordanta cu continutul tabelei, ceea ce ınseamna cala operatiile de UPDATE, DELETE si INSERT el trebuie reactualizat.

• Evident, putem avea si indecsi neunici.

• Pe o tabela putem defini mai multi indecsi iar un index poate fi si compus din maimulte campuri.

• Nodurile unui index sunt si ele stocate fizic, ceea ce ınseamna ca si ele ocupa spatiu pedisc.

57

• Actualizarile tabelelor duc la actualizari ale indecsilor. Acestea presupun, ın principiuumplerea locurilor libere din arbore, iar daca acest lucru nu se poate, se poate ajungesi la crearea de noi noduri si eventual reorganizarea mai complexa a arborelui. Acesteoperatii pot consuma destul de mult timp.

Exista mai multe tipuri de indecsi:

• Indecsi care folosesc B-arbori

• Indecsi care folosesc tabele de dispersie (hash-tables)

Trebuie spus ca fiecare engine are anumite tipuri de indecsi pe care le suporta. Se poatechiar ca acelasi tip de index sa aiba implementari un pic diferite pentru engine-uri diferite.

In functie de situatie se poate ca un anumit tip de index sa fie mai potrivit decat altul.

Indecsi care folosesc B-arbori

Indecsii care folosesc B-arbori - sunt cei mai folositi fiind recunoscuti de aproape toateengine-urile, cu exceptia ARCHIVE (ıl recunoaste doar pentru campurile de tip AUTO INCREMENT).Unele engine-uri lucreaza cu variante foarte asemanatoare dar un pic diferite (NDB folosestede fapt un T-Tree, chiar daca noi ıi zicem sa lucreze cu BTREE).

Fiecare engine poate retine indecsii diferit: MyISAM ıi retine ıntr-un format compresatpe cand InnoDB nu.

Ideea de baza unui B-Tree este ca toate valorile sunt stocate ın ordine, fiecare paginafrunza fiind la aceeasi distanta de nodul radacina (arbore balansat).

Modul ın care se retine un astfel de index este reprezentat ın figura 3.1.

Figura 3.1: Reprezentarea unui index cu B-Tree

58

Aceste tip de indecsi sunt eficienti la sortari si la cautarea unor intervale de date.Un exemplu de cum ar putea arata un index este cel din figura 3.2.

Figura 3.2: Exemplu de valori indexate

Tipuri de interogari care folosesc astfel de indecsi sunt:

• cautari dupa toate coloanele indexului

• cautari dupa doar un prefix al indexului (primele coloane din index, ın ordinea dinindex)

• un criteriu legat de prima coloana, care sa fie compatibil cu indexul

• cautare dupa prima coloana cu valori cuprinse ıntr-un interval

• cautare dupa o valoare fixa din prima coloana si ceva compatibil cu indecsii din a doua.

• se poate sa avem si index-only queries care nu acceseaza mediul de stocare a datelor.

• ne ajuta si la ORDER BY daca avem coloane care sa respecte restrictiile prezentateanterior

• daca criteriile de cautare contin 3 coloane, de ex., iar ın index sunt doar doua le vafolosi pe primele 2.

Atentie! E posibil sa avem mai multi indecsi cu aceleasi coloane dar ın ordine diferita.

59

Indecsi bazati pe tabele de dispersie

Sunt construiti cu ajutorul tabelelor de dispersie si sunt folositi pentru cautari exacte.

Pentru fiecare ınregistrare se calculeaza un hash-code al coloanelor indexate, care va fi ovaloare ıntreaga care probabil va fi diferita pentru ınregistrari cu valori diferite.

Un engine care suporta astfel de indecsi este MEMORY (suporta chiar indecsi neunici).Daca mai multe valori au aceeasi valoare a funtiei de dispersie vor fi stocate ıntr-o lista.

Sunt mai rapizi decat B-Indecsii dar au limitari:

• Trebuie neaparat citite ınregistrari din memorie.

• Nu se pot folosi pentru sortari.

• Nu se pot folosi pentru chei partiale (nici macar pentru prefixe).

• Suporta numai operatii de egalitate precum ”=” sau IN.

• Accesul e foarte rapid daca nu avem foarte multe coliziuni !!!

• Unele operatii pot fi foarte costisitoare daca avem multe coliziuni (ex: stergerea uneiınregistrari).

Engine-ul NDB Cluster suporta hash-tables dar numai pentru valori unice.

Engine-ul InnoDB foloseste adaptive hash indexes. Cand anumite valori sunt folositefoarte des, ısi construieste un hash-index deasupra indecsilor B-Tree, ceea ce permite cautarifoarte rapide. Acest proces este automat si nu poate fi controlat sau configurat.

Un exemplu mai deosebit

Sa presupunem ca avem o coloana ıntr-un tabel care contine adrese web si foarte desvrem sa facem cautari dupa adrese web, de genul:

SELECT *

FROM adrese

WHERE url="http://www.mysql.com"

Am dori sa indexam coloana url, dar problema care apare este ca dimensiunea coloaneieste mare, ceea ce va ıngreuna atat mentinerea indexului cat si toate operatiile care se vorefectua asupra tabelei.

Putem creea o coloana speciala care sa retina codul CRC al coloanei url, urmand catoate cautarile sa le facem cu interogari de genul:

SELECT *

FROM adrese

WHERE url_crc=CRC32("http://www.mysql.com") AND

url="http://www.mysql.com";

60

Alternativa ar fi fost sa indexam tot sirul de caractere. Evident, pot aparea coliziuni, daraceasta nu e neaparat o problema daca nu avem index unic.

Mai mult, putem face un trigger, care ın momentul ın care inseram o noua ınregistrare,sa poata actualiza valoarea campului url crc.

BEFORE INSERT ON adrese FOR EACH NEW ROW BEGIN

SET NEW.url_crc=CRC32(NEW.url)

END

--la fel se poate face si la BEFORE UPDATE

In situatii de genul acesta trebuie sa analiza probabilitatea de a aparea coliziuni (la 93000valori, probabilitatea e de 1%).

Acest lucru este ilustrarea celebrei probleme Birthday Paradox : probabilitatea ca din 23de persoane, doua sa fie nascute ın aceeasi zi este de 50% iar din 75 de persoane probabilitateaeste de 99.9%.

Strategii de indexare pentru performante bune

Trebuie sa fim atenti la lucrul cu indecsi deoarece pot aparea probleme neasteptate:

• Izolarea coloanei care contine indexul

-- nu se foloseste indexul

SELECT x FROM y WHERE x+1<5

... TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10

-- se foloseste indexul

... date_col>=DATE_SUB(CURRENT_DAY,INTERVAL 10 DAY)

• Prefixarea indecsilor si selectivitatea lor - In anumite situatii nu vrem sa indexam unıntreg camp, ci doar un prefix al lui, deoarece campul este foarte mare. Trebuie sa fimatenti sa avem o selectivitate suficienta.

Un dezavantaj ar fi ca nu se mai pot folosi la ORDER BY sau GROUP BY.

In anumite situatii poate avea sens sa definim indecsi pe sufixe. Un exemplu ar fi dacaretinem ıntr-o coloana adrese de mail si vrem sa facem cautari dupa domeniul adresei.

• Clustered indexes - fac ca pentru un astfel de index ınregistrarile sa fie sortate fizic pedisc. Putem avea maxim un astfel de index pe tabel. In MySQL nu se poate selectacoloana care sa fie clustered index, dar ın alte SGBD-uri acest lucru este posibil.

Unul din avantajele acestui tip de index este ca ınregistrarile apropiate se gasesc peputine pagini de memorie.

61

Atentie! Este foarte bine ca valorile care se gasesc ın astfel de campuri sa nu fiealeatoare (deoarece ar putea conduce la multe page split-uri.

Dezavantaje:

– Actualizarea coloanelor indexului ar fi foarte costisitoare, deoarece ar trebui sa semute ınregistrari.

– Poate genera page splits cand o ınregistrare trebuie plasata ıntr-o pagina care estedeja plina.

3.7.3 Procesarea instructiunii SELECT

Pana acum am vazut o varianta simplificata a modului ın care se proceseaza instructiuneaSELECT. Totusi, ın anumite situatii lucrurile sunt mai complicate, deoarece aceasta instructiunepoate fi optimizata.

Pentru fiecare interogare, MySQL ıncearca sa realizeze cea mai buna modalitate de exe-cutare. Aceasta munca este efectuata de o componenta numita query optimizer (optimizatorde interogari). Acest lucru poate fi realizat pe baza mai multor strategii, care au la baza:

• timpul asteptat de executie

• numarul de ınregistrari

• prezenta sau nu a indecsilor

De exemplu, pentru a afisa studentul cu numarul matricol 100, ın strategia generala s-arparcurge urmatorii pasi:

result:=[];

FOR EACH s IN studenti DO

IF s.nrmatricol=100 THEN

result+=s;

ENDFOR;

Daca am avea strategia optimizata, pe baza de indecsi, instructiunea s-ar putea rezolvaastfel:

result:=[];

FOR EACH s IN studenti WHERE s.nrmatricol =100 DO

result+=s;

ENDFOR;

Daca, de exemplu, am vrea sa vedem studentii din Brasov, care au numarul matricol <10 am avea urmatoarea interogare:

SELECT FROM studenti

WHERE nrmatricol < 10 AND oras="Brasov";

62

Prezentam, ın continuare schemele pentru strategia generala si pentru strategia optimi-zata:

result:=[];

FOR EACH s IN studenti DO

IF (s.nrmatricol<10) AND (s.oras="Brasov") DO

result+=s;

ENDFOR;

---------------------------------------------------

result:=[];

FOR EACH s IN studenti WHERE nrmatricol<10 DO

IF s.oras="Brasov" DO

result+=s;

ENDFOR;

3.7.4 Crearea indecsilor

Sintaxa pentru crearea unui index este urmatoarea:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

index_col_name:


index_type:

USING BTREE | HASH

Prezentam, ın continuare cateva exemple de creare a indecsilor:

• Sa se creeze un index hash pentru orasul studentilor:

CREATE INDEX stud_oras USING HASH

ON studenti(oras);

• Sa se creeze un index unic pe nume, pe tabela studenti:

CREATE UNIQUE INDEX nume_stud

ON studenti(nume);

In momentul ın care adaugam un index la o tabela, ınregistrarile din acea tabela trebuiesa respecte conditiile legate de index, altfel crearea nu reuseste.

Automat, cand se creeaza o cheie primara sau alternativa se creeaza un index pentruea. Indexul va purta numele de ”PRIMARY” daca este pentru o cheie primara si respectivnumele primei coloane pentru care este facut daca este vorba de o cheie alternativa.

Indecsii pot fi alterati folosind instructiunea ALTER TABLE, ca ın exemplele urmatoare:

63

• Sa se adauge la tabela studenti un index pe coloana nume


ADD INDEX stud_nume USING BTREE (nume);

• Sa se adauge un index unic ın functie de nume si prenume pe tabela studenti:


ADD UNIQUE INDEX stud_nume_pren

USING HASH (nume, prenume);

3.7.5 Definirea indecsilor ımpreuna cu tabela

Pentru a crea un index se poate folosi oricare din urmatoarele trei metode:

• instructiunea CREATE INDEX

• instructiunea ALTER TABLE

• instructiunea CREATE TABLE

Prezentam, ın continuare doua exemple ın care crearea indecsilor se face ın cadrul instructiuniiCREATE TABLE:

• Sa se creeze tabela studenti, indexandu-se dupa campul nume tabela.


.....

INDEX stud_nume(nume)

)

• Sa se creeze tabela studenti si un index unic dupa nume si prenume care sa foloseascaun hash-index:


.......

UNIQUE INDEX stud_nume_pren USING HASH (nume, prenume)

);

3.7.6 Stergerea indecsilor

Pentru a se sterge un index dintr-o tabela se foloseste interogarea DROP INDEX careare sintaxa:

DROP INDEX index_name ON tbl_name

Un index se poate sterge si folosind interogarea ALTER TABLE.

64

3.7.7 Alegerea coloanelor pentru indecsi

Pentru ca SELECT-urile sa se faca cel mai rapid posibil ın toate cazurile ideal ar fi safacem cate un index pe fiecare coloana. Totusi acest lucru nu este posibil datorita moduluiın care se lucreaza cu indecsi.

Recomandabil este sa avem index pentru:

• cheile primare (se face automat) si pentru cheile candidat.

• pentru cheile straine, deoarece ın felul acesta operatiile de JOIN se vor face mai repede.

Relativ la efectul indecsilor, trebuie remarcate urmatoarele lucruri:

• Cu cat tabela contine mai multe ınregistrari cu atat e mai mare efectul indecsilor.

• Cu cat valorile ınregistrarilor sunt mai variate, cu atat e mai util indexul.

• Daca pentru valoarea cautata sunt foarte putine ınregistrari care o respecta, indexuleste mai eficient.

Alegerea indecsilor ajuta foarte mult la sortarea ınregistrarilor dintr-o tabela, la folosirealui DISTINCT si la GROUP BY.

3.8 Optimizarea performantei interogarilor

Una din principalele probleme cand scriem interogari ineficiente este ca interogarile noas-tre acceseaza prea multe date (prea multe ınregistrari sau prea multe coloane sau MySQLtrebuie sa analizeze prea multe ınregistrari).

Cateva greseli comune sunt:

• Sunt aduse toate datele din care sunt apoi preluate ın aplicatie doar cateva ınregistrari.Totusi, MySQL pentru acest lucru a trebuit sa creeze un ıntreg result-set, care apoi afost trimis la utilizator prin retea.

• Vrem sa vedem toate informatiile despre studentii care s-au ınrolat la vreun curs:

-- GRESIT

SELECT *

FROM studenti s INNER JOIN inrolari i

USING (nrmatricol)

-- CORECT

SELECT s.*

FROM studenti s INNER JOIN inrolari i

USING (nrmatricol)

65

• Trebuie sa fim atenti cand executam interogari de tipul SELECT * FROM ..., deoarececel mai adesea nu avem nevoie de toate coloanele.

Dupa ce ne-am asigurat ca MySQL ne aduce exact datele de care avem nevoie, ar fi binesa ne gandim daca nu cumva modul de lucru al MySQL nu este ineficient. Putem masuramai multe lucruri, precum:

• Timpul de executie

• Numarul de ınregistrari examinate

• Numarul de ınregistrari returnate.

Timpul de executie ne poate spune cam care sunt locurile din aplicatie ın care se pierdecel mai mult timp. Totusi, aceasta nu este neaparat un indicator pentru faptul ca interogareaeste eficienta sau nu, deoarece nu tine cont de complexitatea problemei.

Ideal ar fi ca numarul de ınregistrari returnate sa fie cat mai aproape de numarul deınregistrari examinate, dar foarte des acest lucru nu este posibil.

In principiu, MySQL poate trata o clauza WHERE ın mai multe moduri:

• Foloseste indexul pentru a obtine ınregistrarea dorita.

• Foloseste covering indexes pentru a nu accesa datele din tabele.

• Ia toate datele din tabele, dupa care dupa o scanare, alege doar ınregistrarile care sepotrivesc.

Se pot folosi mai multe moduri de a restructura interogarile:

• E mai bine sa avem interogari complexe sau mai multe interogari simple? In aniitrecuti se considera ca fiind foarte important ca sa facem cat mai putin trafic pe retea.Acest lucru e valabil si acum, dar din ce ın ce mai putin. Totusi, este de preferat safacem un anumit lucru din putini pasi.

• Cateodata e mai bine sa ımpartim o interogare cu foarte multe operatii ın mai multealte subinterogari, fiecare cu mai putine operatii:

--initial

DELETE FROM messages

WHERE created < DATE_SUB(NOW( ),INTERVAL 3 MONTH);

--intr-un program

rows_affected = 0

do {rows_affected = do_query(

"DELETE FROM messages

WHERE created < DATE_SUB(NOW( ),INTERVAL 3 MONTH)

LIMIT 10000")

} while rows_affected > 0

66

• Este bine sa folosim ın anumite situatii ceea ce poarta numele de join decomposition:

--initial

SELECT * FROM tag

JOIN tag_post ON tag_post.tag_id=tag.id

JOIN post ON tag_post.post_id=post.id

WHERE tag.tag=’mysql’;

--ulterior

SELECT * FROM tag WHERE tag=’mysql’;

SELECT * FROM tag_post WHERE tag_id=1234;

SELECT * FROM post WHERE post.id

in (123,456,567,9098,8904);

Aceasta abordare poate avea mai multe avantaje:

– Se poate face caching mult mai eficient.

– Se blocheaza fiecare tabel pentru perioade mai scurte, decat sa fie toate tabeleleblocate pentru mai mult timp.

– Se poate distribui mult mai usor baza de date pe mai multe calculatoare.

– Folosirea lui IN permite MySQL sa sorteze id-urile mai eficient, ceea ce ulteriorpermite regasirea mai usoara a datelor.

– Datele sunt folosite accesate numai o data daca se face join-ul la nivelul aplicatiei,dar de mai multe ori daca se face la nivelul serverului de baze de date.

3.8.1 Lucruri de baza relativ la executia interogarilor

Cand trimitem o interogare la un server se parcurg urmatorii pasi:

1. Clientul trimite interogarea SQL la server.

2. Serverul verifica daca nu cumva ın cache nu are deja salvate datele.

3. Serverul parseaza, preproceseaza si optimizeaza interogarea SQL ıntr-un query execu-tion plan.

4. Query execution engine-ul executa planul facand apel la storage engine.

5. Serverul trimite ınapoi rezultatul la client.

O interogare poate fi ın una din urmatoarele stari (se poate vedea cu SHOW FULLPROCESSLIST): Sleep, Query, Locked, Analyzing and statistics, Copying to tmp table [ondisk], Sorting result, Sending data.

67

O componenta foarte importanta este optimizatorul de interogari. Acesta ıncearca saprezica costuri pentru diverse planuri de execuctie si sa-l aleaga pe cel mai bun.

Unitatea de cost este citirea unei pagini aleatoare de date de 4KB.

Se poate vedea costul unei interogari astfel:

SELECT SQL_NO_CACHE oras, COUNT(*)

FROM studenti

GROUP BY oras

SHOW STATUS LIKE ’last_query_cost’

Exista doua tipuri de optimizari:

• optimizari statice - sunt optimizari care se parcurg pe arborele parsat. Pot fi ganditeca niste optimizari la compilare (se bazeaza pe reguli algebrice care eficientizeaza in-terogarile) si se vor face la fel ıntotdeauna cand se va executa interogarea.

• optimizari dinamice - optimizari la momentul rularii care pot depinde de valoareacautata sau de numarul de ınregistrari din index, etc.

Optimizatorul de interogari poate face optimizari precum:

• Reordonarea join-urilor (poate fi foarte importanta)

• Convertirea OUTER JOIN-urilor ın INNER JOIN-uri (ın anumite situatii cand acestlucru este posibil)

• Aplicarea de reguli algebrice echivalente

• Optimizari pentru COUNT, MIN, MAX

• Evaluarea si reducerea expresiilor constante

• Optimizari ale subinterogarilor

3.9 Utilizarea view-urilor

In principiu, ın MySQL exista doua tipuri de tabele:

• tabele reale - sunt create cu CREATE TABLE si ele stocheaza date propriu-zise.

• virtuale (derivate sau vederi) - care nu stocheaza nici o ınregistrare ci doar anumiteinterogari. Ele exista doar cand sunt folosite ın interogari.

68

3.9.1 Crearea de view-uri

Pentru crearea view-urilor se foloseste urmatoarea sintaxa:

CREATE

[OR REPLACE]

[ALGORITHM = UNDEFINED | MERGE | TEMPTABLE]

[DEFINER = user | CURRENT_USER ]

[SQL SECURITY DEFINER | INVOKER ]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

Exemplu: Sa se creeze un view care returneaza toate orasele distincte din tabela studenti.Sa se foloseasca acest view ıntr-o interogare:

CREATE VIEW view_orase AS

SELECT DISTINCT oras

FROM studenti;

SELECT *

FROM view_orase

WHERE oras LIKE ’B%’;

Dupa cum se poate usor observa, un view poate fi interogat la fel ca orice alta tabela.Mai mult, orice schimbare care apare ın tabela de baza se reflecta imediat si ın view.

Se poate ca la definirea unui view sa se foloseasca un alt view ca ın exemplul urmator:

CREATE VIEW view_orase_b AS

SELECT *

FROM view_orase


Daca se doreste ca view-ul sa fie creat daca nu exista deja sau sa fie ınlocuit daca dejaexista, se poate folosi CREATE OR REPLACE:

CREATE OR REPLACE VIEW view_orase_b AS

SELECT *

FROM view_orase


In mod implicit, un view mosteneste numele coloanelor din care ısi ia datele. Daca sedoreste altceva, se poate face acest lucru.

Exemplu:

CREATE VIEW view_studenti (numele, prenumele) AS


FROM studenti;

69

Datele dintr-un view pot fi actualizate de catre cel care-l foloseste. Acest lucru ınseamnaca se pot face interogari de tipul INSERT, UPDATE si DELETE pe un view.

3.9.2 Optiuni ale view-urilor

Uneori se poate ıntampla, ca ın urma interogarii sa se modifice continutul tabelului sitotusi view-ul sa nu reflecte acest lucru. Este cazul daca inseram o ınregistrare care ın modnormal nu ar fi aparut in view-ul initial, pentru ca acesta avea o clauza WHERE care oexcludea.

Daca vrem sa evitam astfel de situatii, putem folosi optiunea WITH CHECK.Iata si un exemplu:

CREATE VIEW view_studenti AS

SELECT * FROM studenti


WITH CHECK OPTION;

Daca un view contine WITH CHECK atunci:

• UPDATE-ul nu reuseste decat daca, ın urma modificarilor, ınregistrarea ramane ınview.

• INSERT-ul nu reuseste decat daca, ınregistrarea care s-ar adauga se regaseste ın view.

• DELETE-ul sterge doar ınregistrarile din view.

Implicit avem WITH CASCADED CHECK OPTION, ceea ce ınseamna ca se verificaconditiile si pentru view-uri imbricate.

Daca s-ar folosi WITH LOCAL CHECK OPTION atunci nu s-ar verifica decat conditiiledin view-ul curent.

In mod implicit se considera ca utilizatorul curent este cel care a definit view-ul. Dacase doreste schimbarea acestui lucru, se poate folosi optiunea DEFINER:

CREATE DEFINER=’mihai’@’localhost’ VIEW view_studenti_b AS

SELECT * FROM studenti

WHERE nrmatricol<100;

Cel care defineste view-ul trebuie sa aiba drept de SELECT pe tabela (tabelele) pe careactioneaza view-ul. Cei care-l folosesc nu trebuie sa respecte acest lucru. Acest lucru seıntampla daca optiunea SQL SECURITY este setata la SQL SECURITY DEFINER.

Daca optiunea are valoarea SQL SECURITY INVOKER atunci cel care actioneaza view-ul trebuie sa aiba drepturile necesare pentru tabelele pe care le acceseaza.

Pentru un view se mai poate specifica si algoritmul care sa fie folosit la evaluarea view-ului:

• MERGE - instructiunea SELECT care apeleaza view-ul este combinata cu textul luipentru a se obtine interogarea care trebuie efectuata.

70

• TEMPTABLE - se executa ıntai formula view-ului, iar apoi, pe datele dintr-un tabeltemporar se executa noua interogare.

• UNDEFINED - MySQL determina singur ce metoda va fi aplicata (este varianta im-plicita).

Desi nu vom detalia ın cele ce urmeaza, toate aceste optiuni pot fi modificate folosindinstructiunea ALTER VIEW...

3.9.3 Stergerea view-urilor

Pentru a sterge un view, se foloseste instructiunea DROP VIEW cu sintaxa:

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

[RESTRICT | CASCADE]

Cand se sterge un view, automat se sterg toate cele dependente de el. In mod asemanator,cand se sterge o tabela se sterg toate view-urile dependente de ea.

3.9.4 Restrictii referitoare la actualizarea view-urilor

Un view poate fi actualizat numai daca exista o relatie de la 1 la 1 fata de tabela de bazaimplicata.

Trebuie respectate mai multe conditii:

• SELECT-ul nu poate contine DISTINCT

• SELECT-ul nu poate contine functii agregate

• SELECT-ul nu poate contine mai mult de o tabela

• SELECT-ul nu poate contine subinterogari corelate

• SELECT-ul nu poate contine clauza GROUP BY, HAVING, ORDER BY sau operatiipentru lucrul cu multimi

• O coloana virtuala (calculata) nu poate fi actualizata

• SELECT-ul trebuie sa contina toate coloanele care au clauza NOT NULL ın tabela debaza.

71

3.9.5 De ce sa folosim view-urile?

Exista mai multe motive pentru care se folosesc:

• Simplificarea unor rutine complexe

• Reorganizarea tabelelor

• Dezvoltarea pas cu pas a interogarilor SELECT

• Specificarea constrangerilor de integritate - prin folosirea lui WITH CHECK

• O mai buna securitate a datelor - pot fi folosite pentru a proteja de anumiti utilizatorianumite parti ale tabelelor.

72

Capitolul 4

Crearea obiectelor procedurale

Pana ın anii 1986-1987 SQL era un limbaj pur declarativ. Totusi, ın acei ani au ınceputsa apara procedurile stocate.

O procedura stocata consta ıntr-o bucata de cod care se salveaza pe serverul de baze dedate ıntr-o forma precompilata. Ea poate contine mai multe instructiuni SELECT, INSERTprecum si structuri conditionale si repetitive (IF THEN ELSE, WHILE DO).

Ulterior, au aparut si alte tipuri de constructii precum: functiile stocate, triggere-le sievent-urile.

4.1 Proceduri stocate

O procedura stocata reprezinta o bucata de cod care contine instructiuni declarative siprocedurale stocate ıntr-o baza de date. Ele pot fi folosite din interiorul altor proceduristocate, triggere, altor programe sau chiar din aplicatii client MySQL.

Iata un exemplu de procedura stocata care sterge din tabela studenti un student cu unnumar matricol specificat ca parametru de intrare (IN):

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘delete_student‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘delete_student‘ (IN nrmat INTEGER)

BEGIN

DELETE

FROM studenti

WHERE nrmatricol=nrmat;

END $$

DELIMITER ;

Dupa cum se poate vedea, o procedura stocata are 3 parti:

• un nume (ın cazul nostru delete student)

73

• niste parametri (nrmatricol)

• un corp al procedurii stocate

Apelarea instuctiunii CREATE PROCEDURE de mai sus nu conduce la rularea instuctiuniiDELETE, ci la retinerea procedurii ın memorie si o verificare partiala a ei. Pentru a putearula procedura stocata trebuie folosita instructiunea CALL:

CALL delete_student(100);

4.1.1 Transmiterea parametrilor

Parametrii unei proceduri stocate pot fi de 3 feluri:

• parametri de intrare - se foloseste cuvantul cheie IN la specificarea lor

• parametri de iesire - OUT

• parametrii si de intrare si de iesire - INOUT

Observatie: Daca o procedura stocata nu are parametri, atunci trebuie totusi sa folosimparanteze rotunde goale ın locul ın care ar fi trebuit sa fie lista de parametri.

Numele parametrului trebuie sa fie diferit de numele coloanelor. Daca ar fi la fel, MySQLar banui ca de fapt ın ambele locuri ın care ıl folosim avem nume de coloane.

4.1.2 Corpul procedurilor stocate

Corpul procedurii stocate contine ıntre cuvintele cheie BEGIN si END mai multe instructiunicare pot fi procedurale sau declarative, precum si eventuale declaratii de variabile.

La fel ca ın orice limbaj de programare mai multe instructiuni pot fi grupate ıntr-oinstructiune compusa prin BEGIN...END.

Fiecare bloc BEGIN...END trebuie sa se termine cu ”;”. Exceptie face corpul proceduriistocate.

4.1.3 Variabile locale si variabile utilizator

In MySQL, pentru a putea folosi o variabila trebuie sa o declaram ınainte. Prezentam,ın continuare cateva exemple:

DECLARE var1 DECIMAL(4,2);

DECLARE var2 INTEGER;

DECLARE var3 INTEGER DEFAULT 0;

74

Instructiunile DECLARE VARIABLE trebuie sa fie primele instructiuni din cadrul unuibloc BEGIN...END.

Evident, o variabila locala exista doar ın sectiunea BEGIN...END ın care este declarata.Variabilele utilizator sunt acele variabile care sunt definite de utilizator si au o vizibilitate

la nivel de sesiune. Denumirea lor ıncepe cu caracterul ’@’.Prezentam doua exemple de proceduri stocate care folosesc atat variabile locale cat si

utilizator:

• Sa se scrie o procedura stocata care primeste ca parametru un numar si returneaza ıncel de-al doilea numarul imediat urmator:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘next_value‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘next_value‘

(IN nr1 INTEGER, OUT nr2 INTEGER)

BEGIN

DECLARE t INTEGER DEFAULT nr1+1;

SET nr2 = t;

END $$

DELIMITER ;

Pentru a verifica procedura stocata, se pot rula urmatoarele instructiuni din linie decomanda:

SET @x=3;

CALL next_value(@x, @y);

SELECT @y;

• Sa se scrie o procedura stocata care returneaza numarul de ınregistrari din tabelastudenti.

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘nrstudenti‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘nrstudenti‘ (OUT nr INTEGER)

BEGIN

SET nr = (SELECT COUNT(*) FROM studenti);

END $$

DELIMITER ;

Se poate ıncerca prin urmatoarele instructiuni:

75

CALL nrstudenti(@nrs);

SELECT @nrs;

Pentru atribuirea de valori unei variabile se foloseste instructiunea SET (atat pentruvariabile locale cat si pentru cele utilizator).

4.1.4 Structuri de control

In continuare vom prezenta structurile de control care pot fi folosite ın procedurile stocate,prin intermediul unor exemple:

• Sa se scrie o procedura stocata care sa primeasca ca parametri 3 numere si sa returnezeın al 4-lea parametru maximul dintre cele 3 numere:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘max3nr‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘max3nr‘ (

IN x INTEGER, IN y INTEGER, IN z INTEGER, OUT rez INTEGER)

BEGIN

IF x>=y AND x>=z THEN

SET rez=x;

ELSEIF y>=z THEN

SET rez=y;

ELSE

SET rez=z;

END IF;

END $$

DELIMITER ;

Se apeleaza astfel:

SET @x=3, @y=20, @z=4;

CALL max3nr(@x, @y, @z, @max);

SELECT @max;

• Sa se scrie o procedura stocata care calculeaza suma cifrelor unui numar transmis caparametru de intrare si pune rezultatul ıntr-un parametru de iesire:

DELIMITER $$

76

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘suma_cifre‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘suma_cifre‘

(IN x INTEGER, OUT rez INTEGER)

BEGIN

DECLARE suma INT DEFAULT 0;

WHILE x>0 DO

SET suma=suma+x MOD 10;

SET x=x DIV 10;

END WHILE;

SET rez=suma;

END $$

DELIMITER ;

Se apeleaza astfel:

CALL suma_cifre(342, @x);

SELECT @x;

• Sa se construiasca o procedura stocata care primeste ca parametru un numar si retur-neaza ın al doilea numarul de numere prime mai mici sau egale decat acel numar.

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘prime‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘prime‘

(IN n INTEGER, OUT k INTEGER)

BEGIN

DECLARE i INTEGER DEFAULT 2;

DECLARE ok INTEGER;

declare j INTEGER;

SET k=0;

prime_loop: LOOP

IF i>n THEN

LEAVE prime_loop;

END IF;

SET ok=1;

SET j=2;

77

prim: LOOP

IF j > i/2 THEN LEAVE prim;

END IF;

IF i MOD j=0 THEN

SET ok=0;

LEAVE prim;

END IF;

SET j=j+1;

END LOOP prim;

IF ok=1 THEN

SET k=k+1;

END IF;

SET i=i+1;

END LOOP prime_loop;

END $$

DELIMITER ;

Observatie: Asa cum LEAVE este corespondentul din limbajele de programare al luiBREAK, ITERATE este corespondentul lui CONTINUE.

Se apeleaza astfel:

CALL prime(10, @k);

SELECT @k;

4.1.5 Apelarea procedurilor stocate

Sintaxa pentru apelarea unei proceduri stocate este urmatoarea:

CALL sp_name([parameter[,...]])

CALL sp_name[()]

Trebuie tinut cont de anumite reguli:

• Numarul de parametri efectivi trebuie sa fie egal cu numarul de parametri formal (celfolosit la crearea procedurii stocate).

• Daca se apeleaza procedura stocata folosind numele unei baze de date, atunci pentrutabelele care n-au specificata explicit o baza de date se foloseste acea baza de date.

• Evident se poate transmite ca parametru orice valoare scalara. Aceasta este calculataınainte de a fi transmisa procedurii stocate.

78

Se poate chiar sa apelam o procedura stocata ca ın exemplul:

CALL prime((SELECT COUNT(*) FROM studenti), @k);

Se poate chiar sa avem apeluri recursive ale procedurilor stocate.Exercitiu: Sa se construiasca o procedura stocata care sa foloseasca recursivitatea.

4.1.6 Interogarea datelor cu SELECT INTO

Aceasta interogare face ca o singura ınregistrare dintr-o tabela sa fie retinuta ıntr-ovariabila.

Sintaxa noi instructiuni este:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references


[GROUP BY col_name | expr | position

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY col_name | expr | position

[ASC | DESC], ...]

[LIMIT [offset,] row_count | row_count OFFSET offset]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE ’file_name’ export_options

| INTO DUMPFILE ’file_name’

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

Iata si un exemplu:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘studenti_oras‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘studenti_oras‘

(IN oras_in CHAR(50), OUT nr INTEGER)

BEGIN

SELECT COUNT(*)

FROM studenti

WHERE oras=@oras_in

79

INTO nr;

END $$

DELIMITER ;

Se poate ca folosind SELECT INTO sa atribuim mai multe valori ın acelasi timp (douainstructiuni SET):

SELECT oras, judet

FROM studenti

WHERE nrmatricol=100

INTO rez_oras, rez_judet

4.1.7 Mesaje de eroare, handlere si conditii

Toate erorile din MySQL au un numar si o descriere, si mai mult mai multe erori suntgrupate ıntr-un alt cod numit SQLSTATE.

De exemplu, pentru codul SQLSTATE 23000 putem avea mai multe tipuri de erori:

• eroarea 1022 - cheie duplicata

• eroarea 1048 - coloana nu poate avea valoarea NULL

• eroarea 1052 - coloana ambigua

In mod normal, ın momentul ın care apare o eroare, procedura stocata se ıntrerupe. Cuo versiune speciala a lui DECLARE se poate ca totusi acest lucru sa nu se ıntample.

Sintaxa acestei constructii este urmatoarea:

DECLARE handler_type HANDLER

FOR condition_value [, condition_value] ...

statement

handler_type:

CONTINUE

| EXIT

| UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| mysql_error_code

80

Iata si un exemplu:

CREATE PROCEDURE error_1

(OUT ERROR CHAR(5))

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE ’23000’

SET ERROR = ’23000’;


INSERT INTO tipuri_curs VALUES (2,’tip nou’);

END

In exemplul de mai sus se stabileste ca, daca apare o eroare de tipul 23000, variabilaerror primeste o anumita valoare.

Se pot declara si handlere pentru erori de tipul:

DECLARE CONTINUE HANDLER FOR 1062 SET ERROR = ’23000’;

DECLARE CONTINUE HANDLER FOR 1136 SET ERROR = ’21S01’;

Se pot trata si erori mai generale ca ın exemplul urmator:

CREATE PROCEDURE another_error

(OUT ERROR CHAR(5))

BEGIN

DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND,

SQLEXCEPTION SET ERROR = ’XXXXX’;


INSERT INTO tipuri_curs VALUES (2,’another option’);

END

SQLWARNING se foloseste pentru erori al caror cod ıncepe cu 01, NOT FOUND pentruerori al caror cod ıncepe cu 02 iar SQLEXCEPTION pentru erori al caror cod ıncepe cualtceva.

Se poate ca sa dam nume mai semnificative anumitor erori, pentru a le putea folosiulterior ın procedura stocata:

CREATE PROCEDURE another_error

(OUT ERROR CHAR(5))

BEGIN

DECLARE NON_UNIQUE CONDITION FOR SQLSTATE ’23000’;

DECLARE CONTINUE HANDLER FOR NON_UNIQUE



INSERT INTO tipuri_curs VALUES (2,’another option’);

END

S-a folosit aici DECLARE...CONDITION..., care are sintaxa:

81

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

Trebuie facute mai multe observatii:

• Handlerele si conditiile pot fi definite la ınceputul unui bloc BEGIN...END si suntvalabile ın acel bloc si ın toate subblocurile.

• Nu se poate ca ın exact acelasi bloc sa se redefineasca un handler.

• Handlerul poate fi redefinit ıntr-un sub-bloc.

• Cand apare o eroare, se cauta un handler potrivit ın blocul curent, dupa aceea ın bloculimediat superior si tot asa, pana cand se gaseste unul ın care eroarea e tratata.

4.1.8 Folosirea cursoarelor

Cursoarele au fost create pentru a trata acele SELECT-uri care pot returna mai multde o ınregistrare (spre diferenta de SELECT...INTO... care poate trata doar cazul cand sereturneaza o singura ınregistrare).

Exista 4 instructiuni pentru lucrul cu cursoare: DECLARE CURSOR, OPEN CURSOR,FETCH CURSOR si CLOSE CURSOR.

Aceste instructiuni au urmatoarele semnificatii:

• DECLARE CURSOR - permite declararea unui cursor si stabilirea tabelei pe care seaplica

• OPEN CURSOR - deschiderea unui cursor, o singura data la ınceput

• FETCH CURSOR - permite retinerea datelor din ınregistrarea curenta si trecerea laınregistrarea urmatoare

• CLOSE CURSOR - ınchiderea cursorului

Ele au urmatoarea sintaxa:

DECLARE cursor_name CURSOR FOR select_statement

OPEN cursor_name

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name

82

Iata cum s-ar putea numara ınregistrarile dintr-o tabela folosind cursoare:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘students_number‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘students_number‘

(OUT number INTEGER)

BEGIN

DECLARE nrmatr INTEGER;

DECLARE found BOOLEAN DEFAULT TRUE;

DECLARE studenti_cursor CURSOR FOR

SELECT nrmatricol FROM studenti;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET FOUND = FALSE;

SET number = 0;

OPEN studenti_cursor;

FETCH studenti_cursor INTO nrmatr;

WHILE FOUND DO

SET number = number + 1;


END WHILE;

CLOSE studenti_cursor;

END $$

DELIMITER ;

Sa se scrie o procedura stocata care sterge toate ınregistrarile din tabela studenti carenu au corespondent ın tabela inrolari:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘delete_students‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘delete_students‘()

BEGIN

DECLARE nrmatr INTEGER;

DECLARE found BOOLEAN DEFAULT TRUE;

DECLARE studenti_cursor CURSOR FOR

SELECT nrmatricol FROM studenti;

83

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET FOUND = FALSE;

OPEN studenti_cursor;


WHILE FOUND DO

IF (SELECT COUNT(*) FROM inrolari WHERE nrmatricol=nrmatr)

=0 THEN

DELETE FROM studenti WHERE nrmatricol=nrmatr;

END IF;


END WHILE;

CLOSE studenti_cursor;

END $$

DELIMITER ;

4.1.9 Specificarea anumitor caracteristici ale procedurilor stocate

Se pot specifica caracteristici ale procedurilor stocate ıntre parametri transmisi si corpulprocedurii stocate.

Daca specificam NO SQL aceasta ın spune lui MySQL ca ın acea procedura stocata nufolosim instructiuni SQL (nu se acceseazaa baza de date).

CREATE


PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

| SQL SECURITY DEFINER | INVOKER

84

| COMMENT ’string’

routine_body:

Valid SQL procedure statement

exemple:

CREATE DEFINER = ’mihai’@’%’ PROCEDURE test()

(OUT var INTEGER)

BEGIN

SET VAR1 = 10;

END

4.1.10 Stergerea procedurilor stocate

Stergerea procedurilor stocate se face cu ajutorul comenzii DROP PROCEDURE cusintaxa:

DROP PROCEDURE | FUNCTION [IF EXISTS] sp_name

Iata si un exemplu:

DROP PROCEDURE IF EXISTS mydatabase.studenti_oras;

4.1.11 Securitatea procedurilor stocate

Pentru a putea executa o procedura stocata un utilizator trebuie sa aiba drept de EXE-CUTE asupra ei.

Acesta se poate acorda astfel:

GRANT EXECUTE

ON PROCEDURE my_proc

TO mihai;

Utilizatorul care are drept de a executa o anumita procedura nu trebuie sa aiba drepturi siasupra tabelelor folosite din procedura stocata. Totusi, ın alte sisteme de gestiune a bazelorde date se poate ıntampla ca acest lucru sa fie necesar.

4.1.12 Avantaje ale procedurilor stocate

Principalele avantaje ale utilizarii procedurilor stocate sunt:

• Mentenanta mai usoara a codului - Daca avem o procedura stocata care numara catidescendenti are un nod dintr-un arbore, nu trebuie sa schimbam codul programuluidaca structura tabelei se schimba.

85

• Minimizarea traficului ın retea - Nu se executa fiecare interogare separat, ci toateinterogarile se executa si apoi se transmite rezultatul catre programul apelant.

• Evitarea duplicarii codului - Daca aceeasi baza de date se foloseste ın mai multeaplicatii, nu trebuie duplicat codul pentru fiecare limbaj ın parte. Putem scrie codcare sa fie partajat ıntre aceste aplicatii.

4.2 Functii stocate

Functiile stocate seamana foarte mult cu procedurile stocate dar exista si cateva diferenteimportante:

• O functie stocata poate avea parametri de intrare, dar nu poate avea parametri deiesire. Functia ınsasi este parametru de iesire.

• Ele nu se apeleaza folosind instructiunea CALL, ci la fel ca orice functie scalara.

• Ele trebuie sa conctina o instructiune RETURN, care nu poate fi folosita ın cazulprocedurilor stocate.

Sintaxa definirii unei functii stocate este urmatoarea:

CREATE


FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

| SQL SECURITY DEFINER | INVOKER

| COMMENT ’string’

routine_body:

Valid SQL procedure statement

Prezentam, ın continuare, cateva exemple de functii stocate:

86

• Sa se scrie o functie care calculeaza maximul dintre doua numere.

DELIMITER $$

DROP FUNCTION IF EXISTS ‘mydatabase‘.‘maxim‘ $$

CREATE FUNCTION ‘mydatabase‘.‘maxim‘ (x int, y int) RETURNS INT

BEGIN

IF x>=y THEN

RETURN x;

ELSE

RETURN y;

END IF;

END $$

DELIMITER ;

Functia se apeleaza astfel:

SELECT maxim(4,10);

• Sa se scrie o functie care returneaza numarul de studenti dintr-un anumit oras.

DELIMITER $$

DROP FUNCTION IF EXISTS ‘mydatabase‘.‘studenti_oras‘ $$

CREATE FUNCTION ‘mydatabase‘.‘studenti_oras‘ (orass char(30)) RETURNS INT

BEGIN

RETURN (SELECT COUNT(*) FROM studenti WHERE oras=orass);

END $$

DELIMITER ;

Functia se apeleaza astfel:

SELECT studenti_oras(’Brasov’);

• O functie care calculeaza numarul de zile ıntre doua date:

DELIMITER $$

DROP FUNCTION IF EXISTS ‘mydatabase‘.‘studenti_oras‘ $$

CREATE FUNCTION ‘mydatabase‘.‘number_of_days‘

(start_date DATE,end_date DATE)

87

RETURNS INTEGER

BEGIN

DECLARE days INTEGER;

DECLARE next_date, previous_date DATE;

SET days = 0;

SET next_date = start_date + INTERVAL 1 DAY;

WHILE next_date <= end_date DO

SET days = days + 1;

SET previous_date = next_date;

SET next_date = next_date + INTERVAL 1 DAY;

END WHILE;

RETURN days;

END$$

DELIMITER ;

Trebuie facute cateva observatii:

• Functiile stocate pot apela la randul lor alte functii sau proceduri stocate.

• Se pot construi functii stocate recursive.

• Pentru o functie stocata se poate specifica un definer si diverse caracteristici la fel casi pentru o procedura stocata.

• Se poate modifica o functie stocata folosind instructiunea ALTER FUNCTION.

• Acordarea de privilegii se poate face la fel ca ın cazul procedurilor stocate.

• Stergerea unei functii se poate face folosind instructiunea DROP FUNCTION cu sin-taxa:

DROP PROCEDURE | FUNCTION [IF EXISTS] sp_name

4.3 Triggere

Dupa cum am putut vedea pana acum, un server de baze de date este pasiv prin definitie.Aceasta ınseamna ca el nu face nimic fara a i se cere prin intermediul unei interogari SQL.

In aceasta sectiune vom prezenta modul ın care MySQL poate deveni un server activ prinintermediul triggerelor.

Un trigger este o bucata de cod (instructiuni declarative si procedurale) care se executade catre server ın momentul ın care se executa anumite operatii si anumite conditii suntındeplinite.

88

Principala diferenta ıntre triggere si proceduri stocate este aceea ca triggerele nu suntapelate explicit de catre utilizator, ci mai degraba sunt apelate transparent de catre MySQLın anumite conditii.

Exemple de situatii ın care se executa un trigger sunt adaugarea unei noi ınregistrari sauatunci cand se sterge o ınregistrare.

4.3.1 Sintaxa instructiunii CREATE TRIGGER

Instructiunea CREATE TRIGGER are 3 parti:

• momentul ın care se apeleaza triggerul

• evenimentul care genereaza triggerul

• actiunea efectuata de trigger

Sintaxa instructiunii CREATE TRIGGER este urmatoarea:

CREATE


TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Observatii:

• Nu se pot asocia triggere cu tabele temporare sau view-uri.

• Pentru a putea crea un trigger, trebuie ca utilizatorul care-l creeaza sa aiba privilegiulTRIGGER asupra tabelei pentru care-l creeaza (de la versiunea 5.1.6 in sus, pentruprecedentele trebuia sa aiba privilegiul SUPER).

• triger time reprezinta momentul ın care se executa triggerul. Poate fi BEFORE sauAFTER.

• trigger moment reprezinta evenimentul care produce executarea triggerului. Poate fiunul dintre: INSERT (inclusiv INSERT, LOAD DATA si REPLACE), UPDATE sauDELETE (este activat la folosirea lui REPLACE si DELETE, dar nu si ın cazul TRUN-CATE TABLE sau DROP TABLE, deoarece ultimele doua nu folosesc DELETE).

• Nu se poate sa avem doua triggere pentru acelasi eveniment si acelasi moment. Deexemplu, nu putem avea 2 triggere BEFORE UPDATE.

• Momentan, triggerele nu sunt activate de operatiile care rezulta din constrangeri legatede cheile straine !!!!!

Exemplu: Sa se scrie un trigger care, ın momentul ın care se introduce o ınregistrare ın ta-bela tipuri curs, retine o alta ınregistrare ıntr-o tabela tipuri ın care va retine utilizatorulcare a facut inserarea, momentul ın care a facut-o, precum si id-ul ınregistrarii:

89

-- crearea tabelei

CREATE TABLE tipuri(

user VARCHAR(30) NOT NULL,

data DATE NOT NULL,

id BIGINT NOT NULL

);

-- crearea triggerului

DELIMITER $$

DROP TRIGGER IF EXISTS ‘mydatabase‘.‘test‘ $$

CREATE TRIGGER ‘mydatabase‘.‘test‘

AFTER INSERT

ON tipuri_curs

FOR EACH ROW

BEGIN

INSERT INTO tipuri

VALUES(USER(), CURDATE(),NEW.tip_curs);

END $$

DELIMITER ;

Observatie: Am folosit FOR EACH ROW ceea ce ınseamna ca se va executa codul pentrufiecare inregistrare introdusa. FOR EACH STATEMENT ar ınsemna ca se executa trigger-ulpentru fiecare interogare (inca nu e suportata).

Un trigger poate apela si proceduri stocate, ca ın exemplul urmator:

DELIMITER $$

DROP PROCEDURE IF EXISTS ‘mydatabase‘.‘insert_tipuri‘ $$

CREATE PROCEDURE ‘mydatabase‘.‘insert_tipuri‘

(_user char(30), _data date, tipid bigint)

BEGIN

INSERT INTO tipuri

VALUES (_user, _data, tipid);

END $$

DELIMITER ;

-----------------------------------

DELIMITER $$

DROP TRIGGER IF EXISTS ‘mydatabase‘.‘test‘ $$

90

CREATE TRIGGER ‘mydatabase‘.‘test‘

AFTER INSERT

ON tipuri_curs

FOR EACH ROW

BEGIN

CALL insert_tipuri(user(), curdate(),new.tip_curs);

END $$

DELIMITER ;

4.3.2 Stergerea triggerelor

Stergerea triggerelor se poate face cu instructiunea DROP TRIGGER:

DROP TRIGGER table_name.trigger_name

4.4 Event-uri

In mod normal, ın MySQL, dupa cum am vazut pana acum, toate operatiile care seefectueaza sunt datorate unor interogari cerute de utilizator sau de aplicatii client.

Totusi exista posibilitatea retinerii ın baza de date a unor operatii care sa se efectueze laun moment de timp sau periodic.

O situatie ın care se pot folosi evenimente este cand vrem ca, periodic, datele din anumitetabele sa fie mutate ın alte tabele pentru a ri arhivate.

Evenimentele au fost adaugate ın MySQL ıncepand cu versiunea 5.1.6.

4.4.1 Crearea unui eveniment

Un eveniment poate fi creat folosind instructiunea CREATE EVENT care are urmatoareasintaxa:

CREATE


EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT ’comment’]

DO sql_statement;

schedule:

91

AT timestamp [+ INTERVAL interval] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

interval:

quantity YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

Dupa cum se poate vedea, aceasta instructiune are 2 parti:

• momentul sau intervalul ın care sa se produca evenimentul

• actiunea care trebuie sa se produca

Un eveniment poate fi activ (el poate fi folosit, asa este imediat ce este creat) sau inactiv(este retinut ın catalogul de evenimente dar nu este verificat de planificatorul de evenimente).

Executarea unui eveniment poarta numele de invocare. De fiecare data cand este invocatun eveniment se efectueaza actiunea corespunzatoare.

Pentru ca folosirea evenimentelor sa fie activata, trebuie pornit planificatorul de eveni-mente:

SET GLOBAL EVENT_SCHEDULER = TRUE

El poate fi oprit prin instructiunea:

SET GLOBAL EVENT_SCHEDULER = FALSE

Planificatorul de evenimente poate fi pornit si atunci cand se porneste serverul MySQL:

mysqld ... -event_scheduler=1

Pentru a ilustra modul de lucru cu eventuri, cream o tabela events invoked:

CREATE TABLE events_invoked(

event_type VARCHAR(30) NOT NULL,

data DATE NOT NULL

);

Iata si cateva exemple de evenimente:

• Sa se scrie un eveniment care sa se produca chiar ın acelasi moment si care sa inserezeo valoare ın tabela.

CREATE EVENT event1

ON SCHEDULE AT NOW()

DO INSERT INTO EVENTS_INVOKED VALUES (’DIRECT’, NOW())

92

• Sa se scrie un eveniment care sa se produca la finalul anului 2010.

CREATE EVENT END2010

ON SCHEDULE AT ’2010-12-31 11:00:00’

DO INSERT INTO EVENTS_INVOKED VALUES (’END2010’, NOW())

• Sa se scrie un eveniment care sa se produca peste exact 3 zile.

CREATE EVENT THREEDAYS

ON SCHEDULE AT NOW() + INTERVAL 3 DAY

DO INSERT INTO EVENTS_INVOKED VALUES (’THREEDAYS’, NOW())

• Sa se creeze un eveniment care sa se produca ın prima zi de duminica care urmeaza.

CREATE EVENT NEXT_SUNDAY

ON SCHEDULE AT

CASE DAYNAME(NOW())

WHEN ’Sunday’ THEN NOW() + INTERVAL 7 DAY

WHEN ’Monday’ THEN NOW() + INTERVAL 6 DAY

WHEN ’Tuesday’ THEN NOW() + INTERVAL 5 DAY

WHEN ’Wednesday’ THEN NOW() + INTERVAL 4 DAY

WHEN ’Thursday’ THEN NOW() + INTERVAL 3 DAY

WHEN ’Friday’ THEN NOW() + INTERVAL 2 DAY

WHEN ’Saturday’ THEN NOW() + INTERVAL 1 DAY

END

DO INSERT INTO EVENTS_INVOKED

VALUES (’NEXT_SUNDAY’,NOW())

• Sa se creeze acelasi eveniment ca cel precedent, dar mai succint.

CREATE EVENT NEXT_SUNDAY

ON SCHEDULE AT

NOW() + INTERVAL (8 - DAYOFWEEK(NOW())) DAY


VALUES (’NEXT_SUNDAY’,NOW())

• Sa se creeze un eveniment care se va produce ın ziua urmatoare la ora 11.

CREATE EVENT MORNING11

ON SCHEDULE AT TIMESTAMP(CURDATE() +

INTERVAL 1 DAY, ’11:00:00’)

DO INSERT INTO EVENTS_INVOKED VALUES (’MORNING11’, NOW())

93

• Sa se creeze un eveniment care sa se execute la fiecare 2 ore, ıncepand de peste 3 oresi pana la ora 11pm a zilei curente.

CREATE EVENT EVERY2HOUR

ON SCHEDULE EVERY 2 HOUR

STARTS NOW() + INTERVAL 3 HOUR

ENDS CURDATE() + INTERVAL 23 HOUR

DO INSERT INTO EVENTS_INVOKED VALUES (’EVERY2HOUR’, NOW())

• Sa se execute o anumita instructiune de 6 ori, ıncepand de peste exact o zi la ora 12,la interval de 1 minut.

CREATE EVENT SIXTIMES

ON SCHEDULE EVERY 1 MINUTE

STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY,’12:00:00’)

ENDS TIMESTAMP(CURDATE() + INTERVAL 1 DAY,’12:00:00’)

+ INTERVAL 5 MINUTE


VALUES (’SIXTIMES’, NOW())

• Sa se ruleze o instructiune duminica, pentru 5 saptamani la rand.

CREATE EVENT FIVESUNDAYS

ON SCHEDULE EVERY 1 WEEK

STARTS CASE DAYNAME(NOW())

WHEN ’Sunday’ THEN NOW()







END

ENDS CASE DAYNAME(NOW())








END + INTERVAL 4 WEEK


VALUES (’FIVESUNDAYS’,NOW())

94

• Ce face urmatorul eveniment?

CREATE EVENT SUNDAYS

ON SCHEDULE EVERY 1 WEEK

STARTS TIMESTAMP(CASE DAYNAME(NOW())








END, ’15:00:00’)

ENDS TIMESTAMP(

CASE DAYNAME(CONCAT(YEAR(CURDATE()),’-12-31’))

WHEN ’Sunday’ THEN

CONCAT(YEAR(CURDATE()),’-12-31’)

WHEN ’Monday’ THEN

CONCAT(YEAR(CURDATE()),’-12-31’) - INTERVAL 1 DAY

WHEN ’Tuesday’ THEN


WHEN ’Wednesday’ THEN


WHEN ’Thursday’ THEN


WHEN ’Friday’ THEN


WHEN ’Saturday’ THEN


END, ’15:00:00’)

DO INSERT INTO EVENTS_INVOKED VALUES (’SUNDAYS’, NOW())

4.4.2 Proprietati ale evenimentelor

Exemple de astfel de proprietati sunt:

• ON COMPLETION NOT PRESERVE - ın momentul ın care s-a terminat evenimentulel este sters, ON COMPLETION PRESERVE - dupa ce s-a terminat el este pastrat,chiar daca nu se va mai executa

• COMMENT - permite stocarea unui comentariu relativ la eveniment

• DISABLE - face ca evenimentul sa fie dezactivat implicit

95

4.5 Utilizatori si securitate

O problema foarte importanta ın orice sistem soft este mecanismul de securitate si ges-tiune a drepturilor utilizatorilor. Intr-un sistem de gestiune a bazelor de date, aceasta estede maxima importanta.

In MySQL exista 4 mari categorii de privilegii care se pot acorda:

• Privilegii la nivel de coloane (de exemplu, un anumit utilizator are dreptul sa modificedate ıntr-un tabel, dar numai pe anumite coloane)

• Privilegii la nivel de tabele (de ex., un utilizator are dreptul sa faca interogari SELECTnumai pe anumite tabele)

• Privilegii la nivel de baza de date, referitor la toate obiectele specifice ale acesteia (deex, privilegiul de a crea noi tabele).

• Privilegii utilizator relativ la toate bazele de date recunoscute de MySQL (crearea/stergerea unor baze de date)

Vom prezenta ın continuare modul ın care se pot crea noi utilizatori, carora li se potacorda anumite drepturi folosind comanda GRANT si, daca este nevoie, li se pot revocadrepturile folosind REVOKE.

4.5.1 Adaugarea si stergerea utilizatorilor

Pentru adaugarea de noi utilizatori se foloseste comanda CREATE USER, care areurmatoarea sintaxa:

CREATE USER user [IDENTIFIED BY [PASSWORD] ’password’]

[, user [IDENTIFIED BY [PASSWORD] ’password’]] ...

Cel care foloseste aceasta instructiune trebuie sa aiba dreptul de CREATE USER la nivelglobal sau sa aiba dreptul de INSERT la nivelul bazei de date mysql.

Trebuie precizat numele utilizatorului nou, urmat de parola acestuia, iar ın cazul ıncare parola este deja transformata folosind MD5, trebuie sa fie prezent si cuvantul cheiePASSWORD.

Daca nu se introduce nici o parola, atunci utilizatorul se poate logina fara parola, ceeace totusi nu este o idee foarte buna.

Numele unui utilizator este alcatuit din doua parti:

• numele propriu-zis al utilizatorului

• calculatorul de la care se poate conecta utilizatorul (poate fi un nume de calculatorsau % sau o combinatie ıntre cele doua). Daca nu se precizeaza nimic, automat seconsidera ca este ’%’.

Prezentam cateva exemple:

96

• Sa se creeze un utilizator ”dumitru” care sa se poata conecta de pe orice calculator sicare sa aiba parola ’parola’.

CREATE USER dumitru@’%’

IDENTIFIED BY ’parola’;

• Sa se creeze un nou utilizator ”radu” care sa se poata conecta numai de pe calculatorullocal. Nu se va preciza nici o parola.

CREATE USER radu@localhost;

Observatie: Doi utilizatori cu doua calculatoare de pe care se pot loga sunt consideratidoi utilizatori distincti, putand fi asignate drepturi diferite fiecaruia.

Pentru a sterge un utilizator se foloseste comanda DROP USER, care are sintaxa:

DROP USER user [, user] ...

Trebuie facute cateva observatii:

• In momentul ın care se sterge un utilizator, automat se sterg toate drepturile asignatelui.

• Daca se sterge un utilizator nu se sterg automat si obiectele create de el. Astfel,tabelele, vederile, procedurile stocate etc. create de el raman ın continuare.

• Pana la versiunea 5.0.2 nu se puteau sterge decat utilizatori pentru care nu existaudrepturi. Dupa aceasta se sterg automat si drepturile.

• In momentul ın care se sterge un utilizator, daca acesta este loginat, el poate sa-sicontinue operatiile pe care le face pana cand se ınchide sesiunea lui.

4.5.2 Schimbarea numelor utilizatorilor si a parolelor

Pentru a schimba numele unui utilizator exista instructiunea RENAME USER, cu sin-taxa:

RENAME USER old_user TO new_user

[, old_user TO new_user] ...

Putem avea eroare daca utilizatorul nu exista sau daca deja exista utilizatorul nou.Sa se scrie o interogare care sa realizeze redenumirea utilizatorului ’dumitru’@’localhost’

ın ’ovidiu’@’localhost’:

RENAME USER ’dumitru’@’localhost’ TO ’ovidiu’@’localhost’;

Se poate schimba parola unui utilizator folosind instructiunea SET PASSWORD, careare sintaxa:

97

SET PASSWORD [FOR user] = PASSWORD(’some password’)

Iata si doua exemple:

• Sa se schimbe parola utilizatorului curent ın ’parola’:

SET PASSWORD = PASSWORD(’parola’);

• Sa se schimbe parola utilizatorului ’radu’ ın ’parola’:

SET PASSWORD FOR radu = PASSWORD(’parola’);

4.5.3 Acordarea de privilegii la nivel de coloane si tabele

MySQL suporta urmatoarele tipuri de privilegii: SELECT, INSERT, UPDATE, DE-LETE, REFERENCES, CREATE, ALTER, INDEX, DROP, ALL (ALL PRIVILEGES).

Un privilegiu poate fi acordat doar de un utilizator care are la randul lui suficienteprivilegii.

Privilegiile se pot oferi cu instructiunea GRANT, care are sintaxa prezentata ın cele ceurmeaza:

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] tbl_name | * | *.* | db_name.*

TO user [IDENTIFIED BY [PASSWORD] ’password’]

[, user [IDENTIFIED BY [PASSWORD] ’password’]] ...

[REQUIRE

NONE |

[SSL| X509]

[CIPHER ’cipher’ [AND]]

[ISSUER ’issuer’ [AND]]

[SUBJECT ’subject’]]

[WITH with_option [with_option] ...]

object_type =

TABLE

| FUNCTION

| PROCEDURE

with_option =

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

98

Daca se acorda privilegii unui utilizator care nu exista ınca, automat acel utilizator estecreat.

Prezentam, ın continuare, cateva exemple de acordare de drepturi:

• Sa se acorde dreptul de SELECT asupra tabelei studenti utilizatorului ’mihai’@’localhost’,care nu exista si trebuie creat acum.

GRANT SELECT

ON mydatabase.studenti

TO ’mihai’@’localhost’ IDENTIFIED BY ’parola’;

• Sa se acorde dreptul de UPDATE pe coloanele oras, judet si tara utilizatorului ’mi-hai’@’localhost’.

GRANT UPDATE

ON studenti

TO ’mihai’@’localhost’

• Sa se acorde utilizatorului ’mihai’@’localhost’ dreptul de a face UPDATE-uri pe coloa-nele nume si prenume din tabela studenti.

GRANT UPDATE(nume, prenume)

ON studenti

TO ’mihai’@’localhost’;

4.5.4 Acordarea de privilegii la nivelul bazei de date

Privilegiile prezentate anterior se aplica la nivel de tabel. Se pot da drepturi si relativ lao ıntreaga baza de date.

Se pot acorda urmatoarele privilegii: SELECT, INSERT, DELETE, UPDATE, REFE-RENCES, CREATE, ALTER, DROP, INDEX, CREATE TEMPORARY TABLES, CRE-ATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ROU-TINE, LOCK TABLES, ALL (ALL PRIVILEGES).

Prezentam, ın continuare, doua exemple:

• Sa se acorde drepturile de SELECT, INSERT, UPDATE si DELETE, asupra tuturortabelelor din baza de date mydatabase.

GRANT SELECT, INSERT, UPDATE, DELETE

ON mydatabase.*

TO ’mihai’@’localhost’;

• Sa i se acorde utilizatorului cristi dreptul de a face SELECT si INSERT ın toatetabelele de pe server.

GRANT SELECT, INSERT

ON *

TO cristi;

99

4.5.5 Acordarea de privilegii la nivel de utilizator

Pentru fiecare privilegiu care a fost prezentat la sectiunea precedenta se pot defini pri-vilegii la nivel de utilizator (ele nu se refera doar la anumite obiecte, ci au o aplicabilitategenerala).

Aceste privilegii sunt: SELECT, INSERT, DELETE, UPDATE, REFERENCES, CRE-ATE, ALTER, DROP, INDEX, CREATE TEMPORARY TABLES, CREATE VIEW, SHOWVIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ROUTINE, LOCK TABLES(acestea existau si ın sectiunea precedenta), CREATE USER, SHOW DATABASES, FILE,PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN,SUPER, USAGE.

Iata si cateva exemple:

• Sa se acorde utilizatorului mihai drepturile CREATE, ALTER si DROP pentru toateobiectele din baza de date.

GRANT CREATE, ALTER, DROP

ON *.*

TO mihai;

• In timpul instalarii MySQL se acorda utilizatorului root urmatoarele drepturi:

GRANT ALL PRIVILEGES

ON *.*

TO root;

• Sa se acorde utilizatorului ’cristi’ dreptul de a crea alti utilizatori:

GRANT CREATE USER

ON *.*

TO cristi;

4.5.6 Dreptul de a da drepturi: optiunea WITH GRANT

In mod normal, utilizatorul care primeste un drept nu poate sa-l transmita mai departe.Totusi acest lucru se poate face daca atunci cand se acorda un drept se specifica optiuneaWITH GRANT OPTION.

Iata doua exemple:

• Sa se acorde dreptul de SELECT asupra tuturor bazelor de date utilizatorului ’mihai’,astfel ıncat el sa poata sa-l transmita mai departe.

GRANT SELECT

ON *.*

TO mihai

WITH GRANT OPTION;

100

• Ce se ıntampla daca se executa urmatoarele interogari?

GRANT CREATE

ON *.*

TO mihai;

GRANT SELECT, INSERT

ON *.*

TO mihai

WITH GRANT OPTION;

In urma executarii celor 2 interogari, utilizatorul ’mihai’ poate sa dea mai departe,atat drepturile de SELECT si INSERT, cat si dreptul de CREATE.

4.5.7 Restrictionarea privilegiilor

Se poate ca unui utilizator sa i se acorde drepturi si cu anumite constrangeri, precumın exemplul urmator:

GRANT SELECT

ON *.*

TO mihai

WITH MAX_QUERIES_PER_HOUR 1

Alte restrictionari posibile ar putea fi: MAX CONNECTIONS PER HOUR, MAX UPDATES PER HOUR,MAX USER CONNECTIONS.

Valoarea 0 pentru oricare din aceste proprietati implica nelimitarea acelei proprietati.

4.5.8 Revocarea privilegiilor

Privilegiile pot fi revocate prin interogarea REVOKE:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] tbl_name | * | *.* | db_name.*

FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

Aceasta instructiune permite revocarea drepturilor unor utilizatori. Pentru a o puteafolosi, utilizatorul respectiv trebuie sa aiba privilegiul GRANT OPTION si sa aiba drepturilepe care le revoca.

Prezentam cateva interogari:

101

• Sa se revoce toate drepturile utilizatorului ’mihai’@’localhost’:

REVOKE ALL, GRANT OPTION FROM ’mihai’@’localhost’;

Observatie: Aceasta instructiune nu sterge si utilizatorul, ci doar toate drepturile lui.

• Sa se revoce dreptul de INSERT al utilizatorului ’cristi’ asupra tabelei studenti:

REVOKE INSERT

ON studenti

FROM cristi;

Daca un utilizator are drepturi care se suprapun (cum ar fi faptul ca poate sa facaSELECT pe o tabela si poate sa faca SELECT pe toate tabelele dintr-o baza de date),retragerea unuia din drepturi nu implica si retragerea celuilalt.

Observatie: Se pot acorda drepturi unui utilizator si pentru view-uri. Acest lucru ajutala ımbunatatirea securitatii.

4.6 Tranzactii si lucrul intr-un mediu multiuser

In toate exemplele pe care le-am utilizat pana acum am pornit de la presupunerea ca noisuntem singurii utilizatori ai sistemului de gestiune al bazei de date.

Totusi, ın realitate, lucrurile sunt mult mai complicate, deoarece MySQL este folosit ınmod concurent de mai multi utilizatori. Problema care se pune este ce se ıntampla daca maimulti utilizatori acceseaza aceeasi ınregistrare simultan.

Pentru a studia acest lucru vom prezenta conceptele de tranzactie, savepoint, lock, dea-dlock si nivel de izolare.

O observatie importanta este faptul ca nu toate engine-urile suporta lucrul cu tranzactii.Unele din engine-urile care nu suporta sunt MyISAM si Memory, iar unul care suporta esteInnoDB.

4.6.1 Conceptul de tranzactie

O tranzactie este un set de ınregistrari care se executa ın numele unui utilizator si carepot fi refacute ın situatia ın care acesta ısi doreste acest lucru.

In mod implicit, aplicatiile client care utilizeaza MySQL, sunt setate ın modul autocom-mit. Aceasta ınseamna ca orice instructiune SQL este considerata ca o tranzactie, ea putandfi refacuta doar daca se efectueaza instructiunea complementara.

Implicit, cand ne conectam la MySQL, variabila autocommit este setata la 1. Pentru aschimba acest lucru putem efectua urmatoarea instructiune:

SET @@autocommit = 0

102

Daca dupa ce am facut acest lucru, efectuam cateva comenzi SQL care modifica ınregistrariledin BD, executam comanda ROLLBACK, automat toate acestea sunt refacute.

Se poate vedea acest lucru daca se executa urmatoarele interogari:

SET @@autocommit = 0;

SELECT * FROM note;

DELETE FROM note;

SELECT * FROM note;

ROLLBACK;

SELECT * FROM note;

Astfel sunt clare 2 lucruri:

• cu ROLLBACK se refac toate instructiunile de la ınceputul tranzactiei

• cu COMMIT se salveaza modificarile facute.

Motivul pentru care avem nevoie de tranzactii este faptul ca ın timp ce se produc maimulte instructiuni dependente una de alta, se poate ca ceva sa nu mearga bine si atunci s-arlasa baza de date ıntr-o stare inconsistenta.

Observatie importanta: Exista anumite instructiuni pentru care nu se poate face undo sicare, prin executarea lor, automat implica un commit. Astfel de instructiuni sunt CREATETABLE, ALTER FUNCTION, GRANT si DROP INDEX.

4.6.2 Pornirea unei tranzactii

Pornirea unei noi tranzactii se poate face implicit, dupa ce s-a apelat commit sau prinfolosirea instructiunii START TRANSACTION.

O instructiune START TRANSACTION conduce la comiterea tuturor instructiunilorcare apartineau tranzactiei precedente. Mai mult decat atat, modul autocommit este dez-activat. Dupa terminarea tranzactiei care tocmai ıncepe, modul autocommit este setat lavechea lui valoare, indiferent care era aceasta.

4.6.3 Lucrul cu savepoints

Se poate sa se refaca tot ceea ce s-a efectuat ın tranzactia curenta folosindu-se ROLL-BACK WORK. Daca se doreste, se poate, de asemenea, sa se salveze o anumita stare si sase aduca tranzactia la acea stare.

Se creeaza un savepoint, prin instructiunea:

103

SAVEPOINT nume_savepoint

Se poate reveni la acea stare prin instructiunea:

ROLLBACK WORK TO SAVEPOINT nume_savepoint;

Observatie: In momentul ın care se revine la un anumit savepoint, nu se pot reface decatinstructiunile din tranzactia curenta. Iata un exemplu:

SET AUTOCOMMIT=0;

SAVEPOINT test1000;

SELECT * FROM tipuri_curs;

INSERT INTO tipuri_curs VALUES(NULL, ’bla’);

ROLLBACK TO test1000;

SELECT * FROM tipuri_curs;

Tranzactiile pot fi folosite si ın contextul procedurilor stocate.

Observatie: Daca o tranzactie ıncepe ınainte de procedura stocata, si se termina doardupa ea, atunci instructiunile din procedura se considera a fi facute ın acea tranzactie.

4.6.4 Probleme care pot aparea datorita accesului concurent

O astfel de problema ar putea fi daca ıntr-o tranzactie, se sterg toate ınregistrarile dintr-otabela. Ce s-ar ıntampla cu ceilalti utilizatori care folosesc acea tabela pana cand tranzactiaajunge ın starea commit?

Exista mai multe astfel de probleme, care pot fi grupate astfel:

• Dirty Read (Uncommited Read) - Aceasta problema apare atunci cand un utili-zator vede niste date pe care alt utilizator nu le-a comis ınca.

U1: INSERT INTO tipuri_curs VALUES(null,’optional2’);

U2: SELECT * FROM tipuri_curs;

U1: ROLLBACK;

• Nonrepeatable Read (Nonreproducible Read) - Acest lucru se ıntampla atuncicand la citiri diferite avem pentru aceeasi ınregistrare date diferite ın interiorul uneisingure tranzactii.

104

U1: SELECT cod_student FROM studenti WHERE oras=’Brasov’

INTO @test;

U2: UPDATE studenti SET oras=’sibiu’ WHERE cod_student=2;

U2: COMMIT;

U1: SELECT nume, prenume,oras FROM studenti

WHERE cod_student IN @test;

• Phantom Read - In cadrul aceleiasi tranzactii, la doua citiri distincte se obtinınregistrari diferite.


U2: INSERT INTO tipuri_curs VALUES (NULL,’ceva’);

U2: COMMIT;


• Lost Update - Se ıntampla cand doi utilizatori modifica aceleasi date, amandoi avandla baza datele originale. Astfel, modificarea facuta de unul dintre ei se va pierde.

U1: UPDATE tipuri_curs SET den_tip=CONCAT(den_tip,’a’)

WHERE cod_tip=1;

U2: UPDATE tipuri_curs SET den_tip=CONCAT(den_tip,’b’)

WHERE cod_tip=1;

U1: COMMIT;

U2: COMMIT;

In cele ce urmeaza vom prezenta mecanismele pe care le prezinta MySQL pentru a rezolvaastfel de probleme.

Evident ca s-ar putea lucra ın asa fel ıncat doi utilizatori sa nu poata executa simultantranzactii, dar acest lucru ar ıncetini foarte tare serverul de baze de date. Am spune ca avemo concurenta scazuta.

4.6.5 Mecanismul de locking

Mecanismul de locking este unul cat se poate de simplu. In momentul ın care o ınregistrareeste folosita ea este blocata, nemaiputand fi folosita de o alta tranzactie, pana cand nu seexecuta commit.

Are sens sa folosim locking din 2 motive:

• emuleaza lucrul cu tranzactii

• ajuta la realizarea unor update-uri mai rapide.

105

Acest mecanism rezolva cele 4 probleme pe care le-am prezentat anterior.Se considera ca mecanismul functioneaza corect daca cele 2 tranzactii dau un rezultat

care s-ar obtine daca ele s-ar executa serial (indiferent ın ce ordine).Evident, aceste lock-uri se mentin ın niste buffere, care nu sunt vizibile pentru utilizator.Prin folosirea lor se asigura faptul ca tranzactiile sunt executate serial.Pentru a mari nivelul de concurenta, exista de fapt doua tipuri de locking:

• share lock (read) - alti utilizatori pot citi acea ınregistrare dar nu o pot modifica.

• exclusive lock (write) - alti utilizatori nu pot folosi acea ınregistrare pana cand ea nueste eliberata.

Trebuie sa fim foarte atenti atunci cand scriem tranzactii, pentru a nu produce situatiide DEADLOCK. MySQL nu le detecteaza.

Atunci cand se fac numeroase operatii asupra unei ıntregi tabele, se poate sa se blochezetoata tabela cu LOCK TABLE.

Sintaxa ei este:

LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] ...

lock_type:

READ [LOCAL]

| [LOW_PRIORITY] WRITE

UNLOCK TABLES

La sfarsitul unei tranzactii LOCK-ul este eliberat automat.MySQL suporta urmatoarele tipuri de locking:

• READ - aplicatia poate citi tabela, alte aplicatii pot de asemenea sa o citeasca (chiarfara a obtine un lock de tip READ) dar nu pot modifica tabela.

• READ LOCAL - (numai pentru tabele MyISAM) mai multi utilizatori pot adaugaınregistrari simultan (neconflictuale).

• WRITE - aplicatia poate modifica tabela, timp ın care alte aplicatii nu o pot nicimacar accesa.

• LOW PRIORITY WRITE - aplicatia citeste tabela, alte aplicatii pot citi din tabela.O alta aplicatie poate primi lock-ul numai daca s-a terminat cea curenta.

Obs: In momentul ın care s-au blocat niste tabele, numai acele tabele pot fi folosite dinacel moment ın tranzactie. Cu alte cuvinte, toate tabelele pe care le folosim ıntr-o astfel detranzactie trebuie sa fie blocate.

106

4.6.6 Nivelul de izolare

In plus fata de ceea ce stiam, fiecare tranzactie ın MySQL are un anumit nivel de izolare.Avem urmatoarele 4 nivele de izolare:

• Serializable - utilizatorii sunt cel mai separati unii de altii

• Repeatable read - Avem blocari atunci cand se citesc date si blocari exclusive atuncicand se scriu. Putem fi siguri ca daca se face un SELECT de mai multe ori, rezultatelevor fi aceleasi. Singura problema este ın cazul lost update.

• Cursor stability (read committed) - Este la fel ca precedentul, doar ca ın momentulın care s-a procesat un SELECT, datele sunt deblocate. Blocarile exclusive se fac lafel ca ın cazul precedent, deblocarile fiind facute doar cand tranzactia s-a terminat.

• Dirty read (read uncommitted) - Este la fel ca precedenta, numai ca se elibereazalock-urile exclusive imediat ce s-a facut modificarea. Aceasta ınseamna ca, un utilizatorpoate vedea modificarile facute de alt utilizator ınainte ca acesta sa faca commit.

Se poate defini un nivel de izolare numai cu acele engine-uri care suporta tranzactii, cumsunt: InnoDB si BDB.

Pentru a vedea nivelul setat la un moment dat, putem efectua urmatoarea interogare:

SELECT @@GLOBAL.TX_ISOLATION;

sau

SHOW GLOBAL VARIABLES LIKE ’TX_ISOLATION’;

Valoarea implicita a acestui indicator este REPEATABLE READ.

Se poate schimba nivelul de izolare folosind comanda SET TRANSACTION, care aresintaxa:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SERIALIZABLE

Instructiunea SET TRANSACTION poate si ea sa ınceapa o tranzatie la fel ca STARTTRANSACTION.

Daca o tranzactie doreste sa faca un lock la un moment dat, atunci ea trebuie sa astepteca cel care a facut lock-ul sa elibereze resursa (ın cazul ın care cineva a blocat-o).

107

4.6.7 Momentul procesarii interogarilor

Mereu am pornit de la presupunerea ca interogarile INSERT, UPDATE, DELETE, RE-PLACE si SELECT se executa imediat.

Totusi se poate sa alegem una din urmatoarele variante:

• Delayed - Cel care a apelat instructiunea este anuntat ca ea a fost executata, iar ea estepusa ıntr-o lista de asteptare si este executata cand sistemul crede ca este momentul.

• Low priority - Schimbarea se face atunci cand nici un alt utilizator nu citeste datele.

• High priority - MySQL asigura faptul ca nici un alt utilizator nu poate folosi datele ıntimp ce are loc modificarea, nici macar pentru citiri.

Aceste optiuni apar ın cazul diverselor comenzi:

DELETE [ LOW_PRIORITY ] [ IGNORE ] ...

INSERT [ DELAYED | LOW_PRIORITY | HIGH_PRIORITY ] [ IGNORE ] ...

REPLACE [ DELAYED | LOW_PRIORITY] [ IGNORE ] ...

UPDATE [ LOW_PRIORITY ] [ IGNORE ] ...

108

Partea II

Oracle

109

Capitolul 5

Prezentare generala

5.1 Generalitati

Oracle ofera un suport foarte complex pentru lucrul cu baze de date si realizarea deaplicatii profesionale. In cele ce urmeaza vom insista mai ales asupra bazelor de date.

In continuare vom prezenta o varianta de server mai putin costisitoare din punct devedere al resurselor si anume Oracle Express Edition.

De-a lungul ultimilor 30 de ani, Oracle a fost si este poate cea mai avansata solutie carepoate fi aleasa de pe piata sistemelor de lucru cu baze de date.

Numai ın ultimii ani au aparut numeroase versiuni de Oracle:

• Oracle8 (1997)

• Oracle8i (1999) (i vine de la Internet)

• Oracle9i (2001)

• Oracle Database 10g (2003) (g vine de la grid computing)

• Oracle Database 11g (2007)

Inceputurile acestui SGBD provin din 1977 cand o companie Software Development La-boratories Relational Software a scos un produs numit Oracle V.2. Acesta a fost primulSGBD comercial din lume.

Exista mai multe versiuni de server:

• Oracle Express Edition - Este versiunea cea mai simpla si este disponibila fara costuri.Are anumite limitari: memorie de maxim 1GB iar pe disk poate ocupa maxim 4GB.Ii lipsesc facilitatile pentru lucrul cu Java si multe alte lucruri care se pot ıntalni doarın versiunile mai avansate.

• Oracle Personal Edition - Folosit de un singur programator pentru a crea aplicatiimultiutilizator. Ofera toate facilitatile pe care le are Enterprise Edition.

111

• Oracle Standard Edition One - Pentru aplicatii relativ mici. La fel ca varianta urmatoaredoar ca suporta maxim 2 procesoare si nu suporta RAC (Real Applications Clusters).

• Oracle Standard Edition - Pentru aplicatii mici si medii. Suporta atat RAC cat si panala 4 procesoare.

• Oracle Enterprise Edition - Varianta completa a serverului. Prezinta foarte multefacilitati care nu sunt tinta acestui curs.

Pentru a ıntelege mai bine ce ofera Oracle, prezentam urmatoarea lista de facilitati:

• Facilitati pentru dezvoltarea aplicatiilor cu baze de date

• Facilitati legate de conexiunea cu bazele de date

• Facilitati legate de baze de date distribuite

• Facilitati pentru mutarea datelor

• Facilitati legate de performanta

• Facilitati legate de managementul bazei de date

• Facilitati legate de securitate.

5.2 Arhitectura Oracle

In Oracle exista doua entitati care de multe ori sunt folosite pentru a reprezenta acelasilucru cu toate ca ele sunt diferite:

• instanta - se refera la software-ul care ruleaza pe server si face posibil accesul la bazade date. Instanta este logica; ea reprezinta structurile retinute ın memorie si proceselecare se executa pe server. O instanta poate fi pentru o singura baza de date, pe cando baza de date poate avea mai multe instante.

• baza de date - se refera la stocarea fizica a bazei de date. Accesul la o baza de date seface prin intermediul unei instante.

O baza de date Oracle contine numeroase lucruri: tablespaces, fisiere de control, de log,de log arhivate si alte tipuri de fisiere.

Tablespaces - Toate datele stocate ıntr-o baza de date Oracle sunt stocate ıntr-un tables-pace. El este o structura logica neavand echivalent pe disc. Este alcatuit din mai multefisiere de date. Atunci cand creem un tabel, specificam ın ce tablespace ıl creem si Oraclestie sa gaseasca un loc liber pentru el ın fisierele de date corespunzatoare.

O baza de date Oracle poate avea pana la 8 exabytes (un exabyte are 1 milion de terra-bytes).

Fisierele fizice care alcatuiesc o baza de date Oracle sunt de 3 tipuri:

112

• Fisiere de control - contin date despre locatia unde se gasesc celelalte doua tipuri defisiere, numele bazei de date, cand a fost creata, informatii despre tablespaces, backup-uri, log-uri arhivate, etc.

• Fisiere de date - contin datele propriu-zise, tabelele si indecsii, dictionarul de date.

• Fisiere redo de log - Oracle retine toate modificarile care s-au produs asupra bazei dedate ın aceste fisiere. Ele pot fi folosite pentru a reface anumite operatii ın cazul ıncare este nevoie de acest lucru. Ele sunt folosite de asemenea cand avem operatii detipul ROLLBACK.

5.3 Instalare Oracle

In continuare prezentam modul ın care vom lucra ın cele ce urmeaza cu Oracle. Trebuieprecizat ca vom folosi versiunea Express Edition.

In primul rand trebuie descarcat de pe Internet Oracle Express Edition. Pentru a puteaface acest lucru va trebui sa va ınregistrati pe site-ul oracle.com.

Dupa aceea instalam serverul de baze de date. Instalarea este una obisnuita, asa ca nuvom intra ın foarte multe amanunte.

In continuare putem lucra ın mai multe moduri:

• folosind aplicatia web implicita: se da clic pe Go to Database Homepage in sectiuneacorespunzatoare din All Programs sau se porneste un browser si se introduce adresa:http://localhost:8080/apex

• folosind SQLPlus : Tot din sectiunea corespunzatoare din All Programs se alege ”RunSQL Command Line”.

• folosind Oracle SQL Developer : acesta se poate downloada de pe site-ul Oracle, dupacare se instaleaza prin dezarhivare.

Observati faptul ca modul de lucru client/server este identic celui folosit ın cazul MySQL.

In continuare vom prezenta modul ın care se lucreaza folosind SQLPlus. In fereastra careapare tastam connect si dam Enter. Introducem numele utilizatorului si executam Enter,apoi parola si Enter.

Conectarea putea fi facuta si printr-o instructiune de genul:

connect mihai/parola@localhost

Pentru a verifica daca suntem conectati sau nu, putem efectua o interogare simpa:

SELECT SYSDATE FROM dual;

113

Sysdate este o functie predefinita, iar dual este o tabela predefinita, care are o singuraınregistrare si care este folosita ın astfel de situatii.

Cand am terminat cu o conexiune, putem sa ne deconectam folosind disconnect.In acest moment se pot executa diverse instructiuni, la fel ca ın cazul utilitarului mysql.Pentru a iesi din aplicatie putem scrie exit.Ca si in cazul MySQL se poate sa executam anumite fisiere cu comenzi SQL. Pentru

acest lucru, din SQLPlus executam:

@ script.sql

Se poate ıntampla sa trebuiasca sa dam toata calea catre fisierul care contine scriptulSQL.

114

Capitolul 6

Interogarea si actualizarea datelor

6.1 Generalitati

6.1.1 Tipuri de date ın Oracle

In Oracle exista trei tipuri de date:

• Tipuri de date caracter

• Tipuri de date numerice

• Tipuri de date pentru date calendaristice

• Alte tipuri de date

Tipuri de date caracter

Pentru lucrul cu caractere se pot folosi mai multe tipuri de date. Pentru acestea se potfolosi functiile pentru lucrul cu siruri de caractere. Totusi aceste functii nu se pot folosipentru string-uri foarte lungi.

Tipurile permise ın Oracle sunt:

• CHAR - se foloseste pentru a stoca siruri de caractere cu lungime fixa. Poate retinesiruri cu lungimea cuprinsa ıntre 1 si 2000. Daca sirul de caractere nu are lungimeaspecificata se completeaza cu spatii la sfarsit.

• VARCHAR2 - stocheaza siruri de caractere cu lungime variabila. Lungimea acestorapoate fi pana la maxim 4000 de caractere, ele nefiind completate cu spatii daca nu aulungimea maxima. Momentan, acest tip este sinonim cu VARCHAR, dar este posibilca ın viitor tipul VARCHAR sa fie folosit cu alta semnificatie.

• NCHAR si NVARCHAR sunt folosite pentru a putea avea coloane ın care sa retinem sisiruri de caractere care folosesc alte seturi de caractere decat cel principal. De exemplu,

115

ıntr-o baza de date care foloseste alfabetul romanesc, vrem ca ıntr-o coloana sa folosimun text ın limba chineza.

• LONG retine text de pana la 2 GB si provine din versiunile mai vechi de Oracle.Daca dorim sa retinem texte foarte lungi este recomandabil sa folosim mai degrabaCLOB sau NCLOB. Coloanele de acest tip nu pot fi folosite ın WHERE, GROUPBY, ORDER BY, CONNECT BY sau DISTINCT. Coloanele de acest tip nu pot fiindexate.

• CLOB si NCLOB - stocheaza pana la 4GB de caractere pana la versiunea 10g, darulterior pot stoca pana la 128TB.

Tipuri de date numerice

Principalul tip de date numeric ın Oracle este tipul NUMBER. El are doua componente:

NUMBER (precision, scale)

Precizia reprezinta numarul maxim de cifre care se poate folosi ın reprezentarea numarului.Implicit ea este 38.

Scala reprezinta numarul maxim de cifre pe care le putem avea dupa virgula. Daca nueste specificata nici o scala, automat se presupune ca este 0.

In ultimele versiuni de Oracle au mai aparut noi tipuri de date cum ar fi: SIMPLE INTEGER,BINARY FLOAT si BINARY DOUBLE.

Se mai pot folosi si tipurile ANSI, precum DECIMAL, INTEGER, INT, SMALLINT,FLOAT, DOUBLE PRECISION si REAL dar ele sunt reprezentate de Oracle tot ın contextullui NUMBER.

Tipul DATE

Pentru a retine date calendaristice Oracle foloseste urmatoarea reprezentare:

DD-MM-YY HH:MI:SS

La fel ca si ın cazul MySQL, ın ultimele versiuni de Oracle a fost adaugat tipul INTER-VAL.

Alte tipuri de date

In Oracle avem si tipuri de date care nu pot fi ıncadrate ın niciuna din situatiile prece-dente:

• RAW si LONG RAW - stocheaza obiecte care au propriul lor format cum ar fi bitma-purile. RAW merge pana la 2KB iar LONG RAW pana la 2GB.

• ROWID - este un tip special de coloana numit si pseudocoloana. El reprezinta adresafizica a unei ınregistrari.

116

• LOB - poate stoca pana la 4GB de date. Are mai multe varietati:

– CLOB - stocheaza numai caractere

– NCLOB - stocheaza caractere cu caracter national

– BLOB - stocheaza date ın format binar

Se poate ca un astfel de camp sa-si stocheze datele si ın fisiere externe bazei de date.

• XMLType - poate retine un document XML folosind de fapt un CLOB. Exista functiipredefinite care pot prelua valorile unor noduri etc.

• Tipuri de date utilizator - definite pe baza tipurilor de baza care exista ın Oracle.

6.1.2 Functii predefinite ın Oracle

In Oracle exista mai multe tipuri de functii:

• functii pentru lucrul cu siruri de caractere;

• functii matematice;

• functii pentru lucrul cu expresii regulate;

• functii pentru lucrul cu date si timp;

• alte functii.

Functii pentru lucrul cu siruri de caractere

Prezentam cateva din functiile pentru lucrul cu siruri de caractere:

• ASCII(x) - codul ASCII al caracterului x

• CHR(x) - returneaza caracterul asociat unui anumit cod ASCII

• CONCAT(x,y) - concateneaza doua siruri de caractere

• INITCAP(x) - prima litera e transformata ın litera mare iar restul litere mici, pentrufiecare cuvant ın parte

• LENGTH(x) - returneaza lungimea unui sir de caractere

• LOWER(x) - sirul numai cu litere mici

• NVL(x,value) - returneaza x daca nu este null, sau value daca x este null

• REPLACE(x,search string, replace string - ınlocuieste toate aparitiile unui sir decaractere cu altul

117

• SUBSTR(x,start[,length] - returneaza sirul care ıncepe la pozitia start ın sirul x,eventual cu length caractere

• UPPER(x) - sirul transformat ın litere mari

6.1.3 Functii numerice

Prezentam ın continuare doar o parte din functiile numerice fara a le detalia foartetare: ABS(x), ACOS(x), ASIN(x), ATAN(x), BITAND(x,y), COS(x), COSH(x), CEIL(x),EXP(x), FLOOR(x), LOG(x,y), LN(x), MOD(x,y), POWER(x,y), ROUND(x[,y]), SIN(x),SIGN(x), SQRT(x), TAN(x), TRUNC(x[,y]).

6.1.4 Functii pentru lucrul cu date si timp

Implicit, o data ın Oracle se retine ın formatul: ’11-JAN-2009’, dar se poate folosi sicuvantul cheie DATE pentru a introduce date ın formatul clasic (YYYY-MM-DD).

Iata doua exemple:


(2, ’Bocu’,’Dorin’,’profesor’,’m’,’03-AUG-1955’);

sau


(2, ’Bocu’,’Dorin’,’profesor’,’m’,DATE ’1955-08-03’);

Pentru lucrul cu date exista doua functii foarte importante TO CHAR si TO DATE.Exemple de folosire ale lui TO CHAR:

TO_CHAR(datan, ’MONTH DD,YYYY’)

TO_CHAR(datan, ’MONTH DD,YYYY, HH24:MI:SS’)

TO_CHAR(SYSDATE, ’DD-MM-YYYY’)

TO_CHAR(SYSDATE, ’DD/MM/YYYY’)

In mod asemanator, folosind TO DATE si specificand un format se poate obtine un obiectde tipul DATE:

TO_DATE(’04-JUL-2007’)

TO_DATE(’July 4, 2007’, ’MONTH DD, YYYY’)

TO_DATE(’7.4.07’, ’MM.DD.YY’)

Formatul implicit poate fi schimbat pentru sesiunea curenta folosind urmatoarea instrutiuneSQL:

ALTER SESSION SET NLS_DATE_FORMAT = ’MONTH-DD-YYYY’;

Un administrator poate face acelasi lucru pentru server, cu urmatoarea instructiune:

ALTER SYSTEM SET NLS_DATE_FORMAT = ’MONTH-DD-YYYY’;

118

Evident, formatul poate fi ales ın mod asemanator cu formatele prezentate anterior.Exista mai multe functii pentru lucrul cu date si timp, precum:

• ADD MONTHS(x,y) - adauga la data x, y luni

• LAST DAY(x) - ultima zi a lunii din data

• MONTHS BETWEEN(x,y) - numarul de luni ıntre doua date

• SYSDATE - returneaza data si timpul curente

• si altele

Pentru lucrul cu date exista ın Oracle si tipul TIMESTAMP. El prezinta doua avantaje:

• pentru secunde poate retine si parti fractionare;

• poate retine si informatii despre zona de timp.

In plus fata de functiile prezentate mai exista functii pentru conversii precum si pentrulucrul cu expresii regulate. Pentru mai multe detalii, se recomanda consultarea diverselordocumentatii Oracle.

6.2 Interogarea SELECT

6.2.1 Crearea bazei de date exemplu

In interogarile care vor fi prezentate ın continuare, vom folosi aceeasi baza de date ca sicea folosita pentru a ilustra modul de lucru cu MySQL.

Vom folosi un fisier script.sql pentru a retine tot ceea ce este necesar. Acesta arataastfel:

drop table tipuri_curs;

create table tipuri_curs(

tip_curs integer constraint tipuri_curs_pk primary key,

den_tip varchar2(30) not null

);

insert into tipuri_curs values

(1,’obligatoriu’);


(2,’optional’);


(3,’facultativ’);

119

drop table profesori;


cod_prof integer constraint profesori_pk primary key ,

nume varchar2(30) not null,

prenume varchar2(30) not null,

grad varchar2(30) not null,

sex char(1) not null,

datan date

);


(1, ’Iolu’,’Mihai’,’lector’,’m’,’03-05-1979’);


(2, ’Bocu’,’Dorin’,’profesor’,’m’,’03-08-1955’);


(3, ’Ciurea’,’Eleonor’,’profesor’,’m’,’13-07-1943’);


(4, ’Sasu’,’Lucian’,’lector’,’m’,’02-08-1977’);


(5, ’Deaconu’,’Adrian’,’conferentiar’,’m’,’13-03-1974’);


(6, ’Ciupala’, ’Laura’, ’conferentiar’, ’f’, null);

drop table studenti;


nrmatricol integer constraint studenti_pk primary key,



CNP varchar2(13) not null,



oras varchar2(30) not null,

judet varchar2(30) not null,

tara varchar2(30) not null

);


(101, ’Florescu’,’Radu’,’1870409030182’,’m’,’09-07-1987’,

’Bucuresti’,’Bucuresti’,’Romania’);

120


(102, ’Ceausescu’,’Ionut’,’1880323020382’,’m’,’23-03-1988’,

’Brasov’,’Brasov’,’Romania’);


(103, ’Ciumarnean’,’Oana’,’2880520435434’,’f’,’20-05-1988’,



(104, ’Neagu’,’Andrei’,’1871115456537’,’m’,’15-11-1987’,

’Sinaia’,’Prahova’,’Romania’);


(105, ’Saracu’,’Ana-Maria’,’1880308345322’,’f’,’08-03-1988’,

’Fagaras’,’Brasov’,’Romania’);


(106, ’Ionita’,’Emanuel-Ionut’,’1881023345433’,’m’,’23-10-1988’,

’Victoria’,’Brasov’,’Romania’);


(107, ’Dumitrescu’,’Teodora’,’2881220030325’,’f’,’20-12-1988’,

’Cluj-Napoca’,’Cluj’,’Romania’);


(108, ’Radoi’,’Ovidiu’,’1871005325323’,’m’,’05-10-1987’,

’Munchen’,’Bavaria’,’Germania’);


(109, ’Tanase’,’Alexandra’,’2880301345436’,’f’,’01-03-1988’,



(110, ’Mocanu’,’Laura’,’2880402342344’,’f’,’02-04-1987’,


drop table cursuri;

create table cursuri(

cod_curs integer constraint cursuri_pk primary key,

den_curs varchar2(30) not null,

tip_curs integer not null,

credite integer,

cod_profesor integer not null);


(1, ’Design patterns’,2, 6, 1);


(2, ’SGBD (MySQL, Oracle)’,1, 5, 1);


121

(3, ’Inginerie software’,1, 6, 2);


(4, ’POO 2’,1, 6, 2);


(5, ’Algoritmica’,1, 6, 3);


(6, ’Algoritmica grafurilor’,1, 4, 3);


(7, ’Programare C#’,1, 5, 4);


(8, ’Inteligenta artificiala’,1, 4, 4);


(9, ’Programare procedurala’,1, 6, 5);


(10, ’Optimizare combinatorie’,1, 3, 6);

drop table inrolari;


cod_inrolare integer constraint inrolari_pk primary key,

nrmatricol integer not null,

cod_curs integer not null,



(1, 101, 7, ’03-09-2007’);


(2, 101, 1, ’09-09-2007’);


(3, 102, 8, ’14-09-2007’);


(4, 102, 2, ’03-09-2007’);


(5, 102, 9, ’08-09-2007’);


(6, 103, 3, ’14-09-2007’);


(7, 104, 8, ’07-09-2007’);


(8, 105, 2, ’09-09-2007’);


(9, 107, 1, ’03-09-2007’);

122


(10, 107, 3, ’12-09-2007’);


(11, 108, 2, ’08-09-2007’);


(12, 108, 9, ’08-09-2007’);


(13, 109, 8, ’13-09-2007’);


(14, 110, 7, ’14-09-2007’);


(15, 110, 4, ’14-09-2007’);

drop table note;

create table note(

cod_nota integer constraint note_pk primary key,

cod_inrolare integer not null,

nota integer not null,

data date not null);


(1, 1, 8, ’10-01-2008’);


(2, 1, 10, ’20-01-2008’);


(3, 2, 3, ’12-01-2008’);


(4, 3, 6, ’14-01-2008’);


(5, 4, 9, ’16-01-2008’);


(6, 6, 7, ’12-01-2008’);


(7, 7, 4, ’14-01-2008’);


(8, 7, 8, ’12-01-2008’);


(9, 8, 9, ’10-01-2008’);


(10, 10, 3, ’14-01-2008’);


123

(11, 10, 5, ’16-01-2008’);


(12, 12, 7, ’13-01-2008’);


(13, 13, 4, ’08-01-2008’);


(14, 14, 3, ’10-01-2008’);


(15, 14, 4, ’20-01-2008’);

Vom executa acest script dupa ce ne vom conecta la serverul de baze de date folosincontul nostru.

Rularea scriptului se va face prin comanda:@ cale\script.sql

6.2.2 Exemple de interogari

In principiu, multe din lucrurile care mergeau ın MySQL raman valabile si ın cazulOracle. Acestea fiind spuse, prezentam mai multe exemple care vor fi comentate suplimentarın masura ın care este nevoie.

• Sa se afiseze pentru toti studentii, numele si prenumele lor:

SELECT ROWNUM, ROWID, nume, prenume

FROM studenti;

ROWNUM returneaza numarul ınregistrarii, iar ROWID ne arata un identificator unical oricarei ınregistrari din baza de date.

• Sa se calculeze 23*12.

SELECT 23*12

FROM dual;

Pentru a putea face un calcul si a afisa rezultatul, trebuie sa folosim clauza FROM.Special pentru aceste situatii este creata tabela dual.

• Sa se afiseze data curenta si utilizatorul curent.

SELECT SYSDATE, USER

FROM dual;

• Sa se afiseze data obtinuta prin adunarea a 2 zile la data curenta.

124

SELECT SYSDATE+2 FROM dual;

• Sa se afiseze cate zile mai sunt pana la 1 iunie 2010:

SELECT TO_DATE(’01-06-2010’)-SYSDATE

FROM dual;

• Sa se afiseze numele si prenumele studentilor concatenate.

SELECT nume || ’ ’ || prenume AS "Nume student"

FROM studenti;

Se foloseste || pentru a concatena valori.

• Sa se afiseze toate datele despre profesorii care au data nasterii cu valoarea NULL.

SELECT * FROM profesori

WHERE datan IS NULL;

• Si ın Oracle avem cam aceeasi comparatori ca si ın cazul MySQL: =, <, >, <>, !=,<=, >=. La fel ca ın cazul MySQL putem folosi cuvintele cheie ANY, SOME si ALLcu exact aceeasi semnificatie. Si ın Oracle avem operatorii SQL LIKE, IN, BETWEENsi IS NULL.

• Sa se afiseze toti studentii care s-au inscris la un curs si cursul la care s-au inscris:

SELECT nume, prenume, cod_curs

FROM studenti s, inrolari i

WHERE s.nrmatricol=i.nrmatricol;

• Aceeasi interogare ca precedenta doar ca vrem sa vedem si studentii care nu s-au inscrisla nici un curs:

SELECT nume, prenume, cod_curs

FROM studenti s, inrolari i

WHERE s.nrmatricol=i.nrmatricol(+);

De remarcat caracterul + ıntre paranteze, trecut de partea ın care avem entitatea carenu poate fi NULL (am reprezentat un LEFT JOIN). Analog, puteam avea un RIGHTJOIN daca aveam + ın partea stanga. Nu se poate cu aceasta notatie sa avem FULLJOIN.

125

• Interogarile precedente respecta sintaxa ANSI SQL/86. Se poate folosi ın Oracle sisintaxa SQL/92, care foloseste INNER JOIN, LEFT JOIN, RIGHT JOIN si FULLJOIN. Nu mai prezentam exemple deoarece modul de lucru este acelasi ca si ın cazulMySQL.

• Sa se afiseze studentul care are un numar matricol citit de la tastatura:

select nume, prenume

from studenti

where nrmatricol=&nrmat;

• Se pot defini noi variabile cu DEFINE:

DEFINE nrmat=106;

• Se pot vedea toate variabilele definite cu DEFINE:

DEFINE

O variabila poate fi resetata folosind UNDEFINE:

UNDEFINE nrmat;

• Se pot face citiri, folosind ACCEPT, care are urmatoarea forma:ACCEPT variable name [type] [FORMAT format] [PROMPT prompt] [HIDE]Iata si niste exemple:

ACCEPT nrmat NUMBER FORMAT 999 PROMPT ’Nr matricol:’

ACCEPT data DATE FORMAT ’DD-MON-YYYY’ PROMPT ’Date:’

ACCEPT parola CHAR PROMPT ’Parola:’ HIDE

• Un script poate primi anumiti parametri. Iata un exemplu:

SELECT *

FROM studenti

WHERE nrmatricol=&1;

Acest script ar putea fi apelat folosind urmatoarea instructiune:

@script.sql 105

• Si ın Oracle, la fel ca ın MySQL se pot face subinterogari folosind IN si EXISTS. Deasemenea, puteam avea subinterogari corelate si putem folosi UNION, UNION ALL,INTERSECT si MINUS.

126

• Sa se afiseze pentru fiecare student daca e localnic sau nu.

SELECT nume, prenume,CASE oras

WHEN ’Brasov’ THEN ’localnic’

ELSE ’din alta parte’

END

FROM studenti;

• Sa se afiseze numele, prenumele si sexul pentru fiecare persoana:

SELECT nume, prenume, CASE

WHEN sex=’m’ THEN ’masculin’

WHEN sex=’f’ THEN ’feminin’

END

FROM studenti;

• Sa se afiseze pentru fiecare oras, cate persoane sunt din acel oras, precum si pe ce locs-ar clasa orasul din acest punct de vedere:

SELECT oras, COUNT(*),

RANK() OVER (ORDER BY COUNT(*) DESC NULLS LAST) AS rank,

DENSE_RANK() OVER (ORDER BY COUNT(*) DESC NULLS LAST) AS dense_rank

FROM studenti

GROUP BY oras

ORDER BY COUNT(*) DESC;

Interogari pe tabele ierarhice

Dupa cum am discutat si ın cazul MySQL o situatie mai deosebita o reprezinta modul ıncare se poate lucra cu tabele ierarhice. In cazul MySQL trebuia sa facem o mica improvizatiepentru a putea lucra cu ele.

Lucrurile sunt mult mai simple ın cazul Oracle, deoarece acesta are clauzele CONNECTBY si START WITH care ne ajuta ın astfel de situatii.

Sintaxa acestei interogari este urmatoarea:

SELECT [LEVEL], column, expression,...

FROM table

[WHERE where_clause]

[[START WITH start_condition][CONNECT BY PRIOR prior_condition]];

Prezentam ın continuare modul ın care se poate lucra cu acestea:

127

/* creare tabel */

create table categorii(

categ_id integer not null constraint categ_pk primary key,

den_categ varchar2(20) not null,

parent_id integer);

/* inserare informatii */

insert into categorii values(1,’categ1’,null);

insert into categorii values(2,’categ11’,1);







/* toate categoriile */

select * from categorii;

/* parcurgere in adancime */

select categ_id, parent_id, den_categ

from categorii

start with categ_id=1

connect by prior categ_id=parent_id;

/* parcurgere in latime */

select categ_id, parent_id, den_categ, LEVEL

from categorii


connect by prior categ_id=parent_id

order by level asc;

/* numarul de nivele incepand de la angajatul cu id-ul 1 */

select count(distinct level)

from categorii


connect by prior categ_id= parent_id;

/* Formatarea afisarii */

select categ_id, level, lpad(’ ’,3*level-1) || den_categ

128

as "Categorie"

from categorii


connect by prior categ_id= parent_id;

/* afisarea tuturor parintilor unei categorii */

select level, den_categ

from categorii


connect by prior parent_id= categ_id;

/* afisarea tuturor inregistrarilor formatat pe nivele,

fara nodul 2 si descendentii lui */

select categ_id, level, lpad(’ ’,3*level-1) || den_categ

as "Categorie"

from categorii


connect by prior categ_id= parent_id and categ_id!=2;

129

130

Capitolul 7

Crearea obiectelor bazei de date

7.1 Lucrul cu tabele

O tabela se poate crea folosind instructiunea CREATE TABLE care are forma simplifi-cata urmatoare:

CREATE [GLOBAL TEMPORARY] TABLE table_name (

column_name type [CONSTRAINT constraint_def DEFAULT default_exp]

[, column_name type [CONSTRAINT constraint_def DEFAULT default_exp] ...]

)

[ON COMMIT DELETE | PRESERVE ROWS]

TABLESPACE tab_space;

Daca se foloseste GLOBAL TEMPORARY acest lucru semnifica faptul ca se lucreaza cu otabela temporara. Ea va fi pastrata pana la sfarsitul tranzactiei daca este ales ON COMMITDELETE ROWS si pana la sfarsitul sesiunii daca se alege ON COMMIT PRESERVE ROWS.

Nu insistam mai mult asupra celorlalate componente pentru ca ele sunt exact ceea cene-am astepta din experienta folosind MySQL.

Pentru a putea afla informatii despre tabele putem efectua urmatoarele instructiuni:

/* vizualizarea informatiilor despre tabela studenti */

DESCRIBE studenti;

/* vizualizarea tuturor tabelelor utilizatorului curent */

SELECT table_name, tablespace_name

FROM user_tables;

Asemanator cu modul ın care se poate modifica un tabel ın MySQL se poate face acelasilucru ın Oracle folosind instructiunea ALTER TABLE ca ın exemplele urmatoare:


ADD created DATE DEFAULT SYSDATE NOT NULL;

131

De asemenea, pentru tabele se pot stabili diferite constrangeri ca ın exemplele urmatoare:

/* constrangere dupa creare tabel */


ADD CONSTRAINT sex_ck

CHECK (sex IN (’f’,’m’));

/* constrangere la creare tabel */


cod_prof integer constraint profesori_pk primary key ,



grad varchar2(30) not null,

sex char(1) not null check (sex in (’f’,’m’)),

datan date

);

/* specificare cheie straina la creare tabel */


cod_inrolare integer constraint inrolari_pk primary key,

nrmatricol integer not null

constraint nrmatricol_fk

references studenti(nrmatricol)

on delete cascade,

cod_curs integer not null,


Atunci cand se specifica o cheie straina, ın cazul ın care se sterge o ınregistrare din tabelastudenti, se poate ıntampla unul din urmatoarele lucruri:

• daca nu se specifica nimic altceva ınregistrarea nu va putea fi stearsa

• daca se specifica ON DELETE CASCADE sunt sterse automat ınregistrarile cores-punzatoare din tabela inrolari

• daca se specifica ON DELETE SET NULL automat unde apare cheia studentului sters,aceasta este ınlocuita cu NULL.

Si specificarea faptului ca un anumit camp nu poate avea valoarea NULL este de asemeneao constrangere si ar putea primi si ea un nume la fel ca orice constrangere.

Observatie: O tabela nu poate fi stearsa daca are o cheie primara care este legata de ocheie straina printr-o constrangere ???

Pentru o tabela se mai pot face mai multe operatii, precum:

/* redenumirea tabelei */

132

RENAME studenti TO students;

/* stergerea tuturor inregistrarilor si

resetarea zonei de memorie */

TRUNCATE TABLE studenti;

/* stergerea unei tabele */

DROP TABLE studenti;

7.2 Lucrul cu secvente

Crearea unei secvente se poate face folosind CREATE SEQUENCE:

CREATE SEQUENCE sequence_name

[START WITH start_num]

[INCREMENT BY increment_num]

[ MAXVALUE maximum_num | NOMAXVALUE ]

[ MINVALUE minimum_num | NOMINVALUE ]

[ CYCLE | NOCYCLE ]

[ CACHE cache_num | NOCACHE ]

[ ORDER | NOORDER ];

Semnificatiile unora din aceste valori sunt:

• CYCLE - dupa ce se ajunge la valoarea maxima se reıncepe cu valoarea minima;

• NOCACHE - nu se prealoca numere de catre BD, astfel nu vom avea numere nefolositecand se opreste baza de date, dar este mai neeficient;

• start num si increment num au implicit valoarea 1.

Iata si niste exemple de creare de secvente si folosire a lor:

CREATE SEQUENCE s_studenti;

CREATE SEQUENCE s_test

START WITH 10 INCREMENT BY 5

MINVALUE 10 MAXVALUE 1000

CYCLE CACHE;

/* obtinerea urmatoarei valori a secventei */

SELECT s_test.NEXTVAL

FROM dual;

/* se poate popula o inregistrare si

133

folosind secvente in mod direct */

INSERT INTO tipuri_curs (tip_curs, den_tip)

VALUES(s_test.NEXTVAL, ’la alegere’);

/* stergerea unei secvente */

DROP SEQUENCE s_test;

7.3 Lucrul cu indecsi

Este bine sa se creeze indesi pentru acele coloane care au valori de preferinta unice si ınplus care au valori cat mai variate daca nu unice.

Este bine sa se foloseasca indecsii pentru acele interogari ın care numarul de ınregistrarireturnate este mai mic decat 10% din numarul de ınregistrari din tabela.

Un index se poate crea folosind o instructiune de forma urmatoare:

CREATE [UNIQUE] INDEX index_name ON

table_name(column_name[, column_name ...])

TABLESPACE tab_space;

Un exemplu de creare si de stergere a unui index este urmatorul:

CREATE UNIQUE INDEX i_cnp_studenti ON

studenti(cnp);

DROP INDEX i_cnp_studenti;

Se poate ıntampla sa avem indecsi care sa functioneze pe baza unor functii:

CREATE INDEX i_nume_studenti

ON studenti(UPPER(nume));

/* pentru a putea fi folosit, trebuie ca administratorul

sa faca urmatoarea operatie */

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

Putem vedea indecsii corespunzatori unei tabele:

SELECT index_name, table_name, uniqueness, status

FROM user_indexes

WHERE table_name=’studenti’

ORDER BY index_name;

Pentru tabelele ın care avem o coloana care are putine valori si ın acelasi timp au foartemulte ınregistrari se pot folosi bitmap indexes.

Un astfel de index poate fi creat astfel:

CREATE BITMAP INDEX i_sex_studenti ON studenti(sex);

134

7.4 Lucrul cu view-uri

View-urile sunt niste interogari care sunt deja salvate. Datele dintr-un view pot fi pot fiaccesate la fel ca dintr-un tabel. In unele view-uri se pot face si operatii INSERT, UPDATEsi DELETE.

Un view se poate crea printr-o instructiune precum urmatoarea:

CREATE [OR REPLACE] VIEW [FORCE | NOFORCE] VIEW view_name

[(alias_name[, alias_name ...])] AS subquery

[WITH CHECK OPTION | READ ONLY CONSTRAINT constraint_name];

Semnificatiile catorva din optiuni sunt:

• FORCE - view-ul va fi creat chiar daca tabelele de baza specificate nu exista;

• subquery - interogarea care aduce date din tabele sau alte view-uri;

• WITH CHECK OPTION - numai randurile care au fost aduse de interogare pot fi inserate,actualizate si sterse; implicit nu este activata;

• WITH READ ONLY - datele aduse pot fi doar citite nu si modificate.

Avem doua tipuri de view-uri:

• View-uri simple (pe baza unei interogari care foloseste o singura tabela de baza)

• View-uri complexe (pe baza mai multor tabele, sau folosind GROUP BY si/ sau DIS-TINCT sau care contin apeluri de funtii.

Un view se construieste foarte simplu ca ın exemplele urmatoare:

CREATE VIEW studenti_brasov_view AS

SELECT *

FROM studenti



SELECT *

FROM studenti


WITH CHECK OPTION CONSTRAINT brasov_students;

/* un view in care nu se pot modifica date */


SELECT *

FROM studenti


WITH READ ONLY CONSTRAINT brasov_students;

135

Se pot face insert-uri, update-uri si delete-uri numai pentru view-uri simple.Modificarea unui view se poate face folosind CREATE OR REPLACE VIEW ca ın

exemplul urmator:

CREATE OR REPLACE VIEW studenti_brasov_view AS

SELECT *

FROM studenti


Un view poate fi sters folosind DROP VIEW:

DROP VIEW studenti_brasov_view;

7.5 Utilizatori si securitate

Pentru a crea un utilizator se poate executa urmatoarea instructiune SQL:

CREATE USER user_name IDENTIFIED BY password

[DEFAULT TABLESPACE def_tablespace]

[TEMPORARY TABLESPACE temp_tablespace];

Pentru a putea schimba parola unui utilizator:

ALTER USER user_name IDENTIFIED BY password;

De asemenea, parola unui utilizator poate fi schimbata ın SQL*Plus, folosind comandaPASSWORD.

Stergerea unui utilizator se poate face cu comanda:

DROP USER user_name;

In Oracle se pot acorda doua tipuri de drepturi:

• roluri - precum CONNECT si RESOURCE (ele se pot da mai departe daca au fostacordate cu WITH ADMIN OPTION)

• privilegii - precum CREATE TABLE, SELECT, INSERT, etc. (ele pot fi date maideparte daca au fost acordate cu WITH GRANT OPTION).

Un utilizator poate sa faca mai multe interogari referitoare la privilegiile sale:

/* vizualizarea tuturor privilegiilor sistem */

SELECT *

FROM user_sys_privs

ORDER BY privilege;

/* vizualizarea privilegiilor utilizatorului

136

asupra tabelei nume_tabela */

SELECT *

FROM user_tab_privs_made

WHERE table_name=’nume_tabela’;

/* vizualizarea rolurilor unui utilizator */

SELECT *

FROM user_role_privs;

Acordarea de drepturi se poate face la fel ca si ın cazul MySQL cu ajutorul comenziiGRANT. Revocarea drepturilor se face cu REVOKE ıntr-un mod asemanator.

In Oracle se pot crea roluri. Acestor roluri li se pot acorda diverse drepturi. Ele reprezintaun mod de a partaja comportament comun ıntre utilizatori, fiind mai usor sa se acorde nistedrepturi unui rol, si apoi sa se acorde acel rol mai multor utilizatori, decat sa se acordedrepturile fiecarui utilizator ın parte.

O secventa care ilustreaza aceste lucruri este urmatoarea (mihai este un utilizator caredeja exista):

CREATE ROLE manager;

GRANT SELECT, INSERT, UPDATE, DELETE ON baza.* TO manager;

GRANT CREATE USER TO user;

GRANT manager TO mihai;

137

138

Capitolul 8

Crearea obiectelor procedurale

8.1 Programarea PL/SQL

Denumirea de PL/SQL vine de la Programming Language/SQL. Acest limbaj a fost adaugatodata cu versiunea 6 a Oracle si permite scrierea de programe folosind SQL si accesarea lor,atat din aplicatiile client specializate pentru lucrul cu Oracle cat si din programe Java sauC#.

Programele PL/SQL sunt organizate pe blocuri care au urmatoarea structura:

[DECLARE

declaration_statements

]

BEGIN

executable_statements

[EXCEPTION

exception_handling_statements

]

END;

/

Un astfel de exemplu de script care poate fi rulat si din SQL Developer cu F5 sau Runscript este:

SET SERVEROUTPUT ON

DECLARE

a INTEGER :=5;

b INTEGER :=2;

c INTEGER;

BEGIN

c:=a*b;

DBMS_OUTPUT.PUT_LINE(’Rezultat = ’ || c);

EXCEPTION

139

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE(’Division by zero’);

END;

/

Acest script ar putea fi rulat si din SQL*Plus, folosind operatorul ”@”.

8.1.1 Structuri conditionale si repetitive

Pentru lucrul ın aceste programe PL/SQL, se pot folosi urmatoarele structuri conditionalesi repetitive:

IF condition1 THEN

statements1

ELSIF condition2 THEN

statements2

ELSE

statements3

END IF;

----------

LOOP

statements

EXIT WHEN cond;

END LOOP;

/*

se pot folosi

CONTINUE WHEN cond;

sau

IF cond THEN

CONTINUE;

END IF;

*/

----------

WHILE condition LOOP

statements

END LOOP;

----------

FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP

statements

END LOOP;

140

8.1.2 Cursoare

La fel ca ın cazul MySQL, si ın Oracle se pot folosi cursoare. Iata un exemplu (trebuieprecizat ca modul de lucru este foarte asemanator):

SET SERVEROUTPUT ON

DECLARE

-- declarare variabile

v_nrmatricol studenti.nrmatricol% TYPE;

v_nume studenti.nume%TYPE;

v_prenume studenti.prenume%TYPE;

-- declarare cursor

CURSOR v_studenti_cursor IS


FROM studenti


BEGIN

-- deschidere cursor

OPEN v_studenti_cursor;

LOOP

-- obtinerea de inregistrari cu ajutorul cursorului

FETCH v_studenti_cursor

INTO v_nrmatricol, v_nume, v_prenume;

EXIT WHEN v_studenti_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(

’v_nrmatricol = ’ || v_nrmatricol || ’, v_nume = ’ || v_nume ||

’, v_prenume = ’ || v_prenume

);

END LOOP;

-- inchiderea cursorului

CLOSE v_studenti_cursor;

END;

/

Acelasi lucru se poate face si folosind un cursor precum si structura repetitiva FOR:

SET SERVEROUTPUT ON

DECLARE

CURSOR v_studenti_cursor IS


FROM studenti


BEGIN

FOR v_student IN v_studenti_cursor LOOP

141

DBMS_OUTPUT.PUT_LINE(

’nrmatricol = ’ || v_student.nrmatricol ||

’, nume = ’ || v_student.nume ||

’, prenume = ’ || v_student.prenume

);

END LOOP;

END;

/

Dupa cum am putut vedea ın exemplele precedente se poate ıntampla sa apara exceptiiın momentul ın care se ruleaza un program. In Oracle exista numeroase exceptii precum: AC-CESS INTO NULL, CASE NOT FOUND, COLLECTION IS NULL, CURSOR ALREADY OPEN,DUP VAL ON INDEX, INVALID CURSOR, INVALID NUMBER, LOGIN DENIED, NO DATA FOUND,NOT LOGGED ON, PROGRAM ERROR, ROWTYPE MISMATCH, SELF IS NULL, STO-RAGE ERROR, SUBSCRIPT BEYOND COUNT, TIMEOUT ON RESOURCE, TOO MANY ROWS,VALUE ERROR, ZERO DIVIDE si OTHERS (pentru a prinde toate exceptiile posibile).

8.2 Proceduri stocate

O procedura stocata contine interogari SQL si comenzi PL/SQL. Ele permit accesulcentralizat la server si partajarea de comportament ıntre diverse aplicatii.

O procedura stocata se poate crea conform urmatoarei sintaxe:

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

IS | AS

BEGIN

procedure_body

END procedure_name;

Iata, ın continuare o procedura stocata:

CREATE OR REPLACE PROCEDURE test(a in integer, b out integer) AS

BEGIN

b:=a+1;

END test;

/

Ea poate fi apelata din linie de comanda astfel:

variable x number;

call test(3,:x);

print :x;

Un exemplu mai complex este urmatorul:

142

CREATE PROCEDURE update_studenti_nume(

nrmat IN studenti.nrmat%TYPE,

localitate IN studenti.localitate%TYPE

) AS

studenti_count INTEGER;

BEGIN

SELECT COUNT(*)

INTO studenti_count

FROM studenti

WHERE nrmatricol = nrmat;

IF studenti_count = 1 THEN

UPDATE studenti

SET oras=localitate


COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END update_studenti_nume;

/

Se poate sterge o procedura folosind instrutiunea DROP PROCEDURE:

DROP PROCEDURE test;

Daca ın momentul ın care se compileaza o procedura stocata se ıntampla sa apara erori,acestea pot fi vazute folosind instructiunea SHOW ERRORS.

8.3 Lucrul cu functii

O funtie este foarte asemanatoare uneo proceduri stocate, cu exceptia faptului ca eareturneaza un rezultat.

O sintaxa simpla pentru crearea unei functii este urmatoarea:

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

RETURN type

IS | AS

BEGIN

function_body

END function_name;

143

Prezentam, ın continuare o functie care calculeaza cel mai mare divizor comun a douanumere, transmise ca parametri:

CREATE OR REPLACE FUNCTION cmmdc

(a IN NUMBER, b IN NUMBER)

RETURN NUMBER AS

x INTEGER := a;

y INTEGER := b;

r INTEGER;

BEGIN

WHILE y<>0 LOOP

r:=MOD(x,y);

x:=y;

y:=r;

END LOOP;

RETURN x;

END cmmdc;

/

Functia ar putea fi apelata cu o instructiune de genul:

SELECT cmmdc(27,18)

FROM dual;

Evident, se pot construi functii mult mai complexe si care sa contina instructiuni SQL.O funtie poate fi stearsa cu instructiunea DROP FUNCTION:

DROP FUNCTION cmmdc;

8.4 Lucrul cu pachete

Procedurile si functiile se pot grupa ın pachete. In acest fel ne putem crea propriilelibrarii pe care alti utilizatori le pot reutiliza profitand de avantajul modularizarii.

Un astfel de pachet are o parte de specificatie si o parte ın care sunt descrise functii,proceduri, tipuri noi de date si obiecte. In partea de specificatie sunt descrise acele obiectecare dorim sa fie publice. Cele care nu apar ın partea de specificatie sunt sunt considerateprivate.

Se poate crea un package folosind urmatoarea instructiune simplificata:

CREATE [OR REPLACE] PACKAGE package_name

IS | AS

package_specification

END package_name;

144

Un exemplu de creare a unui package este urmatorul (dupa cum se poate vedea se scriedoar specificarea pachetului):

CREATE PACKAGE student_package AS

TYPE t_ref_cursor IS REF CURSOR;

FUNCTION get_studenti_ref_cursor RETURN t_ref_cursor;

PROCEDURE update_studenti_nume(



);

END product_package;

/

Pentru a seta corpul pachetului se foloseste o instructiune precum urmatoarea:

CREATE [OR REPLACE] PACKAGE BODY package_name

IS | AS

package_body

END package_name;

Iata si un exemplu pentru pachetul definit anterior:

CREATE PACKAGE BODY student_package AS

FUNCTION get_studenti_ref_cursor

RETURN t_ref_cursor IS

v_students_ref_cursor t_ref_cursor;

BEGIN

OPEN v_students_ref_cursor FOR


FROM studenti;

RETURN v_students_ref_cursor;

END get_studenti_ref_cursor;

PROCEDURE update_studenti_nume(



) AS

studenti_count INTEGER;

BEGIN

SELECT COUNT(*)

INTO studenti_count

FROM studenti

145

WHERE nrmatricol = nrmat;

IF studenti_count = 1 THEN

UPDATE studenti

SET oras=localitate


COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END update_studenti_nume;

END student_package;

/

Pentru a apela procedurile si functiile dintr-un pachet se va folosi aceeasi sintaxa ca si ıncazul procedurilor si funtiilor normale, doar ca ın denumirea functiei sau procedurii trebuieadaugata denumirea pachetului urmata de caracterul ”.”.

EXEMPLU!!!!Pentru a vedea toate metodele si functiile dint-un pachet se poate scrie urmatoarea

instructiune SQL:

SELECT object_name, procedure_name

FROM user_procedures

WHERE object_name = ’nume_pachet’;

Un pachet poate fi sters folosind instructiunea DROP PACKAGE:

DROP PACKAGE nume_package;

8.5 Lucrul cu triggere

Un trigger este o procedura care se produce automat atunci cand asupra unei anumitetabele se produce o instructiune de genul INSERT, UPDATE sau DELETE.

Trigger-ul se poate produce ınainte sau dupa instructiunea care se produce si se poateexecuta cate o data pentru fiecare ınregistrare afectata sau o data pentru toate (daca suntafectate mai multe ınregistrari).

Un trigger la nivel de rand poate accesa vechile si noile valori din anumite campuri, ıntimp ce un trigger pentru mai multe ınregistrari nu are acest drept. De asemenea, ın cazulın care avem un trigger la nivel de ınregistrare se poate sa avem o conditie care sa limitezeaparitia triggerului.

Se poate crea un trigger cu urmatoarea sintaxa:

146

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF | FOR} trigger_event

ON table_name

[FOR EACH ROW]

[{FORWARD | REVERSE} CROSSEDITION]

[{FOLLOWS | PRECEDES} schema.other_trigger}[{ENABLE | DISABLE}][WHEN trigger_condition]]

BEGIN

trigger_body

END trigger_name;

Semnificatiile cuvintelor cheie mai deosebite sunt:

• INSTEAD OF - triggerul se va produce ın locul evenimentului urmarit

• FOR EACH ROW - ınseamna ca trigger-ul va fi la nivel de ınregistrare, apelandu-se pentrufiecare ınregistrare afectata de operatia urmarita

• {ENABLE | DISABLE} - specifica daca triggerul este activat sau nu

• WHEN trigger condition - triggerul se apeleaza numai cand este ındeplinita conditia.

Un exemplu de trigger este prezentat ın continuare:

CREATE TRIGGER before_studenti_localitate_update

BEFORE UPDATE OF localitate

ON studenti

FOR EACH ROW WHEN (new.localitate =’brasov’)

BEGIN

dbms_output.put_line(’nr_mat = ’ || :old.nrmatricol);

dbms_output.put_line(’Vechea localitate = ’ || :old.localitate);

dbms_output.put_line(’Noua localitate = ’ || :new.localitate);

INSERT INTO modif_stud_localitate (

nrmatricol, localitate_veche, localitate_noua

) VALUES (

:old.nrmatricol, :old.localitate, :new.localitate

);

END modif_stud_localitate;

/

Triggerul s-ar apela ın urma unei instructiuni de genul:

147

UPDATE studenti

SET localitate=’Timisoara’

WHERE nrmatricol IN (104, 108);

Un trigger poate fi activat/dezactivat cu una din metodele:

ALTER TRIGGER trigger_name ENABLE;

ALTER TRIGGER trigger_name DISABLE;

Un trigger poate fi sters printr-o instructiune DROP TRIGGER:

DROP TRIGGER trigger_name;

Triggerele pot fi folosite pentru a crea o coloana autoincrement ıntr-o tabela, ca ın exem-plul urmator:

CREATE table "PERSOANE" (

"PERS_ID" NUMBER,

"NUME" VARCHAR2(20),

"PRENUME" VARCHAR2(20),

constraint "PERSOANE_PK" primary key ("PERS_ID")

);

CREATE sequence "PERSOANE_SEQ";

CREATE trigger "BI_PERSOANE"

before insert on "PERSOANE"

for each row

begin

select "PERSOANE_SEQ".nextval into :NEW.PERS_ID from dual;

end;

8.6 Tranzactii si lucrul ıntr-un mediu multiuser

La fel ca si ın cazul MySQL semnalam terminarea unei tranzactii folosind COMMIT sirefacerea instructiunilor din tranzactia curenta prin ROLLBACK.

O tranzactie ın Oracle ıncepe ın una din urmatoarele 2 situatii:

• cand ne conectam la o baza de date si facem o interogare;

• cand s-a terminat o tranzactie si am executat o interogare.

O tranzactie se termina cand:

• am executat COMMIT sau ROLLBACK;

148

• am executat o instructiune CREATE TABLE;

• am executat o instructiune GRANT;

• cand ne deconectam de la server.

Si ın Oracle exista conceptul de savepoint:

SAVEPOINT save1;

...

ROLLBACK TO SAVEPOINT save1;

Dupa cum am aratat ın ceea ce priveste MySQL exista 4 nivele de izolare:

• READ UNCOMMITTED (nu este implementat ın Oracle)

• READ COMMITTED (este setat implicit ın Oracle)

• REPEATABLE READ (nu este implementat ın Oracle)

• SERIALIZABLE (este implementat ın Oracle)

Nivelul de izolare se poate preciza prin instructiunea urmatoare:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

149

Date post:	25-Dec-2015
Category:	Documents
Upload:	bogdan-musat
View:	13 times
Download:	0 times

CursSGBD

Documents