Zabawy z Oracle SQL, PL/SQL i ODI

niedziela, 12 kwietnia 2020

Obsługa XML - część 2

Bazujemy na tej tabeli co w poprzednim wpisie. W tabeli z kursami jest jeden wiersz zawierający w formacie XML kursy następujących walut (w kolejności ich występowania):
THB
USD
AUD
HKD
CAD

Napiszemy kilka prostych zapytań.

Wyświetlenie kodu waluty z pierwszego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie kodu waluty z pierwszego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie kodu waluty z pierwszego węzła

SELECT EXTRACTVALUE(CXML,'//pozycja[ last() ]/kod_waluty' ) FROM TBL_XML;
lub
SELECT EXTRACTVALUE(CXML,'//pozycja[ position() = last() ]/kod_waluty' ) FROM TBL_XML;
Uwaga: wielkość liter ścieżce XPath ma znaczenie. Następująca ścieżka ,'//pozycja[ Position() = last() ]/kod_waluty' jest już niepoprawna

Wyświetlenie kodu waluty z przedostatniego węzła:

SELECT EXTRACTVALUE(CXML,'//pozycja[ last() -1 ]/kod_waluty' ) FROM TBL_XML;

Wyświetlenie wszystkich kodów walut (przy założeniu, że procesujemy jeden wiersz) - czyli warto dodać odwołanie do klucza w tabeli. W klauzuli CONNECT BY LEVEL należy dodać sensowne górne oszacowanie liczby węzłów

SELECT EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) FROM TBL_XML
    WHERE EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) IS NOT NULL AND CID = 1
    CONNECT BY LEVEL < 100;

Wyświetlenie wszystkich kodów walut zawartych w węzłach od drugiego do przedostatniego

SELECT EXTRACTVALUE(CXML,'//pozycja[' || LEVEL || ']/kod_waluty' ) FROM TBL_XML WHERE EXTRACTVALUE(CXML,'//pozycja[ position() = ' || LEVEL || ' and position() >=2 and position() <= last() -1 ]/kod_waluty' ) IS NOT NULL AND CID = 1
    CONNECT BY LEVEL < 100;

Policzenie ile wierszy w tabeli zawiera w polu CXML kod waluty dolara:

SELECT COUNT(*) FROM TBL_XML WHERE EXISTSNODE(CXML, '//pozycja[kod_waluty="USD"]') = 1;
wynik 1

piątek, 10 kwietnia 2020

Obsługa XML - część 1

Zacznijmy od stworzenia tabeli
CREATE TABLE TBL_XML
(
CID                                 NUMBER(6)       NOT NULL,
CXML                  SYS.XMLTYPE    NOT NULL,
UZYTKOWNIK            VARCHAR2(30 CHAR)      DEFAULT USER NOT NULL,
DATA_WSTAWIENIA DATE DEFAULT SYSDATE    NOT NULL
)

Dlaczego został wybrany typ XMLTYPE , a nie CLOB ?? Ponieważ umożliwia wstawienie do kolumny CXML tylko poprawnego składniowo.
Jako xml wezmę początek pliku XML z kursami walut ze strony NBP na dzień 3 kwietnia 2020, zawierający pierwsze pięć węzłów.

<tabela_kursow typ="A" uid="20a066">
  <numer_tabeli>066/A/NBP/2020</numer_tabeli>
  <data_publikacji>2020-04-03</data_publikacji>
  <pozycja>
    <nazwa_waluty>bat (Tajlandia)</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>THB</kod_waluty>
    <kurs_sredni>0,1287</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>4,2396</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar australijski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>AUD</kod_waluty>
    <kurs_sredni>2,5527</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar Hongkongu</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>HKD</kod_waluty>
    <kurs_sredni>0,5469</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar kanadyjski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>CAD</kod_waluty>
    <kurs_sredni>2,9956</kurs_sredni>
  </pozycja>
</tabela_kursow>

Wstawiamy dane do tabeli:

