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 0Funkcje 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_progFunkcje 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
DavidAustinPoniż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 2Poniż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_PROGFunkcje 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)
---------------
72100Operacje 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.61877Funkcje 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