Obliczanie liczby pi w SQL

Kolejnym zastosowaniem klauzuli MODEL będzie napisanie prostego zapytania wyliczającego  liczbę pi  ( ludolfinę ) z zadaną dokładnością z wykorzystaniem algorytmu Leibniza
 
Zapytanie zwraca cztery kolumny, druga to wartość liczby PI obliczona z funkcji arcus sinus, trzecia
to przybliżona wartość pi obliczona ze wzoru Leibniza, czwarta to  moduł różnicy pomiędzy  kolumną drugą a trzecią. Zwiększając dokładność o kolejne rzędy wielkości  odczujemy istotne  wydłużenie działania zapytania. Iterowanie zakończy się w momencie spełnienia jednego z warunków: osiągniemy zakładaną dokładność lub liczba iteracji osiągnie  wartość umieszczona w nawiasie po  słowie kluczowym ITERATE .
Jak możemy się łatwo przekonać obserwując ostatnia kolumnę, jest to algorytm wolnozbieżny, jego zastosowanie  ma tylko charakter edukacyjny, aby pokazać możliwość  obsługi ciągów liczbowych przez iteracyjną klauzulę MODEL ..

SELECT
      d  + 1 "Nr iteracji",
      PI_ASIN "Pi z funkcji asin",
      4 *PI_COMPUTED "Wartość obliczona",
      DIFF "Różnica"
  FROM   (SELECT   0 d  FROM DUAL)
MODEL
   DIMENSION BY ( 0 d)
   MEASURES (
   CAST( 0 AS  NUMBER)  PI_ASIN,
   CAST( 0 AS  NUMBER)  PI_COMPUTED,
   CAST( 0 AS  NUMBER)  DIFF )   
   RULES
      ITERATE (1000000000) UNTIL (ITERATION_NUMBER >1 AND  DIFF [ITERATION_NUMBER ] < 0.01)
       (      
        PI_ASIN[ ITERATION_NUMBER ] =ASIN(1)*2,      
        PI_COMPUTED[ ITERATION_NUMBER]  =
            CASE ITERATION_NUMBER
                                WHEN 0 THEN 1
                    ELSE
                     PI_COMPUTED[ ITERATION_NUMBER -1 ] + POWER( -1, ITERATION_NUMBER) / (2*ITERATION_NUMBER +1)                   
           END,                     
       DIFF [ ITERATION_NUMBER] = ABS(4*PI_COMPUTED[ ITERATION_NUMBER ] - PI_ASIN[ITERATION_NUMBER] ))

Wyszukiwanie kodów błędów Oracle w tekście

Poniższe zapytanie ma za zadanie wyszukiwać w wejściowym tekście różnych kodów błędów Oracle. Kody błędów wyszukiwane są za pomocą wyrażeń regularnych. Użycie funkcji uprościło zapytanie i umożliwiło wykonanie  DISTINCT

WITH FUNCTION PARSE(ARG VARCHAR2) RETURN VARCHAR2 AS

vc_Wynik VARCHAR2(100 CHAR);

BEGIN

  SELECT LISTAGG(BLAD, ',')  WITHIN GROUP( ORDER BY 1) INTO vc_Wynik

FROM

    (SELECT  DISTINCT REGEXP_SUBSTR( arg, 'ORA[[:punct:]][[:digit:]]{4,5}',1, LEVEL) BLAD    FROM DUAL

        CONNECT BY LEVEL <=  REGEXP_COUNT( arg, 'ORA[[:punct:]][[:digit:]]{4,5}'));

   RETURN vc_Wynik;     

END;

SELECT PARSE( 'pierwszy ORA-33344 Drugi ORA-2342 Trzeci ORA ORA-66666--')  FROM DUAL

Kalendarz na rok 2016 napisany dla bazy w wersji 12c

Poniżej jest kolejny przykład wykorzystania klauzuli model w wersji iteracyjnej, tym razem do wygenerowania kalendarza z uwzględnieniem świąt i dni roboczych na rok 2016. Jest zdefiniowany jeden wymiar oraz cztery miary: data, nazwa dnia tygodnia, nazwa święta obchodzonego w danym dniu oraz informacja, czy dany dzień jest roboczy.
Dodatkowo został przedstawiony sposób tworzenia funkcji nieskładowanych definiowanych jedynie na potrzeby SQL. To jest bardzo wygodne rozwiązanie,  bo upraszcza składnię zapytań,  zamiast komplikować składnię zapytania, niektóre fragmenty czytelniej jest przenieść do funkcji - np. kiedy byśmy chcieli napisać ogólny algorytm obliczania świąt ruchomych,  to moim zdaniem lepiej umieścić go w procedurze Fdb_JestSwieto.
Przy pisaniu zapytania położyłem większy nacisk na czytelność niż wydajność..




