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;
/

Niektóre parametry bazy a możliwośc kompilacji i poprawność kodu

Przenosząc kod PL/SQL pomiędzy serwerami można się natknąć na kilka ciekawych zagwozdek..
Skupiujemy schematy, uprawnienia, a pomimo tego kod może działać źle lub nie działać wcale
Ze względu  na rożne ustawienia serwerów bazy na takiej samej wersji bazy danych kod może nie chcieć się skompilować lub działać inaczej..
compatible - jeśli mamy ustaloną zgodność z poprzednią wersją bazy danych, a korzystamy z  dobrodziejstw najnowszej, mamy gwarantowany błąd kompilacji, np. kod zawierający instrukcję CONTINUE w pętlach przestanie się kompilować
open_links, open_links_per_instance - oba parametry ustalają  maksymalną lczbę połączeń do innych baz danych per sesję/instancję. domyślna wartość dla obu parametrów to 4.  Jeśli mamy kod pobierający dane z większej ilości zewnętrznych baz, nie zostanie on skompilowany..
remote_depedencies_mode - jeśli w złożonych aplikacjach PL/SQL korzystających z wywołań RPC zmienia się wartość parametru z signature na timestamp -może to być źródłem błędów kompilacji i wykonania
plsql_ccflags - są to flagi kompilacji warunkowej.  Wg mnie to potencjalne źródło mnóstwa kłopotów.. Rozważmy poniższy przykład
CREATE OR REPLACE PROCEDURE PDB_Conditional_Compilation IS
BEGIN
 DBMS_OUTPUT.PUT_LINE('START');
 $IF $$SPECIAL_COMPILATION $THEN
  DBMS_OUTPUT.PUT_LINE('Special compilation');
 $END
 DBMS_OUTPUT.PUT_LINE('STOP');
END;
Jeśli podczas kompilacji procedury nie ustawimy parametru $$SPECIAL_COMPILATION w opcji plsql_ccflags, i zmienna nie będzie ustawiona na poziomie sesji, kod wykona się bez instrukcji
DBMS_OUTPUT.PUT_LINE('Special compilation');
Można oczywiście z wykorzystaniem kompilacji warunkowej napisać  wyjątkowo złośliwy kod jak poniżej, ale jest to przypadek teoretyczny:
   $IF dbms_db_version.ver_le_11 $THEN
           ....kod....
   $ELSE
       RAISE_APPLICATION_ERROR(-20100, 'Kto mnie wykryje ??');
   $END

nls_length_semantics - problem został dokładniej omówiony w poniższym linku
Semantyka bajtowa i znakowa a poprawność kodu
Ustawienie zmiennej na semantykę bajtową może powodować błędy kompilacji np. poniższa deklaracja zmiennej:
 vc_Test VARCHAR2(4) :=  'kość';
wewnątrz bloku stanie się przyczyną błędu:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Taki sam błąd (ale już wykonania, nie kompilacji) wygeneruje poniższy blok:

DECLARE

 vc_Test VARCHAR2(4);

BEGIN

  vc_Test  :=  'kość';
END;