#261
Część IV
Zagadnienia praktyczne

#263
Rozdział 12.
Typy danych

Jednym Z najtrudniejszych zadań związanych z obsługą baz danych jest sprawne operowanie wszelkimi typami danych, jakie mogą występować w bazie. Z każdym typem danych wiążą się pewne określone komplikacje, na które trzeba zwracać uwagę podczas programowania. Celem tego rozdziału jest dostarczenie wskazówek dotyczących najlepszych metod przechowywania i modyfikowania danych.
Relacyjne bazy danych oferują bardzo zaawansowane techniki definiowania typów danych - pola bazy danych można skonfigurować w taki sposób, że będą one przechowywać dane ściśle określonego typu. Typy te pozwalają na przyjęcie pewnych założeń odnośnie rodzaju informacji przechowywanych w polach tabel. Nie trzeba zastanawiać się na tym, czy możliwe jest odjęcie wartości z jednego pola numerycznego od wartości z innego pola tego typu - oba te pola muszą zawierać liczby. Dowolna wartość w polu typu datę może być porównana z bieżącą datą, bez potrzeby stosowania innych metod porównania (np. bazujących na typie łańcuchowym). Niektóre języki, w tym również przeznaczone do programowania aplikacji komunikujących się z bazami danych, oferują bardzo słabe rozróżnienie między typami danych (ang. weak data typing). Języki te najczęściej nie rozpoznają różnych typów danych - oznacza to, że w tej samej zmiennej można z łatwością zapisać datę, wartość liczbową lub łańcuch. Konsekwencją tego jest brak ograniczenia pewnych operacji (np. arytmetycznych) do określonych typów wartości - operacja zastosowana w niewłaściwy sposób spowoduje błąd lub zwróci bezsensowny wynik.
Analogicznie, określając rozmiary pól na etapie tworzenia tabel, można określić dopuszczalną wielkość wartości dla każdego z pól. Jeżeli typ danych dla pól kolumny zostanie ustawiony na CHAR (10), możemy być pewni, że każda wartość w tej kolumnie będzie miała długość dziesięciu znaków. Gdy w polu umieszczona zostanie wartość składająca się z mniejszej ilości znaków, łańcuch zostanie uzupełniony spacjami tak, aby w efekcie osiągnąć długość nominalną.
Decyzje o wykorzystaniu tych cech podejmuje się na etapie projektowania bazy danych. Nie istnieją żadne zasady, pomijając oczywiście zdrowy rozsądek, które nakazywałyby umieszczanie danych w polach o odpowiednim typie. Bazę danych można z łatwością zbudować posługując się wyłącznie polami typu VARCHAR (255). Rozwiązanie takie jednak bardzo szybko obróci się przeciwko jego twórcy.
#264
Aby móc wykorzystać pola w wyrażeniach matematycznych lub sortować je numerycznie (w przeciwieństwie do sortowania alfabetycznego), muszą one zostać utworzone jako pola numeryczne. Ponadto nie istnieje żaden sensowny sposób porównywania dat w sytuacji, kiedy nie są one zapisywane w polach typu DATĘ.
Wiesz już zatem, że projektując swoją bazę danych będziesz zwracał baczną uwagę na to, jaki typ danych przypisać każdej z kolumn. Pozostaje pytanie, jak najlepiej wykorzystać dane w zapytaniach? W dalszej części tego rozdziału opiszemy, w jaki sposób dokonuje się konwersji typów danych, wyjaśnimy, jak należy formatować dane, aby można było wstawić je do kolumny o określonym typie, a także omówimy niektóre z ograniczeń różnych typów danych.

Dane numeryczne

Mimo że wszystkie pola numeryczne przechowują ten sam podstawowy typ informacji - liczby - większość baz danych oferuje kilka numerycznych typów danych, które można nadawać kolumnom tabel. Rozróżnienie w przypadku niektórych pól numerycznych jest uzasadnione, dotyczy to np. liczb całkowitych i zmiennoprze-cinkowych. Inne typy danych, jak np. MONEY, istnieją wyłączanie dla wygody programistów. Pole MONEY jest zwykłym polem zmiennoprzecinkowym, którego zawartość jest wyświetlana zawsze z precyzją dwóch cyfr po przecinku.
Weźmy następująca listę numerycznych typów danych:

* DECIMAL.
* FLOAT.
* INTEGER.
* NUMBER.
* SMALLINT.

Jest to lista numerycznych typów danych rozpoznawanych przez bazy Oracle. W rzeczywistości wszystkie te typy są synonimami typu NUMBER lub jego nieznacznymi modyfikacjami. Inne bazy danych definiują własne numeryczne typy danych o odmiennych nazwach i właściwościach w porównaniu do typów wymienionych powyżej.

Sortowanie liczb

Porządkowanie wyników zapytania w oparciu o kolumnę zawierającą liczby sprawia, że rekordy sortowane są w zwykłym porządku numerycznym. Przykładowy wynik zapytania tego typu przedstawia listing 12.1. Zapytanie zwraca listę filmów, posortowaną według wartości z kolumny budget.
#265
--------------------------------
Listing 12.1. Wyniki zapytania posortowane według wartości pola numerycznego

SELECT movie_title, budget
FROM Movies
ORDER BY budget

MOVIE_TITLE BUDGET
Prince Kong 3.25
SQL Strikes Back 5
Bili Durham 10.1
The Code Warrior 10.3
Codependence Day 15
Vegetable House 20
The Linux Flles 22.2
The Programmer 50
The Rear Windows 50
Hard Code 77

10 rows selected
--------------------------------

Rezultaty zapytania są sortowane w porządku numerycznym na podstawie wartości kolumny budget. Zastanówmy się teraz, jak posortowana zostałaby ta sama lista liczb, gdyby znajdowały się one w polach tekstowych, np. typu VARCHAR. Klauzula ORDER BY posortowałaby te wartości w sposób alfabetyczny, a nie numeryczny. Oto, jak wygląda przykładowy ciąg liczb posortowanych alfabetycznie:

1
10
11
2
3
35
4

Grupowaniu podlegają wszystkie liczby rozpoczynające się od tej samej cyfry, podobnie jak w porządku alfabetycznym grupowane są wszystkie wyrazy zaczynające się na tę samą literę. Stwarza to ciekawy problem, kiedy liczby występują w łańcuchach zawierających również zwykłe znaki. Przykładowo, gdyby filmy z cyklu Rocky były tworzone aż do roku 2025, ich lista uporządkowana w sposób alfabetyczny wyglądałaby następująco:

Rocky
Rocky 10
Rocky 11
Rocky 12
Rocky 2
Rocky 3
Rocky 4

Rozwiązanie tego problemu polega na wprowadzeniu dodatkowego pola sortowania do rekordów zawierających kombinacje liter i cyfr. Ustalenie prawidłowego porządku sortowania tabeli jest już wtedy tylko kwestią wpisania odpowiedniej liczby w pole sortujące każdego rekordu. Rozwiązanie takie jest szczególnie użyteczne w sytuacji, kiedy kolejność wyznaczana jest przez liczby występujące przed i za miejscem dziesiętnym. Za przykład weźmy kolejność rysunków w tej książce. Numer każdego rysunku wyznacza w pierwszej kolejności numer rozdziału, a następnie kolejny numer rysunku wewnątrz niniejszego rozdziału.
#266
Liczby tego typu nie mogą być poprawnie posortowane w sposób alfabetyczny ani też numeryczny. Jeżeli spróbujemy sortowania alfabetycznego, wynik będzie następujący:

12.1
12.10
12.2
12.20
12.21
12.21
12.3
12.4

Sortowanie numeryczne przyniesie taki sam efekt, ponieważ liczba 12.21 jest większa od 12.20, ale jednocześnie jest mniejsza od 12.3. Aby liczby zostały zapisane w odpowiednim porządku, zmianie musiałby ulec ich wygląd (potrzebna byłaby zatem oddzielna kolumna, w której można byłoby je zapisać):

12.01
12.02
12.03
12.04
12.10
12.20

Liczby i wartość null

Jak wiadomo, null jest odpowiednikiem wartości pustej. Z tego powodu pojawienie się (jednej lub kilku) wartości tego typu w wyrażeniu matematycznym powoduje, że staje się ono niepoprawne. Za każdym razem, kiedy wartość null pojawi się w wyrażeniu matematycznym, wynik tego wyrażenia przyjmuje wartość null, ponieważ nie istnieje sposób pozwalający włączyć wartość pustą w tok obliczeń (bez ingerencji z zewnątrz). Na temat wartości typu null pisaliśmy w rozdziale 5.
Istnieją sytuacje, w których chcielibyśmy przyjąć pewne założenie co do wartości reprezentowanej przez null w chwili wykonania zapytania, umożliwiając tym samym zwrócenie przez wyrażenie matematyczne wartości rzeczywistej. Bazy danych wszelkiego typu umożliwiają, w ten lub inny sposób, zastępowanie wartości null określonymi wartościami rzeczywistymi.
Standard SQL-92 zakłada możliwość zastępowania wartości pustych przy użyciu funkcji COALESCE (). Funkcja ta przyjmuje dwa argumenty: pole, które może zawierać wartość pustą oraz wartość, która powinna być podstawiona w jej miejsce. Odpowiednikiem funkcji COALESCE () w systemach Oracle jest NVL () (przyjmuje ona takie same argumenty i działa w taki sam sposób, różni się jedynie nazwą).
Spójrzmy na praktyczny przykład, w którym użyta może zostać jedna z funkcji: COALESCE () lub NVL(). Zanim film zostanie wprowadzony na rynek, przychód brutto z jego sprzedaży jest nieokreślony (null). Aby otrzymać średni dochód brutto ze sprzedaży filmów w bazie danych, należy wykonać zapytanie przedstawione w listingu 12.2.
--------------------------------
Listing 12.2. Zapytanie zwracające średnią �wartość przychodu za sprzedane filmy

