Dane – typy i rodzaje
Ostatnio poznaliśmy narzędzie dla komunikowania się z bazą danych w ramach przydzielonego miejsca na serwerze uczelnianym. Zanim jednak przejdziemy do tworzenia naszej pierwszej tabeli musimy prócz syntaktyki i semantyki języka poznać informacje o danych z jakimi możemy mieć do czynienia w ramach Oracle SQL.
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. Najczęściej stosowanymi typami danych SQL w bazie danych Oracle są te wymienione i opisane poniżej.
- CHAR
- VARCHAR2
- Różnice pomiędzy CHAR a VARCHAR2
- NCHAR/NVARCHAR2
- LONG
- DATE
- TIMESTAMP
- Różnice pomiędzy DATE a TIMESTAMP
- INTERVAL
- NUMBER
- BLOB
- CLOB
- ROWID
- RAW
Typy znakowe (tekstowe)
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 6 znakami pozostałe znaki zostaną uzupełnione białymi znakami (standardowo spacjami) aż do wyczerpania szerokości znakowej pola. Co ważne CHAR domyślnie przyjmuje wartości w bajtach1Jednostka 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 (o ile mamy uprawnienia do tabeli przechowującej te dane). 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, można jednak podać typ danych CHAR czyli ilość znaków. Przykład zadeklarowania:
CHAR(5) - napis długości 5 bajtów CHAR(5 CHAR) - napis długości 5 znaków
Różnica między bajtem a znakiem nabiera znaczenia przy użyciu np. polskich znaków. I tak słowo “Łódź” zajmuje 4 znaki i 7 bajtów z uwagi na fakt, iż znaki narodowe kodowane są dwubajtowo. Więc do pierwszej kolumny się nie zmieści natomiast do drugiej już tak. 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.
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(5) - napis długości do 5 bajtów VARCHAR2(5 CHAR) - napis długości do 5 znaków
Podobnie jak w przypadku CHAR do pierwszej kolumny nie uda nam się wstawić słowa “Łódź” ponieważ zajmuje 4 znaki i 7 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.
NCHAR/NVARCHAR2
NCHAR/NVARCHAR2 przechowują łańcuchy znaków odpowiednio o stałej i różnej długości. Wykorzystują zestawy znaków języka narodowego, alternatywnego dla reszty bazy. Dane zapisane w Unicode (UTF8 i AL16UTF16).
LONG
LONG przechowuje do 2GB danych znakowych. Tego typu nie można stosować w klauzulach WHERE, ORDER BY, GROUP BY, DISTINCT. W tabeli może istnieć tylko jedna kolumna z tym typem danych. Alternatywa dla przechowywania dużych danych znakowych to typy CLOB i NCLOB.
Typy przechowujące datę
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:
DATE
W 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 SYSDATE 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
INTERVAL
INTERVAL przechowuje informacje o odcinku czasu między dwoma punktami czasowymi. Przykład zastosowania jest umieszczony tutaj.
Typy liczbowe
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 :)
INTEGER
INTEGER typ całkowity. Przykładami jego użycia są np.: nr indeksu, liczba godzin, klucze obce (fkID) itp.
FLOAT
FLOAT – to typ zmiennoprzecinkowy.
Typy obiektowe
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.
Inne typy danych
ROWID
ROWID – przechowuje łańcuchy znaków, do 18 znaków.
RAW
RAW(rozmiar) przechowuje dane binarne. Minimalny rozmiar to 1, a maksymalny to 2000 bajtów.
Podsumowanie
Baza danych Oracle i SQL mają następujące, najczęściej używane 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.
Lista artykułu ani podsumowania nie wyczerpała w opisie wszystkich dostępnych typów danych. Wszystkie typy opisane są w dokumentacji ORACLE pod tym adresem: ORACLE SQL Data Types DOCs.