Korzystanie z funkcji jednorzędowych

Korzystanie z funkcji pojedynczego wiersza w celu dostosowania wyników

Oracle SQL dostarcza bogatą bibliotekę wbudowanych funkcji, które można wykorzystać do różnych zadań. Podstawowe możliwości funkcji mogą obejmować konwersję wielkości liter w łańcuchach, operacje w łańcuchu lub podłańcuchach, obliczenia matematyczne na danych liczbowych oraz operacje na datach na wartościach typu daty. Funkcje SQL opcjonalnie pobierają argumenty od użytkownika i obowiązkowo zwracają wartość.

W szerszej kategorii istnieją dwa typy funkcji: -

Single Row functions- Funkcje jednorzędowe to te, które działają na jednym wierszu i zwracają jedno wyjście na wiersz. Na przykład funkcje konwersji długości i wielkości liter są funkcjami jednorzędowymi.

Multiple Row functions - Funkcje wielu wierszy działają na grupie wierszy i zwracają jeden wynik dla całego zestawu wierszy. Znane są również jako funkcje grupowe.

Funkcje jednorzędowe

Funkcje jednorzędowe mogą być funkcjami znakowymi, numerycznymi, datowymi i konwersyjnymi. Zauważ, że te funkcje są używane do manipulowania elementami danych. Te funkcje wymagają co najmniej jednego argumentu wejściowego i działają na każdym wierszu, zwracając w ten sposób jedną wartość wyjściową dla każdego wiersza. Argumentem może być kolumna, literał lub wyrażenie. Funkcji jednorzędowych można używać w instrukcjach SELECT, WHERE i ORDER BY. Funkcje jednorzędowe mogą być -

  • General functions- Zwykle zawiera funkcje obsługi NULL. Funkcje w tej kategorii to NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

  • Case Conversion functions- akceptuje wprowadzanie znaków i zwraca wartość znakową. Funkcje w tej kategorii to UPPER, LOWER i INITCAP.

    • Funkcja UPPER konwertuje ciąg znaków na wielkie litery.

    • Funkcja LOWER konwertuje ciąg znaków na małe litery.

    • Funkcja INITCAP konwertuje tylko początkowe alfabety łańcucha na duże litery.

  • Character functions- Akceptuje wprowadzanie znaków i zwraca liczbę lub wartość znaku. Funkcje w tej kategorii to CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM i REPLACE.

    • Funkcja CONCAT łączy dwie wartości ciągów.

    • Funkcja LENGTH zwraca długość ciągu wejściowego.

    • Funkcja SUBSTR zwraca część ciągu od podanego punktu początkowego do punktu końcowego.

    • Funkcja INSTR zwraca numeryczną pozycję znaku lub ciągu w podanym ciągu.

    • Funkcje LPAD i RPAD dopełniają dany ciąg do określonej długości danym znakiem.

    • Funkcja TRIM przycina ciąg wejściowy od początku lub końca.

    • Funkcja REPLACE zamienia znaki z ciągu wejściowego na dany znak.

  • Date functions- Operacje arytmetyczne na datach zwracają datę lub wartości liczbowe. Funkcje w tej kategorii to MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND i TRUNC.

    • Funkcja MONTHS_BETWEEN zwraca liczbę miesięcy między dwiema datami.

    • Funkcja ADD_MONTHS dodaje „n” liczbę miesięcy do daty wejściowej.

    • Funkcja NEXT_DAY zwraca następny dzień podanej daty.

    • Funkcja LAST_DAY zwraca ostatni dzień miesiąca wprowadzonej daty.

    • Funkcje ROUND i LICZBA.CAŁK służą do zaokrągleń i obcięcia wartości daty.

  • Number functions- akceptuje dane numeryczne i zwraca wartości liczbowe. Funkcje w tej kategorii to ROUND, TRUNC i MOD.

    • Funkcje ROUND i LICZBA.CAŁK służą do zaokrąglania i obcinania wartości liczbowej.

    • MOD służy do zwracania pozostałej części operacji dzielenia między dwiema liczbami.

Ilustracje

Funkcje ogólne

Poniższe zapytanie SELECT demonstruje użycie funkcji NVL.

SELECT first_name, last_name, salary, NVL (commission_pct,0) 
FROM employees
WHERE rownum < 5;

FIRST_NAME           LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven               King                           24000                     0
Neena                Kochhar                        17000                     0
Lex                  De Haan                        17000                     0
Alexander            Hunold                          9000                     0

Funkcje konwersji wielkości liter

Poniższe zapytanie SELECT demonstruje użycie funkcji konwersji wielkości liter.

SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;

UPPER(FIRST_NAME)    INITCAP(LAST_NAME)        LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN               King                      ad_pres
NEENA                Kochhar                   ad_vp
LEX                  De Haan                   ad_vp
ALEXANDER            Hunold                    it_prog

Funkcje postaci

Poniższe zapytanie SELECT demonstruje użycie funkcji CONCAT do łączenia dwóch wartości ciągów.

SELECT CONCAT (first_name, last_name) 
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

Poniższe zapytanie SELECT demonstruje użycie funkcji SUBSTR i INSTR. Funkcja SUBSTR zwraca część ciągu wejściowego od pierwszej do piątej pozycji. Funkcja INSTR zwraca numeryczną pozycję znaku „a” w imieniu.

SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;

SUBST INSTR(FIRST_NAME,'A')
----- ---------------------
Ellen                     0
Sunda                     5
Mozhe                     0
David                     2

Poniższe zapytanie SELECT demonstruje użycie LPAD i RPAD do ładnego drukowania informacji o pracowniku i pracy.

SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;

RPAD(FIRST_NAME,10,'_')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROG

Funkcje liczbowe

Poniższe zapytanie SELECT demonstruje użycie funkcji ROUND i LICZBA.CAŁK.

SELECT ROUND (1372.472,1)
FROM dual;

ROUND(1372.472,1)
-----------------
           1372.5

SELECT TRUNC (72183,-2)
FROM dual;

TRUNC(72183,-2)
---------------
          72100

Operacje arytmetyczne na datach

Poniższe zapytanie SELECT pokazuje funkcję arytmetyczną daty, w której jest wykonywana różnica między datą zatrudnienia pracownika a datą sysdate.

SELECT employee_id, (sysdate - hire_date) Employment_days
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_DAYS
----------- ---------------
        100      3698.61877
        101      2871.61877
        102      4583.61877
        103      2767.61877

Funkcje daty

Poniższe zapytanie SELECT demonstruje użycie funkcji MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY i LAST_DAY.

SELECT employee_id, MONTHS_BETWEEN (sysdate, hire_date) Employment_months
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_MONTHS
----------- -----------------
        100        121.504216
        101        94.3751837
        102        150.633248
        103        90.9558289

SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate)
FROM dual;

ADD_MONTH NEXT_DAY( LAST_DAY(
--------- --------- ---------
01-JAN-14 05-AUG-13 31-AUG-13