Jak filtrować i przekształcać liczby w zapytaniach SQL? – lekcja 20

Wstęp

Jak formatować liczby do przydatnego Tobie napisu oraz jak konwertować łańcuchy znaków do danych numerycznych dowiesz się z niniejszej lekcji.

Wspomnę także o operatorach IN oraz BETWEEN.

Najprościej

Najprościej wybierzesz rekordy przyrównując kolumnę typu numerycznego do literału.

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id=55;

Oczywiście możesz używać innych znanych z matematyki operatorów porównania: !=, <, >, <=, >=, > . Jak również znanego z lekcji o odcinkach czasu operatora BETWEEN.

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id BETWEEN 55 AND 69;

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id>=55 AND object_id<=69;

Upraszcza zapis warunków połączonych operatorem AND. Zwróć uwagę, że warunki są nieostre. Literały wskazane w warunku są włączone w zakres.

Przydatny może okazać się także operator IN.

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id IN (134
              , 143);

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id=134
  OR object_id=143;

Po słowie kluczowym IN w nawiasach okrągłych wymieniasz wartości do których chcesz przyrównać operand z lewej strony IN. Działa jak wiele przyrównań połączonych operatorem OR. Podobnie jak BETWEEN przyjmuje wartości różnych typów. Liczbowe, znakowe i datoczasowe. Ważne aby dało się je skonwertować do typu lewego operandu.

SELECT
  object_name
FROM
  all_objects
WHERE
  object_id IN (134, '143');

Zniechęcam do polegania na niejawnej konwersji ponieważ jest zależna od ustawień klienta i może prowadzić do nieoczekiwanych rezultatów.

Formatowanie

Trzeba pamiętać, że liczby niezależnie od tego co oznaczają, w bazie danych składowane są jako specyficzny dla Oracle ciąg bajtów. Przyjmują pożądany przez Ciebie format w momencie przekazywania do klienta. Odwrotnie – z przyjaznego dla Ciebie formatu do akceptowanego przez Oracle ciągu bajtów – konwertowane są gdy używasz literałów liczbowych w zapytaniach.

Kontrola w postaci jawnego formatowania i konwersji dramatycznie zwiększa szanse, że otrzymasz to czego oczekujesz. Formatowanie liczb po stronie bazy danych oszczędzi pracy klientowi.

• dziewiątka wskazuje w którym miejscu Oracle ma wstawić cyfrę formatowanej liczby o ile liczba jest wystarczająco duża
• zero na początku nakazuje dopełnić część całkowitą zerami z lewej
• zero na końcu nakazuje dopełnić część dziesiętną zerami z prawej

SELECT
  to_char(123, '09999') "liczba dopełniona zerami"
  ,to_char(123, '9999') "liczba krótsza niż format"
  ,to_char(123.67, '9999.990') "część ułamkowa dopełniona zerami"
FROM
  dual;

Gdy część całkowita formatu jest krótsza niż część całkowita liczby dostaniesz hasze. Gdy w formacie jest mniej cyfr w części dziesiętnej liczba zostanie zaokrąglona.

SELECT
  to_char(1234, '999') "za krótki format"
  , to_char(123.67, '999.9') "zaokrąglenie"
FROM
  dual;

W ten sposób otrzymasz dane numeryczne sformatowane tak aby zawsze składały się z tej samej liczby znaków.

Przecinek i kropka

Polska notacja liczbowa wykorzystuje przecinek do oddzielenia części całkowitej od części dziesiętnej liczby. Kropka użyta jako separator tysięcy ułatwi odczyt dużych liczb. Anglosasi mają odwrotnie. To jaką notację wykorzystuje Twoja sesja sprawdzisz odczytując jej parametry.

SELECT
  value
FROM
  nls_session_parameters
WHERE
  parameter='NLS_NUMERIC_CHARACTERS';

• pierwszy znak to separator części dziesiętnej
• drugi to separator tysięcy

Ustawienia sesji instruują jedynie motor bazy danych według jakich zasad ma formatować liczby w napisy oraz konwertować literały znakowe do liczb. Możesz zmodyfikować ustawienia sesji, a następnie użyć do sformatowania napisu znanej z lekcji o typach datoczasowych funkcji to_char.

ALTER SESSION
  SET NLS_NUMERIC_CHARACTERS=',.';

SELECT
  to_char(10000.87, '99G999D99') "w polskiej notacji liczbowej"
FROM
  dual;

• pierwszym jej parametrem są dane liczbowe
• drugim pożądany przez Ciebie format w jakim dane liczbowe mają być przedstawione. Duże G oznacza separator tysięcy, a duże D separator części dziesiętnej. Pełną listę dostępnych elementów formatu znajdziesz w dokumentacji.
• trzecim parametrem zmieniasz ustawienia narodowe na poziomie komend SQL

SELECT
  to_char(10000.87, '99G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') "w polskiej notacji liczbowej"
FROM
  dual;

