Algorytmy rekurencyjne w SQL

 Możliwe?? Tak, wystarczy polubić klauzulę model..

Poniżej w dwóch zapytaniach są  algorytmy zapisane w wersji rekurencyjnej

• ciąg Fibonacciego
• silnia
• algorytm Euklidesa do wyznaczania największego wspólnego podzielnika

Przedstawione są dwie techniki:

•  modele iteracyjne -znakomicie nadają się do implementacji jednoargumentowych funkcji rekurencyjnych, a ich zapis jest niemal intuicyjny. Niestety przy większej liczbie parametrów pojawiają się problemy cyklicznego wyliczania komórek
• modele nieiteracyjne  - formuły są nieco bardziej zawiłe, ale można obsłużyć funkcje wieloargumentowe

SELECT                                                    
      d  , f AS FIBBONACCI,
      S AS SILNIA
  FROM   (SELECT   0 d  FROM DUAL)
MODEL
   DIMENSION BY (0 d)
   MEASURES (0 f, 0 s)
   RULES
      ITERATE (10000000) 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)




Poniższy przykład można jeszcze nieco uprościć :)

SELECT   L1, L2, NWD
  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 NWD )
   RULES SEQUENTIAL ORDER
      (NWD [ANY, ANY] =
            CASE
               WHEN CV (l2) = 0
               THEN
                  CV (l1)
               WHEN CV (l2) <= CV (l1)
               THEN
                  NWD[CV (l2), MOD (CV (l1), CV (l2))]
            END,
      NWD [ANY, ANY] =
            CASE
               WHEN CV (l2) > CV (l1) THEN
                        NWD[CV (l2), CV (l1)]
               ELSE NWD[CV (l1), CV (l2)]
            END)

Trzy pułapki w PL/ SQL

1. Modyfikator result_cache dla funkcji niedeterministycznych przekształca je na deterministyczne
Funkcja deterministyczna jest to funkcja zwracająca dla takiego samego zbioru parametrów identyczny  wynik. Deterministyczności wymaga sie  od wyrażeń w indeksach  funkcyjnych i polach wyliczeniowych dla tabel.. Deterministyczność wyklucza użycie funkcji SYSDATE, funkcji losowych...

Rozważmy poniższy przykład:
 Skompilujmy poniższą funkcję:
 CREATE OR REPLACE FUNCTION Fdb_Test RETURN DATE
RESULT_CACHE
    IS
BEGIN
    RETURN SYSDATE;
END;



Następnie wywołajmy pięć  razy w odstępach kilkusekundowych:
SELECT Fdb_Test  FROM DUAL
O ile bufor dla result cache jest nieprzepełniony, to funkcja zwróci nam za każdym wywołaniem taką samą wartość. Poniższe sprawdzenie pokaże czterokrotne użycie bufora(za pierwszym razem rzeczywiście wywoła się SYSDATE):
SELECT SCAN_COUNT
   FROM  V$RESULT_CACHE_OBJECTS WHERE  NAMESPACE ='PLSQL' AND NAME LIKE '%FDB_TEST%'

2. Struktury zakotwiczone %ROWTYPE zapewniają uproszczenie wstawiania..
I z reguły jest to prawda
Stwórzmy tabelę:
CREATE TABLE TBL_TEST
      (ID NUMBER(4),
        OPIS VARCHAR2(50 CHAR)
       )
Można wówczas dokonać wstawiania w bardzo elegancki sposób bez podawania listy kolumn...
DECLARE
  vr_Test TBL_TEST%ROWTYPE;
BEGIN
  vr_Test.ID  := 1;
  vr_Test.Opis := 'Opis';
  INSERT INTO TBL_TEST VALUES vr_Test;
  COMMIT;
END;Zakotwiczenie przy wstawianiu będzie  dalej działało  przy dodaniu nowych pól, chyba, że to będą pola obliczeniowe..

