SQL*PLUS

SQL este un limbaj de comenzi pentru comunicare cu server-ul Oracle.

SQL*Plus este un utilitar Oracle care recunoaşte comenzile SQL şi trimite aceste comenzi server-ului Oracle pentru execuţie.

SQL*Plus instrument puternic care este utilizat în dezvoltarea aplicaţiilor pentru baza de date Oracle.

SQL*Plus instrument flexibil care poate fi executat pe toate platformele pe care se instalează produsul Oracle.

Ce operaţii se pot realiza din consola SQL*Plus ? editarea, salvarea, încărcarea şi execuţia de comenzi SQL sau blocuri

PL/SQL;

formatarea, salvarea, tipărirea şi realizarea anumitor calcule asupra rezultatelor unei interogări sub formă de rapoarte;

listarea definiţiilor tabelelor;

accesarea şi transferarea datelor între baze de date;

realizarea unor funcţii de administrare a BD: administrarea utilizatorilor, administrarea spaţiilor tabel, operaţii de arhivare şi recuperare etc.

Deosebiri esenţiale între comenzile SQL şi comenzile SQL*Plus.

Marcăm dintre aceste deosebiri câteva, care sunt semnificative. Comenzile SQL*Plus:

nu cer caracter de terminare;

cer un caracter de continuare dacă comanda este mai mare decât o linie;

nu sunt depuse în buffer-ul SQL;

nu permit manipularea datelor din baza de date;

cuvintele cheie pot fi prescurtate.

2 Oracle

Conexiune la SQL*Plus

După ce utilizatorul se conectează la SQL*Plus, sistemul afişează un prompt (SQL>) şi aşteaptă comenzile utilizatorului. Utilizatorul poate da:

comenzi SQL pentru accesarea bazei de date;

blocuri PL/SQL pentru accesarea bazei de date;

comenzi SQL*Plus.

Activarea interfeţei SQL*Plus

SQLPLUS [nume_utiliz/parola][@nume_baza_de_date] [@nume_fisier] [-SILENT]

Închiderea sesiunii de lucru SQL*Plus şi preluarea controlului sistemului de operare al calculatorului gazdă se realizează cu QUIT sau EXIT.

Conectarea la o altă bază de date, decât cea deschisă iniţial, în timpul unei sesiuni de lucru SQL*Plus:

CONNECT [nume_utilizator[/parola]][@nume_baza_de_date]

Conexiunea rămâne validă până când apare una din următoarele situaţii:

se părăseşte SQL*Plus;

se dă comanda de deconectare DISCONNECT (comanda nu închide sesiunea de lucru SQL*Plus);

se face conectarea la o altă bază de date.

Crearea unei legături între BD locală şi o BD aflată la distanţă:

CREATE [PUBLIC] DATABASE LINK nume_legatura CONNECT TO nume_utilizator IDENTIFIED BY parola USING baza_de_date_distanta

După ce legătura a fost creată, pot fi făcute interogări asupra tabelelor corespunzătoare BD distante prin specificarea numelui legăturii în clauza FROM din cerere. Dacă se specifică opţiunea PUBLIC, legătura este disponibilă tuturor utilizatorilor cu excepţia celor care şi-au definit o legătură privată cu acelaşi nume.

Suprimarea unei legături între două BD, una locală şi una la distanţă:

DROP [PUBLIC] DATABASE LINK nume_legatura.

Câteva dintre cele mai importante comenzi disponibile în SQL*Plus :

SQL 3

ACCEPT – citeşte o variabilă de intrare de la utilizator;

DEFINE – declară o variabilă (DEF);

DESCRIBE – listează atributele tabelelor sau ale altor obiecte (DESC);

EDIT – deschide un editor în care se poate modifica ultima comandă;

EXIT sau QUIT – deconectează utilizatorul şi încheie sesiunea SQL*Plus;

GET – caută un fişier SQL şi îl depune în buffer-ul SQL;

HOST – execută o comandă a sistemului de operare (!);

LIST – afişează ultima comandă executată din buffer-ul SQL (L);

PROMPT – afişează un text pe ecran;

RUN – listează şi execută comanda salvată în buffer-ul SQL (/);

SAVE – salvează comanda din buffer-ul SQL într-un fişier script;

SET – modifică variabilele de mediu specifice SQL*Plus;

SHOW – afişează setările variabilelor de mediu SQL*Plus;

SPOOL – copiază ieşirea unei comenzi într-un fişier;

START – execută un script SQL (@).

Salvarea comenzilorComanda SQL, care este introdusă interactiv prin interfaţa SQL*Plus, este

memorată într-un buffer SQL. Ea poate fi modificată sau executată de mai multe ori, atâta timp cât utilizatorul nu a introdus o nouă comandă sau nu a şters explicit buffer-ul SQL (CLEAR BUFFER). Cererea nouă va şterge vechea cerere din buffer.

Utilizatorul poate salva una sau mai multe comenzi într-un fişier, ce ulterior poate fi încărcat, modificat sau executat. Extensia implicită a fişierului: .sql.

SAVE nume_fisier [CREATE|REPLACE|APPEND]

– opţiunea CREATE arată că se creează un nou fişier;– opţiunea REPLACE permite înlocuirea (overwrite) unui fişier existent;– opţiunea APPEND adaugă conţinutul buffer-ului în continuarea unui fişier.

Pentru încărcarea şi executarea unui fişier de comenzi poate fi utilizată una din următoarele variante:

comanda START; comanda ∂; comenzile GET şi RUN; comenzile GET şi /.

4 Oracle

Pentru introducerea de comentarii printre comenzile care se găsesc într-un fişier există mai multe posibilităţi:

utilizarea caracterelor “--” urmate de textul comentariului;

utilizarea delimitatorilor “/*” şi “*/” pentru a marca începutul şi sfârşitul unui comentariu.

Pentru a obţine informaţii referitoare la structura tabelelor, vizualizărilor sau sinonimelor, a procedurilor, funcţiilor sau pachetelor fără a fi necesară consultarea cataloagelor de sistem, se utilizează comanda:

DESCRIBE [nume_schema.]nume_obiect

Editarea comenzilorEditorul integrat SQL *PLUS (mini-editor mod linie) se încarcă prin:

EDIT [nume_fisier[.extensie]]

Dacă se doreşte lansarea în execuţie a unui alt editor, se modifică variabila EDITOR cu ajutorul comenzii DEFINE. De exemplu:

DEFINE _EDITOR = viSQL*Plus păstrează în memorie ultima comandă executată. Comenzile

SQL*Plus nu sunt depuse în buffer-ul SQL. Ele se dau secvenţial, câte una la un moment dat. Dacă comanda este prea lungă, ea va fi continuată pe linia următoare tastând caracterul „–” la sfârşitul liniei, înainte de a tasta RETURN.

Pentru editarea comenzilor SQL şi a blocurilor PL/SQL se pot folosi: A[PPEND] text – adaugă textul specificat la sfârşitul liniei curente din

buffer-ul SQL;

C[HANGE] separator old [separator [new [separator]]] – schimbă textul old cu textul new (ca separator se poate folosi orice caracter care nu este alfanumeric);

DEL {n | n m | n * | n LAST | * | * n | * LAST | LAST} – şterge una sau mai multe linii din buffer-ul SQL (caracterul “*” indică linia curentă); DEL fără nici o clauză are ca efect ştergerea liniei curente;

I[NPUT] [text] – adaugă una sau mai multe linii de text după linia curentă din buffer;

L[IST] {n | n m | n * | n LAST | * | * n | * LAST | LAST} – listează una sau mai multe linii din buffer (caracterul “*” indică linia curentă); LIST fără nici o clauză listează toate liniile din buffer.

SQL 5

Comenzi interactiveSQL*Plus dispune de comenzi ce permit comunicarea directă cu utilizatorul:

comanda PAUSE permite afişarea unei linii vide sau unui text suspendând temporar acţiunea şi aşteptând un RETURN de la utilizator;

comanda PROMPT trimite o linie goală sau un mesaj pe ecran;

comanda ACCEPT permite citirea unei linii de pe ecran şi atribuirea valorii sale unei variabile (utilizator) specificate (variabila poate să fie deja definită prin comanda DEFINE).

ACCEPT variabila [NUMBER | CHAR | DATE]           [FORMAT format] [PROMPT text | NOPROMPT] [HIDE]

Observaţii:

tipul implicit al variabilei este CHAR;

textul de la opţiunea PROMPT este tipărit înainte ca utilizatorul să dea valoarea efectivă;

opţiunea HIDE face ca valoarea dată de utilizator să fie mascată (de exemplu, o parolă);

opţiunea FORMAT indică modul de formatare;

variabila de substituţie, dacă este prezentă, nu trebuie prefixată de simbolul “&”.

Exemplu:ACCEPT alfa PROMPT ’Numarul de exemplare:’ACCEPT beta PROMPT ’Numele autorului:’SELECT *FROM carteWHERE nrex = &alfaAND autor = ’&beta’;

Variabilele de substituţie (&nume) sunt utilizate pentru stocarea temporară a unor valori. Variabilele pot să apară în comenzi SQL sau SQL*Plus. Interfaţa cere utilizatorului să dea valori de fiecare dată când întâlneşte o variabilă nedefinită. Dacă variabila este precedată de simbolurile “&&”, doar la prima apelare se va solicita o valoare. Pentru variabilele de tip caracter sau de tip dată calendaristică este obligatorie folosirea ghilimelelor. Variabilele de substituţie pot să apară în condiţia WHERE, în clauza ORDER BY, în expresia unei coloane, în numele unui tabel, în locul unei întregi comenzi SELECT.

6 Oracle

Exemplu:

SELECT &coloanaFROM &tabelWHERE &conditieORDER BY &ordine;

SQL*Plus permite definirea variabilelor utilizator de tip CHAR prin:

DEFINE variabilă = valoare

Variabila rămâne definită până când fie se părăseşte SQL*Plus, fie se dă comanda UNDEFINE pentru variabila respectivă. Tipărirea tuturor variabilelor utilizator, a valorilor şi tipurilor acestora se obţine prin forma DEFINE.

Exemplu:

SQL> DEFINE autor1 = ZolaSQL> DEFINE autor2 = BlagaSQL> SELECT titlu, nrex 2 FROM carte 3 WHERE autor = ’&autor1’ 4 OR autor = ’&autor2’;

Setări în SQL*Plus

Pentru a preciza opţiunile de lucru cu SQL*Plus se utilizează comanda SET. Setările SQL standard se găsesc în fişierul login.sql, care poate fi modificat pentru a conţine setări adiţionale. Când se termină sesiunea toate setările sunt pierdute.

Comanda SET are două forme sintactice:

una din ele se foloseşte ca Help pentru utilizator;

cealaltă se foloseşte pentru setarea variabilelor sistem.

O variabilă sistem este un câmp rezervat în care se reţine o valoare de către sistemul Oracle, valoare care indică o stare a sistemului. Pentru a afişa valorile tuturor variabilelor de mediu la un moment dat se dă comanda SHOW ALL.

SET  variabila_sistem  valoare

Parametrul variabila_sistem poate lua oricare din valorile care apar la execuţia comenzii SHOW ALL.

SQL 7

În continuare sunt prezentate numai o parte a acestor setări (în special cele folosite la formatarea rapoartelor) împreună cu valorile pe care le poate lua parametrul valoare.

SET RECSEP {WR[APPED] | EA[CH] | OFF} – controlează tipărirea separatorilor de înregistrări. Cele trei valori posibile au următoarele semnificaţii:

- WRAPPED tipăreşte un separator după fiecare linie a unei înregistrări (chiar dacă aceasta cuprinde mai multe linii);

- EACH face acelaşi lucru, dar numai la sfârşitul unei înregistrări;- OFF anulează tipărirea unui separator.

SET RECSEPCHAR {_ | c} – setează caracterul ce separă înregistrările;

SET SPACE {1 | n} – setează numărul de spaţii între coloane.

SET WRA[P] {OFF | ON} – specifică modul de afişare a înregistrărilor (trunchiat sau pe o linie).

SET NULL text – specifică textul afişat în locul valorii NULL.

SET HEADS[EP] {| | c | OFF | ON} – specifică caracterele ce separă numele coloanelor de înregistrări.

SET UND[ERLINE] {_ | c | OFF | ON} – specifică caracterul folosit pentru sublinierea numelor coloanelor.

SET NEWP[AGE] {1 | n | NONE} – setează numărul de linii vide lăsate la începutul fiecărei pagini.

SET TERM[OUT] {OFF | ON} – controlează afişarea output-ului generat de comenzi executate dintr-un script.

SET PAGES[IZE] {24 | n} – specifică numărul de linii afişate pe pagină (n implicit este 24; se poate seta la valoarea 0 pentru a suprima afişarea numelor coloanelor, a titlului unui raport).

SET FEED[BACK] {6 | n | OFF | ON} – controlează afişarea numărului de înregistrări furnizate ca rezultat de o comandă SELECT.

SET ECHO {OFF | ON} – controlează afişarea comenzilor dintr-un script SQL lansat cu comanda START, pe măsură ce acestea sunt executate.

Comanda SPOOL permite salvarea rezultatelor unei interogări într-un fişier indicat ca parametru. Ea are următoarea sintaxă:

SPO[OL] [ file_name[. ext] | OFF | OUT]

8 Oracle

Formatarea rezultatelor

Pentru afişarea unui titlu la începutul sau sfârşitul fiecărei pagini a unui raport se folosesc comenzile TTITLE sau BTITLE, având sintaxa următoare:

{TTI | BTI}[TLE] [spec_tiparire [text | variabila]] [OFF | ON]

Semnificaţia parametrilor împreună cu valorile posibile sunt:

spec_tiparire controlează amplasamentul şi formatarea titlului prin combinarea unora dintre următoarele opţiuni:- COL n indentează titlul la coloana n a liniei curente;- S[KIP] n realizează un salt peste n linii, la începutul unei noi

linii; dacă n are valoarea 0, atunci sare la începutul liniei curente;

- TAB n realizează un salt peste n coloane pe linia curentă (salt înainte dacă n este pozitiv şi salt înapoi dacă n este negativ);

- LE[FT], CE[NTER], RI[GHT] aliniază datele la stânga, în centru sau la dreapta;

- BOLD realizează îngroşarea caracterelor textului;- FOR[MAT] format – formatează coloana conform cu

specificaţia dată de format (de exemplu, dacă coloana este de tip caracter atunci An are semnificaţia că valoarea coloanei se va scrie pe n caractere la afişare);

text reprezintă textul titlului încadrat de ghilimele simple (în cazul în care conţine mai multe cuvinte se poate folosi caracterul ‘|’ pentru împărţirea unui titlu pe mai multe linii);

variabila este o variabilă sistem sau o variabilă definită de utilizator (de exemplu, SQL.PNO este o variabilă sistem care specifică numărul paginii curente; SQL.LNO specifică numărul liniei curente; variabila SQL.USER specifică numărul utilizatorului curent etc.).

Comanda COLUMN permite controlul afişării coloanelor şi a numelor.

COL[UMN] [{coloana | expresie} [opţiune]]

coloana este numele coloanei asupra căreia se aplică sau căreia i se citeşte formatarea într-o comandă SELECT. Dintre valorile parametrului opţiune:

NOPRI[NT] | PRI[NT] – controlează afişarea unei coloane;

ALI[as] alias – specifică un alias pentru coloana sau expresia respectivă;

SQL 9

CLE[AR] – resetează atributele de afişare ale coloanei; pentru a face acest lucru pentru toate coloanele se foloseşte comanda CLEAR COLUMNS;

HEA[DING] text – setează numele coloanei text;

NEW_V[ALUE] – specifică o variabilă în care se reţine valoarea unei coloane; variabila poate fi apoi referenţiată în comanda TTITLE; numele coloanei trebuie inclus într-o comandă BREAK împreună cu SKIP PAGE; această opţiune este utilă atunci când trebuie făcute rapoarte master/detail şi fiecare înregistrare master trebuie să apară pe o nouă pagină;

OLD_V[ALUE] – semnificaţia este cea anterioară, dar cu deosebirea că referenţierea variabilei se face într-o comandă BTITLE;

JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]} – aliniază numele coloanelor (implicit are valoarea R[IGHT]);

FOR[MAT] format – aceeaşi semnificaţie ca cea descrisă pentru BTITLE sau TTITLE.

Exemplu:

COLUMN sal JUSTIFY LEFTCOLUMN autor NULL 'necunoscut'

SQL*Plus permite regruparea liniilor rezultatului în funcţie de una sau mai multe valori ale coloanelor şi eventual efectuarea unor acţiuni la fiecare rupere. Liniile rezultatului trebuie să fie ordonate (ORDER BY) după coloana în funcţie de ale cărei valori se fac ruperile. Elementul după care se face fragmentarea poate fi:

un nume de coloană sau o expresie (valoarea coloanei sau a expresiei este afişată doar pentru prima linie a grupului);

ROW (acţiunea este executată pentru fiecare linie);

REPORT (acţiunea este executată la sfârşitul raportului).

Comanda BREAK modifică formatul unui raport într-unul din modurile:

suprimă afişarea valorilor duplicate pentru o anumită coloană;

sare peste o linie când valoarea unei coloane se schimbă;

tipăreşte expresii calculate utilizând comanda COMPUTE de fiecare dată când valoarea unei coloane se modifică sau la sfârşitul unui raport.

BRE[AK] [ON element_fragmentare [acţiune [acţiune]…]]

Parametrii acesteia au forma:

10 Oracle

element_fragmentare {coloană | expresie | ROW | REPORT}acţiune [SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]

Exemplu:

BREAK ON autor SKIP 4 ON intdom SKIP 2

Utilizatorul poate realiza operaţii de calcul asupra liniilor fiecărui subansamblu creat prin BREAK. Pentru calculul anumitor funcţii standard (pe coloane) se foloseşte comanda COMPUTE care are următoarea formă sintactică:

COMP[UTE]  [function  [LAB[EL] text] …           OF  {expr | column | alias} …           ON  {expr | column | alias | REPORT | ROW} …]

Opţiunile au următoarele semnificaţii:

function poate fi una dintre funcţiile AVG, COU[NT], MAX[IMUM], MIN[IMUM], NUM[BER], STD, SUM, VAR[IANCE];

text defineşte eticheta afişată pentru valoarea calculată;

OF {expr | column | alias} specifică coloanele sau expresiile folosite în funcţia calculată; acestea trebuie să apară şi în comanda SELECT, altfel SQL*Plus ignoră comanda COMPUTE; column nu poate fi ataşată unui tabel sau unei vizualizări decât folosind un alias pentru coloana respectivă;