WITH FUNCTION Fdb_JestSwieto(pd_dt DATE) RETURN VARCHAR2 AS
pn_Month NUMBER;
pn_Day NUMBER;
BEGIN
   pn_Day := EXTRACT (DAY FROM pd_dt);
   pn_Month := EXTRACT (MONTH FROM pd_dt);
  IF pn_Month = 1 AND pn_Day =1 THEN
    RETURN 'Nowy Rok';
  ELSIF pn_Month = 1 AND pn_Day =6 THEN
    RETURN 'Święto Trzech Króli' ;
  ELSIF pn_Month = 3 AND pn_Day =27 THEN
    RETURN 'Wielkanoc' ;
  ELSIF pn_Month = 3 AND pn_Day =28 THEN
    RETURN 'Poniedziałek Wielkanocny';
  ELSIF pn_Month = 5 AND pn_Day =1 THEN
    RETURN 'Święto Pracy';
  ELSIF pn_Month = 5 AND pn_Day =3 THEN
    RETURN 'Święto Konstytucji 3 Maja';
  ELSIF pn_Month = 5 AND pn_Day =15 THEN
    RETURN 'Zielone Świątki';
  ELSIF pn_Month = 5 AND pn_Day =26 THEN
    RETURN 'Boże Ciało';
  ELSIF pn_Month = 8 AND pn_Day =15 THEN
    RETURN 'Wniebowzięcie NMP';
  ELSIF pn_Month = 11 AND pn_Day =1 THEN
    RETURN 'Wszystkich Świętych';
  ELSIF pn_Month = 11 AND pn_Day =11 THEN
    RETURN 'Święto Niepodległości';
  ELSIF pn_Month = 12 AND pn_Day IN (25, 26) THEN
    RETURN 'Boże Narodzenie'; 
  END IF;
   RETURN NULL;
END;
SELECT                                                   
    DZIEN , DZIEN_TYGODNIA, SWIETO,
    DZIEN_ROBOCZY
  FROM   (SELECT   0 a  FROM DUAL)
MODEL
   DIMENSION BY (0 lp)
   MEASURES (TO_DATE('2016-01-01', 'YYYY-MM-DD') DZIEN,
CAST(NULL AS VARCHAR2(30 CHAR)) SWIETO,
CAST(NULL AS VARCHAR2(30 CHAR)) DZIEN_TYGODNIA,
    CAST( NULL AS NUMBER(1)) DZIEN_ROBOCZY  )
   RULES
      ITERATE (10000000) UNTIL ( ITERATION_NUMBER = 365)
      (
        DZIEN[ ITERATION_NUMBER ] = DZIEN[0] + ITERATION_NUMBER,
        SWIETO[ ITERATION_NUMBER ] = Fdb_JestSwieto( DZIEN [ ITERATION_NUMBER ]),
        DZIEN_TYGODNIA[ ITERATION_NUMBER ] = TO_CHAR(DZIEN [   ITERATION_NUMBER ], 'DAY'),
        DZIEN_ROBOCZY [ ITERATION_NUMBER ] = CASE  WHEN SWIETO [ ITERATION_NUMBER ]  IS NOT NULL OR TO_CHAR(DZIEN [ ITERATION_NUMBER ], 'D') IN ('6','7')
         THEN 0
          ELSE 1
       END
      )
     
     

Uproszczony kalkulator wynagrodzeń za rok 2015 n. e.

 Poniżej zamieszczam kolejny przykład wykorzystania klauzuli model w wersji iteracyjnej, który przelicza wynagrodzenia brutto z umowy o pracę na poszczególne składniki po stronie pracownika w tym kwotę netto. Nie obsługuje progresji podatkowej ani ograniczenia rocznej podstawy wymiaru składek na ubezpieczenie emerytalne i rentowe (tzw. trzydziestokrotności  prognozowanego przeciętnego wynagrodzenia miesięcznego w gospodarce narodowej na dany rok kalendarzowy).
Istotą przykładu jest pokazanie w jaki elegancki sposób, zbliżony do obliczeń w MS Excel, można przedstawić  niektóre obliczenia finansowe, a zwłaszcza odwoływanie się do wcześniej obliczonych komórek. Zapis taki jest intuicyjny i łatwy do modyfikacji..Arkusz jest jednowymiarowy  w ujęciu klauzuli model, podstawową miarą (do modyfikowania przez użytkownika) jest kwota brutto zaznaczona na zielono. Użytkownik w łatwy sposób może dodawać własne miary np. obciążenia po stronie  pracodawcy lub w inny sposób rozszerzyć poniższe zapytanie.


