Recursive algorithms in SQL

Possible?? Yes, just like the model clause..  

Below, the two queries contain algorithms written in a recursive version:

•   Fibonacci sequence 
•   Factorial
•   Euclid's algorithm for determining the greatest common divisor (GCD)

Two techniques are presented:  

• iterative models - are perfect for implementing unary recursive functions, and their notation is almost intuitive. Unfortunately, with a larger number of parameters, problems arise with cyclical cell enumeration 
• non-iterative models - formulas are a bit more complicated, but multi-argument functions can be handled
  

SELECT                                                    
      d  , f AS FIBBONACCI,
      S AS FACTORIAL
  FROM   (SELECT   0 d  FROM DUAL)
MODEL
   DIMENSION BY (0 d)
   MEASURES (0 f, 0 s)
   RULES
      ITERATE (100) UNTIL (ITERATION_NUMBER = 50)
       (f [ITERATION_NUMBER] =
                            CASE ITERATION_NUMBER
                                WHEN 0 THEN 0
                                WHEN 1 THEN 1
                              ELSE
                                f[ITERATION_NUMBER - 2] + f[ITERATION_NUMBER - 1]
                            END,
      s [ITERATION_NUMBER] =
                  CASE ITERATION_NUMBER
                    WHEN 0 THEN 1
                    ELSE           
                    ITERATION_NUMBER * s[ITERATION_NUMBER - 1]
                  END)


The example below can be simplified a bit :)

SELECT   L1, L2, GCD
  FROM   (SELECT   L1, l2
            FROM   (    SELECT   LEVEL - 1 L1
                          FROM   DUAL
                    CONNECT BY   LEVEL <= 20),
                   (    SELECT   LEVEL - 1 L2
                          FROM   DUAL
                          CONNECT BY   LEVEL <= 20))
MODEL
   DIMENSION BY (L1, L2)
   MEASURES (0 GCD)
   RULES SEQUENTIAL ORDER
   (GCD [ANY, ANY] =
            CASE
               WHEN CV (l2) = 0  AND CV (L1) =0
               THEN
                  1
               WHEN CV (l2) = 0  OR CV (L1) =0
               THEN
                  GREATEST(CV (l1), CV(l2))
               WHEN CV (l2) <= CV (l1)
               THEN
                  GCD[CV (l2), MOD (CV (l1), CV (l2))]
            END,
      GCD[ANY, ANY] =
            CASE
               WHEN CV (l2) > CV (l1) THEN
                        GCD[CV (l2), CV (l1)]
               ELSE GCD[CV (l1), CV (l2)]
            END)

 

Obsługa XML - część 3

Cały czas  działamy na tabeli  TBL_XML - opisanej tutaj. Analizowany XML  zawiera powtarzający się element pozycja, a w ramach tego elementu zagnieżdżone są pola:

• nazwa_waluty
• przelicznik
• kod_waluty
• kurs sredni

 
    dolar amerykański

1 USD 4,2396

Poznamy teraz konstrukcji, która w w elegancki sposób będzie pokazywać elementy i atrybuty z powtarzających się elementów

SELECT   EXTRACTVALUE( x.cxml, '//data_publikacji') data_pubblikacji ,
   EXTRACTVALUE( x.cxml, '//numer_tabeli') nr_tabeli ,
  xt.*
FROM   TBL_XML x,
       XMLTABLE('//pozycja'
         PASSING x.cxml --nazwa_kolumny XML
         COLUMNS
           NAZWA_WALUTY     VARCHAR2(30 CHAR)  PATH 'nazwa_waluty',
           KOD_WALUTY     VARCHAR2(3 CHAR)  PATH '//*/kod_waluty',
           PRZELICZNIK    VARCHAR2(5 CHAR) PATH '//przelicznik',
           KURS_DZIENNY    VARCHAR2(10 CHAR) PATH 'kurs_sredni'
         ) xt;
  
Wynik: Zauważmy - wszystkie pola sa tekstowe !!!!

Jak powyższa konstrukcja działa?

1.  Tworzona jest w pamięci tabela aliasowana przez xt
2. Każdy wiersz tabeli xt to powtarzający się element  pozycja, opisujący parametry waluty
3. W nawiasie po słowie kluczowym XMLTABLE podaje się ścieżkę do analizowanego elementu
4. Po słowie kluczowym PASSING podaje się nazwę pola zawierającego XML
5. Po słowie kluczowym COLUMNS podaje się  definicje i mapowania kolumn. Mapowanie występuje po słowie kluczowym PATH  i może być, jak powyżej, wykonane na różne sposoby
6. Wyrażenie xt.* zwraca kolumny zdefiniowane w mapowaniu

Uwagi:

1. W sekcji COLUMNS  najlepiej jest robić mapowania na typ znakowy lub na typ numeryczny(ale tylko dla liczb całkowitych). W powyższym przykładzie  mapowanie typ NUMBER(4) zadziałałoby dla pola PRZELICZNIK.  Dla liczb zmiennopozycyjnych (jak pole w  powyższym przypadku KURS_DZIENNY) i dat, automatyczna konwersja jest źródłem problemów.  Właściwą konwersję najlepiej przeprowadzić  w głównym zapytaniu, a nie na poziomie mapowania  
2. Jeśli zdefiniujemy  typ o niewystarczającej długości, to podczas mapowania teksty zostaną obcięte
3. Jedli w danym elemencie nie wystąpi ścieżka z mapowania, wtedy dla odpowiadającej temu mapowaniu kolumny  zwracana jest wartość NULL
4. W przypadku zmiany kolumny lub dodania nowych pól, modyfikacja istniejącego zapytania jest banalna

    

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
 
 

   

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 

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