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.