Functii in SQL

Functii in SQL

Exista doua tipuri de functii in SQL. Functii care manipuleaza un singur rand (single-row) si functii care manipuleaza un grup de randuri (multiple-row).

Functiile single-row sunt:

Functii pentru tipul caracter

Functii pentru tipul numeric

Functii pentru data calendaristica

Functii de conversie dintr-un tip de data in altul

Functii generate : NVL

DECODE

Functii pentru tipul caracter

Sunt de doua tipuri:

Functii de conversie

- LOWER ( column/expresie) -converteste la litere mici o valoare alfanumerica

- UPPER (column/expresie) - converteste la litera mare

- INITCAP (column/expresie) - converteste prima litera

mare restul mici

Functii de manipulare a caracterelor:

-CONCAT(column/expresie, column/expresie)- concateneaza doua coloane. Este echivalent cu

- SUBSTR (column/expresie, m [,n]) - returneaza caractere din sir incepand de la pozitia m, n caractere. Daca n este omis va returna incepand de la m pana la sfarsitul sirului.

- LENGTH (column/expresie) - returneaza numarul de caractere.

-INSTR (column/expresie, m) - returneaza pozitia caracterului specificat in sirul dat de expresie.

- LPAD ( column/expresie, n,'string') - aliniaza la dreapta pe lungimea n, introducand sirul 'string' pe lungimea ramasa din partea stanga.

Functii de conversie a sirurilor de caractere:

Vom exemplifica utilizarea functiilor de conversie pentru siruri de caractere prin cateva exemple.

Afiseaza informatii pentru angajatul cu numele 'Blake'.

SELECT empno, ename, sal, comm, deptno

FROM emp

WHERE INITCAP(ename) ='Blake';

Functii de manipulare a caracterelor:

Vom exemplifica utilizarea functiilor de conversie pentru siruri de caractere prin cateva exemple.

Afiseaza informatii despre angajatii care lucreaza la departamentul FINANCIAR.

SELECT ename, sal, comm, LENGTH(ename)

FROM emp

WHERE SUBSTR(job, 1, 9) ='FINANCIAR';

Functii pentru date numerice

ROUND - rotunjeste o valoare , specificata zecimal.

TRUNC - truncheaza o valoare specificata zecimal.

MOD - returneaza restul impartirii a doua numere.

Functii pentru date calendaristice

Oracle stocheaza data intr-un format intern numeric: secol, an, luna, zi, ora, minute si secunde. Implicit formatul pentru data este : DD_MON_YY. SYSDATE este o functie care returneaza data si ora curenta.

Dual este o tabela care apartine user-ului SYS si poate fi accesata de toti utilizatorii. Afisarea datei curente folosind tabela dual :

SELECT SYSDATE

FROM dual;

Adunand sau scazand o valoare numerica dintr-o data calendaristica rezultatul va fi tot o data calendaristica. Deoarece tipul data este stocat ca si tipul numeric, putem utiliza operatori aritmetici cum ar fi adunarea si scaderea. Putem aduna sau scadea un numar dintr-o data.

Se pot utiliza urmatoarele operatii:

Afiseaza numele angajatilor si numarul de saptamani de lucru pentru toti angajati din departamentul 10.

SELECT ename, (SYSDATE - hiredate)/7 WEEKS

FROM emp

WHERE depno=10;

Functii utilizate pentru tipul Date

MONTHS_BETWEEN(date1,date2): returneaza numarul de luni dintre doua date.

ADD_MONTHS(date, n): aduna un numar n de luni la o data, n trebuie sa fie intreg si pozitiv.

NEXT_DAY(date,'sir') - returneaza urmatoarea zi dintr-o data specificata, sir poate fi un numar reprezentand o zi sau un sir de caractere.

LAST_DAY(date) - returneaza ultima zi dintr-o luna.

ROUND(date[,'fmt']) - returneaza data rotunjita la unitatile specificate in formatul fmt. Daca formatul fmt    este omis, data este rotunjita la data cea mai apropiata

TRUNC(date[,'fmt']) - truncheaza o data dupa formatul specificat fmt, daca formatul este omis se truncheaza la zi.

Exemple:

MONTHS_BETWEEN('01-SEP-95', '11-JAN-94')

19.6774194

ADD_MONTHS('11-JAN-94', 6) => '11-JUL-94'

NEXT_DAY('01-SEP-95','FRIDAY') => '08-SEP-95'

LAST_DAY('01-SEP-95') => '30-SEP-95'

ROUND('25-JUL-95','MONTH') => 01-AUG-95

ROUND('25-JUL-95','YEAR') => 01-JAN-96

TRUNC('25-JUL-95','MONTH') => 01-JUL-95

TRUNC('25-JUL-95','YEAR') => 01-JAN-95

