1
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
I. Tipuri de date ORACLE. Schema conceptuală a bazei de date
Structurile logice ale unei baze de date Oracle (DE URMĂRIT TOATE ACESTE
NOŢIUNI LA CURS!):
Spaţiile tabel (tablespaces) – elemente logice care compun baza de date şi care
grupează logic alte elemente componente ale bazei de date. De exemplu, un tablespace
poate conţine toate elementele unei aplicaţii pentru a facilita administrarea acesteia. O
bază de date Oracle poate gestiona fişiere foarte mari de până la 8 ExaBytes1;
Blocurile de date (data blocks) – reprezintă nivelul de granularitate cel mai mic.
Acestea sunt blocurile fizice de memorie în care sunt stocate efectiv datele.
Dimensiunea unui bloc de date este specificată la definirea bazei de date prin
parametrul DB_BLOCK_SIZE;
Extinderi (extents) – O extindere este formată dintr-un număr de blocuri de date
contigue stocate în aceeaşi locaţie şi care conţin un anumit tip de informaţii;
Segmente (segments) – conţine mai multe extinderi ce stochează acelaşi tip de
informaţii (segmente de date, segmente de indecşi, etc);
Elementele componente ale unei scheme. O schemă conţine toată activitatea unui utilizator.
Schema este creată automat de către ORACLE la crearea utilizatorului şi are aceeaşi denumire
cu acesta.
Tabele – elementele de bază pentru stocarea datelor create şi manevrate de utilizatori
în Oracle;
Indecşi – sunt structuri opţionale asociate tabelelor. Pot fi definiţi pentru una sau mai
multe coloane ale tabelei şi au rolul de a creşte viteza de acces la date.
Vederi (views) – reprezintă prezentări ale datelor din mai multe tabele şi/sau vederi.
Vederile nu conţin efectiv datele. Ca şi în cazul datelor din tabele, şi asupra datelor
vederilor se pot face operaţii de interogare, adăugare, ştergere, modificare, etc. Toate
modificările făcute în vedere, se vor reflecta însă şi asupra datelor din tabelele care
stau la baza vederii.
Clusteri (clusters) – conţin unul sau mai multe tabele fizice memorate împreună
deoarece au coloane comune sau sunt utilizate frecvent împreună. Clusterii cresc
viteza de acces la date. Faptul ca un tabel face parte dintr-un cluster este transparent
utilizatorului. Lucrul cu tabelul se va face la fel ca şi în cazul în care tabelul nu ar
aparţine nici unui cluster.
Sinonimele (synonyms) – sunt alias-uri definite pentru o tabelă, coloană, procedură,
vedere, secvenţă, funcţie, etc.
Modelul relaţional al bazei de date pe care se va lucra la laborator.
Baza de date propusă are ca obiectiv rezolvarea „Evidenţei vânzărilor la o societate
comercială prin intermediul agenţilor comerciali”. Modelul relaţional se compune în
principal din următoarele relaţii:
1 1 ExaByte = 2
30GB=2
20TB (TeraByte)=2
10PB (PetaByte)
2
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
Categorii(CodC, DenC, Descr, Foto)
Produse(CodP, DenP, UM, DataF, TermG, CantS, PretS)
Clienţi(CUI, DenCL, Adesa, Telefon, Localitate, Judet, Regiune)
Agenţi(Codag, Nume, Prenume, Data_ang, Data_n, Sex, Studii)
Facturi(NRFact, DataFact, Discount, DataS)
DocInc(NRDoc, TipDoc, DataDoc)
I. Elementele componente ale limbajului SQL:
Tipuri de date şi operatori;
Limbajul de definire a datelor LDD (Data Definition Language - DDL);
Limbajul de manevrare a datelor LMD (Data Manipulation Language - DML);
Limbajul pentru procesarea tranzacţiilor LPT (Data Processing Transaction -DPT);
Limbajul de interogare a datelor LI (Data Interogation Language - DIL);
I.1 Tipuri de date şi operatori specifici SQL
SQL (Structured Query Language) este un limbaj de interogare, definire şi manipulare a
datelor, implementat în toate SGBD-urile relaționale, cu mici diferenţe de la un SGBD la
altul.
1. Tipuri de date:
TIP Caracteristici
Tipuri de date pentru şiruri de caractere
VARCHAR2(size)
Şir de caractere de lungime variabilă. Dimensiunea (size) poate
fi exprimată în caractere sau în Bytes.
VARCHAR2(10 Byte) – va permite memorarea unui număr de
caractere până la ocuparea celor 10B. De obicei 1 caracter ocupă
1B de memorie. Sunt însă şi caractere care pot necesita mai mulţi
B pentru stocare.
VARCHAR2(10 CHAR) – va permite stocarea a maxim 10
caractere dar este posibil sa ocupe mai mult de 10B dacă include
şi caractere care necesită mai mult de 1B pentru stocare.
Lungimea maximă a şirurilor de caractere pe care tipul
VARCHAR le poate memora este de 4000 de caractere.
CHAR(size)
Şir de caractere de lungime fixă. Este similar cu VARCHAR2 cu
deosebirea că întotdeauna va ocupa spaţiul declarat şi nu cel
folosit.
Exemplu:
Nume VARCHAR2 (10) – pentru numele Ion de exemplu, va
ocupa 3B şi nu 10 aşa cum e declarat
Nume CHAR (10) – pentru numele Ion va ocupa 10B atât cât
este declarată lungimea maximă şi nu cât sunt folosiţi. Cei 7 B
diferenţă sunt ocupaţi cu caracterul spaţiu.
Lungimea maximă a şirurilor de caractere pe care tipul CHAR le
poate memora este de 2000 de caractere.
3
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
NCHAR,
NVARCHAR2
Permite stocarea caracterelor codificate UNICODE care necesită
mai mulţi Bytes pentru stocare. De aceea lungimea nu se declară
decât în număr de caractere, nu se mai permite declararea
lungimii în Bytes.
Lungimea maximă a şirurilor de caractere declarate NCHAR este
2000 iar pentru cele declarate NVARCHAR2 este 4000.
LONG
Poate stoca şiruri de caractere până la 2GB. Se mai foloseşte doar
pentru compatibilitate cu aplicaţiile mai vechi. În locul lui se
folosesc tipurile CLOB şi NCLOB.
CLOB, NCLOB Stochează şiruri de caractere până la 4GB.
Tipuri de date pentru numere
NUMBER(p,s)
Permite stocarea valorilor numerice întregi sau cu virgulă
pozitive sau negative. Intervalul de valori acceptat: [10-130
,
9,99*10125
]. Spaţiul ocupat este de 21B.
p – precizia (precision) reprezintă nr. total de cifre al numărului=
1..38,
s - partea fracţionară (scale, numărul de zecimale =-84..127
A se vedea tabelul de mai jos, pentru modul de stocare a datelor
numerice folosind tipul Number.
BINARY_FLOAT,
BINARY_DOUBLE
Se folosesc pentru stocarea datelor numerice cu virgulă în dublă
precizie. Valori foarte mari sau cu precizie foarte mare. Aceste
valori pot fi stocate folosind şi tipul Number. Binary_Float şi
Binary_Double stochează valorile în format binar şi din acest
motiv ocupă mai puţin spaţiu şi permit calcule mai rapide decât
tipul Number.
BINARY_FLOAT [1.17549*10-38
, 3.40282*1038
]. Ocupă 4B.
BINARY_DOUBLE [2.22507*10-308
, 1.79769*10308
]. Ocupă
8B.
Tipuri de date pentru date calendaristice
DATE
Stochează date calendaristice şi timpul (secolul, anul, luna, ziua,
ora, minutele, secundele). Deşi fizic este memorat şi timpul, în
coloană va fi afişată utilizatorului doar data. Ocupă 7B iar
formatul implicit al datei este dd-mon-yy ( 05-Jun-09)
TIMESTAMP Folosit pentru memorarea timpului până la nivel de fracţiuni de
secundă.
Tipuri de date pentru alte categorii de date
RAW, LONG RAW
Stochează date (caractere, imagini, sunete, etc) în format binar.
Lungime maximă 2000 B respectiv 2GB. Se mai folosesc doar
pentru compatibilitate cu aplicaţii mai vechi. Pentru aplicaţii noi
se recomandă tipurile LOB.
BLOB
Binary Large Objects, max 4GB. Stochează date nestructurate
(text, imagine, video, date spaţiale, grafice, fişiere XLS, DOC,
etc) în format binar.
BFILE
Stochează date nestructurate în fişiere binare stocate în sistemul
de fişiere al SO şi nu în baza de date. Aceste fişiere sunt
readonly.
4
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
Modalităţi de stocare a valorilor numerice folosind tipul Number.
Valoarea introdusă Tipul definit Modul de stocare
123.8915 NUMBER 123.8915
123.8915 NUMBER(3) 124
123.8915 NUMBER(4,1) 123.9
123.8915 NUMBER(5,2) 123.89
123.8915 NUMBER(6,3) 123.892
123.8915 NUMBER(7,4) 123.8915
2. Operatori SQL:
Operator Caracteristici
<, >, =, >=, <=, NOT Operatori de comparaţie
BETWEEN ... AND ... Verifică încadrarea între două valori (inclusiv). Stoc
Between 5 And 10
IN(listă) Egal cu oricare valoare din listă.
Nume IN (‘Ion‘,‘Vasile‘,‘Maria‘)
LIKE
Similar cu valoarea specificată:
% - înlocuieşte oricâte caractere: Nume LIKE ‘Pop%’ –
poate returna atât Popa cât şi Popescu.
_ (underscore)- înlocuieşte un caracter:
Nume LIKE ‘Pop_ _ _ _’ – va returna doar Popescu (toate
numele care încep cu Pop urmate strict de alte 4 caractere)
IS NULL Verifică dacă o expresie are valoarea NULL:
Nume IS NULL. Returnează Adevărat sau Fals.
3. Funcţii pentru conversia tipurilor de date
ORACLE realizează implicit conversia datelor după următoarele reguli:
în cadrul operaţiilor de inserare (INSERT) şi modificare (UPDATE) a datelor, Oracle
converteşte datele în formatul aferent tipului ataşat coloanelor afectate;
în cadrul operaţiilor de interogare (SELECT), Oracle converteşte implicit datele din
coloane în formatul cerut de variabilele utilizate;
când se compară o valoare de tip caracter cu o valoare de tip numeric, Oracle implicit
converteşte automat valoarea de tip caracter în valoare numerică;
când se compară o valoare de tip caracter cu una de tip dată, Oracle converteşte datele
de tip caracter în dată calendaristică;
când se foloseşte o funcţie sau operator cu un tip de dată neacceptată ca argument,
ORACLE automat converteşte argumentul la tipul acceptat de operatorul sau funcţia
respectivă;
în cazul operaţiilor de atribuiri de valori, ORACLE converteşte automat valoarea din
dreapta semnului egal la tipul variabilei din stânga semnului;
în cazul operaţiilor de concatenare, ORACLE converteşte valorile noncaracter în
valori de tip caracter;
în cazul operaţiilor aritmetice între o valoare de tip caracter şi una de tip numeric,
ORACLE converteşte automat datele de tip caracter în date numerice;
5
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
EXEMPLE:
SELECT CantS + '10'
FROM produse;
Oracle va converti implicit textul '10' care fiind scris între apostrof înseamnă că este de tip
carcater, în număr. Coloana Cants este de tip numeric.
SELECT DenP
FROM produse
WHERE CodP=’200’
Coloana CodP este de tip numeric. În acest caz, ORACLE va converti caracterele ’200’ în
numărul 200.
SELECT DenP
FROM produse
WHERE DataF=’03-JAN-05’
Oracle va converti automat şirul de caractere ‘03-JAN-05’ în dată calendaristică folosind
formatul implicit pentru dată ’DD-MON-YY’.
SELECT 20+’10’ FROM DUAL
Rezultatul va fi 30. Oracle va converti şirul de caractere 10 în număr. DUAL este un tabel cu
o singură coloană utilizat de Oracle pentru execuţia de comenzi care nu implică date din
tabele.
SELECT 20 ||’10’ FROM DUAL
Rezultatul va fi 2010. Oracle va converti numărul 10 în şir de caractere. ( || - este operatorul
de concatenare a două şiruri de caractere).
Conversia explicită a datelor se face folosind funcţiile:
Funcţie Caracteristici
TO_NUMBER(caractere) Converteşte un şir de caractere în număr
TO_DATE (caractere) Converteşte un şir de caractere în dată calendaristică
TO_CHAR(număr) Converteşte un număr în caractere
TO_CHAR (dată) Converteşte o dată calendaristică într-un şir de caractere
I.2 Comenzi LDD (Limbajul de Definire a Datelor - Data Definition
Language)
Comanda Scop
CREATE Creează un obiect nou (tabela, utilizator, rol, etc)
6
Laborator 1 – SGBD Oracle
Universitatea “Dunarea de Jos” Galati Lect. dr. Gianina Mihai
ALTER Modifică o parte dintre proprietăţile unui obiect
DROP Elimină un obiect din baza de date
GRANT Acordă utilizatorilor drepturile necesare pentru accesul şi manipularea
obiectelor din baza de date
REVOKE Anulează anumite drepturi acordate utilizatorilor
I.3 Comenzi LMD (Limbajul de Manipulare a Datelor - Data Manipulation
Language) Comanda Scop
DELETE Şterge înregistrări din tabele
INSERT Adaugă o nouă înregistrare în tabelă
UPDATE Modifică valorile unor înregistrări din tabele
MERGE Adaugă un tuplu în funcţie de o anumită condiţie
I.4 Comenzi LPT (Limbajul pentru Procesarea Tranzacţiilor)
Comanda Scop
COMMIT Finalizează o tranzacţie
ROLLBACK Anulează o tranzacţie
SAVEPOINT
Defineşte un punct de salvare, în locul în care tranzacţia salvează
toate operaţiile efectuate până în momentul respectiv, înainte de a
continua următorul set de modificări efectuate în baza de date.
I.5 Comenzi LI (Limbajul de interogare a datelor)
SELECT Regăseşte şi afişează înregistrări din tabele sau vederi.
PROBLEME DE REZOLVAT
1. Pornind de la relaţiile identificate mai sus, să se realizeze Modelul Relaţional aferent
bazei de date exemplu ştiind că între relaţii există următoarele legături:
Categorii – Produse 1:n
Produse – Facturi n:n
Agenţi – Facturi 1:n
Clienţi – Facturi 1:n
Clienţi – DocInc 1:n
Facturi – DocInc n:n
Să se adauge în model atributele pentru: cantitatea vândută, preţul de vânzare şi suma
încasată.
2. Să se stabilească tipurile de date pentru atributele modelului relaţional obţinut la punctul
1.