Po dodaniu formuły
ALTER TABLE TBL_TEST  ADD FORMULA VARCHAR2(100 CHAR)  GENERATED ALWAYS  AS ( TO_CHAR(ID) || ' ' || OPIS)
przy próbie wykonania powyższego bloku pojawi się komunikat
ORA-54013: INSERT operation disallowed on virtual columns
Szkoda, że obie możliwości nie mogą działać jednocześnie (:

3, Triggery DML blokują jedynie operacje DML
Jeśli chcemy zablokować  w sposób dynamiczny np. usuwanie z tabel, to nie można użytkownikom końcowym nadawać żadnych uprawnień DDL do tabeli..
Poniższy trigger
CREATE OR REPLACE TRIGGER .TRG_TBL_TEST__NO_DELETE
BEFORE DELETE
ON TBL_TEST
--w celu lepszej wydajności powinien to być triger  on statement, a nie FOR EACH ROW
BEGIN
  IF EXTRACT( HOUR FROM LOCALTIMESTAMP ) BETWEEN 8 AND 18 THEN
    RAISE_APPLICATION_ERROR (- 20100, 'Usuwanie w godzinach pracy z tabeli jest niedozwolone' );
  END IF;
END TRG_TBL_TEST__NO_DELETE;
nie zabezpieczy przed instrukcją DDL 
 TRUNCATE TABLE TBL_TEST
jeśli użytkownik końcowy posiadać będzie bezpośrednio nadane uprawnienie ALTER TABLE

NULL a poprawnośc algorytmów obliczeniowych..

Wartość NULL bywa przyczyną wielu błędów w algorytmach  obliczeniowych - większość  z nich wynika  braku inicjacji zmiennych:
 Rozważmy trzy przykłady:
1.Nie działa inicjalizacja dla typów zakotwiczonych w PL/SQL
CREATE TABLE TBL_TEST
 (FIELD NUMBER DEFAULT 0 NOT NULL);

 DECLARE
   vn_Number TBL_TEST.FIELD%TYPE;
 BEGIN
   IF vn_Number IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Inicjalizacja nie zadziałała');
   END IF;
 END;
 Powyższy przypadek jest dość podstępny- zakotwiczenia pobierają tylko informację o typie bez checków...
2.Funkcje agregujące

 CREATE TABLE TBL_TEST
 (FIELD NUMBER  );


ciąg instrukcji insert, takze z wartością NULL;
INSERT INTO TBL_TEST (FIELD) VALUES (NULL);
INSERT INTO TBL_TEST (FIELD) VALUES (1);
INSERT INTO TBL_TEST (FIELD) VALUES (8); 
COMMIT;
Wykonajmy teraz zapytanie:
SELECT SUM(FIELD), AVG(FIELD), AVG(NVL(FIELD,0)) FROM TBL_TEST
Otrzymane wyniki to
9        4.5         3
Powyższy wynik świadczy o tym, że funkcja AVG do liczenia średniej wybiera wartości tylko  NOT NULL.  Dlatego warto dokładnie przestudiować dokumentację opisującą działanie funkcji agregujących..

3. Operatory arytmetyczne

DECLARE
 vn_A NUMBER(2) :=1;
 vn_B NUMBER(2) ;
 vn_C NUMBER(3);
BEGIN
  vn_C := vn_A + vn_B;
  IF vn_C IS NULL THEN 
    DBMS_OUTPUT.PUT_LINE('RESULT IS NULL');
   END IF;
END; 
Dla operatorów arytmetycznych i wbudowanych funkcji matematycznych np. ABS i SIN, jeśli co najmniej jeden parametr jest NULL, to zwracana jest także wartość NULL..

Rozpatrując powyższe przykłady, nasuwa się oczywisty wniosek - należy unikać wartości NULL w algorytmach obliczeniowych.... są one źródłem bardzo złośliwych i podstępnych błędów
Proponuję zastosowanie poniższych zaleceń:

• na etapie projektowania struktury baz danych dla pól tabel wykorzystywanych w algorytmach numerycznych powinno się wymuszać wartość  NOT NULL.  Wartość domyślna jest niekonieczna, niech lepiej zostanie bezpośrednio zainicjowana w kodzie
•  należy zamiast zmiennych typu NUMBER, BINARY_INTEGER stosować zmienne typu SIMPLE_DOUBLE, SIMPLE_INTEGER, SIMPLE_FLOAT oraz inne z przedrostkiem SIMPLE, które wymagają inicjacji na poziomie deklaracji.. Wykonanie poniższego bloku

       DECLARE
           vn_Test SIMPLE_INTEGER;
       BEGIN
           vn_Test := 0;
       END;
zakończy się błędem: PLS-00218: a variable declared NOT NULL must have an initialization assignment. oprócz tego jakiekolwiek przypisanie wartości NULL do zmiennej vn_Test też wygeneruje wyjątek... Wyjątki wyłowią niebezpieczne fragmenty kodu w algorytmach..

• Jeśli musimy użyć typu NUMBER(12,2) to wówczas najlepiej stworzyć  nagłówek pakietu do definicji własnych typów

     CREATE OR REPLACE PACKAGE PCKG_MyTypes IS
          SUBTYPE t_Number_12_2 IS NUMBER(12,2) NOT NULL;
     END;
     wówczas wykonanie poniższego bloku
     DECLARE
        vn_Test PCKG_MyTypes.t_Number_12_2;
     BEGIN
        vn_test := 0;
   END;
   także zakończy się wyjątkiem : PLS-00218: a variable declared NOT NULL must have an initialization assignment

DBMS_METADATA - generowanie skryptów DDL

Bardzo ciekawym pakietem dostarczonym z bazą danych jest DBMS_METADATA – jego głównym zadaniem jest tworzenie skryptów DDL dla różnych obiektów bazodanowych..

Dokumentacji nie będę przepisywać, ale przedstawię kilka zastosowań

Należy zauważyć, że wywołania w instrukcjach SQL umożliwiają ograniczone możliwości wykorzystania API, skrypty PL/SQL pozwalają na bardziej zaawansowane mozliwości tworzenia skryptów DDL

• Skrypt do tworzenia listy uprawnień dla bieżącego użytkownika:

SELECT

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USER)

            ||

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USER)

            ||

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USER)