INSERT INTO TBL_XML (CID, CXML)
VALUES
( 1,  '<tabela_kursow typ="A" uid="20a066">
  <numer_tabeli>066/A/NBP/2020</numer_tabeli>
<data_publikacji>2020-04-03</data_publikacji>
  <pozycja>
    <nazwa_waluty>bat (Tajlandia)</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>THB</kod_waluty>
    <kurs_sredni>0,1287</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar amerykański</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>USD</kod_waluty>
    <kurs_sredni>4,2396</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar australijski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>AUD</kod_waluty>
    <kurs_sredni>2,5527</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar Hongkongu</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>HKD</kod_waluty>
    <kurs_sredni>0,5469</kurs_sredni>
  </pozycja>
  <pozycja>
    <nazwa_waluty>dolar kanadyjski</nazwa_waluty>
    <przelicznik>1</przelicznik>
    <kod_waluty>CAD</kod_waluty>
    <kurs_sredni>2,9956</kurs_sredni>
  </pozycja>
</tabela_kursow>')

Spróbujmy z pola XML pobrać kod waluty pierwszego węzła w sposób prawidłowy, aby wynik zapytania był typu VARCHAR2. Można do tego celu wykorzystać funkcje EXTRACT i EXTRACTVALUE. Ta druga jest lepsza co poniżej pokażemy:

Poniższe polecenie jest niepoprawne:
SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;

Co nam zwróci - to zależy od edytora SQL.. w TOAD jest to tekst "THB. Ale jakiego rzeczywiście typu typu jest wynik powyższego zapytania ????

SELECT DUMP(EXTRACT(CXML,'//pozycja[1]/kod_waluty' )) FROM TBL_XML;

Otrzymamy:
Typ=58 Len=48: 128,68,239,156,252,127,0,0,192,142,121,1,178,1,0,0,32,3,128,1,178,1,0,..
Taki sam wynik otrzymamy dla zapytania:
SELECT DUMP(EXTRACT(CXML,'//pozycja[1]/kod_waluty/text()' )) FROM TBL_XML;

Co to jest typ 58 ?? wyjaśnienie znajduje się w pakiecie DBMS_TYPES:
TYPECODE_OPAQUE          PLS_INTEGER := 58;
W dokumentacji przeczytamy, że jest to typ abstrakcyjny (w znaczeniu nie można wykorzystywać w PL/SQL), zaimplementowany jako ciąg bajtów..
Niektóre edytory SQL potrafią sobie z tym poradzić i wtedy mamy mylne złudzenie, że zapytanie zwraca wartość tekstową..

Wymusimy konwersję na tekst:
SELECT CAST(EXTRACT(CXML,'//pozycja[1]/kod_waluty' ) AS VARCHAR2 (30 CHAR)) FROM TBL_XML;

Wynik:
THB

Jeśli wynusimy konwersję typu TYPECODE_OPAQUE do typu VARCHAR2 za pomocą funkcji GestStringVal()

SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty' ).GestStringVal() FROM TBL_XML;
Wynik:
THB
wynik typu TYPECODE_VARCHAR

SELECT EXTRACT(CXML,'//pozycja[1]/kod_waluty/text()' ).GestStringVal() FROM TBL_XML;
Wynik:
THB
Wynik typu TYPECODE_VARCHAR

Z funkcję EXTRACTVALUE parsowanie jest prostsze.

SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) FROM TBL_XML;
i
SELECT EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty/text()' ) FROM TBL_XML;
zwraca:
THB

