Home - Rasfoiesc.com
Educatie Sanatate Inginerie Business Familie Hobby Legal
Doar rabdarea si perseverenta in invatare aduce rezultate bune.stiinta, numere naturale, teoreme, multimi, calcule, ecuatii, sisteme



Biologie Chimie Didactica Fizica Geografie Informatica
Istorie Literatura Matematica Psihologie

Baze de date


Index » educatie » » informatica » Baze de date
» Functii de un singur rind


Functii de un singur rind




Functii de un singur rind

Obiective

Dupa parcurgerea acestei lectii, ar trebui sa stiti sa faceti urmatoarele lucruri

Sa descrieti diferite tipuri de functii disponibile in SQL

Sa utilizati caractere, numere si date calendaristice in cadrul instructiunii SELECT




Sa descrieti utilitatea functiilor de conversie

Functiile fac blocul de baza al interogarii mai puternic si sint folosite pentru a manipula date. Aceasta lectie este prima dintr un set de doua lucrari ce au ca obiectiv descrierea acestor functii. Ea se ocupa atit de functiile de un singur rind pentru caractere, numere si date calendaristice cit si de functiile ce fac conversii dintr-un tip de data in altul de exemplu: din caracter in numar

Functii SQL   

Functiile reprezinta o componenta importanta a limbajului SQL, si pot fi utilizate pentru a face urmatoarele

Calcule matematice asupra datelor

Modificarea unor articole individuale

Manipularea iesirii pentru grupuri de rinduri

Stabilirea unui format pentru date calendaristice si numere atunci cind acestea sint tiparite pe ecran

Schimbarea tipului de data a unei coloane

Functiile SQL accepta argumente si intorc valori.

Nota : Majoritatea functiilor descrise in aceasta lectie sint specifice versiunii SQL pentru Oracle.

Functii SQL continuare Exista doua tipuri distincte de functii:

Functii de un singur rind

Functii de mai multe rinduri

Functii de un singur rind - Aceste functii actioneaza doar asupra unui singur rind si intorc un rezultat pentru fiecarea rind. Exista mai multe tipuri de functii de un singur rind. Aceasta lectie se ocupa de urmatoarele tipuri:

Caracter

Numar

Data calendaristica

Conversie

Functii de mai multe rinduri - Aceste functii actioneaza asupra unor grupuri de rinduri si intorc un rezultat pentru fiecare grup.

Pentru mai multe detalii consultati lucarea

Oracle Server SQL Reference. Release 8.0 pentru o lista completa a functiilor disponibile impreuna cu sintaxa aferenta

Functii de un singur rind

Manipuleaza articole

Actioneaza asupra fiecarui rind rezultat din interogare

Intorc un singur rezultat pentru fiecare rind

Pot modifica tipuri de date

Pot fi imbricate

_functie (coloana | expresie, [arg1, arg2, . ] )

 


Functii de un singur rind - Functiile de un singur rind sint utilizate pentru a manipula date. Ele accepta unul sau mai multe argumente si intorc o singura valoare pentru fiecare rind rezultat din interogare. O functie poate avea ca argument unul din urmatoarele:

O constanta furnizata de utilizator

O variabila

O denumire de coloana

O expresie

Caracteristici ale functiilor de un singur rind

Actioneaza asupra fiecarui rind intors de interogare

Intorc o valoare pentru fiecare rind

Pot intoarce o data a carui tip este diferit de tipul argumentului

Este posibil sa astepte unul sau mai multe argumente

Le puteti utiliza in SELECT, WHERE si ORDER BY Le puteti imbrica

In sintaxa:

nume_functie este numele functiei

coloana    este un nume de coloana din baza de date

expresie este orice sir de caractere sau expresie calculabila

arg1, arg2, . sint argumentele utilizate de functie


Functii de un singur rand:


Aceasta lectie prezinta urmatoarele tipuri de functii:

Functii pentru caractere: accepta argumente de tip caracter si intorc rezultate de tip caracter sau numeric

Functii pentru numere: accepta argumente de tip numeric si intorc rezultate de tip numeric

Functii pentru date calendaristice accepta argumente de tip data calendaristica si intorc rezultate de tip data calendaristica cu exceptia functiei MONTH_BEETWEEN care intoarce o valoare numerica

Functii pentru conversie: fac conversia dintr-un tip de data in altul

Functii generale

Functii NVL

Functii DECODE

Functii pentru caractere

Functii pentru caractere

 


SUBSTR

LOWER LENGTH

UPPER INSTR

INITCAP LPAD

Functiile de un singur rind pentru caractere accepta argumente de tip caracter si intorc rezultate de tip caracter sau numeric. Functiile pentru caractere se pot imparti in

Functii de conversie a caracterelor din litere mari in litere mici.

Functii de manipulare a caracterelor

Functie

Scop

