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

Functii 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.
Clauza 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.

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

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

Truncheaza 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.

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

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

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.

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' ;

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

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

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.

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.

Elementele ale modelului de formatare pentru date calendaristice

Exemple de elementele ale modelului de formatare

Elementele ale modelului de formatare pentru date calendaristice

Elemente ce formateaza timpul

Adaugati siruri de caractere prin inchiderea acestora intre ghilimele

Adaugati sufixe pentru a scrie in litere un numar

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.

Alte formate

Specificati sufixe

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

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.

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.

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

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.

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

Utilizarea functiei NVL

2 FROM emp;

Functia NVL

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

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

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;

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 12^th, 1982. Ordonati rezultatul afisarii dupa data angajarii.

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.

Copyright © 2024 - 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