Ściągawka dla Kolegi - dyrektora handlowego w pewnej firmie IT - zastosowanie złączeń kartezjańskich

Zainspirowany bardzo bogatym korposłownictwem z zakresu prezentacji handlowych rozwiązań i usług postanowiłem  napisać ściągawkę do wykorzystania przez przesympatycznego prelegenta. Przykład też pokazuje w jaki sposób zamienić elementy listy na  kolejne wiersze w strukturze zbliżonej do tabeli. Świadome pominięcie warunków złączenia ma na celu wygenerowanie wszystkich możliwych  połączeń bardzo profesjonalnych sformułowań..

SELECT TABLE_1.COLUMN_VALUE || ' ' ||
     TABLE_2.COLUMN_VALUE  || ' ' ||
     TABLE_3.COLUMN_VALUE || ' ' ||
     TABLE_4.COLUMN_VALUE || ' ' || 
     TABLE_5.COLUMN_VALUE || ' ' ||
     TABLE_6.COLUMN_VALUE || ' ' ||
     TABLE_7.COLUMN_VALUE    
FROM
 TABLE(SYS.ODCIVARCHAR2LIST('Przestrzeganie najwyższych standardów podczas ',
    'Ciągłe doskonalenie procesu',
    'Wieloletnie doświadczenie w zakresie',
    'Unikalny zbiór kompetencji naszego zespołu w zakresie')) TABLE_1,
 TABLE(SYS.ODCIVARCHAR2LIST( 'analizy', 'tworzenia', 'projektowania', 'testowania', 'wdrażania' )) TABLE_2,
 TABLE(SYS.ODCIVARCHAR2LIST( 'skalowalnych ', 'wydajnych', 'niezawodnych' , 'optymalnie wspierających biznes') ) TABLE_3,
 TABLE(SYS.ODCIVARCHAR2LIST( 'aplikacji', 'systemów', 'kompleksowych rozwiązań') ) TABLE_4,
 TABLE(SYS.ODCIVARCHAR2LIST( 'w największych bankach', 'w kluczowych organach administracji państwowej', 'w największych zakładach produkcyjnych', 'w największych sieciach handlowych') ) TABLE_5,
 TABLE(SYS.ODCIVARCHAR2LIST( 'potwierdza', 'zapewnia', 'trafnie weryfikuje') ) TABLE_6,
 TABLE(SYS.ODCIVARCHAR2LIST( 'wybór naszej firmy jako gwarancję sukcesu ',
  'korzyści z długofalowej współpracy', 'terminowe wdrożenie systemu', 'uzyskanie przewagi technologiczną dzięki naszej firmie') ) TABLE_7

Pseudominizacja numeru PESEL - przykład rozwiązania

Problem pseudominizacjidanych  występuje często kiedy dane z systemów zawierających dane osobowe lub dane wrażliwe musimy  udostępnić osobom  trzecim,  ukrywając te dane i możliwość identyfikacji osób

Założenia algorytmu:

• nowy PESEL dla danej osoby zachowuje informacje o płci i o dacie urodzenia
• numer ewidencyjny w obrębie daty urodzenia  jest numerowany od stałej cn_DayIndex w dół
• zakłada się, że parametry są poprawne -  parametr pc_PESEL nie jest walidowany
• na wejściu algorytmu powinien być zbiór SELECT DISTINCT PESEL FROM....
• przed  wykonaniem anonimizacji należy wykonać blok:

              BEGIN
                    PCKG_ANONYMIZE_DATA.PDB_CLEARINTERNALDATA;
             END;   

Jak to działa??
Poniższe zapytanie generuje nowy PESEL dla Jarosława K.
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
Otrzymamy       49061899990
Wywołajmy teraz  funkcję  5 razy
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
CONNECT BY LEVEL <= 5
Jako wynik otrzymamy
49061899976
49061899952
49061899938
49061899914
49061899891
Czyli widzimy, że dla kolejnych nr  PESELw obrębie daty urodzenia generowane są kolejne nowe PESELE, ale nie jest sprawdzana unikalność nr PESEL na wejściu..

Czy nowe nr PESEL zapewniają pełną pseudonimizację ze względu na ten nr. Nie..,  Jeśli np wiemy , ze  w bazie było dwóch  klientów  w wieku po 80 lat, to możemy z dużym prawdopodobieństwem ich  zidentyfikować, jeśli maja różne daty urodzenia. Dla  lat, w których jest dużo klientów  w poszczególnych dniach będzie na podstawie  tylko nr  PESEL niemożliwe

CREATE OR REPLACE PACKAGE PCKG_ANONYMIZE_DATA IS
    PROCEDURE PDB_ClearInternalData;  
    FUNCTION FDB_GenerateNewPESEL(pc_PESEL VARCHAR2) RETURN VARCHAR2 ;
    PRAGMA RESTRICT_REFERENCES (FDB_GenerateNewPESEL, WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY PCKG_ANONYMIZE_DATA
IS
   TYPE T_NUMBERS IS TABLE OF VARCHAR2 (4)
      INDEX BY VARCHAR2 (6);

   Vt_Numbers             T_NUMBERS;
   cn_DayIndex   CONSTANT NUMBER (4) := 9999;
/************************************************************************/
   PROCEDURE PDB_ClearInternalData
   IS
   BEGIN
      Vt_Numbers.DELETE;
   END;
/************************************************************************/
   FUNCTION FDB_GenerateNewPESEL (pc_PESEL VARCHAR2 )
      RETURN VARCHAR2
   IS
      TYPE t_Weights IS VARRAY (10) OF SIMPLE_INTEGER;

      vt_Weights    t_weights
                       := t_weights (1,3,7,9,1,3,7,9,1,3);
      vn_CheckSum   SIMPLE_INTEGER := 0;
      vc_PESEL      VARCHAR2 (15);
      vc_DayIndex   NUMBER (4) := '0';
      vn_Plec       NUMBER (1);
   BEGIN
      vc_Pesel := SUBSTR (pc_PESEL, 1, 6);
      vn_Plec := SUBSTR (pc_PESEL, 10, 1);
--------------------
      IF  vt_Numbers.exists(vc_PESEL ) THEN              
        vc_DayIndex := NVL (Vt_Numbers (vc_Pesel) - 1, cn_DayIndex);        
      ELSE  
        vc_DayIndex := cn_DayIndex;           
      END IF;
--------------------
      IF MOD (vc_DayIndex, 2) <> MOD (vn_Plec, 2) THEN
         vc_DayIndex := vc_DayIndex - 1;
      END IF;
--------------------
      Vt_Numbers (vc_Pesel) := vc_DayIndex;
      vc_Pesel :=vc_Pesel || LPAD (TO_CHAR (vc_DayIndex), 4, '0');
--------------------
      FOR i IN 1 .. vt_Weights.COUNT
      LOOP
         vn_CheckSum :=
              vn_CheckSum
            + TO_NUMBER (SUBSTR (vc_PESEL, i, 1)) * vt_Weights (i);
      END LOOP;
--------------------
      IF MOD (vn_CheckSum, 10) = 0 THEN         vc_PESEL := vc_PESEL || '0'; 
    ELSE
         vc_PESEL := vc_PESEL || (10 - MOD (vn_CheckSum, 10));
      END IF;
--------------------
      RETURN vc_Pesel;
   /*EXCEPTION WHEN OTHERS THEN
      RETURN NULL;*/
   END;
/*******************************************************************/
BEGIN  
    Vt_Numbers.DELETE;
END;
/

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
      )