Zarówno
SELECT DUMP(EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty' ) ) FROM TBL_XML;
jak i
SELECT DUMP(EXTRACTVALUE(CXML,'//pozycja[1]/kod_waluty/text()' )) FROM TBL_XML;
zwraca wynik:
Typ=1 Len=3: 84,72,66
czyli długość 3 znaki, gdzie 84,72,66 to kody ASCII poszczególnych liter

W pakiecie DBMS_TYPES znajdziemy
TYPECODE_VARCHAR         PLS_INTEGER :=   1;

Tu jest mała pułapka, bo typ VARCHAR2 nie rozróżnia wartości NULL i pustego tekstu, a typ VARCHAR to rozróżnia. Jest ona niegroźna gdyż puste znaczniki i atrybuty zwracane są jako NULL.

Podsumowując do prostego parsowania najlepsza jest funkcja EXTRACTVALUE, ponieważ wyłuskuje wartość znacznika

poniedziałek, 12 sierpnia 2019

Dzielimy kwotę na banknoty i monety

W jaki sposób w SQL można rozbić dana kwotę na minimalną liczbę banknotów i monet??. To jest proste przy wykorzystaniu niezapomnianej klauzuli MODEL, kod przypomina prosty arkusz w MS Excel. Kwota, która ns interesuje wstawiana jest zamiast przykładowej zielonej kwoty w kolorze zielonym Polecam analizę tego zapytania, ma kilka smaczków takich jak:

podzapytanie ze złączeniem kartezjańskim
ciekawy warunek stopu w iteracji
indeks do iteracji liczony w funkcji analitycznej

SELECT *
FROM ( SELECT KWOTA,
                NOMINAL,
                LICZBA_NOMINALOW,
                RESZTA
           FROM (   SELECT LICZBA * MNOZNIK                                       NOMINAL,
                           ROW_NUMBER( ) OVER (ORDER BY LICZBA * MNOZNIK DESC)    LP
                      FROM( SELECT 1 AS LICZBA FROM DUAL
                            UNION
                            SELECT 2 FROM DUAL
                            UNION
                            SELECT 5 FROM DUAL),
                           (SELECT 100 AS MNOZNIK FROM DUAL
                            UNION
                            SELECT 10 FROM DUAL
                            UNION
                            SELECT 1 FROM DUAL
                            UNION
                            SELECT 0.1 FROM DUAL
                            UNION
                            SELECT 0.01 FROM DUAL)
                  ORDER BY 1 DESC )
         MODEL
             DIMENSION BY( LP - 1 LP )
             MEASURES( NOMINAL,
                       15329.98 KWOTA,
                       0 LICZBA_NOMINALOW,
                       1 RESZTA,
                       0 Z )
             RULES
             ITERATE( 100 ) UNTIL (RESZTA[ITERATION_NUMBER] = 0)
             (
                 UPSERT
                 KWOTA [ITERATION_NUMBER] =
                     CASE ITERATION_NUMBER
                         WHEN 0 THEN TRUNC( KWOTA[0], 2 )
                         ELSE RESZTA[ITERATION_NUMBER - 1]
                     END,
                 LICZBA_NOMINALOW [ITERATION_NUMBER] =
                     TRUNC(
                         KWOTA[ITERATION_NUMBER] / NOMINAL[ITERATION_NUMBER] ),
                 RESZTA [ITERATION_NUMBER] =
                       KWOTA[ITERATION_NUMBER]
                     -   NOMINAL[ITERATION_NUMBER]
                       * LICZBA_NOMINALOW[ITERATION_NUMBER] ) )
WHERE LICZBA_NOMINALOW <> 0

środa, 10 lipca 2019

Gdzie jest Anna Kowalska??

Tworzymy sobie tabelę jak poniżej
CREATE TABLE KLIENCI
(
            ID_KLIENTA NUMBER(10) NOT NULL,
            IMIE VARCHAR2 (50 CHAR) NOT NULL,
            NAZWISKO VARCHAR2 (100 CHAR) NOT NULL
...................
)
Czy   to już wystarczy do poprawnego wyszukiwania klientów np Anny Kowalskiej?? Jeszcze niestety nie.
W jaki sposób mogą zostać zapisane te dane:
Anna Kowalska
ANNA KOWALSKA
AnnA KoWalskA
anna Kowalska
   Anna KOwalska

Możliwości jest wiele. Problem opisany przez mnie dotyczy standaryzacji danych tekstowych...Ten aspekt należy ustalić już sobie na etapie projektowania systemu. - w jaki sposób standaryzujemy istotne dane tekstowe, a w tym przypadku, czy imiona i nazwiska klientów zapisujemy:

dużymi literami
bez niewidocznych znaków wiodących i kończących w polach imienia i nazwiska oraz znaków interpunkcyjnych
czy dodajemy dodatkowe sprawdzenia na poziomie bazy danych np.

CHECK (IMIE = UPPER(TRIM(IMIE) ) )
lub CHECK (IMIE = INITCAP(TRIM(IMIE) ) )
znaki interpunkcyjne można wyłowić za pomocą wyrażeń regularnych

Do czego to prowadzi??

Otrzymujemy złożone i wolne zapytania jak np. poniżej

SELECT * FROM KLIENCI WHERE REGEXP_LIKE(TRIM(UPPER( REGEXP_REPLACE(IMIE, '[^[:alpha:]]', ''))), '^AN(N|K|I)A$') AND TRIM(UPPER(REGEXP_REPLACE(NAZWISKO, '[^[:alpha:]]', ''))) = 'KOWALSKA'

Instrukcja REGEXP_REPLACE(IMIE, '[^[:alpha:]]', '') służy do wycinania znaków, które nie są literami (dla złożonych wieloczłonowych nazwisk może być problem)
Uważny Czytelnik dostrzeże, że funkcja TRIM jest niepotrzebna i spełnia tylko rolę przejaskrawiającą
Nawet takie poplątane zapytania mogą pokrywać jedynie większość danych, ale może istnieć wiele błędnych przypadków, których nie jesteśmy w stanie przewidzieć

Co nam da standaryzacja??

Wyszukiwanie będzie działać szybciej, jeśli będą założone zwykłe indeksy po imieniu i nazwisku, stosowanie funkcji konwertujących będzie przeczesywać cała tabelę i generować niepotrzebne obciążenie
Zapytania staną się prostsze i szybsze. Zapytanie poniższe będzie wystarczające:

SELECT * FROM KLIENCI WHERE IMIE = 'ANNA' AND NAZWISKO = 'KOWALSKA'

Indeks po nazwisku zapewni range scan zamiast full scan
Wyniki raportów będą wymagały mniejszej standaryzacji
Standaryzacja zmniejsza koszty utrzymania systemu

A co można jeszcze zrobić w tym przypadku??

zesłownikować imiona, żeby była tylko Anna, a nie Anka lub Ańka lub Ania. Najczęściej występujące imiona da się zebrać w słowniku , a na formatce przy polu imię można wprowadzić checbox ,., którego odznaczenie wyłączy walidację imion ze słownikiem. Ciągle pojawiają się nowe serialowe inspiracje oraz imiona zagraniczne, których nie da się uwzględnić. Ale mimo wszystko są to sytuacje wyjątkowe

niedziela, 13 stycznia 2019

Klauzula MODEL c.d. - funkcje grupujące

Rozważmy poniższe zapytanie
SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
MEASURES(
       NUM_ROWS AS LICZBA_WIERSZY,
      1 AS SUMA,
      1 AS SREDNIA
      )
RULES(
SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ANY],
SREDNIA[ANY] = ROUND(AVG ( LICZBA_WIERSZY)[ANY])
)
ORDER BY NAZWA_TABELI;

