3. Functii pe un singur rand

Functii SQL

Functiile SQL returneaza intotdeauna o valoare si sunt folosite pentru:

efectuarea calculelor cu date
modificarea datelor
gestionarea iesirilor pentru grupuri de randuri
convertirea coloanelor de date
afisarea datelor si numerelor

Functiile in SQL sunt de doua tipuri: functii pe un sigur rand si functii pe mai multe randuri.

Functii pe un sigur rand

Functiile pe un sigur rand realizeaza operatii asupra unui singur rand si returneaza un singur rezultat pentru fiecare rand. Sunt mai multe tipuri de functii pe un singur rand: caracter, numerice, de date, de conversie.
function_name [(arg1,arg2,...)]
In sintaxa generala function_name este numele functiei si arg1,arg2 sunt argumentele functiei care pot fi date de numele unei coloane sau de o expresie.
Functiile pe un sigur rand cuprind urmatoarele tipuri de functii:

functii de tip caracter
functii de tip numeric
functii de tip data
functii de conversie
functii generale: NVL, NVL2, NULLIF, COALSECE, CASE, DECODE

Functiile de tip caracter

Functiile pe un singur rand de tip caracter aceepta ca date de intrare date de tip caracter si returneaza atat date de tip caracter, cat si numerice.

Functie	Descriere
LOWER(column\|expression)	converteste alfa caracterele din caractere mari in caractere mici
UPPER(column\|expression)	converteste alfa caracterele din caractere mici in caractere mari
INITCAP(column\|expression)	converteste prima litera a fiecarui cuvant in caractere mari si restul cuvantului in caractere mici
CONCAT(column1\|expression1, column2\|expression2)	functia este echivalentul operatorului de concantenare (\|\|)
SUBSTR(column\|expression, m [, n])	returneaza un sir de n caractere incepand cu caracterul aflat pe pozitia m
LENGHT(column\|expression)	returneaza numarul de caractere dintr-o expresie
INSTR(column\|expression, 'string', [m], [n])	returneaza pozitia unui anumit sir, optional se poate incepe cautarea cu pozitia m sau cu a n-a aparitie a sirului. m si n sunt prin definitie 1
REPLACE(text, search_string, replacement_ string)	cauta un anumit text intr-un sir de caractere si daca il gaseste il inlocuieste

Exemple:

Clauza WHERE a acestei cereri SQL specfica ca numele angajatului este 'adams'. Deoarece toate numele din tabela EMP sunt scrise cu majuscule, numele 'adams' nu este gasit in tabela si din aceasta cauza nu este selectat nici un rand.

Clauza WHERE a acestei cereri SQL compara numele din tabela EMP cu 'adams'. Pentru comparatie numele sunt convertite in litere mici si din aceasta cauza se obtine un rezultat.

Pentru afisarea numelui cu majuscule de foloseste functia UPPER.

Functiile de tip numeric

Functiile pe un singur rand de tip numeric aceepta ca date de intrare date de tip numeric.

Functie	Descriere
ROUND(column\|expression, n)	rotunjeste la o valoare cu n zecimale
TRUNC(column\|expression, n)	se obtine o valoare cu n zecimale prin truncare
MOD(m, n)	returneaza restul impartirii lui m la n

Exemplu:

Functiile de tip data

Oracle retine datele intr-un format numeric intern, reprezentand secolul, anul, luna, ziua, ora, minutele si secundele. Formatul standard in Oracle este DD-MM-YY. In Oracle datele valide sunt cuprinse intre 1 ianuarie 4712 B.C. si 31 decembrie 9999 A.D. Functia SYSDATE returneaza data si ora sistemului. SYSDATE se foloseste ca orice alt nume de coloana, se poate afisa data curenta selectand SYSDATE dintr-o tabela. Deoarece o baza de date contine atat numere, cat si date, se pot folosi operatorii aritmetici ca adunarea si scaderea intre numere si date.

Operatie	Rezultat	Descriere
data + numar	Data	Adauga un numar de zile la o anumita data
data - numar	Data	Scade un numar de zile dintr-o anumita data
data - data	Numar de zile	Scade o data din alta data
data + number/24	Data	Adauga un numar de ore la o data