ON {expr | column | alias | REPORT | ROW} specifică evenimentul folosit de SQL*Plus ca break, adică elementul de fragmentare.

Ca şi în cazul comenzilor COLUMN sau BREAK, COMPUTE făra nici o clauză listează toate definiţiile de COMPUTE.

Pentru realizarea corectă a calculului funcţiei trebuie îndeplinite condiţiile:

una sau mai multe dintre expresiile, coloanele sau alias-urile de coloane referenţiate în clauza OF trebuie să apară şi în comanda SELECT;

expresia, coloana sau alias-ul de coloană referenţiat în clauza ON trebuie să apară în comanda SELECT şi în cea mai recentă comandă BREAK;

dacă ROW sau REPORT sunt referenţiate în clauza ON atunci ele trebuie să apară şi în cea mai recentă comandă BREAK.

Să se calculeze valoarea medie a preţurilor cărţilor fiecărui autor.

SQL 11

BREAK ON autor SKIP 1 COMPUTE AVG OF pret ON autor SELECT autor,pret FROM carte ORDER BY autor;

Comanda CL[EAR] opţiune permite resetarea valorilor specificate în anumite opţiuni. Parametrul opţiune reprezintă una din următoarele clauze: BRE[AKS] , BUFF[ER], COL[UMNS], COMP[UTES], SCR[EEN], SQL, TIMI[NG].

Copierea datelorPentru a copia datele furnizate de o interogare într-un tabel aflat într-o bază

de date locală sau la distanţă se poate folosi comanda COPY, care permite: copierea unui tabel dintr-o bază locală într-o bază distantă (opţiunea FROM

poate fi omisă); copierea unui tabel dintr-o bază distantă într-o bază locală (opţiunea TO

poate fi omisă); copierea unui tabel dintr-o bază distantă într-o bază distantă.

COPY {FROM username[ /password]@ database_specification|       TO username[ /password]@ database_specification}       {APPEND | CREATE | INSERT | REPLACE}                  destination_table [(column, column, ...)]        USING query

database_specification reprezintă un şir de conectare SQL*Net;

APPEND inserează liniile întoarse de query în destination_table dacă acesta există; în caz contrar, creează în prealabil tabelul;

CREATE inserează liniile întoarse de query în destination_table, dupa ce creează tabelul; daca acesta deja există, COPY returnează o eroare;

INSERT inserează liniile întoarse de query în destination_table; dacă tabelul nu există COPY returnează o eroare; când se foloseşte opţiunea INSERT, clauza USING query trebuie să selecteze câte o coloană pentru fiecare coloană din destination_table;

REPLACE înlocuieşte destination_table şi conţinutul său cu liniile întoarse de query; dacă destination_table nu există, atunci COPY crează tabelul; altfel, COPY şterge mai întâi tabelul existent şi îl înlocuieşte cu un tabel ce conţine datele copiate.

Exemplu:

12 Oracle

Să se înlocuiască conţinutul tabelului produse cu informaţii ce provin dintr-o bază de date distantă (biblioteca). Aceste informaţii se referă la cărţile de informatică din tabelul carte.

COPY FROM vasile/ana@bibliotecaREPLACE produseUSING SELECT * FROM carte WHERE coded = ’I’;

Exemplu:Să se creeze un fişier care tipăreşte codul cărţilor împrumutate şi codul

cititorilor care au împrumutat aceste cărţi, la o dată ce aparţine unui interval specificat. Să se concateneze cele două coduri astfel încât să apară separate prin virgulă şi având numele informatii.

Soluţie:

SET ECHO OFF FEEDBACK OFFACCEPT low_date DATE FORMAT ’MM/DD/YY’ – PROMPT ’dati limita inferioara a datei:’ACCEPT high_date DATE FORMAT ’MM/DD/YY’ – PROMPT ’dati limita superioara a datei:’COLUMN informatii FORMAT A25SELECT codel || ’,’ || codec informatii, dataimFROM imprumutaWHERE dataim BETWEEN TO_DATE(’&low_date’, ’MM/DD/YY’) AND TO_DATE(’&high_date’, ’MM/DD/YY’)/UNDEFINE low_dateUNDEFINE high_dateCLEAR COLUMNSSET ECHO ON SET FEEDBACK ON

Exemplu:Să se creeze un fişier care tipăreşte un raport grupat pe autori, conţinând:

numele autorului, titlul cărţilor sale din bibliotecă, domeniul fiecărei cărţi şi

SQL 13

valoarea totală a fiecărei cărţi. Aceste rezultate sunt obţinute pentru cărţile din bibliotecă care sunt într-un număr de exemplare specificat. Rezultatele să apară sub forma:

NUME TITLU DOMENIU VALOARE AUTOR CARTE CARTE TOTALA Oprean Grupuri Algebra 55,000 Inele Algebra 63,768

Soluţie:

SET ECHO OFF FEEDBACK OFFACCEPT num PROMPT ’Dati numarul de exemplare:’COLUMN autor HEADING ’NUME|AUTOR’ FORMAT A15COLUMN titlu HEADING ’TITLU|CARTE’ FORMAT A15COLUMN intdom HEADING ’DOMENIU|CARTE’ FORMAT A15COLUMN val HEADING ’VALOARE|TOTALA’ FORMAT $99,999BREAK ON autorSELECT autor, titlu, intdom, pret*nrex valFROM carte c, domeniu dWHERE c.coded = d.codedAND nrex = &numORDER BY autor/UNDEFINE numSET ECHO ON FEEDBACK ONCLEAR BREAKSCLEAR COLUMNS

Exemplu:Să se creeze un script la a cărui rulare să apară următorul raport:

14 Oracle

CONTRACTELE SUBANTREPRENORULUI CONSTRUCT SALA DATA DE 14/10/2001

NUMAR VALOARENUME CONTRACT CONTRACT---------------------- ---------------- ---------------CONSTRUCT SA 100 50000 101 120000******************** ---------VALOAREA MAXIMA 120000TOTAL 170000

Pagina 1

CONTRACTELE SUBANTREPRENORULUI ERBASU SALA DATA DE 14/10/2001

NUMAR VALOARENUME CONTRACT CONTRACT---------------- --------------- ----------------ERBASU SA 107 20000 108 300000 109 750000******************** ---------VALOAREA MAXIMA 750000TOTAL 1070000

Pagina 2CONTRACTELE SUBANTREPRENORULUI GAUDILA DATA DE 14/10/2001

NUMAR VALOARENUME CONTRACT CONTRACT----------- --------------- ----------------GAUDI 104 500000 105 27000 106 10000******************** ---------VALOAREA MAXIMA 500000TOTAL 537000

Pagina 3

SQL 15

Soluţie:

SET ECHO OFF FEEDBACK OFFCOLUMN cod NOPRINTCOLUMN name NEW_VALUE nv_name HEADING 'NUME'COLUMN nrct FORMAT A8 HEADING 'NUMAR|CONTRACT'COLUMN val HEADING 'VALOARE|CONTRACT'COLUMN azi NOPRINT NEW_VALUE nv_aziTTITLE SKIP 1 LEFT 'CONTRACTELE SUBANTREPRENORULUI'-       nv_name SKIP 1 LEFT 'LA DATA DE 'nv_azi SKIP 2 BTITLE SKIP 1 LEFT 'Pagina:' FORMAT 99 SQL.PNO COMPUTE MAX LABEL 'VALOAREA MAXIMA' SUM LABEL –                  'TOTAL' OF val ON nameBREAK ON name SKIP PAGE ON nameSELECT   ct.cod_contractant cod,sub.nume name,          ct.nr_contract nrct, ct.val_investitie val, TO_CHAR(SYSDATE,'DD/MM/YYYY') aziFROM     contract ct, subantreprenor subWHERE    ct.cod_contractant = sub.cod_contractant AND      ct.tip_contract = 1ORDER BY cod/SET ECHO ON FEEDBACK ONCLEAR BREAKSCLEAR COLUMNSCLEAR COMPUTES

Exemplu:

16 Oracle

Presupunem că tabelul carte conţine un câmp suplimentar numit tip, prin care se specifică tipul unei cărţi. Acest tip poate fi: roman, poezie, monografie, culegere sau diverse. Să se genereze un raport care să conţină pentru fiecare autor: numărul exemplarelor de fiecare tip scrise de acesta şi numărul total de exemplare. De asemenea, să se obţină în finalul raportului, numărul exemplarelor de fiecare tip din bibliotecă.

Soluţie:

SET ECHO OFF FEEDBACK OFF COLUMN autor FORMAT A12 HEADING ’Nume|autor’ COLUMN roman FORMAT 99999 HEADING ’Roman’ COLUMN poezie FORMAT 99999 HEADING ’Poezie’ COLUMN monografie FORMAT 99999 HEADING ’Monografie’ COLUMN culegere FORMAT 99999 HEADING ’Culegere’ COLUMN diverse FORMAT 99999 HEADING ’Diverse’ COLUMN total FORMAT 99999 HEADING ’Total’ BREAK ON REPORT SKIP 2 COMPUTE SUM OF roman ON REPORT COMPUTE SUM OF poezie ON REPORT COMPUTE SUM OF monografie ON REPORT COMPUTE SUM OF culegere ON REPORT COMPUTE SUM OF diverse ON REPORT COMPUTE SUM OF total ON REPORT TTITLE CENTER ’Raport carte’ SELECT autor, SUM(DECODE(tip,’roman’,nrex,0)) roman, SUM(DECODE(tip,’poezie’,nrex,0)) poezie, SUM(DECODE(tip,’monografie’,nrex,0))monografie, SUM(DECODE(tip,’culegere’,nrex,0))culegere, SUM(DECODE(tip,’diverse’,nrex,0)) diverse, SUM(nrex) total FROM carte GROUP BY autor / CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES SET ECHO ON FEEDBACK ON

SQL 17

Exemplu:Să se genereze un raport prin care să se obţină pentru fiecare domeniu de

carte informaţii despre numele domeniului, titlurile cărţilor din domeniu, numărul de exemplare din fiecare carte şi din fiecare domeniu, iar în final raportul să conţină şi numărul total de cărţi din bibliotecă.

Soluţie:

SET ECHO OFF FEEDBACK OFF COLUMN intdom FORMAT A25 HEADING ’denumire|domeniu’ COLUMN titlu FORMAT A30 HEADING ’titlu|carte’ COLUMN nrex FORMAT 99999 HEADING ’numar|carti’ BREAK ON intdom SKIP 2 ON REPORT SKIP 1 COMPUTE SUM OF nrex ON intdom COMPUTE SUM OF nrex ON REPORT TTITLE CENTER ’Raport carti’ SKIP 1 LINE SELECT intdom, titlu, nrex FROM carte c, domeniu d WHERE c.coded = d.coded ORDER BY intdom / COLUMN denumire CLEAR COLUMN titlu CLEAR COLUMN nrex CLEAR CLEAR BREAKS CLEAR COMPUTES SET ECHO ON FEEDBACK ON

18 Oracle

SQL

Structured Query Language (SQL) este un limbaj universal care poate fi utilizat pentru a defini, interoga, reactualiza şi gestiona baze de date relaţionale. SQL este accesibil utilizatorilor începători, dar în acelaşi timp poate oferi programatorilor experimentaţi facilităţi deosebite. SQL este un limbaj non-procedural, adică se specifică ce informaţie este solicitată, dar nu modul cum se obţine această informaţie. SQL poate fi utilizat autonom sau prin inserarea comenzilor sale într-un limbaj de programare. SQL a sistemului Oracle este o extensie a normei SQL89 şi o implementare a normei SQL92.

În SQL se disting trei familii de comenzi: Comenzi pentru definirea datelor, care permit descrierea (definirea)

obiectelor ce modelează sistemul studiat. Aceste comenzi definesc limbajul de definire a datelor (LDD).

Comenzi pentru manipularea datelor, care permit consultarea, reactualizarea, suprimarea sau inserarea datelor. Aceste comenzi definesc limbajul de manipulare a datelor (LMD).

Comenzi pentru controlul datelor, care permit asigurarea confidenţialităţii şi integrităţii datelor, salvarea informaţiei, realizarea fizică a modificărilor în baza de date, rezolvarea unor probleme de concurenţă. Aceste comenzi definesc limbajul de control al datelor (LCD).

Sistemul impune anumite restricţii asupra identificatorilor. Numele unui obiect nu poate depăşi 30 de caractere, cu excepţia numelui

bazei de date care este limitat la 8 caractere şi a numelui legăturii unei baze care poate ajunge la 128 caractere.

Nu se face distincţie între litere mici şi litere mari. Numele trebuie să înceapă printr-un caracter alfabetic şi nu poate fi un

cuvânt cheie rezervat; poate să conţină literele mari şi mici ale alfabetului englez, cifrele 0 - 9 şi caracterele $, _, #.

Un utilizator nu trebuie să definească două obiecte cu acelaşi nume. În general este bine ca numele unui obiect să fie descriptiv şi fără

prescurtări excesive.

SQL 19

Limbajul de definire a datelor

Limbajul de definire a datelor constă din acele instrucţiuni SQL (CREATE, ALTER, DROP) care permit crearea, modificarea şi distrugerea obiectelor BD.

Principalele obiecte ce pot fi definite şi manipulate în sistemul Oracle sunt următoarele: tabele (table), indecşi (index), secvenţe (sequence), vizualizări (view), proceduri (procedure), funcţii (function), pachete (package), declanşatori (trigger), clişee (snapshot) etc.

Tipuri de date

Pentru memorarea datelor numerice, tipurile cele mai frecvent folosite sunt: NUMBER, INTEGER, FLOAT, DECIMAL.

Pentru memorarea şirurilor de caractere, cele mai frecvent tipuri de date utilizate sunt: CHAR, VARCHAR2 şi LONG.

Există restricţii referitoare la folosirea tipului de date LONG. Într-un tabel poate să fie o singură coloană de tip LONG. Nu pot fi comparate două şiruri de caractere de tip LONG. O coloană de tip LONG nu poate fi parametru într-o procedură. O funcţie nu poate întoarce ca rezultat o valoare de tip LONG. O coloană de tip LONG nu poate fi folosită în clauzele WHERE, ORDER BY,

GROUP BY, CONNECT. Operatorii sau funcţiile Oracle nu pot fi folosiţi în SQL pentru a modifica

coloane de tip LONG. O coloană de tip LONG nu poate fi indexată.

Alte tipuri de date scalare furnizate de SQL sunt NCHAR şi NVARCHAR2, folosite pentru reprezentarea caracterelor limbilor naţionale.

Informaţii relative la timp sau dată calendaristică se obţin utilizând tipul DATE. Pentru fiecare dată de tip DATE sunt depuse: secolul, anul, luna, ziua, ora, minutul, secunda. Pentru o coloană de tip DATE sistemul rezervă 7 bytes, indiferent dacă se memorează doar timpul, sau doar data calendaristică.

Formatul implicit al datei se defineşte cu ajutorul parametrului de iniţializare NLS_DATE_FORMAT. În general, acest parametru este setat la forma DD-MON-YY. Dacă nu este specificat timpul, timpul implicit este 12:00:00.

20 Oracle

În Oracle8, alături de aceste tipuri scalare, există şi tipuri de date LOB (Large Objects), care specifică locaţia unor obiecte de dimensiuni mari.

Tabele

Crearea unui tabel

Crearea unui tabel constă din generarea structurii sale, adică atribuirea unui nume tabelului şi definirea caracteristicelor sale (se definesc coloanele, se definesc constrângerile de integritate, se specifică parametrii de stocare etc.).

Pentru a crea un tabel, utilizatorul trebuie să aibă acest privilegiu şi să dispună de spaţiul de memorie în care să creeze obiectul. La nivelul schemei sale, un utilizator are toate privilegiile.

CREATE TABLE [<nume_schema>.] <nume_tabel> (                   <nume_coloana_1> <tip_date> [DEFAULT <expresie>],                  …                  <nume_coloana_n> <tip_date> [DEFAULT <expresie>])  [CLUSTER <nume_cluster> (<coloana_1>,…, <coloana_m>)]  [ENABLE | DISABLE <clause>];

Comanda poate conţine opţional clauza TABLESPACE, care specifică spaţiul tabel în care va fi stocat tabelul. De asemenea, poate conţine opţional clauza STORAGE care este folosită pentru setarea parametrilor de stocare prin intermediul cărora se specifică mărimea şi modul de alocare a extinderilor segmentului tabel. La crearea unui tabel nu este nevoie să se specifice dimensiunea maximă a acestuia, ea fiind determinată până la urmă de cât de mult spaţiu a fost alocat spaţiului tabel în care este creat tabelul.

Structura unui tabel poate fi creată în următoarele patru moduri:

fără a indica cheile;

indicând cheile la nivel de coloană;

indicând cheile la nivel de tabel;

prin copiere din alt tabel.

1. Crearea structurii unui tabel fără a indica cheile:

CREATE TABLE carte (codel CHAR(5),

SQL 21

titlu VARCHAR2(30), autor VARCHAR2(30), pret NUMBER(8,2), nrex NUMBER(3), coded CHAR(5));

2. Crearea structurii unui tabel indicând cheile la nivel coloană:

CREATE TABLE carte (codel CHAR(5) PRIMARY KEY, titlu VARCHAR2(30), autor VARCHAR2(30), pret NUMBER(8,2), nrex NUMBER(3), coded CHAR(5) NOT NULL

REFERENCES domeniu(coded));

Constrângerea de cheie primară sau externă ce presupune?

CREATE TABLE carte (codel CHAR(5) PRIMARY KEY, titlu VARCHAR2(30), autor VARCHAR2(30), pret NUMBER(8,2), nrex NUMBER(3), coded CHAR(5) NOT NULL

REFERENCES domeniu(coded)ON DELETE CASCADE);

Opţiunea ON DELETE CASCADE specifică că suprimarea oricărui domeniu de carte din tabelul domeniu este autorizată şi implică suprimarea automată a tuturor cărţilor din domeniul respectiv care se găsesc în tabelul carte.

3. Crearea structurii unui tabel indicând cheile la nivel de tabel:

CREATE TABLE carte (codel CHAR(5), titlu VARCHAR2(30), autor VARCHAR2(30), pret NUMBER(8,2),

22 Oracle

nrex NUMBER(3), coded CHAR(5) NOT NULL, PRIMARY KEY (codel),

FOREIGN KEY (coded) REFERENCES domeniu (coded));