Afiseaza numarul angajatilor si data angajarii pentru toti angajatii din 1987.

SELECT ename, hiredate, ROUND(hiredate,'MONTH'), TRUNC(hiredate,'MONTH')

FROM emp

WHERE hiredate like '%87' ;

Functii de conversie

In asignari, Oracle poate converti automat urmatoarele tipuri de date:

Asignarea reuseste daca serverul Oracle poate converti tipul de date al valorii folosite in asignare la tipul variabilei asignate.

Pentru evaluarea expresiilor Oracle poate converti automat urmatoarele tipuri de date:

In general, cand este necesara o conversie de tip de date, in locurile in care regulile de conversie folosite in asignari nu pot fi folosite, serverul Oracle foloseste regulile pentru expresii.

Conversiile explicite de date

Exista 3 functii pentru conversia unui tip de data in altul:

TO_CHAR(number/date [,'fmt') - converteste o valoare numerica sau o data la un sir de caractere, dupa formatul specificat 'fmt'.

TO_NUMBER(char) - converteste un sir de caractere continand cifre la un numar.

TO_DATE(char[,'fmt']) - converteste un sir de caractere reprezentand o data calendaristica, la tipul data dupa formatul specificat 'fmt', daca 'fmt' este omis, formatul de data implicit este DD-MON-YY.

Formatul model trebuie inclus intre apostroafe ' ' si este case sensitive.

Elemente de formatare a datei:

Afiseaza numele si data angajarii pentru toti angajatii din tabela EMP.

SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY') DATA_ANG

FROM emp ;

Elemente pentru formatarea orei dintr-o data

Elementele de timp formateaza partea de timp dintr-o data.

Includerea de caractere in afisarea datei se face prin incadrarea lor intre ghilimele ''.

Afisarea in litere a unui numar se face astfel:

Pentru a afisa valoarea unui numar ca si caracter se pot folosi urmatoarele formate:

Convertirea unui sir de caractere la un numar se face folosind functia: TO_NUMBER(char).

Convertirea unui sir la o data se face folosind functia: TO_DATE(char[,'fmt']).

Afisam salariul lui SCOTT folosind simbolul si indicatorul de mii.

SELECT TO_CHAR(sal, '$99,999') SALARY

FROM emp WHERE ename = 'SCOTT ';

Afiseaza numele si data angajarii tuturor angajatilor din tabela EMP care s-au angajat in 22 februarie 1981.

SELECT ename, hiredate

FROM emp

WHERE hiredate = TO_DATE('February 22, 1981', 'Month dd, YYYY');

Functia NVL

Converteste o valoare NULL la o valoare actuala.

Tipurile de date care pot fi folosite sunt: date, number si char:

NVL(comm, 0)

NVL(hiredate, '01-JAN-97')

NVL(job, 'No job yet').

Sintaxa:

NVL(expr1, expr2)

unde:

expr1 este expresia care poate contine NULL

expr2 este valoarea la care va fi convertita o expresie NULL.

Afiseaza numele, salariul, comisionul si salariul anual pentru toti angajatii din tabela EMP, daca comisionul este NULL va fi convertit la 0.

SELECT ename, sal, comm, (sal*12)+NVL(comm,0)

FROM emp;

Functia DECODE

Este similara cu CASE sau IF-THEN-ELSE. Are sintaxa:

DECODE(col/expresie, s1, r1[,s2, r2, . ..][,default])

Functia DECODE evalueaza expresia, dupa care o compara cu fiecare valoare s1, s2, . .. Daca valoarea expresiei este aceasi cu una dintre valorile s1, s2, . atunci se va returna rezultatul corespunzator valorii gasite. Daca valoarea implicita [default] este omisa, o valoare NULL va fi returnata atunci cand nu se gaseste nici o valoare identica cu expresia.

Exemplu:

SELECT job, sal,

DECODE(job, 'ANALIST', sal*1.1,

'CLERK', sal*1.15,

'MANAGER', sal*1.2,

sal)

REVISED_SALARY

FROM emp;

Aceeasi instructiune poate fi scrisa folosind IF-THEN-ELSE astfel:

IF job = 'ANALIST' THEN sal = sal*1.1

IF job = 'CLERK ' THEN sal = sal*1.15

IF job = 'MANAGER' THEN sal = sal*1.2

ELSE sal = sal

Functiile avand ca si rezultat un singur rand, pot fi incuibate pana la orice adancime. Functiile incuibate sunt evaluate incepand cu cel mai adanc nivel spre nivelul superior.

Afiseaza numele managerului, cel care nu mai are sef (mgr este NULL).

SELECT ename,NVL(TO_CHAR(mgr), 'No Manager')

FROM emp

WHERE mgr IS NULL;