In acest capitol vom trata functiile (de tip) referitoare la date calendaristice 'DATE', functiile de conversie si functii care accepta orice tip de data de intrare.
Aceste functii se aplica asupra datelor ORACLE.
Toate functiile de tip data calendaristica intorc valoarea tipului
DATE cu exceptia lui MONTHS_BETWEEN care intoarce o valoare numerica.
ORACLE stocheaza datele calendaristice intr-un format numeric intern reprezentind:
Formatul implicit de afisare sau intrare pentru o data calendaristica e DD-MON-YY.Datele calendaristice ORACLE pot varia intre 1 ian 4712 ien si 31 dec 4712 e.n
Sysdate intoarce data curenta si timpul.Puteti folosi SYSDATE la
fel ca folosirea oricarui nume de coloana. De exemplu , puteti
afisa data curenta selectind SYSDATE dintr-o tabela. Este obisnuit
sa se selecteze SYSDATE dintr-o tabela 'dummy' numita DUAL.
Tabela DUAL apartine utilizatorului 'SYS' si poate fi accesata de
toti utilizatorii.Contine o singura coloana ,DUMMY si o linie cu
valoarea 'x'.
Tabela DUAL e folositoare cind doriti sa obtineti o singura valoare-de exemplu , valoarea unei constante , pseudo-coloane sau expresii care nu e derivata dintr-o tabela cu data 'user'.
Pentru a afisa date curenta:
SELECT SYSDATE
FROM SYS.DUAL;
Puteti selecta usor SYSDATE din EMP, dar 14 linii cu aceeasi
SYSDATE vor fi intoarse , una pentru fiecare linie din tabela EMP.
DUAL e preferata pentru ca e suficienta o singura linie intoarsa.
Pornind de la faptul ca data calendaristica e memorata ca un
numar , e posibil sa se faca calcule cu date calendaristice,
folosind operatori aritmetici ca + sau -.Puteti aduna sau scadea
constante numerice ca si date calendaristice.
Operatiile pe care le puteti face sunt:
SELECT HIREDATE,HIREDATE+7, HIREDATE-7,SYSDATE - HIREDATE
FROM EMP
WHERE HIREDATE LIKE '%JUN%';
HIREDATE HIREDATE+7 HIREDATE-7 SYSDATE-HIREDATE
-------------------------------------------------------
13-jun-83 20-jun-83 06-jun-83 1982.70628
11-jun-84 18-jun-84 04-jun-84 1618.70628
04-jun-84 11-jun-84 28-may-84 1625.70628
04-jun-84 11-jun-84 28-may-84 1625.70628
Scazind din SYSDATE ,HIREDATE din tabela EMP intoarce nr.de zile de la angajarea fiecarui om.
SELECT MONTHS_BETWEEN (SYSDATE,HIREDATE),
MONTHS_BETWEEN('01-jan-84','05-nov-88')
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)>59;
MONTHS_BETWEEN(SYSDATE,HIREDATE) MONTHS_BETWEEN('01-jan-84','05-nov-88')
-----------------------------------------------------------------------
65.0873622 -58.129032
63 -58.129032
60.5067171 -58.129032
59.3454267 -58.129032
59.3454267 -58.129032
59.8292977 -58.129032
6 records selected
Partea neintreaga a rezultatului reprezinta o portiune dintr-o luna.
SELECT HIREDATE, ADD_MONTHS(HIREDATE,3),ADD_MONTHS(HIREDATE,-3)
FROM EMP
WHERE DEPTNO =20;
HIREDATE ADD_MONTHS(HIREDATE,3) ADD_MONTHS(HIREDATE,-3)
----------------------------------------------------------------
13-jun-83 13-sep-83 13-mar-83
31-oct-83 31-jan-84 31-jul-83
05-mar-84 05-jun-84 05-dec-83
04-jun-84 04-sep-84 04-mar-84
05-dec-83 05-mar-84 05-sep-83
SELECT HIREDATE,NEXT_DAY(HIREDATE,'FRIDAY'),NEXT_DAY(HIREDATE,6) FROM EMP WHERE DEPTNO =10; HIREDATE NEXT_DAY(HIREDATE,'FRIDAY') NEXT_DAY(HIREDATE,6) ------------------------------------------------------------------ 14-may-84 18-may-84 18-may-84 09-jul-84 13-jul-84 13-jul-84 21-nov-83 25-nov-83 25-nov-83
SELECT SYSDATE,LAST_DAY(SYSDATE),HIREDATE,LAST_DAY(HIREDATE),
LAST_DAY('15-FEB-88')
FROM EMP
WHERE DEPTNO =20;
SYSDATE LAST_DAY(SYSDATE) HIREDATE LAST_DAY(HIREDATE) LAST_DAY('15-feb-88')
----------------------------------------------------------------------------
04-DEC-89 31-DEC-89 17-DEC-80 31-DEC-80 29-FEB-88
o4-dec-89 31-dec-89 02-apr-81 30-apr-81 29-feb-88
04-dec-89 31-dec-89 09-dec-82 31-dec-82 29-feb-88
04-dec-89 31-dec-89 12-jan-83 31-jan-83 29-feb-88
04-dec-89 31-dec-89 03-dec-81 31-dec-81 29-feb-88
SELECT SYSDATE,ROUND(SYSDATE,'MONTH'),ROUND(SYSDATE,'YEAR') FROM SYS.DUAL; SYSDATE ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'YEAR') ---------------------------------------------------------- 04-dec-89 01-dec-89 01-jan-90
SELECT SYSDATE,TRUNC(SYSDATE,'MONTH'),TRUNC(SYSDATE,'YEAR') FROM SYS.DUAL; SYSDATE TRUNC(SYSDATE,'MONTH') TRUNC(SYSDATE,'YEAR') ----------------------------------------------------------- o4-dec-89 01-dec-89 01-jan-89
TRUNC e folositor cind vreti sa stergeti timpul dintr-o data. Timpul component al zilei este sters implicit.
SQL furnizeaza un numar de functii care controleaza conversiile datelor calendaristice.Aceste functii de conversie , convertesc o valoare de la un tip de data la alt tip.
SELECT TO_CHAR(SYSDATE,'DAY, DDTH MONTH YYYY') FROM SYS.DUAL; TO_CHAR (SYSDATE,'DAY,DDTHMONTHYYYY') ------------------------------------- TUESDAY ,05TH SEPTEMBER 1989
Observati ca:
SELECT TO_CHAR (SYSDATE,'fmDAY,ddth Month YYYY')
FROM SYS.DUAL;
TO_CHAR(SYSDATE,'FMDAY,DDTHMONTHYYYY')
--------------------------------------
Tuesday , 5th September 1989
FM poate fi folosit pentru a sterge zerourile de la inceput din formatul ddth ex:05TH este schimbat in 5th. Cazul in care 'date picture' este introdusa este cazul in care va fi afisata.
TO_CHAR poate fi deasemenea folosita pentru extragerea timpului din zi , si afisarea lui intr-un format specificat.
Pentru afisarea timpului dintr-o zi:
SELECT TO_CHAR (SYSDATE.'HH:MI:SS')
FROM SYS.DUAL;
TO_CHAR(SYSDATE,'HH:MI:SS')
---------------------------
08:16:24
TO_CHAR(numar,'number picture')
SELECT TO_CHAR (SAL,'$9,999')
FROM EMP;
TO_CHAR(SAL,'$9,999')
--------------------
$1,000
$1,600
$1,250
$2,975
Observati ca formatul este optional.Daca'date picture' e omis,
data este convertita la o valoare de tip caracter in format Oracle,
implicit DD-MON-YY.
Daca 'number picture ' nu e specificat, numarul e convertit intr-o
valoare de tip caracter.
Observati de asemenea ca formatele nu afecteaza actuala reprezentare
interna a valorii coloanei.Ele afecteaza doar cum valoarea coloanei
este afisata cind e regasita cu o constructie SELECT.
Formate pentru date calendaristice
Elementele unui format de data calendaristica sunt:
Prefixul de mai jos poate fi adaugat astfel:
Sufixele de mai jos pot fi adaugate astfel:
? Codurile sunt case sensitive si vor afecta afisarea datelor calendaristice astfel:
DAY MONDAY
Day Monday
Month July
ddth 14th
ddTh 14th
Daca-l folositi in locul lui YY, secolul variaza in acord cu anul specificat cu 2 cifre si ultimii 2 digiti ai anului curent.
Tabloul de mai jos rezuma comportamentul elementului RR.
---------------------------------------------------------------------- | | | Daca cei 2 digiti ai anului sint | | Daca cei --------------------------------------------------------- | 2 digiti | | 0-49 50-99 | | ai anului --------------------------------------------------------- | curent | | | | sunt | 0 | data intoarsa este data intoarsa este | | | - | in secolul crt. in secolul anterior | | | 49 | celui crt. | | | ---------------------------------------------------------- | | 50 | data intoarsa este data intoarsa este in| | | - | in secolul dupa secolul crt. | | | 99 | cel crt. | ------------------------------------------------------------------------
EXEMPLE:
-------------------------------------------------------------- | Anul crt. Valoarea formatata Anul interpretat | | (DD_ MON_RR) | -------------------------------------------------------------- | 1994 27-oct-95 1995 | -------------------------------------------------------------- | 1994 27-oct-17 2017 | -------------------------------------------------------------- | 2001 27-oct-95 1995 | -------------------------------------------------------------- | 2001 27-oct-17 2017 | --------------------------------------------------------------
Elementele modelului pt.formatul de numere sunt:
Format Semnificatie Exemple
------------------------------------------------------------
9 numere(nr.de 9 999999 1234
determina lung de
afisare)
0 afis.zerourile de
la inceput 099999 001234
$ semnul dolar $999999 $1234
. punct zecimal in 999999.99 1234.00
, pozitie specificata 999,999 1,234
MI semnele minus la
dreapta(valori
negative) 999999MI 1234-
PR paranteze pentru
numere negative 999999PR <1234>
EEEE notatie stiintifica
(formatul trebuie
sa aiba 4E) 99.999EEEE 1.234E+03
V inmultire cu 10
(n=numar de 9 dupa V) 9999V99 123400
B afiseaza valori zero
ca blancuri nu 0 B9999.99 1234.00
-----------------------------------------------------------------
Observatie:
Formatele numerice afisate mai jos in tablou pot fi folosite cu comanda SQL*PLUSCOLUMN(vezi cap.10).
In exemplul urmator , functia TO_NUMBER e folosita pentru a transforma un numar memorat sub forma unui caracter intr-un tip numar.
SELECT EMPNO, ENAME,JOB,SAL
FROM EMP
WHERE SAL>TO_NUMBER('1500');
Pentru a afisa toti angajatii cu data angajarii 4 iulie 1984, puteti folosi functia TO_DATE:
SELECT EMPNO,ENAME, HIREDATE
FROM EMP
WHERE HIREDATE =TO_DATE('June 4,1984','Month dd, YYYY');
EMPNO ENAME HIREDATE
------------------------
7844 TURNER 04-jun-84
Constanta e convertita intr-o data si apoi comparata cu valoarea HIREDATE.
Functia TO_DATE e frecvent folosita pentru a transforma o valoare in ORACLE intr-un format diferit de cel implicit. De exemplu , cind inserati o data , Oracle asteapta o data in formatul implicit DD_MON_YY. Daca nu vreti sa folositi formatul implicit , trebuie sa folositi functia TO_DATE si masca formatului dorit.
De exemplu:
Pentru a introduce in tabela EMP - o linie cu un format nestandard introduceti:
INSERT INTO EMP(EMPNO,DEPTNO,HIREDATE)
VALUES(777,,20,TO_DATE('19/08/90','DD/MM/YY'));
Comanda INSERT e tratata in detaliu mai tirziu.
Functii care accepta orice tip de data la intrare
DECODE este cea mai puternica functie SQL.
Aceasta faciliteaza interogarile, facind munca unui 'case' sau a
unei constructii'if-then-else'.
Syntax:
DECODE (col/expression,
search1,rezult1,[search2,rezult2,....,]
default)
Col/expression e comparata cu fiecare valoare'search'si intoarce 'rezult' daca col/expression este egal cu valoarea 'search'.
Daca nu e gasita nici o egalitate, functia DECODE intoarce valoarea 'default'.Daca valoarea'default' e omisa, NULL e intors pentru cazurile de nepotrivire.
DECODE trebuie sa aiba minim 4 parametrii ca argumente.
Observatie:
Exemplul urmator decodifica tipurile 'CLERK'si' MANAGER';altele nu testate.Acestea devin implicite pentru UNDEFINE.
SELECT ENAME,
JOB,
DECODE(JOB,'CLERK','WORKER',
'MANAGER','BOSS',
'UNDEFINED') DECODED_JOB
FROM EMP;
ENAME JOB DECODED_JOB
-------------------------------
SMITH CLERK WORKER
ALLEN SALESMAN UNDEFINED
WARD SALESMAN UNDEFINED
JONES MANAGER BOSS
MARTIN SALESMAN UNDEFINED
BLAKE MANAGER BOSS
CLARK MANAGER BOSS
SCOTT ANALYST UNDEFINED
KING PRESIDENT UNDEFINED
TURNER SALESMAN UNDEFINED
ADAMS CLERK WORKER
JAMES CLERK WORKER
FORD ANALYST UNDEFINED
MILLER CLERK WORKER
Pentru a afisa procentajele in functie de coloane GRADE din tabela SALGRADE:
SELECT GRADE,
DECODE(GRADE,'1','15%'
'2','10%',
'3','8%',
'5%')BONUS FROM SALGRADE;
GRADE BONUS
---------------
1 15%
2 10%
3 8%
4 5%
5 5%
Acest exemplu ilustreaza ca valoarea intoarsa e fortata la tipul celui de al treilea argument, la folosirea functiei DECODE. Il sfatuim pe utilizator sa specifice ordinea in care informatia e afisata introducind o valoare la fiecare rulare:
select * from emp
order by decode (&orderby,
1,
sal,
2,
ename,
sal);
Enter value for orderby:2
order by decode (2,1,sal,2,ename,sal)
*
ERROR at line 2:ORA -1722: invalid number
Observati ca aceata comanda produce o eroare fiindca tipul lui 'ename'(char) difera de cel al lui 'sal'(numar) care e al treilea argument.
In exemplul de mai jos , dorim sa introducem salariul crescut in functie de meseria fiecarui angajat.
SELECT JOB,SAL,DECODE(JOB,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*0.95,
SAL) DECODED_SALARY FROM EMP;
JOB SAL DECODED_SALARY
--------------------------------
CLERK 800 920
SALESMAN 1,600 1600
SALESMAN 1,250 1250
MANAGER 2,975 2826.25
SALESMAN 1,250 1250
MANAGER 2,850 2707.5
MANAGER 2,450 2327.5
ANALYST 3,000 3300
PRESIDENT5,000 5000
SALESMAN 1,500 1500
CLERK 1,100 1265
CLERK 950 1092.5
ANALYST 3,000 3300
CLERK 1,300 1495
NVL(col/value,val) converteste o valoare NULL la 'val' Tipul datei trebuie sa se potriveasca cu (col/value si val).
SELECT SAL*12+NVL(COMM,0),NVL(COMM,1000),SAL*12+NVL(COMM,1000)
FROM EMP
WHERE DEPTNO=10;
SAL*12+NVL(COMM,0) NVL(COMM,1000) SAL*12+NVL(COMM,1000)
----------------------------------------------------------
29400 1000 30400
60000 1000 61000
15600 1000 16600
GREATEST(col(value1,col/value2....) -intoarce cel mai mare dintr-o lista de valori.Toate 'col/value2' sint convertite la tipul col/value1 inaintea comparatiei.
SELECT GREATEST(1000,2000),GREATEST(SAL,COM)
FROM EMP
WHERE DEPTNO=30;
GREATEST(1000,2000) GREATEST(SAL,COMM)
-------------------------------------------
2000 1600
2000 1250
2000 1400
2000
2000 1500
2000
6 records selected.
LEAST(col/value1,col/value2....) intoace cea mai mica valoare.
Toate col/value 2 sint convertite la tipul col/value1 inainte
de comparatie.
SELECT LEAST(1000,2000),LEAST(SAL,COMM)
FROM EMP
WHERE DEPTNO=30;
LEAST(1000,2000) LEAST(SAL,COMM)
---------------------------------
1000 300
1000 500
1000 1250
1000
1000 0
1000
6 records selected.
VSIZE(col/value) - intoarce nr.de biti din reprezentarea interna ORACLE a lui col/value'.
SELECT DEPTNO,VSIZE(DEPTNO),VSIZE(HIREDATE),VSIZE(SAL),
VSIZE(ENAME)
FROM EMP
WHERE DEPTNO=10;
DEPTNO VSIZE(DEPTNO) VSIZE(HIREDATE) VSIZE(SAL) VSIZE(ENAME)
---------------------------------------------------------
10 2 7 3 5
10 2 7 2 4
10 2 7 2 6
Amintim ca functiile pot fi imbricate la orice nivel si functiile interioare sunt evaluate intii mergind pina la functia cea mai exterioara.Este bine sa se tina nr. de paranteze de inceput (deschidere) si de sfirsit(inchidere).Trebuie sa fie acelasi numar din fiecare tip de paranteze.
Functiile de mai jos sint imbricate si sint evaluate dupa cum urmeaza.
SELECT ENAME,NVL(TO_CHAR(MGR),'UNMANAGEABLE')
FROM EMP
WHERE MGR IS NULL;
ENAME NVL(TO_CHAR(MGR),'UNMANAGEABLE')
-----------------------------------------------
KING UNMANAGEABLE
Functiile imbricate pot fi de asemenea folosite pentru a afisa date de vineri , la doua luni de azi in formatul Day dd Month YYYY.
SELECT SYSDATE,
TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY'),'Day dd
Month YYYY')
FROM SYS.DUAL;
SYSDATE TO_CHAR(NEXT_DAY(ADD_MONTHS(SYSDATE,2),'FRIDAY'),'DAYDDMONTHYYYY')
-------------------------------------------------------------------------
04-dec-89 Friday 09 Februarie 1990
- Functia ADD_MONTHS aduna 2 luni la luna curenta(decembrie)
- Functia NEXT_DAY gaseste vineri la 2 luni de SYSDATE
- Functia TO_CHAR converteste coloana data la tipul CHAR pentru
afisarea in formatul Day dd Month YYYY
Capitolul 5 Exercitii - Functii
Aceste exercitii acopera functiile discutate in acest capitol, ca si
pe cele din capitolul 4.
- Afisati pt.fiecare angajat din departamentul 20 numele si data
angajarii.
Fiti siguri ca specificati aliasul 'DATE_HIRED' dupa expresia
voastra altfel coloana respectiva va fi pierduta. Se folo-
seste o lungime de 80 de caractere care este lungimea implicita
pentru coloane de caracter.
ENAME DATE_HIRED
---------------------------
SMITH June,Thirteenth 1983
JONES October,Thirty-First 1983
SCOTT March,Fifth 1984
ADAMS June,Fourth 1984
FORD December,Fifth 1983
- Afisati pt.fiecare angajat numele, data angajarii si adaugati
un an la data angajarii pt.fiecare. Ordonati iesirea in
ordinea crescatoare a datei angajarii la care s-a adaugat
un an.
ENAME HIREDATE REVIEW
---------------------------
SMITH 13-jun-83 13-jun-84
ALLEN 15-aug-83 15-aug-84
JONES 31-oct-83 31-oct-84
MILLER 21-nov-83 21-nov-84
MARTIN 05-dec-83 05-dec-84
FORD 05-dec-83 05-dec-84
SCOTT 05-mar-84 05-mar-85
WARD 26-mar-84 26-mar-85
CLARK 14-may-84 14-may-85
TURNER 04-jun-84 04-jun-85
ADAMS 04-jun-84 04-jun-85
BLAKE 11-jun-84 11-jun-85
KING 09-jun-84 09-jun-85
JAMES 23-jun-84 23-jun-85
14 records selected
- Afisati lista angajatilor , pentru fiecare afisind salariul
daca acesta e mai mare ca 1500, daca e egal cu 1500 afiseaza
'On Target', daca e mai mic decit 155, afiseaza 'BELOW 1500'.
ENAME SALARY
------------------
ADAMS Below 1500
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES Below 1500
JONES 2975
KING 5000
MARTIN Below 1500
MILLER Below 1500
SCOTT 3000
SMITH Below 1500
TURNER On Target
WARD Below 1500
14 records selected
- Scrieti o cerere care intoarce ziua din saptamina
(ex:MONDAY), pt. fiecare data introdusa in formatul
DD.MM.YY.
Enter value for anydate:12.11.88
DAY
------------------------
SATURDAY
- Scrieti o cerere pt. a determina cit timp au fost angajati ai
companiei .Folositi DEFINE pentru a evita repetarea tipului
functiilor.
Enter value for employee_name:king
ENAME LENGTH OF SERVICE
-------------------------------
KING 4 YEARS 4 MONTHS
- Dindu-se un sir in formatul 'nn/nn', verificati daca primele
si ultimele 2 caractere sunt numere si daca caracterul din
mijloc este '/'.
Afisati expresia 'Yes' daca e asa , sau 'No' altfel. Folositi
urmatoarele valori pentru a testa solutia voastra:
'12/34',01/la','99/88'
VALUE VALID?
-----------
12/34 YES
- Angajatii pe 15 ale fiecarei luni sau inainte sint platiti
in ultima vineri a acelei luni . Cei angajati dupa data de
15 sunt platiti in ultima vineri a urmatoarei luni.
Afisati o lista a angajatilor , cu data de angajare si prima
data de plata.Sortati in functie de data angajarii.
ENAME HIREDATE PAYDAY
---------------------------
SMITH 13-jun-83 24-jun-83
ALLEN 15-aug-83 26-aug-83
JONES 31-oct-83 25-nov-83
MILLER 21-nov-83 30-dec-83
MARTIN 05-dec-83 30-dec-83
FORD 05-dec-83 30-dec-83
SCOTT 05-mar-84 30-mar-84
WARD 26-mar-84 27-apr-84
CLARK 14-may-84 25-may-84
TURNER 04-jun-84 29-jun-84
ADAMS 04-jun-84 29-jun-84
BLAKE 11-jun-84 29-jun-84
KING 09-jul-84 27-jul-84
JAMES 23-jul-84 31-aug-84
14 records selected
CAPITOLUL 5 SOLUTII
SELECT ENAME,
TO_CHAR(HIREDATE,'fmMonth,Ddspth YYYY')
date_hired
FROM EMP
WHERE DEPTNO=20;
SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,12) REVIEW
FROM EMP
ORDER BY ADD_MONTHS(hiredate,12);
SELECT ENAME,
DECODE(SIGN(1500-SAL),1,'BELOW 1500',0,'On
Target',SAL)
SALARY
FROM EMP
ORDER BY ENAME;
SELECT TO-CHAR(TO_DATE('&ANYDATE','DD.MM.YY'),'DAY') DAY
FROM SYS.DUAL;
DEFINE TIME=MONTHS_BETWEEN(SYSDATE,HIREDATE)
SELECT ENAME,
FLOOR(&TIME/12)||'YEARS'||
FLOOR(MOD(&TIME,12))||'MONTHS'
"LENGTH OF SERVICE"
FROM EMP
WHERE ENAME=UPPER('&EMPLOYEE_NAME');
SELECT '12/34' VALUE
DECODE(
TRANSLATE('12/34','1234567890',
'9999999999'),
'99/99','YES',NO') "VALID?"
FROM SYS.DUAL;
SELECT ENAME,
HIREDATE,
DECODE(SIGN(TO_CHAR(HIREDATE,'DD')-15
1,NEXT-DAY(LAST-DAY(ADD_MONTHS(HIREDATE,1)),
'FRIDAY')-7,
NEXT-DAY(LAST_DAY(HIREDATE),'FRIDAY')-7)
PAYDAY
FROM EMP
ORDER BY HIREDATE;
sau
SELECT ENAME,HIREDATE,
NEXT_DAY(LAST_DAY(ROUND(HIREDATE,'MONTH'))-7
'FRIDAY') PAYDAY
FROM EMP
ORDER BY HIREDATE;