Dacă cheia primară are mai mult de o coloană atunci cheile trebuie indicate la nivel de tabel.

CREATE TABLE imprumuta (codel CHAR(5), codec CHAR(5), dataim DATE DEFAULT SYSDATE, datares DATE, dataef DATE, PRIMARY KEY (codel, codec, dataim), FOREIGN KEY (codel) REFERENCES carte(codel), FOREIGN KEY (codec) REFERENCES cititor(codec));

4. Crearea structurii unui tabel prin copiere din alt tabel:

CREATE TABLE carte_info AS SELECT codel, titlu, autor FROM carte WHERE coded = ’I’;

Constrângerile din primul tabel nu se păstrează şi pentru al doilea tabel. Comanda creează un tabel, dar şi inserează date în tabel.

Constrângeri

Constrângere este un mecanism care asigură că valorile unei coloane sau a unei mulţimi de coloane satisfac o condiţie declarată. Unei constrâgeri i se poate da un nume unic. Dacă nu se specifică un nume explicit atunci sistemul automat îi atribuie un nume de forma SYS_Cn, unde n reprezintă numărul constrângerii. Constrângerile pot fi şterse, pot fi adăugate, pot fi activate sau dezactivate, dar nu pot fi modificate.

Exemplu:Să se definească o constrângere la nivel de coloană prin care să se specifice

cheia primară şi cheia externă.

SQL 23

CREATE TABLE carte(codel CHAR(5)

CONSTRAINT cp_carte PRIMARY KEY, titlu VARCHAR2(30),… coded CHAR(5)

CONSTRAINT nn_coded NOT NULLCONSTRAINT ce_coded REFERENCES domeniu(coded));

Exemplu:Să se definească o constrângere la nivel de tabel prin care să se specifice

cheia primară şi cheia externă.

CREATE TABLE carte(codel CHAR(5), titlu VARCHAR2(30),… coded CHAR(5) NOT NULL, CONSTRAINT cp_carte PRIMARY KEY (codel), CONSTRAINT ce_coded FOREIGN KEY (coded) REFERENCES domeniu(coded));

Observaţii Liniile ce nu respectă constângerea sunt depuse automat într-un tabel special.

Constrângerile previn ştergerea unui tabel dacă există dependenţe.

Constrângerile pot fi create o dată cu tabelul sau după ce acesta a fost creat.

Constrângerile pot fi activate sau dezactivate în funcţie de necesităţi.

Constrângeri declarative: constrângeri de domeniu, constrângerea de integritate a entităţii, constrângerea de integritate referenţială.

Constrângerile de domeniu definesc valori luate de un atribut (DEFAULT, CHECK, UNIQUE, NOT NULL).

constrângerea (coloană) DEFAULT ;

constrângerea (coloană sau tabel) CHECK ; constrângerea CHECK la nivel de tabel poate compara coloane între ele, poate face referinţă la una sau mai multe coloane, dar nu poate conţine subcereri. Constrângerea la nivel de coloană nu poate referi alte coloane ale aceluiaşi tabel.

24 Oracle

CREATE TABLE carte (codel CHAR(5),… pret NUMBER(8,2)

CONSTRAINT alfaCHECK (pret < nrex),…);

La execuţia acestei comenzi apare mesajul: ORA – 02438: Column check constraint cannot reference other columns. Dacă se adaugă o virgulă după NUMBER(8, 2) atunci constrângerea va fi la nivel de tabel, iar în aceste caz este permisă referirea altei coloane. constrângerea (coloană sau tabel) UNIQUE ; constrângerea declarativă NOT NULL poate fi doar la nivel coloană.

Constrângerea de integritate a entităţii precizează cheia primară a unui tabel. Când se creează cheia primară se generează automat un index unic. Valorile cheii primare sunt distincte şi diferite de valoarea null.

Constrângerea de integritate referenţială asigură coerenţa între cheile primare şi cheile externe corespunzătoare. Când este definită o cheie externă sistemul Oracle verifică:

dacă a fost definită o cheie primară pentru tabelul referit de cheia externă;

dacă numărul coloanelor ce compun cheia externă corespunde numărului de coloane a cheii primare;

dacă tipul şi lungimea fiecărei coloane a cheii externe corespunde cu tipul şi lungimea fiecărei coloane a cheii primare.

În versiunea Oracle8 există posibilitatea ca o constrângere să fie amânată (DEFERRABLE). În acest caz, mai multe comenzi SQL pot fi executate fără a se verifica restricţia, aceasta fiind verificată numai la sfârşitul tranzacţiei, atunci când este executată comanda COMMIT. Dacă vreuna din comenzile tranzacţiei încalcă restricţia, atunci întreaga tranzacţie este derulată înapoi şi este returnată o eroare. Opţiunea implicită este NOT DEFERRABLE.

O noutate introdusă în Oracle8 este posibilitatea de a partiţiona tabele, adică de a împărţi tabelul în mai multe părţi independente, fiecare cu parametri de stocare diferiţi şi cu posibilitatea ca părţi diferite ale tabelului să se găsească pe spaţii tabel diferite. Fiecare partiţie a tabelului va conţine înregistrări care au valoarea cheii într-un interval specificat. Partiţionarea este transparentă pentru utilizatori şi aplicaţii. Dacă o parte a tabelului este inaccesibilă, celelalte părţi pot fi disponibile pentru reactualizare. De asemenea, se poate bloca accesul la o parte a tabelului în timp ce restul înregistrărilor sunt disponibile.

SQL 25

Exemplu:

CREATE TABLE carte ( ) PARTITIONED BY RANGE (nrex) ((PARTITION mic VALUES LESS THAN(2) TABLESPACE… STORAGE …), PARTITION mediu VALUES LESS THAN (10) TABLESPACE… STORAGE …), PARTITION mare VALUES LESS THAN (MAXVALUE) TABLESPACE… STORAGE …));

Modificarea structurii unui tabel

Comanda care realizează modificarea structurii tabelului (la nivel de coloană sau la nivel de tabel), dar nu modificarea conţinutului acestuia, este ALTER TABLE. Comanda ALTER TABLE permite:

adăugarea (ADD) de coloane, chei (primare sau externe), constrângeri într-un tabel existent;

modificarea (MODIFY) coloanelor unui tabel;

specificarea unei valori implicite pentru o coloană existentă;

activarea şi dezactivarea (ENABLE, DISABLE) unor constrângeri;

suprimarea (DROP) cheii primare, a cheii externe sau a unor constrângeri.

Comanda ALTER TABLE are următoarea sintaxă simplificată:

ALTER TABLE [<nume_schema>.] <nume_tabel>  [ADD          (<nume_coloana> <tip_date>, <constrângere>) |  MODIFY       (<nume_coloana_1>,…, <nume_coloana_n>) |  DROP          <clauza_drop>,]  [ENABLE | DISABLE <clause>];

1. Pentru a adăuga o coloană, o cheie primară, o cheie externă sau o constrângere unui tabel este folosită următoarea formă:

ALTER TABLE nume_tabelADD (nume_coloana constrangere,…

nume_coloana constrangere);

26 Oracle

2. Pentru a modifica una sau mai multe coloane existente:

ALTER TABLE nume_tabelMODIFY (nume_coloana constrangere,…

nume_coloana constrangere);

3. Pentru a suprima cheia primară sau alte constrângeri sunt utilizate formele:

ALTER TABLE nume_tabelDROP PRIMARY KEY;ALTER TABLE nume_tabelDROP CONSTRAINT nume_constrangere;

4. Pentru a activa (ENABLE) sau dezactiva (DISABLE) constrângeri este utilizată forma:

ALTER TABLE nume_tabelENABLE nume_constrangere;

Observaţii Schimbarea definiţiei unei coloane din NOT NULL în NULL O.K.

Schimbarea definiţiei unei coloane din NULL în NOT NULL se poate face doar dacă există siguranţa că fiecare linie a coloanei respective este NOT NULL sau dacă tabelul (???sau coloana???) este vid.

Pentru mărirea lungimii coloanei nu există condiţii speciale. Pot reduce lungimea unei coloane sau pot schimba tipul unei coloane doar dacă coloana nu conţine valori sau conţine doar valori NULL.

Definirea cheii primare sau a cheii externe după crearea tabelului.

CREATE TABLE carte (CODEL char(5), …);ALTER TABLE carteADD CONSTRAINT cheie_prim PRIMARY KEY (codel);

Suprimarea cheii primare.

ALTER TABLE carteDROP PRIMARY KEY;

Dacă există o CE care referă o CP şi dacă se încearcă ştergerea cheii primare, această ştergere nu se poate realiza (tabelele sunt legate prin declaraţia de cheie externă). Ştergerea este totuşi permisă dacă în comanda ALTER apare opţiunea CASCADE, care determină şi ştergerea cheilor externe ce referă cheia primară.

SQL 27

ALTER TABLE carteDROP PRIMARY KEY CASCADE;

Suprimarea cheii externe.

ALTER TABLE carteADD CONSTRAINT beta

FOREIGN KEY (coded) REFERENCES domeniu;ALTER TABLE carteDROP CONSTRAINT beta;

Schimbarea cheii primare. Este destul de complicat procesul schimbării cheii primare fără a afecta modul de proiectare a bazei de date. Schimbarea se face în două etape: se şterge cheia primară şi apoi se recreează.

ALTER TABLE carteADD (PRIMARY KEY(codel));ALTER TABLE carteDROP PRIMARY KEY;ALTER TABLE carteADD PRIMARY KEY(titlu, autor));

Adăugarea unei coloane. Această coloană iniţial va fi null (pentru toate liniile). Nu se poate specifica unde să apară coloana, ea devenind ultima coloană a tabelului.

ALTER TABLE carteADD (rezumat LONG);

Suprimarea unei coloane. se creează un tabel selectând toate câmpurile, cu excepţia coloanei care trebuie ştearsă (CREATE TABLE AS SELECT …);se transferă privilegiile;se şterge tabelul iniţial.

În Oracle9i se poate şterge direct prin DROP COLUMN.

Constrângerile pot fi adăugate (ADD CONSTRAINT), şterse (DROP CONSTRAINT), activate (ENABLE) sau dezactivate (DISABLE), dar nu pot fi modificate.

ALTER TABLE cititorADD CONSTRAINT cp_cititor

PRIMARY KEY (codec) DISABLE;

28 Oracle

ALTER TABLE cititorENABLE CONSTRAINT cp_cititor;

Prima comandă adaugă o constrângere, dar nu-i dă viaţă. Constrângerea există, dar server-ul nu o verifică. Când se activează o constrângere, sistemul controlează toate liniile tabelului şi inserează într-un tabel special toate liniile care nu verifică constrângerea. Tabelul are următoarea structură:

(ROW_ID ROWID(OWNER VARCHAR2(30),(TABLE_NAME VARCHAR2(30),(CONSTRAINT VARCHAR2(30))

Din punct de vedere fizic, comanda ALTER TABLE permite schimbarea parametrilor PCTFREE şi PCTUSED şi a parametrilor din clauza STORAGE.

Comanda permite alocarea (ALLOCATE EXTENT) şi dealocarea (DEALLOCATE UNUSED) manuală a spaţiului utilizat de către un tabel. Alocarea se face prin adăugarea de noi extinderi, iar dealocarea reprezintă eliberarea spaţiului nefolosit de tabel (care nu a fost folosit niciodată sau a devenit liber datorită ştergerii unor linii).

Suprimarea unui tabel

DROP TABLE [<nume_schema>.]<nume_tabel>;

Suprimarea unui tabel presupune:

suprimarea definiţiei sale în dicţionarul datelor;

suprimarea indecşilor asociaţi;

suprimarea privilegiilor conferite în legătură cu tabelul;

recuperarea spaţiului ocupat de tabel;

invalidarea (dar nu suprimarea) funcţiilor, procedurilor, vizualizărilor, sinonimelor referitoare la tabel.

DROP TABLE este ireversibilă. Nu poate fi făcut un rollback pe această comandă. In DD, informaţiile despre tabele se găsesc în vizualizarea USER_TABLES. Dintre cele mai importante coloane ale acesteia, fac parte:

TABLE_NAME Numele tabeluluiTABLESPACE_NAME Spaţiul tabel în care se află tabelulCLUSTER_NAME Numele cluster-ului din care face parte tabelulPCT_FREE Procentul de spaţiu păstrat liber în interiorul fiecărui bloc

SQL 29

PCT_USED Procentul de spaţiu ce poate fi utilizat în fiecare blocINI_TRANS Numărul iniţial de tranzacţii concurente în interiorul unui

blocNITIAL_EXTENT Dimensiunea spaţiului alocat pentru prima extensieNEXT_EXTENT Dimensiunea spaţiului alocat pentru următoarea extensieMIN_EXTENTS Numărul minim de extensii ce se alocă la crearea unui tabelMAX_EXTENTS Numărul maxim de extensii ce se alocă la crearea unui tabelPCT_INCREASE Procentul cu care creşte dimensiunea unei extensiiBACKED_UP Y sau N, după cum tabelului i-a fost făcută o copie de

siguranţă de la ultima modificareNUM_ROWS Numărul de înregistrări din tabelBLOCKS Numărul de blocuri utilizate de tabelEMPTY_BLOCKS Numărul de blocuri ce nu conţin dateAVG_SPACE Spaţiul mediu liber din tabelAVG_ROW_LEN Lungimea medie, în octeţi, a unei liniiTABLE_LOCK ENABLED (activat) sau DISABLED (dezactivat): este

activată sau nu blocarea tabeluluiPARTITIONED YES sau NO, indică dacă tabelul este partiţionat (sau nu)TEMPORARY Y sau N, indică dacă tabelul este temporar (sau nu)NESTED YES sau NO, indică dacă tabelul este imbricat (sau nu)

DESCRIBE USER_TABLES;SELECT TABLE_NAME, NUM_ROWS, NESTEDFROM USER_TABLES;

Indecşi

Un index este un obiect al schemei unei baze de date care: creşte viteza de execuţie a cererilor; garantează că o coloană conţine valori unice.

Server-ul Oracle utilizează identificatorul ROWID pentru regăsirea liniilor în structura fizică a bazei de date. Indexul, din punct de vedere logic, este compus dintr-o valoare cheie şi din identificatorul adresă ROWID.

Cheia indexului poate fi coloana unui tabel sau concatenarea mai multor coloane (numărul maxim de coloane care pot defini cheia indexului este 32). Coloanele care apar în cheia indexului trebuie declarate NOT NULL în tabel.

30 Oracle

Indecşii, fiind obiecte ale schemei bazei, beneficiază de procesul de definire a unui obiect. Un index unic este creat automat când în definirea unui tabel apar constrîngerile PRIMARY KEY sau UNIQUE. Crearea unui index pe una sau mai multe coloane ale unui tabel se face prin comanda:

CREATE [UNIQUE] INDEX <nume_index>ON [<nume_schema>.] <nume_tabel>           (<nume_col> [ASC | DESC], <nume_col> [ASC | DESC], …)       | CLUSTER <nume_cluster>];

Când este creat un index, un segment de date este rezervat automat în spaţiul tabel. Alocarea de memorie este controlată prin clauzele INITIAL, PCTINCREASE, PCTFREE, NEXT, care pot să apară în comanda CREATE INDEX. Gestiunea inserţiilor şi a reactualizărilor se face, ca şi la tabele, utilizând parametrii PCTFREE şi PCTUSED.

Exemplu:1) Să se creeze un index descrescător relativ la coloana adresa din tabelul cititor.2) Să se afişeze informaţiile referitoare la indexul cititor_idx.

Soluţie:

CREATE INDEX cititor_idx ON cititor (adresa DESC);SELECT TABLE_NAME, UNIQUENESS, MIN_EXTENTSFROM USER_INDEXESWHERE INDEX_NAME='cititor_idx';

Ştergerea unui index se face prin comanda:

DROP INDEX nume_index [ON [nume_schema.] nume_tabel]

Pentru a suprima indexul trebuie ca acesta să se găsească în schema personală sau să ai privilegiul de sistem DROP ANY INDEX.

Pentru a reconstrui un index se pot folosi două metode: se şterge indexul (DROP INDEX) şi se recreează (CREATE INDEX); se utilizează comanda ALTER INDEX cu opţiunea REBUILD.

Modificarea parametrilor de stocare a indecşilor (STORAGE), alocarea (ALLOCATE EXTENT) şi dealocarea (DEALLOCATE UNUSED) manuală a spaţiului utilizat de un index se pot realiza cu ajutorul comenzii ALTER INDEX.

Validarea unui index, adică verificarea integrităţii indexului specificat pentru un tabel, se face prin comanda:

SQL 31

VALIDATE INDEX nume_index [ON nume_tabel] [WITH LIST]

Oracle8 foloseşte următoarele tipuri de indecşi:

index de tip arbore B* – creat la executarea unei comenzi standard CREATE INDEX;

index partiţionat – folosit în cazul tabelelor mari pentru a stoca valorile coloanei indexate în mai multe segmente;

index de cluster – bazat pe coloanele comune ale unui cluster;

index cu cheie inversă – sunt B* arbori, dar care stochează datele în mod invers;

index de tip bitmap – nu se stochează valorile efective ale coloanei indexate, ci un bitmap format pe baza acestor valori.

Ce tabele sau ce coloane trebuie (sau nu) indexate?

indexaţi tabelele pentru care interogările selectează un număr redus de rânduri (sub 5%);

indexaţi tabelele care sunt interogate folosind clauze SQL simple;

nu indexaţi tabelele ce conţin puţine înregistrări (accesul secvenţial este mai simplu);

nu indexaţi tabelele care sunt frecvent actualizate, deoarece ştergerile, inserările şi modificările sunt îngreunate de indecşi;

indexaţi coloanele folosite frecvent în clauza WHERE sau în clauza ORDER BY;

nu indexaţi coloanele ce conţin date asemănătoare (puţine valori distincte);

indexaţi coloanele care sunt utilizate pentru a face legătura dintre tabele.

Versiunea Oracle8 permite construirea de tabele organizate pe bază de index. În acest caz, datele sunt stocate în indexul asociat. Un astfel de tabel poate fi manipulat de către aplicaţii la fel ca un tabel obişnuit, folosind comenzi SQL. Diferenţa constă în faptul că în cazul tabelului organizat pe bază de index, toate operaţiile sunt efectuate numai asupra indexului. În loc ca fiecare intrare a indexului să conţină valoarea coloanei sau coloanelor indexate şi valoarea ROWID (care identifică unic un rând) pentru rândul corespunzător, ea conţine întreg rândul. Coloana sau coloanele după care se face indexarea sunt cele care constituie cheia primară a tabelului.