SYSDATE este o functie SQL care returneaza data curenta si ora.

Principalele functii de tip data folosite in Oracle sunt:

MONTHS_BEETWEEN (date1, date2): Returneaza numarul de luni dintre data1 si data2. Rezultatul poate fi pozitiv sau negativ. Daca data1 este mai mare decat data2 rezultatul este pozitiv, daca data1 este mai mica decat data2 rezultaul este negativ. Valoarea zecimala reprezinta o parte a lunii.
ADD_MONTHS (date, n): Adauga n luni la o data. n trebuie sa aiba o valoare intreaga si poate fi negativ.
NEXT_DAY (date, 'char')

char

LAST_DAY (date)
ROUND (date [, 'fmt'])

fmt

TRUNC (date [, 'fmt'])

fmt

Exemple:

Functiile de conversie

In unele cazuri Oracle foloseste date de un anumit tip atunci cand astepta date de un alt tip. Cand se intampla acest lucru, Oracle poate converti automat datele in tipul de date dorit. Acesta conversie poate fi facuta implicit de serverul Oracle sau explicit de utilizator. Conversia implicita se face conform anumitor reguli, iar conversia explicita se face folosind functii de conversie. Functiile de conversie convertesc o valoare de un anumit tip in alt tip. SQL contine trei astfel de functii:

Functie	Descriere
TO_CHAR (number\|date, [fmt])	Converteste o valoare de tip numeric sau data intr-un sir de caractere ce are formatul specificat optional de fmt.
TO_NUMBER (number\|date, [fmt])	Converteste un sir de caractere ce contine cifre intr-un numar in formatul specificat optional de fmt.
TO_DATE (number\|date, [fmt])	Converteste un sir de caractere reperezentand o data intr-o valoare de tip data avand formatul fmt specificat. Daca formatul nu este specificat, formatul este DD-MON-YY.

Exemple:

Functii generale

Functiile generale sunt: NVL, NVL2, NULLIF si COALESCE, aceste functii lucreaza cu orice tip de date.

Functie	Descriere
NVL (expr1, expr2)	Converteste o valoare nula intr-o valoare actuala.
NVL2 (expr1, expr2, expr3)	Daca expr1 nu este nula, NVL2 returneaza expr2. Daca expr1 este nula, NVL2 returneza expr3. Argumentul expr1 poate fi orice tip de date.
NULLIF (expr1, expr2)	Compara doua expresii si returneaza valoarea nula daca sunt egale sau prima expresie daca nu sunt egale.
COALESCE (expr1, expr2, ..., expr n)	Returneaza prima expresie non-nula din lista de expresii.

Exemple:

Functia NVL converteste o valoare nula intr-o valoare actuala. Functia NVL se poate folosi pentru a converti orice tip de date, dar valoarea rezultata are intotdeauna acelasi tip cu expr1.

Functia NVL2 examineaza prima expresie. Daca prima expresie nu este nula, atunci functia NVL2 returneaza a doua expresie. Daca prima expresie este nula, atunci a treia expresie este returnata.

Functia NULLIF compara doua expresii. Daca cele doua expresii sunt egale, functia returneaza valoare nula. Daca cele doua expresii nu sunt egale, functia returneaza prima expresie. Functia NULLIF este echivalenta cu expresia CASE.

Functia COALESCE returneaza prima expresie care nu este nula.

Functii pe mai mutle randuri

Functiile pe mai mutle randuri lucreaza cu grupuri de randuri pentru a returna un singur rezultat pentru fiecare grup. Aceste functii sunt cunoscute su denumirea de functii de grup.

Exercitii propuse:

1. Salariul toturor angajatilor se va mari cu 10%. Scrieti o cerere care sa intoarca numele, salariul actual, noul salariu si cresterea salariului pentru toti angajatii.

2. Scrieti o cerere care sa intoarca numele, data angajarii si data primei zi de sambata de cand s-a angajat King.

3. Scrieti o cerere care sa intoarca numele, numarul de saptamani pe care le-au lucrat cei care sunt Salesman.

Pentru a vizualliza raspunsurile le intrebarile de mai sus, apasati aici: RASPUNSURI

Capitolul precedent

Acasa

Capitolul urmator