Korzystanie z funkcji użytkownika w SQL

Poziomy czystości (Purity Levels):

Poziom Rozwinięcie Znaczenie
WNDS Writes No Database State Podprogram nie zmienia zawartości tabel bazodanowych.
RNDS Reads No DatabaseState Podprogram nie odwołuje się do zawartości tabel bazodanowych.
WNPS Writes No Package State Podprogram nie zmienia wartości zmiennych pakietowych
RNPS Reads No Package State Podprogram nie odwołuje się do zmiennych pakietowyc

Funkcje zdefiniowane przez użytkownika mogą być również (od wersji PL/SQL 2.1) używane w wyrażeniach SQL, pod warunkiem, że spełniają pewne wymogi.

  • Zachowują WNDS
  • W przypadku pracy przez DB link muszą zachowywać także RNPS i WNPS
  • W przypadku wywoływania funkcji w przypadkach innych niż w klauzulach SELECT, VALUES, SET funkcje te muszą zachowywać WNPS
  • Funkcje mogą mieć tylko parametry typu IN
  • Wszystkie parametry i zwracane wartości muszą być typów bazodanowych
  • Funkcje nie mogą zawierać poleceń do sterowania transakcjami
  • W przypadku istnienia parametrów domyślnych, muszą one zostać jawnie podane
  • Można stosować tylko pozycyjną notację przekazywania parametrów

Poziom czystości funkcji jest sprawdzany w momencie wykonywania się tej funkcji i wtedy ewentualnie zgłaszany jest błąd.

Aby poziom czystości nie musiał być weryfikowany przed wykonaniem, należy stosować odpowiednią pragmę, która mówi, jaki poziom czystości jest zachowany w funkcji (kontrola jest przeprowadzana w momencie kompilacji).


PRAGMA RESTRICT_REFERENCES (funkcja, lista_zachowanych_poziomów)

Niestety pragma ta może być umieszczana tylko dla podprogramów zgrupowanych w pakietach.

Przypinanie pakietów, determinizm i równoległość, poziomy czystości

Purity Levels

Jeżeli chcemy, aby Oracle pozwalał wywoływać podprogramy zdefiniowane w pakietach w SQL’u bez sprawdzania ich poziomu czystości w trakcie wykonywania, musimy zażądać, aby poziom był sprawdzony tylko jeden raz, już podczas kompilacji. Robi się to poprzez wpisanie w specyfikacji pakietu odpowiedniej pragmy. 


PRAGMA RESTRICT_REFERENCES (nazwa_funkcji, poziomy_czystości);

Ponieważ przed wykonaniem się funkcji w pakiecie wykonywana jest także sekcja inicjalizująca pakietu, więc jeżeli została ona zdefiniowana, to również ona musi spełniać warunki narzucone przez pragmę; Jeżeli chcemy w jednym miejscu zapewnić sobie, że wszystkie podprogramy w pakiecie będą miały zachowany odpowiedni poziom czystości możemy jednokrotnie w pakiecie użyć pragmy i zamiast nazwy funkcji użyć słowa DEFAULT 


PRAGMA RESTRICT_REFERENCES (DEFAULT, poziomy_czystości);

W przypadku, gdy kompilator PL/SQL nie może sprawdzić poprawności poziomów czystości w momencie kompilacji, np., gdy mamy do czynienia z funkcją napisaną w javie lub C, możemy stosować słowo kluczowe TRUST, które pozwala kompilatorowi zaufać, że poziomy czystości rzeczywiście są zachowane. 


PRAGMA RESTRICT_REFERENCES (nazwa_funkcji, poziomy, TRUST);

Przykład 


CREATE OR REPLACE PACKAGE P1 IS
  FUNCTION funkcja1 RETURN VARCHAR2 IS
    LANGUAGE JAVA
    NAME 'Test.Uppercase(char[]) RETURN char[]';
    PRAGMA RESTRICT_REFERENCES(funkcja1, WNDS, WNPS, TRUST);
  PROCEDURE procedura1;
  PRAGMA RESTRICT_REFERENCES(procedura1, WNDS);
END;

CREATE OR REPLACE PACKAGE BODY P1 IS
  PROCEDURE procedura1 IS
    v1 varchar2(110);
  BEGIN
    v1 := funkcja1;
  END;
END;

Przypinanie pakietów

Każdy podprogram przed wykonaniem jest wczytywany z dysku do pamięci w tzw. obszar Shared Pool. Po wykonaniu najczęściej jest z pamięci usuwany. Decyduje o tym algorytm LRU.

Na proces ten można mieć wpływ korzystając z pakietu wbudowanego DBMS_SHARED_POOL. Pakiet ten zawiera między innymi procedury: KEEP, UNKEEP. 


PROCEDURE keep (nazwa_pakietu);
PROCEDURE unkeep (nazwa_pakietu);

Po wywołaniu procedury keep pakiet nie zostanie nigdy usunięty z shared pool (nie będzie podlegał działaniu algorytmu LRU), dopóki nie zostanie jawnie usunięty procedurą unkeep.

Pakiet dbms_shared_pool nie jest tworzony automatycznie podczas tworzenia bazy danych. Aby z niego korzystać, należy na użytkowniku SYS uruchomić skrypt „dbmspool.sql”.

Rozmiary pakietów

Ograniczenia na rozmiar pakietu:

  • 32 K linii (w 8i 64M)
  • 32K procedur i funkcji
  • 232 stałych znakowych

Jeżeli któraś z tych wielkości zostanie przekroczona, to podczas kompilacji generowany jest błąd „PLS-123: program too large”

Generalnie pakiety powinny być możliwie małe, aby można nimi było łatwiej zarządzać. Rozmiar pakietów również ma wpływ na efektywne wykorzystanie pamięci, ponieważ w momencie wykonania któregokolwiek podprogramu zawartego w pakiecie cały kod pakietu jest ładowany do pamięci.

Podpowiedzi optymalizatora

W Oracle 8i dostępne są dwa nowe słowa kluczowe – DETERMINISTIC i PARALLEL_ENABLE, które, jeśli istnieją, są wykorzystywane przez optymalizator do podniesienia wydajności.


CREATE [OR REPALCE] FUNCTION funkcja1 [lista_parametrów]
  RETURN typ
  [DETERMINISTIC]
  [PARALLEL_ENABLE]
IS
ciało_funkcji;

Funkcja jest deterministyczna, jeżeli ten sam zestaw parametrów zawsze powoduje zwrócenie tej samej wartości, bez względu na stan danych oraz stan pakietów. Pozwala to na niewykonywanie wielokrotnie tej samej funkcji przez Oracle, jeżeli kiedyś już wywołanie takie miało miejsce i jeszcze jest pamiętana zwrócona wartość

Przykład 


CREATE FUNCTION parzyste (p1 IN number) RETURN boolean
  DETERMINISTIC
IS
  IF mod(p1,2) = 0 THEN
    RETURN TRUE;
  ELSE 
    RETURN FALSE:
  END IF;
END:

Wszystkie funkcje używane w indeksach opartych na funkcjach muszą być deterministyczne. Jeżeli funkcja ma być używana w operacjach zrównoleglonych, to nie może zawierać odwołań do zmiennych pakietowych. Można to zapewnić poprzez pragmę i poziomy WNPS i RNPS lub poprzez wskazówkę PARALLEL_ENABLE. Kompilator PL/SQL nie sprawdza w momencie kompilacji, czy podprogram zaznaczony jako deterministyczny, rzeczywiście taki jest.