Pamiętaj, że dwa apostrofy w łańcuchu znaków zostaną zinterpretowane jako jeden.

Funkcja to_number używana jest do konwersji napisu na dane liczbowe.

ALTER SESSION
  SET NLS_NUMERIC_CHARACTERS=',.';

SELECT
    to_number('10.000,87') "w polskiej notacji liczbowej"
FROM
    dual;

Co ciekawe domyślnie nie rozpoznaje separatora tysięcy. Jego miejsce trzeba jawnie wskazać w formacie.

ALTER SESSION
  SET NLS_NUMERIC_CHARACTERS=',.';

SELECT
    to_number('10.000,87', '99G999D99') "w polskiej notacji liczbowej"
FROM
    dual;

Parametrem trzecim wymuszasz ustawienia narodowe na poziomie komendy SQL.

SELECT
    to_number('10.000,87', '99G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') "w polskiej notacji liczbowej"
FROM
    dual;

Dlaczego to ważne?

Na przykład dlatego, że w klauzuli SELECT możesz sklejać w napis dane różnych typów. Wszystkie nieznakowe zostaną niejawnie sformatowane według ustawień narodowych sesji. Aby otrzymać to czego potrzebujesz sformatuj jawnie.

Podobnie gdy chcesz użyć literału znakowego w klauzuli WHERE do przyrównania do danych liczbowych. Skonwertuj go jawnie do postaci numerycznej, a unikniesz nieporozumień.

Także gdy wstawiasz dane do bazy danych. O czym w dalszych publikacjach.

Trzeba pamiętać, że literał liczbowy zawsze używa kropki do oddzielenia części dziesiętnej. Ustawienia narodowe dotyczą jedynie konwersji z i na dane znakowe.

SELECT
    'równe'
FROM
    dual
WHERE
    10.87=10.87;

Symbol waluty

Na format oznaczających waluty danych liczbowych masz wpływ poprzez trzy parametry.

SELECT
    *
FROM
    nls_session_parameters
WHERE
    parameter LIKE '%CURRENCY%'

• NLS_CURRENCY oznacza symbol waluty. Przekreślone S dla dolara. E z dwoma kreskami dla euro, i tak dalej. W formacie reprezentowane przez duże L.
• NLS_DUAL_CURRENCY wskazuje symbol drugiej waluty. W formacie jako duże U.
• NLS_ISO_CURRENCY oznacza trzyliterowy skrót ISO waluty. Czyli USD dla dolara, EUR dla euro czy PLN dla złotówki. W formacie jako duże C.

Dwa pierwsze parametry przyjmą dowolne znaki. O tyle dla trzeciego trzeba wybrać z dostępnych. Dla Polski będzie to Poland. Parametry możesz ustawiać na poziomie sesji.

ALTER SESSION
  SET NLS_CURRENCY='zł';

SELECT
    to_char(10000.87, '99G999D99L') "napis"
FROM
    dual;

Lub wymuszać w zapytaniu SQL.

SELECT
    to_char(10000.87, '99G999D99C', 'NLS_ISO_CURRENCY=''POLAND''') "napis"
FROM
    dual;

Te same zasady obowiązują gdy konwertujesz literał znakowy do danych liczbowych. Możesz podać parametry dotyczące separatorów i symbolu waluty jednocześnie.

SELECT
  to_number('10.000,87PLN', '99G999D99C', 'NLS_ISO_CURRENCY=''POLAND'' NLS_NUMERIC_CHARACTERS='',.''') "kwota złotówek"
  ,to_number('€1.234,56', 'L9G999D99', 'NLS_CURRENCY = ''€'' NLS_NUMERIC_CHARACTERS = '',.''')  "kwota euro"
FROM
    dual;

Dzięki znajomości reguł budowania formatów w Oracle nie będzie potrzeby formatowania w kliencie.

Oracle dostarcza funkcjonalności pozwalającej na obsługę błędów konwersji o czym w następnej lekcji.

Funkcje

Funkcje ceil i floor zaokrąglają liczbę podaną jako argument do najbliższej wartości całkowitej odpowiednio w górę i w dół.

SELECT
  ceil(16.87) "zaokrąglony w górę"
  ,floor(16.87) "zaokrąglony w dół"
FROM
  dual;

Funkcje round jak i trunc pozwalają wskazać do którego miejsca zaokrąglić lub obciąć liczbę.

• pierwszy argument to liczba do zaokrąglenia lub obcięcia
• drugi to miejsce do którego należy ją zaokrąglić lub obciąć

Gdy drugi argument jest ujemny to zaokrąglona lub obcięta zostanie część całkowita.

SELECT
  round(16.87,-1) "zaokrąglona"
  ,trunc(16.87,-1) "obcięta"
FROM
  dual;

Zwróć uwagę, że round zaokrągliła, a trunc obcięła liczbę do drugiego miejsca przed przecinkiem.

