#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 okrelone 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 cile okrelonego typu. Typy te pozwalajš na przyjęcie pewnych założeń odnonie rodzaju informacji przechowywanych w polach tabel. Nie trzeba zastanawiać się na tym, czy możliwe jest odjęcie wartoci z jednego pola numerycznego od wartoci 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 łatwociš zapisać datę, wartoć liczbowš lub łańcuch. Konsekwencjš tego jest brak ograniczenia pewnych operacji (np. arytmetycznych) do okrelonych typów wartoci - operacja zastosowana w niewłaciwy sposób spowoduje błšd lub zwróci bezsensowny wynik. Analogicznie, okrelajšc rozmiary pól na etapie tworzenia tabel, można okrelić dopuszczalnš wielkoć wartoci 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 iloci 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 oczywicie zdrowy rozsšdek, które nakazywałyby umieszczanie danych w polach o odpowiednim typie. Bazę danych można z łatwociš 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, wyjanimy, jak należy formatować dane, aby można było wstawić je do kolumny o okrelonym 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 wywietlana zawsze z precyzjš dwóch cyfr po przecinku. Wemy 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 rzeczywistoci 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łaciwociach 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 wartoci z kolumny budget. #265 -------------------------------- Listing 12.1. Wyniki zapytania posortowane według wartoci 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 wartoci 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 wartoci 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 wemy kolejnoć rysunków w tej ksišżce. Numer każdego rysunku wyznacza w pierwszej kolejnoci 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 jednoczenie 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 wartoci pustej. Z tego powodu pojawienie się (jednej lub kilku) wartoci 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 wartoci typu null pisalimy w rozdziale 5. Istniejš sytuacje, w których chcielibymy przyjšć pewne założenie co do wartoci reprezentowanej przez null w chwili wykonania zapytania, umożliwiajšc tym samym zwrócenie przez wyrażenie matematyczne wartoci rzeczywistej. Bazy danych wszelkiego typu umożliwiajš, w ten lub inny sposób, zastępowanie wartoci null okrelonymi wartociami rzeczywistymi. Standard SQL-92 zakłada możliwoć zastępowania wartoci 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 nieokrelony (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 wartoci 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 wartoci 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 wartoci 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. Wyjanil...
sliwak