FROM DUAL

• Skrypt do tworzenia indeksów w bieżącym schemacie

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, USER) ||';' FROM USER_INDEXES WHERE VISIBILITY = 'VISIBLE'

• Skrypt do tworzenia bieżącego użytkownika

SELECT DBMS_METADATA.GET_DDL('USER', USER)

FROM DUAL – pod warunkiem, że użytkownik nie jest SYS/SYSDBA

• Przeniesienie tabel  ze schematu SOURCE_SCHEMA  do schematu DESTINATION_SCHEMA wszystkich z tabel z nazwami zaczynającymi się na B, z pominięciem atrybutów storage i  kluczy obcych…

zacznijmy od  stworzenia tabeli na wynikowy skrypt
CREATE  TABLE TBL_DDL_DATA(
DDL_SCRIPT CLOB DEFAULT EMPTY_CLOB()
);

poniżej skrypt

DECLARE

v_hdl NUMBER;

v_th1 NUMBER;

v_th2 NUMBER;

v_doc sys.ku$_ddls := sys.ku$_ddls();

   BEGIN

      delete from T_BLOB;

      v_hdl := DBMS_METADATA.OPEN('TABLE', 'LATEST');

      DBMS_METADATA.SET_FILTER (v_hdl,'SCHEMA_EXPR','

IN( ''SOURCE_SCHEMA'')');

      DBMS_METADATA.SET_FILTER (v_hdl,'NAME_EXPR','LIKE ''B%''');

      v_th1 := DBMS_METADATA.ADD_TRANSFORM (v_hdl,'MODIFY', NULL, 'TABLE');

      DBMS_METADATA.SET_REMAP_PARAM(v_th1,'REMAP_SCHEMA', 'SOURCE_SCHEMA', 'DESTINATION_SCHEMA');

      v_th2:=DBMS_METADATA.ADD_TRANSFORM(v_hdl,'DDL');

      DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,    'SQLTERMINATOR', TRUE);

DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,    'SEGMENT_ATTRIBUTES', FALSE);

DBMS_METADATA.SET_TRANSFORM_PARAM(v_th2,'REF_CONSTRAINTS', FALSE, 'TABLE');

DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_QUERY(v_hdl));

LOOP

   

    v_doc := DBMS_METADATA.FETCH_DDL(v_hdl);

    IF v_doc IS NOT EMPTY THEN

        IF v_doc.COUNT >0 THEN

            INSERT INTO TBL_DDL_DATA(DDL_SCRIPT)

            VALUES (v_doc(v_doc.last).DDLTEXT);

        END IF;

    END IF;

EXIT

WHEN v_doc IS NULL;

END LOOP;

COMMIT; 

DBMS_METADATA.CLOSE(v_hdl);

END;

Jeśli chcemy wykonać skrypt, zaleca się posiadać rolę CREATE TABLE do schematu docelowego

Moje uwagi:

• z poziomu SQL nie ma możliwości parametryzacji skryptów - pominięcia parametrów storage, ustawienia filtra, zmiany schematu - to wszystko można zrobić w PL/SQL
• Skrypty  dla lokalnych dla tabeli partycjonowanych działają dobrze pod warunkiem, że owe tabele są puste(zgodnie z założeniem pakiety – tworzenie  struktur, a nie ich rozbudowywanie)
• Skrypty DDL dla tabel partycjonowanych  interwałowo niekiedy są generowane błędnie
• Dłuższe skrypty opłaca się zapisywać do pól CLOB
• Pakiet jest dość powolny

Walidacja NIP, PESEL i REGON - im mniej kodu tym lepiej

W poniższym niewielkim pakiecie chciałem pokazać zastosowanie niektórych rzadziej wykorzystywanych możliwości języka PL/SQL, a mianowicie:

• kolekcji
• parametrów domyślnych
• typu SIMPLE_INTEGER ( ze względów wydajnościowych)

Zauważmy, że wszystkie trzy algorytmy walidacji dla NIP, PESEL i REGON  mają następujące podobne cechy:

• tekst na wejściu ma mieć długość o jedną cyfrę dłuższą niż długość tablicy wag
• w jednakowy sposób obliczamy sumę kontrolną - różnica jest w dzielniku
• walidacja sumy kontrolnej z ostatnim cyfrą wprowadzonego literału występuje w dwóch wersjach dla PESEL oraz dla NIP i REGON

Inne założenia i uwagi:

•  każda z funkcji zwraca jeden jeśli parametr jest poprawny i zero w przeciwnym przypadku
• pragma WNDS umozliwia wywołanie funkcji na poziomie SQL
• argument może być dowolnej długości i zawierać dowolną liczbę znaków nie będących cyframi za pomocą wyrażeń regularnych usuniemy je
• deklaracja tablic wag w części deklaratywnej ciała pakietu poprawiło wydajność o około 20% w porównaniu z deklaracja tablic wag wewnątrz ciał funkcji. Tablice tworzone są podczas pierwszego wywołania w danej sesji bazodanowej
• jest to kod szybki, ale stanowi pewien kompromis pomiędzy wydajnością a czytelnością
• kompilacja za pomocą poniższych parametrów (do kodu maszynowego z zaawansowaną optymalizacją) zwiększa wydajność około dwukrotnie: ALTER PACKAGE PCKG_WALIDUJ_IDS COMPILE PLSQL_CODE_TYPE = NATIVE  PLSQL_OPTIMIZE_LEVEL = 3
• jest to krótki protest  przeciwko kodowaniu metodą kopiuj & wklej
• wymagany jest Oracle 11g

