Funkcje wbudowane SQL

W język SQL jest wbudowanych wiele gotowych funkcji, które pozwalają wykonać wiele użytecznych operacji na wartościach. Funkcje można używać w SQL wywołując je z pewnymi parametrami w poleceniu SELECT i innych poleceniach DML. Na podstawie otrzymanych parametrów funkcje te wykonują obliczenia i zwracają pewną wartość.

Wywołania funkcji można w sobie zagnieżdżać dowolną ilość razy, tzn. wartość zwrócona z wywołania jednej funkcji może być przekazana jako parametr do kolejnej funkcji, itd.

Poniżej znajduje się spis najczęściej używanych funkcji wbudowanych, podzielonych pod kątem typu wartości parametrów, na których operują.

Funkcje znakowe

lower(text), upper(text), initcap(text)

Funkje te zwracają text modyfikując odpowiednio wielkość liter.

lpad(text, n [,text1]), rpad(text, n [,text1])

Funkcje uzupełniają text dopisując text1 z lewej lub z prawej strony w taki sposób, aby napis text osiągnął szerokość n znaków. Jeżeli text1 nie jest podany, to text jest dopełniany spacjami.

ltrim(text [,text1]), rtrim(text [,text1])

Funkcje usuwają z prawej lub lewej strony napisu text znaki zawarte w text1. Jeżeli text1 nie jest wyspecyfikowany, to usuwane są spacje.

replace(text, text1 [,text2])

Funkcja podmienia wszystkie wystąpienia ciągu text1 w ciągu text na text2. W przypadku braku ciągu text2 z ciągu text usuwane są wszystkie wystąpienia ciągu text1.

translate(text, text1 ,text2)

Funkcja działa podobnie jak funkcja replace, tylko że zamienia wszystkie wystąpienia pojedynczych liter z ciągu text1 na odpowiednie litery z ciągu text2.

substr(text, m [,n])

Wycina z ciągu text n znaków począwszy od pozycji m. Jeżeli n nie jest podane, to wycina wszystkie znaki od pozycji m do końca ciągu text. Jeżeli m jest ujemne, to znaki są odliczane od końca ciągu text.

to_char(liczba [,wzorzec])

Zamienia liczbę na postać znakową według podanego wzorca. Wzorzec może być zbudowany np. z następujących elementów: 9,0,G,D

to_number(text [,wzorzec])

Zamienia ciąg znaków na liczbę według wzorca zbudowanego podobnie jak powyżej.

length(text)

Zwraca długość ciągu text, jeśli text ma wartość NULL funkcja zwraca NULL, a nie 0.

instr(text, text1 [,m,n])

Zwraca miejsce położenia n-tego wystąpienia ciągu text1 w ciągu text począwszy od pozycji m. Jeśli m jest ujemne znaki są liczone od prawej do lewej, domyślną wartością n i m jest 1. Jeżeli nie znaleziono ciągu funkcja zwraca wartość 0.

Przykład

SELECT substr(klt_imie, 1, 1) || substr(klt_nazwisko, 1, 1),
       substr(klt_tel, 1, 4),
       lpad(klt_email, 25)
  FROM klienci
 WHERE klt_typ = 'IND';
                        

Przykład

SELECT substr(tow_nazwa, 1, 15),
       to_char(tow_cena*(1+tow_podatek/100), '999G999G990D99')
  FROM towary;
                        

Przykład

SELECT initcap(replace(tow_nazwa, 'rz', 'XXXX'))
  FROM towary
 WHERE tow_cena < 2000;
                        

Przykład

SELECT *
  FROM towary
 WHERE length(tow_nazwa) > 12;
                        

Funkcje numeryczne

round(m [,n])

Zaokrągla m do n-tego miejsca po przecinku, ujemne n zaokrągla do n-tego miejsca przed przecinkiem, jeśli n nie podano n=0.

trunc(m [,n])

Obcina m do n-tego miejsca po przecinku, ujemne n zamienia na 0 n cyfr przed przecinkiem, jeśli n nie podano n=0.

power(m, n)

Podnosi m do potęgi n(n>=0,n<0).

sqrt(m)

Pierwiastek kwadratowy z liczby m.

sign(m)

Zwraca wartość 1, 0 lub -1 w zależności od znaku liczby m.

abs(m)

Zwraca wartość bezwzględną liczby m.

mod(m,n)

Zwraca resztę z dzielenia m przez n, jeśli n = 0 zwraca m.

sin(n), cos(n), tan(n), sinh(n), cosh(n), tanh(n)

Zwracają wartość funkcji trygonometrycznych, gdzie n podawane jest w radianach.

Przykład

SELECT tow_nazwa,
       to_char(tow_cena*(1+tow_podatek/100), '999G999G990D99')
  FROM towary
 WHERE tow_cena*(1+tow_podatek/100)
     - trunc(tow_cena*(1+tow_podatek/100)) = 0;
                        

Inne funkcje

decode(wyrażenie_testowane, wartość_A_1, wartość_B_1, wartość_A_2, wartość_B_2, ..., wartość_A_n, wartość_B_n, wartość_domyślna)

Funkcja testuje wartość wyrażenia testowanego i zwraca wartość_B_x w przypadku, gdy wyrażenie testowane ma wartość_A_x. Jeżeli żadne z wartości_A_x nie jest równe wartości testowanej, to funkcja zwraca wartość_domyślna.

Przykład

SELECT tow_nazwa,
       tow_cena,
       decode(tow_podatek,
              22, 'pełna stawka',
              7, 'ulgowa stawka',
              'brak podatku') podatki
  FROM towary;
                        

