Calculating Euler's number in Oracle SQL

 

I use the MODEL clause again to calculate the Euler's number.
Euler proved that e is the sum of the infinite series e = 1/0!  +1/1!  +1/2! +1/3!  +1/4! ........
There is one dimension - the iteration number.
The measures are:

• E_EXP -the Euler's number calculated from the EXP function
• FACTORIAL - value of the factorial function
• E_COMPUTED - our estimate of the number e
• DIFF -the difference between EXP(1) and the current estimate

My proposal is to change the precision in the stop condition

- the second parameter of the POWER function: 

UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))

----------------------------------------------------------- 

SELECT
       d              "Iteration number",
       FACTORIAL      "Factorial",
       E_EXP          "Euler's number from EXP",
       E_COMPUTED     "Euler's number calculated",
       DIFF           "Difference"
  FROM (SELECT 1 d FROM DUAL)
MODEL
    DIMENSION BY (0 d)
    MEASURES (CAST (0  AS NUMBER) AS E_EXP,
              CAST (1 AS NUMBER) AS FACTORIAL,
              CAST (1 AS NUMBER) AS E_COMPUTED,
              CAST (0 AS NUMBER) AS DIFF)
    RULES
    ITERATE (1000) UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))
    (
        FACTORIAL [ITERATION_NUMBER] =
            CASE
                WHEN ITERATION_NUMBER=0  THEN 1
                ELSE FACTORIAL[ITERATION_NUMBER- 1] * (ITERATION_NUMBER)
            END,
        E_EXP [ITERATION_NUMBER] =  
          CASE
            WHEN ITERATION_NUMBER= 0 THEN    
                EXP(1)
            WHEN ITERATION_NUMBER> 0 THEN
                E_EXP[ITERATION_NUMBER-1]
            END,
        E_COMPUTED [ITERATION_NUMBER] =
            CASE ITERATION_NUMBER
                WHEN 0 THEN 1
                ELSE
                      E_COMPUTED[ITERATION_NUMBER- 1]
                    + 1 / FACTORIAL[ITERATION_NUMBER]
            END,
        DIFF [ITERATION_NUMBER] =
            ABS (E_COMPUTED[ITERATION_NUMBER] - E_EXP[ITERATION_NUMBER]))

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