Zwraca ono następujące kolumny:

NAZWA_TABELI - pole pobierane z widoku ALL_TABLES, tutaj występuje jako indeks, daltego wartość tego pola powinna być unikalna i niezmienna
LICZBA _WIERSZY - pole pobierane z kolumny NUM_ROWS widoku ALL_TABLES, zawiera liczbę wierszy w tabeli na ostatnie odświeżenie statystyk
SUMA - pole wyliczeniowe typu numerycznego, wartość początkowa w deklaracji pola w sekcji MEASURES w postaci 1 AS SUMA determinuje typ zmiennej
SREDNIA - pole wyliczeniowe typu numerycznego

W sekcji RULES mamy następujące formuły
SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ANY],
SREDNIA[ANY] = ROUND(AVG ( LICZBA_WIERSZY)[ANY])

Słowo ANY oznacza, że dane wyrażenie wykonuje się dla każdej wartości indeksu.
Dla każdej wartości indeksu dokonywane jest sumowanie wartości z kolumny LICZBA_WIERSZY dla wszystkich wierszy. Zwróćmy uwagę, że po słowie SUM w nawiasie jest nazwa kolumny, zakres sumowania znajduje się w nawiasie kwadratowym. Analogicznie wyliczana jest wartość średniej.

U mnie zapytanie zwraca następujące wartości.Dla kolumny LICZBA_WIERSZY skróciłem nagłówek
NAZWA_TABELI LW   SUMA     SREDNIA
COUNTRIES          25          215     30,71
DEPARTMENTS    27          215 30,71
EMPLOYEES       107          215     30,71
JOB_HISTORY      10          215 30,71
JOBS                      19           215 30,71
LOCATIONS         23           215 30,71
REGIONS               4            215    30,71

Spróbujmy te formuły nieco zmodyfikować. Zwróćmy uwagę, na sortowanie po nazwie tabeli.

SUMA[ANY] = SUM ( LICZBA_WIERSZY) [NAZWA_TABELI LIKE 'J%']
Powyższe wyrażenie sumuje liczbę kolumn, ale do sumy bierze wiersze, dla których NAZWA tabeli zaczyna się od litery J.

SUMA[ANY] = SUM ( LICZBA_WIERSZY) [NAZWA_TABELI IN ('COUNTRIES', 'JOBS', 'REGIONS') ]
Do sumy brane są wiersze dla tabel występujących na liście zdefiniowanej po operatorze wyliczeniowym IN. Wartości z trzech wierszy wchodzą do sumy/

SUMA[NAZWA_TABELI   IN ('COUNTRIES', 'JOBS', 'REGIONS')] = SUM ( LICZBA_WIERSZY) [ANY ],
W tym przypadku suma wyliczana jest dla trzech wierszy i uwzględnia dane ze wszystkich wierszy, które znajdują się na liście, dla pozostałych wierszy wyświetla się 1, ponieważ pole suma w klauzuli MEASURES inicjowane jest taką wartością

SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ NAZWA_TABELI <= CV() ]
Funkcja CV() (należy pamiętać o nawiasach !!!!!) - zwraca nam bieżącą wartość indeksu, czyli w naszym przypadku wartość pola NAZWA_TABELI. W ten sposób otrzymamy sumę narastająco z uwzględnieniem aktualnego sortowania.

SUMA[ANY] = SUM ( LICZBA_WIERSZY) OVER ()
Jest to sumowanie wierszy z wykorzystaniem składni z funkcji analitycznych.

SUMA[ANY] = SUM ( LICZBA_WIERSZY) OVER ( ORDER BY NAZWA_TABELI)
Jest to suma narastająco z wykorzystaniem składni z funkcji analitycznych.