Conținut

• Funcții de conversie
• Funcții diverse
• Funcții de grup

Aceste funcții fac converia de la un tip de date la un alt tip de date:

Parametrul format specifică formatul în care se va face afișarea, dacă lipsește se ia formatul implicit. Pentru TO_NUMBER specifică formatul în care este dat expr. Parametrul ‘nlsparam’ este utilizat pentru a seta parametrii de sistem să evalueze expresia în diferite limbi, formate, etc.

Ex 1. Să se selecteze toți angajații care au venit în firmă în 1982.

Formatul pentru numere (se pot folosi toate cu comanda COLUMN):

Format	Seminificație
9	Numere (nr. de 9 determină lungimea de afișare)
0	Afișează 0 de la început
\
.	Punct decimal
,	Virgulă
MI	Semnul minus la dreapta
PR	Paranteze pentru numere negative
EEEE	Notație științifică
V	Înmulțire cu 10 (nr. de 9 de după V specifică de câte ori se face înmulțirea)
B	Afișează valori 0 ca blancuri
D	Specifică câte zerouri să fie înainte și după delimitatorul decimal

• DECODE(expr, search_1, result_1, search_2, result_2, …, search_n, result_n, default) – compară expr cu fiecare valoare search_i și întoarce valoarea result_i dacă expr este egală cu valoarea search_i, dacă nu găsește nicio egalitate întoarce valoarea default (i=1..n).
  • expr – poate din orice tip de dată
  • search_i – este de același tip ca expr
  • result_i – este valoarea întoarsă și poate fi de orice tip
  • default – este de același tip ca result_i

Această funcție are efectul unei unei structuri case sau if-then-else.

Ex 2. Să se caluleze o primă pentru angajații din departamentul 20 în funcție de jobul angajatului.

Ex 3. Să se calculeze o primă în funcție de vechime pentru angajații din departamentul 20.

• Instrucțiunea CASE

Instrucținea CASE poate fi folosită în clauza SELECT sau WHERE:

Sintaxa 1:

CASE expr
WHEN value1 THEN statements_1;
WHEN value2 THEN statements_2;
...
[ELSE statements_k;]
END

• expr – reprezintă expresia care se va evalua;
• statements_1 – reprezintă valoarea care se va returna pentru expr = value1;
• statements_2 – reprezintă valoarea care se va returna pentru expr = value2;
• statements_k – reprezintă valoarea implicită care se va returna pentru expr <> value1, value2, …

Sintaxa 2:

CASE 
WHEN expr_1 THEN statements_1;
WHEN expr_2 THEN statements_2;
...
[ELSE statements_k;]
END

• expr_i – reprezintă expresia care se va evalua
• statements_i – reprezintă valoarea care se va returna pentru expr_i

• NVL(expr1, expr2) – returnează expr2 dacă expr1 este null

Pentru a afla userul curent se folosește funcția user:

SELECT USER FROM dual;

Aceste funcții returnează rezultate pe grupuri de înregistrări, nu pe o singură înregistrare, cum au fost cele prezentate până acum. Gruparea se face folosind clauza GROUP BY într-o comandă SELECT și în acest caz toate elementele listei, cu exceptia funcțiilor de grupare, trebuie cuprinse în clauza de grupare. Pentru a se pune condiții folosind funcțiile de grup acestea trebuie să apară în clauza HAVING, nu în clauza WHERE. Se poate folosi operatorul DISTINCT pentru a selecta doar elemente distincte din listă, dar se poate folosi și operatorul ALL pentru a selecta și duplicatele. Funcțiile de grup se mai numesc și funcții de agregare (SQL Aggregate Functions).

• AVG([DISTINCT|ALL] expr) – returnează valoarea medie a unei expresii

Ex 4. Afișați media salariilor pentru toate valorile din tabel, iar apoi doar pentru salariile distincte. Rezultatul este diferit deoarece există salarii duplicate.

Ex 5. Să se calculeze salariul mediu pentru fiecare departament.

Ex 6. Să se calculeze venitul lunar mediu pentru fiecare departament. Afișati id_dep și venitul lunar doar pentru departamentele care au venitul lunar mediu mai mare de 2000. Pentru a aplica o condiție bazată pe funcții de agregare, folosim HAVING în loc de WHERE.

• COUNT(*|[DISTINCT|ALL] expr) – returnează numărul de înregistrări întoarse de interogare. Dacă se folosește * se numără toate înregistrările, inclusiv cele nule, iar dacă se folosește expr se numără toate înregistrările not null .

Ex 7. Să se afișeze numărul angajatilor care au primit salariu pentru fiecare departament.

Ex 8. Să se afișeze departamentele care au cel puțin două funcții distincte pentru angajați.

• MAX([DISTINCT|ALL] expr) – returnează valoarea maximă pentru expr.
• MIN([DISTINCT|ALL] expr) – returnează valoarea minimă pentru expr.
• SUM([DISTINCT|ALL] expr) – returnează suma valorilor pentru expr.

Ex 9. Să se afișeze salariul minim, maxim și suma slariilor pentru fiecare departament.

• VARIANCE([DISTINCT|ALL] expr) – returnează variația standard pentru expr, ingnorând valorile null;
• STDDEV([DISTINCT|ALL] expr) – returnează deviația standard pentru expr, ingnorând valorile null.

Ex 10. Să se afișeze varianțtia standard și deviația standard a salariilor pentru fiecare departament.

Ex 1. Să se facă o listă cu:

• numele angajatului - formatați prima litera mare și restul mici
• departamentul în care lucreaza angajatul - formatați cu litere mici
• data angajării pentru angajat - afișati doar luna și anul
• prima - calculată conform regulilor de mai jos
• gradul salarial în funcție de salariu
• numele șefului - formatați prima litera mare și restul mici
• departamentul în care lucreaza șeful - formatați cu litere mici

Prima se acorda doar pentru angajații care au gradul salarial 2 sau 4 și au venit în firmă în același an cu șeful lor

• dacă angajatul are gradul salarial 2: salariu * sqrt(c * log2(n))
• dacă angajatul are gradul salarial 4: salariu * sqrt(c * log3(n))

unde n este vechimea în firmă calculată în luni și c este o valoare introdusă de la tastatură (folosiți 0.15 pentru testare).

Ordonați rezultatele crescător dupa numele angajatului.

1. Alexandru Boicea - Oracle SQL SQL Plus, Editura Printech