SELECT AVG(gross)
FROM Movies
WHERE gross IS NOT NULL
#267
AVG(GROSS)
-------------
24.7625
--------------------------------

Niestety, istnieją takie sytuacje, w których powyższe zapytanie będzie nieprzydatne, ponieważ do średniej brane są tylko filmy już wypuszczone na rynek. Jeżeli wartość średnia ma uwzględniać również filmy oczekujące dopiero na wypuszczenie, trzeba obowiązkowo posłużyć się jedną z funkcji zastępujących wartości null. Przykład takiego rozwiązania pokazuje listing 12.3.
--------------------------------
Listing 12.3. Zapytanie korzystające z funkcji zastępującej wartość null w celu zwrócenia wartości rzeczywistej z funkcji AVG()

SELECT AVG(NVL(gross,0) )
FROM Movies

AVG(NVL(GROSS,0) )
-------------------
19.81
--------------------------------

Jak widać, wyniki w obu przypadkach są różne. Suma dochodów brutto w pierwszym przypadku jest dzielona przez 8, podczas gdy w drugim przypadku przez 10. Podmiana wartości typu null pozwala na dołączenie wszystkich filmów do funkcji agregującej bez obawy, że zwrócona zostanie wartość pusta.

Wyrażenia matematyczne

W rozdziale 5. Wyjaśniliśmy, w jaki sposób proste wyrażenia matematyczne mogą być włączane do listy argumentów instrukcji SELECT oraz klauzuli WHERE. W rozdziale 7. poszerzyliśmy to zagadnienie o funkcje agregujące. Już wtedy zdałeś sobie zapewne sprawę, że możliwości matematyczne SQL-a wykraczają znacznie poza zakres pokazany w obu tych rozdziałach. Oprócz czterech podstawowych operacji matematycznych udostępnianych przez język SQL, istnieje ponadto pewien zbiór funkcji, które można wykorzystać w wyrażeniach matematycznych. Liczba dostępnych funkcji tego typu jest różna dla różnych systemów baz danych, faktem jest jednak, iż implementacje SQL-a dostarczają ich całe mnóstwo.

Sposób wywoływania funkcji matematycznych nie różni się niczym od wywołań

zwykłych funkcji. Funkcję umieszcza się na liście argumentów instrukcji SELECT lub za klauzulą WHERE. Argumenty funkcji wpisywane są w nawiasach bezpośrednio za j ej nazwą.
Przykładowo, aby wyciągnąć pierwiastek kwadratowy z przychodu brutto każdego filmu w zapytaniu, trzeba skorzystać z funkcji SQRT ( ) , tak jak pokazuje to listing 12.4.
--------------------------------
Listing 12.4. Wyrażenie z funkcją matematyczną na liście argumentów instrukcji SELECT

SELECT movie_title, SORT(gross)
FROM Movles
WHERE studio_id = 1
#268
MOVIE_TITLE SQRT(GROSS)
--------------------------------
Vegetable House 5.41722558
Codependence Day.. 5.47722558
The Rear Windows 4.18330013
--------------------------------

Powyższy przykład jest może trochę mało praktyczny, ale za to doskonale pokazuje działanie funkcji wywołanej w zapytaniu. Funkcje matematyczne mogą być zagnieżdżane, dopuszcza się także wywoływanie ich wielokrotnie w tym samym wyrażeniu. Przyjrzyjmy się zapytaniu w listingu 12.5.
--------------------------------
Listing 12.5. Zapytanie zawierające zagnieżdżone wywołania funkcji matematycznych

SELECT movie_title, SQRT (POWER (gross, 2) )
FROM Movies
WHERE studio id = 1
MOVIE_TITLE SQRT(POWER(gross,2))
--------------------------------
Vegetable House 30
Codependence Day... 30
The Rear Windows 17.5
--------------------------------

W tym przykładzie wywołanie funkcji POWER () zostało zagnieżdżone w wywołaniu funkcji SQRT (). Funkcja POWER () wymaga dwóch argumentów: podstawy i wykładnika. Patrząc na wyniki zapytania, łatwo można wywnioskować, że wyrażenie matematyczne podnosi dochód z filmu do kwadratu, a następnie wyciąga pierwiastek z tej wartości. W przypadku zagnieżdżenia funkcji są one wyliczane od środka na zewnątrz. Zatem w pierwszej kolejności wykonana została operacja potęgowania, a później pierwiastkowania.
Pojedyncze wyrażenie może zawierać wiele wywołań funkcji, które dodatkowo mogą być ze sobą łączone operatorami matematycznymi. Załóżmy, że mamy tabelę składającą się z dwóch kolumn, z których każda przechowuje długość jednego z krótszych boków trójkąta prostokątnego. Stosując zapytanie z listingu 12.6 (oparte na znanym ze szkoły twierdzeniu Pitagorasa), możemy wyznaczyć długość trzeciego boku.
--------------------------------
Listing 12.6. Zapytanie wyznaczające najdłuższy bok trójkąta prostokątnego w oparciu o twierdzenie Pitagorasa

SELECT side_one, side_two,
SQRT(POWER(side_one, 2) + POWER(side_two)) AS length
FROM triangles

SIDE_ONE SIDE_TWO LENGTH
--------------------------------
10 15 18.0277564
4 6 7.21110255
5 3 5.83095189
--------------------------------

Standardowe funkcje matematyczne

Pokazaliśmy użycie dwóch funkcji matematycznych: SQRT () i POWER () .Większość implementacji SQL-a oferuje ponadto wiele innych funkcji tego typu.
#269
Funkcjami matematycznymi, z którymi zdążyłeś się już zaznajomić, są funkcje agregujące (omawiane w rozdziale 7.): MIN (), MAX (}, SUM (), AVG () i COUNT (). Pozostałe (aczkolwiek nie wszystkie) funkcje matematyczne zostały zestawione w tabeli 12.1. Należy pamiętać o tym, że pewne bazy danych mogą nie obsługiwać całego zestawu , ; poniższych funkcji. O tym, czy dana funkcja ma zastosowanie w bazie danych można się dowiedzieć zaglądając do dokumentacji produktu.

Tabela 12.1. Funkcje matematyczne języka SQL

Funkcja; Działanie
ABS(expr); Zwraca wartość absolutną argumentu expr. Zarówno ABS(-5) iABS(5) zwracają wartość 5

CEIL(expr); Zwraca najmniejszą liczbę całkowitą większą lub równą argumentowi expr.CEIL (1.1) zwraca 2, podczas gdy CEIL (1) zwraca 1.CEIL (-1.1) zwraca -1. (Bazy danych korzystających z Transact-SQL, funkcja ta nosi nazwę CEILING ())

cos(expr); Funkcja trygonometryczna zwracająca cosinus argumentu expr wradianach.

COSH (expr); Funkcja trygonometryczna zwracająca cosinus hiperboliczny kąta reprezento-
wanego przez argument expr

EXP ([expr ]); EXp () zwraca wartość liczby e (2.71828...). Jeżeli funkcja wywołana zostanie
z argumentem, zwracana jest liczba e podniesiona do potęgi expr