CREATE OR REPLACE PACKAGE PCKG_WALIDUJ_IDS AS
    FUNCTION    FDB_Waliduj_NIP(pc_NIP VARCHAR2) RETURN INTEGER;
    FUNCTION    FDB_Waliduj_PESEL(pc_PESEL VARCHAR2) RETURN INTEGER;
    FUNCTION    FDB_Waliduj_REGON(pc_REGON VARCHAR2) RETURN INTEGER;
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_NIP, WNDS);
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_PESEL, WNDS);
    PRAGMA RESTRICT_REFERENCES(FDB_Waliduj_REGON, WNDS);
END;
/

CREATE OR REPLACE PACKAGE BODY PCKG_WALIDUJ_IDS IS
------------------------------------------------------
TYPE t_Wagi IS TABLE OF SIMPLE_INTEGER;
vt_NIP t_Wagi := t_Wagi(6, 5, 7, 2, 3, 4, 5, 6, 7 );
vt_PESEL t_Wagi := t_Wagi(1, 3, 7, 9, 1, 3, 7,9,1,3);
vt_REGON_9 t_Wagi := t_Wagi(8, 9, 2, 3, 4, 5, 6, 7);
vt_REGON_14 t_Wagi := t_Wagi( 2, 4, 8, 5, 0, 9, 7, 3, 6, 1, 2, 4, 8);
----------------------
FUNCTION PDB_Waliduj( pc_Literal VARCHAR2, pt_Wagi IN t_Wagi,
    pn_Modulo SIMPLE_INTEGER, pb_PESEL BOOLEAN :=FALSE) RETURN NUMBER
IS
    vc_Literal VARCHAR2(14 CHAR);
    vn_SumaKontr SIMPLE_INTEGER:=0;
BEGIN
    IF NVL(REGEXP_COUNT(pc_Literal,'[[:digit:]]'),0) <> pt_Wagi.COUNT + 1 THEN        
        RETURN 0;
     END IF;
---Suma kontrolna
    vc_Literal := REGEXP_REPLACE( pc_Literal,'[^[:digit:]]','');                      
    FOR i IN 1..pt_Wagi.COUNT
    LOOP
        vn_SumaKontr := vn_SumaKontr + TO_NUMBER(SUBSTR( vc_Literal, i,1)) * pt_Wagi(i);
    END LOOP;
---
    vn_SumaKontr := MOD(vn_SumaKontr, pn_Modulo);
--  Dla NIP poniższy przypadek nie powinien wystąpić, dla PESEL nie wystąpi
    IF vn_SumaKontr = 10 THEN
        vn_SumaKontr := 0;
    END IF;  
--  PESEL    
    IF pb_Pesel THEN
        IF vn_SumaKontr + TO_NUMBER(SUBSTR( vc_Literal, Length(vc_Literal),1)) IN ( 0, 10)  THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END IF;
-- NIP, REGON
    IF vn_SumaKontr = TO_NUMBER(SUBSTR( vc_Literal, Length(vc_Literal),1)) THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
----
FUNCTION  FDB_Waliduj_NIP(pc_NIP VARCHAR2) RETURN INTEGER
IS   
BEGIN    
    RETURN PDB_Waliduj(pc_NIP, vt_NIP, 11);           
END;
------------------------------------------------------  
FUNCTION  FDB_Waliduj_PESEL(pc_PESEL VARCHAR2) RETURN INTEGER
IS  
BEGIN
    RETURN PDB_Waliduj(pc_Pesel, vt_PESEL, 10, TRUE);
END;
------------------------------------------------------
FUNCTION  FDB_Waliduj_REGON(pc_REGON VARCHAR2) RETURN INTEGER
IS  
BEGIN           
    RETURN PDB_Waliduj(pc_REGON, vt_REGON_9,  11) +
    PDB_Waliduj(pc_REGON, vt_REGON_14, 11);
END;
END;
/