Vytváření uživatelských procedur a funkcí

Jak bylo řečeno, uložené procedury a funkce jsou objekty databáze stejně jako tabulky. Vytváříme je příkazem CREATE PROCEDURE, resp. CREATE FUNCTION. Syntaxe těchto příkazů je následující:

CREATE PROCEDURE jmeno_proc (parametry) IS

i NUMBER; -- deklarace proměných

BEGIN

    –tělo procedury

END;

CREATE FUNCTION jmeno_funkce (parametry) RETURN datovy_typ IS

i NUMBER;

BEGIN

  --tělo funkce

     RETURN vysledek;

END;

Místo příkazu CREATE můžeme použít CREATE OR REPLACE, který v případě, že procedura či funkce již existuje, provede její nahrazení. Při vytváření funkce na rozdíl od procedury musíme specifikovat datový typ výsledku funkce. Výsledek funkce spočítáme v těle funkce v libovolné proměnné, na konci funkce označíme zvolenou proměnnou jako proměnnou obsahující finální výsledek pomocí příkazu RETURN. Tímto příkazem vykonávání funkce končí. Parametry procedur a funkcí jsou proměnné, pomocí kterých předáváme proceduře či funkci vstupní hodnoty při jejím volání. U procedur mohou parametry sloužit i k předávání výsledků (OUTPUT parametry). Parametrů může být 0 až N, stejně jako interní proměnné mají svůj název a datový typ. Definují se za názvem procedury a funkce a vzájemně jsou odděleny čárkou.  

Jako příklad funkce můžeme uvést uživatelskou funkci, která ze dvou datumů spočíta věk, tedy rozdíl datumů v celých rocích.

CREATE OR REPLACE FUNCTION age (datum1 DATE, datum2 DATE) RETURN NUMBER

IS

roku NUMBER;

BEGIN

    roku := ABS(TRUNC(MONTHS_BETWEEN(datum1, datum2) / 12));

    RETURN roku;

END;

Na vstupní hodnoty použijeme ORACLE funkci MONTHS_BETWEEN, výsledek podělíme dvanácti, odřízneme desetinnou část a funkcí ABS zajistíme, že výsledek je kladné číslo pro případ, že předáme funkci datumy v opačném pořadí.

Tuto funkci můžeme pak následně použít jako jakoukoliv jinou funkci v SQL dotazech:

SELECT age(date_of_birth, SYSDATE) vek FROM patients

Jako uloženou proceduru si můžeme definovat kód, který provádí hromadné mazání (sérii DELETE příkazů) tabulek v našem schématu.

CREATE PROCEDURE uklid () IS

BEGIN

     DELETE FROM tab1;

     DELETE FROM tab2;

     DELETE FROM tab3;

 END;

Abychom spustili uloženou proceduru z databázového klienta, musíme její volání vymezit jako PL/SQL blok pomocí BEGIN a END. Spuštění procedury uklid bude vypadat takto:

BEGIN

     uklid();

END;