32 Oracle

Informaţii despre indecşi pot fi obţinute cu ajutorul view-urilor USER_INDEXES şi USER_IND_COLUMNS. Dintre coloanele tabelului USER_INDEXES se remarcă:

INDEX_NAME Numele indexuluiINDEX_TYPE Tipul indexului (NORMAL, LOB, CLUSTER etc.)TABLE_OWNER Proprietarul tabelului indexatTABLE_NAME Numele tabelului indexatTABEL_TYPE Tipul tabelului indexat (TABLE, CLUSTER etc.)UNIQUENESS Starea de unicitate (UNIQUE, NONUNIQUE)TABLESPACE_NAME Spaţiul tabel în care este stocat indexulINITIAL_EXTENT Spaţiul alocat pentru prima extensieNEXT_EXTENT Spaţiul alocat pentru următoarea extensieMIN_EXTENTS Numărul minim de extensii alocate MAX_EXTENTS Numărul maxim de extensiiPCT_INCREASE Procentul cu care cresc extensiileBLEVEL Nivelul din B-arbore. Acesta arată adâncimea indexului de

la ramuri la frunzeLEAF_BLOCKS Numărul de blocuri frunză din indexDISTINCT_KEYS Numărul de chei distincte în indexSTATUS Starea indexului (VALID, INVALID, DIRECT_LOAD)NUM_ROWS Numărul de linii utilizate. Acesta nu trebuie să includă

valorile NULL din tabelul de bazăPARTITIONED Determină dacă indexul este partiţionat (YES sau NO)GENERATED Determină dacă sistemul a generat numele indexului (Y)

sau utilizatorul (N)

Exemplu:Să se obţină informaţii referitoare la indecşii tabelului carte.

SELECT a.index_name, a.column_name,a.column_position poz, b.uniqueness

FROM user_indexes b, user_ind_columns aWHERE a.index_name = b.index_nameAND a.table_name = ’carte’;

Secvenţe

SQL 33

O secvenţă este un obiect în baza de date care serveşte pentru a genera întregi unici în sistemele multi-utilizator, evitând apariţia conflictelor şi a blocării.

Secvenţele sunt memorate şi generate indiferent de tabele aceeaşi secvenţă poate fi utilizată pentru mai multe tabele. O secvenţă poate fi creată de un utilizator şi poate fi partajată de mai mulţi utilizatori.

Crearea unei secvenţe se face cu ajutorul comenzii:

CREATE SEQUENCE [<nume_schema>.]<nume_secventa>  [INCREMENT BY n]  [START WITH m]  [{MAXVALUE n | NOMAXVALUE}]  [{MINVALUE n | NOMINVALUE}]  [{CACHE k | NOCACHE}]  [{ORDER | NOORDER}]

CACHE k | NOCACHE specifică numărul de valori alocate de server-ul Oracle pe care le va păstra în memoria cache pentru a oferi utilizatorilor un acces rapid (implicit sunt alocate 20 de valori);

ORDER | NOORDER specifică dacă valorile generate de secvenţă sunt ordonate în conformitate cu cererile.

O secvenţă este referită într-o comandă SQL cu ajutorul pseudo-coloanelor:

NEXTVAL – referă valoarea următoare a secvenţei;

CURRVAL – referă valoarea curentă a secvenţei.

NEXTVAL şi CURRVAL pot fi folosite în:

clauza VALUES a unei comenzi INSERT;

clauza SET a unei comenzi UPDATE;

lista unei comenzi SELECT.NEXTVAL şi CURRVAL nu pot fi folosite în:

subinterogare;

interogarea unei vizualizări;

comandă SELECT cu operatorul DISTINCT;

comandă SELECT cu clauza GROUP BY sau ORDER BY;

clauza WHERE a unei comenzi SELECT;

condiţia unei constrângeri CHECK;

valoarea DEFAULT a unei coloane într-o comandă CREATE TABLE sau ALTER TABLE;

34 Oracle

comandă SELECT care este combinată cu altă comandă SELECT printr-un operator mulţime (UNION, INTERSECT, MINUS).

Din dicţionarul datelor pot fi obţinute informaţii despre secvenţe folosind vizualizarea USER_SEQUENCES.

Exemplu:1) Să se creeze o secvenţă domeniuseq care să fie utilizată pentru a insera noi

domenii în tabelul domeniu şi să se insereze un nou domeniu.

2) Să se afişeze informaţiile referitoare la secvenţa domeniuseq.

CREATE SEQUENCE domeniuseq START WITH 1 INCREMENT BY 1;INSERT INTO domeniuVALUES (domeniuseq.NEXTVAL,’Informatica’);SELECT INCREMENT, START, MAXVALUE, MINVALUE,FROM USER_SEQUENCESWHERE SEQUENCE_NAME = 'domeniuseq';

Modificarea unei secvenţe se face prin comanda ALTER SEQUENCE. Sintaxa comenzii este similară instrucţiunii CREATE SEQUENCE , dar:

noua valoare maximă pentru MAXVALUE nu poate fi mai mică decât valoarea curentă;

opţiunea START WITH nu poate fi modificată de comandă.

Suprimarea unei secvenţe se face cu ajutorul comenzii:

DROP SEQUENCE [<nume_schema>.]<nume_secventa>;

După ce a fost ştearsă, secvenţa nu mai poate fi referită. Pentru a putea şterge sau modifica secvenţa trebuie fie să fi proprietarul acesteia, fie să ai privilegiul de sistem DROP ANY SEQUENCE, respectiv privilegiul ALTER SEQUENCE.

Comentarii

Sistemul Oracle oferă posibilitatea de a comenta obiectele create, printr-un text care este inserat în dicţionarul datelor. Comentariul se poate referi la tabele, vizualizări, clişee sau coloane.

SQL 35

COMMENT ON {TABLE nume_obiect | COLUMN nume_obiect.nume_coloana} IS ’text comentariu’

Sinonime

Oracle oferă posibilitatea de a atribui mai multe nume aceluiaşi obiect. Aceste nume adiţionale sunt numite sinonime (synonymes). Ele sunt utile deoarece permit simplificarea formulării cererii şi referirea la obiecte, fără a fi nevoie să se specifice proprietarii obiectelor sau localizarea acestora.

Spre deosebire de alias a cărui durată de viaţă este limitată la cererea ce conţine alias-ul, sinonimele sunt salvate în dicţionarul datelor şi pot fi reutilizate.

Sistemul Oracle permite crearea de sinonime pentru obiecte de tipul: tabel, vizualizare, secvenţă, funcţie, procedură, pachet, clişeu, sinonim.

CREATE [PUBLIC] SYNONYM [schema.]nume_sinonim FOR [schema.]obiect

Administratorul bazei poate produce şi poate suprima sinonime publice sau private, iar utilizatorii pot genera sau suprima doar sinonime private. Pentru suprimarea unui sinonim din baza de date se utilizează comanda:

DROP [PUBLIC] SYNONYM [schema.]nume_sinonim

36 Oracle

Vizualizări

Vizualizarea (view) este un tabel logic (virtual) relativ la date din una sau mai multe tabele sau vizualizări. Vizualizarea este definită plecând de la o cerere a limbajului de interogare a datelor, moştenind caracteristicile obiectelor la care se referă. Vizualizarea, fiind virtuală, nu solicită o alocare de memorie pentru date. Ea este definită în DD cu aceleaşi caracteristici ca şi un tabel.

Textul cererii care defineşte vizualizarea este salvat în DD. Nucleul Oracle determină fuzionarea cererii relative la vizualizare cu comanda de definire a vizualizării, analizează rezultatul fuziunii în zona partajată şi execută cererea.

Oracle transformă cererea referitoare la o vizualizare într-o cerere relativă la tabelele de bază.

Dacă sunt utilizate clauzele UNION, GROUP BY şi CONNECT BY, atunci Oracle nu determină fuzionarea, el va rezolva vizualizarea şi apoi va aplica cererea rezultatului obţinut.

O vizualizare reflectă la orice moment conţinutul exact al tabelelor de bază. Orice modificare efectuată asupra tabelelor se repercutează instantaneu asupra vizualizării. Ştergerea unui tabel implică invalidarea vizualizărilor asociate tabelului şi nu ştergerea acestora.

Vizualizările sunt definite pentru:

furnizarea unui nivel mai înalt de securizare a bazei;

simplificarea formulării unei cereri;

mascarea complexităţii datelor;

afişarea datelor într-o altă reprezentare decât cea a tabelelor de bază;

asigurarea independenţei datelor;

asigurarea confidenţialităţii anumitor informaţii;

definirea constrângerilor de integritate;

restricţionarea acesului la date.

Crearea unei vizualizări se realizează cu ajutorul comenzii:

CREATE [OR REPLACE][FORCE | NOFORCE] VIEW  [<nume_schema>.]<nume_view> [(<alias>[,<alias>]…)]    AS <cerere_SELECT>

SQL 37

    [WITH {CHECK OPTION [CONSTRAINT <nume_constrangere>] |     READ ONLY }];

– OR REPLACE recreează vizualizarea dacă aceasta deja există.

– FORCE creează vizualizarea chiar dacă tabelul de bază nu există sau chiar dacă vizualizarea face referinţă la obiecte care încă nu sunt create. Deşi vizualizarea va fi creată, utilizatorul nu poate să o folosească.

– NO FORCE este implicită şi se referă la faptul că vizualizarea este creată numai dacă tabelele de bază există.

– Cererea este o comandă SELECT care poate să conţină alias pentru coloane.

– WITH CHECK OPTION specifică faptul că reactualizarea datelor din tabele (inserare sau modificare) se poate face numai asupra datelor selectate de vizualizare (care apar în clauza WHERE).

– WITH READ ONLY asigură că nici o operaţie LMD nu poate fi executată asupra vizualizării.

Exemplu:

Să se genereze o vizualizare care conţine cărţile împrumutate şi în care să fie implementată constrîngerea că orice carte, care există într-un singur exemplar, poate fi împrumutată maximum 15 zile.

CREATE VIEW imprumutareAS SELECT *

FROM imprumutaWHERE codel NOT IN

(SELECT codel FROM carte WHERE nrex = 1)

OR datares - dataim < 15WITH CHECK OPTION;

Observaţii: Numărul coloanelor specificate în definiţia vizualizării trebuie să fie egal cu cel

din lista asociată comenzii SELECT. Cererea ce defineşte o vizualizare poate conţine cereri SELECT complexe care

includ subcereri, grupări, operaţii de compunere. Cererea care defineşte o vizualizare nu poate conţine clauza ORDER BY.

Clauza poate fi specificată dacă se fac interogări asupra vizualizării.

38 Oracle

Modificarea unei vizualizări presupune modificarea definiţiei acesteia. Pentru a înlocui o vizualizare trebuie avut privilegiul de sistem necesar pentru distrugerea şi crearea acesteia. Înlocuirea se poate face în două moduri.

Vizualizarea poate fi distrusă (DROP VIEW) şi apoi recreată (CREATE) cu noua definiţie. Atunci când este distrusă, toate privilegiile sunt retrase. Aceste privilegii trebuie să fie create pentru noua vizualizare.

Vizualizarea poate fi recreată prin redefinire cu instrucţiunea CREATE VIEW, dar cu clauza OR REPLACE. Această metodă conservă toate privilegiile curente.

Modificarea unui vizualizări are următoarele efecte:

definiţia vizualizării din DD este actualizată;

nici unul din obiectele de bază nu este afectat de înlocuire;

toate restricţiile care existau în vizualizarea originală sunt distruse;

toate vizualizările şi programele PL/SQL dependente de vizualizarea înlocuită devin invalide.

Suprimarea unei vizualizări se realizează prin comanda DROP VIEW care şterge definiţia vizualizării din baza de date.

DROP VIEW <nume_view>;

Ştergerea vizualizării nu va afecta tabelele relativ la care a fost definită vizualizarea. Aplicaţiile şi vizualizările care se bazează pe vizualizarea suprimată devin invalide. Pentru a suprima o vizualizare, utilizatorul trebuie să aibă privilegiul DROP ANY VIEW sau să fie creatorul vizualizării respective.

Recompilarea unei vizualizări permite detectarea eventualelor erori referitoare la vizualizare, înaintea executării vizualizării. După fiecare modificare a tabelelor de bază este recomandabil ca vizualizarea să se recompileze:

ALTER VIEW <nume_view> COMPILE;

Reactualizarea tabelelor implică reactualizarea corespunzătoare a vizualizărilor!!!

Reactualizarea vizualizărilor implică reactualizarea tabelelor de bază? NU! Există restricţii care trebuie respectate!!!

SQL 39

Nu pot fi înserate, şterse sau actualizate date din vizualizări ce conţin: operatorul DISTINCT; clauzele GROUP BY, HAVING, START WITH, CONNECT BY; pseudo-coloana ROWNUM; funcţii grup; operatori de mulţimi.

Nu pot fi inserate sau actualizate date care ar încălca constrângerile din tabelele de bază.

Nu pot fi inserate sau actualizate valorile coloanelor care rezultă prin calcul. Nu se pot face operaţii LMD asupra coloanelor calculate cu DECODE.

Alături de restricţiile prezentate anterior, aplicabile tuturor vizualizărilor, există restricţii specifice, aplicabile vizualizărilor bazate pe mai multe tabele.

Regula fundamentală este că orice operaţie INSERT, UPDATE sau DELETE pe o vizualizare bazată pe mai multe tabele poate modifica datele doar din unul din tabelele de bază.

Un tabel de bază al unei vizualizări este protejat prin cheie (key preserved table) dacă orice cheie selectată a tabelului este de asemenea şi cheie a vizualizării. Deci, un tabel protejat prin cheie este un tabel ale cărui chei se păstrează şi la nivel de vizualizare. Pentru ca un tabel să fie protejat prin cheie nu este necesar ca tabelul să aibă toate cheile selectate în vizualizare. Este suficient ca, atunci când cheia tabelului este selectată, aceasta să fie şi cheie a vizualizării.

Asupra unui join view pot fi aplicate instrucţiunile INSERT, UPDATE sau DELETE, doar dacă sunt îndeplinite următoarele condiţii:

instrucţiunea LMD afectează numai unul dintre tabelele de bază;

în cazul instrucţiunii UPDATE, toate coloanele care pot fi reactualizate trebuie să corespundă coloanelor dintr-un tabel protejat prin cheie (în caz contrar, Oracle nu va putea identifica unic înregistrarea care trebuie reactualizată);

în cazul instrucţiunii DELETE, rândurile unei vizualizări pot fi şterse numai dacă există un tabel în join protejat prin cheie şi numai unul (în caz contrar, Oracle nu ar şti din care tabel să şteargă);

în cazul instrucţiunii INSERT, toate coloanele în care sunt inserate valori trebuie să provină dintr-un tabel protejat prin cheie;

Exerciţiu!!!

Dacă vizualizarea este definită folosind clauza WITH CHECK OPTION, atunci pot fi şterse, inserate sau modificate rânduri din vizualizare???

40 Oracle

ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS şi USER_UPDATABLE_COLUMNS sunt vizualizări din DD ce conţin informaţii referitoare la coloanele vizualizărilor existente, care pot fi reactualizate.

Exmplu:1. Să se creeze un view ce conţine câmpurile nume, prenume, job din tabelul

salariat.2. Să se insereze, să se actualizeze şi să se şteargă o înregistrare în acest view.

Ce efect vor avea aceste acţiuni asupra tabelului de bază?

Soluţie:

CREATE VIEW vederea2AS SELECT  nume, prenume, jobFROM salariat;

Nu se pot face inserari deoarece view-ul nu conţine cheia primară!

INSERT INTO vederea2VALUES ('Popescu','Valentin','grafician');

va genera eroarea:

ORA-01400: cannot insert NULL into            ("SCOTT"."SALARIAT"."COD_SALARIAT")

Actualizarea job-ului salariatului având numele "Popescu":

UPDATE vederea2SET job = 'programator'WHERE nume = 'Popescu';SELECT nume, prenume, jobFROM salariat;

Ştergerea înregistrării referitoare la salariatul având numele "Popescu":

DELETE vederea2WHERE nume = 'Popescun';

Operaţiile care se realizează asupra view-ului se realizează şi în tabelul salariat. Pentru un caz mai general, când view-ul conţine cheia externă a tabelului de bază, sunt permise modificări ale view-ului, dacă acestea nu afectează cheia externă.

Exemplu:

SQL 41

Să se creeze un view care conţine câmpurile nume, prenume, job din tabelul salariat. Să se introducă în view doar persoanele care sunt graficieni.

CREATE VIEW vederea21AS SELECT nume, prenume, jobFROM salariatWHERE job = 'grafician'WITH CHECK OPTION;

Exemplu:Să se creeze o vizualizare care să conţină cod_salariat, nume, prenume din

tabelul salariat şi coloana tip din tabelul grafician. Apoi să se insereze, să se actualizeze şi să se şteargă o înregistrare din acest view (vizualizarea conţine cheia primară cod_salariat din tabelele salariat şi grafician).

Soluţie:

CREATE VIEW vederea4AS SELECT s.cod_salariat,nume,prenume,tipFROM salariat s, grafician gWHERE s.cod_salariat=g.cod_salariat;

În cazul inserării unei înregistrări pentru care se specifică toate câmpurile:

INSERT INTO vederea4VALUES (30,'Popescu','Valentin','artist plastic');

va apare următoarea eroare:

ORA-01776: cannot modify more than one base TABLE through a join view

Pot fi inserate date doar într-un tabel de bază (în oricare, dar în unul singur) prin intermediul view-ului, astfel:

INSERT INTO vederea4 (cod_salariat, nume)VALUES (30, 'Popescu');

Comanda pentru ştergerea unei înregistrări:

DELETE vederea4WHERE cod_salariat = 3;

va genera următoarea eroare:

ORA-01752: cannot delete from view without exactly one key-preserved TABLE.

42 Oracle

Modificarea unei înregistrări se face prin secvenţa care urmează. Toate actualizările care se fac în view se fac şi în tabelele de bază.

UPDATE vederea4SET tip = 'designer'WHERE cod_salariat = 3;

Exemplu:Care dintre coloanele unei vizualizări sunt actualizabile?

SELECT column_name, updatable FROM user_updatable_columnsWHERE table_name = 'vederea4';