Funkcje greatest i least przyjmują zmienną liczbę różnych typów argumentów. Ważne aby pamiętać, że pierwszy argument determinuje jak Oracle potraktuje pozostałe. Na przykład jeśli pierwszy argument będzie numeryczny to pozostałe również zostaną tak potraktowane.

SELECT 
  greatest(25, '3.14', 02, 58.9) "Największa"
  ,least(25, '3.14', 02, 58.9) "Najmniejsza"
FROM
  dual;

Trzeba pamiętać, że liczby przedstawione jako literały znakowe zostaną niejawnie skonwertowane do typu numerycznego zgodnie z ustawieniami klienta.

Opis wszystkich funkcji arytmetycznych i trygonometrycznych znajdziesz w dokumentacji.

Rzymskie heksy

Litera X w formacie wskazuje pozycję cyfry w zapisie heksadecymalnym.

SELECT
  to_char(64, '000X')
  ,to_number('FF','XX')
FROM
  dual;

Przedstawienie liczby całkowitej w systemie rzymskim wymuszasz elementem RN formatu.

SELECT
  to_char(0, 'RN') "za mała"
  ,to_char(1, 'RN') "najmniejsza"
  ,to_char(1.7, 'RN') "zaokrąglona"
  ,to_char(3999, 'RN') "największa"
  ,to_char(4000, 'RN') "za duża"
FROM
  dual;

Liczba musi być większa niż 0 i mniejsza niż 4000. Zobacz jak zmieni się wynik gdy elementy formatu zapiszesz małymi literami.

Funkcja to_number niestety nie umie w konwersję liczby z systemu rzymskiego. Niemniej w internetach można znaleźć przykłady jak to zrobić jedną kwerendą.

Ujemne

Klasycznym formatem zapisu liczby ujemnej jest umieszczenie minusa przed liczbą.

SELECT
  -64 "liczba ujemna"
  ,to_char(-64) "domyślny format"
  ,to_char(-64, 'S999') "minus przed liczbą"
  ,to_char(-64, '999S') "minus za liczbą"
  ,to_char(-64, '999PR') "w nawiasach ostrokątnych"
  ,to_char(-64, '999MI') "minus lub spacja za liczbą"
  ,to_number('-64')
  ,to_number('64-', '99S')
  ,to_number('<64>','99PR')
FROM
  dual;

Elementami formatu możesz wpłynąć na jego położenie lub przedstawić liczbę ujemną w nawiasach ostrokątnych.

Zmiennoprzecinkowe

To_number konwertuje napisy do danych typu number. Jest to uniwersalny i najczęściej wykorzystywany typ pozwalający na dokładne przetwarzanie danych numerycznych. Dane te składowane są w bazie danych jako specyficzny dla Oracle ciąg bajtów. Oracle gwarantuje takie same wyniki przetwarzania typu number niezależnie od platformy na jakiej pracuje motor bazy danych.

Oprócz typu number dostępne są specyficzne dla liczb zmiennoprzecinkowych typy binary float i binary double. Do konwertowania napisów na te typy służą funkcje to_binary_float i to_binary_double.

SELECT
  to_binary_float('123,456','999D999','NLS_NUMERIC_CHARACTERS='',.''')
  ,to_binary_double('123.456')
FROM
  dual;

• pierwszym parametrem jest literał do skonwertowania
• drugim jest format
• trzeci to ustawienia narodowe

Jeśli drugi i trzeci są pominięte to brane są ustawienia klienta.

Wydajność

Oracle przed wysłaniem do klienta przepakowuje dane do struktur umożliwiających transmisję po sieci. Możesz wykorzystać ten fakt i sformatować dane liczbowe w pożądany przez Ciebie napis aby nie robić tego w kliencie.

Gdy używasz funkcji konwertujących w klauzuli WHERE zadbaj aby parametrem był literał, a nie kolumna.

Użycie przekształcenia na kolumnie zaowocuje wykonaniem go na wszystkich rekordach tabeli oraz uniemożliwi użycie indeksu. Przekształcenie literału zostanie wykonane jedynie raz. Zniechęcam do polegania na konwersji niejawnej.

Podsumowanie

Gratuluję Ci ukończenia dwudziestej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak formatować dane liczbowe do pożądanych przez Ciebie napisów oraz jak konwertować literały znakowe do danych numerycznych obsługiwanych przez Oracle. Wiesz także jak używać operatora IN oraz BETWEEN.

Zadanie dla Ciebie: sformatuj w napis w polskim formacie finansowym swoją wymarzoną pensję pomnożoną przez jeden przecinek piętnaście setnych, gdzie separatorem tysięcy jest spacja, część dziesiętna zaokrąglona jest do dwóch miejsc po przecinku, a kwota przedstawiona jest w złotówkach.

Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak posługiwać się literałami liczbowymi oraz jak formatować dane liczbowe. Wiesz także jak wykorzystać operatory IN oraz BETWEEN. W następnej lekcji zaprezentuję wszystkokonwertującą funkcję CAST oraz jak obsłużyć błędy konwersji.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji