Jak obsługiwać błędy konwersji danych i konwertować funkcją CAST? – lekcja 21

Wstęp

Twoja komenda SQL zakończy się błędem gdy motor bazy danych nie poradzi sobie z konwersją wartości na pożądany przez Ciebie typ. Co zrobić z błędem konwersji? O tym i o znanej ze standardu ANSI wszystkokonwertującej funkcji cast w niniejszej lekcji.

Najprościej

Funkcje konwertujące zaprezentowane przeze mnie w poprzednich lekcjach potrafiły jedynie konwertować napis na typ liczbowy czy datoczasowy lub odwrotnie.

Cechą wyróżniającą cast jest możliwość konwertowania wartości dowolnego typu podanej w pierwszym parametrze na dowolny typ podany po słowie kluczowym AS.

SELECT
  cast(123.7 as number(3)) "liczba zokrąglona"
  ,cast('Marcin' as varchar2(3)) "napis obcięty"
  ,cast(sysdate as timestamp with time zone) "date jako timestamp"
  ,cast(systimestamp as date) "data systemowa"
FROM
  dual;

Niemniej cast może być także używana do konwersji literałów na dane wbudowanych typów. Formatem podanym jako drugi parametr określasz postać literału, który ma zostać skonwertowany.

SELECT
  cast('123.4' as number(4,1), '999.9') "konwersja do liczby"
  ,cast('50-05-20' as date, 'rr-mm-dd') "konwersja do daty z XX wieku"
  ,cast('49-05-20' as timestamp, 'rr-mm-dd') "konwersja do daty z XXI wieku"
  ,cast(123 as char(3)) "liczba jako napis"
  ,cast('49-11' as interval year to month) "lat i miesięcy"
  ,cast('4 19:20:00' as interval day to second default '-1 00:00:00' on conversion error) "dni i godzin"
FROM
  dual;

Zwróć uwagę na elementy rr w formacie daty. Oznaczają rok w formacie dwucyfrowym. Oracle dopisze 19 jeśli liczba jest większa niż 49. Lub 20 jeśli liczba jest mniejsza niż 50.

Trzecim parametrem określasz ustawienia narodowe.

SELECT
  cast('123,4' as number(4,1), '999D9', 'NLS_NUMERIC_CHARACTERS='',.''') "konwersja do liczby"
  ,cast('50-Maj-20' as date, 'rr-Month-dd', 'NLS_DATE_LANGUAGE=Polish') "konwersja do daty z XX wieku"
  ,cast('49-MAJ-20' as date, 'rr-MONTH-dd', 'NLS_DATE_LANGUAGE=Polish') "konwersja do daty z XXI wieku"
FROM
  dual;

Dwa apostrofy użyte w parametrze nls_numeric_characters maskują ich interpretację. Cast nie pozwala na formatowanie danych liczbowych czy datoczasowych w napisy o pożądanym kształcie.

Po co konwertować?

Relacyjna baza danych rygorystycznie przestrzega typu danych. Dlatego aby wstawić do niej dane muszą przybrać akceptowany kształt. Różne typy danych mają różne mechanizmy pozwalające na wykonywanie na nich operacji arytmetycznych czy porównań.

Nawet jeśli funkcja substr wytnie dla Ciebie dwie cyfry z liczby to tylko dlatego, że niejawnie skonwertuje liczbę na napis.

SELECT
  substr(1234, 2, 2)
FROM
  dual;

• podczas niejawnej konwersji dane mogą przybrać postać na której niekoniecznie Ci zależy
• niejawna konwersja jest szczególnie dotkliwa gdy uniemożliwia skorzystanie z indeksu

Są to najważniejsze powody dla których warto mieć kontrolę nad konwersją danych do pożądanego typu. Dla optymalnej wydajności Twoich zapytań SQL ważne jest aby nie porównywać jabłek do gruszek.

Obsługa błędów

Domyślnie błąd konwersji zwracany jest do klienta, a transakcja wycofywana. Wiąże się z komunikacją sieciową oraz koniecznością oprogramowania obsługi błędów w aplikacji. Całego tego ambarasu unikniesz zlecając obsługę błędów motorowi bazy danych. Od wersji 12.2 Oracle udostępnia składnię DEFAULT ON CONVERSION ERROR.

SELECT
  cast('123,4' as number(4,1) default -100.1 on conversion error, '999D9') "konwersja do liczby"
  ,cast('50-Maj-20' as date default '50-May-20' on conversion error, 'rr-Month-dd') "konwersja do daty z XX wieku"
  ,cast('49-MAJ-20' as date default NULL on conversion error, 'rr-MONTH-dd') "konwersja do daty z XXI wieku"
  ,cast('49--20' as interval year to month default '99-11' on conversion error) "lat i miesięcy"
  ,cast('49::20' as interval day to second default '-1 00:00:00' on conversion error) "dni i godzin"
FROM
  dual;

Pozwala zdefiniować wartość, która zostanie skonwertowana gdy konwersja pierwszego parametru się nie powiedzie. Czyli po pożądanym typie wpisujesz słowo kluczowe default, następnie wartość co do której masz pewność, że zostanie poprawnie skonwertowana oraz frazę on conversion error.

O obsługę błędów rozbudowane zostały także funkcje natywne to_number, to_date, to_timestamp czy to_interval.

SELECT
  to_number('123,4' default -1 on conversion error)
  ,to_date('50-Maj-20' default '99-May-20' on conversion error, 'rr-Month-dd')
  ,to_timestamp('49-MAJ-20 12:13:14.123456' default NULL on conversion error, 'rr-MONTH-dd')
  ,to_yminterval('49--20' default '99-11' on conversion error)
  ,to_dsinterval('49::20' default '-1 00:00:00' on conversion error)
FROM
  dual;

Po pierwszym parametrze wpisujesz słowo kluczowe default, następnie wartość co do której masz pewność, że zostanie poprawnie skonwertowana na pożądany typ oraz frazę on conversion error. Klauzula on conversion error gwarantuje, że zamiast błędu konwersji otrzymasz zdefiniowną wartość pożądanego typu lub NULL. Czyli komenda SQL w której wykorzystujesz funkcje konwertujące zostanie wykonana z sukcesem.

Walidacja

Wykorzystując funkcję validate_conversion dowiesz się czy konwersja powiedzie się zanim uruchomisz właściwą komendę SQL konwertującą dane. Dzięki temu będziesz móc zdecydować co chcesz zrobić z danymi, które nie poddadzą się konwersji.

SELECT
  data_jako_napis
FROM
  tabela_zewnętrzna
WHERE
  validate_conversion(data_jako_napis as date, 'dd-month-yyyy', 'NLS_DATE_LANGUAGE=Polish')!=1;

Składnia validate_conversion przypomina wcześniej opisane funkcje konwertujące. Gdy konwersja się powiedzie zwraca jeden. Zero gdy konwersja zakończy się niepowodzeniem.

Możesz to wykorzystać gdy masz do załadowania do bazy danych dane datoczasowe i liczbowe w postaci tekstowej. Nie masz pewności czy dane nie są zabrudzone. Albo wręcz masz pewność, że występują w kilku różnych formatach. Możesz wykorzystać mechanizmy motoru bazy danych do ich wyczyszczenia lub dostosowania formatu. Na przykład funkcją validate_conversion testować czy konwersja przebiegnie poprawnie. Jeśli tak to wstawić dane do tabeli docelowej. Jeśli nie to wstawić dane do tabeli wyjątków lub automatycznie zmodyfikować dane czy format i ponowić próbę konwersji.

Funkcjonalności Oracle, które mogą być pomocne w procesie to external tables i procedury składowane. Jeśli temat jest dla Ciebie interesujący to komentarzami namówisz mnie do realizacji dedykowanego materiału.

Wydajność

Walidując konwersję oraz obsługując błędy konwersji po stronie motoru bazy danych oszczędzisz na komunikacji sieciowej. Co będzie miało pozytywny wpływ na szybkość oraz odporność przetwarzania danych. Dodatkowo z procedur walidujących umieszczonych w bazie danych będą mogły skorzystać inne źródła danych. Wtedy zasady konwertowania będą identyczne dla wszystkich źródeł. Nie będzie potrzeby oprogramowywania reakcji na błędy konwersji w aplikacji.

Korzystnie na wydajność przetwarzania danych wpłynie nawet sama klauzula default on conversion error. Definiując dzięki niej wartość spoza zakresu – na przykład ujemny wiek, płacę czy odległą datę – umożliwisz późniejsze łatwe wyszukanie takich wartości w bazie danych. Taka wartość odwrotnie niż NULL zostanie uwzględniona w indeksie.

Komentarzami możesz namówić mnie do realizacji materiału szerzej omawiającego temat walidacji konwersji po stronie bazy danych.

Podsumowanie

Gratuluję Ci ukończenia dwudziestej pierwszej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, jak konwertować dane do pożądanych przez Ciebie typów oraz formatów znaną ze standardu ANSI funkcją CAST. Wiesz także jak obsługiwać błędy konwersji w funkcji CAST, TO_NUMBER, TO_DATE, TO_TIMESTAMP i TO_INTERVAL.

Zadanie dla Ciebie: napisz i uruchom zapytanie konwertujące datę 20 maja 72 roku do typu date przy założeniu, że data pochodzi z XX wieku. W razie błędu konwersji niech zostanie użyty literał 1 stycznia 1901 roku. Miesiące podane są w języku polskim.

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ę typami znakowymi, datoczasowymi i numerycznymi w zapytaniach SQL. W następnej lekcji zaprezentuję nie istniejący do wersji 23ai typ boolean.

Tymczasem dziękuję za ukończenie niniejszej.

Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Wróć do poprzedniej lekcji