FLOOR (expr); Zwraca największą wartość całkowitą mniejszą lub równą argumentowi expr.
FLOOR (1.1 zwraca 1, FLOOR (-1.1) zwraca -2, a FLOOR (1) zwraca 1

LN (expr); Zwraca logarytm naturalny z expr. LN (5) zwraca 1.60943791

LOG(base, expr); Zwraca logarytm o podstawie base z liczby expr.LOG (10, 5) zwraca0.698970004

MOD (expr,dzielnik); Funkcja modulo. Zwraca resztę dzielenia liczby expr przez dzielnik,
MOD(5, 3) zwraca 2

PI (); Wartość pi z szesnastoma miejscami po przecinku
POWER (expr,exponent); Funkcja wykładnicza; podnosi expr do potęgi exponent.POWER(2,3)zwraca 8

SIGN(expr); Zmienia znak expr

SIN (expr); Funkcja trygonometryczna zwracająca sinus argumentu expr w radianach

SINH (expr); Funkcja trygonometryczna zwracająca sinus hiperboliczny kąta reprezentowa-
nego przez argument expr

SQRT (expr); Zwraca pierwiastek kwadratowy z expr

TAN (expr); Funkcja trygonometryczna zwracająca tangens argumentu expr w radianach

TANH (expr); Funkcja trygonometryczna zwracająca tangens hiperboliczny kąta reprezento-
wanego przez argument expr
#270
Formatowanie liczb

Liczby zapisywane w bazie danych czasami posiadają dokładność znacznie większą od tej, która jest nam potrzebna podczas tworzenia raportów. Bazy danych operują na długich liczbach w celu zapewnienia odpowiedniej dokładności obliczeń. Dla przykładu typ NUMBER w systemie Oracle 8 obsługuje liczby o długości do 40 cyfr.
Większość baz danych dysponuje funkcją ROUND (), która umożliwia zmniejszanie precyzji wyświetlanych liczb. Oracle udostępnia ponadto funkcję TRUNC (), która obcina liczbę bez jej zaokrąglania. Obie funkcje używane są w jednakowy sposób, a jedyna różnica między nimi dotyczy operacji przeprowadzanych na liczbie przed jej wyświetleniem. Składnia funkcji wygląda następująco:

ROUND(val, precyzja)
TRUNC(val, precyzja)

Obie funkcje zachowują się w różny sposób, w zależności od tego, czy wartością argumentu precyzja jest liczba dodatnia, czy też ujemna. W przypadku wartości dodatniej liczba zaokrąglana jest do liczby miejsc po przecinku odpowiadającej wartości argumentu precyzja. Weźmy dla przykładu zapytanie z listingu 12.7.
---------------------------------
Listing 12.7. Zapytanie korzystające z funkcji ROUNDQdo zaokrąglania liczb po przecinku

SELECT ROUND(4.5, 2), ROUND(4.568137, 2)
FROM DUAL

ROUND(4.5, 2) ROUND(4.568137, 2)
---------------------------------
4.5 4.57
---------------------------------

Liczba z sześcioma miejscami po przecinku jest zaokrąglana do dwóch miejsc. Natomiast pierwszy element instrukcji SELECT, 4.5, pozostaje niezmieniony, ponieważ część ułamkowa już w chwili wywołania funkcji składa się z mniej niż dwóch cyfr. Warto zauważyć, że liczba 4.568137 została zaokrąglona w górę, czyli tak jak należy. Dla porównania spójrz teraz na listing 12.8 zawierający zapytanie z wywołaniem funkcji TRUNC ().
---------------------------------
Listing 12.8. Zapytanie korzystające z funkcji TRUNCO do obcięcia liczby po przecinku

SELECT TRUNCI4.568137, 2)
FROM DUAL

TRUNC(4.568137, 2)
---------------------------------
4.56
---------------------------------

Funkcja TRUNC () najzwyczajniej w świecie obcina cyfry znajdujące się poza zakresem wyznaczonym przez argument precyzja.
================
rADA
Oracle przestrzega standardu amerykańskiego, zgodnie z którym wartości kończące się liczbą 5 są zaokrąglane w górę. Innymi słowy, funkcja ROUND (5.5,0) zwraca wartość 6, a nie 5.
================
#271
Kiedy drugim argumentem funkcji ROUND () lub TRONC () jest liczba ujemna, zaokrąglanie lub obcinanie przeprowadzane jest na lewo od przecinka, łącznie z wszystkimi cyframi występującymi po prawej stronie miejsca dziesiętnego. Operacje tego typu przedstawione zostały w listingu 12.9.
---------------------------------
Listing 12.9. Funkcje ROUNDQ i TRUNC() zaokrąglające liczby na lewo od przecinka

SELECT ROUND(175, -2), TRUNC (175, -2)
FROM DUAL

ROUND(175, -2) TRUNCCL75, -2)
--------------------------------- 200 100

SELECT ROUND(15.1, -1), TRUNC(15.1, -1)
FROM DUAL

ROUNDU5.1, -1) TRUNC (15.1, -1)
---------------------------------
20 10
---------------------------------

Wynik drugiego zapytania z listingu 12.9 pokazuje wyraźnie, iż zastosowanie ujemnej "precyzji" powoduje wyzerowanie nie tylko określonej liczby cyfr po lewej stronie przecinka, ale także wszystkie cyfry na prawo od niego. W przypadku użycia funkcji TRUNC (), pewna liczba cyfr, wyspecyfikowana przez drugi argument, zostaje po prostu zastąpiona zerami. ROUND () zaokrągla cyfry, które są kolejno eliminowane.

Dane łańcuchowe

W SQL-u istnieją dwa typy danych łańcuchowych: VARCHAR i CHAR. Różnica między nimi polega na tym, że pola YARCHAR zajmują tylko tyle miejsca w pamięci i na dysku, ile wynika z ich faktycznego rozmiaru. Pola CHAR z kolei zajmują zawsze stałą liczbę bajtów, zdefiniowaną w chwili utworzenia pola. Podczas porównywania i modyfikowania łańcuchów oba typy pól zachowują się identycznie. SQL nie posiada żadnych specjalizowanych typów danych, które są w stanie przechowywać bardzo długie łańcuchy, jak np. GLOB w systemach Oracle lub TEXT w produkcie firmy Microsoft - SQL Server. Typami tymi zajmiemy się w dalszej części rozdziału, gdyż mają one niewiele wspólnego z typami CHAR i VARCHAR.

Konkatenacja łańcuchów

Wszystkie bazy danych udostępniają operator konkatenacji, który umożliwia traktowanie kilku łańcuchów jako jednej całości. W bazach danych Oracle operator ten ma postać dwóch znaków pipeline (||), podczas gdy Transact-SQL (używany w bazach danych Sybase oraz w MS SQL Server) stosuje do tego samego celu znak plus (+). Oba operaty działają w taki sam sposób. Listing 12.10 pokazuje przykład użycia operatora konkatenacji do stworzenia nagłówka adresu pocztowego.
#272
---------------------------------
Listing 12.10. Konkatenacja lańcóuchów

SELECT person_fname|| ' ' ||person_lnarae AS mail_name
FROM People

MAIL_NAME
--------------
Jeff Price
Chuck Peterson
Brandon Brooks
Brian Smith
Paul Monk
Reece Randall
Peter Jong
Maggie Davis
Becky Orvis
Carol Delano
FraN Friend

11 rows selected
---------------------------------

W powyższym listingu pola person_fname i person_lname są łączone z środkowym łańcuchem zawierającym pojedynczą spację. Konkatenacja łańcuchów może zostać użyta w dowolnym wyrażeniu zawierającym łańcuchy.
Przykładem niech będzie użycie połączonych łańcuchów w porównaniu klauzuli WHERE zapytania, jak pokazuje to listing 12.11.
---------------------------------
Listing 12.11. Użycie konkatenacji łańcuchów w klauzuli WHERE

SELECT person_ld, person_fname, person_lname
FROM people
WHERE person_fname || ' ' || person_lname = 'Jeff Price'

PERSON_ID PERSON_FNA PERSON_LNA
---------------------------------
1 Jeff Price
---------------------------------


Funkcje łańcuchowe

Relacyjne bazy danych oferują szereg funkcji przeznaczonych do manipulowania łańcuchami w celu porównań, a także do formatowania ich. Na nieszczęście, nazwy tych funkcji mogą różnić się między sobą w sposób znaczący, w zależności od implementacji bazy danych.
Dobrą no winą jest fakt, iż różnice te mają zazwyczaj charakter powierzchowny. Na przykład większość baz danych udostępnia funkcję zwracającą długość łańcucha (liczoną w znakach). W Oracle'u funkcja ta nosi nazwę LENGTH (), z kolei w Tran-sact-SQL jej nazwa to CHAR_LENGTH ().
Pomijając nazwy, obie funkcje działają identycznie - przyjmują argument w postaci wyrażenia będącego łańcuchem, a następnie zwracają liczbę całkowitą określającą liczbę zajmowanych przez niego bajtów.
#273
Przeszukiwanie łańcuchów

Większość relacyjnych baz danych udostępnia metodę pozwalającą sprawdzić, czy określony łańcuch znajduje się wewnątrz innego łańcucha. Można np. sprawdzić, czy łańcuch Col występuje wewnątrz łańcucha Rafę Colburn. W Oracle'u funkcją przeznaczoną do tego celu jest INSTR (), a jej składnia wygląda następująco:

INSTR(string1, string2 [, start [, wystąpienie ]])

string1 jest łańcuchem przeszukiwanym, a string2 łańcuchem, którego szukamy. Argument start jest pozycją w łańcuchu, od której należy rozpocząć przeszukiwanie, natomiast liczba reprezentowana przez wystąpienie określa, które wystąpienie łańcucha string2 powinna zwrócić funkcja. Przyjrzyjmy się kilku przykładom. Poniższe wywołanie funkcji zwraca liczbę 5, ponieważ litera c pojawia się na piątym miejscu w przeszukiwanym łańcuchu:

INSTR('a b "zawarte" ba', 'c')
"zawarte" - w książce symbol

W następnym przykładzie przeszukiwanie rozpoczyna się od czwartego znaku łańcucha, dlatego funkcja zwraca liczbę 7:

INSTR(('a b "zawarte" ba', 'B', 4)

Pierwsza litera b znajduje się w łańcuchu przed miejscem rozpoczęcia przeszukiwań, dlatego zwracana jest druga pozycja tej litery. Kolejny przykład:

INSTR('a b "zawarte" ba', 'a', 1, 2)

To zapytanie zwraca liczbę 9. Przeszukiwanie rozpoczyna się od pierwszej pozycji w łańcuchu, ale ponieważ czwarty argument wynosi 2, pierwsze wystąpienie litery "a" jest pomijane, a zwracane jest drugie. Wywołanie funkcji poniżej zwraca O, ponieważ przeszukiwany łańcuch nie spełnia kryteriów określonych przez argumenty:

INSTR('a b "zawarte" ba', 'a', 5, 2)

Listing 12.12 demonstruje, w jaki sposób funkcja INSTR może zostać użyta wewnątrz zapytania, zamiast użycia operatora porównania LIKE.
--------------------------
Listing 12.12. Zapytanie, które znajduje wszystkie filmy ze słowem "Linux" w tytule

SELECT movie_title, INSTR(movie_title, 'Linux')
FROM Movies
WHERE INSTR(movie title, 'Linux') <> 0

MOVIE_TITLE INSTR(MOVIE_TITLE, 'LINUK')
--------------------------
The Linux Files 5
--------------------------

Odpowiednikiem funkcji INSTR () w Transact-SQL jest CHARINDEX (). Funkcja ta nie posiada możliwości określenia pozycji rozpoczęcia przeszukiwania, a także tego, które wystąpienie łańcucha powinno być zwracane. Przeniesienie zapytania z listingu 12.12 do języka Transact-SQL wymagałoby wprowadzenia pewnych modyfikacji - pokazuje to listing 12.13. #274
--------------------------
Listing 12.13. Użycie funkcji CHARINDEXQ języka Transact-SQL

SELECT movie_title, CHARINDEK('Linux', movie_title)
FROM Movies
WHERE CHARINDEX('Linux', movie_title) <> 0

movie_title
-------------- --
The Linux Files 5

(1 row{s) affected)
--------------------------

Podciągi

Funkcje wycinające podciągi umożliwiają programistom wydobywanie określonego zakresu znaków z większego łańcucha. Transact-SQL udostępnia do tego celu funkcję SUBSTRING (), jej odpowiednikiem w systemie Oracle jest SUBSTR (). Obie funkcje są podobne, z wyjątkiem trzeciego argumentu, który jest opcjonalny w funkcji SUBSTRO, a obowiązkowy w SUBSTRING(). Składnia wywołań obu funkcji wygląda następująco:

SUBSTRING(string, start, length) SUBSTR (string, start [, length])

Pierwszym argumentem jest łańcuch lub wyrażenie, z którego wydobyty powinien być podciąg. Drugi argument określa pozycję w łańcuchu, od którego rozpoczyna się podciąg, a trzeci argument jego długość. Jeżeli trzeci argument funkcji SUBSTR () w Orac-le'u zostanie pominięty, funkcja zwróci wszystkie znaki od miejsca startu do końca łańcucha.
Oto kilka przykładów użycia obu funkcji oraz rezultaty ich działania (nie są to jednak prawdziwe zapytania - wynik zwracany przez funkcję pojawia się po prawej stronie, za znakiem równości):

SUBSTRING('Rafę Colburn', 6, 3) = 'Col1
SUBSTRCRafe Colburn1, 1, 4) = 'Rafę'
SUBSTR('Rafę Colburn', 6) = 'Colburn'


Użycie przeszukiwania i wyciągania podciągów

Wykorzystując wspólnie funkcje INSTR () lub CHARINDEX () z funkcjami SUBSTR () lub SUBSTRING () można przeprowadzać jednocześnie operacje szukania i wydobywania podciągów z większych łańcuchów. Jeżeli mamy łańcuch za--wierający w sobie dowolny ogranicznik (w postaci jednego lub kilku znaków) i chcemy wydobyć tekst znajdujący się po jednej ze stron tego ogranicznika, możemy do tego celu wykorzystać kombinację funkcji INSTR () i SUBSTR ().
Załóżmy dla przykładu, że mamy bazę danych zawierającą imiona i nazwiska osób w pojedynczym polu i chcemy z niego wydobyć jedynie imiona. W Oracle'u zadanie to realizuje zapytanie z listingu 12.15, natomiast w bazach Sybase i MS SQL Server zapytanie z listingu 12.16. Przykładową tabelę przedstawia listing 12.14.
#275
--------------------------
Listing 12.14. Przykładowa tabela, z której korzystają zapytania w listingach 12.15 i 12.16

SELECT role
FROM Cast_Movies

ROLE
------------
Thomas Black
Robert Maxwell
Malcolm Richards
Nina Smith
Pam Green
George Blake
Mitch Crane
Chris Parker
Paul Nero
Sheila Slade
Manfred Powell
--------------------------
--------------------------
Listing 12.15. Wydobywanie nazwisk z pola role w bazie danych Oracle

SELECT SUBSTRIrole, INSTR(role, '')+1)
FROM Cast Movles

SUBSTRIROLE, INSTR(ROLE, ' ') +1)
--------------------------
Black
Maxwell
Richards
Smith
Green
Blake
Crane
Parker
Nero
Slade
Powell

11 rows selected
--------------------------
--------------------------
Listing 12.16. Wydobywanie nazwisk z pola role w bazie danych Sybase

SELECT SUBSTRING(role, CHARINDEX(' ', role) +1, 40)
AS last_name
FROM Cas"t_Movies

last_name
----------
Black
Maxwell
Richards
Smith
Green
Blake
Crane
Parker
Nero
Slade
Powell

(11 row(s) affected)
--------------------------
#276
==================
Rada
Jeżeli używasz funkcji SUBSTR w Transact-SQL, jako długość łańcucha (trzeci argument) możesz przyjąć rozmiar kolumny. Rozwiązanie takie spowoduje zwrócenie przez funkcję wszystkich znaków od początku podciągu do końca łańcucha. Nawet jeśli długość kolumny będzie większa od liczby pozostałych znaków łańcucha, nie spowoduje to przerwania funkcji.
=================

Soundex

Soundex jest algorytmem służącym do porównywania łańcuchów w oparciu o ich brzmienie. Funkcja SOUNDEX () nie porównuje słów w tradycyjnym tego słowa znaczeniu, tzn. w oparciu o poszczególne znaki, lecz rozbija słowa w sposób fonetyczny i porównuje ich brzmienie. Funkcja SOUNDEX () jest dostępna zarówno w Oracle'u, jak i w Transact-SQL. SOUNDEX() przyjmuje argument w postaci słowa, a następnie zwraca kod reprezentujący to słowo. Spójrz na przykład w listingu 12.17.
--------------------------
Listing 12.17. Użycie funkcji SOUNDEX()

SELECT SOUNDEX('Colburn')
FROM Dual

SOUN
------
C416
--------------------------

Wartość zwrócona przez funkcję (C416) jest dla nas nieczytelna. Wynik generowany przez algorytm funkcji jest istotny dla celów porównawczych. Przyjrzyj się dla przykładu wynikom z listingu 12.17 i 12.18.
--------------------------
Listing 12.18. Kolejny przykład użycia funkcji SOUDNEX()

SELECT SOUNDEX('Cohlberne')
FROM Dual

SOUN
----
C416
--------------------------

Chociaż zapis obu słów jest różny, Soundex wykazuje (zgodnie z prawdą), iż słowa te posiadają jednakową wymowę. Funkcja SOUNDEK () przydaje się, gdy w bazie danych istnieje wykaz słów (nazwisk lub innych odpowiednio sformułowanych rzeczowników), możliwy do przeszukania przez użytkownika. Można szukać pasujących elementów przez wywoływanie funkcji SOUNDEX () z różnymi, podobnie brzmiącymi słowami, by w końcu trafić na pasujący rekord, mimo że użytkownik nie był w stanie dokładnie przeliterować słowa, którego tak naprawdę szukał. Spójrz na przykładowe zapytanie w listingu 12.19, które przeszukuje tablicę People.
--------------------------
Listing 12.19. Przeszukiwanie tabeli People z użyciem funkcji Soundex()

Select person_fname, person_lname
FROM People
WHERE SOUNDEKCPedersen1) = SOUNDEK (person_lname)
#277
PERSON_FNA PERSONALNA
---------------------------
Chuck Peterson
--------------------------

Oto krótkie wyjaśnienie działania funkcji SOUNDEK (). Po pierwsze, funkcja zamienia wszystkie znaki słowa na wielkie i w miarę potrzeby uzupełnia go spacjami. Pierwsza litera wyrazu zostaje zachowana jako pierwszy znak reprezentacji słowa przez algorytm SOUNDEX (uważni czytelnicy wiedząjuż zapewne, że reprezentacje łańcuchów w notacji SOUNDEK nie będą do siebie pasować, jeżeli pierwsze litery wyrazów będą różne). Następnie litery należące do pewnej grupy są usuwane ze słowa, jeżeli nie znajdują się w nim na pierwszej pozycji. Później każdej literze przypisywana jest wartość z zakresu od l do 6 (określone litery są skojarzone z odpowiednimi liczbami). Wszystkie kolejne pary tych samych cyfr są usuwane, a jeśli w efekcie łańcuch skróci się do mniej niż czterech znaków, zostanie uzupełniony zerami. Jeżeli łańcuch jest dłuższy od czterech znaków, wszystkie cyfry wystające poza czwartą pozycję są usuwane.