Exemplu:1. Să se creeze un view (vederea3) care să conţină, pentru fiecare categorie de

salariat, salariile medii şi numărul de angajaţi din tabelul salariat.2. Să se insereze, să se actualizeze şi să se şteargă o înregistrare în view.

Soluţie:

CREATE VIEW vederea3 (nr, job, salmed)AS SELECT COUNT(*), job, AVG(salariu)FROM salariatGROUP BY job;

Nu se pot face inserări, actualizări sau ştergeri într-un view ce conţine funcţii grup. După oricare din aceste operaţii apare acelaşi mesaj:

ORA-01732: data manipulation operation not legal on this view

Exemplu:Să se creeze o vizualizare care să conţină coloanele cod_contractant, adresa,

telefon din tabelul contractant şi coloanele nr_contract, tip_contract, data_incheiere din tabelul contract. Să se insereze o înregistrare în vizualizare.

CREATE VIEW vederea44

SQL 43

AS SELECT c.cod_contractant, adresa, telefon,co.nr_contract, tip_contract,data_incheiere

FROM contractant c, contract coWHERE c.cod_contractant=co.cod_contractant;

La inserarea unei înregistrări căreia i se specifică valorile tuturor câmpurilor din ambele tabele:

INSERT INTO vederea44(cod_contractant, adresa,          nr_contract, data_incheiere)

VALUES (200, 'Str. Marmurei, 14', '6235',TO_DATE('January 03,2002','Month dd,yyyy'));

se obţine eroarea:

ORA-01779: cannot modify a column which maps to a non key-preserved TABLE

Cele două tabele de bază, contractant şi contract, se află într-o relaţie “one-to-many”, iar view-ul creat conţine cheile primare din ambele tabele. Datorită acestei situaţii, doar tabelul contract este protejat prin cheie şi, prin urmare, doar el poate fi modificat prin intermediul view-ului. Aceasta, deoarece ar putea exista mai multe înregistrări în view, cu aceeaşi valoare corespunzătoare câmpului cod_contractant (CP în contractant). Exact aceeaşi eroare se obţine dacă încercăm inserarea unei înregistrări în vederea44, specificând fie şi numai un câmp provenind din tabela contractant (indiferent dacă el conţine sau nu CP).

Singura operaţie de inserare permisă este aceea efectuată prin specificarea cheilor provenind doar din tabelul contract. Astfel, prin executarea comenzii:

INSERT INTO  vederea44(nr_contract, tip_contract)VALUES       ('6234', 0);

este creată o înregistrare, dar este modificat şi tabelul contract. Dacă la inserţie nu se specifică cheia primară din contract:

INSERT INTO  vederea44(tip_contract)VALUES       (1);

ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

Grupări

44 Oracle

Cluster-ul este o regrupare fizică a două sau mai multe tabele, relativ la una sau mai multe coloane, cu scopul măririi performanţelor. Coloanele comune definesc cheia cluster-ului.

Un cluster este un obiect al bazei care necesită:

un nume unic la nivelul schemei,

specificare a coloanelor care compun cheia cluster-ului,

specificare a spaţiului de stocare (opţional),

un index (relativ la cheia cluster-ului).

Un cluster trebuie să aibă cel puţin un index. Acest index trebuie creat înaintea oricărei comenzi LMD care va acţiona asupra tabelelor cluster-ului. Un index al cluster-ului se deosebeşte de un index al tabelului (de exemplu, absenţa indexului afectează utilizatorul – datele cluster-ului nu sunt accesibile).

Coloanele comune definite pentru cluster, reprezintă cheia cluster-ului şi criteriul de regrupare. Liniile diferitelor tabele sunt regrupate în interiorul aceluiaşi bloc urmărind cheia cluster-ului. Dacă liniile asociate unei aceiaşi valori a cheii cluster-ului necesită un spaţiu de mai multe blocuri, atunci blocurile sunt înlănţuite.

Crearea unui cluster presupune:

crearea structurii cluster-ului;

crearea indexului cluster-ului;

crearea tabelelor care vor compune cluster-ul.

Crearea unui cluster:

CREATE CLUSTER nume_cluster (nume_coloana tip_data [,nume_coloana tip_data] …) [SIZE n]

Există două modalităţi pentru introducerea unui tabel într-un cluster.

O primă variantă presupune că cluster-ul este creat pentru un tabel care deja există. De fapt, nu se poate asocia un cluster unui tabel care există!

A doua variantă presupune că introducerea tabelului în cluster se face în momentul creării structurii tabelului (comanda CREATE TABLE).

Exerciţiu:Să se obţină un cluster referitor la lista cărţilor din fiecare domeniu.

Varianta 1

CREATE CLUSTER cdoml(cdom CHAR(1));CREATE INDEX indcom ON CLUSTER cdoml;

SQL 45

CREATE TABEL dominoCLUSTER cdoml(coded)AS SELECT * FROM domeniu;

DROP TABEL domeniu;RENAME domino TO domeniu;ALTER TABLE carteMODIFY coded NOT NULL;CREATE TABEL carticica

CLUSTER cdoml(coded)AS SELECT * FROM carte;

DROP TABLE carte;RENAME carticica TO carte;

Varianta 2

CREATE CLUSTER cdoml(cdom CHAR(1));CREATE INDEX indcom ON CLUSTER cdoml;-- crearea spatiuluiCREATE TABLE domeniu

(coded CHAR(1) NOT NULL,intdom CHAR() ... )CLUSTER cdoml(coded);

CREATE TABLE carte(codel CHAR(5) NOT NULL, …

coded CHAR(1) NOT NULL)CLUSTER cdoml(coded);

Pentru a scoate un tabel dintr-un cluster sunt parcurse următoarele etape: se creează un nou tabel, în afara cluster-ului, prin duplicarea celui vechi; se distruge tabelul din cluster; se suprimă cluster-ul.

CREATE TABLE alfaAS SELECT * FROM domeniu;

DROP TABLE domeniu;RENAME alfa TO domeniu;CREATE TABLE beta

AS SELECT * FROM carte;DROP TABLE carte;RENAME beta TO carte;DROP CLUSTER cdoml;

46 Oracle

Un alt tip de cluster oferit de Oracle este cluster-ul hash. În acest caz, pentru a accesa o înregistrare, cluster-ul hash nu foloseşte un index, ci o funcţie numerică, numită funcţia hash. Funcţia are ca parametru cheia cluster-ului şi returnează o anumită valoare (valoare hash). Această valoare corespunde blocului de date din cluster pe care Oracle îl va citi sau scrie pe baza comenzii executate.

De exemplu, apelurile telefonice efectuate de un client într-o lună vor fi facturate împreună. Apelurile pot fi depozitate într-un cluster hash a cărui cheie este formată din coloanele ce conţin numărul telefonului, anul şi luna în care a avut loc convorbirea.

Suprimarea unui cluster din baza de date se face prin comanda:DROP CLUSTER nume_cluster

În urma ştergerii unui cluster, tabelele pe care acesta le conţine nu mai sunt grupate. Secvenţa următoare suprimă: cluster-ul, toate tabelele definite relativ la acest cluster şi constrângerile lor de integritate.

DROP CLUSTER nume_clusterINCLUDING TABLESCASCADE CONSTRAINTS;

Modificarea unui cluster permite redefinirea condiţiilor, modificarea parametriilor de stocare şi a caracteristicelor de stare (ALTER CLUSTER).

Informaţii despre obiectele bazei de date

Pot fi obţinute consultând DD. Dintre ele se remarcă: definiţiile tuturor obiectelor din baza de date; spaţiul alocat şi spaţiul utilizat în prezent de obiectele schemei;

SQL 47

constrângerile de integritate; numele utilizatorilor bazei; privilegiile şi rolurile acordate fiecărui rol; alte informaţii generale despre baza de date.

Tabelul USER_CATALOG conţine informaţii despre tabelele şi vizualizările definite de un utilizator particular. Acest tabel poate fi referit şi prin sinonimul său public CAT.

Tabelul USER_OBJECTS conţine informaţii despre toate obiectele definite de utilizatorul curent. Tabelul are următoarea schemă relaţională:

USER_OBJECTS (object_name, object_id, object_type, created, last_ddl_time, timestamp, status)

Vizualizările cele mai importante ale dicţionarului datelor conţin:

descrierea tabelelor disponibile utilizatorului (USER_ALL_TABLES),

informaţii despre constrângerile definite de utilizator(USER_CONSTRAINTS),

informaţii despre legăturile bazei de date (USER_DB_LINKS),

erorile curente ale obiectelor depozitate (USER_ERRORS),

informaţii despre indecşii creaţi de utilizator (USER_INDEXES),

informaţii despre tabelele utilizatorului (USER_TABLES) etc.

Vizualizările din dicţionarul datelor referitoare la tabele conţin:

USER_TAB_COLUMNS|COLS – informaţii despre coloanele tabelelor,

USER_CONS_COLUMNS – informaţii despre constrângeri la nivel coloană,

USER_TAB_COMMENTS – informaţii despre comentarii la nivel tabel,

USER_COL_COMMENTS – informaţii despre comentarii la nivel coloană,

USER_TAB_PARTITIONS – informaţii despre partiţiile tabelelor.

Limbajul de manipulare a datelor

48 Oracle

SQL furnizează comenzi ce permit consultarea (SELECT) şi actualizarea (INSERT, UPDATE, DELETE) conţinutului bazei de date. Aceste comenzi definesc limbajul de manipulare a datelor (LMD).

Comenzile limbajului LMD pot fi:

formulate direct, utilizând interfaţa SQL*PLUS ;

utilizate în utilitare ale sistemului ORACLE;

încapsulate într-un program PL/SQL ;

încapsulate într-un program scris în limbaj gazdă.

În funcţie de momentul în care se doreşte realizarea actualizărilor asupra bazei de date, utilizatorul poate folosi una din următoarele comenzi:

SET AUTOCOMMIT ON – schimbările se efectuează imediat;

SET AUTOCOMMIT OFF – schimbările sunt păstrate într-un buffer până la execuţia uneia din comenzile:

 – COMMIT, care are rolul de a permanentiza schimbările efectuate; – ROLLBACK, care determină renunţarea la schimbările realizate.

Comanda INSERT

INSERT INTO nume_tabel / nume_view [(col1[, col2[,…]])]VALUES (expresia1[, expresia2[,…]]) / subcerere;

SQL 49

expresia1, expresia2, reprezintă expresii a căror evaluare este atribuită coloanelor precizate (se inserează o linie);

subcerere, reprezintă o interogare (se inserează una sau mai multe linii).

Observaţii:

Dacă lipseşte specificaţia coloanelor se consideră că sunt completate toate câmpurile tabelului sau vizualizării.

Dacă nu a fost specificată lista coloanelor şi dacă există câmpuri care nu au valori efective, atunci valoarea null va fi atribuită acestor câmpuri.

Dacă se introduc date doar în anumite coloane, atunci aceste coloane trebuie specificate. În restul coloanelor se introduce automat null.

Specificarea cererii din comanda INSERT determină copierea unor date dintr-un tabel în altul pe atâtea linii câte au rezultat din cerere.

Dacă se introduc numai anumite câmpuri într-o înregistrare, atunci printre acestea trebuie să se găsească câmpurile cheii primare.

Pentru a putea executa comanda INSERT este necesar ca utilizatorul care execută această instrucţiune să aibă privilegiul de a insera înregistrări în tabel sau în vizualizare.

Exemplu:Să se insereze în tabelul carte toate cărţile din tabelul carte_info,

presupunând că tabelul carte_info a fost deja creat. De asemenea, să se introducă o nouă carte căreia i se cunoaşte codul (c34), titlul (algebra) şi preţul (500).

Soluţie:

INSERT INTO carteSELECT *FROM carte_info;INSERT INTO carte(codel,titlu,autor,nrex,pret,coded)VALUES (’c34’,’algebra’,null,null,500,null);

Exemplu: INSERT INTO carte(codel, nrex)VALUES ('c25', 25);INSERT INTO domeniuVALUES ('&cod','&intdom');inserare prin parametrizare

Exemplu:

50 Oracle

Presupunând că tabelul salariat a fost completat cu datele tuturor salariaţilor editurii, să se completeze tabelele grafician, tehnoredactor şi redactor_sef, în concordanţă cu datele conţinute în tabelul salariat (nu pot exista graficieni, tehnoredactori sau redactori şefi care să nu fie salariaţi!).

Soluţie:

INSERT INTO grafician (cod_salariat)SELECT cod_salariatFROM salariatWHERE job = ’grafician’; INSERT INTO tehnoredactor (cod_salariat)SELECT cod_salariatFROM salariatWHERE job = ’tehnoredactor’; INSERT INTO redactor_sef (cod_salariat)SELECT cod_salariatFROM salariatWHERE job = ’redactor_sef’;

Exemplu:Se doreşte ca toţi graficienii având salariile mai mari decât media salariilor

să colaboreze la realizarea tuturor frame-urilor din publicaţii coordonate de redactori şefi având vechimea maximă. Să se completeze tabelul realizeaza cu înregistrările corespunzătoare.

Soluţie:INSERT INTO realizeaza (cod_salariat, nr_publicatie,

nr_capitol, nr_frame)SELECT s.cod_salariat,f.nr_publicatie, f.nr_capitol,

f.nr_frameFROM salariat s, frame fWHERE s.salariu > (SELECT AVG(s1.salariu)

FROM salariat s1)AND job = 'grafician'AND f.nr_publicatie IN

(SELECT p.nr_publicatie FROM salariat s2, publicatie p WHERE s2.cod_salariat = p.cod_salariat

SQL 51

AND s2.vechime = (SELECT MAX(s3.vechime)  FROM salariat s3));

Comanda DELETE

DELETE [FROM] tablename / viewname [WHERE condiţie]

Observaţii:

Pentru a se putea executa instrucţiunea DELETE, utilizatorul care o lansează în execuţie trebuie să aibă acest privilegiu.

Comanda DELETE nu şterge structura tabelului.

În clauza WHERE pot fi folosite şi subcereri.

Comanda nu poate fi folosită pentru ştergerea valorilor unui câmp individual. Acest lucru se poate realiza cu ajutorul comenzii UPDATE.

Atenţie la ştergere, pentru a nu afecta integritatea referenţială!

Exemplu:Să se elimine cititorii care au numele ‘Popa’şi cei

care au restituit astăzi cel puţin o carte.

DELETE FROM cititor WHERE nume=’Popa’ OR codec IN (SELECT codec FROM imprumuta WHERE data_ef=SYSDATE);

Exemplu:Să se şteargă toţi tehnoredactorii care colaborează la mai puţin de trei

publicaţii.

DELETE FROM salariatWHERE job = ’tehnoredactor’AND COUNT

(SELECT DISTINCT c.nr_publicatie FROM capitol c WHERE c.cod_salariat = cod_salariat)< 3;

52 Oracle

Comanda UPDATE

Valorile câmpurilor care trebuie modificate pot fi furnizate explicit sau pot fi obţinute în urma unei cereri SQL.

UPDATE tablename / viewnameSET (column1[,column2[,…]]) = (subquery) / column = expr / (query)[WHERE condition]

Observaţii:

Pentru a se putea executa instrucţiunea UPDATE, utilizatorul care o lansează în execuţie trebuie să aibă acest privilegiu.

Dacă nu este specificată clauza WHERE se vor modifica toate liniile.

Cererea trebuie să furnizeze un număr de valori corespunzător numărului de coloane din paranteza care precede caracterul de egalitate.

Exemplu:Preţul cărţilor scrise de Lucian Blaga să fie modificat, astfel încât să fie egal

cu preţul celei mai scumpe cărţi de informatică din bibliotecă.

Soluţie:

UPDATE carteSET pret = (SELECT MAX(pret)

FROM carte WHERE coded = ’I’)

WHERE autor = ’Lucian Blaga’;

Exemplu:Să se modifice preţul cărţilor din bibliotecă, care se găsesc într-un număr de

exemplare mai mic decât media numărului de exemplare pe bibliotecă. Noua valoare a preţului să fie egală cu suma preţurilor cărţilor scrise de Zola.

Soluţie:

UPDATE carteSET pret = (SELECT SUM(pret)

FROM carte WHERE autor = ’Zola’)

WHERE nrex < (SELECT AVG(nrex) FROM carte);

SQL 53

Exemplu:Să se mărească cu 5% salariile redactorilor şefi care coordonează publicaţia

care are cel mai mare număr de frame-uri.

Soluţie:

UPDATE salariatSET salariu = 1,05*salariuWHERE cod_salariat IN (SELECT cod_salariat FROM publicatie WHERE nr_publicatie IN (SELECT nr_publicatie FROM frame GROUP BY nr_publicatie HAVING COUNT(*) > ANY (SELECT COUNT(*) FROM frame GROUP BY nr_publicatie)));

Exemplu:Să se reducă cu 10% salariile redactorilor şefi care nu sunt asociaţi nici unei

publicaţii.

Soluţie:

UPDATE salariatSET salariu = 0,9*salariuWHERE cod_salariat IN (SELECT cod_salariat FROM redactor_sef WHERE cod_salariat NOT IN (SELECT cod_salariat FROM publicatie));

Comanda SELECT

SELECT [ALL | DISTINCT]{* | listă de atribute selectate | expr AS alias}

