Toate datele (variabile si constante), care sunt stocate în baza de date Oracle sunt caracterizate prin tip si lungime. Aceasta caracterizare este definita ca tipul de date al obiectului.
Ce sunt tipurile de date?
Tabelele sunt dispozitive de stocare într-o baza de date relationala. Coloanele tabelului definesc caracteristicile datelor si în acelasi timp atribuie un nume datelor din coloana respectiva. 737c29h
Coloanele, precum si variabilele si constantele sunt caracterizate din tipul si lungimea datelor. Tipul datei este un set static de proprietati cu o valoare predefinita. Aceste proprietati fac ca serverul Oracle sa trateze în mod diferentiat tipurile de date diferite.
Oracle recunoaste tipuri de date interne si externe. Tipurile de date interne sunt cele care descriu coloanele tabelelor, clusterele si argumentele procedurilor. Precompilatorul Oracle recunoaste si alte tipuri de date care se gasesc în programe SQL încapsulate. Aceste'tipuri de date sunt numite tipuri de date externe si sunt asociate variabilelor gazda. Acest capitol este axat pe tipurile de date interne ale programului Oracle.
Tipurile interne de date ale programului Oracle sunt:
char
varchar2
varchar
number
date
raw
long raw
rowid
long
mlslabel
În acest capitol, sunt tratate toate aceste tipuri de date.
De ce se folosesc tipurile de date?
Utilizarea tipurilor de date în baze de date ofera urmatoarele avantaje:
Restrâng domeniul valorilor pe care le pot lua diverse entitati. De exemplu, o entitate de tip date nu poate lua valoarea JIM.
Permit conservarea spatiului de stocare prin adaptarea tipului la datele stocate.
Permit conversia automata a datelor în anumite conditii.
Cum se utilizeaza tipurile de date?
Urmatoarele paragrafe descriu fiecare dintre tipurile de date ale serverului Oracle care pot fi utilizate pentru definirea coloanelor, variabilelor si constantelor.
Tipul char este utilizat pentru a defini o coloana care contine siruri de caractere de lungime fixa. Lungimea acestor siruri este cuprinsa în domeniul 1-255. Acest tip de date stocheaza date alfanumerice. Atunci când utilizati tipul char, Oracle efectueaza urmatoarele operatii:
La definirea coloanei îi atribuie lungimea fixa specificata de dumneavoastra, iar la inserarea unei valori aceasta este ajustata pentru a o aduce la lungimea respectiva. 737c29h
Daca valoarea introdusa este mai scurta decât lungimea de definitie a coloanei, valorii îi sunt adaugate spatii.
Daca valoarea introdusa este mai lunga decât lungimea de definitie a coloanei, Oracle returneaza un cod de eroare.
Daca valoarea care depaseste lungimea coloanei poseda spatii la sfârsit, acestea sunt eliminate pentru a aduce data la lungimea fixata.
Iata un exemplu de definire a unei coloane de tip char:
|
Nr_Articol |
char (15) |
|
Exemplu: |
"TY33693X1 " |
Tipul varchar2 este utilizat pentru a defini o coloana care contine siruri de caractere de lungime variabila. Lungimea acestor caractere poate fi cuprinsa în domeniul 1-2,000 octeti. Pentru o coloana definita ca fiind de tip varchar2, fiecare data este stocata ca data de lungime variabila.
Atunci când utilizati tipul varchar2 pentru a defini o coloana, Oracle efectueaza urmatoarele operatii:
La definirea coloanei îi atribuie lungimea maxima specificata de dumneavoastra, iar la inserarea unei valori aceasta este de lungime variabila.
Daca valoarea introdusa este mai scurta decât lungimea de definitie a coloanei, nu sunt adaugate spatii la sfârsitul valorii. Acesta este un tip de date în care valorile nu sunt completate cu spatii.
Daca valoarea introdusa este mai lunga decât lungimea de definitie a coloanei, programul Oracle returneaza un cod de eroare.
Daca valoarea care depaseste lungimea coloanei poseda spatii la sfârsit, acestea sunt eliminate pentru a aduce data la lungimea maxima fixata.
în exemplul care urmeaza, coloana Nr_Articol este definita ca fiind de tip varchar2 si având o lungime maxima de 15 octeti:
|
Nr_Articol |
varchar2(15) |
|
exemplu: |
"TY33693X1" |
în versiunea Oracle 7.1, tipul varchar este sinonim cu tipul varchar2. In versiunile Oracle viitoare, s-ar putea ca datele de tip varchar sa stocheze siruri de caractere de lungime variabila comparate cu diverse expresii de comparatie. Este deci recomandabil sa nu folositi niciodata tipul varchar si sa folositi exclusiv tipul varchar2.
Iata un exemplu de definire a unei date de tip varchar:
|
Nr_Articol |
varchar(15) |
|
Exemplu: |
'TY33693X1' |
Datele de tip number stocheaza numere în virgula fixa si în virgula mobila.
Practic pot fi stocate numere oricât de mari. Tipul number permite definirea preciziei si a scalei. Precizia este egala cu numarul total de cifre ale numarului;
scala se refera la numarul de cifre din dreapta punctului zecimal. Aceste numere pot fi stocate cu o precizie de pâna la 38 de cifre.
Exemplul urmator ilustreaza definirea unei date de tip number:
|
Nr_Articol |
number |
|
Exemplu: |
sau
|
Nr_Articol |
number(6, 2) |
|
Exemplu: |
În acest exemplu, (6,2) reprezinta urmatoarele:
6 este precizia. Precizia este sinonima cu numarul total de cifre permise. Acest numar include numarul total de cifre din ambele parti ale punctului zecimal.
2 este scala, sau numarul de cifre din dreapta punctului zecimal.
Este recomandabil sa specificati întotdeauna precizia si scala datei, în acest fel se asigura verificarea integritatii datei atunci când aceasta este introdusa.
Tipul date stocheaza data calendaristica si ora în coloanele unui tabel cu precizie de o secunda. Acest tip de data stocheaza cele patru cifre ale anului, luna, ziua, ora, minutele si secundele scurse de la miezul noptii.
Pentru operatiile de intrare/iesire de date, formatul prestabilit este DD-MON-YY. Ora este stocata în format HH:MM:SS. Nu puteti specifica data în litere. Daca nu specificati componenta pentru ora într-o valoare de tip data, ora prestabilita este 12:00:00 AM. In mod similar, daca omiteti componenta corespunzatoare datei calendaristice a unei valori de tip data, data prestabilita este prima zi a lunii curente. Domeniul valid pentru date calendaristice este 1 Ianuarie 4712 Î.E.N - 1 Ianuarie 4712 E.N.
Programul Oracle ofera mai multe combinatii de litere care pot fi utilizate drept coduri de format care:
Convertesc o valoare de tip data care nu este în formatul data prestabilit.
Este în general cazul functiilor de tipul to_char. Convertesc o valoare de tip sir de caractere care este în alt format decât formatul data prestabilit. Este în general cazul functiei to_date.
Aceste coduri de format nu modifica reprezentarea interna a datei; modifica numai modul de afisare a acesteia. Urmeaza o lista de coduri de format care pot fi folosite în functiile to_date si to_char:
MM - Numarul Lunii.
MON - Prescurtarea De Trei Litere (Toate Majuscule) A Numelui Lunii.
Mon - Prescurtarea De Trei Litere (Prima Majuscula) A Numelui Lunii.
mon - Prescurtarea De Trei Litere (Toate Litere Mici) A Numelui Lunii.
RM - Luna Exprimata În Cifre Romane.
J - Data Dupa Calendarul Iulian. Reprezinta Numarul De Zile Scurse De La 1 Ianuarie 4712 Î.E.N.
DDD - Numarul De Zile Scurse De La 1 Ianuarie Anul Curent.
DD - Numarul Zilei Din Luna.
D - Numarul Zilei Saptamânii.
DY - Prescurtare Pe Trei Litere A Numelui Zilei.
Dy - Prescurtare Pe Trei Litere A Numelui Zilei Cu Prima Litera Majuscula.
DAY - Numele Complet Al Zilei Cu Toate Literele Majuscule.
Day - Numele Complet Al Zilei Saptamânii Cu Prima Litera Majuscula.
day - Numele Complet Al Zilei Cu Litere Mici.
CC - Secolul.
SCC - Secolul (Precedat De "-" Daca Se Refera La Un Secol Î.E.N.).
YYYY - Anul Scris Cu Toate Cele Patru Cifre.
SYYY - anul scris cu toate cele patru cifre si precedat de semnul "-" daca se refera la un an i.e.n.
IYYY - anul iso de patru cifre.
YYY - ultimele trei cifre ale anului.
IYY - ultimele trei cifre ale anului iso.
YY - ultimele doua cifre ale anului.
IY - ultimele doua cifre ale anului iso.
R - asemanator formatului yy, însa se foloseste pentru anii din alte secole. Acest format e explicat mai târziu în acest capitol.
Y - Ultima Cifra A Anului.
L - Ultima Cifra A Anului ISO.
Year - Anul Scris În Litere.
O - Trimestrul.
WW - Numarul saptamânii din anul curent.
W - Numarul saptamânii din luna.
IW - Numarul saptamânii în anul.
HH - Ora din zi în reprezentarea ceasului cu 12 ore.
HH24 - Ora din zi în reprezentarea ceasului cu 24 ore.
MI - Minutul din ora.
SS - Secunda minutului.
SSSSS - Secundele Scurse De La Miezul Noptii.
A.M. - Afiseaza A.M. În Functie De Ora Din Zi.
P.M. - Afiseaza P.M. În Functie De Ora Din Zi.
Bc - În Functie De An, Afiseaza B.C. Sau A.D.
În exemplele care urmeaza, vor fi folosite functiile to_date si to char în scopuri demonstrative. Aceste functii primesc ca argument un sir de caractere într-un anumit format si îl convertesc în formatul Oracle prestabilit sau în formatul specificat de utilizator.
Converteste data de intrare din formatul MM/DD/YY în formatul standard prestabilit:
select to_date ('04-21-95', MM/DD/YY') "Formatata" from dual;
Rezultat:
Format data
21-APR-95
Converteste data de angajare a salariatilor în format Iulian.
select to_char(data_angajare,'I') "Iulian" from ang;
Rezultat:
Iulian
14 rows selected.
Converteste data de intrare în formatul 29-Sept-86:
update ang
set data_angajare = to_date ('86 09 29','yy mm dd');
Formatul de data RR este similar formatului de data YY. Secolul returnat de functia to_date cu formatul de data YY corespunde întotdeauna anului curent. Formatul RR va permite sa stocati ani din alte secole. Programul Oracle determina secolul sirului de tip data care utilizeaza formatul RR prin evaluarea ultimelor doua cifre ale anului curent si a celor doua cifre (reprezentând anul) specificate, în figura 4. 1 este sintetizata comportarea formatului de data RR.
Exemplul care urmeaza foloseste formatul RR.
|
Figura 4.1 Comportamentul formatului de data RR. |
|
||||||||||||
select to_char (to_date ('03-OCT-21', 'DD-MON-RR'), 'YYYY')
"Anul formatat cu RR" from dual;
Rezultat:
Anul formatat cu RR
Atunci când prelucreaza date, functia to_char poate primi doi modificatori de format. Acestia sunt:
fm - Suprima blancurile de la sfârsit din valorile returnate de functia to_char
fx - Specifica faptul ca argumentul de tip sir de caractere trebuie sa aiba acelasi format ca modelul de format (al doilea argument) furnizat functiei to_date.
În acest prim exemplu, nu vor fi utilizati modificatori de format pentru formatarea datelor de iesire, în al doilea exemplu, va fi lansata aceeasi interogare, însa datele de iesire vor fi formatate cu modificatorul de format f m.
select data_angajare, to_char(data_angajare,'Month, dd, YYYY')
"Data NE-Formatata" from ang;
Rezultate în cazul neutilizarii modificatorilor de format:
DATA_ANGAJARE Data NE-Formatata
17-DEC-80 December, 17, 1980
20-FEB-81 February, 20, 1981
22-FEB-81 February, 22, 1981
02-APR-81 April , 02, 1981
În mod prestabilit, programul Oracle aliniaza câmpurile rezultatului, în exemplul urmator, vor fi folositi modificatori de format pentru a suprima blancurile de la sfârsit din câmpurile rezultatelor:
select data_angajare, to_char(data_angajare,'fmMonth, dd, YYYY')
"Data Formatata" from ang;
Rezultate în cazul neutilizarii modificatorilor de format:
DATA ANGAJARE Data Formatata
17-DEC-80 December,17,1980
20-FEB-81 February,20,1981
22-FEB-81 February,22,1981
02-APR-81 April,02,1981
Oracle va permite sa adaugati oricare dintre sufixurile urmatoare unui cod de formatare a datelor. Atunci când se utilizeaza un sufix, data returnata este întotdeauna în engleza.
TH - Adauga datei literele TH. De exemplu, puteti specifica formatul de data DDTH pentru a obtine valoarea returnata 4TH.
SP - Acest sufix duce la scrierea în litere a datei. De exemplu, pentru a obtine valoarea returnata FOUR, adaugati sufixul SP codului de format DD.
SPTH - Scrie numarul de ordine al datei în litere. De exemplu, atunci când codul de format este DDSPTH rezultatul este FOURTH.
În acest exemplu, va fi utilizat sufixul th împreuna cu modificatorul de format f m pentru a crea un rezultat atractiv:
select data_angajare, to_char(data_angajare,'fmMonth, ddth, YYYY')
"Data Formatata" from ang;
Rezultate în cazul neutilizarii modificatorilor de format:
DATA_ANGAJARE Data Formatata
17-DEC-80 December, 17th, 1980
20-FEB-81 February, 20th, 1981
22-FEB-81 February 22nd, 1981
02-APR-81 April, 2nd, 1981
Oracle permite efectuarea de operatii aritmetice cu date - cu alte cuvinte, adunarea sau scaderea a doua date. La evaluarea rezultatelor acestor operatii, Oracle tine seama de anomaliile istorice ale calendarului si ajusteaza rezultatul în consecinta. De exemplu, anul zero nu exista. De asemenea, sunt luati în calcul anii bisecti.
Oracle interpreteaza orice constanta numerica dintr-o instructiune care efectueaza operatii aritmetice cu date calendaristice ca reprezentând un numar de zile. De exemplu, în expresia care urmeaza, se citeste data curenta, la care se aduna o zi, respectiv se scade o zi din ea.
select sysdate, sysdate+1, sysdate-1 from dual; Rezultat:
SYSDATE SYSDATE+1 SYSDATE - 1
15-JUN-95 16-JUN-95 14-JUN-95
Numeroase operatii aritmetice cu date calendaristice dau un rezultat care contine o parte fractionara sau un rest. Acest lucru se datoreaza faptului ca datele calendaristice contin si o componenta timp. Partea fractionara a rezultatului reprezinta de obicei o parte a zilei.
Coloanele, variabilele si constantele de tip long stocheaza date caracter cu lungime variabila continând pâna la 2 gigaocteti de informatie. Desi tipul de date long este util în numeroase situatii, el prezinta urmatoarele limitari:
Intr-un tabel este permisa cel mult o coloana de tip long
Coloanele de tip long nu pot fi indexate
Coloanele de tip long nu pot aparea în restrictii de integritate
Nu se pot face referiri la coloane de tip long în functii SQL cum sunt substr, length si asa mai departe
Coloanele de tip long nu pot fi utilizate în clauzele where, group by, order by si connect by
în unitati de program PL/SQL nu pot aparea variabile sau argumente de tip long
Aceste tipuri de date sunt destinate datelor binare. Ele nu trebuie utilizate atunci când datele urmeaza sa fie interpretate de Oracle. Aceste tipuri de date sunt ideale pentru stocarea datelor multimedia sau a altor fisiere binare.
Tipul raw este similar cu tipul varchar2, iar long raw este similar cu tipul long. Singura deosebire este ca datele de tip raw nu fac obiectul operatiunilor de conversie la atribuire. Datele de tip raw pot fi indexate, în timp ce datele de tip long raw nu pot fi indexate.
Tipul de date rowid corespunde adresei fizice a unei linii dintr-un tabel Oracle care nu apartine unui cluster. El este un identificator unic al fiecarei linii. O coloana sau variabila de tip rowid utilizeaza o reprezentare binara a adresei fizice. Atunci când este rezultatul unei interogari în limbaj SQL*Plus sau în limbajul unui alt instrument Oracle, reprezentarea binara este convertita în reprezentare hexazecimala.
Fiecare tabel al unei baze de date Oracle poseda propria coloana rowid, pe care însa nu o veti vedea afisata atunci când executati o instructiune select * from sau describe. Cu toate acestea, adresa fiecarei linii poate fi citita prin intermediul unei interogari SQL în care este folosit cuvântul rezervat rowid drept nume de coloana. Exemplul urmator ilustreaza o instructiune SQL care utilizeaza tipul de date rowid, precum si valorile de iesire ale acesteia:
SELECT rowid, nume_ang FROM ang;
Rezultat:
Rowid NUME_ANG
000C0B7.0000.0001 'WHITE'
Aceasta adresa rowid se traduce în felul urmator:
000CB7 Acesta este blocul de date care contine linia. Numerele blocurilor sunt relative la fisierul de date de care apartin si nu la spatiul-tabel.
Acesta este numarul de secventa al liniei în bloc. Numarul primei linii este întotdeauna 0.
Acesta este numarul fisierului de date care contine linia.
Datele de tip rowid ofera urmatoarele avantaje:
Reprezinta cea mai rapida metoda de accesare a liniilor.
Pot fi folosite pentru a vedea modul în care este organizat un tabel.
Ele identifica în mod univoc datele dintr-un anumit tabel. Coloanele rowid sunt utilizate la construirea indecsilor.
Datele de tip rowid pot fi selectate sau utilizate într-o clauza where, dar nu pot fi înlocuite cu operatiile insert, update sau delete. Totusi, rowid se poate înlocui daca tabelul este exportat si importat.
Sistemul Trusted Oracle 7.x prevede un nou tip de date: tipul mlslabel. Trusted Oracle este produsul-sistem de administrare a bazelor de date cu nivele multiple de securitate. Acesta este proiectat pentru furnizarea nivelului înalt de securitate a administrarii datelor necesar organizatiilor care prelucreaza informatii sensibile sau secrete, în vederea asigurarii compatibilitatii între aplicatiile Oracle si Trusted Oracle, declararea coloanelor de tip mlslabel este permisa si în Oracle.
Tipul mlslabel este utilizat pentru stocarea formatului binar al unei etichete a sistemului de operare. Lungimea maxima pentru datele de acest tip este de 255 octeti, mlslabel este stocata sub forma unei etichete (tag) de lungime variabila; corespondenta dintre acest tag si eticheta sistemului de operare este pastrata în dictionarul de date. Motivul pentru care datele mslabel sunt stocate sub forma unei etichete reprezentative si nu sub forma etichetei binare propriu-zise este ca etichetele binare ale sistemului de operare pot avea lungimi foarte mari. întrucât pentru fiecare linie a bazei de date este stocata o asemenea eticheta, stocarea unui numar mare de etichete ar duce la un consum ridicat de spatiu de stocare.
în anumite situatii, programul Oracle converteste automat datele de la un tip la altul. Pe de alta parte, pentru a converti datele în mod explicit puteti folosi urmatoarele functii Oracle:
to_char()
to_date()
to_number()
to_label()
chartorowid()
rowidtochar()
Pentru atribuiri, programul Oracle efectueaza în mod implicit urmatoarele conversii de tip:
varchar2 sau char la number
number la varchar2
varchar2 sau char la date
date la varchar2
varchar2 sau char la rowid
rowid la varchar2
Conversia la atribuire reuseste daca Oracle poate converti tipul valorii care urmeaza sa fie atribuita la tipul destinatiei atribuirii.
Pentru evaluarea expresiilor, Oracle efectueaza în mod implicit urmatoarele conversii de tip:
varchar2 sau char la number
varchar2 sau char la date
Conversiile char la number reusesc numai daca rezultatul de tip sir de caractere reprezinta un numar valid. Conversiile char la date reusesc numai daca rezultatul de tip sir de caractere are formatul de data prestabilit' DD-MON-YY.
Null nu este un tip de data Oracle, însa este un concept important legat de valoarea datelor. Valoarile null pot aparea în coloanele tabelelor si în expresii SQL.
Null se defineste ca o valoare care este necunoscuta, fara sens sau irelevanta. Orice tip de data Oracle poate avea valoarea nuli. Daca într-o linie lipseste valoarea pentru o anumita coloana, valoarea acelei coloane este null. Valorile null pot aparea în orice coloana atât timp cât aceasta nu este supusa restrictiilor de integritate not null sau primary key.
Nu confundati valoarea zero cu null. Zero este un numar si are o semnificatie. O valoare null într-o coloana de tip number nu este acelasi lucru cu o valoare zero. O valoare de tip sir de caractere de lungime zero este considerata null.
Pentru a testa valorile null, se folosesc operatorii de comparatie is null si is not null. Deoarece null semnifica lipsa datelor, un null nu poate fi egal cu sau diferit de nici o valoare. Exemplul urmator ilustreaza o instructiune select care va selecta orice înregistrare în care valoarea coloanei sold nu este null.
SELECT nr_cont, nume_cont, sold
FROM conturi
WHERE sold is NOT null;
în acest capitol, ati facut cunostinta cu tipurile de date Oracle ca reprezentând caracteristicile datelor. Aceste caracteristici sunt un set predefinit de informatii care definesc felul si lungimea datei. Programul Oracle trateaza diferentiat datele de tipuri diferite.
Tipurile de date permit sistemului Oracle sa economiseasca spatiu de stocare prin adaptarea tipului datelor la datele care urmeaza a fi stocate, în plus, utilizarea tipurilor de date va permite sa restrângeti domeniul de valori pe care le poate contine o data, contribuind astfel la asigurarea integritatii datelor.
Ati învatat sa utilizati tipurile de date la definirea coloanelor, variabilelor si constantelor, în primul rând stabiliti tipul datelor pe care le vor contine, dupa care definiti lungimea acestora. Oracle efectueaza conversii de tip fie implicit, fie explicit. Aceste conversii sunt permise numai pentru anumite tipuri de date.
|
