| CATEGORII DOCUMENTE |
Functiile Oracle sunt impartite astfel:
Functii singulare - acestea opereaza la un moment dat asupra unei singure inregistrari. Aceste functii vor fi discutate in acest capitol
Functiile de grup - opereaza asupra unui grup de inregristrari si returneaza o singura singura valoare pentru intregul grup.
Functiile singulare pot fi folosite in:
clauza SELECT, pentru a modifica modul de afisare a datelor, pentru a realiza diferite calcule etc.
clauza WHERE, pentru a preciza mai exact care sunt inregistrarile ce se afiseaza
clauza ORDER BY
Functiile singulare (single-row functions) pot fi la randul lor impartite in:
Functii care opereaza asupra sirurilor de caractere
Functii numerice
Functii pentru manipularea datelor calendaristice
Functii de conversie - care convertesc datele dintr-un tip in altul
Functii de uz general.
Unele functii, precum TRUNC si ROUND pot actiona asupra asupra mai multor tipuri de date, dar cu semnificatii diferite.
In cele ce urmeaza vom folosi tabela DUAL pentru a testa modul de operare a functiilor singulare.
Aceasta tabela este una speciala, care contine o singura coloana numita "DUMMY" si o singura linie (vezi figura II.4.1).
Tabela DUAL se foloseste atunci cand realizam calcule, sau evaluam expresii care nu deriva din nici o tabela anume.
Fie de exemplu comanda
SELECT (5*7-3)/2 FROM DUAL;
Expresia evaluata in aceasta comanda nu are in componenta nici o coloana a vreunei tabele, motiv pentru care este nevoie sa apelam la tabela DUAL.
Putem privi tabela DUAL ca pe o variabila in care memoram rezultatele calculelor noastre.
Sirurile de caractere pot contine orice combinatie de litere, numere, spatii, si alte simboluri, precum semne de punctuatie, sau caractere speciale. In Oracle exista doua tipuri de date pentru memorarea sirurilor de caractere:
CHAR - pentru memorarea sirurilor de caractere de lungime fixa
VARCHAR2 - pentru memorarea sirurilor de caractere avand lungime variabila.
LOWER(sir) - converteste caracterele alfanumerice din sir in litere mari.
UPPER(sir) - converteste caracterele alfanumerice din sir in litere mici.
INITCAP(sir) - converteste la majuscula prima litera din fiecare cuvant al sirului. Cuvintele sunt siruri de litere separate prin orice caracter diferit de
|
Exemplu |
Rezultatul afisat |
|
SELECT LOWER(ename) FROM emp; |
afiseaza prenumele persoanelor din tabela emp scrise cu litere mici |
|
SELECT LOWER('abc123ABC') FROM DUAL; |
abc123abc |
|
SELECT UPPER('abc123ABC') FROM DUAL; |
ABC123ABC |
|
SELECT INITCAP('aBc def*ghi') FROM dual; |
Abc Def*Ghi Explicatie sirul contine 3 cuvinte aBc def si ghi |
CONCAT(sir1, sir2) - concateneaza doua siruri de caractere
|
Exemplu |
Rezultatul afisat |
|
SELECT CONCAT('abc','def') FROM dual; |
abcdef Explicatie comanda poate fi transcrisa folosind operatorul de concatenare astfel: SELECT 'abc'||'def' FROM dual; |
SUBSTR(sir,poz,nr) - extrage din sir cel mult nr caractere incepand din pozitia poz.
Observatii
daca din pozitia poz pana la sfarsitul sirului sunt mai putin de nr caractere, se vor extrage toate caracterele de la pozitia poz pana la sfarsitul sirului.
parametrul poz poate fi si o valoare negativa, ceea ce inseamna ca pozitia de unde se va incepe extragerea caracterelor din sir se va determina numarand caracterele din sir de la stanga spre dreapta (vezi ultimele 3 exemple de mai jos)
daca nr nu este specificat, se va returna subsirul incepand cu caracterul de pe pozitia poz din sir pana la sfarsitul sirului.
|
Exemplu |
Rezultatul afisat |
|
select substr('abcdef',3,2) from dual |
cd |
|
select substr('abcdef',3,7) from dual; |
cdef Explicatie. Chiar daca din pozitia 3 pana la sfarsitul sirului nu mai sunt 7 caractere se returneaza caracterele ramase |
|
select substr('abcdef',3) from dual; |
cdef Explicatie. Acelasi rezultat ca mai sus daca nu se specifica numarul de caractere ce se extrag |
|
select substr('abcdef',7,3) from dual; |
nu se va afisa nimic deoarece nu exista pozitia 7 in sir, acesta avand 5 caractere. |
|
select substr('abcdef',-4,2) from dual; |
cd Explicatie. Se extrag doua caractere incepand cu al patrulea caracter din dreapta. |
|
select substr('abcdef',-4,7) from dual; |
cdef |
|
select substr('abcdef',-10,3) from dual; |
nu se va afisa nimic deoarece sirul contine mai putin de 10 caractere |
INSTR(sir,subsir,poz,k) - returneaza pozitia de inceput a celei de a k-a aparitii a subsirului subsir in sirul sir, cautarea facandu-se incepand cu pozitia poz .
Daca parametrii poz si k lipsesc, atunci se va returna pozitia primei aparitii a subsirului subsir in intregul sir sir.
Pozitia de unde incepe cautarea poate fi precizata si relativ la sfarsitul sirului, ca si in cazul functiei substr, daca parametrul poz are o valoare negativa.
|
Exemplu |
Rezultatul afisat |
|
select instr('abcdabcdabc','cd') from dual; | |
|
select instr('abcd','ef') from dual | |
|
select instr('abcd','bce') from dual; | |
|
select instr('ababababababab','ab',4,2) from dual; |
Explicatie. Se incepe cautarea din pozitia a patra, adica in zona subliniata cu o linie, si se afiseaza pozitia de start a celei de a doua aparitii, (subsirul subliniat cu linie dubla) |
|
select instr('abababababab','ab',-4,1) from dual; |
LENGTH(sir) - returneaza numarul de caractere din sirul sir.
|
Exemplu |
Rezultatul afisat |
|
select length('abcd') from dual |
LPAD(sir1,nr,sir2) - completeaza sirul sir1 la stanga cu caracterele din sirul sir2 pana ce sirul obtinut va avea lungimea nr.
Daca lungimea sirului sir1 este mai mare decat nr, atunci functia va realiza trunchierea sirului sir1, stergandu-se caracterele de la sfarsitul sirului.
|
Exemplu |
Rezultatul afisat |
|
select lpad('abcd',3,'*') from dual |
abc |
|
select lpad('abcd',10,'*.') from dual |
*.*.*.abcd |
|
select lpad('abc',10,'*.') from dual |
*.*.*.*abc |
|
select lpad('abc',5,'xyzw') from dual |
xyabc |
RPAD(sir,nr,subsir) - similara cu functia LPAD, completarea facandu-se la dreapta.
|
Exemplu |
Rezultatul afisat |
|
select rpad('abcd',3,'*') from dual |
abc |
|
select rpad('abcd',10,'*.') from dual |
abcd*.*.*. |
|
select rpad('abc',10,'*.') from dual |
abc*.*.*.* |
|
select rpad('abc',5,'xyzw') from dual |
abcxy |
functia TRIM sterge caracterele ch de la inceputul, sfarsitul sau din ambele parti ale sirului sir.
in ultimele doua formate ale functiei este subinteleasa optiunea BOTH.
daca ch nu este specificat se vor elimina spatiile inutile de la inceputul, sfarsitul sau din ambele parti ale sirului sir.
|
Exemplu |
Rezultatul afisat |
|
select trim(leading 'a' from 'aaxaxaa') from dual |
xaxaa |
|
select trim(trailing 'a' from 'aaxaxaa') from dual |
aaxax |
|
select trim(both 'a' from 'aaxaxaa') from dual |
xax |
|
select trim('a' from 'aaxaxaa') from dual |
xax |
|
select '*'||trim(' abc ')||'*' from dual |
*abc* |
REPLACE(sir,subsir,sirnou) - inlocuieste toate aparitiile subsirului subsir din sirul sir cu sirul sirnou. Daca nu este specificat noul sir, toate aparitiile subsirului subsir se vor elimina.
|
Exemplu |
Rezultatul afisat |
|
select replace('abracadabra','ab','xy') from dual |
xyracadxyra |
|
select replace('abracadabra','ab','xyz') from dual |
xyzracadxyzra |
|
select replace('abracadabra','ab') from dual |
brcdbr |
Evident intr-o expresie pot fi folosite doua sau mai multe astfel de functii, imbricate. In tabelul urmator se pot urmari cateva exemple interesante de acest fel.
select
substr('abcabcabc',1,instr('abcabcabc','bc')-1) || 'xyz'
|| substr('abcabcabc',instr('abcabcabc','bc')+length('bc'))
from dual
TRIM(LEADING ch FROM sir),
TRIM(TRAILING ch FROM sir)
TRIM(BOTH ch FROM sir)
TRIM(sir)
Aceste functii opereaza asupra valorilor numerice si returneaza un rezultat numeric. Functiile numerice oferite de Oracle sunt destul de puternice.
ABS(n) - returneaza valoarea absoluta a argumentului.
|
Exemplu |
Rezultatul afisat |
|
select abs(-5.23) from dual | |
|
select abs(5) from dual |
ABS(n) - returneaza valoarea absoluta a argumentului.
|
Exemplu |
Rezultatul afisat |
|
select abs(-5.23) from dual | |
|
select abs(5) from dual |
SIN(n), SIN(n), TAN(n) - sunt functiile trigonometrice cu aceeasi semnificatie ca si la matematica. Argumentul este acestor functii trebuie precizat in radiani.
|
Exemplu |
Rezultatul afisat |
|
select sin(3.1415/2) from dual | |
|
select sin(3.1415/2) from dual |
ACOS(n), ASIN(n), ATAN(n) - sunt functiile trigonometrice inverse, cu semnificatia din matematica. Valoarea returnata de aceste functii este exprimata in radiani.
POWER(m,n) - calculeaza valoarea 
.
|
Exemplu |
Rezultatul afisat |
|
select power(2,3) from dual; | |
|
select power(2,0.5) from dual; | |
|
select power(2,-1) from dual | |
|
select power(2,-0.75) from dual; |
SQRT(x) - calculeaza radacina patrata a argumentului. Apelul SQRT(x) returneaza aceeasi valoare ca si POWER(x,0.5).
|
Exemplu |
Rezultatul afisat |
|
select sqrt(3) from dual; |
REMAINDER(x,y) - in cazul in care ambii parametrii x si y sunt numere intregi, functia calculeaza restul impartirii lui x la y. Daca cel putin unul dintre parametrii este numar real, functia determina mai intai acel multiplu a lui y care este cel mai apropiat de x, si returneaza apoi diferenta dintre x si y.
|
Exemplu |
Rezultatul afisat |
|
select remainder(10,3) from dual; |
Explicatie. Cel mai apropiat de 10 multiplu a lui 3 este 9. 10-9=1. |
|
select remainder(5,3) from dual |
Explicatie. Cel mai apropiat de 5 multiplu a lui 3 este 6, iar 5-6=-1. |
|
select remainder(10,3.5) from dual |
Explicatie. Cel mai apropiat de 10 multiplu a lui 3.5 este 10.5, iar 10-10.5=-0.5. |
|
select remainder(-10,3.5) from dual |
Explicatie. Cel mai apropiat de -10 multiplu a lui 3.5 este -10.5, iar |
MOD(x,y) - daca cei doi parametrii sunt numere intregi, atunci functia returneaza acelasi rezultat ca si functia REMAINDER, adica restul impartirii lui x la y. Teorema impartirii cu rest este extinsa de aceasta functie si pentru numerele reale. Adica se tine cont de relatia
x=y * cat + rest
unde restul trebuie sa fie in modul strict mai mic decat y.
|
Exemplu |
Rezultatul afisat |
|
select mod(10,3) from dual; |
Explicatie. |
|
select mod(5,3) from dual; |
Explicatie. |
|
select mod(10,3.5) from dual; |
Explicatie. |
|
select mod(-10,3.5) from dual |
Explicatie. |
|
select mod(-10,-3.5) from dual; |
Explicatie. |
|
select mod(10,-3.5) from dual; |
Explicatie. |
Se observa din exemplele anterioare ca restul are intotdeauna acelasi semn cu primul parametru.
CEIL(x) - returneaza cel mai mic numar intreg care este mai mare sau egal decat parametrul transmis.
FLOOR(x) - returneaza cel mai mare numar intreg care este mai mic sau egal decat parametrul transmis.
|
Exemplu |
Rezultatul afisat |
|
select ceil(3) from dual; | |
|
select ceil(-3) from dual; | |
|
select ceil(-3.7) from dual; | |
|
select ceil(3.7) from dual; | |
|
select floor(3) from dual; | |
|
select floor(-3) from dual; | |
|
select floor(-3.7) from dual; | |
|
select floor(3.7) from dual; |
SIGN(x) - returneaza semnul lui x, adica 1 daca x este numar pozitiv, respectiv -1 daca x este numar negativ.
ROUND(x,y) - rotunjeste valoarea lui x la un numar de cifre precizat prin parametrul y.
Daca al doilea parametru este un numar pozitiv, atunci se vor pastra din x primele y zecimale, ultima dintre aceste cifre fiind rotunjita, in functie de de urmatoarea zecimala.
Al doilea argument poate fi o valoare negativa, rotunjirea facandu-se la stanga punctului zecimal. Cifra a |y|+1 din fata punctului zecimal (numarand de la punctul zecimal spre stanga incepand cu 1) va fi rotunjita in functie cifra aflata imediat la dreapta ei. Primele |y| cifre din stanga punctului zecimal vor deveni 0.
Cel de al doilea argument este optional, in cazul in care nu se precizeaza, este considerata implicit valoarea 0.
|
Exemplu |
Rezultatul afisat |
|
select round(745.123,2) from dual; | |
|
select round(745.126,2) from dual; | |
|
select round(745.126,-1) from dual; | |
|
select round(745.126,-2) from dual; | |
|
select round(745.126,-3) from dual; | |
|
select round(745.126,-4) from dual; | |
|
select round(745.126,0) from dual; | |
|
select round(745.826,0) from dual; | |
|
select round(745.826) from dual; |
TRUNC(x) - este asemanatoare cu functia ROUND, fara a rotunji ultima cifra.
|
Exemplu |
Rezultatul afisat |
|
select trunc(745.123,2) from dual; | |
|
select trunc(745.126,2) from dual; | |
|
select trunc(745.126,-1) from dual; | |
|
select trunc(745.126,-2) from dual; | |
|
select trunc(745.126,-3) from dual; | |
|
select trunc(745.126,-4) from dual; | |
|
select trunc(745.126,0) from dual; | |
|
select trunc(745.826,0) from dual; | |
|
select trunc(745.826) from dual; |
Una dintre caracteristicile importante ale Oracle este abilitatea de a memora si opera cu date calendaristice. Tipurile de date calendaristice recunoscute de Oracle sunt:
DATE - valorile avand acest tip sunt memorate intr-un format intern specific, care include pe langa ziua, luna si anul, de asemenea ora, minutul, si secunda.
TIMESTAMP - valorile avand acest tip memoreaza data calendaristica, ora, minutul si secunda dar si fractiunea de secunda.
TIMESTAMP WITH [LOCAL] TIME ZONE - este similar cu TIMESTAMP, insa se va memora si diferenta de fus orar fata de ora universala, a orei de pe serverul bazei de date, sau a aplicatiei client, in cazul in care se include optiunea LOCAL.
INTERVAL YEAR TO MONTH - memoreaza o perioada de timp in ani si luni.
INTERVAL DAY TO SECOND - memoreaza un interval de timp in zile, ore, minute si secunde.
Oracle ofera un numar foarte mare de functii care opereaza asupra datelor calendaristice, dar in cele ce urmeaza ne vom opri asupra celor mai importante dintre acestea.
SYSDATE - returneaza data si ora curenta a serverului bazei de date.
CURRENT_DATE - returneaza data si ora curenta a aplicatiei client. Aceasta poate sa difere de data bazei de date.
SYSTIMESTAMP - returneaza data in formatul TIMESTAMP.
ADD_MONTHS(data,nrluni) - adauga un numar de luni la data curenta. Daca al doilea parametru este un numar negativ, se realizeaza de fapt scaderea unui numar de luni din data precizata.
|
Exemplu |
Rezultatul afisat |
|
select sysdate, ADD_MONTHS(sysdate,2) from dual; |
27-FEB-07 27-APR-07 |
|
select sysdate, ADD_MONTHS(sysdate,-2) from dual; |
27-FEB-07 27-DEC-07 |
MONTHS_BETWEEN(data1,data2) - determina numarul de luni dintre doua date calendaristice precizate. Rezultatul returnat poate fi un numar real (vezi figura II.4.8). Daca prima data este mai mica (o data mai veche) atunci rezultatul va un numar negativ.
LEAST(data1,data2,.) - determina cea mai veche (cea mai mica) data dintre cele transmise ca parametru.
GREATEST(data1,data2,.) - determina cea mai recenta (cea mai mare) data dintre cele transmise ca parametru.
NEXT_DAY(data, 'ziua') - returneaza urmatoarea data de 'ziua' de dupa data transmisa ca parametru, unde 'ziua' poate fi 'Monday', 'Tuesday' etc. in exemplele care urmeaza data curenta este considerata ziua de marti, 27 februarie 2007.
|
Exemplu |
Rezultatul afisat |
|
select next_day(sysdate,'Friday') from dual; |
02-MAR-07 |
|
select next_day(sysdate,'TUESDAY') from dual; |
06-MAR-07 Explicatie. Chiar daca ziua curenta este o zi de marti, functia va returna urmatoarea zi de marti. |
LAST_DAY(data) - returneaza ultima zi din luna din care face parte data transmisa ca parametru.
|
Exemplu |
Rezultatul afisat |
|
select last_day(sysdate) from dual; |
28-FEB-07 |
|
select last_day(sysdate+20) from dual; |
31-MAR-07 |
|
select last_day(ADD_MONTHS(sysdate,12)) from dual; |
29-FEB-07 Explicatie. Ziua returnata de sysdate este 27-FEB-07, la care adaugam 12 luni, deci obtinem data de 27-FEB-08, iar anul 2008 este un an bisect de aceea ultima zi din luna este 29-FEB-08. |
ROUND(data,'format') - daca nu se precizeaza formatul, functia rotunjeste data transmisa ca parametru la cel mai apropiat ora 12 AM, adica daca ora memorata in data este inainte de miezul zilei atunci se va returna ora 12 AM a datei transmise. Daca ora memorata in data este dupa miezul zilei se va returna ora 12 AM a zilei urmatoare.
y, yy, yyyy, year - se rotunjeste data la cea mai apropiata data de 1 Ianuarie. Daca data este inainte de 1 iulie, se va returna data de 1 ianuarie a aceluiasi an. Daca data este dupa data de 1 iulie se va returna data de 1 ianuarie a anului urmator.
mm, month - rotunjeste data la cel mai apropiat inceput de luna. Orice data calendaristica aflata dupa data de 16, inclusive, este rotunjita la prima zi a lunii urmatoare.
ww, week - se rotunjeste data la cel mai apropiat inceput de saptamana, Prima zi a saptamanii este considerata lunea. Pentru datele aflate dupa ziua de joi, inclusive, se va returna ziua de luni a saptamanii urmatoare.
|
Exemplu |
Rezultatul afisat |
|
select sysdate, round(sysdate+3,'year'), round(ADD_MONTHS(sysdate,5),'year') from dual; |
27-FEB-07 01-JAN-07 01-JAN-08 |
|
select sysdate, round(sysdate,'mm'), round(sysdate+16,'mm'), round(sysdate+17,'mm') from dual; |
27-FEB-07 01-MAR-07 01-MAR-07 01-APR-07 |
|
select sysdate, round(sysdate,'ww'), round(sysdate+1,'ww'), round(sysdate+2,'ww') from dual; |
27-FEB-07 26-FEB-07 26-FEB-07 05-FEB-07 |
TRUNC(data,'format') - truncheaza data specificata conform formatului specificat. Se pot folosi aceleasi formate ca si in cazul functiei ROUND.
|
Exemplu |
Rezultatul afisat |
|
select sysdate, trunc(sysdate+3,'year'), trunc(ADD_MONTHS(sysdate,5),'year') from dual; |
27-FEB-07 01-JAN-07 01-JAN-07 |
|
select sysdate, trunc(sysdate,'month'), trunc(sysdate+16,'month'), trunc(sysdate+17,'month') from dual; |
27-FEB-07 01-FEB-07 01-MAR-07 01-MAR-07 |
|
select sysdate, trunc(sysdate,'ww'), trunc(sysdate+1,'ww'), trunc(sysdate+2,'ww') from dual; |
27-FEB-07 26-FEB-07 26-FEB-07 26-FEB-07 |
Oracle ofera un set bogat de functii care va permit sa transformati o valoare dintr-un tip de data in altul.
Transformarea unei date calendaristice in sir de caractere se poate realiza cu ajutorul functiei TO_CHAR. Aceasta operatie se poate dovedi utila atunci cand dorim obtinerea unor rapoarte cu un format precis. Sintaxa acestei functii este:
TO_CHAR (dt, format)
dt poate avea unul din tipurile pentru date calendatistice (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL MONTH TO YEAR, or INTERVAL DAY TO SECOND). Formatul poate contine mai multi parametrii care pot afecta modul in care va arata sirul returnat. Cativa din acesti parametrii sunt prezentati in continuare.
|
Aspect |
Parametru |
Descriere |
Examplu |
|
Secolul |
CC |
Secolul cu doua cifre | |
|
Trimestrul |
Q |
Trimestrul din an in care se gaseste data | |
|
Anul |
YYYY, RRRR |
Anul cu patru cifre. | |
|
YY, RR |
Ultimele doua cifre din an. | ||
|
Y |
Ultima cifra din an | ||
|
YEAR, Year |
Numele anului |
TWO THOUSAND-SIX, Two Thousand-Six |
|
|
Luna |
MM |
Luna cu doua cifre | |
|
MONTH, Month |
Numele complet al lunii. |
JANUARY January |
|
|
MON, Mon |
Primele trei litere ale denumirii lunii. |
JAN, Jan |
|
|
RM |
Luna scrisa cu cifre romane. |
IV |
|
|
Saptamana |
WW |
Numarul saptamanii din an. | |
|
W |
Ultima cifra a numarului saptamanii din an. | ||
|
Ziua |
DDD |
Numarul zilei din cadrul anului. | |
|
DD |
Numarul zilei in cadrul lunii | ||
|
D |
Numarul zilei in cadrul saptamanii. | ||
|
DAY, Day |
Numele complet al zilei din saptamana |
SATURDAY, Saturday |
|
|
DY, Dy |
Prescurtarea denumirii zilei din saptamana. |
SAT, Sat |
|
|
Ora |
HH24 |
Ora in formatul cu 24 de ore. | |
|
HH |
Ora in formatul cu 12 ore. | ||
|
Minutele |
MI |
Minutele cu doua cifre | |
|
Secundele |
SS |
Secundele cu doua cifre | |
|
Sufixe |
AM sau PM |
AM sau PM dupa cum e cazul. |
AM |
|
A.M. sau P.M. |
A.M. sau P.M. dupa cum e cazul. |
P.M. |
|
|
TH |
Sufix pentru numerale (th sau nd sau st) | ||
|
SP |
Numerele sunt scrise in cuvinte. |
In cadrul separatorilor, se pot folosi oricare dintre urmatorii separatori
Daca in sirul returnat dorim sa includem si anumite texte acestea se vor include intre ghilimele.
Iata in continuare si cateva exemple de folosire a acestei functii.
|
Exemplu |
Rezultatul afisat |
|
select sysdate, to_char(sysdate,'MONTH DD, YYYY') to_char(sysdate,'Month DD, YYYY') to_char(sysdate,'Mon DD, YYYY') from dual; |
28-FEB-07 FEBRUARY 28, 2007 February 28, 2007 Feb 28, 2007 |
|
select to_char(sysdate,''Trimestrul 'Q 'al anului ' Year') from dual; |
Trimestrul 1 al anului Two Thousand Seven |
|
select to_char(sysdate,''Secolul 'CC') from dual; |
Secolul 21 |
|
select to_char(sysdate,'Day, dd.RM.YYYY') from dual |
Wednesday, 28.II.2007 |
|
select to_char(sysdate,'Dy, D, DD, DDD') from dual; |
Wed, 4, 28, 059 |
|
select to_char(sysdate,'HH24:MI/HH:MI AM') from dual; |
21:53/09:53 PM |
|
select to_char(sysdate+1,'ddth') from dual; |
01st |
|
select to_char(sysdate+1,'ddspth') from dual; |
first |
|
select to_char(sysdate+2,'Ddspth') from dual; |
Second |
|
select to_char(sysdate+10,'DDspth') from dual |
TENTH |
|
select to_char(sysdate,'mmsp') from dual; |
two |
Folosind functia TO_DATE se poate transforma un sir de caractere precum 'May 26, 2006' intr-o data calendaristica. Sintaxa functiei este:
TO_DATE(sir,format)
Formatul nu este obligatoriu, insa daca nu este precizat, sirul trebuie sa respecte formatul implicit al datei calendaristice DD-MON-YYYY sau DD-MON-YY. Formatul poate folosi aceiasi parametrii de format ca si functia TO_CHAR.
|
Exemplu |
Rezultatul afisat |
|
select to_date('7.4.07', 'MM.DD.YY') from dual; |
04-JUL-07 |
|
select to_date('010101','ddmmyy') from dual; |
01-JAN-01 |
Asa cum s-a precizat anterior in formatarea unei date calendaristice se pot folosi pentru an atat YY (respectiv YYYY) cat si RR (respectiv RRR). Diferenta dintre aceste doua formate este modul in care ele interpreteaza anii apartinand de secole diferite. Oracle memoreaza toate cele patru cifre ale unui an, dar daca sunt transmise doar doua din aceste cifre, Oracle va interpreta secolul diferit in cazul celor doua formate.
Daca utilizati formatul YY si anul este specificat doar prin doua cifre, se presupune ca anul respectiv face parte din acelasi secol cu anul curent. De exemplu, daca anul transmis este 15 iar anul curent este 2007, atunci anul transmis este interpretat cu 2015. De asemenea 75 interpretat ca 2075.
Daca folositi formatul RR si anul transmis este de doua cifre, primele doua cifre ale anului transmis este determinat in functie de cele doua cifre transmise si de ultimele doua cifre ale anului curent. Regulile dupa care se determina secolul datei transmise sunt urmatoarele:
Regula 1: Daca anul transmis este intre 00 si 49, si ultimele doua cifre ale anului curent sunt intre 00 si 49 atunci secolul este acelasi cu secolul anului curent. De exemplu daca anul transmis este 15 iar anul curent este 2007, anul transmis este interpretat ca fiind 2015.
Regula 2 Daca anul transmis este intre 50 si 99 iar anul curent este intre 00 si 49 atunci secolul este secolul prezent minus 1. De exemplu daca transmiteti 75 iar anul curent este 2007, anul transmis este interpretat ca fiind 1975.
Regula 3: Daca data transmisa este intre 00 and 49 iar anul prezent este intre 50 si 99, secolul este considerat secolul prezent plus 1. De exemplu daca ati transmis anul 15 iar anul curent este 2087, anul transmis este considerat ca fiind anul 2115.
Regula 4 Daca anul transmis este intre 50 si 99, iar anul curent este intre 50 si 99, secolul este acelasi cu a anului curent. De exemplu, daca transmiteti anul 55 iar anul prezent ar fi 2087, atunci anul transmis este considerat ca fiind anul 2155.
Pentru a transforma un numar intr-un sir de caractere, se foloseste functia TO_CHAR, cu urmatoarea sintaxa
TO_CHAR(numar,format)
Formatul poate contine unul sau mai multi parametrii de formatare dintre cei prezentati in tabelul urmator.
|
Parametru |
Exemplu de format |
Descriere |
|||
|
Returneaza cifrele numarului din pozitiile specificate cu precedat de semnul minus daca numarul este negativ |
|||||
|
Completeaza cifrele numarului cu zerouri in fata |
|||||
|
specifica pozitia punctului zecimal |
|||||
|
Specifica pozitia separatorului virgula |
|||||
|
Afiseaza semnul dolar |
|||||
|
EEEE |
9.99EEEE |
Returneaza scrierea stiintifica a numarului. |
|||
|
EEEE |
9.99EEEE |
Returneaza scrierea stiintifica a numarului. |
|
||
|
L |
L999 |
Afiseaza simbolul monetar. |
|
||
|
MI |
999MI |
Afiseaza semnul minus dupa numar daca acesta este negativ. |
|
||
|
PR |
999PR |
Numerele negative sunt inchise intre paranteze unghiulare. |
|
||
|
RN |
RN |
Afiseaza numarul in cifre romane.. |
|
||
|
V |
99V99 |
Afiseaza numarul inmultit cu 10 la puterea x, si rotunjit la ultima cifra, unde x este numarul de cifre 9 de dupa V. |
|
||
|
X |
XXXX |
Afiseaza numarul in baza 16.. |
|
||
Vom exemplifica in continuare cateva dintre aceste formate.
|
Exemplu |
Rezultatul afisat |
|
select to_char(123.45,'9999.99') from dual; |
123.45 |
|
select to_char(123.45,'0000.000') from dual; |
0123.450 |
|
select to_char(123.45,'9.99EEEE') from dual; |
1.23E+02 |
|
select to_char(-123.45,'999.999PR') from dual; |
<123.450> |
|
select to_char(1.2373,'99999V99') from dual; |
124 |
|
select to_char(1.2373,'L0000.000') from dual |
$0001.237 |
|
select to_char(4987,'XXXXXX') from dual; |
137B |
|
select to_char(498,'RN') from dual; |
CDXCVIII |
Transformarea din inversa din sir de caractere intr-o valoare numerica se realizeaza cu ajutorul functiei TO_NUMBER:
TO_NUMBER(sir,format)
Parametrii de formatare ce se pot folosi sunt aceeasi ca in cazul functiei TO_CHAR. Iata cateva exemple.
|
Exemplu |
Rezultatul afisat |
|
select to_number('970.13') + 25.5 FROM dual; |
995.63 |
|
select to_number('-$12,345.67','$99,999.99') from dual; |
-12345.67 |
Pe langa functiile care controleaza modul de formatare sau conversie a datelor, Oracle ofera cateva functii de uz general, care specifica modul in care sunt tratate valorile NULL.
NVL(val1,val2) - functia returneaza valoarea val1, daca aceasta este nenula, iar daca val1 este NULL atunci va returna valoarea val2. Functia NVL poate lucra cu date de tip caracter, numeric sau data calendaristica, insa este obligatoriu ca cele doua valori sa aiba acelasi tip.
NVL2(val1,val2,val3) - daca valoarea val1 nu este nula atunci functia va returna valoarea val2, iar daca val1 are valoarea NULL atunci functia va returna valoarea val3 (vezi figura II.4.15.).
NULLIF(expr1,expr2) - daca cele doua expresii sunt egale, functia returneaza NULL. Daca valorile celor doua expresii sunt diferite atunci functia va returna valoarea primei expresii (vezi figura II.4.16.).
COALESCE(expr1, expr2, , exprn) - functia returneaza valoarea primei expresii nenule
Oracle SQL ofera posibilitatea de a construi expresii alternative asemanatoare structurilor IF-THEN-ELSE imbricate prezente in alte limbaje.
DECODE(expresie, val11, val12, val21, val22, , valn1, valn2, val) aceasta compara valoarea expresiei cu valorile val11 val21 valn1. Daca valoarea expresiei este egala cu valoarea vali1, atunci functia va returna valoarea vali2. Daca functia nu este egala cu nici una din valorile vali1, atunci functia va returna valoarea val
select DECODE('Maria' ,'Dana', 'Ea este Ana' ,
'Maria','Ea este Maria' ,
'Nu e nici Ana nici Maria')
from dual;
aceasta comanda va afisa mesajul "Ea este Maria" insa urmatoarea comanda va afisa "Nu e nici Ana nici Maria".
select DECODE('Valeria' ,'Dana', 'Ea este Ana' ,
'Maria','Ea este Maria' ,
'Nu e nici Ana nici Maria')
from dual;
In locul functiei DECODE se poate folosi expresia conditionala CASE. Functia CASE utilizeaza cuvintele cheia when, then, else, si end pentru a indica ramura selectata. In general orice apel al functiei DECODE poate fi transcris folosind functia CASE. Chiar daca o expresie folosind CASE este mai lunga decat expresia echivalenta care foloseste functia DECODE, varianta cu CASE este mult mai usor de citit si greselile sunt depistate mai usor. In plus varianta CASE este compatibila ANSI-SQL.
desc emp;
select NVL2(COMM,length(ename),length(job)) from emp;
desc emp;
select NVL(COMM,0.8) from emp;
select TO_CHAR(TO_DATE('04-JUL-15','DD-MON-RR'),'DD-MON-YYYY') AS D1,
TO_CHAR(TO_DATE('04-JUL-75','DD-MON-RR'),'DD-MON-YYYY') AS D2
from DUAL;
select TO_CHAR(TO_DATE('15','YY'),'YYYY') AS D1,
TO_CHAR(TO_DATE('75','YY'),'YYYY') AS D2
from DUAL;
select TO_CHAR(TO_DATE('04-JUL-15','DD-MON-YY'),'DD-MON-YYYY') AS D1,
TO_CHAR(TO_DATE('04-JUL-15','DD-MON-RR'),'DD-MON-RRRR') AS D2
from DUAL;
select TO_CHAR(SYSDATE,'DD-MON-YYYY hh:mi AM'),round(sysdate) from DUAL;
select SYSDATE,greatest(hiredate,sysdate) from emp;
desc emp;
select SYSDATE,LEAST(hiredate,sysdate) from emp;
select SYSDATE,months_between(sysdate,hiredate) from emp;
select sysdate-TO_DATE('26-FEB-07','dd-MON-yy') from dual;
select sysdate-hiredate from emp;
select sysdate-70 from emp;
SELECT SUBSTR('ORACLE',2,4),SUBSTR(DNAME,2),
SUBSTR(DNAME,3,5)
FROM DEPT;
à
SUBS SUBSTR(DNAME,2) SUBSTR(DNAME,3,
RACL CCOUNTING COUNT
RACL ESEARCH SEARC
RACL ALES LES
RACL PERATIONS ERATI
SOUNDEX(col/value)
intoarce un sir de caractere reprezentind pronuntia(sunetul) cuvintului pentru fiecare coloana sau valoare literala.Aceasta functie intoarce o reprezentare fonetica a fiecarui cuvint si ne permite sa comparam cuvinte care sunt scrise diferit, dar se pronunta(suna) la fel.
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 2246
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