FROM { [schema.]{tabel [PARTITION (partition_name)] |

54 Oracle

[THE] (subquery)} [alias_tabel] }[WHERE condiţie][START WITH condiţie][CONNECT BY condiţie][GROUP BY listă de expresii[HAVING condiţie]][ORDER BY {expresie | poziţie | c_alias} [ASC | DESC]][FOR UPDATE [OF [schema.]{table | view}.coloană] [NOWAIT]

Clauzele START WITH şi CONNECT BY sunt utile pentru a construi cereri ierarhizate. Pentru a specifica înregistrarea rădăcină a arborelui se va folosi clauza START WITH. Dacă această clauză este omisă fiecare înregistrare din tabel poate fi considerată ca înregistrare de start. Cu ajutorul clauzei CONNECT BY se pot specifica coloanele (părinte şi copil) care participă la relaţie. Prin ordinea apariţiilor acestor coloane (în condiţie) se poate determina ordinea de parcurgere a structurii arborescente (top-down sau bottom-up). Prin folosirea operatorului PRIOR se poate face referinţă la înregistrarea părinte.

Clauza FOR UPDATE permite blocarea coloanei (coloanelor) înainte de a actualiza sau şterge înregistrări din tabelele bazei de date. Prin folosirea clauzei NOWAIT se va genera o excepţie şi nu se va mai aştepta până la ridicarea blocajelor de pe înregistrări.

Operatorii utilizaţi (în ordinea priorităţii de execuţie) sunt:

operatori aritmetici (unari sau binari),

operatorul de concatenare ( || ),

operatorii de comparare (=, !=, ^=, < >, >, >=, <, <=, IN (echivalent cu =ANY, adică egal cu cel puţin una din valorile listei), NOT IN (echivalent cu !=ALL, adică diferit de toate elementele listei), ALL, [NOT] BETWEEN x AND y, [NOT] EXISTS, [NOT] LIKE, IS [NOT] NULL,

operatori logici (NOT, AND, OR).

Limbajul permite prezenţa unor instrucţiuni SELECT imbricate în oricare din clauzele WHERE, HAVING sau FROM (instrucţiunile SELECT care apar în clauzele respective se numesc subcereri).

În cazul folosirii subcererilor, pot fi utilizaţi operatorii ALL, ANY, IN (=ANY), EXIST, NOT IN (!=ANY), care sunt specifici cererilor ce returnează mai multe linii (multiple-row subquery) sau operatorii de comparaţie =, <, >, >=, <=, <>, specifici cererilor care returnează o singură linie (single-row subquery).

SQL 55

Executarea subcererilor se poate face:

fie cu sincronizare (corelat evaluarea subcererii face referinţă la o coloană a cererii principale şi cererea interioară se execută pentru fiecare linie a cererii principale care o conţine);

fie fără sincronizare (încuibărit se execută mai întâi cererea interioară, iar rezultatul ei este transmis cererii de nivel imediat superior).

Exemplu:În ce interogări este necesară utilizarea cuvântului cheie HAVING?

A. când este necesar să eliminăm linii duble din rezultat;

B. când este necesar să ordonăm mulţimea rezultat;

C. când este necesar să efectuăm un calcul pe grup;

D. când este necesar să restricţionăm grupurile de linii returnate.

Cereri mono – relaţie

Exemplu:

SELECT dateres–dataim ”numar zile”FROM imprumuta;

Exemplu:

SELECT codelFROM imprumutaWHERE datares >= ’01–JAN–99’;

Exemplu:Să se obţină titlurile şi numărul de exemplare ale cărţilor scrise de autorii al

căror nume începe cu litera S.

SELECT titlu, nrexFROM carteWHERE autor LIKE ’S%’;

Exemplu:Să se afişeze data şi ora curentă.

SELECT TO_CHAR(SYSDATE,’DD/MM/YY HH24:MI:SS’)FROM DUAL;

Exemplu:Utilizând ideea că directorul este salariatul care nu are şef, să se tipărească

numele directorului.

56 Oracle

SELECT ename,NVL(TO_CHAR(mgr),’Nu are sef’)FROM empWHERE mgr IS NULL;

Exemplu:Să se afişeze codurile cititorilor care nu au împrumutat cărţi într-un interval

precizat.

SELECT DISTINCT codecFROM imprumutaWHERE dataim NOT BETWEEN ’&d1’ AND ’&d2’;

Clauza GROUP BY

Exemplele care urmează arată modul general de constituire a subansamblelor virtuale folosind clauza GROUP BY. Fiecare expresie care apare în SELECT trebuie să aibă aceeaşi valoare pentru toate liniile care aparţin aceleiaşi partiţii. Numele coloanelor din GROUP BY nu trebuie să figureze obligatoriu în lista de la SELECT.

Exemplu:Să se obţină numărul de câte ori a fost împrumutată fiecare carte.

SELECT codel, COUNT(*)FROM imprumutaGROUP BY codel;

Exemplu:Pentru fiecare domeniu de carte să se obţină numărul cărţilor din domeniu,

media preţurilor şi numărul total de exemplare.

SELECT coded,COUNT(*),AVG(pret),SUM(nrex)FROM carteGROUP BY coded;

Dacă în comanda SELECT apar atribute coloană (nu funcţii grup) şi se utilizează clauza GROUP BY atunci aceste coloane trebuie obligatoriu să apară în clauza GROUP BY.

Exemplu:Să se obţină pentru fiecare autor, media preţurilor cărţilor din bibliotecă.

SELECT autor, AVG(pret)FROM carteGROUP BY autor;

SQL 57

Exemplu:Pentru departamentele în care salariul maxim depăşeşte 5000$ să se obţină

codul acestor departamente şi salariul maxim pe departament.

SELECT deptno, MAX(sal)FROM empGROUP BY deptnoHAVING MAX(sal)>5000;

Exemplu:Să se afişeze numele şi salariul celor mai prost plătiţi angajaţi din fiecare

departament.

SELECT ename, salFROM empWHERE (deptno, sal) IN

(SELECT deptno, MIN(sal) FROM emp GROUP BY deptno);

Exemplu:Să se obţină pentru fiecare carte, codul său şi numărul de exemplare care nu

au fost încă restituite.

SELECT codel, COUNT(*)FROM imprumutaWHERE dataef IS NULLGROUP BY codel;

Exemplu:Să se obţină numărul cărţilor împrumutate cel puţin o dată.

SELECT COUNT(DISTINCT codel)FROM imprumuta;

Exemplu:Să se afişeze numărul cărţilor împrumutate cel puţin de două ori (pentru

fiecare carte împrumutată mai mult decât o dată să se obţină numărul de câte ori a fost împrumutată).

SELECT COUNT(COUNT(codel))FROM imprumutaGROUP BY codel

58 Oracle

HAVING COUNT(*)>1;

În cererea anterioară COUNT(codel), reprezintă numărul care arată de câte ori a fost împrumutată fiecare carte, iar COUNT(COUNT(codel)), reprezintă numărul total al cărţilor împrumutate.

Exemplu:Lista codurilor cititorilor care au mai mult de 3 cărţi nerestituite la termen.

SELECT codecFROM imprumutaWHERE dataef IS NULL AND datares < SYSDATEGROUP BY codecHAVING COUNT(*) > 2;

Exemplu:Pentru fiecare domeniu de carte care conţine cel puţin o carte şi unde preţul

oricărei cărţi nu depăşeşte o valoare dată, să se obţină: codul domeniului, numărul cărţilor din domeniu şi numărul mediu de exemplare.

SELECT coded, COUNT(*), AVG(nrex)FROM carteGROUP BY codedHAVING COUNT(*) > 1AND MAX(pret) < &pret_dat;

Relaţii ierarhice

SQL permite afişarea rândurilor unui tabel ţinând cont de relaţiile ierarhice care apar între rândurile tabelului. Parcurgerea în mod ierarhic a informaţiilor se poate face doar la nivelul unui singur tabel. Operaţia se realizează cu ajutorul clauzelor START WITH şi CONNECT BY.

În comanda SELECT pot să apară clauzele:

CONNECT BY {expresie = PRIOR expresie | PRIOR expresie = expresie}[START WITH conditie]

Clauza CONNECT BY specifică coloanele prin care se realizează relaţia ierarhică. Operatorul PRIOR stabileşte direcţia în care este parcurs arborele. Dacă apare în stânga, atunci arborele este parcurs de sus în jos, iar dacă apare în dreapta arborele este parcurs de jos în sus. Clauza START WITH specifică nodul (înregistrarea de început) arborelui. Dacă lipseşte, orice nod poate fi rădăcină.

SQL 59

Clauza SELECT poate conţine pseudo-coloana LEVEL, care indică nivelul înregistrării în arbore (cât de departe este de nodul rădăcină). Nodul rădăcină are nivelul 1, fii acestuia au nivelul 2 ş.a.m.d.

Exemplu:Ierarhia poate fi reprezentată cu ajutorul unui tabel.

Se presupune că fiecare salariat are un singur superior (este o ierarhie). Să se afişeze superiori ierarhic lui Ion.

SELECT LEVEL, numeFROM salariatCONNECT BY nume = PRIOR nume_sefSTART WITH nume = (SELECT nume_sef FROM salariat WHERE nume = ’Ion’);

Cereri multi – relaţie

Comanda SELECT oferă posibilitatea de a consulta informaţii care provin din mai multe tabele. Operatorii care intervin în astfel de cereri pot fi: operatori pe mulţimi (UNION, UNION ALL, INTERSECT, MINUS) sau operatori compunere care implementează diferite tipuri de join.

Există două moduri de realizare a cererilor multi-relaţie:

forma procedurală, în care trebuie indicat drumul de acces la informaţie prin imbricarea de comenzi SELECT;

forma relaţională, în care drumul de acces la informaţie este în sarcina sistemului.

Exemplu:Să se obţină, utilizând aceste două forme, codurile şi titlurile cărţilor

împrumutate.

a) Forma procedurală (imbricare de comenzi SELECT):SELECT codel, titluFROM carteWHERE codel IN (SELECT codel

FROM imprumuta);b) Forma relaţională:

SELECT carte.codel, titluFROM carte, imprumuta

60 Oracle

WHERE carte.codel = imprumuta.codel;

Operatori pe mulţimi (UNION, UNION ALL, INTERSECT, MINUS)

Comenzile SELECT, care intervin în cereri ce conţin operatori pe mulţimi, trebuie să satisfacă anumite condiţii:

toate comenzile SELECT trebuie să aibă acelaşi număr de coloane;

opţiunea DISTINCT este implicită (excepţie UNION ALL);

numele coloanelor sunt cele din prima comandă SELECT;

dimensiunea coloanei implicit este cea mai mare dintre cele două coloane;

sunt admise combinaţii de forma:

1. SELECT1 UNION SELECT2 INTERSECT SELECT3 şi ordinea de execuţie este de la stânga la dreapta;

2. SELECT1 UNION (SELECT2 INTERSECT SELECT3) şi ordinea este dată de paranteze.

Exemplu:Să se obţină, utilizând operatorul INTERSECT, codurile cărţilor din care sunt

mai puţin de 15 exemplare şi care au fost împrumutate de cel puţin trei ori.

SELECT codelFROM carteWHERE nrex < 15INTERSECTSELECT codelFROM imprumutaGROUP BY codelHAVING COUNT(*) > 3;

Exemplu:Să se afişeze codurile cititorilor care nu au împrumutat cărţi.

SELECT codecFROM cititorMINUSSELECT DISTINCT codecFROM imprumuta;

Operaţii de compunere

SQL 61

Un join simplu (natural join) este o instrucţiune SELECT care returnează linii din două sau mai multe tabele. Este preferabil ca tabelul care are linii mai puţine să fie al doilea în operaţia de compunere. Comanda durează mai puţin, dacă tabela este indexată după coloana, relativ la care se face compunerea. Compunerea a n tabele cere minim (n-1) condiţii de join.

Exemplu:Să se obţină codurile şi titlurile cărţilor împrumutate.

SELECT carte.codel, titluFROM carte, imprumutaWHERE carte.codel = imprumuta.codel;

S-ar putea ca tabelele legate prin operaţia de compunere să nu aibă coloane comune (non-equijoin). În acest caz în clauza WHERE nu apare operatorul egalitate şi sunt folosiţi operatorii: <=, >=, BETWEEN.

Pentru a simplifica scrierea şi pentru a elimina ambiguităţile care pot să apară este necesară folosirea alias-ului pentru tabele. Alias-ul este valid doar pentru instrucţiunea SELECT curentă.

Exemplu:Să se obţină pentru fiecare salariat numele, salariul şi grila de salarizare (Θ join).

SELECT e.ename, e.sal, s.gradeFROM emp e, salgrade sWHERE e.sal BETWEEN s.lasal AND s.hisal;

Exemplu:Să se obţină titlurile şi preţurile cărţilor mai scumpe decât cartea având titlul

“Baze de date”, al cărui autor este Oszu (self join).

SELECT x.titlu, x.pretFROM carte x, carte yWHERE x.pret > y.pretAND y.titlu = ’Baze de date’AND y.autor = ’Oszu’;

O altă variantă de rezolvare a problemei, ca o cerere cu sincronizare:

SELECT titlu, pretFROM carte xWHERE EXISTS

(SELECT * FROM carte

62 Oracle

WHERE carte.titlu=’Baze de date’ AND carte.autor=’Oszu’ AND x.pret > pret);

Exemplu: Să se obţină informaţii despre cititorii al căror cod este mai mare decât codul unui cititor având un nume dat.

a) Forma procedurală:SELECT *FROM cititorWHERE codec > (SELECT codec

FROM cititor WHERE nume=’&nume1’);

b) Forma relaţională:SELECT c2.*FROM cititor c1, cititor c2WHERE c1.nume = ’&nume1’AND c2.codec > c1.codec;

Dacă o linie nu satisface condiţia de join, atunci linia respectivă nu va apare în rezultatul cererii. Pentru a evita această pierdere, în algebra relaţională a fost introdus operatorul outer-join.

Un outer-join (join extern) este reprezentat prin operatorul (+) care este plasat în clauza WHERE după numele tabelului ale cărui linii trebuie să nu se piardă din rezultatul cererii. Semnul (+) poate fi plasat în oricare parte a condiţiei din clauza WHERE, însă nu în ambele părţi. Efectul operatorului (+) este că se generează valori null pentru coloanele tabelului lângă care apare scris, ori de câte ori tabelul nu are nici o linie care să poată fi reunită cu o linie din celălalt tabel.

Exemplu:Să se obţină titlurile cărţilor şi numele domeniului căruia îi aparţin,

remarcând situaţiile în care domeniul nu ar avea cărţi (dacă domeniul este fără cărţi atunci apare null la titlul cărţii).

SELECT titlu, intdomFROM carte, domeniuWHERE carte.coded(+) = domeniu.coded;

Exemplu:Considerăm că tabelele dept şi emp au următorul conţinut:

SQL 63

dept emp

deptno dname empno deptno 1 algebra 101 null2 analiza 102 null

103 null105 1106 1

Interogarea următoare furnizează lista tuturor salariaţilor, inclusiv a celor care nu sunt asignaţi nici unui departament (right outher join).

SELECT a.deptno, a.dname, b.empno, b.deptnoFROM dept a, emp bWHERE a.deptno(+) = b.deptno;

Rezultatul cererii anterioare va fi:

a.deptno a.dname b.empno b.deptno101102103

1 algebra 105 11 algebra 106 1

Interogarea următoare afişează lista departamentelor, inclusiv a celor care nu au salariaţi (left outer join).

SELECT a deptno, a.dname, b.empno, b.deptnoFROM dept a, emp bWHERE a.deptno = b.deptno(+);

Rezultatul cererii anterioare va fi:

a.deptno a.dname b.empno b.deptno1 algebra 105 11 algebra 106 12 analiza null null

Interogarea următoare produce ca rezultat departamentele, chiar şi cele fără funcţionari, şi funcţionarii, chiar şi cei care nu sunt asignaţi nici unui departament (full outer join).

SELECT NVL(TO_CHAR(b.empno),’***’) id,

64 Oracle

NVL(a.dname,’***’) nume_depFROM dept a, emp bWHERE a.deptno = b.deptno(+)UNIONSELECT NVL(TO_CHAR(b.empno),’***’) id,

NVL(a.dname,’***’) nume_depFROM dept a, emp bWHERE a.deptno(+) = b.deptno;

Rezultatul cererii va fi:

id nume_dep*** analiza101 ***102 ***103 ***105 algebra106 algebra

Subcereri

De cele mai multe ori, pentru a implementa anumite interogări, nu este suficientă o singură cerere SELECT ci sunt necesare subcereri. Subcererile sunt comenzi SELECT încapsulate în oricare din clauzele WHERE, HAVING, FROM.

Dacă subcererea urmează clauzei WHERE sau HAVING, ea poate conţine unul dintre operatorii ALL, ANY, IN (=ANY), EXIST, NOT IN (!=ALL) care sunt specifici cererilor care întorc mai multe linii (multiple-row subquery) sau unul dintre operatorii de comparare (=, <, >, >=, <=, <>) care sunt specifici cererilor care întorc o singură linie (single-row subquery).

Subcererile trebuie incluse între paranteze şi trebuie plasate în partea dreaptă a operatorului de comparare. Subcererea nu poate conţine ORDER BY.

Exemplu:Să se obţină numele şi salariul angajaţilor, având salariul minim.

SELECT ename, salFROM empWHERE sal=(SELECT MIN(sal)

FROM emp);Exemplu:

Să se obţină job-ul şi salariul minim.

SQL 65

SELECT job, AVG(sal)FROM empGROUP BY jobHAVING AVG(sal)=(SELECT MIN(AVG(sal))

FROM emp GROUP BY job);

Exemplu:Să se găsească salariaţii din fiecare departament, care au salariul minim în

departamentul respectiv.

SELECT ename, sal, deptnoFROM empWHERE sal IN (SELECT MIN(sal)

FROM emp GROUP BY deptno);

Operatorul ANY presupune că este adevărată condiţia dacă comparaţia este adevărată pentru cel puţin una din valorile returnate. Sunt evidente relaţiile:

< ANY mai mic ca maximul;> ANY mai mare ca minimul;= ANY IN.Pentru operatorul ALL se presupune că este adevărată condiţia, dacă

comparaţia este adevărată pentru toate elementele listei returnate. Pentru operatorul ALL sunt evidente relaţiile:

< ALL mai mic ca minimul;> ALL mai mare ca maximul;! = ALL NOT IN.

Exemplu:

WHERE codec > ALL (‘C1’, ‘C2’) este superior tuturor elementelor din listă;

WHERE codec > ANY (‘C1’, ‘C2’) este superior cel puţin unui element din listă.

Exemplu:Să se obţină salariaţii al căror salariu este mai mare ca salariile medii din

toate departamentele.

SELECT ename, jobFROM empWHERE sal > ALL(SELECT AVG(sal)

66 Oracle

FROM emp GROUP BY deptno);

Există subcereri care au ca rezultat mai multe coloane (multiple-column subquery). Aceste interogări au următoarea sintaxă generală:

SELECT col,col,…FROM tabelWHERE (col,col,…) IN (SELECT col,col,…

FROM tabel WHERE condiţie);

Exemplu:Să se obţină numele, numărul departamentului, salariul şi comisionul tuturor

funcţionarilor ale căror salarii şi comisioane coincid cu salariile şi comisioanele unor salariaţi din departamentul 7.

SELECT ename, deptno, sal, comFROM empWHERE (sal,NVL(com,-1)) IN

(SELECT sal,NVL(com,-1) FROM emp WHERE deptno = 7);

Rezultatul acestei interogări este diferit de rezultatul următoarei interogări:

SELECT ename, deptno, sal, comFROM empWHERE sal IN (SELECT sal

FROM emp WHERE deptno=7)

AND NVL(com,-1) IN (SELECT NVL(com,-1) FROM emp WHERE deptno=7);

Dacă una din valorile returnate de subcerere este valoarea null atunci cererea nu întoarce nici o linie. Prin urmare, dacă valoarea null poate să facă parte din rezultatul subcererii nu trebuie utilizat operatorul NOT IN. Problema nu mai apare dacă se utilizează operatorul IN.

Exemplu:Să se obţină salariaţii care nu au subordonaţi.

SELECT e.ename

SQL 67

FROM emp eWHERE e.empno NOT IN (SELECT m.mgr

FROM emp m);

În acest caz, instrucţiunea SQL nu întoarce nici o linie deoarece una din valorile furnizate de subcerere este valoarea null.

Exemplu:Să se obţină numele salariaţilor, salariile, codul departamentului în care

lucrează şi salariul mediu pe departament pentru toţi angajaţii care au salariul mai mare ca media salariilor din departamentul în care lucrează (folosirea subcererii în clauza FROM).

SELECT a.ename,a.sal,a.deptno,b.salavgFROM emp a,(SELECT deptno,avg(sal) salavg

FROM emp GROUP BY deptno) b

WHERE a.deptno=b.deptnoAND a.sal>b.salavg

Exemplu:Să se obţină lista celor mai scumpe cărţi.

SELECT titluFROM carteWHERE pret = (SELECT MAX(pret)

FROM carte);

Exemplu:Să se obţină lista scriitorilor care au în bibliotecă un număr de exemplare mai

mare decât numărul mediu al cărţilor din bibliotecă.

SELECT DISTINCT autorFROM carteWHERE nrex > (SELECT AVG(nrex)

FROM carte);

Exemplu:Să se obţină informaţii despre cărţile al căror preţ depăşeşte media preţurilor

cărţilor ce aparţin aceluiaşi domeniu

SELECT *FROM carte c

68 Oracle

WHERE pret > (SELECT AVG(pret) FROM carte WHERE coded = c.coded);

Exemplu:Să se obţină lista cititorilor care au împrumutat cel puţin o carte.

SELECT numeFROM cititorWHERE codec IN (SELECT DISTINCT codec

FROM imprumuta);

Exemplu:Să se obţină codurile cititorilor care nu au împrumutat niciodată cărţi.

SELECT codecFROM cititorWHERE codec NOT IN (SELECT DISTINCT codec FROM imprumuta);

Exemplu:Să se obţină lista cititorilor care sunt în întârziere cu predarea cărţilor.

SELECT numeFROM cititorWHERE codec IN (SELECT DISTINCT codec

FROM imprumuta WHERE dataef IS NULL AND dares<SYSDATE);

Exemplu:Să se obţină numele cititorilor care au împrumutat cel puţin o carte scrisă de

ZOLA.

SELECT numeFROM cititorWHERE codec IN

(SELECT DISTINCT codec FROM imprumuta WHERE codel IN

(SELECT codel FROM carte WHERE autor=’ZOLA’));

SQL 69

Exemplu:Să se obţină numele cititorilor care nu au împrumutat nici o carte scrisă de

ZOLA.

SELECT numeFROM cititorWHERE codec NOT IN

(SELECT DISTINCT codec FROM imprumuta WHERE codel IN

(SELECT codel FROM carte

WHERE autor=’ZOLA’));

Operatorul IN poate fi înlocuit cu = ANY (un element este în listă dacă şi numai dacă este egal cu un element al listei), iar operatorul NOT IN poate fi înlocuit prin !=ALL.

Exemplu:Să se obţină codurile cititorilor care au împrumutat o carte de algebră.

SELECT DISTINCT codecFROM imprumutaWHERE codel IN (SELECT codel FROMcarte WHERE coded= (SELECT coded FROM domeniu

WHERE intdom=’ALGEBRA’));

Exemplu:Să se obţină cititorii care au împrumutat numai cărţi scrise de ‘ZOLA’.

SELECT numeFROM cititorWHERE codec NOT IN

(SELECT DISTINCT codec FROM imprumuta WHERE codel NOT IN

(SELECT codel FROM carte

70 Oracle

WHERE autor=’ZOLA’));

Exemplu:Să se obţină numele cititorilor care au împrumutat cel puţin o carte de

informatică (procedural).

SELECT numeFROM cititorWHERE codec IN(SELECT DISTINCT codec FROM imprumuta WHERE codel IN

(SELECT codel FROM carte WHERE coded=

(SELECT coded FROM domeniu WHERE intdom= ’INFORMATICA’)));

Exemplu:Să se obţină, utilizând sincronizarea subcererii cu cererea principală, titlurile

cărţilor care au toate exemplarele împrumutate (se selectează un titlu din carte şi pentru acest titlu se numără câte exemplare sunt împrumutate).

SELECT titluFROM carteWHERE nrex=(SELECT COUNT(*)

FROM imprumuta WHERE codel = carte.codel AND dataef IS NULL);

Exemplu:Să se obţină titlurile cărţilor al căror preţ depăşeşte media preţurilor cărţilor

din domeniul respectiv.

SELECT titluFROM carte cWHERE pret > (SELECT AVG(pret)

FROM carte WHERE coded = c.coded);

Exemplu:Să se obţină codurile cititorilor şi codul ultimei cărţi împrumutate.

SQL 71

SELECT codec, codelFROM imprumuta iWHERE dataim>=ALL (SELECT dataim

FROM imprumuta WHERE codec=i.codec);

Pentru această interogare, clauza WHERE putea fi scrisă şi sub forma:

WHERE dataim=(SELECT MAX(dataim) FROM imprumuta WHERE codec=i.codec);

Exemplu:Să se obţină lista codurilor cărţilor împrumutate şi codul primului cititor care

a împrumutat aceste cărti.

SELECT codel,codecFROM imprumuta iWHERE dataim<=ALL (SELECT dataim

FROM imprumuta WHERE i.codel=codel);

Exemplu:Să se obţină codurile cărţilor din care cel puţin un exemplar este împrumutat.

SELECT codelFROM carteWHERE EXISTS

(SELECT codel FROM imprumuta WHERE codel = carte.codel AND dataef IS NULL);

Operatorul WHERE EXISTS (subcerere) presupune că predicatul este adevărat dacă subcererea întoarce cel puţin un tuplu, iar WHERE NOT EXISTS (subcerere) presupune că predicatul este adevărat dacă subcererea nu întoarce nici un tuplu. EXISTS şi NOT EXISTS cer sincronizarea subcererii.

Exemplu:Să se obţină titlurile cărţilor care sunt momentan împrumutate.

Soluţia 1 (cu sincronizare):SELECT titluFROM carte

72 Oracle

WHERE EXISTS (SELECT * FROM imprumuta WHERE codel = carte.codel AND dataef IS NULL);

Soluţia 2 (fără sincronizare):SELECT titluFROM carteWHERE codel IN

(SELECT DISTINCT codel FROM imprumuta WHERE dataef IS NULL);

Exemplu:Să se obţină codurile cărţilor care nu au fost împrumutate niciodată.

Soluţia 1 (cu sincronizare)SELECT codelFROM carteWHERE NOT EXISTS

(SELECT codel FROM imprumuta WHERE codel = carte.codel);

Soluţia 2 (fără sincronizare)

SELECT codelFROM carteWHERE codel NOT IN

(SELECT DISTINCT codel FROM imprumuta);

Exemplu:Să se obţină lista salariaţilor având salariul minim în departamentul în care

lucrează.

SELECT ename,salFROM emp eWHERE sal=(SELECT MIN(sal)

FROM empWHERE deptno=e.deptno);

Exemplu:

SQL 73

Să se obţină numele primilor trei salariaţi având retribuţia maximă (ideea rezolvării este de a verifica dacă numărul salariaţilor care au leafa mai mare decât leafa salariatului considerat, este mai mic decât 3).

SELECT enameFROM emp aWHERE 3>(SELECT COUNT(*)

FROM emp WHERE sal > a.sal);

Exemplu:Să se obţină numele cititorilor care au împrumutat cel puţin aceleaşi cărţi ca

şi cititorul având codul C19 (ideea problemei este de a selecta cititorii pentru care este vidă lista cărţilor împrumutatede C19 mai puţin lista cărţilor împrumutate de acei cititori).

SELECT numeFROM cititorWHERE NOT EXISTS (SELECT codel FROM imprumuta WHERE codec=’C19’ MINUS

SELECT codel FROM imprumuta WHERE codec= cititor.codec);

Dacă problema era modificată în sensul că „cel puţin”este înlocuit prin „cel mult” atunci trebuiau inversate interogările legate prin MINUS.

Exemplu:Să se obţină codurile cititorilor care au împrumutat aceleaşi cărţi ca şi

cititorul având un cod specificat. Rezolvarea problemei se bazează pe următoarea idee: A = B A B şi B

A (A-B) = şi (B-A) = A-B şi B-A nu furnizează nici un tuplu rezultat.

SELECT codecFROM imprumuta iWHERE NOT EXISTS

(SELECT codel FROM imprumuta WHERE codec=i.codec

74 Oracle

MINUS SELECT codel FROM imprumuta WHERE codec=’&ccc’)

AND NOT EXISTS(SELECT codel FROM imprumuta WHERE codec=’&ccc’

MINUS SELECT codel FROM imprumuta WHERE codec=i.codec)

AND codec!=’&ccc’);

Ultimul operator (AND), asigură să nu apară în rezultat cititorul specificat.

În cazul formei relaţionale de rezolvare a cererii, drumul de acces la informaţie este în sarcina SGBD-lui şi prin urmare nu mai apar cereri imbricate.

Exemplu:Să se obţină numele cititorilor care au împrumutat cel puţin o carte.

Soluţia 1 (forma relaţională):SELECT DISTINCT numeFROM cititor,imprumutaWHERE cititor.codec=imprumuta.codec;

Soluţia 2 (forma procedurală):SELECT numeFROM cititorWHERE codec IN

(SELECT DISTINCT codec FROM imprumuta);

Exemplu:Să se obţină numele cititorilor care au împrumutat cel puţin două cărţi.

Soluţia 1 (forma relaţională):SELECT numeFROM cititor, imprumutaWHERE cititor.codec=imprumuta.codecGROUP BY numeHAVING COUNT(*)>1;

SQL 75

Soluţia 2 (forma procedurală):SELECT numeFROM cititorWHERE codec IN

(SELECT codec FROM imprumuta GROUP BY codec HAVING COUNT(*)>1);

Exemplu:Să se obţină numele cititorilor şi titlurile cărţilor de informatică împrumutate

de aceşti cititori.

SELECT nume, titluFROM cititor, carte, imprumuta, domeniuWHERE imprumuta.codel = carte.codelAND carte.coded = domeniu.codedAND imprumuta.codec = cititor.codecAND intdom = ’INFORMATICA’;

Exemplu:Să se obţină numele salariaţilor care nu cunosc nici o limbă străină.

SELECT nume, prenumeFROM salariatWHERE NOT EXISTS

(SELECT *FROM limbaWHERE limba.cod_salariat = salariat.cod_salariatAND limba.limba_cun IS NOT NULL);

Exemplu:Să se afişeze graficienii care au întârziat să predea frame-urile.

a) cu sincronizare:

SELECT nume, prenumeFROM salariatWHERE EXISTS

(SELECT *FROM realizeaza rWHERE salariat.cod_salariat=r.cod_salariatAND data_lim < SYSDATE);

76 Oracle

b) fără sincronizare:

SELECT nume, prenumeFROM salariatWHERE cod_salariat IN

(SELECT DISTINCT cod_salariat FROM realizeaza WHERE data_lim < SYSDATE);

Exemplu:Să se determine revistele coordonate de redactori şefi care nu cunosc limba

în care sunt scrise. Se ştie că în urma inspectării vizuale a rezultatului interogării se poate decide schimbarea redactorilor şefi ai revistelor respective, de aceea se doreşte blocarea înregistrărilor găsite.

SELECT p.nr_publicatieFROM salariat s, publicatie pWHERE s.cod_salariat = p.cod_salariatAND p.limba NOT IN

(SELECT limba_cun FROM limba WHERE limba.cod_salariat = s.cod_salariat)

FOR UPDATE OF p.cod_salariat;

Funcţii în SQL funcţii pentru manipularea caracterelor, funcţii aritmetice, funcţii pentru manipularea datelor calendaristice, funcţii de conversie, funcţii grup funcţii diverse.

Funcţii de conversieConversiile pot fi făcute:

implicit de către server-ul Oracle ;

explicit de către utilizator.

Conversii impliciteÎn cazul atribuirilor, sistemul poate converti automat:

SQL 77

VARCHAR2 sau CHAR în NUMBER ; VARCHAR2 sau CHAR în DATE; VARCHAR2 sau CHAR în ROWID; NUMBER, ROWID, sau DATE în VARCHAR2.

Pentru evaluarea expresiilor, sistemul poate converti automat: VARCHAR2 sau CHAR în NUMBER, dacă şirul de caractere reprezintă un

număr; VARCHAR2 sau CHAR în DATE, dacă şirul de caractere are formatul

implicit DD-MON-YY; VARCHAR2 sau CHAR în ROWID.

Conversii explicite

funcţia TO_CHAR converteşte data calendaristică sau informaţia numerică în şir de caractere conform unui format;

funcţia TO_NUMBER converteşte un şir de caractere în număr;

funcţia TO_DATE converteşte un şir de caractere în dată calendaristică conform unui format.

Dacă formatul este omis, convertirea se face conform unui format implicit. Funcţia TO_DATE are forma TO_DATE(şir_de_caractere [,’fmt’]). Funcţia este utilizată dacă se doreşte conversia unui şir de caractere care nu are formatul implicit al datei calendaristice (DD-MON-YY).

Alte funcţii de conversie sunt: CHARTOROWID, CONVERT, HEXTORAW, RAWTOHEX, ROWIDTOCHAR etc., iar denumirea semnificativă arată rolul fiecăreia.

Exemplu:

SELECT TO_DATE(’Feb 22,1981’,’Mon dd,YYYY’)FROM DUAL;

Funcţii pentru manipularea caracterelor

LENGTH(string) – returnează lungimea şirului de caractere string;

LENGTHB(string) – îndeplineşte aceaşi funcţie ca şi LENGTH, cu deosebirea că returnează numărul de octeţi ocupaţi;

SUBSTR(string, start [,n]) – returnează subşirul lui string care începe pe poziţia start şi are lungimea n; dacă n nu este specificat, subşirul se termină la sfârşitul lui string;

78 Oracle

LTRIM(string [,’chars’]) – şterge din stânga şirului string orice caracter care apare în chars până la găsirea primului caracter care nu este în chars; dacă chars nu este specificat, se şterg spaţiile libere din stânga lui string;

RTRIM(string [,’chars’]) – este similar funcţiei LTRIM, cu excepţia faptului că ştergerea se face la dreapta şirului de caractere;

LPAD(string, length [,’chars’]) – adaugă chars la stânga şirului de caractere string până când lungimea noului şir devine length; în cazul în care chars nu este specificat, atunci se adaugă spaţii libere la stânga lui string;

RPAD(string, length [,’chars’]) – este similar funcţiei LPAD, dar adăugarea de caractere se face la dreapta şirului;

REPLACE(string1, string2 [,string3]) – returnează string1 cu toate apariţiile lui string2 înlocuite prin string3; dacă string3 nu este specificat, atunci toate apariţiile lui string2 sunt şterse;

INITCAP(string) – transformă primul caracter al şirului în majusculă;

INSTR(string, ‘chars’ [,start [,n]]) – caută în string, începând de de la poziţia start, a n-a apariţie a secvenţei chars şi întoarce poziţia respectivă; dacă start nu este specificat, căutarea se face de la începutul şirului; dacă n nu este specificat, se caută prima apariţie a secvenţei chars;

UPPER(string), LOWER(string) – transformă toate literele şirului de caractere string în majuscule, respectiv minuscule;

ASCII(char) – returnează codul ASCII al unui caracter;

CHR(num) – returnează caracterul corespunzător codului ASCII specificat;

CONCAT(string1, string2) – realizează concatenarea a două şiruri de caractere;

SOUNDEX(string) – returnează reprezentarea fonetică a şirului de caractere specificat;

TRANSLATE(string, from, to) – fiecare caracter care apare în şirurile de caractere string şi from este transformat în caracterul corespunzător (aflat pe aceeaşi poziţie ca şi în from) din şirul de caractere to;

Funcţii aritmetice

SQL 79

Cele mai importante funcţii aritmetice sunt: ABS (valoarea absolută), ROUND (rotunjire cu un număr specificat de zecimale), TRUNC (trunchiere cu un număr specificat de zecimale), EXP (ridicarea la putere a lui e), LN (logaritm natural), LOG (logaritm într-o bază specificată), MOD (restul împărţirii a două numere specificate), POWER (ridicarea la putere), SIGN (semnul unui număr), COS (cosinus), COSH (cosinus hiperbolic), SIN(sinus), SQRT(rădăcina pătrată), TAN(tangent), funcţiile LEAST şi GREATEST, care returnează cea mai mică, respectiv cea mai mare valoare a unei liste de expresii etc.

Funcţii grup

AVG (media aritmetică),

COUNT(*) (numărul de linii returnate de o cerere),

COUNT (numărul valorilor unui expresii),

SUM (suma valorilor unei expresii),

MIN (valoarea minimă a unei expresii),

MAX (valoarea maximă a unei expresii),

STDDEV (deviaţia standard),

VARIANCE (dispersia).

Observaţii: Funcţiile grup operează pe un grup de linii şi nu cer folosirea clauzei

GROUP BY.

Funcţiile grup ignoră valorile null.

Orice funcţie grup întoarce o singură valoare.

Ele întorc valoarea null când sunt aplicate unei mulţimi vide, cu excepţia operatorului COUNT care întoarce valoarea zero.

Spre deosebire de funcţiile COUNT, MIN şi MAX care pot fi aplicate unor câmpuri numerice sau nenumerice, restul funcţiilor grup se aplică doar câmpurilor numerice.

Funcţiile grup pot să apară în lista de la SELECT sau în clauza HAVING.

Exemplu:Să se afişeze numărul cărţilor distincte împrumutate.

SELECT COUNT(DISTINCT codel)FROM imprumuta;

80 Oracle

Exemplu:Comanda care urmează este greşită! De ce?

SELECT titlu, COUNT(*)

