Vytvoření rutiny (CREATE PROCEDURE | FUNCTION)

Syntaxe popisu rutiny (SQL)

popis_rutiny ::= { popis_procedury | popis_funkce };
popis_procedury ::= PROCEDURE [schéma.]jméno ([ formální_parametr {, formální_parametr }… ]) ; tělo
popis_funkce ::= FUNCTION jméno ([ formální_parametr {, formální_parametr }… ]) RETURNS typ; tělo
formální_parametr ::= [ mód_parametru ] [ jméno ] typ [ DEFAULT výraz ]
mód_parametru  ::= IN | OUT | INOUT
tělo ::= příkaz | EXTERNAL NAME  označení_externí_rutiny

Každá rutina má svoje jméno, pomocí něhož se volá. Dále rutina obsahuje výčet formálních parametrů, jimž se při volání rutiny přiřadí skutečné parametry. Po zavolání rutiny se provede její tělo -jednoduchý nebo složený příkaz.

Je-li v těle rutiny v rámci složeného příkazu zavolán příkaz LEAVE, je možné jako návěští použít jméno rutiny.

Mód parametru určuje způsob předávání hodnoty parametru na začátku a konci provádění rutiny. Pokud tělo neobsahuje specifikaci EXTERNAL, pak platí tato pravidla: Při zahájení provádění rutiny se hodnoty skutečných parametrů s módy IN (vstupní parametr) a INOUT (vstupně-výstupní parametry) zkopírují do formálních parametrů. Po skončení provádění rutiny se hodnoty formálních parametrů s módy OUT (výstupní parametry) a INOUT zkopírují (zpět) do skutečných parametrů. Pokud mód není uveden, pak se v případě funkce předpokládá IN, v případě procedury se odvodí ze zacházení s formálním parametrem v těle procedury takto: parametry, jejichž hodnota se čte, mají mód IN, parametry, do nichž se zapisuje, mód OUT, a parametry, jejichž hodnota se čte i přepisuje, mód INOUT.

Pokud při volání rutiny není některý parametr uveden, pak příslušný formální parametr bude mít hodnotu výrazu uvedeného za DEFAULT. Není-li takový výraz uveden, bude hodnota parametru NULL. Je-li mód takového parametru OUT nebo INOUT, nezapíše se při ukončení rutiny hodnota formálního parametru nikam.

Jméno parametru lze ve specifikaci formálního parametru vynechat. Na takový parametr se však nelze v těle odvolávat. Vynechání jména má proto smysl pouze u externích rutin.

Rutina, v jejímž těle je specifikováno EXTERNAL, je implementována v externí knihovně DLL (resp. so na Linuxu). Označení externí rutiny je řetězec znaků ve tvaru ‘jméno_funkce@jméno_knihovny’. Ve jméně knihovny lze uvést cestu - pokud cesta uvedena není, hledá se podle pravidel operačního systému (proměnná PATH). Pokud u jména knihovny chybí přípona, doplní se ve Windows .DLL a na Linuxu .so. Díky tomu je možné psát jeden kód pro obě prostředí - stejná externí funkce se najde v obou prostředích.

Pokud knihovna není nalezena nebo pokud v knihovně není nalezena funkce uvedeného jména, nastane chybový sqlstate 38001 (SQ_EXT_ROUT_NOT_AVAIL) resp. W0224 (LIBRARY_NOT_FOUND). Pokud není knihovna v adresáři, ze kterého je povoleno spouštět knihovny, nastane chyba W0223 (LIBRARY_ACCESS_DISABLED).

Pro předávání parametru do externích rutin platí přísná pravidla definující vztah mezi formálními parametry uvedenými v deklaraci a parametry volaných rutin vytvořených v externím jazyce. Viz Volání externích rutin a předávání parametrů.

Rozšíření jazyka SQL proti normě

Ve 602SQL funkce smějí mít parametry s módem IN, OUT nebo INOUT, zatímco SQL 3 povoluje pouze mód IN.

Příklad použití: (z aplikace Knihovna)

PROCEDURE CancelRes(IN lim INT);
BEGIN

  DECLARE PROCEDURE CheckResBook(code CHAR(8));  // pomocná lokální procedura
  BEGIN
    DECLARE pocet INT;
    SELECT COUNT(co) INTO pocet               // kolikrát je daná kniha rezervována?
    FROM Rezervace
    WHERE Rezervace.co = code;

    IF pocet = 1 THEN                        // pouze jednou, pak odstraň označení
      UPDATE Knihy
      SET Knihy.rezervovana = FALSE
      WHERE Knihy.kod = code;
    END IF;
  END;

  FOR radek AS pomc SENSITIVE CURSOR FOR        // pro všechny rezervace po limitu proveď
    SELECT * 
    FROM Rezervace
    WHERE CURRENT_DATE - Rezervace.dat_rez > lim
  DO BEGIN
      CALL CheckResBook(radek.co);      // změň příznak Rezervovano v tabulce Knihy
      DELETE WHERE CURRENT OF pomc;     // smaž záznam v tab. Rezervace
    END;
  END FOR;
END

Viz

Rutiny uložené na serveru

Vytvoření rutiny (CREATE PROCEDURE | FUNCTION)