SELECT i lp,
       DATA_WYPLATY AS "Data wypłaty" ,
       KWOTA_BRUTTO,
       SKLADKA_EMERYTALNA_PRACOWNIK,
       SKLADKA_RENTOWA_PRACOWNIK,
       SKLADKA_WYPADKOWA_PRACOWNIK,
       SKLADKI_PRACOWNIK_RAZEM,      
       DOCHOD_PRACOWNIKA,
       SKLADKA_ZDROWOTNA ,
       ZALICZKA_PODATEK,
       WYNAGRODZENIE_NETTO            
  FROM (SELECT 1 FROM DUAL)
MODEL
   DIMENSION BY (0 d)
   MEASURES (6000 KWOTA_BRUTTO,
    TO_DATE( '2015-01-01', 'YYYY-MM-DD') DATA_WYPLATY  , 0 i,   
    0 SKLADKA_EMERYTALNA_PRACOWNIK,
    0 SKLADKA_RENTOWA_PRACOWNIK,
    0 SKLADKA_WYPADKOWA_PRACOWNIK,    
    0 SKLADKI_PRACOWNIK_RAZEM,
    111.25 KOSZT_UZYSKANIA_PRZYCHODU /*Pracownik niedojeżdżający*/ ,
    0 DOCHOD_PRACOWNIKA,
    0 SKLADKA_ZDROWOTNA,
    0 ZALICZKA_PODATEK,
    0 WYNAGRODZENIE_NETTO )
   RULES
      ITERATE (100) UNTIL (ITERATION_NUMBER >= 11)
       (          
           i[ITERATION_NUMBER] = ITERATION_NUMBER +1,           
           DATA_WYPLATY [ITERATION_NUMBER ] =  LAST_DAY(DECODE( DATA_WYPLATY [ITERATION_NUMBER -1],  NULL, DATA_WYPLATY [ITERATION_NUMBER], 
           ADD_MONTHS( DATA_WYPLATY [ITERATION_NUMBER -1],  1))),
           KWOTA_BRUTTO[ITERATION_NUMBER ] = KWOTA_BRUTTO[ 0 ],        
           SKLADKA_EMERYTALNA_PRACOWNIK[ ITERATION_NUMBER ] = ROUND(KWOTA_BRUTTO[ITERATION_NUMBER ]   *  0.0976, 2),
           SKLADKA_RENTOWA_PRACOWNIK[ ITERATION_NUMBER ] = ROUND(KWOTA_BRUTTO[ITERATION_NUMBER ]   *  0.015, 2),
           SKLADKA_WYPADKOWA_PRACOWNIK[ ITERATION_NUMBER ] = ROUND(KWOTA_BRUTTO[ITERATION_NUMBER ]   *  0.0245, 2) ,
           SKLADKI_PRACOWNIK_RAZEM[ITERATION_NUMBER ]
                = SKLADKA_EMERYTALNA_PRACOWNIK[ ITERATION_NUMBER ] + SKLADKA_RENTOWA_PRACOWNIK[ ITERATION_NUMBER ]
                    +SKLADKA_WYPADKOWA_PRACOWNIK[ ITERATION_NUMBER ],                                              
           DOCHOD_PRACOWNIKA[ITERATION_NUMBER ] = ROUND( KWOTA_BRUTTO[ITERATION_NUMBER ] -SKLADKI_PRACOWNIK_RAZEM[ITERATION_NUMBER ] -  KOSZT_UZYSKANIA_PRZYCHODU[0]),
           SKLADKA_ZDROWOTNA[ITERATION_NUMBER ] = ROUND( (KWOTA_BRUTTO[ITERATION_NUMBER ]  - SKLADKI_PRACOWNIK_RAZEM[0]) * 0.09, 2),
           ZALICZKA_PODATEK[ITERATION_NUMBER ] = ROUND(DOCHOD_PRACOWNIKA[ITERATION_NUMBER ] * 0.18  -  46.33 - ROUND( (DOCHOD_PRACOWNIKA[ITERATION_NUMBER ]  + KOSZT_UZYSKANIA_PRZYCHODU[0]) * 0.0775)),
           WYNAGRODZENIE_NETTO[ITERATION_NUMBER ]= KWOTA_BRUTTO [ITERATION_NUMBER ]   - SKLADKI_PRACOWNIK_RAZEM[ITERATION_NUMBER ]  - SKLADKA_ZDROWOTNA[ITERATION_NUMBER ] - ZALICZKA_PODATEK[ITERATION_NUMBER ]                                    
      )

