Baza danych Oracle oraz język SQL posiadają własne typy danych. Typy danych różnią się między językami programowania nawet różnymi systemami baz danych. W tym kursie przedstawię najczęściej wykorzystywane typy danych SQL w bazie danych Oracle.
Ten kurs jest częścią cyklu Kurs SQL
Zapisz się na autorskie Szkolenie SQL
Aby sprawnie posługiwać się językiem SQL w Oracle należy poznać typy danych jakie w niej występują. Poniżej przedstawię specyfikę najpopularniejszych typów danych:
- CHAR
- VARCHAR2
- Różnice pomiędzy CHAR a VARCHAR2
- DATE
- TIMESTAMP
- Różnice pomiędzy DATE a TIMESTAMP
- NUMBER
- BLOB
- CLOB
Jeżeli jesteś początkujący w SQL to do zrozumienia tego kursu warto odwiedź wcześniejsze kurs:
CHAR
Pierwszym i podstawowym typem danych jest CHAR. Jest to typ danych znakowy o stałej określonej długości. Oznacza to, że typ ten służy do przechowywania ciągów znaków czyli napisów, liter, wyrazów, zdań etc. 🙂 Należy go zadeklarować wraz z długością. Jako, że jest to ciąg o stałej długości to jeżeli zadeklarujemy CHAR o długości 10 bajtów a wypełnimy go tylko 4 znakami pozostałe znaków 6 zostanie wypełnione białymi znakami. Co ważne CHAR domyślnie przyjmuje wartości w długości bajtów*, można jednak podać typ danych CHAR czyli ilość znaków. Przykład zadeklarowania:
CHAR(10) <- napis długości 10 bajtów
CHAR(10 CHAR) <- napis długości 10 znakówRóżnica między bajtem a znakiem nabiera znaczenia przy użyciu np. polskich znaków. I tak słowo “źródłowe” zajmuje 8 znaków i 11 bajtów. Więc do pierwszej kolumny się nie zmieści natomiast do drugiej już tak. Jako, że kolumny typu CHAR są stałej długości to np. słowo “król” zostanie zapisane w pierwszej kolumnie typu CHAR(10) jako “król ” natomiast w kolumnie CHAR(10 CHAR) jako “król “. Różnica jest w ilości spacji. Patrząc na ten typ danych można się zastanowić czy ma sens trzymanie napisu zawsze o stałej długości w postaci spacji i czy nie jest to przypadkiem marnotrawienie przestrzeni. W praktyce typ kolumny CHAR nie jest prawie w ogóle używany. W związku z specyfiką char powstał inny typ danych w Oracle: varchar2.
* Jednostka długości ciągu znaków jest przechowywana przez parametr NLS_LENGTH_SEMANTICS. Jej wartość można sprawdzić poleceniem: SHOW PARAMETER NLS_LENGTH_SEMANTICS. Domyślną konfiguracją bazy dla parametru NLS_LENGTH_SEMANTICS jest bajt dlatego też domyślnie ciągi znaków są długości bajtów
VARCHAR2
Kolejnym znacznie bardziej praktycznym i popularniejszym typem znakowym jest VARCHAR2. VARCHAR2 Jest to ciąg znaków o zmiennej długości. Również należy go zadeklarować wraz z długością jednak nie jest to stała długość a maksymalna długość ciągu. Nie występuje w tym przypadku wypełnienie pozostałej przestrzeni spacjami. I tak przykładowo możemy zadeklarować kolumnę typu VARCHAR2 jak poniżej:
VARCHAR2(10) <- napis długości do 10 bajtów
VARCHAR2(10 CHAR) <- napis długości do 10 znakówPodobnie jak w przypadku CHAR do pierwszej kolumny nie uda nam się wstawić słowa “źródłowe” ponieważ zajmuje 8 znaków i 11 bajtów. Jednak wstawiając słowo “król” w obu kolumnach przetrzymywana będzie wartość “król” bez spacji. I tak wartość “król” w obu kolumnach jest tym samym ciągiem bo nie występuje różnica w białych znakach. Maksymalna zadeklarowana długość kolumny VARCHAR2 to 2000 znaków lub 4000 bajtów. Typ VARCHAR2 jest najpopularniejszym i najczęściej wykorzystywanym typem danych dla kolumn znakowych w SQL i bazie Oracle .
CHAR vs VARCHAR2
Różnica między CHAR a VARCHAR2 jest tak, że pierwszy zawsze jest stałej długości i niewykorzystane miejsce jest zapełniane białymi znakami. VARCHAR2 jest zmiennej długości co oznacza, że słowo “król” to “król” a nie “król “. Korzystanie z VARCHAR2 jest prawie zawsze bardziej zasadne niż z CHAR.
DATE
Kolejnym ważnym typem danych jest DATE. Jest do typ danych który przechowuje datę rozumianą jako rok, dzień, miesiąc, godzinę, minutę i sekundę. Nie przechowuje natomiast milisekund czy strefy czasowej. Aby utworzyć kolumnę o typie datowym należy podać słowo kluczowe:
DATEW Oracle SQL typ DATE jest dedykowany do przechowywania dat. Jest bardzo często stosowany. Oracle pozwala konwertować DATE do innych formatów np. do ciągu znaków. W bazie Oracle istniej domyślny aktualny typ danych SYSDATE który zwraca aktualny czas DATE.
Aby zobaczyć aktualną wartość parametru SYSDATE oraz SYSDTAE wraz z maską należy wykonać poniższe zapytanie:
select
sysdate
, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') date_z_maska
from dual;oraz wynik:
TIMESTAMP
Nieco bardziej zaawansowanym i dokładnym typem danych przechowującym datę wraz z czasem jest TIMESTAMP. Typ danych TIMESTAMP jest rozszerzeniem typu danych DATE. Przechowuje rok, miesiąc, dzień, godziny, minuty, sekundy oraz części sekund i strefę czasową. Ten typ danych jest przydatny do przechowywania dokładnych wartości czasu. Aby utworzyć kolumnę typu TIMESTAMP należy podać frazę:
TIMESTAMP([precyzja_części_sekund])Precyzja sekund jest parametrem mówiącym ile części sekund przechowywać będzie wartość. Domyślnie określona jest na wartość 6 natomiast może zawierać się w przedziale od 0 do 9. W bazie Oracle istniej domyślny aktualny typ danych SYSTIMESTAMP który zwraca aktualny czas TIMESTAMP.
Aby zobaczyć aktualną wartość parametru SYSTIMESTAMP oraz SYSTIMESTAMP wraz z maską należy wykonać poniższe zapytanie:
select
systimestamp
, to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF9 TZR') czas_z_maska
from dual;oraz przykładowy wynik:
DATE vs TIMESTAMP
Typ danych DATE przechowuje wartość czasu do sekund natomiast TIMESTAMP przechowuje te same wartości co DATE rozszerzone o części sekund do 9 miejsc po przecinku wraz z strefą czasową. Przykładową różnicę pomiędzy DATE i TIMESTAMP pokazują poniższe wartości:
13-MAR-20 21.05.49.000000000 <- typ danych DATE 13-MAR-20 21.05.49.116074000 <- typ danych TIMESTAMP a po nałożeniu maski: 2020-03-13 21:05:49 <- typ date 2020-03-13 21:05:49.116074000 -04:00 <-typ timestamp
NUMBER
Do tej pory poznaliśmy dwie grupy typów danych. Dane znakowe oraz datowe. Kolejnym bardzo ważnym typem danych jest NUMBER który służy jak nazwa wskazuje do przechowywania wartości numerycznych. W kolumnie NUMBER możemy przechowywać zarówno liczby całkowite jak i zmiennoprzecinkowe. Maksymalna długość NUMBER to 38 cyfr.. Przy deklarowaniu kolumny typu NUMBER należy podać jej precyzję jak na przykładzie poniżej:
NUMBER(ilość_cyfr_znaczących, ilość_cyfr_po_przecinku) np. Dla wartości: 7456123.89 NUMBER <-- 7456123.89 NUMBER(*,1) <-- 7456123.9 NUMBER(9) <-- 7456124 NUMBER(9,2) <-- 7456123.89 NUMBER(6) <-- błąd, zbyt mały number aby pomieścić taką wartość NUMBER(7,-2) <-- 7456100
NUMBER jest bardzo często wykorzystywanym typem danych.
Ciekawostka:
Wartość NUMBER to 38 znaków przy czym zakres wartości dla liczb dodatnich jest od 1 x 10-130 do 9.99…9 x 10125 natomiast dla wartości ujemnych 1 x 10-130 do 9.99…99 x 10125. Co to oznacza? Oznacza to, że można w NUMBER przechowywać liczby większe niż 38 znakowe ale tylko 38 znaków może przechowywać cyfry od 0-9 natomiast pozostałe są zerami i tak dla przykładu:
NUMBER(1, 50) może przechowywać wartość: 0.00000000000000000000000000000000000000000000000001 (49 zer i jedynka) Co ważne, przy tym typie danych liczby 0.00000000000000000000000000000000000000000000000001 i 0.0000000000000000000000000000000000000000000000000111 dają tą samą liczbę równą 0.00000000000000000000000000000000000000000000000001 :)
BLOB
BLOB czyli ang. Binary Large Object służy do przechowywania dużych plików binarnych takich jak najróżniejsze: zdjęcia, dokumenty, czy jakiekolwiek inne formaty plików. Maksymalna wielkość tego pola to 128 terabajtów 🙂 Przechowywanie tak dużych plików w bazie zazwyczaj nie jest optymalnym rozwiązaniem dlatego w praktyce raczej stosuje się z koncepcję, że w bazie zapisujemy jedynie ścieżkę do pliku natomiast sam plik znajduje na jakimś zasobie np. ftp.
Typ ten nie jest wykorzystywany ale warto wiedzieć, że występuje.
CLOB
CLOB czyli ang. Character Large Object służy do przechowywania dużych plików tekstowych. Jako, że inne typy znakowe jak CHAR czy VARCHAR ograniczone są do 4000 bajtów to CLOB często wykorzystywany jest do przechowywania dłuższych ciągów znaków. maksymalna wielość tego pola to również 128 terabajtów 🙂
Typ często wykorzystywany gdy potrzebujemy przechowywać dłuższe ciągi znaków niż 4000 bajtów lub 2000 znaków.
Podsumowanie
- Baza danych Oracle i SQL mają własne typy danych
- CHAR — ciąg znakowy o stałej długości.
- VARCHAR2 — ciąg znaków o zmiennej długości
- DATE — typ danych do przechowywania daty z dokładnością do sekund.
- TIMESTAMP — typ danych do przechowywania szczegółowej informacji o dacie z dokładnością do części sekund i strefie czasowej
- NUMBER — Typ danych numeryczny
- BLOB — Wielkie pliki
- CLOB — Ciągi znaków dłuższe niż 4000 bajtów lub 2000 znaków. Krótsze lepiej trzymać w VARCHAR2
W kolejnym kursie opiszę jak wykorzystać typy danych m.in. przy tworzeniu tabel: ORACLE DROP | CREATE TABLE