Daty i określenia czasu

Biorąc pod uwagę zarządzanie datami w języku SQL, trzeba stwierdzić, że jest to temat pod pewnymi względami bardziej złożony, niż łańcuchy czy liczby. Problem z datami (mimo ich standaryzacji) polega na tym, że ich wizualna reprezentacja może przybierać wiele form. Komplikacje pojawiają się w chwili, kiedy umożliwimy wprowadzanie dat użytkownikom. Trzeba wtedy zadbać o ich odpowiednie formatowanie tak, aby można było je wstawiać do pól typu DATĘ. Z drugiej strony, daty wydobywane z bazy danych muszą posiadać formę czytelną dla użytkownika.
Kolejnym utrudnieniem jest fakt, iż daty są przetwarzane w różny sposób w zależności od typu bazy danych. Za przykład Transact-SQL dostarcza kilka typów danych reprezentujących daty, podczas gdy Oracle tylko jeden - DATE.
Problemy nie ominęły również funkcji zarządzających datami. Ich implementacje silnie zależą od typu bazy danych. Różnice te są na tyle duże, że (pomijając sytuacje najbardziej ogólne) można stwierdzić, iż wiedzy na temat zarządzania datami w jednej bazie danych nie da się zastosować gdzie indziej. Za przykład weźmy funkcje zwracające bieżący czas, zestawione w tabeli 12.2.

Tabela 12.2. Funkcje różnych baz danych, zwracające bieżący czas

Funkcja Baza danych Rezultat
SYSDATE() Oracle 12-APR-99
GETDATE() Sybase 1999-07-20 11:08:18.267
GETDATE() MS SQL Server 1999-07-20 11:16:20
NOW() MySQL 1999-07-20 11:14:26
#278
Jak widać, każda baza danych formatuje daty w inny sposób. Większość relacyjnych baz danych zapisuje daty z niemal zadziwiającą dokładnością. Chociaż daty w systemie Oracle są domyślnie wyświetlane w formacie dzień-miesiąc-rok, dokładność dat zapisywanych w polach typu DATĘ jest w rzeczywistości równa ułamkom sekund.
Kolejna kwestia, znormalizowana w różny sposób przez różnych producentów baz danych, dotyczy operacji arytmetycznych związanych z czasem. Większość baz danych udostępnia funkcje umożliwiające użytkownikom dodawanie i odejmowanie dat, jednak i tutaj nie obeszło się bez widocznych niezgodności.

Formatowanie dat przed wyprowadzeniem

Kiedy daty zapisane w bazie danych mają zostać wykorzystane w raporcie, zazwyczaj przed prezentacją wymagają odpowiedniego sformatowania. Rzadko kiedy użytkownik chce przeglądać daty z dokładnością do milisekund, nie wspominając już o tym, że na ogół nie podoba mu się ich forma, domyślnie proponowana przez bazę danych. Oracle oferuje funkcję TO_CHAR (), która umożliwia prezentowanie dat w wymaganym formacie. Funkcja zwraca wyspecyfikowaną datę we wskazanym formacie, tak jak widać na przykładzie z listingu 12.20.
--------------------------
Listing 12.20. Formatowanie dat wypuszczenia filmów na rynek, przy użyciu funkcji TO_CHAR()

SELECT TO_CHAR(release_date, 'MONTH DD, YYYY')
FROM Movies

TO_CHARIRELEASE_DA
--------------------------
JANUARRY 01, 1975
MAY 01, 1979
SEPTEMBER 01, 1991
JULY 15, 1988
JULY 01, 1997
AUGUST 22, 1993
NOYEMBER 01, 1998
APRIL 17, 1993
APTIL 18, 1995
JULY 11, 1987
JANUARY 06, 1995