Jeden parametr sekwencji a wydajność przy wstawianiu

Sekwencja zapewnia unikalność identyfikatorów, ale nie ich ciągłość na zbiorze liczb całkowitych.  W zależności od liczby wstawianych rekordów warto buforować w pamięci kolejne wartości sekwencji, zwłaszcza przy masowym wstawianiu. Stwórzmy sekwencję
CREATE SEQUENCE SEQ_START START WITH 1  CACHE 2;
oraz prostą tabelę, gdzie w polu ID będziemy wstawiać kolejne wartości sekwencji
CREATE TABLE TBL_TEST
(
 ID NUMBER NOT NULL,
 DESCRIPTION_1 VARCHAR2(40 CHAR) NOT NULL,
 DESCRIPTION_2 VARCHAR2(40 CHAR) NOT NULL,
 DESCRIPTION_3 VARCHAR2(40 CHAR) NOT NULL
);

Poniższy blok wstawia do pustej tabeli  sto tysięcy rekordów bez korzystania z sekwencji. Uśredniony czas wykonania to 4,3 sekundy..
DECLARE
    vn_Start NUMBER;
BEGIN
  EXECUTE IMMEDIATE  'TRUNCATE TABLE TBL_TEST';
  vn_Start := dbms_utility.get_time;
  
  FOR EL IN  1..100000
   LOOP
       INSERT INTO ALTAIRDB.TBL_TEST (
        ID, DESCRIPTION_1, DESCRIPTION_2,
        DESCRIPTION_3)
    VALUES (  EL /* ID */,
        'DESCRPTION_1 ' || EL /* DESCRIPTION_1 */,
        'DESCRPTION_2 ' || EL /* DESCRIPTION_2 */,
        'DESCRPTION_3 ' || EL /* DESCRIPTION_3 */ );
   END LOOP;
   COMMIT;
dbms_output.put_line( ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );
END;

W poniższym przykładzie wstawiamy wielokrotnie do pustej tabeli po  sto tysięcy rekordów z wykorzystaniem sekwencji z różnym parametrem buforowania.
DECLARE.
vn_Start NUMBER;
BEGIN
  FOR EL_OUT IN (SELECT COLUMN_VALUE AS CACHE_SIZE,
                    DECODE( COLUMN_VALUE, 1 , 'NOCACHE', 'CACHE ' || COLUMN_VALUE)   AS CACHE_DDL
                 FROM TABLE(SYS.ODCINUMBERLIST(1, 2,5,10, 20, 50, 100, 500, 1000, 5000, 10000))) 
  LOOP
    BEGIN
        EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_START';
    EXCEPTION WHEN OTHERS THEN
        NULL;   
    END;
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TBL_TEST';
  EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_START START WITH 1 ' || EL_OUT.CACHE_DDL;
  vn_Start := dbms_utility.get_time;
    FOR EL_IN IN  1..100000
       LOOP
           INSERT INTO TBL_TEST (
            ID, DESCRIPTION_1, DESCRIPTION_2,
            DESCRIPTION_3)
        VALUES (  SEQ_START.nextval /* ID */,
            'DESCRPTION_1 ' || EL_IN /* DESCRIPTION_1 */,
            'DESCRPTION_2 ' || EL_IN /* DESCRIPTION_2 */,
            'DESCRPTION_3 ' || EL_IN /* DESCRIPTION_3 */ );
       END LOOP;
    COMMIT;
  dbms_output.put_line( EL_OUT.CACHE_DDL || ' - ' || ROUND( (dbms_utility.get_time- vn_start)/100, 2 ) || ' sekund...' );
  END LOOP;
END;

Wyniki:
NOCACHE - 26,9 sekund...
CACHE 2 - 17,82 sekund...
CACHE 5 - 10,82 sekund...
CACHE 10 - 8,78 sekund...
CACHE 20 - 7,79 sekund...
CACHE 50 - 7,17 sekund...
CACHE 100 - 7,01 sekund...
CACHE 500 - 6,94 sekund...
CACHE 1000 - 6,95 sekund...
CACHE 5000 - 6,87 sekund...
CACHE 10000 - 6,56 sekund...

To jest przykład na to, jak mało ważne  parametry mogą istotnie zmniejszyć wydajność,zwłaszcza dla masowego przetwarzania...