FROM carte;

Exemplu:Să se calculeze media preţurilor cărţilor din bibliotecă.

SELECT AVG(pret)FROM carte;

Funcţii pentru manipularea datelor calendaristice

SYSDATE – returnează data şi timpul curent;

ADD_MONTHS(d, count) – returnează data care este după count luni de la data d;

NEXT_DAY(d, day) – returnează următoarea dată după data d, a cărei zi a săptămânii este cea specificată prin şirul de caractere day;

LAST_DAY(d) – returnează data corespunzătoare ultimei zile a lunii din care data d face parte;

MONTHS_BETWEEN(d2, d1) – returnează numărul de luni dintre cele două date calendaristice specificate;

NEW_TIME(data, zona_intrare, zona_iesire) – returnează ora din zona_intrare corespunzătoare orei din zona_iesire;

ROUND(d) – dacă data d este înainte de miezul zilei, întoarce data d cu timpul setat la ora 12:00 AM; altfel, este returnată data corespunzătoare zilei următoare, cu timpul setat la ora 12:00 AM;

TRUNC(d) – întoarce data d, dar cu timpul setat la ora 12:00 AM (miezul nopţii);

LEAST(d1, d2, …, dn), GREATEST(d1, d2, …, dn) – returnează, dintr-o listă de date calendaristice, prima, respectiv ultima dată în ordine cronologică.

Exemplu:ROUND(’25-jul-95’, ’MONTH’) este 01-AUG-95,ROUND(’25-jul-95’, ’YEAR’) este 01-JAN-96,TRUNC(’25-jul-95’, ’MONTH’) este 01-JUL-95,TRUNC(’25-jul-95’, ’YEAR’) este 01-JAN-95.

SQL 81

Utilizarea literelor mari sau mici în formatul unei date calendaristice precizează forma rezultatului. De exemplu, ’MONTH’ va da rezultatul MAY, iar ’Month’ va da rezultatul May.

Pentru afişarea câmpurilor de tip dată calendaristică sau pentru calcule în care sunt implicate aceste câmpuri, există funcţii specifice. Câteva din elementele care apar în formatul unei date calendaristice sunt prezentate în tabelul următor.

Format Descriere Domeniu

SS Secunda relativ la minut 0-59SSSSS Secunda relativ la zi 0-86399MI Minut 0-59HH Ora 0-12HH24 Ora 0-24DAY Ziua săptămânii SUNDAY-SATURDAYD Ziua săptămânii 1-7DD Ziua lunii 1-31 (depinde de lună)DDD Ziua anului 1-366 (depinde de an)MM Numărul lunii 1-12MON Numele prescurtat al lunii JAN-DECMONTH Luna JANUARY-DECEMBERYY Ultimele două cifre ale anului de exemplu, 99YYYY Anul de exemplu, 1999YEAR Anul în litereCC Secolul de exemplu, 17Q Numărul trimestrului 1-4W Săptămâna lunii 1-5WW Săptămâna anului 1-52

Operaţii cu date calendaristice

Operaţie Rezultat DescriereData + număr Data Adaugă un număr de zile la o datăData - număr Data Scade un număr de zile dintr-o datăData - data Număr zile Scade două date calendaristiceData + numar/24 Data Adună un număr de ore la o dată

Funcţii diverse

82 Oracle

DECODE(value, if1, then1, if2, then2, … , ifN, thenN, else) – returnează then1 dacă value este egală cu if1, then2 dacă value este egală cu if2 etc.; dacă value nu este egală cu nici una din valorile if, atunci funcţia întoarce valoarea else (selecţie multiplă);

NVL(e1, e2) – dacă e1 este NULL, returnează e2; altfel, returnează e1;

UID – returnează ID-ul utilizatorului curent;

USER – returnează username-ul utilizatorului curent;

VSIZE(expr) – returnează numărul de octeţi ai unei expresii de tip DATE, NUMBER sau VARCHAR2;

EMPTY_BLOB – iniţializează o variabilă sau o coloană de tip BLOB;

NLS_CHARSET_NAME(id_set_caractere) – returnează numele setului de caractere NLS asociat cu identificatorul setului transmis ca argument. Această funcţie nu există în versiuni anterioare Oracle8.

Exemplu:NVL(comision, 0) este 0 dacă comisionul este null. Prin urmare, expresia

salariu*12 + comision nu este corectă, deoarece rezultatul său este null dacă comisionul este null. Forma corectă este salariu*12 + NVL(comision, 0).

Exemplu:Să se afişeze preţul modificat al unor cărţi în funcţie de editură. Pentru cărţile

din editura ALL să se dubleze preţurile, pentru cele din editura UNIVERS să se tripleze preţurile, iar pentru cele din editura XXX să se reducă la jumătate acest preţ.

SELECT pret,editura, DECODE(editura, ’ALL’,pret*2, ’UNIVERS’,pret*3, ’XXX’,pret/2, pret) pret_revizuit FROM carte;

LIMBAJUL PENTRU CONTROLUL DATELOR

Sistemul de gestiune trebuie:

să pună la dispoziţia unui număr mare de utilizatori o mulţime coerentă de date;

SQL 83

să garanteze coerenţa datelor în cazul manipulării simultane de către diferiţi utilizatori.

Coerenţa este asigurată cu ajutorul conceptului de tranzacţie. Tranzacţia este unitatea logică de lucru constând din una sau mai multe instrucţiuni SQL, care trebuie să fie executate atomic (ori se execută toate, ori nu se execută nici una!), asigurând astfel trecerea BD dintr-o stare coerentă în altă stare coerentă.

Dacă toate operaţiile ce constituie tranzacţia sunt executate şi devin efective, spunem că tranzacţia este validată, iar modificările aduse de tranzacţie devin definitive.

Dacă dintr-un motiv sau altul (neverificarea condiţiilor, accesul imposibil) o operaţie a tranzacţiei nu a fost executată spunem că tranzacţia a fost anulată. Modificările aduse de toate operaţiile tranzacţiei anulate sunt şi ele anulate şi se revine la starea bazei de date de dinaintea tranzacţiei anulate.

Este posibil ca o tranzacţie să fie descompusă în subtranzacţii, astfel încât dacă este necesar să se anuleze doar parţial unele operaţii.

Fiecare tranzacţie se poate termina:

“normal” (commit);

“anormal” (rollback).

Controlul tranzacţiilor constă în:

definirea începutului şi sfârşitului unei tranzacţii,

validarea sau anularea acesteia,

eventuală descompunere în subtranzacţii.

Limbajul pentru controlul datelor (LCD) permite salvarea informaţiei, realizarea fizică a modificărilor în baza de date, rezolvarea unor probleme de concurenţă. Limbajul conţine următoarele instrucţiuni:

COMMIT - folosită pentru permanentizarea modificărilor executate asupra BD (modificările sunt înregistrate şi sunt vizibile tuturor utilizatorilor);

ROLLBACK - folosită pentru refacerea stării anterioare a BD (sunt anulate toate reactualizările efectuate de la începutul tranzacţiei);

SAVEPOINT - folosită în conjuncţie cu instrucţiunea ROLLBACK, pentru definirea unor puncte de salvare în fluxul programului.

O tranzacţie constă:

dintr-o singură instrucţiune LDD;

dintr-o singură instrucţiune LCD;

84 Oracle

din instrucţiuni LMD care fac schimbări consistente în date.

Tranzacţia începe:

după o comandă COMMIT,

după o comandă ROLLBACK,

după conectarea iniţială la Oracle,

când este executată prima instrucţiune SQL.

Tranzacţia se termină:

dacă sistemul cade;

dacă utilizatorul se deconecteazăş

dacă se dau comenzile COMMIT sau ROLLBACK ;

dacă se execută o comandă LDD.

După ce se termină o tranzacţie, prima instrucţiune SQL executabilă va genera automat începutul unei noi tranzacţii.

Un commit apare automat când este executată o comandă LDD, sau o comandă LCD, sau după o ieşire normală din SQL*Plus fără specificarea explicită a comenzilor COMMIT sau ROLLBACK. Un rollback apare automat după o ieşire “anormală“ din SQL*Plus sau o cădere sistem.

Din momentul în care s-a executat instrucţiunea COMMIT, BD s-a modificat (permanent) în conformitate cu instrucţiunile SQL executate în cadrul tranzacţiei care tocmai s-a terminat. Din acest punct începe o nouă tranzacţie.

Dacă se foloseşte utilitarul SQL*Plus, există posibilitatea ca după fiecare comandă LMD să aibă loc o permanentizare automată a datelor (un COMMIT implicit). Acest lucru se poate realiza folosind comanda:

SET AUTO[COMMIT] {ON | OFF]

Comanda ROLLBACK permite restaurarea unei stări anterioare a BD.

ROLLBACK [TO [SAVEPOINT] savepoint];

Dacă nu se specifică nici un savepoint, toate modificările făcute în tranzacţia curentă sunt anulate, iar dacă se specifică un anumit savepoint, atunci doar modificările de la acel savepoint până în momentul respectiv sunt anulate. Executarea unei instrucţiuni ROLLBACK presupune terminarea tranzacţiei curente şi începerea unei noi tranzacţii.

SQL 85

Punctele de salvare pot fi considerate ca nişte etichete care referă o submulţime a schimbărilor dintr-o tranzacţie, marcând efectiv un punct de salvare pentru tranzacţia curentă. Server-ul Oracle implementează un punct de salvare implicit pe care îl mută automat după ultima comandă LMD executată. Dacă este creat un punct de salvare având acelaşi nume cu unul creat anterior, cel definit anterior este şters automat.

SAVEPOINT savepoint;

Exemplu:Comanda ROLLBACK nu va genera terminarea tranzacţiei.

COMMITINSERT …SAVEPOINT aUPDATE …INSERT …SAVEPOINT bDELETE …ROLLBACK TO a

Starea datelor înainte de COMMIT sau ROLLBACK este următoarea:

starea anterioară a datelor poate fi recuperată;

utilizatorul curent poate vizualiza rezultatele operaţiilor LMD prin interogări asupra tabelelor;

alţi utilizatori nu pot vizualiza rezultatele comenzilor LMD făcute de utilizatorul curent (read consistency);

înregistrările afectate sunt blocate şi, prin urmare, alţi utilizatori nu pot face schimbări în datele acestor înregistrări.

Execuţia unei comenzi COMMIT implică anumite modificări.

Toate schimbările (INSERT, DELETE, UPDATE) din baza de date făcute după anterioara comandă COMMIT sau ROLLBACK sunt definitive. Comanda se referă numai la schimbările făcute de utilizatorul care dă comanda COMMIT.

Toate punctele de salvare vor fi şterse.

Starea anterioară a datelor este pierdută definitiv.

Toţi utilizatorii pot vizualiza rezultatele.

86 Oracle

Blocările asupra liniilor afectate sunt eliberate; liniile pot fi folosite de alţi utilizatori pentru a face schimbări în date.

Execuţia unei comenzi ROLLBACK implică anumite modificări.

Anulează tranzacţia în curs şi toate modificările de date făcute după ultima comandă COMMIT.

Sunt eliberate blocările liniilor implicate.

Nu şterge un tabel creat prin CREATE TABLE. Eliminarea tabelului se poate realiza doar prin comanda DROP TABLE.

Exemplu:Ce efect are următoarea secvenţă de instrucţiuni?

(a) SELECT *FROM salariat;

(b) SAVEPOINT a;

(c) DELETE FROM salariat;INSERT INTO salariat VALUES (18,’Breaban’,’Marin’,4,5000, ’tehnored’);INSERT INTO salariatVALUES (23,’Popescu’,’Emil’,7,40000,’grafician’);SAVEPOINT b;

(d) INSERT INTO salariatVALUES (29,’’,’’,5,3000000,’tehnoredactor’);SELECT AVG(salariu)FROM salariat;

(e) ROLLBACK TO b;SELECT AVG(salariu)FROM salariat;

(f) ROLLBACK TO a;INSERT INTO salariatVALUES (18,’Ion’,’Mihai’,5,580,’redr_sef’);COMMIT;

SQL 87

Într-un sistem multi-user, sistemul Oracle 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 la ultimul commit, înainte să înceapă o operaţie LMD. Prin urmare, modificările efectuate asupra unei baze de date nu sunt vizibile decât după ce operaţia de actualizare a fost validată. Numai utilizatorul care a executat tranzacţia poate vedea modificările făcute de el în cursul acestei tranzacţii.

Modelul multiversiune, furnizat de Oracle, asigură consistenţa la citire:

garantează că setul de date văzut de orice instrucţiune SQL este consistent şi nu se schimbă în timpul execuţiei unei instrucţiuni (Oracle asigură o consistenţă la citire la nivel de instrucţiune);

operaţiile de citire(SELECT) nu trebuie să vadă datele care sunt în proces de schimbare;

operaţiile de scriere (INSERT, DELETE, UPDATE) nu trebuie să afecteze consistenţa datelor şi să întrerupă sau să intre în conflict cu alte operaţii de scriere concurente.

Cum se implementează modelul multiversiune? Dacă asupra bazei este executată o comandă LMD, server-ul Oracle face o copie a datelor dinainte de modificare şi o depune în segmentul rollback. Toţi utilizatorii (cu excepţia celor care modifică datele) vor vedea datele cum sunt înainte de modificare (văd conţinutul segmentului rollback). Dacă comanda LMD este commit, atunci schimbările din baza de date devin vizibile oricărui utilizator care foloseşte instrucţiunea SELECT. Când se termină tranzacţia, spaţiul ocupat în segmentul rollback de “vechea” dată este liber pentru reutilizare. Server-ul Oracle asigură astfel o vizualizare consistentă a datelor în orice moment.

Blocările sunt folosite în ORACLE pentru a asigura integritatea datelor, permiţând în acelaşi timp accesul concurent la date de către un număr “infinit” de utilizatori.

Din punct de vedere a resursei blocate, blocările pot fi:

la nivel de linie (blocarea afectează un rând);

nivel de tabel (blocarea afectează întreg tabelul).

La nivel de rând, blocările se pot face numai în modul exclusiv (X), adică un utilizator nu poate modifica un rând până ce tranzacţia care l-a blocat nu s-a terminat (prin permanentizare sau prin derulare înapoi).

88 Oracle

Blocările la nivel de tabel pot fi făcute în mai multe feluri, în funcţie de caracterul mai mult sau mai puţin restrictiv al blocării (RS – row share; RX – row exclusive; S – share; SRX – share row exclusive; X – exclusive).

Modul X de blocare la nivel de tabel este cel mai restrictiv. Blocarea în mod X este obţinută la executarea comenzii LOCK TABLE cu opţiunea EXCLUSIVE. O astfel de blocare permite altor tranzacţii doar interogarea tabelului. Tabelul nu mai poate fi blocat în acelaşi timp de nici o altă tranzacţie în nici un mod.

Modul de blocare RX arată că tranzacţia care deţine blocarea a făcut modificări asupra tabelului. O blocare RX permite acces (SELECT, INSERT, UPDATE, DELETE) concurent la tabel şi blocarea concurentă a tabelului de către altă tranzacţie în modurile RS şi RX.

Modul de blocare S (se obţine prin comanda LOCK TABLE cu opţiunea SHARE) permite altor tranzacţii doar interogarea tabelului şi blocarea sa în modurile S şi RS.

Modul de blocare SRX (se obţine prin comanda LOCK TABLE cu opţiunea SHARE ROW EXCLUSIVE) permite altor tranzacţii doar interogarea tabelului şi blocarea sa în modul RS.

Modul de blocare RS permite acces (SELECT, INSERT, UPDATE, DELETE) concurent la tabel şi blocarea concurentă a tabelului de către altă tranzacţie în orice mod, în afară de X. Modul de blocare RS, care este cel mai puţin restrictiv, arată că tranzacţia care a blocat tabelul, a blocat rânduri din tabel şi are intenţia să le modifice.

Din punct de vedere a modului de declanşare a blocării, blocările pot fi:

implicite (blocarea este făcută automat de sistem în urma unei operaţii INSERT, DELETE sau UPDATE şi nu necesită o acţiune din partea utilizatorului);

explicite (blocarea este declanşată ca urmare a comenzilor LOCK TABLE sau SELECT cu clauza FOR UPDATE).

Folosirea clauzei FOR UPDATE într-o comandă SELECT determină blocarea rândurilor selectate în modul X şi blocarea întregului tabel (sau tabelelor) pe care se face interogarea în modul RS. La actualizarea rândurilor (UPDATE) blocarea la nivel de linie se menţine în timp ce blocarea la nivel de tabel devine RX.

Exemplu:

SQL 89

SELECT salariuFROM salariatWHERE cod_salariat = 1234FOR UPDATE OF salariu;

UPDATE salariatSET salariu = 23456WHERE cod_salariat = 1234;

COMMIT;

La executarea primei comenzi, rândul cu cod_salariat = 1234 este blocat în mod X în timp ce tabelul salariat este blocat în modul RS. La executarea celei de a doua comenzi, blocarea la nivel de linie se menţine în timp ce blocarea la nivel de tabel devine RX. La executarea comenzii COMMIT, tranzacţia este permanentizată şi toate blocările sunt eliberate.

Unul sau mai multe tabele pot fi blocate în oricare din modurile prezentate mai sus folosind comanda LOCK TABLE, care are sintaxa:

LOCK TABLE nume_tabel [, nume tabel] … IN mod_blocare MODE [NOWAIT]

unde mod_blocare poate avea valorile ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE. Dacă se specifică NOWAIT şi rândurile selectate sunt deja blocate de altă tranzacţie, atunci utilizatorul este înştiinţat de acest lucru, returnându-i-se controlul.

Datorită accesului concurent la date este posibil ca mai mulţi utilizatori să se blocheze reciproc. Această situaţie este numită interblocare (deadlock), pentru că fiecare dintre utilizatori aşteaptă ca celălalt să elibereze resursa blocată. În cazul acesta problema nu se poate rezolva prin simpla aşteptare, una din tranzacţii trebuind să fie derulată înapoi. Oracle detectează automat interblocările. În acest caz, Oracle semnalează o eroare uneia dintre tranzacţiile implicate şi derulează înapoi ultima instrucţiune din această tranzacţie. Acest lucru rezolvă interblocarea, deşi cealaltă tranzacţie poate încă să aştepte până la deblocarea resursei pentru care aşteaptă.

Care din următoarele comenzi încheie o tranzacţie?SELECTROLLBACK UPDATEDELETE

90 Oracle

CREATE TABLE