TO_CHAR(RELEASE_DA
--------------------------
AUGUST 08, 1991

12 rows selected.
--------------------------

Format 'MONTH DD, YYYY' wskazuje, iż data powinna być prezentowana z użyciem pełnej nazwy miesiąca, dnia miesiąca i roku w formacie czterocyfrowym. MONTH, DD i YYYY są znacznikami, które podczas wyświetlania zostają zastąpione rzeczywistymi elementami daty. Nie są to oczywiście jedyne znaczniki, jakie można umieścić w wywołaniu funkcji TO_CHAR (), w rzeczywistości jest ich znacznie więcej i pozwalają one na niemal dowolne formowanie dat występujących w raportach. Wszystkie znaczniki zostaną przedstawione w dalszej części rozdziału, przy okazji omawiania funkcji TO_CHAR () i TO_DATE ().
#279
Inne bazy danych oferują podobne funkcje przeznaczone do formatowania dat. Np. w Transact-SQL jest to funkcja ogólnego przeznaczenia konwertująca daty, o nazwie CONYERT (). W przeciwieństwie jednak do TO_CHAR (), funkcja CONVERT () korzysta z predefiniowanych stylów dat, zamiast ze znaczników, które umożliwiają tworzenie dowolnych stylów od podstaw. Do funkcji CONYERT () wrócimy później w tym rozdziale.

Formatowanie dat przed zapisem

Zanim data zostanie zapisana w bazie danych, musi zostać sformatowana w taki sposób, aby baza danych faktycznie uznała wstawioną wartość za datę. Bazy danych różnią się pod względem możliwości oferowanych w tym zakresie. Niektóre z nich rozpoznają dużą liczbę formatów dat, inne cechują się ścisłymi regułami określającymi wygląd, jaki musi posiadać data, aby mogła być wstawiona do określonego pola.
Ogólnie rzecz biorąc, istnieją dwie metody przygotowania daty do wstawienia w pole bazy danych. Pierwsza polega na ustaleniu, jaki format daty jest rozpoznawany przez bazę, umieszczeniu w pisanym programie kodu odpowiedzialnego za prawidłowe formowanie dat i w końcu wstawienie daty do bazy danych przy użyciu wyrażenia SQL. Drugi sposób opiera się na wykorzystaniu funkcji konwertujących typy danych do takiego przetworzenia daty, aby była ona rozpoznawana przez bazę danych.
Zazwyczaj data wstawiana w pole bazy danych jest łańcuchem, niezbędne jest zatem użycie funkcji konwertującej, która zmieni łańcuch na postać rozpoznawaną przez bazę danych jako poprawną datę. W bazach Oracle funkcja konwertująca łańcuchy na daty nosi nazwę TO_DATE (}; standardowy SQL (a także Transact-SQL) używają do tego celu funkcji CONVERT (). Obie funkcje zostaną omówione w dalszej części rozdziału, poświęconej konwersji dat.

Transact-SQL konwertuje łańcuchy na daty w sposób niejawny. Jeżeli dane znakowe użyte zostaną w kontekście daty, Transact-SQL podejmie próbę przekonwertowania ich do postaci daty. W przypadku niepowodzenia, wyrażenie SQL zakończy się błędem.

Dodawanie i odejmowanie dat

Podobnie, trudno jest mówić o działaniach arytmetycznych na datach w ogólności, ponieważ przeznaczone do tego celu funkcje różnią się znacznie między sobą w zależności od typu bazy danych. Np. Oracle pozwala na dodawanie dni do daty przy użyciu zwykłego operatora arytmetycznego, jak widać to w listingu 12.21.
--------------------------
Listing 12.21. Dodawanie dni do daty w bazie Oracle

SELECT SYSDATE, SYSDATE + 3
FROM Dual

SYSDATE SYSDATE+3
--------------------------

14-APR-99 17-APR-99
--------------------------
#280
Wadą systemu Oracle jest to, że nie posiada on funkcji umożliwiającej przeprowadzanie operacji na datach w oparciu o dowolnie wybraną jednostkę czasu. Transact-SQL pozwala na dodanie godziny do bieżącego czasu przy użyciu następującej funkcji:

DATEADD (hour, 1, GETDATE () )

Argumentami funkcji DATEADD o są: rodzaj jednostki czasu dodawanej do daty, liczba jednostek dodawanych do daty i w końcu data, do której dodana ma być wartość wynikająca z dwóch pierwszych argumentów. Aby odjąć czas od daty, funkcja DATEADD () musi zostać wywołana z ujemną liczbą jednostek. Oto przykład, w którym od bieżącej daty odejmowany jest tydzień:

DATEADD(week, -1, GETDATE())

W tabeli 12.3 znajduje się lista jednostek, które mogą zostać użyte razem z funkcją DATEADD (), a także z kilkoma innymi funkcjami Transact-SQL, operującymi na datach.

Tabela 12.3. Jednostki czasu oraz skróty używane w Transact-SQL

Jednostka Skrót Zakres wartości

milisekunda mm 0-999
sekunda ss 0-59
minuta mm 0-59
godzina hh 0-23
dzień dd 1-31
dzień tygodnia dw 1-7
dzień roku dy 1-366
tydzień wk 1-54
miesiąc mm 1-12
kwartał qq 1-4
rok yy 1753-9999

Oracle nie posiada podobnych możliwości, dlatego, aby odjąć godzinę od bieżącej daty, trzeba najpierw zapisać ją w postaci dziesiętnej. Przykład takiej operacji znajduje się w listingu 12.22.
--------------------------
Listing 12.22. Odejmowanie czasu w systemie Oracle

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS'),
TO_CHAR(SYSDATE - (1/24), 'HH:MI:SS')
FROM Dual

TO_CHAR( TO_CHAR(
---------------------
07:17:20 06:17:02
--------------------------

Przeprowadzanie obliczeń na datach w bazie Oracle jest możliwe dzięki zastosowaniu operatora dzielenia.
#281
Pierwszy argument instrukcji SELECT w powyższym listingu zwraca czas bieżący, drugi argument odejmuje 1/24 (lub inaczej jedną godzinę) od czasu bieżącego i zwraca otrzymany wynik.
=================
Rada
Przeliczanie różnych jednostek czasu w bazie Oracle staje się proste, jeżeli uzmysłowimy sobie fakt, iż podstawową jednostką czasu dla Oracle'a jest dzień. Aby wyznaczyć minutę, wystarczy pomnożyć 24 razy 60, otrzymując w ten sposób liczbę minut na dzień, a następnie podzielić jeden przez tę wartość:
(17(24*60))
Dodanie jednego dnia i jednej minuty do bieżącej daty można zrealizować przy u życiu wyrażenia:
SYSDATE + (1 + (1/(24 * 60)))
===========================

Ponieważ liczba dni w każdym miesiącu jest różna, Oracle wprowadził oddzielną funkcję, ADD_MONTHS (), której celem jest zwiększanie daty o dowolną liczbę miesięcy. Funkcja wymaga dwóch argumentów, daty oraz (ujemnej lub dodatniej) liczby miesięcy - patrz listing 12.23.
--------------------------
Listing 12.23. Użycie funkcji ADD_MONTHSQ w Oracle

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 6)
FROM Dual

SYSDATE ADD_MONTH
---------------------
02-may-99 02-NOV-99
--------------------------

Porównywanie dat

Wiesz już, że daty mogą być zwiększane lub zmniejszane przy użyciu prostych operatorów arytmetycznych w bazie Oracle i funkcji DATEADD () w Transact-SQL. Wiesz także, że daty mogą być porównywane w klauzuli WHERE zapytania. Nie znasz za to jeszcze metody, która pozwala określać różnicę czasu występującą między dwoma datami.
Do określania ilości miesięcy, jakie upłynęły między dwoma datami, służy w bazie Oracle funkcja o nazwie MONTHS_BETWEEN (}. Rzecz jasna, wartością zwracaną przez funkcję jest liczba miesięcy między porównywanymi datami; czas pozostały po odliczeniu miesięcy zwracany jest w postaci części ułamkowej. Zapytanie z listingu 12.24 zwraca czas, jaki upłynął od momentu wypuszczenia każdego z filmów w tabeli Movies. Wartość zwracana przez funkcję MONTHS_BETWEEN () jest tutaj dzielona przez 12, aby wyrazić miniony czas w latach, a nie w miesiącach.
--------------------------
Listing 12.24. Użycie funkcji MONTHS_BETWEENQdo określenia odstępu czasu między dwoma datami

SELECT movie_title, (MONTHS_BETWEEN(SYSDATE, release_date)/12)
FROM Movies
WHERE release_date IS NOT NULL
282
MOVIE_TITLE (MONTHS_BETWEEN(SYSDATE, RELEASE_DATE)/12)
----------------------------------------------------
Vegetable House 24.3382251
Prince Kong 20.0048917
The Code Warrior 7.67155839
Bili Durham 10.8005907
Codependence Day 1.83822506
The Linux Files 6.69844011
SQL Strikes Back .504891726
The Programmer 6.04521431
Hard Code 4.04252614
The Rear Windows 11.8113433

10 rows selected.
--------------------------

W Transact-SQL zróżnicowanie czasu między dwoma datami jest zwracane przez funkcję DATEDIFF (). Sposób wywoływania DATEDIFF () jest podobny do jej odpowiednika z Oracle'a - funkcja przyjmuje trzy argumenty: jednostkę czasu, której ma używać oraz dwie daty, na podstawie których wyliczona zostanie różnica czasu. Lista jednostek czasu akceptowanych przez funkcję DATEDIFF () znajduje się w tabeli 12.3 (jest to ta sama lista jednostek, z której korzysta funkcja DATEADDO). Listing 12.25 zawiera przykład zapytania z użyciem funkcji DATEDIFFO, wyznaczającego czas (w latach), jaki upłynął od wypuszczenia każdego z filmów na rynek.
--------------------------
Listing 12.25. Określenie ilości lat, jakie upłynęły od chwili wypuszczenia filmu na rynek, przy użyciu funkcji DATEDIFF()

SELECT movie_title, DATEDIFF(YEAR, release_date, getdate())
FROM Movies

movie title
--------------
Vegetable House 24
Prince Kong 20
The Code Warrior 8
Bili Durham 11
Codependence Day 2
The Linux Files 6
SQL Strikes Back 1
The Programmer 6
Hard Code` 4
The Rear Windows 12

(10 row(s) affected)
--------------------------

Zaokrąglanie dat

Jak wiadomo, typ DATĘ w Oracle'u jest w stanie przechowywać wartości daty i czasu z bardzo dużą dokładnością. Podobnie rzecz ma się w przypadku pól DATE-TIME języka Transact-SQL i innych pól tego typu we wszelkiego rodzaju relacyjnych bazach danych.
Niestety, porównywanie czasów przechowywanych z tak dużą dokładnością może nastręczyć wiele trudności. Jeżeli pewna wartość czasu posiada dokładność rzędu milisekund, porównanie jej z inną wartością tego typu, w celu sprawdzenia, czy wystąpiły w ciągu tej samej godziny, będzie z góry skazane na niepowodzenie. O ile oba czasy nie zgadzają się ze sobą co do milisekundy, wynikiem porównania będzie fałsz.
#283
Za każdym razem, kiedy porównywane są daty, trzeba zadbać o to, aby dokładność obu wartości sięgała jedynie poziomu istotnego z punktu widzenia celu porównywania. Jeżeli chcemy dowiedzieć się, czy dwie daty zaliczają się do tego samego roku, porównujemy jedynie wartości lat, a nie całe daty.
- Porównanie tego typu można przeprowadzić w Oracle'u po wcześniejszym sformatowaniu dat przy użyciu funkcji TO_DATE (). Porównywanie znaczących części dat w Transact-SQL umożliwia funkcja DATEPART ().
W Oracle'u do zredukowania precyzji wartości w polach DATĘ można wykorzystać również funkcje ROUND () i TRUNC (). Funkcja ROUND () zwraca datę z godziną 12:00, jeżeli część czasowa daty nie przekracza północy lub datę z dnia następnego (względem daty przekazanej do funkcji) z godziną 12:00, jeżeli część czasowa daty przekroczyła południe. Funkcja TRUNC () zwraca po prostu przekazaną jej datę z godziną ustawioną na 12:00. Jeżeli chcesz określić, czy data zapisana w rekordzie odpowiada dacie bieżącej, powinieneś użyć funkcji TRUNC (), tak jak pokazuje to listing 12.26.
--------------------------
Listing 12.26. Redukowanie precyzji daty przy użyciu funkcji TRUNC()

SELECT movie_title
FROM Movies
WHERE release_date = TRUNC(SYSDATE)

no rows selected
--------------------------

Do redukcji dokładności dat w Transact-SQL służy funkcja CONVERT() (patrzpunkt "Funkcja Convert()", w podrozdziale "Konwertowanie danych").

Konwertowanie danych

Większość wyrażeń i funkcji języka SQL wymaga, aby dane były im dostarczane w odpowiednim kontekście, z kolei w przypadku porównań danych ich typ powinien być niemal zawsze zgodny. Dla przykładu funkcja UPPER (} działa jedynie wtedy, gdy jej argumentem jest łańcuch. Podobnie, poniższe porównanie nie zadziała, ponieważ liczba 500 znajduje się w pojedynczych cudzysłowach i jest traktowana jako łańcuch, a nie liczba:

'500' > 600

Aby porównać te dwie wartości (z całą pewnością będące liczbami[Nie w sensie typu danych (przyp. tlum)]), jedna z nich musi zostać przekonwertowana do typu danych zgodnego z drugą wartością.
Pola nie zawsze muszą posiadać dokładnie ten sam typ danych, aby mogły być porównywane - czasami niezbędne konwersje przeprowadzane są w sposób niejawny. Ogólnie można stwierdzić, iż typy danych należące do tej samej kategorii mogą być porównywane bez potrzeby konwersji typów.
#284
Dopuszczalne jest porównywanie ze sobą pól znakowych o stałej i zmiennej długości bez konieczności dokładnego dopasowania typów danych (podobne zasady obowiązują w przypadku typów numerycznych i dat).
Jeżeli baza danych rozróżnia pola liczb całkowitych od pól liczb zmiennoprzecin-kowych, wartości z obu tych pól mogą być ze sobą porównywane w sposób dowolny bez potrzeby konwersji. Istnieją bazy danych posiadające wiele typów danych przechowujących czas. Jeżeli porównujemy jeden typ daty z innym typem daty, baza danych sama dokonuje wszelkich niezbędnych konwersji w sposób niejawny.
Wiele baz danych jest ponadto w stanie dokonywać niejawnej konwersji między różnymi kategoriami typów danych, jeżeli konwersja taka ma sens w kontekście, w którym dane te są używane. Dla przykładu, jeżeli w bazie Oracle do funkcji RTRIM () przekazany zostanie argument numeryczny, jego wartość zostanie automatycznie przekon-wertowana do postaci łańcucha (czyli typu danych wymaganego przez tę funkcję). Analogicznie Transact-SQL automatycznie podejmuje próbę przekonwertowania łańcucha na datę, jeżeli został on użyty w kontekście, w którym oczekiwano daty.

Funkcja Convert()

Bazy danych zgodne z Transact-SQL do konwertowania typów danych używają funkcji ogólnego przeznaczeń in o nazwie CONYERT'). Funkcja CONVERT () wymaga trzech argumentów: docelowego typu danych, wyrażenia do przekonwertowania oraz stylu daty, jeżeli jest ona konwertowana na łańcuch. Składnia funkcji CONVERT () wygląda następująco:
CONYERT (typ_danych, wyrażenie!, styl]}
Poniżej (listing 12.27) znajduje się przykład pokazujący konwersję pola numerycznego na łańcuch przy użyciu funkcji CONVERT ().
---------------------------
Listing 12.27. Użycie funkcji CONVERT() w zapytaniu

SELECT budget
FROM Movies
WHERE CONVERT(CHAR(10), budget) = '50'

budget
--------
50
50

(2 row(s) affected)
---------------------------

Powyższe zapytanie jest trochę abstrakcyjne (porównanie w klauzuli WHERE można było zapisać znacznie prościej: budget = 50), ale za to doskonale demonstruje użycie funkcji CONYERT(). Funkcja CONYERT () nakazuje bazie danych traktowanie zawartości pola budget jako łańcucha (CHAR (10)), a nie jako wartości numerycznej, którą jest w rzeczywistości. Generalnie rzecz biorąc, nie istnieją żadne ograniczania pod względem konwertowania danych numerycznych na łańcuchy.
#285
Konwersja powinna przebiec bez komplikacji, jeśli tylko liczba zmieści się w polu wyspecyfikowanym przez funkcję CONYERT (). Konwertowanie łańcuchów na liczby jest zadaniem odrobinę trudniejszym, ponieważ łańcuchy mogą zawierać wiele różnych znaków, których nie da się przetłumaczyć na dane numeryczne. Istnieje kilka przypadków, które uniemożliwiają pomyślne przekonwertowanie danych:
* Przecinki w danych konwertowanych na typ całkowity lub pieniężny. 4 Miejsca dziesiętne w danych konwertowanych na typ całkowity.
* Litery w danych konwertowanych na typ numeryczny.
* Błędnie zapisane lub nieprawidłowe nazwy miesięcy w danych konwertowanych na daty.
Funkcja CONYERT () służy również do konwertowania dat z i do innych typów danych. W przeciwieństwie do bazy Oracle, Transact-SQL nie posiada możliwości prostego określenia formatu daty, a następnie użycia go do zmiany wyglądu daty lub utworzenia jej ze specjalnie przygotowanego łańcucha. Zamiast tego istnieje kilka wbudowanych formatów dat (przedstawionych w tabeli 12.4), spośród których trzeba wybrać jeden przy wywołaniu funkcji CONVERT ().
================
Rada
Jeżeli korzystasz z kilku baz danych, powinieneś pamiętać, że funkcja CONVERT() jest również dostępna w Oracle'u, ale jej przeznaczenie jest zupełnie inne niż w przypadku Transact-SQL. W Oracle'u CONVERT() służy do transformowania danych łańcuchowych z jednego zestawu znaków na inny. Podobnie jak w Transact-SQL, funkcja CONVERT() przyjmuje trzy argumenty, które w tym przypadku określają łańcuch do konwersji oraz docelowy i źródłowy zestaw znaków.
=================

Funkcja CONVERT () nie tylko konwertuje łańcuchy na daty, ale służy również do prezentowania ich w formacie innym niż standardowy, co pokazuje tabela 12.4. To właśnie tutaj znaczenia nabiera atrybut style funkcji CONVERT (). Transact-SQL oferuje szereg formatów prezentacji daty, które można wykorzystać do zmiany jej wyglądu. Każdy format można zastosować na dwa sposoby: z dwoma cyframi wskazującymi stulecie lub bez nich. Lista formatów oraz ich wygląd znajduje się w tabeli 12.4. Listing 12.28 pokazuje przykład użycia funkcji CONYERT () do formatowania daty i czasu. Kolumna ID tabeli 12.4. zawiera identyfikatory tworzące daty z rokiem dwucyfrowym, a kolumna ID ze stuleciem, identyfikatory tworzące datę z rokiem czterocyfrowym. Kolumna Standard określa, do jakiego standardu pasuje dany format daty. Próbki dat w każdym z formatów znajdują się w kolumnie Przykład.
---------------------------
Listing 12.28. Użycie funkcji CONVERTQdo formatowania daty

SELECT CONVERT(varchar, GETDATE(), 107)
---------------------------
May 23, 1999

(1 row{s) selected)
---------------------------
#286
Tabela 12.4. Formaty dat w Transact-SQL

ID ID ze stuleciem Standard Przykład
- 0 lub 100 domyślny May 23 19991 2:54 \PM
1 101 USA 05/23/99
2 2 standard SQL 99.05.23
3 103 Anglia/Francja 23/05/99
4 104 Niemcy 23.05.99
5 105 - 23-05-99
6 106 - 23 May 99
7 107 - May 23, 99
8 108 - 12:56:59
9 lub 109 domyślny + ms May 23 1999 12:57:09:056 \PM
10 110 USA 05-23-99
11 111 Japonia 99/05/23
12 112 ISO 990523
---------------------------

Konwertowanie typów danych w Oracle'u

Oracle dysponuje trzema funkcjami do konwersji typów danych: TO_CHAR(), TO_DATE() i TO_NUMBER(). O funkcji TO_CHAR() już mówiliśmy, zwracając szczególną uwagę na jej możliwości formatowania dat, nie wspomnieliśmy jednak, że TO _CHAR () jest funkcją posiadającą bardziej ogólne przeznaczenie, pozwalające jej konwertować inne typy danych na typy łańcuchowe.
Funkcja TO_CHAR () jest używana do konwertowania liczb i dat na postać łańcuchów znakowych. Większość konwersji liczb na łańcuchy jest przeprowadzana w sposób niejawny. Załóżmy dla przykładu, że mamy tabelę pod nazwą Test o następującym schemacie:

Name Null? Type
---- ------ ----
TEST - VARCHAR(20)

Wykonanie poniższego polecenia INSERT zakończy się pomyślnie, mimo że wstawiana wartość posiada typ numeryczny (brak pojedynczych cudzysłowów):

INSERT INTO Test
VALUES (500)

Przed wstawieniem wartości do tabeli Oracle automatycznie konwertuje liczbę 500 na łańcuch tej samej postaci. Ponieważ liczby są automatycznie (niejawnie) konwertowane na typ łańcuchowy, głównym przeznaczeniem funkcji TO_CHAR () pozostaje formatowanie dat.
#287
Daty są również niejawnie konwertowane na łańcuchy, ale tylko w formacie podstawowym (DD-MM-RR). Żeby przekonwertować datę na łańcuch przy użyciu innego formatu, funkcji TO_CHAR() trzeba przekazać datę (lub nazwę kolumny zawierającej datę) oraz odpowiedni format.
Jak już wcześniej wspomnieliśmy, formaty są definiowane przy użyciu znaczników, które reprezentują różne komponenty daty. Zestawienie znaczników znajduje się w tabelach 12.5 i 12.6. Znaczniki grupowane są w otoczeniu pojedynczych cudzysłowów tworząc kompletne formaty dat, tak jak ten widoczny w listingu 12.29.

Tabela 12.5. Znaczniki stosowane do tworzenia dat w Oracle'u

Znacznik Opis

YYYY Rok czterocyfrowy
SYYYY Rok ze znakiem (obejmuje lata p.n.e.)
YYY Trzy ostatnie cyfry roku
YY Dwie ostatnie cyfry roku
Y Ostatnia cyfra roku
IYYY Rok czterocyfrowy wg standardu ISO
IYY Rok trzycyfrowy wg standardu ISO
IY Rok dwucyfrowy wg standardu ISO
I Rok jednocyfrowy wg standardu ISO
YEAR Rok zapisany słownie
RR Dwie ostatnie cyfry roku liczone względem dary bieżącej
Q Numer kwartału
MM Numer miesiąca
RM Numer miesiąca zapisany liczbą rzymską
MON Trzyliterowy skrót nazwy miesiąca
MONTH Pełna nazwa miesiąca
WW Liczba tygodni liczona względem początku roku
DDD Dzień roku
DD Dzień miesiąca
D Dzień tygodnia (w postaci liczby)
Y Dzień tygodnia (trzyliterowy skrót)
DAY Pełna nazwa dnia tygodnia
HH Godzina (w skali 12 godzinnej)
HH12 Godzina (w skali 12 godzinnej)
HH24 Godzina (w skali 24 godzinnej)
#288
MI Minuty
SS Sekundy
SSSSS Liczba sekund liczona od północy
A.M. Wyświetla A.M. lub P.M. dla wybranego czasu[]A.M. Oac. ante meridiem) przed południem, p.m. (łac. post meridiem) po południu (przyp. tlum.)
P.M. To samo co A.M.
AM, PM Wyświetla AM lub PM (bez kropek)
J Data wg kalendarza juliańskiego
A.D. Wyświetla A.D. lub B.C. w zależności od daty[A.D. (łac. anno domini) - lata n.e., B.C. (ang. before Christ) - lata p. n. e. (przyp. llum.)]
B.C. To samo co A.D.
AD, BC Wyświetla AD lub BC (bez kropek)
/,=: Interpunkcja dat

Tabela 12.6. Znaczniki używane do formatowania dat przez funkcje TO_CHAR()

Znacznik Opis
"łańcuch" Łańcuch przeznaczony do sformatowania na datę
fm Użyty przed znacznikiem MONTH lub DAY, usuwa nadmiarowe spacje (normalnie,
nazwy miesięcy i dni są uzupełniane spacjami, dzięki czemu zajmują zawsze tyle miejsca, ile wymaga miesiąc lub dzień o najdłuższej nazwie)
TH Dodaje odpowiedni przyrostek TH[W języku angielskim przyrostek TH tworzy liczebniki porządkowe, np. seventh - siódmy {przyp. tłum.).] do znacznika, przy którym został umieszczony
SP Zamienia liczbę, przy której się znajduje, na formę słowną.
SPTH Zamienia liczbę, przy której się znajduje, na formę słowną, z odpowiednim przyrostkiem
TH.
THSP To samo co SPTH.
---------------------------
Listing 12.29. Przykładowy format daty tworzony przy użyciu funkcji TO_CHAR()

FROM Dual

SELECT TO_CHAR(SYSDATE, 'DAY, MONTH DD')

TO_CHAR(SYSDATE, 'DAY, M)
---------------------------
MONDAY MAY 03
---------------------------

Wielkość liter używana w znacznikach wpływa na postać danych wyjściowych, generowanych przez funkcję TO_CHAR ().
#289
Wygląd słów, tworzonych przez tę funkcję, zależy od wielkości znaków poszczególnych znaczników. Na przykład weźmy zapytanie z listingu 12.30.
=================
rADA
Razem z formatem daty na liście argumentów instrukcji SELECT można również umieścić dowolne wyrażenie tekstowe, np.:

SELECT 'Today is', TO_CHAR(SYSDATĘ, 'DAY'}
FROM Dual
TODAYIS TO_CHAR(S
------------------
Today is MONDAY
=====================
------------------------------
Listing 12.30. Wpływ wyglądu znaczników na generowane dane wyjściowe

SELECT TO_CHAR(SYSDATE, 'Month'), TO_CHAR(SYSDATE, 'ddTHSP'),
TO_CHAR(SYSDATE, 'YEAR ad')
FROM Dual
TO_CHAR(S TO_CHAR(SYSDAT TO_CHAR (SYSDATE, 'YEARAD')
------------------------------
May fourth NINETEEN NINETY-NINE ad
------------------------------

Zwróć uwagę na to, że wielkość znaków w poszczególnych słowach rezultatu zapytania odpowiada wielkości znaków w znacznikach użytych w kolejnych wywołaniach funkcji TO_CHAR (). Fakt, iż pierwszy znak słowa Month jest pisany wielką literą sprawia, że w wyniku zapytania generowana jest nazwa o takiej samej postaci (tutaj May). Jak widać na powyższym przykładzie, pojedyncze wywołania funkcji TO_CHAR () mogą zawierać różne style wielkości znaków (w ostatnim wywołaniu funkcji nazwa bieżącego roku jest pisana wielkimi literami, natomiast nazwa epoki znakami małymi).
Funkcja TO_DATE () służy do konwertowania łańcuchów na postać dat systemu Oracle. Przekazany funkcji łańcuch jest porównywany z formatem (drugim argumentem funkcji) i jeśli jest to możliwe, zostaje z niego wydobyta data. Jak można się domyślić, funkcja TO_DATE () korzysta z tego samego zestawu znaczników, co funkcja TO_CHAR (). Przykład użycia funkcji TO_DATE () pokazuje listing 12.31.
-----------------------------
Listing 12.31. Konwertowanie łańcucha na datę przy użyciu funkcji TO_DATE()

SELECT TO_DATE('March 1, 1999, 'Month DD, YY');
FROM Dual

TO_DATE ('
-----------
01-MAR-99
-----------------------------


W praktyce

Kolejny typ danych, który nie został jeszcze omówiony, dotyczy tzw. dużych obiektów (ang. large objects). Większość baz oferuje typ danych przeznaczony do przechowywania dużych ilości danych binarnych lub znakowych. Niektóre systemy baz danych przechowują tego typu dane wewnątrz bazy danych, podczas gdy inne zapisują je jako pliki zewnętrzne, natomiast w odpowiednich polach pamiętająjedynie wskaźniki do plików.
#290
Ze względu na nieporęczność operowania ogromnymi ilościami danych, duże obiekty przechowywane w bazie danych obarczone są dodatkowymi ograniczeniami, których nie posiadają inne typy danych. Duże obiekty nie mogą być indeksowane, a w wielu przypadkach nie mogą być również przeszukiwane przy użyciu porównań z klauzulą LIKE. Niektóre bazy danych nie pozwalają także na porównywanie dużych obiektów przy użyciu standardowych metod porównań.
Ograniczenia nakładane na duże obiekty stają się mniej bolesne w miarę wzrostu pojemności standardowych typów danych. Przykładowo maksymalny rozmiar pola YARCHAR w MS SQL Sewer 6.5 wynosił 255 znaków. W wersji 7.0 limit ten został podniesiony do 4000 znaków. Podobnie Oracle w wersji 8 umożliwia tworzenie pól typu CHAR o długości 2000 znaków i pól typu VARCHAR2 o długości 4000 znaków. Wzrost pojemności tego typu pól sprawia, że ograniczenia nakładane na sposób użytkowania dużych obiektów tracą na znaczeniu.
Oczywiście, pola dużych obiektów są wciąż niezbędne do przechowywania danych binarnych oraz dużych porcji danych w kodzie ASCII, w międzyczasie jednak pola standardowe znajdują zastosowanie przy coraz większej liczbie zadań. Większość baz danych udostępnia dwa oddzielne typy dużych obiektów, jeden przeznaczony dla danych znakowych i drugi dla danych binarnych. W bazie Oracle istnieją aż cztery różne typy dużych obiektów. Typ CLOB (ang. character large object) może przechowywać do czterech GB danych znakowych, a typ BLOB (ang. binary large object) do czterech GB danych binarnych. Kolejny typ NCLOB jest podobny do typu CLOB, z tą różnicą, że może przechowywać wielobajtowe zestawy znaków. Ostatni typ dużych obiektów nosi nazwę BFILE i jest w rzeczywistości wskaźnikiem do pliku zewnętrznego.
Transact-SQL oferuje typ TEXT będący w stanie przechować do 2 GB danych znakowych oraz typ IMAGE, zdolny do przechowania 2 GB danych binarnych. Żaden z tych typów nie może być użyty w następujących przypadkach:

* Jako argument procedury.
* Jako argument wywołania RPC.
* W klauzulach UNION, ORDER BY, GROUP BY lub COMPUTE .
* W indeksie.
* W podzapytanich lub porównaniach łączących.
* W jakimkolwiek miejscu klauzuli WHERE, z wyjątkiem porównania typu LIKE.
* W połączeniu z operatorem konkatenacji łańcuchów ( + ).
* W triggerze używającym polecenia IN UPDATE.

Jak widać, Transact-SQL narzuca wiele ograniczeń na duże obiekty. Podobna sytuacja występuje w większości baz danych, ponieważ przetwarzanie pól zawierających ogromne ilości danych jest zadaniem niełatwym.Metody wykorzystania dużych obiektów są ściśle uzależnione od typu bazy danych. Szczegółowych informacji na temat implementacji dużych obiektów w określonym produkcie należy zatem szukać w jego dokumentacji.