LOWER (expresie coloana

Face conversia caracterelor alfabetice in litere mici

UPPER (expresie coloana

Face conversia caracterelor alfabetice in litere mari

INITCAP (expresie coloana

Face conversia pentru primul caracter din fiecare cuvint in litera mare iar pentru restul caracterelor conversia se face in litere mici

CONCAT(expresie coloana1,

(expresie coloana2)

Concateneaza prima valoare de tip caracter cu a doua valoare de tip caracter.

Aceasta functie este echivalenta cu operatorul de concatenare ( || )

SUBSTR(expresie coloana,

m/,n/)

Intoarce un sir de caractere din cadrul valorii de tip caracter incepind cu pozitia m si avind lungimea n. Daca m este negativ atunci pozitia de inceput a numararii se considera a fi ultimul caracter din sir. Daca n este omis atunci functia intoarce toate caracterele de la pozitia m pina la sfirsitul sirului.

LENGTH(expresie coloana

Intoarce numarul de caractere dintr-o valoare de tip caracter

INSTR(expresie coloana,m

Intoarce pozitia in cadrul valorii de tip caracter a caracterului specificat.

LPAD(expresie|coloana,   

n,’sir caractere’)

Aliniaza valoarea de tip caracter la dreapta pe o lungime de n caractere.

Nota Aceasta este o lista incompleta a functiilor disponibile.

Pentru mai multe detalii consultati lucarea

Oracle Server SQL Reference. Release 8.0 “Character Function”

Functii de conversie a caracterelor din litere mari in litere mici.

FUNCTIE

REZULTAT

LOWER (‘SQL Course’)

sql course

UPPER (‘SQL Course’)

SQL COURSE

INITCAP (‘SQL Course’)

Sql Course


Func
tii de conversie a caracterelor din litere mari in litere mici.

Cele trei functii de conversie a caracterelor sint: LOWER, UPPER, INITCAP.

LOWER: Face conversia in litere mici pentru un text scris cu litere mari si mici

UPPER : Face conversia in litere mari pentru un text scris cu litere mari si mici

INITCAP : Face concersia pentru prima litera din fiecare cuvint in litera mare iar pentru celelalte litere ale cuvintului conversia se face in litera mica.

SQL > SELECT ‘The job title for ‘ || INITCAP(ename) || ‘is’ || LOWER(job)

2 AS “EMPLOYEE DETAILS”

3 FROM emp;

 

EMPLOYEE DETAILS

The job for King is manager

The job for Blake is manager

The job for Clark is manager

14 rows selected.

 


Utilizarea functiilor de conversie a caracterelor din litere mari in litere mici.

Afisati numarul de ordine, numele si departamentul la care lucreaza pentru angajatul Blake.

2 FROM emp

3 WHERE ename = 'blake';

no rows selected

 

2 FROM emp

3 WHERE LOWER(ename) = 'blake';

 

7698 BLAKE 30

 


Exemplul de mai sus afiseaza numarul de ordine, numele si departamentul la care lucreaza pentru angajatul BLAKE.
Clau
za WHERE din prima instructiune SQL specifica numele angajatului ca fiind blake. Din moment ce toate informatiile din tabela EMP sint memorate folosind litere mari numele ‘blake’ (scris cu litere mici) nu poate fi gasit si ca urmare nu se afiseaza nimic

Clauza WHERE din cea de a doua instructiune SQL face mai intii conversia numelui memorat in tabela din litere mari in litere mici si compara rezultatul obtinut cu numele ‘blake’. In acest caz ambii termeni din comparatie sint scrisi cu litere mici si deci de aceasta data se pot selecta informatiile necesare din tabela. Clauza WHERE mai poate fi scrisa ca in exemplul de mai jos , efectul instructiunii fiind acelasi.

. WHERE ename = ‘BLAKE’

 


Numele angajatului din partea dreapta a comparatiei este scris cu litere mari adica asa cum apare in tabela. Pentru a afisa numele cu prima litera convertita in litera mare iar restul in litere mici utilizati functia INITCAP.

SQL> SELECT empno, INITCAP(ename), deptno

2 FROM emp

3 WHERE LOWER(ename) = 'blake';

 


Functii pentru manipulat caractere -

Manipulati siruri de caractere cu ajutorul:

FUNCTIE

REZULTAT

CONCAT (‘Good’,’String’)

SUBSTR (‘String’,1,3)

LENGTH (‘String’)

INSTR (‘String’,’r’)

LPAD (sal,10,’*’)

GoodString

Str

Cele cinci functii pentru manipulat caracatere prezentate in cadrul acestei lectii sint: CONCAT, SUBSTR, LENGTH, INSTR si LPAD.

CONCAT: Concateneaza cei doi parametri. Functia limiteaza numarul parametrilor la 2.

SUBSTR: Extrage un sir de caracter de o lungime spcificata.

LENGTH Intoarce lungimea sirului de caractere (intoarce o valoare numerica

INSTR: Gaseste pozitia caracterului specificat.

LPAD Intoarce un sir de caractere rezultat prin inserarea arg. trei la stinga primului argument lungimea rezultatului avind lungimea specificata de cel de al doilea parametru.

Nota RPAD are un comportament similar cu functia LPAD numai ca inserarea arg. trei se la dreapta primului argument.

Utilizarea functiilor pentru manipulat caractere

2 INSTR(ename, 'A')

3 FROM emp

4 WHERE SUBSTR(job,1,5) = 'SALES';

 

MARTIN MARTINSALESMAN 6 2

ALLEN ALLENSALESMAN 5 1

TURNER TURNERSALESMAN 6 0

WARD WARDSALESMAN 4 2

 


Exemplul de mai sus afiseaza numele angajatului si slujba sa impreuna, lunginea numelui si pozitia literei A in cadrul numelui, pentru toate persoanele care au functia de vinzator.

Exemplu

Modificati exemplul de mai sus astfel incit instructiunea SQL sa afiseze informatiile despre angajati pentru acele persoane a caror nume se termina in litera N.

SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename),

2 INSTR(ename, 'A')

3 FROM emp

4 WHERE SUBSTR(job,-1,1) = 'N';

 

ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')

---------- ----- ----- --------- ------------- ----- ----- ------

MARTIN MARTINSALESMAN 6 2

ALLEN ALLENSALESMAN 5 1

 


Functii pentru valori numerice

ROUND Rotunjeste valoarea cu un numar specificat de zecimale.

ROUND (45.926,2)

TRUNC: Truncheaza valoare

TRUNC

MOD: Intoarce restul impartirii

MOD

Functii pentru valori numerice - Functiile pentru valori numerice accepta valori numerice si intorc valori numerice. Aceasta sectiune descrie o parte din aceste functii

Funtie

Scop

ROUND (coloana | expresie, n)

Rotunjeste coloana, expresia sau valoarea la un numar cu n pozitii la partea zecimala. Daca n este omis numarul rezultat din conversie nu are parte zecimala. Daca n este negativ este rotunjit numarul din partea stinga a punctului zecimal.

TRUNC (coloana | expresie, n)

Truncheaza coloana, expresia sau valoarea la un numar cu n pozitii la partea zecimala. Daca n este omis numarul rezultat din conversie nu are parte zecimala. Daca n este negativ este truncheat numarul din partea stinga a punctului zecimal catre zero

MOD (m,n)

Intoarce restul impartirii dintre m si n

Nota Aceasta este o lista incompleta a functiilor disponibile.

Pentru mai multe detalii consultati lucarea





Oracle Server SQL Reference. Release 8.0 “Number Function”

Utilizarea functiei ROUND

2 ROUND(45.923,-1)

3 FROM DUAL;

 

45.92 46 50

 


Functia ROUND

Functia ROUND rotunjeste coloana, expresia sau valoarea la un numar cu n pozitii la partea zecimala. Daca al doilea argument este omis sau este 0 numarul rezultat din conversie nu are parte zecimala. Daca al doilea argument este 2 atunci numarul rezultat din conversie are 2 cifre la partea zecimala. Daca al doilea argument este 2 atunci se rotunjesc primele 2 cifre ale numarului de la stinga punctului zecimal

Functia ROUND poate fi utilizata asupra datelor calendaristice.

Veti vedea exemple mai tirziu in cadrul acestei lectii.

NOTA DUAL este o tabela fictiva. Mai multe detali despre acest aspect vor fi oferite mai tirziu.

Utilizarea functiei TRUNC

2 TRUNC(45.923,-1)

3 FROM DUAL;

 

45.92 45 40

 



Truncheaz
a coloana, expresia sau valoarea la un numar cu n pozitii la partea zecimala.

Functia TRUNC functioneaza cu argumente similare ca si functia ROUND. Daca al doilea argument este omis sau este 0 numarul rezultat din conversie nu are parte zecimala. Daca al doilea argument este 2 atunci numarul rezultat din conversie are 2 cifre la partea zecimala.

Functia TRUNC la fel ca si functia ROUND poate fi utilizata asupra datelor calendaristice.

Utilizarea functiei MOD

2 FROM emp

3 WHERE job = 'SALESMAN';

 

ENAME SAL COMM MOD(SAL,COMM)

---------- --------- --------- -------------

MARTIN 1250 1400 1250

ALLEN 1600 300 100

TURNER 1500 0 1500

WARD 1250 500 250

 


Functia MOD intoarce restul impartirii dintre valoarea1 si valoarea2. Exemplul de mai sus calculeaza restul impartirii dintre salar si comisionpentru toti angajatii care sint agenti comerciali

Utilizarea datelor calendaristice

Oracle memoreaza datele calendaristice intr un format numeric intern Secol, an, luna, zi, ora, minute, secunde.

Formatul implicit pentru date calendaristice este:

DD-MON-YY.

SYSDATE este o functie care intorce data si timpul.

DUAL este o tabela fictiva utilizata pentru a vedea rezultatul intors de SYSDATE.

Formatul datei calendaristice in Oracle - Oracle memoreaza datele calendaristice intr un format numeric intern Secol, an, luna, zi, ora, minute, secunde.

Formatul implicit pentru date calendaristice este: DD-MON-YY. Valorile valide pentru date calendaristice se situeaza intre Ianuarie 1. 4712 B.C. si Decembrie 31. 9999 A.D.

SYSDATE

SYSDATE este o functie care intorce data si timpul curent. Puteti sa utilizati SYSDATE asa cum utilizati orice denumire de coloana. De exemplu puteti afisa data curenta selectind SYSDATE dintr o tabela. Tabela pe care o folositi ramine la latitudinea dumneavoastra. Se poate de exemplu folosi pentru afisarea datei tabela fictiva DUAL.

DUAL

Tabela DUAL este proprietatea utilizatorului SYS si poate fi accesata de toti utilizatorii. Ea contine o coloana DUMMY, si un rind cu valoarea X. Tabela DUAL este folositoare atunci cind avem de intors o singura valoare

ca de exemplu valoare unei constante, pseudocoloane sau o expresie care nu este derivata dintr-o tabela cu datele utilizatorului.

Exemplu Afisarea datei curente folosind tabela DUAL.

SQL> SELECT SYSDATE

2 FROM DUAL;

 


Operatii aritmetice cu date calendaristice

Daca adunati sau scadeti un numar la sau dintr o data calendaristica veti obtine tot o data calendaristica.

Scadeti doua date pentru a gasi numarul de zile dintre acele date.

Adunati un numar de ore la o data adunind la acea data numarul de ore impartit la 24.

Din moment ce baza de date memoreaza datele calendaristice ca numere, rezulta ca asupra acestor date se pot efectua operatii aritmetice utilizind operatori aritmetici cum ar fi si - . Puteti deasemeni sa adunati sau sa scadeti constante numerice la date calendaristice.

Aveti posibilitatea de aefectua urmatoarele operatii

Operatie

Rezultat

Descriere

data + numar

data

aduna un numar de zile la o data

data – numar

data

scade un numar de zile dintr-o data

data – data

numar de zile

scade o data din cealalta

data + numar/24

data

aduna un numar de ore la o data


Folosirea operatorilor aritmetici cu date calendaristice

2 FROM emp

3 WHERE deptno = 10;

 

  WEEKS

KING 830.93709

CLARK 853.93709

MILLER 821.36566

 


Exemplul de mai sus prezinta o tabela cu numele angajatilor din departamentul 10 alaturi de perioada in care au fost angajati exprimata in saptamini. Pentru a afiasa perioada angajarii in saptamini se face diferenta intre data curenta (data de SYSDATE) si data la care a fost angajata persoana si apoi se imparte rezultatul la 7.

Nota SYSDATE este o functie SQL ce intoarce data si timpul curent. Rezultatul pe care il obtineti daca probati exemplul poate sa difere de rezultatul de mai sus.

Functii pentru date calendaristice

Functie

Descriere

MONTH_BETWEEN

Intoarce numarul de luni dintre doua date calendaristice.

ADD_MONTH

Aduna un numar de saptamini la o data calendaristica

NEXT_DAY

Intoarce ziua ce urmeaza datei specificate

LAST_DAY

Ultima zi a lunii

ROUND

Rotunjeste data calendaristica

TRUNC

Truncheaza data calendaristica

Functiile pentru date calendaristice opereaza asupra datelor calendaristice de tip Oracle. Toate functiile pentru date intorc o valoare de tip data cu exceptia functiei MONTH_BETWEEN, care intoarce o valoare numerica.

MONTH_BETWEEN(data1, data2): Gaseste numarul de luni dintre data1 si data2. Rezultatul poate fi pozitiv sau negativ. Daca data1 este mai tirzie decit data2 atunci rezultatul este pozitiv. Daca data2 este mai tirzie decit data1 atunci rezultatul este negativ. Partea neintreaga a rezultatului reprezinta o parte din luna.

ADD_MONTH(data,n): Aduna un numar de n luni la data. Numarul n trebuie sa fie intreg si poate fi negativ.

NEXT_DAY(data,’char’): Determina data calendaristica a urmatoarei zile specificate, din saptamina, care urmeaza datei     “data

LAST_DAY(data): Determina data calendaristica a ultimei zile specificate, din saptamina, care urmeaza datei “data”

ROUND(data[,’fmt’]): Intoarce data rotunjita in functie de formatul fmt. Daca fmt este omis atunci data este rotunjita la cea mai apropiata data.

TRUNC(data[,’fmt’]): Intoarce data “data” trunchiata in functie de de formatul fmt. Daca fmt este omis atunci data este rotunjita la cea mai apropiata zi

Aceasta lista este un subset al functiilor disponibile. Modelele pentru format sint tratate mai tirziu in cadrul acestui capitol. Exemple de format sint month si year.

Utilizarea functiilor pentru date calendaristice

MONTH_BETWEEN (‘01-SEP-95’,’11-JAN-94’)

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’   

Pentru toate persoanele care au fost angajate pe o perioada mai mica de 200 de luni, afisati numarul de ordine al angajatului , data angajarii, numarul de luni pe care persoana le-a acumulat ca angajat, data reviziei care trebuie facuta peste 6 luni, prima vineri de dupa data angajarii, ultima zi a lunii in care s-a facut angajarea.

SQL > SELECT empno, hiredate,

2 MONTHS_BETWEEN (SYSDATE, hiredate) TENURE,

3 ADD_MONTHS (hiredate, 6) REVIEW,

4 NEXT_DAY (hiredate, ‘FRIDAY’), LAST_DAY(hiredate)

5 FROM emp

6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate) < 200;

 

EMPNO HIRDATE TENURE REVIEW NEXT_DAY( LAST_DAY(

----- ----- ------- ----- ----- ---- ----- ----- ---- ----- ----- ---- ----- ----- -------- ----- ----- -------

7839 17-NOV-81 192.24794 17-MAY-82 20-NOV-81 30-NOV-81

7698 01-MAY-81 198.76407 01-NOV-81 08-MAY-81 31-MAY-81

.

11 rows selected.

 


Utilizarea functiilor pentru date calendaristice

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   

Functiile ROUND si TRUNC pot fi utilizate atit pentu numere cit si pentru date calendaristice. Atunci cind sint utilizate cu date calendaristice, acestea rotunjesc sau truncheaza data tinind cont de modelul specificat. Astfel se pot , de exemplu, rotunji date calendaristice spre cel mai apropiat an sau cea mai apropiata luna.

Exemplu

Comparati datele in care s-au facut angajari pentru toate persoanele care au inceput sa lucreze in anul 1987. Afisati numarul de ordine al angajatului, data angajarii, si luna in care acesta a inceput sa lucreze exprimata sub forma unui interval, folosind functiile ROUND si TRUNC.

> SELECT empno, hiredate,

2 ROUND (hiredate, ‘MONTH’), TRUNC(hiredate, ‘MONTH’)

3 FROM emp

6 WHERE hiredate like ‘%87’ ;

 

EMPNO HIREDATE ROUND(HIR TRUNC(HIR

----- ----- ------- ----- ----- ------ ----- ----- ------- ----- ----- -------

7788 19-APR-87 01-MAY-87 01-APR-87

7876 23-MAY-87 01-JUN-87 01-MAY-87

 


Functii pentru conversia tipului de date

Conversie implicita a tipului dedate

 

Conversie explicita a tipului de date

 


Pe linga tipurile de date din Oracle, coloanele tabelelor dintr-o baza de date Oracle8 pot fi definite utilizind tipuri de date ANSI, DB2 si SQL DS. Intern server ul Oracle face conversia din aceste tipuri de date in tipuri de date Oracle8

In unele situatii, server ul Oracle accepta anumite tipuri de date desi in mod normal ar trebui sa primeasca alte tipuri. Acest lucru se intimpla atunci cind server ul Oracle poate face automat conversia in tipul de date pe care il astepta. Aceste conversii se pot face implicit de catre server ul Oracle sau explicit de catre utilizator.

Conversiile de date implicite se fac conform unui set de reguli ce va fi detaliat mai tirziu.

Conversiile de date explicite se fac utilizind functii de conversie. Functiile de conversie transforma tipul unei valori in altul. In general functiile de conversie respecta urmatoarea forma: tip de data1 TO tip de data2 unde

tip de data1este tipul de data care trebuie transformat si reprezinta intrarea, iar tip de data2este tipul de data spre care se face conversia si reprezinta iesirea.

Nota Desi se fac conversii de date in mod implicit atunci cind este nevoie, este recomnadat ca aceste conversii sa fie facute implicit de catre utilizator pentru a sigura corectitudinea instructiunilor

Conversii de date implicite In operatii de atribuire Oracle poate automat conversia

DIN

IN

VARCHAR2 sau CHAR

(sir de caractere

NUMBER

(valoare numerica)

VARCHAR2 sau CHAR

(sir de caractere

DATE

NUMBER

(valoare numerica)

VARCHAR2

(sir de caractere

DATE

(data calendaristica

VARCHAR2

(sir de caractere

In operatii de atribuire Oracle poate automat conversia

Din VARCHAR2 sau CHAR in NUMBER

Din VARCHAR2 sau CHAR in DATE

Din NUMBER in VARCHAR2

Din DATE in VARCHAR2

Operatia de atribuire are loc cu succes daca server-ul Oracle poate converti tipul de data al sursei in tipul de data al destinatiei.

Conversii de date implicite

In cazul evaluarii expresiilor, Oracle poate automat conversia

DIN

IN

VARCHAR2 sau CHAR

(sir de caractere

NUMBER

(valoare numerica)

VARCHAR2 sau CHAR

(sir de caractere

DATE

(data calendaristica)

In cazul evaluarii expresiilor, Oracle poate automat conversia

Din VARCHAR2 sau CHAR in NUMBER

Din VARCHAR2 sau CHAR in DATE

In general server-ul Oracle utilizeaza regulile de conversie pentru expresii in cazul in care regulile de conversie pentru atribuire nu acopera si situatia respectiva.

Nota Conversia din CHAR in NUMBER are loc cu succes doar daca sirul de caractere reprezinta un numar valid. Conversia din CHAR in DATE are loc cu succes doar daca sirul de caractere respecta formatul implicit

DD-MON-YY.

Conversii de date explicite

SQL pune la dispozitie trei functii cu ajutorul carora se pot face conversii dintr un tip de data in altul.



Functie

Scop

TO_CHAR (numar data calendaristica, ‘fmt’

Face conversia dintr-un numar sau o data calendaristica intr un sir de caractere de tipul VARCHAR2 respectind formatul fmt specificat.

TO_NUMBER (caracter)

Face conversia dintr-un sir de caractere ce contine cifre intr o valoare numerica

TO_DATE (caracter ,[‘fmt’])

Face conversia dint-un sir de caractere ce reprezinta o data intr-o valoare de tip DATE respectind formatul fmt specificat.

Daca fmt este omis formatul implicit este DD-MON-YY)

Nota Lista prezentata mai sus reprezinta un subset din functiile disponibile pentru conversii.

Pentru mai multe detalii consultati lucarea

Oracle Server SQL Reference. Release 8.0 “Conversion Function”

Utilizarea functiei TO CHAR impreuna cu date calendaristice


Modelul de formatare:

Trebuie inclus intre ghilimele simple si este

case sensitive

Poate include orice element valid al modelului de formatare pentru date calendaristice

Are un element fm care elimina spatiile albe sau zerourile nesemnificative.

Este separat de data calendaristica prin virgula

‘fmt’)

 


Afisarea datei calendaristice intr-un anumit format

Pina acum toate datele calendaristice au fost afisate respectind formatul DD-MON-YY. Functia TO_CHAR va permite sa faceti conversia din formatul implicit intr un format specificat de dumneavoastra

Observatii

Trebuie inclus intre ghilimele simple si este case sensitive

Poate include orice element valid al modelului de formatare pentru date calendaristice. Asigurati-va ca valoarea este separata de modelul de formatare prin virgula.

Pentru numele zilelor si a lunilor in iesire se adauga automat spatii albe.

Pentru a elimina spatiile si zerourile nesemnificative folositi elementul pentru modul de umplere.

Aveti posibilitatea de a redimensiona lungimea pe care se face afisarea pentru un cimp cu ajutorul comenzii SQL*Plus COLUMN.

Lungimea implicita a coloanei rezultate este de 80 caractere.

SQL > SELECT empno, TO_CHAR (hiredate, ‘MM/YY’) Month_Hired

2 FROM emp

3 WHERE ename=’BLAKE’;

 


Elementele ale modelului de formatare pentru date calendaristice

YYYY

Anul afisat pe 4 digiti

YEAR

Anul in litere

MM

luna scrisa cu doua cifre

MONTH

numele lunii

DY

o abreviatie a denumirii unei zile din saptamina formata din trei litere

DAY

denumirea completa a zilei

Exemple de elementele ale modelului de formatare

Element

Descriere

SCC sau CC

Secol:S precede data i.e.n cu

YYYY sau SYYYY

(an in cadrul datelor calendaristice

Anul: S precede data i.e.n cu

YYY sau YY sau Y

Ultimele 3,2 sau 1 cifre din an

Y,YYY

O virgula in cadrul anului

[YYY,[YY,[Y,]

4,3,2 sau o cifra din an conform standardului ISO

SYEAR sau YEAR

Anul in litere :S precede data i.e.n cu

BC sau AD

Indicatorul BC AD

B.C. sau A.D.

Indicatorul BC AD cu puncte

Q

Sfertul unui an

MM

Luna scrisa cu doua cifre

MONTH

Numele intreg al lunii scris pe 9 caractere. Daca denumirea lunii nu ocupa cele 9 caractere, spatiul ramas liber este automat umplut cu spatii

MON

O abreviatie a denumirii unei luni formata din trei litere

RM

Luna scrisa cu cifre romane

WW sau W

Saptamina din an sau luna

DDD sau DD sau D

Ziua din an ,luna sau saptamina.

DAY

Denumirea completa a zilei completata eventual cu spatii pina la 9 caractere.

DY

O abreviatie a denumirii unei zile formata din trei litere

J

Numarul de zile de la data de 31 Decembrie 4713BC

Elementele ale modelului de formatare pentru date calendaristice

Elemente ce formateaza timpul

HH24:MI:SS AM

15:45:32 PM

Adaugati siruri de caractere prin inchiderea acestora intre ghilimele

DD “of” MONTH

12 of OCTOBER

Adaugati sufixe pentru a scrie in litere un numar

ddspth

fourteenth

Modele de formatare pentru timp

Utilizarea functiei TO_CHAR impreuna cu date calendaristice

Utilizati elementele descrise mai jos atunci cind doriti sa afisati timpul intr-un anumit format sau folosind litere in loc de cifre.

Element

Descriere

AM sau PM

indicator de meridian

A.M. sau P.M.

indicator de meridian cu puncte

HH sau HH12 sau HH24

ora

MI

minute (0

SS

secunde (0-59)

SSSSS

Numarul de secunde incepind cu miezul noptii

Alte formate

Element

Descriere

Punctuatia este reprodusa in rezultat.

“of the”

sirul incadrat intre ]ghilimele este reprodus

Specificati sufixe

Element

Descriere

TH

Numar de ordine dat in cifre (de exemplu DDTH pentru 4TH)

SP

Numar scris in litere (de exemplu DDSP pentru FOUR)

SPTH sau THSP

Numar de ordine scris in litere (de exemplu DDSPTH pentru FOURTH)


Utilizarea functiei TO_CHAR impreuna cu date calendaristice

Exemplul de mai sus prezinta o modalitate de a afisa numele si data angajarii pentru fiecare angajat.( De remarcat este formatul in care se afiseaza data.

Exemplu

Modificati exemplul de mai sus astfel incit data calendaristica sa aiba urmatorul format

Ex. Seventh of February 1981 08:00:00 AM

SQL > SELECT ename,

2 TO_CHAR (hiredate, ‘fmDdspth “of” Month YYYY fmHH:MI:SS AM’)

3 HIREDATE

3 FROM emp;

 

ENAME HIREDATE

----- ----- ---------- ----- ----- ------------

KING Seventeenth of November 1981 12:00:00 AM

BLAKE First of May 1981 12:00:00 AM

.

14 rows selected

 


De remarcat este faptul ca denumirea lunii respecta modelul pentru format specificat (INITCAP).

Utilizarea functiei TO_CHAR impreuna cu valori numerice


Pentru a afisa o valoare numerica sub forma unui caracter utilizati urmatoarele elemnte de formatare impreuna cu functia TO_CHAR.

Reprezinta un numar

Forteaza afisarea unei cifre 0

Semnul dolar

L

Foloseste simbolul local pentru moneda

Afiseaza un punct

Tipareste un indicator pentru mii

Utilizarea functiei TO_CHAR impreuna cu valori numerice

Atunci cind lucrati cu valori numerice ca siruri de caractere ar trebui sa convertiti acele numere spre valori de tip caracter utilizind functia TO_CHAR, care face conversia dintre o valoare de tip NUMBER spre o valoare de tip VARCHAR2. Aceasta tehnica este folositoare in cadrul unei concatenari

Elemente de formatare pentru numere

Daca aveti de convertit un numar intr o valoare de tip caracter puteti utiliza urmatoarele elemente.

Element

Descriere

Exemplu

Rezultat

Pozitie numerica numarul cifrelor de 9 determina lungimea pe care se face afisarea

Afiseaza zerourile nesemnificative

Semnul dolar

L

Foloseste simbolul local pentru moneda

L999999

FF1234

Determina afisarea unui punct zecimal in pozitia specificata.

Determina afisarea unei virgule in pozitia specificata.

MI

Determina afisarea semnului minus in partea dreapta (pentru valori negative)

999999MI

PR

Inchide intre paranteze numerele negative

999999PR

<1234>

EEEE

Notatie stiintifica formatul impune existenta a patru litere E

99.999EEEE

1.234E

V

Inmultire cu 10 de n ori (n=numarul de cifre de 9 de dupa litera V)

9999V99

B

Inlocuieste valorile de 0 cu blank

B9999.99

Utilizarea functiei TO_CHAR impreuna cu valori numerice




Observatii

Server-ul Oracle afiseaza semnul in locul valorii numerice a carui numar cifre a depasit valoarea specificata prin model.

Server ul Oracle rotunjeste valoarea zecimala stocata ca o valoare cu un numar de zecimale furnizat de catre modelul de formatare.

Functiile TO_CHAR si TO_DATE

Pentru a face conversia dintr-un sir de caractere intr un numar folositi functia TO_NUMBER


Pentru a face conversia dintr-un sir de caractere intr-o data calendaristica folositi functia TO_DATE


Este posibil sa apara o situatie in care doriti sa faceti conversia dintr un sir de caractere intr-un numar sau intr o data callendaristica. Pentru a realiza aceste tipuri de conversii utilizati functiile TO_NUMBER si TO_DATE. Modelul dupa care se face formatarea va trebui sa-l alcatuiti pe baza elementelor pentru formatare prezentate anterior.

Exemplu

Afisati numele si data angajarii pentru toate persoanele care au fost angajate pe February


Formatul RR pentru date calendaristice

Anul curent

Data specificata

Formatul RR

Formatul YY

27-OCT-95

27-OCT-17

27-OCT-17

27-OCT-95

Daca cele doua cifre specificate ale anului sint

Daca cele doua cifre ale anului curent sint

Data intoarsa se incadreaza in secolul curent

Data intoarsa se incadreaza in secolul de dinaintea celui curent

Data intoarsa se incadreaza in secolul de dupa secolul curent

Data intoarsa

se incadreaza

in secolul

curent

Formatul RR pentru date calendaristice este similar cu elementul YY, dar va permite sa specificati secole diferite. Aveti posibilitatea de a folosi elementul pentru formatarea datelor RR in locul elementului YY si astfel secolul valorii returnate variaza in functie de cei doi digiti specificati in an si de ultimii doi digiti ai anului curent. Tabelul urmator descrie comportamentul elementului RR.

Anul curent

Data specificata

Formatul RR

Formatul YY

-OCT-95

27-OCT-17

27-OCT-17

Functia NVL

Converteste o valoare nula intr o valoare efectiva

Tipurile de date care pot fi folosite sint: data calendaristica, caracter si numar.

Tipurile de date trebuie sa se potriveasca

NVL (comm,0)

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

- NVL (job, ‘No Job Yet)

Pentru a face conversia intre o valoare nula si o valoare efectiva utilizati functia NVL.

Sintaxa

NVL (expr1, expr2)

 


unde: expr1 este valoarea sau expresia sursa care ar putea sa contina o valoare nula.

expr2 este valoarea tinta, valoarea spre care se face conversia

Aveti posibilitatea de a utiliza functia NVL impreuna cu orice tip de data, dar tipul valorii intoarse este de fiecare data la fel cu tipul parametrului expr1.

Conversii NVL pentru diferite tipuri de date

Tip de data

Exemplu de conversie

NUMBER

NVL (coloana ce contine o valoare de tip numeric

DATE

NVL (coloana ce contine o valoare de tip data

calendaristica, ‘01-JAN-95’)

CHAR sau VARCHAR2

NVL (coloana ce contine o valoare de tip caracter,‘Unavariable’)

Utilizarea functiei NVL

2 FROM emp;

 

ENAME SAL COMM (SAL*12)+NVL(COMM,0)

KING 5000 60000

BLAKE 2850 34200

CLARK 2450 29400

JONES 2975 35700

MARTIN 1250 1400 16400

ALLEN 1600 300 19500

14 rows selected.

.

14 rows selected.

 


Functia NVL

Pentru a calcula compensatia anuala pentru toti angajatii, trebuie sa inmultiti salariul lunar cu 12 si apoi sa adugati comisionul.

SQL> SELECT ename, sal, comm, (sal*12)+comm

2 FROM emp;

 

ENAME SAL COMM (SAL*12)+NVL(COMM,0)

KING 5000

BLAKE 2850

CLARK 2450

JONES 2975

MARTIN 1250 1400 16400

14 rows selected.

.

14 rows selected.

 


Din exemplul precedent se poate remarca faptul ca compensatia anuala se calculeaza doar pentru acei angajati care au o valoare pentru comision nenula. Daca se intilneste pe colana o valoare nula atunci rezultatul este nul. Pentru a calcula valorile pentru toti angajatii trebuie sa convertiti valorile nule in valori numerice inainte de a aplica operatorul aritmetic. O solutie corecta pentru o astfel de problema este prezentata in exemplul precedent celui luat in discutie, exemplu in care pentru conversia valorilor nule s-a folosit functia NVL.

Functia DECODE Faciliteaza simularea unor structuri de tip CASE sau IF-THEN-ELSE

[, search2, result2,,]

[, default])

 


Functia DECODE evalueaza o expresie intr-un mod similar structurii IF-THEN-ELSE, structura folosita in multe limbaje de programare. Funtia DECODE evalueaza expresia dupa ce o compara cu fiecare valoare search. Daca valoarea expresiei este la fel cu valoarea continuta in search atunci valoarea result este intoarsa. Daca valoarea default implicita) este omisa functia va intoarce o valoare nula in cazul in care valoarea expresiei nu se potriveste cu nici o valoare search.

2 DECODE(job, 'ANALYST', SAL*1.1,

3 'CLERK', SAL*1.15,

4 'MANAGER', SAL*1.20,

5 SAL)

6 REVISED_SALARY

7 FROM emp;

 

PRESIDENT 5000 5000

MANAGER 2850 3420

MANAGER 2450 2940

14 rows selected.

 


Utlizarea functiei DECODE

In exemplul de mai sus valoarea evaluata este JOB. Daca JOB este ANALIST, sporul de salar este de 10%; daca JOB este CLERK, sporul de salar este de 15% iar daca JOB este MANAGER, sporul de salar este de 20%.Pentru celelalte slujbe salariile nu se modifica.

Aceeasi structura scrisa cu IF-THEN-ELSE are urmatoarea forma

IF job = ‘ANALIST’ THEN sal = sal * 1.1

IF job = ‘CLERK’ THEN sal = sal * 1.15

IF job = ‘MANAGER’ THEN sal = sal * 1.20

ELSE sal = sal

 


Imbricarea functiilor

Functiile de un singur rind se pot imbrica de cite ori dorim. Evaluarea lor se face din centrul expresiei imbricate spre exteriorul acesteia. Exemplele care urmeaza va vor demonstra flexibilitatea acestor functii.

Imbricarea functiilor

2 NVL(TO_CHAR(mgr),'No Manager')

3 FROM emp

4 WHERE mgr IS NULL;

 


ENAME NVL(TO_CHAR(MGR),'NOMANAGER')

KING No Manager

 

Imbricarea functiilor (continuare)

Exemplul de mai sus afiseaza acele persoane care nu are au superior. Evaluarea instructiunii SQL se realizeaza in doi pasi.

1. Evaluarea functiei din interior ce face conversia dintr-o valoare numerica in una de tip caracter.

- Rezultat1=TO_CHAR (mgr)

2. Evaluarea functiei din exterior care inlocuieste valorile nule cu un text

- NVL (Rezultat1, ‘No Manager’)

Denumirea coloanei este data de intreaga expresie din moment ce nu este specificat nici un alias pentru acea coloana.

Exemplu

Afisati data calendaristiaca a zilei de vineri ce urmeaza dupa sase luni de la data angajarii. Data rezultata ar trebui sa aiba o forma de genul Friday, March 12th, 1982. Ordonati rezultatul afisarii dupa data angajarii.

SQL > SELECT TO_CHAR (NEXT_DAY (ADD_MONTHS

2 (hiredate, 6), ‘FRIDAY’) ,

3 ‘fmDay, Month ddth, YYYY’)

4 “Next 6 Month Review”

5 FROM emp

6 ORDER BY hiredate;

 


Sumar

Utilizati functii in cazul in care

aveti de facut calcule asupra unor date calendaristice

Modificarea unor articole individuale

Manipularea iesirii pentru grupuri de rinduri

Stabilirea unui format pentru date calendaristice si numere atunci cind acestea sint tiparite pe ecran

Schimbarea tipului de data a unei coloane

Functii de un singur rind

Functiile de un singur rind se pot imbrica de cite ori dorim. Cu ajutorul functiilor de un singur rind putem manipula

Date de tip caracter

- LOWER, UPPER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH

Date de tip numeric

- ROUND, TRUNC, MOD

Date calendaristice

- MONTHS_BETWEEN, ADD_MONTH, NEXT_DAY, LAST_DAY, ROUND, TRUNC

- valorile de tip data calendaristica pot fi utilizate impreuna cu operatori aritmetici

Functiile de conversie pot converti valori numerice, valori de tip caracter si datelor calendaristice

- TO_CHAR, TO_DATE, TO_NUMBER

SYSDATE si DUAL
SYSDATE este o functie care intoarce data si timpul curent. . Tabela pe care o folositi ramine la latitudinea dumneavoastra. Se poate de exemplu folosi pentru afisarea datei tabela fictiva DUAL.

Privire generala asupra exercitiilor

Crearea unor interogari care sa includa lucru cu numere, caractere si date calendaristice

Utilizarea concatenarilor cu functii

Realizarea unor interogari care sa nu depinda de faptul ca informatiile sint scrise cu litere mari sau mici

Realizarea unor calcule asupra anilor si lunilor de angajare ale unor persoane

Determinare datei cind se recalculeaza salariul pentru un angajat.

Privire generala asupra exercitiilor

Exercitiile ce urmeaza sint astfel concepute incit sa aveti posibilitatea sa puneti in aplicatie cunostintelor acumulate pe parcursul acestei lectii cu privire la functiile pentru caractere,valori numerice, date calendaristice si tipuri de date.

Este bine sa va reamintiti ca in cazul functiilor imbricate evaluarea se face incepind cu functia din interior si terminind cu cea din exterior.

1. Scrieti o interogare care sa afiseze data curenta. Denumiti coloana Date

2.Afisati numarul de ordine,numele,salariul si salariu marit cu 15%(intr-un singur numar Denumiti ultima coloana Salar Nou. Salvati instructiunea intr-un fisier numit p3q2.sql

3.Rulati programul salvat anterior

4. Modificati programul salvat in fisierul p3q2.sql astfel incit acesta sa adauge o coloana in care veti trece difereta dintre salariul nou si cel vechi. Denumiti coloana Crestere. Rulati noul program.

5. Afisati numele angajatului, data angajarii, data cind se recalculeaza salariul, care este prima luni dupa 6 luni de servici. Denumiti coloana REVIEW. Formatati afisarea datei astfel incit sa arate similar cu exemplul de mai jos:

Ex. Sunday, the Seventh of September, 1981

6. Pentru fiecare angajat afisati numele si calculati numarul de luni intre data de astazi si data angajarii. Denumiti coloana LUNI_DE_ACTIVITATE. Ordonati rezultatul dupa numarul de luni de lucru. Rotunjiti numarul de luni.

7. Scrieti o interogare care sa produca urmator afisaj pentru fiecare angajat

<nume angajat> cistiga <salariu> lunar dar ar dori <3 * salariu>. Denumiti coloana Salariul de vis.

8. Scrieti o interogare care sa afiseze numele si salariul pentru toti angajatii. Afisati valoarea salariului pe 15 caracter aliniata la dreapta iar spatiul ramas la stinga sa fie umplut cu caracterul $. Denumiti coloana SALARIU.

9. Scrieti o interogare care sa afiseze numele angangajatului cu litere mici cu exceptia primei litere care se va scrie cu litera mare si lungimea numelui.

10. Afisati numele, data angajarii si ziua din saptamina in care angajatul a inceput lucrul. Denumiti coloana ZI.

Ordonati rezultatul dupa cimpurile coloanei ZI incepind cu Monday (Luni).

11. Scrieti o interogare care sa afiseze numele angajatului si valoarea comisionului. Daca angajatii nu obtin comision introduce-ti No commission”. Denumiti coloana COMM.








Politica de confidentialitate


Copyright © 2020 - Toate drepturile rezervate

Baze-de-date


Access
Adobe photoshop
Autocad
Baze de date
C
Calculatoare
Corel draw
Excel
Foxpro
Html
Internet
Java
Linux
Mathcad
Matlab
Outlook
Pascal
Php
Powerpoint
Retele calculatoare
Sql
Windows
Word