nvl(wyrazenie1, wyrażenie2)

Jeśli wyrażenie1 ma wartość NULL, to funkcja zwraca wartość wyrażenie2, w przeciwnym wypadku zwraca wartość wyrażenia1. Typy obu wyrażeń muszą być zgodne.

Przykład

SELECT tow_nazwa, nvl(tow_podatek, 0)
  FROM towary;
                        

Funkcje agregujące

Funkcje agregujące, to takie funkcje, które zwracają jedną wartość wyliczoną na podstawie wielu wierszy. Wszystkie funkcje grupowe ignorują wiersze zawierające wartość NULL w kolumnie, na której działają.

avg([DISTINCT] wyrażenie)

Funkcja oblicza wartość średnią wyrażenia dla wszystkich wierszy.

count([DISTINCT] {wyrażenie|* })

Funkcja zwraca ilość wierszy dla których wyrażenie jest różne od NULL. Użycie gwiazdki powoduje zliczenie wszystkich wierszy w tabeli.

max(wyrażenie), min(wyrażenie)

Funkcje obliczają maksymalną i minimalną wartość wyrażenia, wartość wyrażenia może być liczbą, ciągiem znaków lub datą.

sum([DISTINCT] wyrażenie)

Funkcja oblicza sumę wartości wyrażeń dla wszystkich wierszy.

stddev([DISTINCT] wyrażenie)

Funkcja oblicza odchylenie standardowe wartości wyrażeń dla wszystkich wierszy.

variance([DISTINCT] wyrażenie)

Funkcja oblicza wariancję wartości wyrażeń dla wszystkich wierszy.

Użycie klauzuli DISTINCT w powyższych funkcjach powoduje, że wiersze, dla których agregowane wyrażenie się powtarza, agregacji podlegają tylko jeden raz.

Jeżeli na liście SELECT występuje choć jedna funkcja agregująca, to nie mogą tam wystąpić żadne inne wyrażenia nie będące wywołaniem funkcji agregującej.

Przykład

SELECT max(tow_cena), min(tow_cena), max(tow_cena)-min(tow_cena)
  FROM towary;
                        

Przykład

SELECT count(*), count(tow_podatek), count(DISTINCT tow_cena)
  FROM towary;
                        

Daty i funkcje do manipulowania datami

Daty i czas w systemie bazy danych Oracle przechowywane są z dokładnością do jednej sekundy, zakodowane na siedmiu bajtach.

Bieżącą datę z serwera bazy danych można odczytać dzięki zmiennej SYSDATE.

Odczytać wartość tej zmiennej można najłatwiej korzystając z tabeli DUAL. Jest to bardzo specyficzna tabela dostępna każdemu użytkownikowi. Posiada ona jeden wiersz i jedną kolumnę, z której na dodatek się nigdy nie korzysta. Dzięki tej tabeli możemy korzystając ze składni języka SQL odczytać wartość np. czasu systemowego.

Przykład

SELECT sysdate
  FROM DUAL;
                        

Na datach można wykonywać niektóre operacje arytmetyczne. Do daty można dodawać i odejmować liczby. Wynikiem takiej operacji będzie data przesunięta o zadaną liczbę dni. Jeżeli chcemy zmodyfikować datę o np. dwadzieścia minut powinniśmy do modyfikowanej daty dodać lub odjąć liczbę 20/(24*60).

Przykład

SELECT SYSDATE + 10000
  FROM DUAL;
                        

Dwie daty można od siebie odjąć. Wynikiem takiej operacji jest liczba oznaczająca ilość dni pomiędzy tymi datami. Dwie daty można także porównywać. W przypadku porównywania dwu dat znaczenie ma nie tylko data zawarta w zmiennych typu data, ale także czas.

Funkcje manipulowania datami

months_between(data1,data2)

Zwraca ilość miesięcy pomiędzy datami.

add_months(data,n)

Dodaje do podanej daty n miesięcy.

last_day(data)

Zwraca datę ostatniego dnia miesiąca zawierającego podaną datę.

round(data [,{'MONTH'|'YEAR'])

Funkcja zaokrągla datę do północy, jeśli jest przed południem lub do północy dnia następnego, jeśli jest po południu. W przypadku podania dodatkowego parametru zaokrągla datę do pełnego miesiąca lub roku.

trunc(data[,{'MONTH'|'YEAR'}])

Funkcja działa podobnie jak powyższa, tylko że nie zaokrągla a obcina część daty.

to_date(text [,text1])

Zamienia ciąg znaków text na datę według wzorca zawartego w text1. Wzorzec pozwala poprawnie zinterpretować znaki zawarte w ciągu text.

to_char(date [,text1])

Zamienia datę na postać znakową według wzorca zawartego w ciągu text1.

Wzorce do konwersji pomiędzy tekstem i datą buduje się z pojedynczych składników, z których każdy ma swoje znaczenie. Poniżej przedstawiony został pełny zbiór tych składników.

Formaty wzorca

Użycie dużych lub małych liter wzorca wpływa na wynik funkcji, np. po użyciu wzorca 'day' możemy otrzymać napis „poniedziałek”, po użyciu wzorca 'DAY' otrzymamy napis „PONIEDZIAŁEK”, po użyciu wzorca 'Day' otrzymamy napis „Poniedziałek”.

Przykład

SELECT to_char(SYSDATE, 'yyyy.mm.dd hh24:mi:ss'),
       to_char(trunc(SYSDATE), 'yyyy.mm.dd hh24:mi:ss')
  FROM DUAL;
                        

Przykład

SELECT to_char(add_months(SYSDATE, -12), 'day')
  FROM DUAL;