CAPITOLUL 5
Alte functii singulare aplicate liniilor unei baze de date
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.
Functiile de tip data calendaristica
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.
Stocarea datelor calendaristice ORACLE
ORACLE stocheaza datele calendaristice intr-un format numeric intern
reprezentind:
* Secolul
* Anul
* Luna
* Ziua
* Ora
* Minutele
* Secundele
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
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.
Folosirea operatorilor aritmetici
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:
data + numar
aduna un numar de zile la data, reintorcind o data
calendaristica
data - numar
scade un numar de zile dintr-o data, producind o data
calendaristica
date - date
scade o data dintr-o data , obtinind un nr. de zile.
date + numar/24
aduna un nr. de ore pentru a obtine o data calen- daristica.
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.
MONTHS_BETWEEN(data1,data2)
gaseste nr. de luni intre data1 si data2. Rezultatul poate fi
pozitiv sau negativ. Daca data1 e mai tarzie decat data2 ,
REZULTATUL E POZITIV, daca data2 este mai tirzie decit data1 ,
REZULTATUL E NEGATIV.
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.
ADD_MONTHS(data,n)
aduna n numar de luni calendaristice la 'data'. n trebuie sa
fie intreg si poate fi si negativ.
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
NEXT_DAY(data1,,char)
data urmatoarei zile a saptaminii(char) urmind data1. Char
trebuie sa fie un numar reprezentind o zi sau un caracter.
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
LAST_DAY(data1)
gaseste data reprezentind ultima zi a lunii care contine data
1.
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
Functia ROUND poate fi aplicata pe date calendaristice.
Round(data1)
intoarce data1 cu timpul setat la 12:00AM(noaptea). Aceasta e
folositor cind comparam date care au timpuri diferite.
ROUND (data1,'MONTH')
intoarce prima zi a lunii continind data1 daca data1 este in
prima parte a lunii, altfel intoarce prima zi a urmatoarei
luni.
ROUND(data 1,' YEAR')
intoarce prima zi a anului continind data1, daca data1 este in
prima jumatate a anului, altfel intoarce prima zi a urmatorului
an.
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
TRUNC(data1,'char')
gaseste prima zi a lunii care e continuta in data1, cind
char='MONTH'.Daca char= 'YEAR', gaseste prima zi a anului care
contine data1.
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.
Functii de conversie
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.
TO_CHAR(numar/data,{'fmt'})
converteste numar sau data la caractere in format 'fmt'.
TO_NUMBER(char)
converteste 'char' care contine un numar in 'NUMBER'.
TO_DATE('char','fmt')
converteste valoarea 'char' reprezentind o data calendaristica,
intr-o valoare data in felul 'fmt' specificat. Daca fmt e omis
, formatul e DD-MON-YY.
TO_CHAR
Functia TO_CHAR este folosita frecvent pentru a schimba un format de
data de la cel implicat la un format de afisare alternativ. TO_CHAR
(data,'date picture') specifica ca data va fi convertita la un nou
format de iesire.
Pentru a converti date curente de la formatul implicit (DD-MON-YY) la
un nou format 'date picture':
SELECT TO_CHAR(SYSDATE,'DAY, DDTH MONTH YYYY')
FROM SYS.DUAL;
TO_CHAR (SYSDATE,'DAY,DDTHMONTHYYYY')
-------------------------------------
TUESDAY ,05TH SEPTEMBER 1989
Observati ca:
* 'Date picture', care trebuie incadrate de apostrofi poate contine
orice format dintre cele tratate mai jos.Coloana si 'date picture'
trebuie separate de virgula.
* DAY si MONTH la iesire sunt automat aduse la lungimea 9 adaugind
blancuri.
* Pentru a sterge blancurile adaugate folositi prefixul FM(FILL
MODE).
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
Functia TO_CHAR este de asemenea folosita pentru conversia unei valori
de tip data numerica la o valoare de tip data caracter.
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:
SCC or CC
Secol;Prefixul 'S'face ca datele i.e.n. sa fie prefixate de'-'.
YYYY or SYYYY
An;Prefixul 'S' face ca datele i.e.n. sa fie prefixate de '-'.
YYY or YY or Y
Ultimele 3,2,1 cifre din an
Y,YYY
An cu virgula in pozitie specificata.
SYEAR or YEAR
An in litere ;Prefixul 'S' face ca pt.anii i.e.n. sa se puna
'-'.
BC or AD
Indicatorul BC/AD(pt.i.e.n. sau e.n.).
B.C.or A.D.
Indicatorul BC/AD cu puncte.
Q
Sfert de an.
MM
Luna.
MONTH
Numele lunii adaugind blancuri pina la lungimea 9.
MON
Numele lunii abreviat la 3 litere.
WW or W
Saptamina a anului sau a lunii.
DDD or DD or D
Zi a anului, lunii,saptaminii.
DAY
Numele zilei ,adaugind blancuri pina la lungimea de 9
caractere.
DY
Numarul zilei abreviat la 3 litere.
J
Nr.de zile din decembrie 31,4713 i.e.n.
AM or PM
Indicator meridian.
A.M. or P.M.
Indicator meridian cu puncte.
HH or HH12
Ora din zi (1-12).
HH24
Ora din zi (0-23).
MI
Minute.
SS
Secunde.
SSSSS
Secunde dupa miezul noptii(0-86399).
/.,etc.
E reprodusa punctuatia in rezultat.
"..."
Sirul cu apostrofi reprodus in rezultat.
Prefixul de mai jos poate fi adaugat astfel:
fm
'FILL mode'.Prefixind MONTH sau DAY, suprima blancurile, lasind
un rezultat de lungime variabila ,FM va suprima zerourile de
inceput din formatul ddth.Nu e folositor cu alte coduri.A doua
aparitie a lui 'fm' pune blan- curile din nou.
Sufixele de mai jos pot fi adaugate astfel:
TH
nr.ordinal(ex:''DDTH" transformat in "4TH").
SP
nr.transformat in sir (ex.DDSP"pt"FOUR").
SPTH sau thsp
confirma numerele ordinale cu transformarea numar- cadru
(ex.DDSPTH pt.FOURTH).
Observatie:
? Codurile sunt case sensitive si vor afecta afisarea datelor
calendaristice astfel:
DAY MONDAY
Day Monday
Month July
ddth 14th
ddTh 14th
Formatul de date RR
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.
FORMATUL DE DATE RR(ORACLE 7)
Pentru anii in alte secole
----------------------------------------------------------------------
| | | 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 |
--------------------------------------------------------------
Formatul pentru numere
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
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).
TO_NUMBER
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');
TO_DATE
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
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.
ARGUMENTE
DECODE trebuie sa aiba minim 4 parametrii ca argumente.
COL/EXPRESSION
numele coloanei sau expresie pentru a fi evaluate
SEARCH1
prima valoare pentru testare
RESULT1
valoarea intoarsa daca se potriveste cu SEARCH1.
SEARCH1 si RESULT1 pot fi repetate de cite ori e
necesar-[SEARCH2, RESULT2,...]
DEFAULT
valoarea pentru a fi reintoarsa daca nu exista nici o
potrivire.
Observatie:
* col/expression pot fi orice tip de data
* SEARCH poate fi acelasi tip ca expression /col
* Valoarea intoarsa este fortata la acelasi tip ca al treilea
argument (result1)
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
FUNCTII IMBRICATE REVIZITATE
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
1. Coloana MGR e convertita la caracter cu functia TO_CHAR
2. Functia NVL inlocuieste NULL mgr cu un sir de caractere
'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
1. Functia ADD_MONTHS aduna 2 luni la luna curenta(decembrie)
2. Functia NEXT_DAY gaseste vineri la 2 luni de SYSDATE
3. 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.
1. 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
2. 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
3. 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
4. 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
5. 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
6. 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
7. 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
1.
SELECT ENAME,
TO_CHAR(HIREDATE,'fmMonth,Ddspth YYYY')
date_hired
FROM EMP
WHERE DEPTNO=20;
2.
SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,12) REVIEW
FROM EMP
ORDER BY ADD_MONTHS(hiredate,12);
3.
SELECT ENAME,
DECODE(SIGN(1500-SAL),1,'BELOW 1500',0,'On
Target',SAL)
SALARY
FROM EMP
ORDER BY ENAME;
4.
SELECT TO-CHAR(TO_DATE('&ANYDATE','DD.MM.YY'),'DAY') DAY
FROM SYS.DUAL;
5.
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');
6.
SELECT '12/34' VALUE
DECODE(
TRANSLATE('12/34','1234567890',
'9999999999'),
'99/99','YES',NO') "VALID?"
FROM SYS.DUAL;
7.
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;
BogSoft 1999