curs sgbd oracle
TRANSCRIPT
FACULTATEA DE INFORMATICA MANAGERIALA
CURS BD ORACLE an III Seria A (Versiune site:)
PLANIFICARE CURSURI: ORACLE AN III – SEM. II/2010
Prof. dr. Virgil Chichernea
Calendar activitate:
15.02 – 09.05.2010 - ACTIVITATE DIDACTICA (12 sapt.);
10.05 – 23.05.2010 - Definitivare lucrare licenta (2 sapt.);
24.05 – 20.06.2010 - Sesiune de examene (4 saptamani);
21.06 -04.06.2010 - RESTANTE (2 sapt.);
21.06 – 05.06.2010 - Inscriere la lienta ;
7.07 - 11.07.2010 - EXAMEN DE LICENTA
PLANIFICARE CURSURI- Semestrul II – An Univ: 2009-2010
Planificarea cursuri : S.G.B.D. ORACLE-SQL AN III: SEMESTRUL II -2010
Cursul nr. 1. – 17.02.10
Prezentarea generală a SGDB-urilor şi a arhitecturii sistemelor informatice cu baze de
date/ baze de date relationale;
SGBD – ORACLE. Arhitectura funcţională şi internă a sistemului ORACLE.
Sesiuni de lucru şi controlul accesului utilizatorilor;
Cursul nr. 2. – 24.02.10
Utilizarea SQL*PLUS - Editarea comenzilor. Comenzi interactive. Formatarea
rezultatelor;
Cursul nr.3. – 3.03.10
SQL - Limbajul de definire a datelor; Crearea si actualizarea bazei de date prin
comenzi SQL;
Cursul nr.4. – 10.03.10
Limbajul de manipulare a datelor; Limbajul pentru controlul accesului la date;
Cursul nr.5. – 17.03.10
Interogarea bazei de date şi setul de operatori SQL. Subinterogări. Interogări
corelate. Utilizarea subinterogărilor pentru crearea si actualizarea tabelelor.
Cursul nr.6. – 24.03.10
Implementarea operatorilor relaţionali în SQL. Operatorii asamblişti. Operatorii
relaţionali (selecţia, proiecţia, joncţiunea, diviziunea). Operatori aritmetici, funcţii şi
grupuri de funcţii; Stabilire teme proiect de semestru
Cursul nr.7. –31.03.10
Test nr. 1 - test semestrial în plen
Cursul nr.8. – 7.04.10
Functii agregat SQL. Subtotaluri. Analize multidimensionale. Operatorii CUBE şi
GROUPING SETS. Soluţii OLAP. Ferestre pentru funcţii analitice;
Obiecte din schema bazei de date. Extensii procedurale ale SQL. Dicţionarul bazei de
date. Reguli de validare. Tabele virtuale în ORACLE si VFP.
Cursul nr.9. – 14.04.10
Realizarea de rapoarte si videoformate in SQL. Crearea viziunilor, secventelor,
sinonimelor si grupurilor de tabele sau indecsi (clusteri)
Cursul nr.10. – 21.04.10
Introducere in PL/SQL. Proceduri, funcţii stocate şi declanşatori în VFP şi ORACLE
Cursul nr.11. – 28.04.10
Test nr. 2 - test semestrial în plen
Cursul nr.12. – 5.05.10
Dezvoltari de proiecte in ORACLE. Privilegii, roluri, utilizatori, schema unui
utilizator, securitatea datelor. Expresii si Functii.
Bibliografie:
1. BAZE DE DATE – ORACLE – SQL, SQL*Plus, PL/SQL
Autori : Prof. Dr. Virgil Chichernea, Lector drd. Gabriel Garais, Asistent univ. Dragos
Paul Pop, Editura Universitatii Romano-Americane, Bucuresti 2009.
2. Pliant cu comenzile SQL
CUPRINSUL MANUALULUI CURSULUI
UNIVERSITATEA ROMANO-AMERICANA BUCURESTI
Prof. univ. dr. Virgil Chichernea
Lect. univ. drd. Gabriel Eugen Garais Asist. univ. Dragos Paul Pop
BAZE DE DATE ORACLE
- SQL, SQL*Plus, PL/SQL -
Bucuresti 2009
Cuprins
Prefaţă
BAZE DE DATE RELATIONALE SI ARHITECTURA
FUNCTIONALA A SGBD ORACLE 1.1. Baze de date relaţionale (BDR)
1.2. Modelul relaţional
1.3. Forme normale
1.4. Arhitectura funcţională a SGBD ORACLE
PARTEA I
Sectiunea I:
LIMBAJUL SQL (Structured Query Language) - ORACLE 2.1. Prezentarea limbajului SQL-ORACE
- Tabele
- Structura comenzilor în SQL
2.2. Limbajul de definire a datelor (LDD)
a) Crearea bazei de date şi spaţiului alocat tabelelor
Crearea bazei de date în SQL : Comanda CREATE DATABASE
Crearea spaţiului alocat tabelelor
b) Crearea obiectelor în baza de date
Crearea unei tabele
Modificarea structurii într-o tabela existentă:
c) Crearea viziunilor : Comanda CREATE VIEW
d) Vizualizarea structurii unei tabele
e) Ştergerea unui obiect din baza de date
2.3. Limbajul de manipulare a datelor
Comanda INSERT
Comanda DELETE
Comanda UPDATE
Comanda SELECT
2.4. Limbajul pentru controlul accesului la date (LCD)
2.5. Interogarea bazelor de date. Implementarea operatorilor relaţionali
Interogări simple
2.5.1 Implementarea operatorilor de selecţie şi proiecţie
2.5.2. Expresii, condiţii şi operatori
Operatori:
a) Operatori aritmetici
b) Operatori de comparaţie:
c) Operatori de subliniere
d) Operatori logici: AND; OR; NOT;
e) Operatori pentru mulţimi: UNION şi UNION ALL
f) Operatorul INTERSECT
g) Operatorul MINUS
h) Operatorul BETWEEN
i) Operatorul LIKE
j) Operatorul IN
2.5.3. Implementarea operatorului JOIN (operator de compunere)
a) Echi-joncţiunea ( jonctiunea echivalentă)
b) Non – echi- joncţiuni
c) Joncţiuni externe
d) Auto-joncţiunea (joncţiunea tabelei cu ea însăsi)
2.5.4. Crearea de scripturi (Macro)
2.5.5. Definirea variabilelor în SQL
2.6. Funcţii SQL şi funcţii agregate SQL
2.6.1. Funcţii totalizatoare:
a) Funcţia COUNT – returnează nr. total de linii care respectă clauza WHERE
b) Funcţia SUM – returnează suma tuturor valorilor dintr-o coloană
c) Funcţia AVG – calculează valoarea medie a unei coloane
d) Funcţia MAX – găseste şi afişează valoarea cea mai mare dintr-o coloană
e) Funcţia MIN – returnează cea mai mică valoare dintr-o coloană
f) Funcţia VARIANCE – Calculează şi afişează dispersia valorilor dintr-o coloană
g) STDDEV – calculează şi afişează abaterea (deviaţia) standard
2.6.2. Funcţii pentru data calendaristică şi ora
a) Funcţia ADD-MOUNTHS – adaugă un număr de luni la data curentă
2.6.3. Funcţii matematice
a) Funcţii aritmetice
b) Funcţii trigonometrice
c) Funcţii logaritmice EXP, LN, LOG
d) Funcţii le POWER, SIGN, SQRT
2.6.4. Funcţii de tratare a şirului de caractere
2.6.5. Funcţii diverse
2.6.7 Funcţii agregate - Clauzele utilizate în comanda SELECT
a) Clauza WHERE
b) Clauza STARTING WITH
c) Clauza ORDER BY
d) Clauza GROUP BY
e) Clauza HAVING
2.7. Cereri complexe
2.7.1. Cereri complexe de interogare
2.7.2. Subinterogări
2.8. Realizare rapoarte şi videoformate(Forms) în SQL
2.8.1. Realizarea rapoartelor în SQL
2.8.2. Realizarea de videoformate (FORMS) în SQL
Sectiunea a II-a:
SQL*PLUS (Manual de utilizare). 3.1. Sesiunea de lucru
3.2. Moduri de lucru cu SQL*Plus
3.3. Editarea comenzilor SQL*Plus (exemple)
3.4. Opţiunile de lucru. Comanda SET;
3.5. Utilizarea variabilelor utilizator
3.6. Introducerea de comentarii printre comenzile dintr-un fişier
3.7. Afişarea unor informaţii: (DESCRIBE: HELP{COMANDS/ CLAUSE})
3.8. Parametrizarea/abandonarea schimbărilor asupra BD
3.9. Controlul accesului la baza de date: nume utilizator şi parola.
3.10. Formatarea rezultatelor şi editarea de rapoarte
3.11. Dicţionarul datelor – componenta a SGBD ORACLE
PARTEA a II-a:
LIMBAJUL DE PROGRAMARE PL/SQL
Sectiunea I: 4.1. Introducere în limbajul PL/SQL
4.2. Structura unui bloc de bază PL/SQL
4.3. Declararea variabilelor
4.4. Elemente de limbaj
4.1.3 Declaraţiile
4.5. Instrucţiuni PL/SQL
4.6. Gestiunea cursorului în PL/SQL
4.7. Subprograme PL/SQL
4.8. Pachete
4.9. Declanşatori (triggeri) - 4.9.1. Integritatea restricţiilor şi triggeri
- 4.9.2. Integritatea restricţiilor
- 4.9.3. Exemple de triggeri
- 4.9.4. Programarea trigerilor
- 4.9.5. Mai multe despre trigeri
4.10. Gestiunea erorilor
4.11. Abordarea orientată obiect în PL/SQL - 4.11.1. Programarea în PL/SQL
- 4.11.2. Încorporarea SQL şi Pro*C
- 4.11.3. Concepte generale
- 4.11.4. Variabile gazda şi variabile de comunicaţii
- 4.11.5. Zona de comunicare
Sectiunea a II-a:
PROBLEME REZOLVATE ÎN SQL ŞI PL/SQL 5.1. Prezentarea studiului de caz şi a modelului relaţional
5.2. Probleme referitoare la LDD şi LMD
5.3. Interogări monorelaţie
5.4. Interogări utilizând operatorii relaţionali
5.5. Interogări multi relaţie
5.6. Aplicaţii referitoare la vizualizări
5.7. Aplicaţii referitoare la declanşatori
5.8. Subprograme în PL/SQL
PARTEA a III-a:
Sectiunea I: Proiecte realizate in SQL si PL/SQL
Sectiunea a II-a:
PREZENTAREA ŞI UTILIZAREA APLICAŢIEI SITI-
RATING 6.1 Obiective
6.2 Modele economico-matematice utilizate
6.3 Manual de utilizare
Sectiunea a III-a: Pliant SQL
Teste de autoevaluare
Bibliografie
EXTRAS DIN CUPRINSUL CURSURILOR PREDATE LA :
DISCIPLINA : BD-ORACLE AN III – SERIA A Prof. Dr. Virgil Chichernea
Cursul nr. 1 : (extras)
Obs : Continutul informational complet se afla in manual.
1.4. Arhitectura generală a SGBD-ului ORACLE
Corporaţia ORACLE este principalul furnizor de software pentru gestiunea
bazelor de date relaţionale (SGBDR), software ce lucrează pe o gamă largă de
calculatoare conectate la Internet. În fig. nr. 3 sunt prezentate principalele etape evolutive
din istoria acestei corporaţii, a doua companie de software la nivel mondial.
Fig. 3 Etape semnificative ale evolutiei SGBD-ului ORACLE
1.4.1 Arhitectura funcţională şi internă
În cele ce urmează vom discuta despre componentele SGBD ORACLE: arthitectură,
structura logică şi fizică a bazei de date, şi modul în care comenzile SQL sunt procesate
şi cum se crează obiectele bazei de date.
Principalele caracteristici ale sistemului ORACLE sunt:
- ORACLE este un SGBD relaţional, portabil pe o mare varietate de platforme şi
foloseşte un număr mare de utilitare care se pot folosi pe toate etapele modelării,
de la specificarea cerinţelor până la exploatarea aplicaţiilor dezvoltate.
- ORACLE dispune de un limbaj de definire (DDL), un limbaj de manipulare
(DML) a datelor şi un limbaj de control al datelor (LCD);
- Manipularea datelor se face cu ajutorul limbajului SQL şi PL/SQL (Procedural
Language);
- Oracle suportă proceduri, funcţii şi biblioteci (pachete) stocate la nivelul bazei de
date. Pachetele pot fi apelate prin programele utilizatorilor sau de către sistem ca
urmare a realizării unui anumit eveniment declaşator (trigger).;
- Oracle asigură: coerenţa datelor, confidenţialitatea datelor, integritatea datelor,
salvarea şi restaurarea datelor, gestiunea acceselor concurente, portabilitatea
datelor şi a aplicaţiilor dezvoltate,
Arhitectura funcţională, sub o formă simplificată, a SGBD ORACLE este prezentată în
fig.4
ARHITECTURA ORACLE
INTERFETE DE DEZVOLTARE
DEVELOPER DESIGNER PRO*…
NUCLEUL ORACLE
SQL*PLUS PL/SQL JAVA
INSTRUMENTE DE INTRETINERE
ADMINISTRATOR TOOLS NET PRODUCTS
BROWSERE, EDITOARE, …
BD
Fig. 5. Arhitectura funcţională ORACLE
Componentele care formează arhitectura ORACLE sunt structurate pe trei niveluri:
nucleul, intefeţele şi instrumentele de întreţinere. Aceste componente sunt dispuse într-o
configuraţie client/server. Componentele de client şi de server sunt plasate pe
calculatoare diferite într-o reţea în care:
- serverul asigură memorarea şi manipularea datelor, precum şi administrarea bazei
de date;
- clientul asigură interfaţa cu utilizatorul şi lansează aplicaţia care accesează datele
din baza de date.
1.4.2. Managementul memoriei şi al proceselor
Oracle DBMS server se bazează pe aşa numita arhitectură Multi-Server. Serverul este
responsabil cu procesarea tuturor bazelor de date active, adică cu execuţia unei comenzi
SQL, managementul userului, resurselor şi memoriei. Deşi există numai o copie a codului
de program pentru SGBD server, fiecare utilizator este conectat la un server logic care îi
este destinat. Fig. Nr.6 ilustrează arhitectura SGBD Oracle privind structura memoriei,
proceselor şi fişierelor.
Figura 6: Arhitectura sistemului Oracle
1.4.3. Structura logica şi fizica a bazei de date
Baza de date a unei aplicaţii realizată în ORACLE conţine tabele corelate în care se
memorează datele aplicaţiei. Rândurile unei tabele reprezintă Entităţi (înregistrări), iar
coloanele tabelei reprezintă atribute (câmpuri). Un model de date este o structură pentru
descrierea şi documentarea datelor necesare utilizatorului care pot fi implementate într-o
bază de date. Un model de date trebuie să fie creat pentru fiecare user. Toate modelele de
useri sunt adunate într-un model de bază de date. Un view de user este dezvoltat din
modelul de bază de date pentru a reprezenta modelul fiecărui user.
System Global Area (SGA)
Utilizator 1 Utilizator 2 Utilizator n
Server
Proces
PGA
Server
Proces
PGA
Server
Proces
PGA
Buffer
Baza de date
(Database)
Buffer
Redo-log
Buffer
Arhiva
Log
Shared Pool
Dicţionar Cache
Bibliotecă
Cache
DBWR LGWR ARCH PMON SMON
Procese de bază
Fişiere
date
Fişiere
Redo-log
Fişiere
Control
Fişiere
Arhivă şi backup
Schema bazei de date descrie în totalitate baza de date adică: descrie tabelele, defineşte
relaţiile, defineşte domeniile, defineşte regulile afacerilor.
Elementele unei baze de date ORACLE (vezi fig. nr. 8):
- Zonele de memorie: Zona globală de sistem (SGA); Zona globală de program
(PGA); Zona de context (CA).
- Procesoarele ORACLE: DataBase Writer; Process Monitor; System Monitor;
Proces Check Monitor. (Client; Sever).
- Obiectele tip ORACLE: BD; Tabela de bază; Tabela virtuală; Clusterul; Indexul;
- Tipuri de date – cele din standardul SQL;
- Fişierele - pentru memorarea datelor din structura fizică a BD ORACLE:
o Fişierele de date (tabele de bază, indecşi, clusteri, segmente, dicţionarul de
date);
o Fişierele de control
o Fişierele jurnal (redo, log);
o Alte fişiere;
Fig. nr. 8. Baza de date ORACLE
În arhitectura bazei de date se face distincţie între structura logică şi fizică a bazei de date
(vezi fig. nr. 9). Structura logică descrie zonele logice de memorie (numite spaţii) unde
obiectele ca tabelele pot fi memorate. Structura fizică este determinată de fişierele
sistemului de operare care constituie baza de date.
Figure 9: Structura logica şi fizica a unei baze de date
Urmatoarea schema ilusrează arhitectura ORACLE a instanţei BD şi a legăturilor între
structurile logice şi fizice .
Fig. nr.10 Relaţiile dintre structura logică şi fizică a bazei de date
Fişiere redo-log Baza de date Fişiere control
Fişiere Date Tabele spaţiu
Segment
Tabele
Tabele
Tabele
Tabele extent Block
Baza de date
Spatiu tabel
Segment
Extensie
Bloc ORACLE
Fisier de date
Bloc OS
FIZIC LOGIC
Cursul nr. 2 : (extras)
Obs : Continutul informational complet se afla in manual.
PARTEA I
Sectiunea a II-a
1.2
SQL*PLUS (Manual de utilizare). 2.1. Sesiunea de lucru
2.2. Moduri de lucru cu SQL*Plus
2.3. Editarea comenzilor SQL*Plus (exemple)
2.4. Opţiunile de lucru. Comanda SET;
2.5. Utilizarea variabilelor utilizator
2.6. Introducerea de comentarii printre comenzile dintr-un fişier
2.7. Afişarea unor informaţii: (DESCRIBE: HELP{COMANDS/ CLAUSE})
2.8. Parametrizarea/abandonarea schimbărilor asupra BD
2.9. Controlul accesului la baza de date: nume utilizator şi parola.
2.10. Formatarea rezultatelor şi editarea de rapoarte
2.11. Dicţionarul datelor – componenta a SGBD ORACLE
SQL*PLUS - PREZENTARE GENERALA
Obiective:
- Prezentarea unui manual minimal de utilizare a interfeței SQL*Plus;
- Controlul accesului la baza de date: nume utilizator şi parola;
- Formatarea rezultatelor şi editarea de rapoarte;
- Accesul la dicționarul datelor ORACLE.
SQL*Plus asigură interfața interactivă (low-level) dintre utilizator şi SGBD-ul
ORACLE. SQL*Plus este un program utilitar, cel mai utilizat de SGBD ORACLE, care
permite utilizatorilor să definească şi să manipuleze interactiv obiectele bazei de date şi
asigură accesul la obiectele (componentele) unei baze de date (tabele, viziuni, clustere
etc.). Acest program utilitar se utilizează implicit la afișarea rezultatelor interogarilor sau
a view-urilor pe ecran.
Printre facilităţile oferite de către SQL*Plus se pot enumera:
• Este un editor de linii de comanda SQL. ( Se precizează că SGBD ORACLE acceptă şi
alte editoare de linii de comandă );
• Oferă numeroase comenzi pentru formatarea rezultatului unei cereri;
• SQL*Plus asigură asistența on line (help on line).
• Rezultatul cererii poate fi memorat în fişiere care pot fi printate;
• Cererile frecvente pot fi salvate într-un fişier şi pot fi apelate de câte ori este nevoie;
• Cererile pot fi parametrizate astfel că este posibil să se invoce o cerere salvată şi să se
precizeze numai parametrii.
După ce utilizatorul s-a conectat la SQL*Plus, sistemul afișează promterul SQL>
şi asteaptă comenzile utilizatorului. Utilizatorul poate scrie trei tipuri de comenzi:
- Instrucţiuni SQL pentru definirea şi accesarea bazei de date;
- Blocuri PL/SQL pentru accesarea bazei de date;
- Comenzi SQL*Plus pentru editarea şi memorarea comenzilor SQL, a
blocurilor PL/SQL, pentru formatarea rezultatelor şi pentru setarea opţiunilor
de lucru.
-
3.1. Sesiunea de lucru:
Pentru a lucra cu SQL*Plus utilizatorul trebuie să se conecteze la sistemul de operare şi
să deschidă o sesiune de lucru ORACLE conform procedurilor de mai jos.
Deschiderea unei sesiuni de lucru:
Pentru a deschide o sesiune de lucru utilizatorul SGBD ORACLE poate opta pentru
una din procedurile prezentate mai jos:
a) Clic pe START All Programs ORACLE Application
Development SQL*PLUS 10g ….
Apare o fereastră pentru conectare şi se introduce nume utilizator şi parola,
[nume bază de date] OK ; Apare prompterul SQL>
b) Click pe START sau click pe iconul
Se tastează nume utilizator, parolă, [nume baza de date] OK ; Apare
prompterul SQL>
Inchidere sesiune SQL*PLUS: se realizează prin una din comenzile:
SQL> QUIT;
SQL> EXIT;
SQL> [CTRL]Z;
Și apare mesajul : Disconnected from ORACLE.
3.2. Moduri de lucru cu SQL a) modul direct, în care utilizatorul introduce o comandă SQL care se tastează după
promterul SQL. Această comandă este memorată în bufferul SQL şi poate fi
modificată sau lansată în execuție de mai multe ori, atâta timp cât utilizatorul nu a
introdus o nouă comandă sau nu a șters explicit bufferul SQL; O nouă comandă SQL
va șterge automat vechea comandă din buffer.
b) modul program, care constă în elaborarea şi rularea unui fişier de comenzi ce
conţine instrucţiunile unui program sursă scris în limbajul SQL. Fişierul este de
forma nume.SQL şi se obţine prin una din urmatoarele proceduri:
- cu ajutorul comenzii SQL > EDIT tastare comenzi SQL
SAVE nume_fişier [CREATE|REPLACE|APPEND];
- salvarea buffer-ului SQL, care conţine un număr de comenzi, cu ajutorul
comenzii: GET nume_fişier[.ext] [LIST] [NOLIST].
- utilizarea comenzii INPUT urmată de comenzile fişierului şi apoi salvarea
conţinutului buffer-ului într-un fişier.
3.3 Editarea comenzilor SQL*Plus (exemple)
Cele mai recente instrucţiuni SQL sunt memorate în bufferul SQL, indiferent dacă aceste
instrucţiuni au sau nu sintaxa corectă. Se poate edita bufferul folosind una din comenzile:
• l[ist] – listează toate liniile din bufferul SQL şi marchează cu “_” linia curentă din
buffer
• l<number> - listază linia <number>
• c[hange]/<old string>/<new string> - inlocuiește secvența șirului vechi cu șirul nou;
• a[ppend]<string> - adauga <string> la linia curentă;
• del – șterge linia curentă;
• r[un] – execută conţinutul bufferului;
• get<file> - citește datele din fişierul <file> în buffer;
• save<file> - scrie bufferul current în <file>;
•edit invocă (lansează) editorul şi incarcă bufferul curent în editor. După iesirea din editor
instrucțiunea SQL modificată este memorată în buffer şi poate fi executată cu comanda r;
Editorul poate fi definit în shell-ul SQL*Plus prin tastarea comenzii:
editor =<name>, unde <name> poate fi un nume de editor.
Comenzile SQL sunt memorate în buffer-ul comenzilor SQL şi pot fi modificate fără
a fi nevoie de retastare. Editarea se poate realiza utilizând următoarele comenzi
SQL:
L afişează toată comanda (toate liniile comenzii din buffer-ul SQL);
Ln afişează numai linia n;
L* afişează linia curent[;
Lmn afişează liniile de la m la n;
Llast afişează linia precedentă;
I inserarea de linii după linia curentă.
2.4.Opţiunile de lucru. Setarea se face cu ajutorul comenzii SET
a) Afişarea opţiunilor se face cu comanda SHOW
1) Să se afişeze numărul poziţiei liniei în cadrul paginii curente
SQL> SHOW LNO;
lno 9
.......
b) Evaluarea timpului necesar executării comenzilor
5) Să se creeze o zonă de contorizare a timpului necesar executării diferitelor comenzi,
cu numele CONTOR
6) Să se indice efectuarea unui salt de trei rânduri la începutul unei pagini noi.
SQL> SET PAGESIZE 7
3.5. Utilizarea variabilelor utilizator. &nume_variabila
prin comanda ACCEPT sau comanda DEFINE
1) Să se definească variabila VAR1 ca fiind coloana MARCA, prin comanda DEFINE
şi variabila VAR2 ca fiind coloana NUME, prin coloana ACCEPT.
SQL> DEFIME VAR1=MARCA;
ACCEPT VAR2 NUME
SQL> /
old 1: selected &VAR1, &VAR2
new 1: SELECTED MARCA, NUME
MARCA NUME
----------------------------
400 GICA ION
3.6. Introducerea de comentarii printre comenzile dintr-un fişier
(REMARK; DOCUMENT; /*… */ )
1) Să se introducă un rând de comentariu în programul de bufer curent
SQL> INPUT /* PROGRAM TEST */
SQL> LIST
1 SELECT &VAR1, &VAR2
2 /* PROGRAM TEST */
3 FROM SALARIATI
4 * WHERE FUNCT= ' DIRECTOR'
Obs. Pentru ca după afişarea unei pagini să se facă pauză, urmând să se aştepte apăsarea
tastei ENTER , se foloseşte comanda PAUSE [text].
3.7. Afişarea unor informaţii : ( DESCRIBE: HELP{COMANDS/ CLAUSEE})
Help System SQL*Plus şi alte comenzi utile
Pentru a avea acces la help-ul online SQL*Plus se tastează help; • Pentru a schimba parola se foloseşte comanda:
alter user <user> identified by <new password>;
• Comanda desc[ribe] <table> listează toate coloanele unei tabele împreună cu tipul
datelor şi informaţii despre valorile null dacă au fost alocate;
• Comanda spool <file> asigură ca toate informaţiile de pe display să fie memorate în
<file>. Pentru a inhiba această comandă se utilizează: spool off;
• Comanda copy poate fi utilizată pentru copierea completă a unei tabele.
Exemplu:
copy from scott/tiger create EMPL using select _ from EMP;
3.8. Parametrizarea/abandonarea schimbărilor asupra BD
1) Să se realizeze efectiv actualizările făcute asupra bazei de date
SQL> COMMIT
commit complete
b) Să se renunţe la actualizările făcute
SQL> ROLLBACK
rolback complete
Copierea datelor între BD diferite
COPY [FROM nume_utilizator / parola@baza_date]
[TO nume_utilizator / parola@baza_date]
{APPEND | CREATE | INSERT |REPLACE |
nume_tabela [ (nume_col, nume_col,…..)]
USING cerere;
3.9. Controlul accesului la baza de date: nume utilizator şi parolă.
Acordarea drepturilor de acces
1) Să se acorde dreptul de conectare la baza de date utilizatorului COMBAZA1;
SQL> GRANT CONNECT TO COMBAZA1;
Grand succeeded.
2) Să se definească un acces privilegiat pentru tabela SALARIATI utilizatorului util.
SQL> GRANT ALL ON SALARIATI TO UTIL1;
Grant succeeded.
3) Să se revoce drepturile de acces la baza de date pentru utilizatorul UTIL1.
SQL> REVOKE CONNECT FROM UTIL1;
Revoke succeeded.
4) Să se revoce accesul privilegiat la tabela PRODUSE utilizatorului UTIL2.
SQL> REVOKE ALL ON PRODUSE FROM UTIL2;
Revoke succeeded.
Reţinerea în baza de date a comenzilor utilizator [ AUDIT].
5) Să se controleze operaţia de introducere a datelor în tabela SALARIATI.
SQL> AUDIT INSERT
2 ON SALARIATI BY ACCESS;
Audit succeeded.
Partajarea accesului la tabele
SQL> LOCK TABLE SALARIATI
2 IN EXCLUSIVE MODE NOWAIT;
Table(s) Locked.
Observaţie. Comanda LOCK TABLE dă posibilitatea utilizatorului să împartă
în mod egal cu alţi utilizatori accesul la una sau mai multe tabele, păstrând
integritatea acestora.
2.10.Formatarea rezultatelor şi editarea de rapoarte
Raportul este o formă de afişare a datelor într-un format cerut de beneficiar. Pentru
realizarea editării de rapoarte profesionale SGBD ORACLE pune la dispoziţia
utilizatorilor un număr de comenzi aşa cum se poate vedea în cele ce urmează:
Tema 1: Executaţi secvenţa de comenzi şi observaţi efectul lor :
SQL> SET BUFER BAFUTIL
I
TTITLE ' Stat de salarii'
BTITLE 'Terminare raport'
COLUMN MARCA FORMAT A5 HEADING 'Marca| Angajat'
COLUMN NUME FORMAT A12 HEADING 'Nume| Angajat'
COLUMN PREN HEADING 'Prenume| Angajat'
COLUMN SALAR FORMAT 9999 HEADING 'Salariu'
COLUMN ATEL HEADING 'Atelier' FORMAT A7
BREAK ON REPORT ON ATEL SKIP1
COMPUTE SUM OF SALAR ON ATEL
COMPUTE SUM OF SALAR ON REPORT
SELECT ATEL, MARCA, NUME, PREN, SALAR
FROM PERSONAL ORDER BY ATEL;
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
Se apasă tasta ENTER
3.11.Dicţionarul datelor ORACLE
Dicţionarul datelor Oracle este cea mai importantă componentă a SGBD-ului Oracle. El
conţine toate informaţiile despre structura şi obiectele bazei de date cum ar fi tabelele,
coloanele, utilizatorii, fişierele de date etc. Datele memorate în dicţionarul datelor se
numesc metadate. Deşi el constituie domeniul administratorul bazei de date, datele
dicţionarului sunt surse de informaţii pentru utilizatori şi pentru dezvoltatorii de aplicaţii.
Dicţionarul datelor conţine două nivele : nivelul intern care conţine toate tabelele de bază
care sunt utilizate de către componentele software SGBD si ele nu sunt accesibile
utilizatorilor finali.
Nivelul extern furnizează numeroaselor vederi informaţiile accesate de către aceste tabele
despre obiectele şi structurile la diferite nivele în detaliu.
3.11.1. Tabelele dicţionarului datelor
La instalarea unei baze de date Oracle întotdeuna se crează trei utilizatori standard
Oracle:
• SYS: Acesta este proprietarul tabelelor dicţionarului de date şi vederilor. Acest
utilizator are cele mai mari privilegii în mânuirea obiectelor şi structurilor bazei de date
Oracle şi are dreptul de a crea noi utilizatori.
• SYSTEM: este proprietarul tabelelor utilizate de diferite utilitare cum ar fi SQL*Forms,
SQL*Reports etc. Acest utilizator are mai puţine privilegii decat SYS.
• PUBLIC: Acesta este un utilizator “dummy” al bazei de date Oracle. Toate privilegiile
asignate acestui utilizator sunt automat asignate tuturor utilizatorilor cunoscuţi ai bazei de
date.
Tabelele şi vederile furnizate de către dicţionarul datelor conţin informaţii despre:
• utilizatorii şi privilegiile lor;
• coloanele tabelelor şi tipul de date, restricţii de integritate, indexi;
• statistici despre tablele şi indexi utilizati pentru optimizare;
• Privilegiile acordate obiectelor bazei de date;
• structurile memorate ale bazei de date.
Comanda SQL
select * from DICT[IONARY];
3.11.2. Vederile (Views) din dicţionarului datelor
Nivelul extern al dicţionarului datelor furnizează utilizatorilor un acces la datele relevante
ale utilizatorilor. Acest nivel asigură numeroase vederi (cca 540) care reprezintă date
privind tabelele într-o manieră inteligibilă.
Aceste vederi (views ) pot fi utilizate în interogările SQL la fel ca şi tabelele normale.
Vederile furnizate de dicţionarul datelor conţine trei grupuri de informaţii: USER, ALL şi
DBA. Grupul nume se construieşte cu un prefix acordat vederii. Exista asociate sinonime
aşa cum se prezintă în continuare.
• USER : Înregistrările din vederea USER conţin informaţii despre obiectele
proprietarului care a lansat interogarea SQL (current user)
USER TABLES – toate tabelele cu numele lor, numărul de coloane, informaţia
memorată, informaţii statistice etc. (TABS)
USER CATALOG tabele, vederi şi sinonime (CAT)
USER COL COMMENTS commentarii pentru coloane;
USER CONSTRAINTS definiţia restricţiilor pentru tabele;
USER INDEXES toate informaţiile despre indexi create pentru (IND);
USER OBJECTS toate obiectele bazei de date aparţinând utilizatorului (OBJ)
........
Cursul nr. 3 - 9 : (extras)
Obs : Continutul informational complet se afla in manual.
PARTEA I
Sectiunea I:
LIMBAJUL SQL (Structured Query Language) - ORACLE
1. Prezentarea limbajului SQL-ORACE
2. Limbajul de definire a datelor (LDD)
3 Limbajul de manipulare a datelor
4. Limbajul pentru controlul accesului la date (LCD)
5. Interogarea bazelor de date. Implementarea operatorilor relaţionali
6. Interogări simple
7. Funcţii SQL şi funcţii agregate SQL
8. Cereri complexe
9. Realizare rapoarte şi videoformate(Forms) în SQL
Nota: Pentru interfata utilizator cu platforma ORACLE recomandam utilizarea
SQL*Plus. Manualul minimal de utilizare a SQL*Plus se afla in sectiunea a II-a.
FUNDAMENTELE PROGRAMĂRII ÎN LIMBAJUL SQL - ORACLE
Obiective:
- Prezentarea şi modul de lucru cu limbajului SQL;
- Interogarea bazelor de date şi implementarea operatorilor relaţionali:
- Funcţii SQL şi funcţii agregate SQL;
- Cereri complexe care au ca suport mai multe tabele ;
- Realizare rapoarte şi videoformate(Forms) în SQL.
2.1. Prezentarea limbajului SQL-ORACLE
Limbajul SQL (Structured Query Language) este un limbaj standard care poate fi
utilizat pentru a defini, interoga, actualiza, şi gestiona o bază de date relaţională.
SQL utilizează următorii termeni: tabelă, linii, coloane, sau termeni echivalenţi, relaţii,
înregistrării/tupluri, atribute. În acest manual se va folosi: linii/randuri/înregistrări/tupluri
şi coloane/atribute.
O tabelă poate avea maxim 254 coloane care pot avea acelaşi tip de date sau tipuri
diferite de date şi în care se pot memora domenii de valori. Domeniile posibile sunt: date
alfanumerice (şiruri), numere şi date formatate.
SGBD ORACLE oferă următoarele tipuri de date:
- char (n): date tip caracter în lungime fixă (şir), de lungime n caractere.
Mărimea maximă pentru n este 255 bytes. Spaţiul fizic ocupat este egal cu n.
Dacă lungimea reală a şirului este mai mică decât lungimea declarată atunci
se completează automat şirul cu spaţii în partea dreaptă pană la completarea
celor n caractere declarate.
2.2. Limbajul de definire a datelor (LDD)
Limbajul de Definire a Datelor (LDD) permite definirea bazei de date (BD) şi a
obiectelor care compun baza de date. Prin definirea unui obiect al bazei de date în SGBD
ORACLE se înţelege crearea, modificarea şi suprimarea obiectului şi pentru realizarea
acestui obiectiv, SGBD ORACLE pune la dispoziţia utilizatorilor un set de
instrucţiuni/comenzi SQL (CREATE; ALTER; DESCRIBE; DROP), care permit crearea,
vizualizarea structurii tabelelor, modificarea şi distrugerea obiectelor bazei de date.
Schema bazei de date conţine o serie de structuri logice de date care aparţin unui
utilizator al BD şi care poartă numele acestuia. Un utilizator poate avea o singură schemă
care va conţine numele schemei (adică numele utilizatorului), descrierea tabelelor,
vizualizărilor şi privilegiilor asociate. Comanda de definire a schemei BD are sintaxa:
CREATE SCHEMA AUTHORIZATION nume-schemă
{comanda CREATE TABLE | comanda CREATE VIEW | comanda GRANT}…
Tipuri de date în SGBD- ORACLE: SGBD SGBD ORACLE permite memorarea şi prelucrarea următoarelor tipuri de
date:
- numerice : NUMBER; FLOAT; DECIMAL; INTEGER;
- şiruri de caractere (alfa-numerice): CHAR; VARCHAR2; LONG;
- date calendaristice: DATE;
- Binary Large Object(BLOB).
Fig. 1 Ecran de vizualizare a structurii unei tabele a bazei de date.
2.2.1. Definirea de obiecte în SGBD ORACLE
Definirea tabelelor
Tabela este acea structură a bazei de date în care se memorează datele bazei de date
relaţionale. Definirea tabelelor include operaţiile de creare de tabele noi (comanda
CREATE), vizualizarea structurii tabelei (comanda DESCRIBE), modificarea structurii
tabelelor existente (comanda ALTER), şi ştergerea la cererea utilizatorilor a tabelelor
(comanda DROP).
Crearea unei tabele: Sintaxa generala a comenzii de creare a unei tabele este:
CREATE TABLE nume_tabela (nume_coloana1 tip (marime) restricţie,
nume_coloana 2 tip (marime) restricţie, ...,
nume_coloana n tip (marime) restricţie,....)
Indicarea restricţiilor(constrângerilor) la nivel de coloană:
Pentru a indica o restricţie la nivel de coloană când se creează tabela, se introduce
restricţia pe aceeaşi linie cu definiţia coloanei. Restricţiile (constrângerile) acceptate sunt
:
UNIQUE
Forţează unicitatea pe coloană;
NOT NULL
Nu acceptă valori null;
PRIMARY KEY
Defineşte coloana ca fiind cheie primară
REFERENCES tabela (coloana1, coloana2,...)
Defineşte coloana ca fiind o cheie străina;
ON DELETE CASCADE
Forţează ştergerea înregistrărilor când părintele cheilor străine este şters.
Poate fi folosit doar cu clauza REFERENCES;
DEFAULT
Oferă o valoare implicită pentru coloană;
CHECK conditie
Verifică condiţia înainte de a accepta data;
Indicarea constrângeri la nivel de tabelă:
Cheile primare şi cheile externe se pot defini fie la nivel de câmp, fie la nivel de tabelă.
Dacă cheia primară sau cheia externă este compusă, trebuie definită la nivel de tabelă
prin clauzele:
PRIMARY KEY (nume_coloana, nume_coloana,...)
FOREIGN KEY nume_coloana REFERENCES nume_ tabela (coloana)
CHECK (nume_coloana condiție) sau (nume_coloana ÎN (SET OF
VALUES))
Pentru a specifica restricţiile la nivel de tabelă, restricţiile se introduc după ultima
definiţie a coloanei.
Sintaxa generală de creare a structurii unei tabele în SQL este de forma:
CREATE TABLE <nume_tabela>
( <nume_coloana_1> <tip data > [not null] [unique] [<constrangeri coloana>],
. . . . . . . . .
( <nume_coloana_ n> <tip data > [not null] [unique] [<constrangeri coloana>],
[<restricţii nume_tabela>]);
Verificarea tabelelor create:
Înainte de crearea unei tabele este necesar să stim:
. care sunt atributele unei înregistrări care urmează să fie memorate;
. care sunt tipurile de date asociate coloanelor;
. se va folosi varchar2 sau char;
. din ce coloane se construiește cheia primară;
. ce coloane permit (nu permit) valori nule;
. ce coloane au (nu au) valori duplicate;
. care sunt valorile implicite pentru coloanele care permit memorarea valorii null.
Comanda DESCRIBE:
Pentru a vizualiza structura unei tabele se utilizează comanda DESCRIBE, care
are ca efect listarea structurii unei tabele cu toate componentele ei (nume tabelă, nume şi
tip coloane, cheie primară, etc.) şi are sintaxa:
DESCRIBE < NUME_TABELA>;
Exemplu: DESCRIBE PERSONAL;
Modificarea structurii unei tabele (Comanda ALTER TABLE).
Comanda ALTER TABLE realizează modificarea structurii unei tabele, dar nu modifică
conţinutul ei.
Această comandă ( ALTER TABLE) permite:
- adăugarea (ADD) unei coloane într-o tabelă existentă;
- modificarea (MODIFY) coloanelor unei tabele;
- specificarea constrângerilor la nivel coloană;
- specificarea unei valori implicite pentru o coloană existentă;
- activarea şi dezactivarea (ENABLE/ DISABLE) unor constrângeri;
- schimbarea sau ştergerea (DROP) cheii primare, a cheii externe;
Comanda ALTER TABLE are una din urmatoarele forme:
ALTER TABLE nume-tabelă ADD (nume-coloană1, ..., nume coloanăN);
ALTER TABLE nume-tabelă MODIFY (nume-coloana1, ..., nume coloanăN);
Observaţie. Putem folosim ALTER TABLE pentru a adăuga sau renunţa la restricţiile
definite într-o tabelă. Constrângerile sunt înregistrate în dicţionarul datelor şi sunt de
forma:
nume-coloană numele unei coloane unde este aplicată restricţia;
NULL or NOT NULL activează sau anulează dreptul unei coloane de a avea valori
nule;
UNIQUE fiecare rând trebuie să aibă o valoare distinctă pentru coloană;
fiecare coloană trebuie să fie declarată NOT NULL, coloana
nu are voie să fie PRIMARY KEY
PRIMARY KEY specifică coloana ca fiind cheie primară;
FOREIGN KEY column, column... REFERENCES table column, column...
specifică coloana ca şi cheie străină a coloanei tabelei;
CHECK condition setează o condiţie, coloana trebuie să satisfacă această condiţie
pentru a exista în tabelă. Într-o coloană restricţia CHECK se
poate referi doar la coloana de care restricţia aparţine. Într-o
tabelă, clauza CHECK restricţie, se poate referi la coloane
multiple.
Restricţia FOREIGN KEY/REFERENCES:
- respinge INSERT sau UPDATE dacă nu există o valoare corespondentă în
cheia primară a tabelei;
- respinge DELETE dacă invalidează restricţiile REFERENCES;
- trebuie să facă referire la o coloană cheie primară sau unică în tabela cheii
primare;
- se va referi la cheia primară a tabelei dacă nici o coloană sau grup de coloane
nu este specifică t în restricţie;
- trebuie să facă referire la o tabelă, nu o viziune;
- nu restricţionează modul in care celelalte restricţii se pot referi la aceeaşi
tabelă;
- necesită coloana cheie primară şi coloana restricţionată să aibă aceleași tipuri
de date;
- poate referi aceeaşi tabelă numită în CREATE TABLE.
Cu ajutorul acestei comenzi se pot adăuga atribute, sau şterge atribute din structura unei
tabele. Când un atribut este adăugat, valorile pentru aceasta sunt NULL, dacă data nu este
o valoare default specificată în definirea coloanei.
Definirea vizualizărilor (Comanda CREATE VIEW...):
O vizualizare (view) este o tabelă logică relativă la datele conţinute în una sau mai
multe tabele sau vizualizări. Vizualizarea este definită pornind de la o cerere de
interogare de date şi fiind o tabelă virtuală nu solicită o alocare de memorie pentru date.
Ea este definită în dicţionarul datelor şi are caracteristicile unei tabele.
Sintaxa generală de creare a unei vizualizări este următoarea:
CREATE VIEW <nume _view > [(lista coloane)] AS <SELECT clause>;
Unele comentarii pe tabele
Adăugarea de comentarii la definiții
Pentru aplicațiile care includ numeroase tabele, este util să se adauge comenatrii la
definirea tabelelor ori comentarii pentru coloane. Un comentariu poate fi creat prin
comanda:
COMMENT ON TABLE <nume- tabela> IS ‟<text>‟;
Un comentariu pe o coloana poate fi creat prin comanda:
COMMENT ON COLUMN <tabela>.<coloana> IS ‟<text>‟;
Comentariile pe tabele şi coloane sunt memorate în dictionarul datelor. Ele pot fi accesate
utilizând vederi din dicționarul datelor prin comenzile USER TAB COMMENTS şi
USER COL COMMENTS .
Observații:
1. Tabelele se crează fără date şi se populeaza cu date cu ajutorul comenzii
INSERT. După crearea unei tabele, putem adăuga noi coloane, partiții şi
restricţii de integritate cu ajutorul clauzeii ADD a comenzii ALTER TABLE.
Se poate schimba definirea unei coloane existente sau a unei partiții cu
ajutorul clauzei MODIFY a comenzii ALTER TABLE.
2. Privilegiile sistemului ORACLE sunt grupate în doua categorii:
- Privilegii de sistem – privilegii privind accesul la baza de date şi la definitia obiectelor
sale. Comanda pentru acordarea privilegiilor de sistem are sintaxa:
GRANT {privilegiu_sistem |role}
[,{privilegiu_sistem | role }]…
TO {utilizator | role | PUBLIC}
{,utilizator | role | PUBLIC}…
[WITH ADMIN OPTION].
2.3. Limbajul de manipulare a datelor
După crearea unei tabele se pot efectua operații de inserare, ștergere sau modificare a
înregistrării în această tabelă. Prin manipularea datelor conţinute în baza de date se
înţelege efectuarea operaţiilor de actualizare, modificare şi consultare a conţinutului bazei
de date. SQL oferă patru comenzi care permit actualizarea înregistrărilor, modificarea
datelor şi consultarea conţinutului bazei de date, dupa cum urmeaza:
Comanda INSERT oferă posibilitatea adăugării de noi înregistrări într-o tabelă;
Comanda UPDATE asigură modificarea uneia sau mai multor valori dintr-o înregistrare;
Comanda DELETE se utilizează pentru ştergerea unor înregistrări dintr-o tabelă;
Comanda SELECT permite interogarea obiectelor create cu ajutorul LDD;
Cererile Limbajului de Manipulare a Datelor (LMD) pot fi formulate direct prin interfaţa
SQL*Plus sau pot fi utilizate în utilitarele sistemului SGBD ORACLE. Ele pot fi
încapsulate într-un program PL/SQL sau într-un program scris în limbajul gazdă. În
funcţie de momentul în care utilizatorul doreşte realizarea actualizărilor asupra BD se
poate folosi una din comenzile:
SET AUTOCOMMIT ON - actualizările se fac imediat;
SET AUTOCOMMIT OFF - schimbările sunt păstrate într-un buffer până la execuţia
uneia din comenzile:
COMMIT - care permanentizează schimbările efectuate;
ROLLBACK – determină renunţarea la schimbările realizate.
2.3.1. Adăugarea datelor în SQL ( Comenzi INSERT):
Instrucţiunile INSERT se folosesc în operaţia de introducere date în baza de date., şi sunt
de forma: INSERT …… VALUES, INSERT ……..SELECT.
Sintaxa generală a acestor instrucţiuni are forma:
INSERT INTO nume-tabela
(col1, col2, ….)
VALUES (val1, val2, …);
2.3.2. Comanda UPDATE
Instrucțiunea UPDATE – este folosită pentru modificarea valorilor din înregistrările
existente. Sintaxa generală a acestei comenzi este:
UPDATE nume-tabel SET nume-col1 = Val1 [, col2 = Val2,…]
WHERE conditie-de-cautare;
Instrucţiunea UPDATE verifică mai întâi condiţia de căutare şi pentru toate înregistrările
care verifică condiţia, valoarea corespunzătoare este actualizată.
2.3.2.Instrucțiunea DROP TABLE – nume-tabela;
Permite ştergerea completă a unei tabele dintr-o bază de date, inclusiv indecşi ataşaţi.
DROP TABLE Facturi-noi;
DROP DATABASE nume-baza –de –date;
2.3.3. Comanda DELETE
Ștergerea (suprimarea) înregistrărilor dintr-o tabelă (dar fără a elibera spaţiul) se
realizează cu ajutorul comenzii DELETE, care are urmatoarea sintaxa:
DELETE FROM nume-tabelă [alias] [WHERE condiţie];
2.3.5. Comanda SELECT – Interogarea bazelor de date:
I. Interogări simple – interogarea datelor conţinute într-o singură tabelă
Pentru regăsirea informaţiilor memorate în baza de date SGBD ORACLE se utilizează
limbajul de interogare SQL. În cele ce urmează ne vom concentra pe interogări simple
urmând ca să se reia discuția mai tarziu pentru interogarile complexe.
În SQL o interogare are următoarea formă (unde componentele din parantezele [ ] sunt
opționale, iar Comanda se incheie prin ;).
SELECT [distinct] <column(s)>FROM <TABLE>[ WHERE <condition> ]
[ order by <column(s) [asc|desc]> ];
a) Selectarea coloanelor
Pentru a fi selectate coloanele dintr-o tabela ele sunt specifică te prin cheia de selecție.
Această operație se mai numește şi proiecție. De exemplu, interogarea
SELECT Loc, Cod_Dep FROM DEPART;
b) Selectarea înregistrărilor
Până acum am văzut cum se selectează atributele (coloanele) unei tabele. Pentru a selecta
numai anumite înregistrări care satisfac anumite condiții, aceste condiții se specifică prin
clauze care se referă la înregistrări. În clauzele de selecție pot apărea operatori de
comparație, operatori logici, sau chiar subinterogari şi clauze
Examplu: Să se listeze funcţia şi salariile numai salariaților pentru care codul șef proiect
(CodSP) este 7698 or 7566 şi castigă mai mult de 1500:
SELECT Funcţia, Salar FROM PERSONAL
WHERE (CodSP = 7698 or CodSP = 7566) AND Salar > 1500;
c) Accesarea tabelelor altor utilizatori.
Presupunem că un utilizator are privilegiile de acces la tabelele altui utilizator. El poate
referi aceste tabele în cererile sale. Fie <user> un utilizator SGBD ORACLE şi
<nume_tabela> o tabela a acestui utilizator. Această tabelă poate fi accesată de alți
utilizatori (privilegiați) cu ajutorul comenzii:
SELECT * FROM <user>.<nume_tabela>;
Se recomandă să se foloseasacă un sinonim în loc de <user>.<nume_tabela>. În SGBD
ORACLE-SQL un sinonim poate fi creat prin comanda:
CREATE SYNONYM <nume> for <user>.<nume_tabela> ;
Este posibil să se utilizeze numai <nume> în clauza FROM. Sinonimele pot fi
deasemenea create şi pentru tabelele proprii.
d) Operații şi mulțimea rezultatelor
Uneori este utilă o combinație a rezultatelor a două sau mai multor cereri într-un singur
rezultat.
SQL pune la dispozitie trei seturi de operatori care au acelasi sablon:
<interogare 1> <set operator> <interogare 2>
Setul de operatori este urmatorul:
• union [all] - returnează o tabelă care conţine toate rândurile care apar fie în rezultatul
cererii <interogare 1> fie în cele ale cererii <interogare 2>. Duplicările de înregistrări
sunt automat eliminate, în afara cazului în care se utilizează clauza all;
• intersect – returnează toate înregistrările care apar în ambele cererii (<interogare 1> şi
<interogare 2>;
• minus – returnează toate înregistrările care apar în rezultatul <interogare 1>
dar nu se afla în rezultatul cererii <interogare 2>;
e) Operatii asupra sirurilor
Pentru a compara conţinutul unui atribut cu un sir, trebuie ca șirul să fie scris între
apostroafe.
Exemplu : WHERE LOCATION = ‟DALLAS‟.
Împreună cu operatorii mai pot fi utilizate şi două caractere speciale: % şi _ (caracter de
subliniere). De exemplu, dacă dorim să extragem toate înregistrările din tabela DEPART
care conțin două caractere C în numele departamentului, condiția va fi :
WHERE CodDept LIKE ‟%C%C%‟.
Semnul % precizează șirul care este căutat, chiar dacă șirul este gol.
În contrast semnul _ semnifică înlocuirea unui singur caracter. De exemplu condiția :
WHERE CodDept LIKE ‟%C_C%‟
arată că cererea se referă numai la un singur caracter care poate să apară între cei doi C.
Asupra șirurilor mai sunt disponibile următoarele operații:
• upper(<sir>) transformă toate lierele din șir în litere mari;
• lower(<sir>) transformă toate lierele din șir în litere mici;
• initcap(<sir>) transformă prima litera din șir ( <sir>) în litera mare.
• length(<sir>) returnează lungimea șirului;
• substr(<sir>, n [, m]) extrage m caractere din șir începând cu poziția n;
Dacă nu se sepcifică m se consideră sfârșitul șirului
Exemplu: substr(‟DATABASE SYSTEMS‟, 10, 7) returnează șirul ‟SYSTEMS‟.
f) Funcţii agregate
Funcţiile agregate sunt funcţiile statistice: COUNT, MIN, MAX etc. Ele pot fi utilizate
numai asupra valorilor unei coloane.
Exemplu: Câte tupluri sunt memorate în tabela PERSONAL?
SELECT COUNT(*) FROM PERSONAL;
Exeamplu: Câte funcţii diferite sunt memorate în tabela PERSONAL?
SELECT COUNT(DISTINCT Funcţia) FROM PERSONAL;
Exemplu : listați salariul minim şi maxim:
SELECT MIN(Salar), MAX(Salar) FROM PERSONAL;
Exemplu: Calculați diferența dintre salriul minim şi salariu maxim:
SELECT MAX(Salar) - MIN(Salar) FROM PERSONAL;
Funcţia SUM se aplică numai asupra datelor de tip numeric.
Exemplu:: Calculați suma salariilor din departamentul 30:
SELECT SUM(Salar) FROM PERSONAL
WHERE CodDept = 30;
Funcţia AVG calculează valoarea medie pentru o coloană de tip numeric:
Obs: Funcţiile AVG, MIN şi MAX ignoră înregistrările care au valoare nulă, dar funcţia
COUNT le consideră (numără).
Procesul de căutare şi obţinere a datelor şi informaţiilor dintr-o bază de date se numeşte
interogarea bazei de date. În limbajul SQL formularea unei interogări înseamană
redactarea unei fraze SELECT în care se apelează un număr de clauze prin care se
exprimă solicitarea de date dorită. Ne reamintim sintaxa generală a unei comenzi
SELECT şi precizarea că într-o Comanda SELECT sunt obligatorii clauzele SELECT,
FROM şi nume_tabelă:
SELECT [ALL|DISTINCT] {* | lista de SELECTAT}
FROM lista tabele
[WHERE condiţie]
[GROUP BYlista expresii [HAVING condiţie]]
[ORDER BY {expresie |poziţie} [ASC|DECS]
[,{expresie|poziţie} [ASC |DESC] ]…];
g) Grupări
În paragraful anterior am vazut cum funcţiile agregate pot fi utilizate pentru a calcula o
valoare pentru o anumită coloană. Deseori se cere gruparea înregistrărilor care au aceleași
proprietăți şi apoi să aplicăm funcţiile agregate pentru o coloană aparținând unui grup
anume.
Pentru aceasta, SQL pune la dispoziție clauza: <GROUP BY COLUMN(S)>. Această
clauză apare după clauza WHERE şi trebuie să se refere la coloanele tabelelor listate în
cluaza FROM şi are sintaxa:
SELECT <column(s)> FROM <table(s)>
WHERE <conditi> GROUP BY<grup coloane)> [HAVING <grup condiții>];
Prin acest procedeu se regasesc şi se grupează acele înregistrări care au aceași valoare
pentru <grup coloane>. Agregarea specificată în clauza SELECT este aplicată separat
fiecărui grup. Este important ca numai acele coloane care apar în clauza “group
column(s)” pot fi listate fară ca funcţia de agregare în clauza SELECT.
h) Implementarea operatorilor de selecţie şi proiecţie
În general o interogare a bazei de date poate fi scrisă sub forma:
SELECT câmp1, câmp2,, ..., câmpn FROM Fis1, Fis2,..., Fism WHERE condiţie;
În urma execuţiei acestei comenzi se obţine un rezultat sub forma unei liste afişată pe
display, sau a unei tabele, sau a unei tabele temporare. Dacă se doreşte eliminarea
înregistrărilor identice din rezultatul interogării atunci se utilizează în fraza SELECT
opţiunea DISTINCT şi comada de selecţie în acest caz devine:
SELECT DISTINC câmp1, câmp2,, ..., campn FROM Fis1, Fis2,..., Fism
WHERE condiţie;
Analizând efectele clauzelor din Comanda SELECT, remarcăm faptul că interogarea
corespunde:
- unei selecţii de înregistrari din tabelele suport ale interogării, înregistrări care satisfac
condiţiile de selecţie exprimate prin clauza WHERE;
- unei proiecţii, adică reţinerea în rezultatul interogarii numai a câmpurilor specifică te în
lista de câmpuri ale comenzii SELECT;
- unui produs cartezian format din mulţimea înregistrărilor conţinute în tabelele
menţionate în clauza FROM (FROM Fis1, Fis2, ..., Fisn);
Pentru a redacta într-un proiect operaţiile de selecţie şi proiecţie folosite în cadrul
interogărilor se folosesc notaţiile:
R1 SELECŢIE (FURNIZORI, DenF = „ Furnizor x SA‟)
R2 PROIECŢIE (R1, Judet)
Precizări privind utilizarea operatorilor de selecţie şi proiecţie:
- Utilizarea instrucţiunilor SELECT cu clauza FROM pentru:
o Selectarea şi listarea integrală a unei tabele
o Selectarea şi listarea numai a anumitor coloane dintr-o tabelă
o Selectarea şi listarea numai anumite linii dintr-o tabelă
Expresii:
O expresie este formată din variabile, constante şi operatori.
Exemplu . Fie baza de date PERSONAL (Nume, Prenume, Data-n, Adresa,
Salar,….). In comanda urmatoare se exemplifica conceptul de expresie.
SELECT (Prenume +‟ „ +Nume) WHERE Nume = „STAN‟
FROM PERSONAL;
Condiţii:
Condiţiile fac posibile interogările selective, adică permit returnarea numai a acelei
submulţimi de înregistrări care satisfac condiţia specificată. În mod frecvent condiţiile
sunt exprimate prin clauza WHERE a comenzii SELECT şi apar sub forma:
WHERE < condiţie de căutare>
Exemplu: Precizaţi efectul comenzilor:
SQL> SELECT * FROM PERSONAL; (Efect ?)
SQL> SELECT *
FROM PERSONAL
WHERE Nume=‟Stan‟; (Efect ?)
Operatori:
Operatori aritmetici: +; -; /; *; modulo(%); (returnează restul împărţirii)
Exemplu : 5%2=1 sau 6%2=0
În multe cazuri pentru a da o formă mai profesională (o formă elegantă şi eficientă) a
cererilor se utilizează conceptul de alias. Spre exemplu să urmărim folosirea în sintaxa
unei instrucţiunii a unui nume-coloană alias.
SQL > SELECT Nume, Preţ-cost, (Preţ-cost*1.25) Preţ-vanzare
FROM PRODUSE;
(Atenție ! Caracterul spaţiu dintre atribut şi aliasul său este obligatorie)
Operatori de comparaţie:
În algebra relaţiilor se pot utiliza operatorii de comparaţie. Valori obţinute prin
evaluarea acestui operator sunt: TRUE; FALSE; UNKNOWN
Exemplu. Fie tabela PRODUSE(Nume, Preţ-unitar, Preţ-vânzare,….), care conţine o
înregistrare în care Preţ-unitar este NULL. Care este semnificaţia comenzilor:
SQL>SELECT * FROM PRODUSE;
SQL> SELECT *FROM PRODUSE WHERE Preţ-unitar IS NULL;
SQL> SELECT * FROM PRODUSE WHERE Preţ-unitar <10000;
SQL > SELECT * FROM PRODUSE
WHERE Nume LIKE „%TABLA‟;
Comanda de mai sus are ca efect listarea tuturor înregistrăilor care au în coloana
Nume o expresie care conţine șirul de caractere TABLA; De remarcat următoarele
detalii:.
1. „TABLA%‟ - listează toate înregistrarile care în coloana Nume începe cu TABLA;
2. LIKE face diferenţiere între literele mari şi literele mici.).
Operatori de subliniere: Fie tabela : STUDENŢI (Nume, Iniţiala, Prenume, Telefon,….). Precizați
semnificațiile comenzilor:
1) SQL> SELECT * FROM STUDENŢI;
2) SQL >SELECT * FROM STUDENŢI WHERE Iniţiala LIKE „C_„;
3) SQL> SELECT * FROM STUDENŢI WHERE Telefon „555_6_6‟;
4) SQL> SELECT * FROM STUDENŢI WHERE Telefon „555_6%‟;
Operatori logici: AND; OR; NOT;
Exemplu: Fie tabela PERSONAL (Nume, Marca, Vechime, Zile-concediu). Care
este semnificaţia instrucţiunii:
SQL> SELECT Nume, Vechime, Zile-concediu
FROM SALARIATI
WHERE Nume LIKE „B%‟ANDZile-concediu >12;
Operatori pentru mulțimi: UNION şi UNION ALL
Operatorul UNION implementează operaţia de reuniune a două tabele care sunt
compatibile. Operatorul UNION ALL realizează de asemenea reuniunea a două tabele,
dar fără eliminarea dublurilor. UNION combină două sau mai multe tabele. Pentru a
realiza operaţiunea de UNION, tabelele implicate trebuie să fie compatibile (acelaşi
număr de coloane şi coloanele corespondente din acelaşi domeniu). UNION este precum
adunarea (TABELA1+TABELA2) UNION – returnează rezultatele a două interogări,
mai puţin înregistrările duplicate.
Sintaxa generală a operatorului are una din formele:
1. SQL> SELECT Nume FROM FIS1
UNION
SELECT Nume FROM FIS2;
Efectul acestei comenzi constă în returnarea valorilor câmpului Nume dinstincte din
cele două tabele.
2. SQL > SELECT Nume FROM FIS1
UNION ALL
SELECT Nume FROM FIS2;
Această comandă are ca efect returnarea tuturor valorilor câmpului Nume dinstincte
din cele două liste, fara a elimina duplicatele.
Observatii:
1.Prin definiţie, rândurile duplicate sunt înlăturate din reuniune.
2.Dacă opţiunea ALL este. inclusă, rândurile duplicat sunt păstrate
Tema. Care este efectul comenzii de mai jos:
SELECT CodTranz,, DenP, Val, DataScadentă FROM CONTRACTE
WHERE DataScadentă < SYSDATE
UNION
SELECT CodTranz,, DenP, Val, DataScadentă FROM CONTRACTE
WHERE Val > 10000;
Operatorul INTERSECT – returnează numai liniile găsite de ambele interogări:
INTERSECŢIA combină două tabele şi păstrează doar tabelele comune. Putem considera
o propoziţie logică de tip AND (să se extragă toate înregistrările din prima tabelă care
sunt comune cu înregistrările din a două tabele). Operatorul INTERSECT realizează
intersecţia a două tabele compatibile.
Sintaxa comenzii este:
SQL> SELECT *FROM FIS1
INTERSECT
SELECT * FROM FIS2;
Comanda are ca rezultat listarea liniilor comune din tabele FIS1 şi FIS2.
Exemplu: Să se listeze produsele care au o valoare în stoc mai mică de 100 şi au fost
contractate de cel puţin 8 clienţi:
SELECT CodP, DenP, FROM PRODUSE
WHERE Cant < 1000
INTERSECT
SELECT CodP, DenP, FROM CONTRACTE
GROUP BYCodP
HAVING COUNT (*) > 8;
Operatorul MINUS returnează înregistrările din prima interogare care nu fac parte
din a doua interogare.
Operatorul MINUS implementează operaţia de diferenţă din algebra relaţională. Acest
operator returnează doar rândurile ce se află în prima tabelă dar nu şi în a doua. O putem
considera o INTERSECŢIE cu propoziţia NOT. Considerăm aceast operator ca opusul
operatorului INTERSECT.
Sintaxa comenzii este:
SQL> SELECT * FROM FIS1
MINUS
SELECT * FROM FIS2;
Exemplu : . Să se afişeze codurile produselor care nu au fost contractate.
SELECT CodP FROM PRODUSE
MINUS
SELECT DISTINCT CodP FROM CONTRACTE;
Operatorul BETWEEN
Operatorul BETWEEN se utilizează pentru definirea intervalelor de valori.
Exemplu : Să se afişeze facturile emise în perioada 1.08.2009 până în 15.09. 2009.
SELECT * FROM FACTURI
WHERE DataFact BETWEEN TO_DATE („01/08/09‟, „DD/MM/YY‟)
AND TO_DATE („15/09.09‟. „DD/MM/YY‟);
Același rezultat se poate obține şi prin comanda echivalentă cu cea anterioară:
SELECT * FROM FACTURI
WHERE DataFact BETWEEN {01/08/09}AND{15/09.09};
Operatorul LIKE:
Operatorul LIKE permite compararea unui atribut (expresii) cu un literal utilizând o
mască cu ajutorul semnelor %, _.
Exemplu: . Care din lista firmelor din tabela Clienţi sunt firme de tip SRL?
SELECT * FROM CLIENŢI
WHERE DenCl LIKE „%SRL‟;
Operatorul IN:
Operatorul IN permite căutarea unei valori într-o listă.
Exemplu:. Care sunt localităţile din judeţele Braşov şi Sibiu
SELECT * FROM LOCALITATI
WHERE Jud IN („BV‟, „SB‟);
Implementarea operatorului JOIN (operator de compunere):
Analizând cu atenţie efectele operatorului produs cartezian observăm că obţinem o
fuziune necondiţionată a două tabele. Dacă dorim să extragem din două tabele numai
înregistrările care care au o proprietate comună atunci folosim operatorul de joncţiune.
În esență joncţiunea este echivalentă unui produs cartezian urmat de o selecţie. Un
operator join reprezintă o legatură între două tabele, care au cel puţin o coloană comună.
Operatorul join permite crearea unei tabele temporare care conţine liniile care satisfac
condiţia de join. Atunci când se utilizează operatorul join se recomandă ca tabela care are
linii mai puţine să fie a doua în operația de compunere. Pentru ca durata de execuţie a
operatorului să fie mai mică atunci indexăm această tabelă după coloana precizată în
compunere.
Pentru a manipula datele între mai multe tabele se foloseşte operația de Joncțiune.
Operaţiile de joncţiune “lipesc” tabelele. Există următoarele tipuri de joncţiuni: Joncţiuni
externe; Joncţiuni stânga; Joncţiuni dreapta; Echi-joncţiunea; Non-echi-joncţiunea; Auto-
joncţiunea
Prin clauza JOIN se poate specifica calea de acces la date. Daca numele
atributelor sunt duplicate în cele două tabele legate, pentru fiecare atribut trebuie să se
specifice tabela care îl conţine.
a) Echi-joncțiunea (joncţiunea echivalentă)
Temă:
a) Să se listeze înregistrările din tabela COMPONENTE care au înregistrări
echivalente în tabela COMENZI
b) Să se listeze toate comenzile care se referă la “BICICLETE CARPAŢI”
c) Să se calculeze suma încasată din vânzarea biciletelor CARPAŢI;
d) Să se genereze factura pe clienți.
Non – echi- jonctiuni
Exemplu:
SQL > SELECT O.Nume, O.Cod-C, P.Cod-C, O.Cantitate*P.Preţ TOTAL, P.Descriere
FROM COMENZI O, COMPONENTE P
WHERE O.Cod-Cl >P.Cod-Cl;
Joncţiuni externe
Nu mai combină toate liniile unui tabel cu ale altui tabel şi filtrează numai cele dorite.
Exemple:
a) SELECT P.Cod_c, P.Denumire, P.Preţ
O.Nume, O.Cod-C
FROM COMPONENTE P
JOIN COMENZI O ON COMENZI.Cod_c =54;
Analiză: Rezultatul este format din toate liniile din tabela COMPONENTE îmbinate cu
liniile specificate din tabela COMENZI în care Cod_c=54;
Auto-joncţiunea (joncţiunea tabelei cu ea insasi)
Se utilizeză pentru a elimina înregistrările duble.
SELECT F.Cod-C, F. Denumire
S. Cod-C, S. Denumire
FROM COMPONENTE F, COMPONENTE S
WHERE F. Cod-C = S. Cod-C
AND F. Denumire < > S. Denumire;
Crearea de scripturi (Macro): Un script este un set de comenzi SQL salvate ca şi un fişier. Scripturile sunt utilizate
pentru a stoca interogări sau alte comenzi ce sunt folosite în mod des. Pentru a crea un
script este utilizat orice editor de text sau procesor word. Dacă este utilizat un procesor
word, scriptul se salvează în mod TEXT dar extensia trebuie să fie SQL.
Pentru a crea un un fişier script de la prompterul SQL se introduce comanda EDIT
<nume_fişier> unde nume_fişier este numele fişierlui script. Aceasta va porni editorul.
Se introduc comenzile SQL necesare ca şi cum ar fi introduse în SQL. Se salvează
fişierul cu extensia SQL.
Pentru a rula un script se foloseste START < nume_fişier > ca şi comanda SQL. Se
introduce numele fişierului. Dacă fişierul are extensie SQL, nu este necesară ca aceasta să
fie scrisă.
Definirea variabilelor în SQL:
Pentru a defini o variabilă se foloseşte o comanda care are construcția de forma:
ACCEPT variabilă PROMPT mesaj. Partea ACCEPT a comenzii permite introducerea
unei valori pentru variabilă. Partea PROMPT este afişată pe ecran ca şi prompt pentru
valoarea variabilei.
Funcţii SQL şi funcţii agregate SQL: SQL acceptă urmatoarele tipuri de funcţii: funcţii totalizatoare, funcţii pentru data
calendaristică şi ora, funcţii matematice, funcţii de tratare a şirului de caractere, funcţii
diverse, funcţii de agregare.
Funcţii totalizatoare:
Fie baza de date: PERSONAL (Marca, Nume, Loc_Munca, Nr_Copii, Salar,
Telefon,….)
Funcţia COUNT – returnează numărul total de linii care respectă clauza WHERE
Exemple: Să se listeze numărul de salariaţii care au salariul mai mare decât 600:
a) SQL> SELECT COUNT (*)
FROM PERSONAL
WHERE SALAR > 600;
Rezultat: Ieşire:
COUNT (*)
……………
4
Funcţia SUM – returnează suma tuturor valorilor dintr-o coloană:
Exemple: a) Să se calculeze salariul total al angajaților:
SQL > SELECT SUM (SALAR) TOTAL-SALAR
FROM PERSONAL;
Se obține rezultatul:
TOTAL-SALAR
……………………..
15027
Observații: Se pot utiliza mai multe operaţii de sumare în aceeaşi comandă:
Funcţia AVG – calculează valoarea medie a unei coloane:
Exemplu:
SQL > SELECT AVG (SALAR) SALAR-MEDIU
FROM PERSONAL;
Rezultat - ieşire:
SALAR-MEDIU
……………………..
480
Funcţia MAX – găseşte şi afişează valoarea cea mai mare dintr-o coloană:
Exemplu:
SQL > SELECT MAX (SALAR) TOTAL-SALAR
FROM PERSONAL;
Rezultat- ieşire:
MAX(SALAR)
……………………..
850
Observatie: Funcţia MAX nu operează într-o clauza WHERE:
Funcţia MIN – returnează cea mai mică valoare dintr-o coloană:
Exemplu:
SQL > SELECT MIN (SALAR), MAX(Nr-Copii)
FROM PERSONAL;
Rezultat - ieşire:
MIN(SALAR) MAX(Nr-Copii)
……………………..
290 4
Funcţia VARIANCE – calculează şi afişează dispersia valorilor dintr-o coloană:
Exemplu:
SQL > SELECT VARIANCE (SALAR)
FROM PERSONAL;
Rezultat- ieşire:
VARIANCE (SALAR)
……………………..
622.5
Observaţie: Funcţia VARIANCE se aplică numai valorilor numerice.
Funcţia STDDEV – calculează şi afişează abaterea (deviaţia) standard:
Exemplu:
SQL > SELECT STDDEV(SALAR)
FROM PERSONAL;
Rezultat:-ieşire:
STDDEV(SALAR)
……………………..
350
Funcții pentru data calendaristică şi ora:
Fie baza de date: PROIECTE (Nume, Data-Start, Data-Finala, …)
a) Funcţia ADD-MOUNTHS – adaugă un număr de luni la data curentă;
Funcţia LAST-DAY – afişează ultima zi a lunii specifică te;
Funcţia MONTHS-BETWEEN – afişează numărul de luni dintre luna X şi luna Y;
Funcţia SYSDATE – afişează ora şi data calendaristică a sistemului;
Tema : 1. Să se adauge 2 luni la data finală a proiectului:
SQL > SELECT Nume
Data-Start,
Data_finala Sfarsit
ADD-MOUNTHS (Data-Finala,2)
FROM PROIECTE;
2. Să se afişeaze stadiul în care se află activităţile din proiect la data curentă (stadiul
lucrărilor).
SQL > SELECT *
FROM PROIECTE
WHERE DATA-START > SYSDATE;
Funcții matematice:
Fie baza de date: NUMERE (A,B), unde A şi B sunt numere reale.
a) Funcţii aritmetice
ABS – afişează valoarea absolută din argument
Exemplu:
SQL > SELECT ABS(A) VAL-ABSOLUTA
FROM NUMERE;
CEIL - afişează cel mai mare număr întreg mai mare sau egal cu argumentul
FLOOR - afişează cel mai mic număr întreg mai mic sau egal cu argumentul
Exemplu:
SQL > SELECT B, CEIL(B)
FROM NUMERE;
b) Funcţii trigonometrice : COS, SIN, SINH, TAN, TANH – gradele trebuiesc
convertite în radiani.
Exemplu:
SQL > SELECT A, COS(A*0.0174)
FROM NUMERE;
c) Funcţii logaritmice: EXP, LN, LOG
Exemplu:
SQL > SELECT A, EXP(A)
FROM NUMERE;
d) Funcţiile: POWER, SIGN, SQRT
Exemplu:
SQL > SELECT A, B, POWER(A, B)
FROM NUMERE;
Funcţii de tratare a şirului de caractere:
a) Funcţia CHR (COD) – returnează caracterul echivalent numeric folosit în
argumentul funcţiei;
Exemplu:
SQL > SELECT COD, CHR(COD)
FROM CARACTERE;
Vezi tabela ASCII: COD CHR(COD)
67 C
68 D
87 W
b) Funcţia CONCAT - Concatenează (lipeşte) două şiruri
Exemplu:
SQL > SELECT COCAT (PRENUME, NUME) NUMELE
FROM CARACTERE;
i) Funcţia INITCAP –
Această funcţie formatează șirul de caractere dintr-un cuvânt şi are ca
efect: scrie prima litera din cuvant litera mare şi celelalte litere mici.
Exemplu:
SQL > SELECT PRENUME , INITCAP(PRENUME)
FROM CARACTERE;
Sir caractere
initial
Sir caractere final
ION Ion
VICTOR Victor
ANDREI ANDrei
j) Funcţiile: LOWER şi UPPER –
Aceste funcţii au următorul efect: formatează un cuvânt, transforma toate
literele din sir în litere mici şi respectiv, toate literele din șir în litere mari.
e) Funcţia REPLACE (arg1, arg2, arg3), unde: arg1 este şirul care trebuie căutat, arg2
este cheia de căutare şi arg3 este şirul de înlocuire.
Funcţia REPLACE are ca efect inlocuirea sirului cautat cu sirul propus pentru
inlocuire.
Observaţie: Dacă al treilea argument lipseşte sau este NULL, toate secvenţele identice cu
cheia de căutare din şirul respectiv sunt eliminate.
Exemplu:
SQL > SELECT NUME REPLACE (NUME, „ST‟) INLOCUIRE
FROM CARACTERE;
NUME ÎNLOCUIRE
IONESCU IONESCU
CONSTANTIN CONANTIN
Funcţii diverse:
USER - returnează informaţii despre sistem (adică numele curent al bazei de date)
Exemplu:
SQL > SELECT USER
FROM CONVERSIE;
Funcţii de agregare: (Clauzele utilizate în Comanda SELECT)
Clauze SQL: WHERE, STARTING WITH, ORDER BY, GROUP BY, HAVING
Sintaxa generală a instrucţiunii SELECT este:
SELECT [DISTINCT | ALL] {* | Veder..}
FROM Tabela
[WHERE cANDitie]
[GROUP BYexpresie…. [HAVING conditie]]
[{UNION | UNION ALL…}]
[ORDER By {expresie}];
a) Clauza WHERE – oferă posibilitatea de exprimare a unui filtru de selecţie.
SGBD ORACLE suportă toţi operatorii de comparaţie (<, =, >, <=, =>), alături de
operatorii logici AND, OR şi NOT. Dacă este folosit NOT, comparaţia ar trebui închisă
în paranteze.
Clauza STARTING WITH:
1). SQL > SELECT NUME, SUMA, OBS
FROM BONURI
WHERE NUME STARTING WITH („Ca‟);
Clauza ORDER BY:
Pentru a ordona tupluri după un atribut specificat, trebuie folosită clauza ORDER BY.
Tuplurile pot fi ordonate ascendent sau descendent. În mod implicit, ordonarea este
ascendentă. Pentru a obţine o ordonare descendentă, este necesară includerea atributului
DESC după numele câmpului în clauza ORDER BY. Următoarele instrucţiuni selctează
toate atributele şi sortează după numele angajatului în secvenţă ascendentă.
Clauza GROUP BY- permite sumarea unui câmp după un anumit criteriu:
Este folosită de regulă cu funcţii precum COUNT, AVG, SUM. Returnează exact un rând
pentru fiecare valoare unică a coloanei utilizate în clauza GROUP BY. Când este
utilizată, orice atribut folosit trebuie specificat şi în propozitia SELECT sau clauza
DISTINCT. Funcţia este aplicată grupului.
1). SQL > SELECT Departm, AVG(Salar)
FROM PERSONAL
GROUP BYDepartm;
2). SQL > SELECT Funcţia, SUM(Salar)
FROM PERSONAL
GROUP BYFuncţia;
3). SQL > SELECT NUME, SUM(SUMA)
FROM BONURI
GROUP BYNUME;
Clauza HAVING:
Clauza HAVING este de asemenea acceptată. Trebuie utilizată cu o clauză de grup şi
testează rezultatele unei funcţii de grup. Rândurile sunt raportate doar dacă trec condiţia
în clauza HAVING.
Exemple:
1). SQL > SELECT Funcţia, COUNT (*)
FROM PERSONAL
WHERE Sal > 3000
GROUP BYFuncţia
HAVING COUNT(*) > 10;
Atentie! Clauza WHERE nu operează cu funcţii totalizatoare.
2). SQL > SELECT SECTIE, AVG(SALAR)
FROM SALARII
GROUP BYSECTIE
HAVING AVG(SALAR) < 3800000;
Exerciţii cu funcţii de grup: 1. Să se afişeze numărul produselor distincte contractate.
Rezolvare:
SQL > SELECT COUNT (DISTINCT CodP, DenP)
FROM CONTRACTE;
II. Cereri complexe – interogari care au ca suport datele din mai multe tabele:
În paragrafele anterioare s-au tratat numai cererile care se referă la o singură tabelă. De
aceea condițiile din clauza WHERE erau simple comparații. O facilitate majoră a bazelor
de date relaţionale constă în combinarea înregistrărilor memorate în diferite tabele pentru
a obține informaţii mai multe şi mai complete. În SQL comanda SELECT este utilizată
pentru formularea cererilor din mai multe tabele sub forma:
SELECT [distinct] [<alias 1>.]<coloaqna i>, . . . , [<alias j>.]<coloana j>
FROM <TABLE 1> [<alias a1>], . . . , <TABLE n> [<alias an>]
[WHERE <condition>];
Specificarea alias-urilor tabelelor în clauza FROM este necesară pentru referirea
coloanelor care au același nume din diferite tabele. De exemplu CodP apare şi în tabela
PERSONAL şi în tabela DEPART. Dacă dorim să ne referim la aceste coloane în
clauzele WHERE sau în comanda SELECT, un alias al tabelei trebuie specificat şi pus în
fața numelui coloanei. Se poate utilize şi numele tabelei, dar uneori acest nume este prea
mare şi conduce la o formulare prea lungă a cererii.
În acest sens vom exemplifica prin prezentarea unor cazuri concrete:
Funcţii de grup:
De multe ori pentru a obține rezultatul unei interogări trebuie să apelam la funcţiile de
grup. Sintaxa generală în astfel de cazuri este:
SELECT [domeniu] [funcţie-agregata(nume-camp) AS alias [lista-selectie]
FROM nume-tabela1, nume-tabela2,….
GROUP BYcamp-de-agregare
[HAVING criteriu-de grupare]
[ORDER BY campuri-criteriu {ASC/DESC]];
Exemplu: Să se listeze clienţii răi platnici care au acumulat o valoare a facturilor
neachitate > 20 milioane;
SELECT den-client, SUM([Val-neachitata]) AS Total
FROM Creante
GROUP BYden-client
HAVING SUM (Val-neachitate) > 20000000;
Relatii de tip Joining:
Comparațiile în clauza WHERE sunt utilizate pentru a combina rândurile din tabelele
listate în clauza FROM.
Exemplu: În tabela PERSONAL numai numărul departamentului este memorat şi nu
denumirea lui. Dacă dorim să regăsim numărul şi numele departamentului unde lucrează
salariații vom scrie comanda:
SELECT Nume, P.CodDept, D.Nume FROM PERSONAL P, DEPART D
WHERE E.P.CodP = D.CodP AND Funcţia = ‟Analist‟;
Subinterogări:
a) Construirea unei subinterogări:
O subinterogare permite să se transmită setul de rezultate al unei interogări la o altă
interogare.
Fie baza de date formata din tabelele COMPONENTE şi COMENZI care au urmatoarea
structură:
COMPONENTE (Cod_c, Denumire, Pret)
COMENZI (Data-C0m, Nume-Cl, Cod-C, Cantitate, Achitat)
Se cere să se extragă din baza de date şi să se listeze câmpurile Data-Com, Cod_c,
Denumire, Cantitate, Achitat, pentru toate înregistrările care se referă la BICICLETA
CARPATI.
SELECT O.Data-Com, O.Cod-C, P. Denumire, O. Cantitate, O. Achitat
FROM COMENZI O, COMPONENTE P
WHERE O.Cod-C = P.Cod-C
AND
O.C0d-C =
(SELECT Cod-C
FROM COMPONENTE
WHERE Denumire = „Biciclete carpati‟);
Observaţie: Pentru a evita erorile din subinterogări trebuie să ne asigurăm de unicitatea
valorilor memorate în câmpul de căutare.
b) Folosirea funcţiilor totalizatoare în subinterogări:
Temă: Să se găsească valoarea medie a unei comenzi:
SELECT AVG (O.Cantitate *P.Pret)
FROM COMENZI o, COMPONENTE P
WHERE O.Cod-C = P.Cod-C;
c) Imbricarea subinterogărilor:
Temă: Avem baza de date de mai sus. Să se listeze comenzile mai mari decât valoarea
medie.
SELECT ALL C.Nume, C.Adresa, C.Judet, C. Cod-Postal
FROM CLIENTI C WHERE C.Nume IN
(SELECT O.NUME FROM COMENZI O, COMPONENTE P
WHERE O.Cod-C = P.Cod-C
AND
O.Cantitate *P.Pret > (SELECT AVG (O.Cantitate *P.Pret)
FROM COMENZI O, COMPONENTE P WHERE O.Cod-C = P.Cod-C));
EXEMPLU - TEST:
În baza de date a firmei “SOFT” se memorează atributele:
Cod_Proiect(N3), Den_Proiect(C30), ID-APrg (N), Nume-APrg(C20),
Prenume-APrg(C20), Limbaj-Prg(C15), Data-Angaj(D8), Funcţia(C10), Salar(N6),
Tip_Proiect(C20), Cod-Etapa(C2), Den_Etapa(C18), Nr-Ore-Etapa(N4), Val-Etapa(N6),
Nr-Ore-Proiect(N8), Val-Proiect(N8), Val-Ore-Etapa(N6), Nr_Contract(N4),
Cod_Cl(N4), Nume_Client(C25), Cont_Cl(C12), Data-Contract(D8),
Termen_predare(D8), Val-Contract(N8).
Se cere:
1. Creaţi şi scrieţi relaţiile bazei de date utilizând metoda prescurtată; Aduceţi
relaţiile acestei baze de date în FN3;
2. Trasaţi diagrama entitate-asociere.
3. Baza de date “SOFT” este utilizată pentru aplicaţii multi-user realizate în SQL. Se
cere:
1. Să se creeze tabela SALARIATI;
2. Să se adauge în tabela SALARIATI înregistrările de mai jos şi să se listeze această
tabelă, sortată după câmpul Cod_Proiect;
ID-APrg Nume-APrg Prenume-APrg Data -_Angaj Cod- Proiect Limbaj Salar (C3) (C20) (C20) (D8) (N3) (C15) (N6)
305 Ionescu Adrian 7/15/98 101 Java 4000
174 Popescu Maria 11/20/04 304 C++ 2500 578 Nanu Oana 8/26/05 101 VB 2000
321 Popa Ion 2/25/01 304 C++ 3500
3. Să se anuleze tranzacţia anterioară şi să se elimine fişierul index realizat;
4. Să se adauge câmpul Achitat în tabela CONTRACTE şi să se memoreze valoarea DA
pentru toate înregistrările din tabelă;
5. Să se calculeze valoarea medie a CONTRACTELOR şi să se listeze toate
CONTRACTELE care au valori mai mari decât valoarea medie;
6. Să se calculeze valoarea totală a tuturor CONTRACTELOR;
7. Să se numere câte CONTRACTE au avut valori mai mari decât valoarea medie;
8. Eliminaţi dublurile din tabela CONTRACTE.
2.4. Limbajul pentru controlul accesului la date (LCD)
O tranzacţie este o unitate logică de lucru, o secvenţă de comenzi, care trebuie să se
execute ca un întreg pentru a menţine consistenţa bazei de date. De exemplu, un transfer
bancar între conturile A şi B implică atât scăderea sumei transferate din contul A cât şi
adăugarea acestei sume în contul B. Dacă din anumite motive, una din comenzile
tranzacţiei “cade”, toate schimbările generate de comenzile tranzacţiei vor fi anulate.
O tranzacţie constă din:
- o singură instrucţiune LDD;
- o singură instrucţiune LCD;
- instrucţiuni LMD care fac schimbări consistente în baza de date.
O tranzacţie începe după o comanda COMMIT, după o comanda ROLLBACK, după
conectarea iniţială la SGBD ORACLE sau când este executată prima instrucţiune SQL. O
tranzacţie se termină dacă sistemul cade sau utilizatorul se deconectează, dacă se dau
comenzile COMMIT sau ROLLBACK sau dacă se execută comenzi LDD sau LCD.
După ce se termină o tranzacţie, prima instrucţiune SQL executabilă va genera automat
începutul unei noi tranzacţii.
O comanda COMMIT apare automat când se execută o comanda LDD< sau LCD , sau
după o ieşire normală din SQL*Plus.
O comanda ROLLBACK apare automat după o ieşire “anormală” din SQL*Plus sau o
cădere de sistem. Dacă în SQL*Plus se dă comanda SET AUTOCOMMIT ON atunci
toate modificările după comenzile INSERT, UPDATE, DELETE vor fi definitive.
Comanda COMMIT este similară operaţiei de salvare a unui fişier în MS WORD.
Execuţia unei comenzi COMMIT implică:
- Toate modificările (INSERT, DELETE, UPDATE) sunt definitive. Comanda se
referă numai la schimbările făcute de către utilizatorul care dă comanda.
- Toate punctele de salvare vor fi şterse;
- Starea anterioară a datelor este definitiv pierdută;
- Toţi utilizatorii pot vizualiza rezultatele;
- Blocările asupra înregistrărilor afectate sunt eliberate.
Comanda ROLLBACK anulează tranzacţia în curs şi toate modificările de date făcute
după ultima comandă COMMIT.
Pentru tranzacţii care implică execuţia mai multor comenzi SQL sunt necesare puncte de
salvare (savepoint). Un punct de salvare poate fi asociat unei comenzi ROLLBACK prin
comanda:
ROLLBACK [TO nume_punct_salvare];
Punctul de salvare se defineşte în secvenţa de comenzi SQL sub forma:
SAVEPOINT nume_punct_salvare;
Consistenţa la citire (READ CONSISTENCY).
SGBD ORACLE, ca SGBD multiuser, furnizează READ CONSISTENCY la nivel de
instrucţiune SQL, adică o singură comandă SQL nu poate da rezultate care sunt
contradictorii sau inconsistente. READ CONSISTENCY asigură că fiecare utilizator
vede datele aşa cum existau ele la ultimul COMMIT, înainte să înceapă o operaţie LMD.
Dacă asupra bazei se execută o comanda LMD, serverul SGBD ORACLE face o copie a
datelor înainte de modificăre şi o depune în segmentul ROLLBACK.
2.5. Realizarea rapoartelor şi videoformatelor (FORMS)
1. Realizarea rapoartelor în SQL
Conţinutul unei tabele poate fi listată cu ajutorul comenzii:
SELECT * FROM <nume- tabela>;
Rezultatul executării comenzii SELECT apare ca o listă în care numele atributelor tabelei
sunt titlurile coloanei şi fiecare rând al listei reprezintă o înregistrare. Această listă poate
fi modificătă folosind câteva opţiuni ce stabilesc variabile de sistem sau modifică
variabile de sistem existente. O variabilă de sistem este o simplă variabilă ce este definită
pentru sesiunea SQL. Aceasta poate conţine doar o valoare şi o formatare la un moment
dat.
Setarea paginii şi mărimii liniei:
Mărimea paginii este setată cu SET PAGESIZE n, unde n este numărul de linii per
pagină. Următoarea comandă va seta pagina la 20 de linii:
SQL>SET PAGESIZE 20
Mărimea liniei este setată folosind SET LINESIZE n, unde n este numărul de caractere
permise pe o linie. Următoarea comandă va seta mărimea liniei la 100 caractere:
SQL>SET LINESIZE 100
Sunt multe opţiuni pentru comanda SET. În HELP-ul sistemului putem examina opţiunile
comenzii SET.
Să urmărim acum sintaxa şi semnificaţia principalelor comenzi utilizate în realizarea
rapoartelor în SQL.
Editarea titlului tabelului:
Pentru a afişa o linie de titlu în partea superioară a fiecărei pagini a unui raport se
utilizează Comanda TTITLE, care are sintaxa:
TTITLE pozitie ‘text’ pozitie ‘text’ ......
unde:
Poziţia poate fi: Left, Right, Center, Justified, COL n,ANDSKIP n;
Left, Right,ANDCenter sunt poziţii relative faţă de marginea stângă şi dreaptă;
Text poate fi orice şir de caractere ce poate fi folosit ca şi titlu.
COL n unde n este poziţia numărului unei coloane, primul caracter al titlului în coloana n a
liniei.
Skip n unde n este un integer ce spune SGBD ORACLE câte linii să sară după printarea unui
element.
Exemplu. Care este semnificaţia comenzii?
SQL>TTITLE CENTER „LISTA STUDENTI‟ SKIP 2 LEFT „ AN UNIV. 2008-2009‟
BTITLE
Complementar lui TTITLE este Comanda BTITLE. Această Comanda are aceeaşi
opţiune şi formatare ca şi TTITLE, dar printează titlul la sfârşitul fiecărei pagini.
Editarea capului de tabel:
Textul HEADING defineşte titlul unei coloane. Dacă nu este folosită clauza HEADING,
titlul default va fi column sau o expresie. Dacă textul aferent unei coloane conţine spaţii
sau caractere de punctuaţie, acestea trebuie incluse între “” sau „‟.
Spaţierea corpului tabelului şi linii de sumar:
Când este folosită clauza ORDER BY în Comanda SQL> SELECT, rândurile cu aceeaşi
valoare în coloanele ordonate sunt afişate împreună în output. Acest output poate fi mai
util creat folosing comenzile BREAK şi COMPUTE aparţinând SQL*Plus pentru a crea
subseturi de înregistrări şi pentru a adăuga spaţii sau linii de sumar după fiecare subset.
De exemplu, următoarea interogare, fără comenzile BREAK sau COMPUTE:
SELECT DEPT, NUME, SAL
FROM salariati
WHERE SAL < 2500
ORDER BY DEPT;
produce următorul rezultat neformat:
DEPT NUME SAL
------ ------ -----
10 IONESCU 2450
10 POPESCU 1300
20 ANCA 800
20 ALEXA 1100
30 IANCU 1600
30 GEORGESCU 950
30 VLAD 1500
30 ALEXE 1250
30 MARTIN 1250
Pentru a face acest raport mai util, vom folosi Comanda BREAK pentru a stabili DEPT
ca şi coloană break. Prin BREAK poţi suprima valori duplicate în DEPT şi plasa linii
goale sau începe o pagină nouă între departamente. Poţi folosi BREAK împreună cu
COMPUTE pentru a calcula liniile de sumar ce conţin salariul total pentru fiecare
departament şi pentru toate departamentele.
Suprimarea valorilor duplicate în coloanele BREAK. Comanda BREAK suprimă
valorile duplicate din default în coloană sau expresia numită. Pentru a suprima valorile
duplicate într-o coloană specificată într-o clauză ORDER BY, se foloseşte cea mai simplă
formă a comenzii BREAK.
BREAK ON break column
Notă: De fiecare dată când este specifică o coloană sau o expresie într-o Comanda
BREAK, se foloseşte clauza ORDER BY pentru a specifica aceeaşi coloană sau
expresie. Dacă nu se procedează astfel, break-ul poate aparea aleator. Pentru a suprima
afişarea departamentelor duplicate în interograre, vom introduce:
SQL> BREAK ON DEPT
SQL> SELECT DEPT,NUME, SAL
2 FROM salariati
3 WHERE SAL < 2500
4 ORDER BY DEPT;
SQL*Pus afişează următorul output:
DEPT NUME SAL
------ ------ -----
10 IONESCU 2450
POPESCU 1300
20 ANCA 800
ALEXA 1100
30 IANCU 1600
GEORGESCU 950
VLAD 1500
ALEXE 1250
MARTIN 1250
Inserarea spaţiului când se schimbă valoarea unei coloane break:
Linii blank pot fi inserate sau trecute la o nouă pagină de fiecare dată când valoarea se
schimbă în coloana break. Pentru a insera n linii goale, se foloseşte următoarea formă a
comenzii BREAK:
BREAK ON break_column SKIP n
Pentru a sări la o nouă pagină, se foloseşte următoarea formă a comenzii:
BREAK ON break_column SKIP PAGE
Exemplu: Pentru a plasa o linie goală între departamente, se foloseşte Comanda:
SQL> BREAK ON DEPT SKIP 1
Rulând acum Comanda:
SQL> SELECT DEPT, NUME, SAL FROM salariati WHERE SAL < 2500
2 ORDER BY DEPT;
SQL*Plus afişează următorul rezultat:
DEPT NUME SAL
------ ------ -----
10 IONESCU 2450
POPESCU 1300
20 ANCA 800
ALEXA 1100
30 IANCU 1600
GEORGESCU 950
VLAD 1500
ALEXE 1250
MARTIN 1250
Dacă dorim să inserăm linii goale sau pagini goale după fiecare rând folosim următoarea
Comanda pentru a sării n linii:
BREAK ON ROW SKIP n
Folosim următoarea Comanda pentru a sări un număr definit de linii ce formează o
pagină:
BREAK ON ROW SKIP PAGE
Notă: SKIP PAGE sare doar numărul de linii definite să fie o pagină. E posibil ca acestea
să nu conţină o pagină întreagă.
Tehnica folosirii spaţiilor multiple Să presupunem că avem mai mult de o coloană în clauza ORDER BY şi dorim să inserăm
un spaţiu când valoarea fiecărei coloane se schimbă. Fiecare Comanda BREAK inserată
înlocuieşte pe cea anterioară. Dacă dorim să schimbăm diferite tehnici de spaţiere într-un
raport după ce valoarea se schimbă trebuie specificate coloane multiple şi acţiuni într-o
singură comanda BREAK.
Exemplu: Avem două coloane de tip BREAK într-o cerere:
1 SELECT DEPT, FUNCTIE, NUME, SAL FROM salariati WHERE SAL < 2500
2 ORDER BY DEPT, FUNCTIE;
Acum, pentru a sări 3 linii când valoarea DEPT se schimbă şi o linie când valoarea
FUNCTIE se schimbă, se utilizează comanda:
SQL> BREAK ON DEPT SKIP 3 ON FUNCTIE SKIP 1 ;
Rulând din nou cererea de mai sus rezultă:
DEPT FUNCTIA NUME SAL
------- -------------- -------- ------
10 Programator IONESCU 300
Sef proiect POPESCU 2450
20 Programator ANCA 800
ALEXA 1100
30 Programator IANCU 950
Analisti GEORGESCU 1600
VLAD 1500
ALEXE 1250
MARTIN 1250
Afişarea şi ştergerea definiţiilor BREAK
Se poate lista actualul break prin folosirea comenzii BREAK fără nicio clauză.
BREAK Se poate anula actualul break prin Comanda CLEAR cu clauza BREAK
CLEAR BREAKS Comanda CLEAR BREAKS se poate plasa la începutul fiecărui fişier de comandă pentru
a ne asigura de faptul că anterioarele comenzi BREAK nu vor afecta interogarea ce o
rulăm în fişierul dat.
Inserarea liniilor de sumar când valoarea unei coloane break se schimbă.
Dacă rândurile unui raport sunt organizate în subseturi cu Comanda BREAK, se pot
realiza diverse inserări în rândurile fiecărui subset. Acest lucru se poate realiza cu funcţii
ale SQL*Plus. Se pot folosi BREAK şi COMPUTE în următoarea formă:
BREAK ON break_column
COMPUTE funcţion OF column, column, column ... ON break_column
Pot fi incluse multiple break-uri de coloană şi acţiuni precum sărirea unor linii în
comanda BREAK, atâta timp cât coloana numită după ON în comanda COMPUTE apare
de asemenea şi în comanda BREAK după ON.
Pentru a include coloane multiple de tip break impreună cu comanda COMPUTE, se
utilizează comenzile în următoarea formă:
BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1
COMPUTE funcţion OF column column column ... ON break_column_2
Comanda COMPUTE nu are efect dacă nu corespunde comenzii BREAK.
Se poate realiza COMPUTE la numărul coloanei sau în cazuri certe la toate tipurile de
coloane. Următoarele tabele afişează funcţiile de calcul şi efectele acestora.
Funcţia Efect SUM suma valorilor din coloană
MIN minimul valorilor din coloană
MAX maximul valorilor din coloană
AVG medium valorilor din coloană
STD deviaţia stANDard a valorilor din coloană
VAR variaţia valorilor din coloană
COUNT numărul valorilor not null din coloană
NUM numărul de rânduri din coloană
Funcţia specificată în comanda COMPUTE se aplică tuturor coloanelor introduse după
OFF şi înainte de ON. Valorile cumulate (însumate) vor fi scrise pe o linie separată când
valoarea coloanei de tip break se schimbă. Etichetele pentru valorile adunate apar în
prima coloană. Dacă folosim COMPUTE pe prima coloană, ar trebui creată o coloană
fictivă pentru etichete folosind comanda COLUMN. Altfel eticheta nu va fi afişată.
Toate funcţiile COMPUTE în afară de SUM ignoră valorile null.
Pentru a aduna totalul SAL pe departament, întâi listăm definiţia curentă BREAK ON:
SQL> BREAK ON DEPT SKIP 2 ON FUNCTIE SKIP 1;
Apoi introducem următoarea Comanda COMPUTE şi rulăm interogarea:
SQL> COMPUTE SUM( SAL) DEPT SELECT DEPT, FUNCTIE, NUME, SAL
2 FROM salariati WHERE SAL < 2500 ORDER BY DEPT, FUNCTIE;
SQL*Plus afişează următorul output:
DEPT FUNCTIA NUME SAL
------- -------------- -------- ------
10 Programator IONESCU 300
Sef proiect POPESCU 2450
******** ******** ******** ******
sum 3750
20 Programator ANCA 800
ALEXA 1100
******** ******** ******** *******
sum 1900
30 Programator IANCU 950
Analist GEORGESCU 1600
VLAD 1500
ALEXE 1250
MARTIN 1250
******* ******** ******** ******
Sum 6550
Editarea liniilor de sumar la sfârşitul raportului: Se pot calcula şi afişa linii de sumar bazate pe valorile unei coloane folosind BREAK şi
COMPUTE în următoarele forme:
BREAK ON REPORT
COMPUTE funcţion OF column, column, column... ON REPORT
Pentru a calcula şi afişa totalul salariilor pentru toţi agenţii de vânzării, întâi introducem
următorul BREAK şi COMPUTE:
SQL> BREAK ON REPORT;
SQL> COMPUTE SUM OF SAL ON REPORT;
Apoi introducem şi rulăm o nouă interogare
SQL> SELECT NUME, SAL FROM salarii WHERE Funcţia = 'Analist';
SQL*Plus afişează rezultatele:
NUME SAL
--------- ------
GEORGESCU 1600
VLAD 1500
ALEXE 1250
MARTIN 1250
******** ------
sum 5600
Efectuarea calculelor:
COMPUTE
Pot fi anulate toate definiţiile de tip COMPUTE prin introducerea comenzii CLEAR cu
clauza COMPUTE.
Pentru a anula definiţiile COMPUTE şi BREAK , se utilizează comenzile:
SQL> CLEAR BREAKS
SQL> CLEAR COMPUTES
Este indicat să plasăm comenzile CLEAR BREAKS şi CLEAR COMPUTE la începutul
fiecărui fişier de comenzi pentru a ne asigura de faptul că anterioarele comenzi
COMPUTE şi BREAK nu afectează fişierul curent.
Tema nr.1: Executaţi secvenţa de comenzi şi observaţi efectul lor:
SQL> SET BUFFER BAFUTIL
I
TTITLE ' Stat de salarii'
BTITLE 'Terminare raport'
COLUMN MARCA FORMAT A5 HEADING 'Marca| Angajat'
COLUMN NUME FORMAT A12 HEADING 'Nume| Angajat'
COLUMN PREN HEADING 'Prenume| Angajat'
COLUMN SALAR FORMAT 9999 HEADING 'Salariu'
COLUMN ATEL HEADING 'Atelier' FORMAT A7
BREAK ON REPORT ON ATEL SKIP1
COMPUTE SUM OF SALAR ON ATEL
COMPUTE SUM OF SALAR ON REPORT
SELECT ATEL, MARCA, NUME, PREN, SALAR
FROM PERSONAL ORDER BY ATEL;
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
Se pasa tasta ENTER
SAVE PROG1
(Pentru a salva bufferul într-un fişer cu numele PROG1)
START PROG1
(Pentru a rula programul)
2.5.2. Utilizarea video formatelor în SQL (SQL FORMS)
Vederi (Views):
In SGBD ORACLE comanda SQL pentru crearea unei tabele virtuale (vedere/view) este
de forma:
create [or replace] view <view-name> [(<column(s)>)] as
<SELECT-statement> [with check option [constraint <name>]];
Opțional se poate folosi clauza replace dacă exista deja numele coloanei coloanelor în
vedere. Daca <column(s)> nu sunt specificate în definiția view, coloanele vor avea
aceleași nume cu cele listate în comanda SELECT.
Exemplu:: Următoarea vedere conţine numele, funcţia şi salariu annual al salariațiilor
care lucrează în departamentul 20:
Create view DEPT20 as SELECT ENAME, JOB, SAL_12 ANNUAL SALARY FROM
EMP
WHERE DEPTNO = 20;
În instrucțiunea SELECT aliasul coloanei ANNUAL SALARY este specificat pentru
expresia SAL_12 şi acest alias este dat prin view. O alternativă la această formulare poate
fi:
CREATE VIEW DEPT20 (ENAME, JOB, ANNUAL SALARY) as
SELECT ENAME, JOB, SAL _ 12 FROM EMP WHERE DEPTNO = 20;
O vedere poate fi utilizată în același mod ca o tabelă, adică, rândurile pot fi regăsite dintr-
o vedere, sau aceste rânduri pot fi modificate. O vedere este evaluată din nou de fiecare
dată când este accesată.
În SGBD ORACLE SQL nu sunt permise operațiile de inserare, actualizare sau ștergere
dacă se utilizează la definirea vederi următorii constructori:
• Joins
• Funcţii de agregare ca: sum, min, max etc.
• Operatorii (in, any, all)sau exists în subinterogari;
• clauzele group sau distinct .
O vedere poate fi ștearsă prin Comanda <view-name>.
Structura platformei SGBD ORACLE DEVELOPER :
Pentru dezvoltarea de aplicaţii informaţice platforma SGBD ORACLE DEVELOPER
pune la dispoziţie următoarele utilitare:
- FORMS DEVELOPER sau SQL*FORMS;
- PROJECT BULIDER;
- REPORTS DEVELOPER sau SQL*REPORTS;
- GRAPHICS BUILDER sau SQL*GRAPHICS;
- PROCEDURE BUILDER (creare aplicaţii client-server cu PL/SQL);
- TRANSLATION BUILDER (integrare de aplicaţii construite în diferite medii
de programare specifice platformei SGBD ORACLE);
- SCHEMA BUILDER (actualizarea şi redefinirea structurii BD);
- QUERY BUILDER (interogare obiecte BD).
1. Prezentarea FORMS DEVELOPER sau SQL*FORMS:
Structura utilitarului:
- Form Builder (creare VF);
- Form Compiler (creare fişiere executabile);
- Form Runtime (rulare VF).
Form Builder:
Conţine 8 submeniuri prin care se pot proiecta şi dezvolta aplicaţii cu BD:
Modulele unui VF create astfel, pot fi salvate atât în fişiere cât şi în BD curentă, sub
forma următoarelor tipuri de fişiere:
Tip modul Tip extensie modul Tip format fişier
FORM
.FMB Binar
.FMX Executabil
.FMT Text
MENU
.MMB Binar
.MMX Executabil
.MMT Text
PL/SQL LIBRARY
.PLL Binar
.PLX Executabil
ORACLE Forms Builder – Nume aplicatie………
File Edit View Navigator Program Tools Windows Help
.PLD Text
Cu FORMS BUILDER se pot defini şi rula patru tipuri distincte de VF, şi anume:
- Single Block Form;
- Master/Detail Form;
- Single block Form cu câmpuri de căutare;
- Master/Dtail Form cu câmpuri de căutare.
SGBD ORACLE oferă metode mai elegante şi eficiente pentru introducerea de date,
vizualizarea sau modificarea datelor din una sau mai multe tabele, prin utilizarea VIDEO
FORMATELOR (SQL FORMS).
Prin SQL FORMS se pot vizualiza datele pe ecran sau se pot introduce date de la
tastatură.
Elementele unui FORMS sunt:
- Pagina (Ecranul – unul sau mai multe);
- Blocul (O pagină are unul sau mai multe blocuri. Fiecare bloc are un nume.
Într-un bloc se afişează sau se introduc date numai dintr-o tabelă);
- Zona (Un bloc e format din una sau mai multe zone. O zonă poate fi formată
dintr-un text, sau dintr-o coloană (spaţiu de afişare/introducere de valori
coloană) sau text + coloană.
Deci, ZONA:
text;
coloană;
text + coloană.
Structura unui VF:
- Blocul;
- Elemente;
- CANVAS (suprafaţa în care sunt aşezate listele de valori, grafice, vizualizate
elemente, etc.);
- Fereastra;
- Frame;
- Unităţi de program;
- Trigger;
- Alte obiecte.
A) Operaţii asupra SQL – FORMS:
a) - Definirea structurii SQL- FORMS;
- Compilarea SQL-FORMS (IAG- utilitar de complilare a videoformatului);
- Execuţia SQL-FORMS (RUN FORMS).
b) - Modificarea structurii SQL- FORMS;
- Recompilarea structurii;
- Execuţia SQL- FORMS.
- Definirea structurii SQL- FORMS se face cu utilitarul SQL- FORMS;
Pentru a rula acest utilitar kernelul trebuie să fie încărcat în memorie.
- Se tastează: SQL> SQLFORMS;
Pentru a accepta numele şi parola se apasă tasta END.
- Modul de lucru este de tip asistat;
- Există la dispoziţie un sistem de meniuri;
- În modul de lucru asistat se utilizează taste sau combinaţii de taste, fiecare din
acestea având o anumită semnificaţie, adică lansează o anumită operaţie;
- Prin tastarea lui F8 se obţine semnificaţia tastelor din SQL- FORMS.
B) Semnificaţia tastelor funcţionale în SQLFORMS:
Tab - parcurgerea opţiunilor înainte (sau ENTER);
Săgeată sus - parcurgerea opţiunilor de sus în jos;
Săgeată jos - parcurgerea opţiunilor de jos în sus;
SHIFT +TAB - parcurgerea opţiunilor înapoi.
Acţionarea unei opţiuni se face tastând tasta F1.
F1 F1
Crearea unui videoformat:
Urmarea execuţiei comenzii:
SQL> SQLFORMS;
apare în ecranul principal, denumit OBJECT NAVIGATOR, o fereastră care
poate fi structurată în şase secţiuni:
- FORMS;
- MENUS;
Meniu END
Meniu 1 Meniu 2
- PL/SQL LIBRARIES (Bibliotecile PL/SQL);
- OBJECT LIBRARIES (Biblioteca de obiecte);
- BUIT-IN PACKAGE (Lista pachetelor PL/SQL);
- DATABASE OBJECT (Obiectele bazei de date).
Modificarea VF
a) Introducerea de noi blocuri;
b) Ştergerea de blocuri;
c) Modificare bloc:
1. introducere texte noi,
2. corectare texte,
3. modificare loc/zone,
4. ştergere zone,
5. introducere de noi zone,
6. trasare de linii sau chenare.
d) În CHOOSE BLOCK se dă un nume şi se alege opţiunea DEFAULT;
e) Se face cu DROP (din CHOOSE BLOCK);
Observaţie: Într-un VF care are mai multe blocuri, pentru a sări de la un bloc la altul
se utilizează PgUp şi PgDn.
Oricare din operaţiile de la C) se fac numai în cadrul unui bloc, chiar dacă pe ecran
sunt afişate mai multe blocuri.
c1 - tastez ce doresc în locul unde vreau să înceapă textul.
c2 - intru pe text şi corectez.
Tasta DEL şterge caracterul indicat de cursor.
Cursul nr. 10-12 : (extras)
Obs : Continutul informational complet se afla in manual.
PARTEA a II-a
Sectiunea I
LIMBAJUL DE PROGRAMARE PL/SQL
1. Introducere în limbajul PL/SQL
2. Structura unui bloc de bază PL/SQL
3. Declararea variabilelor
4. Elemente de limbaj
5. Instrucţiuni PL/SQL
6. Gestiunea cursorului în PL/SQL
7. Subprograme PL/SQL
8. Pachete
9. Declanşatori (triggeri) 10.Gestiunea erorilor
11. Abordarea orientată obiect în PL/SQL
4.1 INTRODUCERE ÎN LIMBAJUL PL/SQL
În rezumat, obiectivele majore ale lui PL/SQL sunt:
• creşterea expresivităţii limbajului SQL;
• prelucrarea rezultatului unei cereri în mod orientat pe înregistrare;
• optimizarea combinaţiilor de instrucţiuni SQL;
• dezvoltarea de programe modulare de aplicaţii modulare cu BD;
• reutilizarea codului program şi
• reducerea costului de întreţinere şi de schimbare a aplicaţiilor.
Denumirea PL/SQL provine de la Procedural Language extensie a limbajului
SQL. Limbajul PL/SQL oferă un mediu robust de programare care permite programarea
procedurală cu tehnici de programare orientată pe obiect, cum ar fi încapsularea,
ascunderea informaţiei şi rescrierea funcţiilor.
PL/SQL este o extensie procedurală a limbajului SQL. PL/SQL este un limbaj de
acces la datele stocate în baze de date relaţionale ( BDR), care permite gruparea unei
mulţimi de comenzi într-un bloc unic de tratare a datelor. PL/SQL include instrucţiuni
SQL pentru manipularea datelor şi instrucţiuni pentru gestionarea lor.
Elemente din SQL acceptate de PL/SQL sunt:
- instrucţiuni pentru manipularea datelor : INSERT, SELECT, UPDATE ,
DELETE;
- instrucţiuni pentru prelucrarea tranzacţiei (secvenţa SQL care este tratată ca o
unitate) COMMIT, ROLLBACK, SAVE-POINT.
- Funcţiile : toate funcţiile SQL care sunt utilizate în instrucţiunile specifice
SQL.
- Predicatele SQL (condiţiile din clauza WHERE , toţi operatorii BETWEEN,
IN, IS, NULL, LIKE).
PL/SQL are un număr de instrucţiuni proprii: BEGIN, END, DECLARE,
atribuire, DECLARE…. CURSOR, OPEN, FETCH, CLOSE, EXCEPTION, RAISE,
EXCEPTION_INIT, IF…THEN.….ELSE, LOOP, GO TO , NULL, EXIT. În SQL există
comenzi care nu pot fi utilizate direct în PL/SQL, dar pot fi utilizate indirect prin
intermediul pachetelor (package): CREATE, ALTER, DROP, GRANT, REVOKE.
Comenzile de definire a datelor CREATE TABLE nu sunt permise în PL/SQL deoarece
codul PL/SQL este compilat şi nu poate să se refere la obiecte care nu există în momentul
compilării. In fig. 1 se prezintă o imagine sugestivă privind definirea limbajului PL/SQL.
Fig. 1. Structura limbajului PL/SQL
Facilităţi PL/SQL:
PL/SQL oferă construcţii procedurale cum ar fi variabile, constante şi tipuri.
Limbajul oferă construcţii selective şi iterative construcţiilor SQL. Avantajele majore ale
folosirii PL/SQL sunt:
- integrarea comenzilor de bază SQL;
- definirea şi gestiunea blocurilor de instrucţiuni;
- gestiunea variabilelor şi a constantelor;
- gestiunea cursoarelor explicite şi implicite;
- definirea subprogramelor;
- definirea pachetelor;
- utilizarea structurilor de control fundamentale;
- detectarea şi gestiunea erorilor de execuţie şi a situaţiilor excepţionale.
PL/SQL este necesar pentru a construi aplicaţii cu baze de date care includ
programarea logică, construcţii secvenţiale şi construcţii SQL. Folosind limbajul PL/SQL
se pot construi aplicaţii care pot fi actualizate în timp. PL/SQL îmbină puterea de
manipulare a datelor oferită de SQL , cu puterea de prelucrare a datelor oferită de
limbajele procedurale.
4.2 Structura unui bloc de bază PL/SQL
Construcţia de bază în PL/SQL este blocul. PL/SQL este un limbaj structurat pe
blocuri. Un bloc este o unitate de program care are un nume şi care poate fi imbricat
(adică un bloc poate conţine mai multe blocuri). Blocurile conţin proceduri, funcţii sau
pachete care au un nume. Un bloc are o secţiune de declaraţii (secţiune opţională), o parte
care conţine instrucţiuni PL/SQL şi o parte opţională de tratare a erorilor. Structura unui
bloc este următoarea ( conţinutul din parantezele [] este opţional):
[<Block header>]
[declare
<Constants>
<Variables>
<Cursors>
<User defined exceptions>]
begin
<PL/SQL statements>
[exception
<Exception handling>]
end;
În partea de început a blocului (header) se specifică dacă blocul este o procedură, o
funcţie sau un pachet. Dacă nu se specifică headerul atunci blocul se numeşte bloc
anonim PL/SQL. Fiecare bloc PL/SQL conţine instrucţiuni PL/SQL. Aceste blocuri pot fi
imbricate la fel ca şi blocurile din limbajele convenţionale de programare. Scopul
variabilelor declarate este analog cu scopul variabilelor din limbajele de programare C
sau Pascal. Un bloc constă dintr-o mulţime de construcţii SQL şi/sau PL/SQL.Un bloc
PL/SQL constă din 3 secţiuni:
Declaraţii (opţional): această secţiune începe cu cuvântul cheie DECLARE şi se încheie
când începe secţiunea cu cod executabil;
Cod executabil (obligatoriu): această secţiune începe cu cuvântul cheie BEGIN şi
se sfârşeşte cu END. După cuvântul cheie END trebuie pus semnul de punctuaţie
punct şi virgulă;
Tratarea excepţiilor (opţională): secţiunea de tratare a excepţiei este inclusă în
partea de cod executabil şi începe cu cuvântul cheie EXCEPTION.
Secţiune Descriere
Declaraţii
(DECLARE)
Conţine declaraţii de variabile, constante, cursoare şi excepţii
definite de user care sunt referite în secţiunile de cod executabil
şi excepţie.
Execuţie
(BEGIN...END)
Conţine construcţii SQL care caută date în baza de date şi
construcţii PL/SQL pentru a manipula datele din bloc.
Excepţie
(EXCEPTION)
Specifică ce acţiuni trebuie efectuate când sunt întâlnite erori sau
condiţii anormale în secţiunea de cod executabil.
Un bloc de programe PL/SQL poate conţine un număr de instrucţiuni SQL
combinat cu:
- instrucţiuni de atribuire;
- instrucţiuni alternative (IF);
- instrucţiuni repetitive (FOR, WHILE) specifice PL/SQL.
Programul PL/SQL conţine mai multe blocuri care pot fi separate sau conţinute unul
intr-altul. Există 3 tipuri de blocuri:
Blocuri anonime: acestea sunt blocuri PL/SQL care sunt definite în cadrul
unei aplicaţii şi nu au nume;
Proceduri: acestea sunt blocuri PL/SQL care au un nume, au parametri de
intrare, dar nu au parametri de ieşire expliciţi;
Funcţii: acestea sunt blocuri PL/SQL care au un nume, au parametri de intrare
şi tot timpul returnează o valoare.
Exemple de blocuri scrise în PL/SQL:
1. Sintaxa blocurilor anonime:
[DECLARE
Declarate variabile]
BEGIN
Cod program
[EXCEPTION
Cod tratare erori]
END;
2. Sintaxa pentru definirea unei funcţii:
FUNCTION nume [(lista_argumente)] RETURN tip_data {IS,AS}
Declarare variabile
BEGIN
Cod program
[EXCEPTION
Cod tratare erori]
END;
3. Blocuri imbricate:
-- blocuri imbricate
DECLARE
error_flag BOOLEAN :=false;
BEGIN
DBMS_OUTPUT.PUT_LINE ('NUMARAM DE LA 100 LA 1000.');
DECLARE
HUNDREDS_COUNTER NUMBER (1,-2);
BEGIN
HUNDREDS_COUNTER := 100;
LOOP
DBMS_OUTPUT.PUT_LINE (HUNDREDS_COUNTER);
HUNDREDS_COUNTER := HUNDREDS_COUNTER + 100;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('ACEASTA ESTE VALOAREA MAXIMA GASITA');
END;
IF error_flag THEN
DBMS_OUTPUT.PUT_LINE ('NU POT NUMARA ASA MULT');
ELSE
DBMS_OUTPUT.PUT_LINE ('DONE');
END IF;
END;
/
3. Sintaxa pentru declararea unei proceduri:
PROCEDURE nume [(listă_argumente)] {IS,AS}
BEGIN
Cod program
[EXCEPTION
cod tratare erori]
END;
Exemplu de bloc procedură:
PROCEDURE alfa (a1 IN OUT NUMBER, a2 IN OUT NUMBER ) IS
temp_a NUMBER;
BEGIN
temp_a := a1;
a1 := a2;
a2 := temp_a;
END;
Un bloc de programe PL/SQL poate conţine un număr de instrucţiuni SQL combinat
cu: instrucţiuni de atribuire; instrucţiuni alternative (IF); instrucţiuni repetitive (FOR,
WHILE) specifice PL/SQL.
4.3 Declararea variabilelor
Limbajul PL/SQL lucrează cu un număr de tipuri de date care pot fi grupate în: date de
tip scalar, date de tip obiect, înregistrări şi pointeri. O variabilă de tip scalar este o
variabilă care nu se obţine din combinarea altor variabile. Acest tip de dată se foloseşte
pentru a construi tipuri de date complexe cum ar fi înregistrările şi tablourile (arrays).
PL/SQL lucrează cu următoarele tipuri de date:
Tip dată Semnificaţie
VARCHAR2 Variabilă, sir caractere cu lungime variabilă
CHAR Variabilă, sir caractere cu lungime fixă
NUMBER Număr, cu lungime fixă
BINARY_INTEGER Valori întregi
DATE Date calendaristice
BOOLEAN Valori TRUE/FALSE
Variabilele pot avea orice tip de dată SQL, cum ar fi CHAR, DATE, sau NUMBER, sau
un tip de dată PL/SQL cum ar fi BOOLEAN sau PLS_INTEGER. După fiecare
declaraţie se pune punct şi virgulă. Constantele, variabilele, cursorii şi excepţiile utilizate
în blocul PL/SQL trebuie să fie declarate în secţiunea de declarare a blocului. Clauza
COSTANT arată că o valoare alocată la o variabilă nu poate fi schimbată (variabila
devine constantă).
4.4 Instrucţiuni PL/SQL
Instrucţiunile PL/SQL controlează fluxul execuţiei unui program PL/SQL. În cadrul
limbajului PL/SQL distingem următoarele tipuri de instrucţiuni:
instrucţiuni de atribuire (:=).
Instrucţiuni iterative: LOOP, WHILE, FOR.
Instrucţiuni condiţionale : IF;
Instrucţiuni de salt : GO TO , EXIT.
Instrucţiunea vidă : NULL.
Operatorul atribuire în PL/SQL este “:=” iar în SQL este “=”.
Exemplu:
DECLARE
data1 DATE;
BEGIN
data1 := TO_DATE(‚29-DEC-2009‟,‟DD-MON-YYYY‟);
END;
/
Fluxul secvenţial de execuţie a comenzilor unui program PL/SQL poate fi
modificat cu ajutorul structurilor de comenzi de control: IF, LOOP, FOR, WHILE,
GOTO, EXIT.
Sintaxa comenzii IF
IF condiţie THEN
Instrucţiuni;
[ELSIF conditie THEN
Instrucţiuni ;]
[ELSE instrucţiuni;]
END IF;
Exemplu: IF (A>=100) THEN
IF ! (B=10) THEN
DBMS_OUTPUT.PUT_LINE („B nu este egal cu 10‟);
ELSIF B<> 11 THEN
DBMS_OUTPUT.PUT_LINE („B nu este egal cu 11‟);
ELSE
DBMS_OUTPUT.PUT_LINE („B diferit de 10, 11‟);
END IF;
END IF;
În cadrul unui program scris în limbajul PL/SQL se pot distinge mai multe structuri de
control:
1. Instrucţiunea IF are 3 forme: IF-THEN, IF-THEN-ELSE şi IF-THEN-ELSIF.
a. Sintaxa pentru instrucţiunea IF-THEN:
IF <condiții>
THEN
<instrucţiuni>
END IF;
Exemplu:
DECLARE
sales NUMBER(8,2) := 12100;
quota NUMBER(8,2) := 10000;
bonus NUMBER(6,2);
emp_id NUMBER(6) := 120;
BEGIN
IF sales > (quota + 200) THEN
bonus := (sales - quota)/4;
ELSE
bonus := 50;
END IF;
UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
b. Sintaxa pentru instrucţiunea IF...THEN...ELSE
IF <condiții>
THEN
<instrucţiuni care se execută dacă condiții = TRUE>
ELSE
<instrucţiuni care se execută dacă condiții = FALSE>
END IF;
c. Sintaxa pentru instrucţiunea IF ... ELSIF
IF <condiții1>
THEN
<instrucţiuni care se execută dacă condiții1= TRUE>
ELSIF <condiții2 >
THEN
<instrucţiuni care se execută dacă condiții2= TRUE>
ELSIF <condiții3 >
THEN
<instrucţiuni care se execută dacă condiții3= TRUE>
.....
ELSE
<instrucţiuni care se execută dacă condiții = FALSE>
END IF;
2. Instrucţiunea CASE
Pentru a nuanţa salturile în secvenţa de program în funcţie de valoarea condiţiei se
foloseşte instrucţiunea CASE, care are sintaxa:
CASE variabilă
WHEN ‟A‟ THEN
Instrucţiuni
WHEN ‟B‟ THEN
Instrucţiuni
WHEN ‟C‟ THEN
Instrucţiuni
.....
ENDCASE;
3. Instrucţiunea GO TO
Instrucţiunea GO TO asigură saltul necondiţionat la instrucţiunea cu eticheta indicată.
Scopul acestei instrucţiuni este de a asigura saltul (ieşirea) dintr-un subloc, dintr-un ciclu,
dintr-o instrucţiune IF. Sintaxa acestei instrucţiuni este:
GO TO etichetă;
Exemplu:
DECLARE
I POSITIVE:=1;
Max_loop CONSTANT POSITIV :=100;
BEGIN
I := 1;
LOOP
I :=i+1;
EXIT WHEN I > max_loop;
GOTO mai_mult;
END IF;
END LOOP;
<< mai_mult>>
I :=1;
END;
4. Instrucţiunea vidă: NULL
Această instrucţiune se utilizează pentru a marca în instrucţiunea de tip IF... THEN...
ELSE că nu se execută nici o acţiune pentru o clauză ELSE particulară.
IF (a=b) THEN NULL
ELSE
DBMS_OUTPUT.PUT_LINE („a<>b‟);
END IF;
IF (a=0) THEN GOTO sfarsit
…..
<<sfârşit>>
NULL;
END;
5. Instrucţiunea iterativă WHILE...LOOP
Această instrucţiune evaluează o condiţie înainte de a executa o secvenţa de comenzi.
Dacă condiţia nu este indeplinită atunci nu execută secvenţa de comenzi. Instrucţiunea
iterativă WHILE ... FOR evaluează condiţia după executarea sevenţei de comenzi (adică
secvenţa de comenzi se execută cel puţin odată). Sintaxa instrucţiuni WHILE... Loop
este:
WHILE <condiţie> LOOP
<instrucţiuni>
END LOOP;
Exemplu:
DECLARE
Contor NUMBER := 0;
BEGIN
WHILE contor >= 10 LOP
DBMS_OUTPUT.PUT_LINE (Valoare contor este‟ || contor);
END LOOP;
END;
/
6. Instrucţiunea de ciclare WHILE . . .LOOP
Instrucţiunea WHILE . . . LOOP se execută până atâta timp cât condiţia este adevărată.
Sintaxa acestei instrucţiuni este:
WHILE condiţie LOOP
secvenţa de comenzi
END LOOP;
Această formă a instrucţiunii este echivalentă cu:
LOOP
sequence_of_statements
EXIT WHEN boolean_expression;
END LOOP;
Folosirea instrucţiunii FOR-LOOP
Bucla FOR iterează peste un anumit interval de întregi. Numărul de iteraţii este cunoscut
înainte de intrarea în buclă. Operatorul (..) serveşte ca operator de domeniu. Domeniul
este calculat când se intră prima dată în buclă şi nu mai este niciodată reevaluat.
7. Instrucţiunea WHILE ... FOR (ciclare cu pas)
Exemplu:
DECLARE
A_a INTEGER;
BEGIN
SELECT COUNT(*) INTO A_a from imprumută;
FOR i IN 1..a_a LOOP
…………
END LOOP;
……………
END;
8.Instrucţiunea EXIT
Această instrucţiune forţează ieşirea dintr-o buclă.
Sintaxa acestor instrucţiuni este:
EXIT
Exemplu:
DECLARE
I POSITIVE:=1;
Max_loop CONSTANT POSITIVE :=100;
BEGIN
LOOP
I :=i+1;
EXIT WHEN I > max_loop;
END LOOP;
END;
Comanda EXIT trebuie plasată în afara buclei.
9. Instrucţiunea EXIT ... WHEN
Instrucţiunea EXIT ...WHEN determină ieşirea din buclă în urma unei condiţii. Sintaxa
acestei comenzi este:
EXIT [nume_etichetă] WHEN condiţie;
Exemplu:
IF count > 100 THEN EXIT;
ENDIF;
sau
EXIT WHEN count > 100;
Cele 2 construcţii de mai sus sunt echivalente, dar EXIT ...WHEN este mai uşor de
înţeles.
Temă: Daţi 2 exemple de programe în PL/SQL care folosesc structurile de control LOOP,
folosind obiectele din baza de date creată de voi !
4.6 Gestiunea cursorului în PL/SQL
Cursorul este o zonă de memorie, în care se scriu 0 sau n rânduri dintr-o tabelă, printr-o
cerere SELECT. Cursorul determină o bază de date să se comporte ca baza de date de tip
ISAM (acces indexat secvenţial). Pentru a utiliza mecanismul cursorului trebuie să se
parcurgă urmatoarele etape:
1. Declarare cursor – etapa de definire a instrucţiunii SELECT care va fi utilizată şi
eventualele opţiuni privind cursoarele;
2. Deschidere cursor – etapa în care se încarcă efectiv datele, folosind instrucţiunea
SELECT definită anterior;
3. Regăsirea rândurilor (înregistrărilor) individuale – conform necesităţilor;
4. Închidere cursor.
Etapa: Crearea cursoarelor:
Să urmărim crearea cursorului ClientCursor care va conţine toţi clienţii fără adresa de e-
mail.
DECLARE CURSOR ClintCursor
IS
SELECT * FROM Clienti
WHERE email_client IS NULL;
Etapa: Utilizarea cursoarelor
OPEN CURSOR ClintCursor;
Când această instrucţiune este prelucrată, interogarea este executată şi datele regăsite sunt
stocate în vederea operaţiilor ulterioare de navigare şi derulare. Datele din cursor sunt
accesibile prin instrucţiunea FETCH.
Există două tipuri de cursoare:
- implicite care sunt generate de serverul ORACLE când în partea executabilă a
unui bloc PL/SQL apare o instrucţiune SQL;
- explicite – declarate şi definite de către utilizator atunci când o cerere SELECT
întoarce ca rezultat mai mult decât o linie (înregistrare).
Cursorul se deschide pentru acces (OPEN) şi este accesat secvenţial prin (FETCH).
Definirea cursorului se face în partea declarativă a blocului, prin instrucţiunea
CURSOR, iar execuţia prin instrucţiunile care manipulează cursorul: OPEN, FETCH,
CLOSE. Prin cursor se pot obţine informaţii despre execuţia instrucţiunilor: INSERT,
UPDATE, DELETE, SELECT.
Utilizarea cursoarelor implicite: atributele acestor cursoare oferă informaţii
despre executarea ultimei comenzi.
Exemplu: Excepţiile (erorile) ce pot să apară la execuţia comenzii SELECT pot fi
sesizate cu ajutorul atributelor:
% ROWCOUNT – returnează numărul de linii afectate de ultima instrucţiune
SQL;
%FOUND – atributul are valoarea TRUE dacă ultima comandă SQL afectează
una sau mai multe linii;
%NOTFOUND - atributul are valoarea TRUE dacă ultimă comandă SQL nu
afectează nici o linie.
poate fi formulat ca un trigger de înregi