Základy databázového procedurálního jazyka

Databázový procedurální jazyk vychází ze stejného konceptu a používá stejné prvky jako jakýkoliv standardní procedurální programovací jazyk. V následujícím textu se seznámíme s těmito jeho základními prvky:

• Oddělovače bloku kódu a oddělovač příkazů

• Práce s proměnnými

• Podmíněný výraz

• Programová smyčka

• Volání jiných procedur či funkcí

• Zpracování výjimek

Procedurální kód PL/SQL je vždy ohraničen klíčovými slovy BEGIN na začátku a END na konci. Pro oddělení jednotlivých příkazů se používá středník (;) včetně finálního slova END. Za klíčovým slovem BEGIN se naopak středník nevkládá. BEGIN a END vymezují tzv. blok kódu, který může obsahovat další zanořený blok opět ohraničený slovy BEGIN a END. Stejně jako v SQL funguje symbol "--" k oddělení jednořádkových komentářů, víceřádkové komentáře uzavíráme mezi "/*" a "*/". 

BEGIN

     --toto je jednořádkový komentář

/* toto je

    víceřádkový komentář */

END;

V procedurálním kódu se využívají tzv. proměnné pro uchování a přenos zpracovávaných hodnot. Proměnná je obdobou sloupce tabulky, má své jméno a datový typ, základní typy proměnných však mohou přenášet v danou chvíli jen jednu hodnotu. Datové typy jsou stejné jako v případě SQL. Proměnné musíme deklarovat ještě před úvodním slovem BEGIN, kde uvedeme název proměnné a její datový typ oddělený mezerou, jednotlivé proměnné oddělujeme středníkem:

i NUMBER (5);

str VARCHAR2 (100);

BEGIN

....

END;

Proměnné přiřadíme hodnotu pomocí operátoru přiřazení, kterým je ":=". Pomocí standardních operátorů můžeme provádět základní aritmetické operace. Proměnné můžeme využívat na místě konstant v SQL příkazech:

i NUMBER (5);

str VARCHAR2 (100);

BEGIN

     i:=1; str := 'text';

     i:= (i-14) * 9875  + 456;

     str := str || ' pripojeny text';

     DELETE FROM tab WHERE sloupec = i AND sloupec2 = str;

     INSERT INTO tab2 (sloupec, sloupec2) VALUES (i, str);

END;

Jednou ze základních technik v procedurálním programování je větvení kódu dle definované podmínky pomocí konstrukce IF-THEN-ELSE. V prostředí PL/SQL má podmínková konstrukce následující tvar:

IF podminka THEN

     prikaz1;

ELSE

     prikaz2;

END IF;

Pokud je podmínka splněna provede se příkaz1 (obecně sada příkazů mezi THEN a ELSE), pokud podmínka platná není, provede se příkaz 2 (sada příkazů mezi ELSE a END). Část ELSE je nepovinná, naopak při potřebě vyhodnotit postupně více podmínek, můžeme konstrukci rozšířit o ELSEIF (deklarační část proměnných leu a grade je vynechána):

IF leu  >= 3000 THEN

      grade  := 'I';

ELSIF leu < 3000 AND leu >= 2000 THEN

      grade := 'II';

   ELSIF leu < 2000 and leu >= 1000 THEN

      grade := 'III';

   ELSE

      grade := 'IV';

   END IF;

V konstrukci IF/ELSIF/ELSE se provede kód vždy jen jednoho ramena. Pokud je při průchodu splněna podmínka více než jednoho ramena, provede se pouze první se splněnou podmínkou. Pokud není splněna žádná podmínka, provede se část ELSE.  Konstrukci IF/ELSEIF můžeme nahradit za podmíněný výraz CASE, který známe z SQL.

Dalším prvkem procedurálního programování jsou programové smyčky, které nám umožní provádět určitou část kódu opakovaně. Smyček je v PL/SQL několik typů, my si ukážeme jednu z nejvíce využívaných, která umožňuje procházet výsledek SQL dotazu řádek po řádku. Její syntaxe je následující:

FOR vektor IN (SELECT_dotaz) LOOP

     prikaz;

END LOOP;

Tato smyčka využívá speciální proměnnou (vektor), která se při každém průchodu naplní hodnotami jednoho řádku z výsledné množiny specifikovaného SELECT dotazu. Na jednotlivé hodnoty (sloupce) se odkazujeme jako na sloupce tabulky, pouze místo názvu tabulky používáme název proměnné (vektoru):

FOR k IN (SELECT patient_id, sex, date_of_birth FROM patients) LOOP

     IF k.date_of_birth > SYSDATE THEN

                 INSERT INTO tabulka_chyb (patient_id, popis_chyby)

                 VALUES (k.patient_id, 'Chybné datum narození');

     END IF;

     IF k.sex <> 'M' AND k.sex <> 'F' THEN

                 INSERT INTO tabulka_chyb (patient_id, popis_chyby)

                 VALUES (k.patient_id, 'Chybné pohlaví');

     END IF;

END LOOP;

Uvedená smyčka projde postupně všechny řádky tabulky patients a provádí příkazy mezi klíčovými slovy LOOP a END LOOP, v našem případě zkontroluje správnost vyplnění pohlaví a datumu narození. Zjištěné chyby zapisuje do tabulky tabulka_chyb. Pokud by tabulka patients byla prázdná, a tedy zdaný SQL dotaz by nevrátil žádný řádek, celý FOR blok by se při běhu programu přeskočil. Vektor v tomto typu smyčky na rozdíl od běžných proměnných nemusíme dopředu deklarovat, za ukončením END LOOP se však již na něj nelze odkazovat. Smyčku lze předčasně ukončit použitím příkazu EXIT, který umístíme do vhodné podmínky.

PL/SQL podporuje i další typy smyček jako je nekonečná smyčka LOOP .. END LOOP, smyčka pro dopředu neurčený počet průchodů WHILE-LOOP či smyčka FOR-LOOP pro předem určený počet průchodů. Detaily lze najít v dokumentaci k databázi ORACLE.

Uvnitř PL/SQL procedury můžeme volat jiné uložené procedury. Buď naše vlastní nebo některou z procedur z bohaté knihovny databázové systému. Proceduru spustíme prostým uvedením jejího jména a případnou specifikací parametrů, které uzavřeme do kulatých závorek. Procedury a funkce mohou být seskupeny do tzv. balíku (package), v tom případě při volání procedury vkládáme před jejich název i název balíku oddělený tečkou. V následujícím příkladě spustíme proceduru PUT_LINE, která je součástí balíku DBMS_OUTPUT. Spustíme ji s textovým parametrem:

BEGIN

     DBMS_OUTPUT.PUT_LINE('Všechno špatně' );

END;

Balík procedur DBMS_OUTPUT je součástí databázového systému ORACLE. Jeho procedura PUT_LINE vypisuje na obrazovku obsah předaného parametru a slouží nejčastěji k výpisu ladících zpráv uložených procedur a funkcí. 

Během provádění procedurálního kódu může dojít k chybě, například když se pokusíme dělit nulou nebo přiřadit do proměnné hodnotu, která neodpovídá jejímu datovému typu. Těmto chybám lze částečně předcházet pomocí podmínkových konstrukcí, ale existuje i jiná varianta řešení chybových stavů. Jde o tzv. zachytávání výjimek a obsloužení chybového stavu. Pokud dojde při běhu PL/SQL k chybě, dojde k přerušení vykonávání kódu na chybovém řádku a generuje se výjimka (exception). Pokud je na konci bloku kódu, kde chyba nastala, sekce pro zpracování výjimek, přesune se vykonávání kódu sem. Každá vzniklá výjimka s sebou nese identifikaci chyby, která ji způsobila. V sekci výjimek pak lze reagovat na jednotlivé druhy chyb:

BEGIN

...

...

EXCEPTION

   WHEN ZERO_DIVIDE  THEN

      -- osetreni deleni nulou

   WHEN VALUE_ERROR THEN

      -- osetreni chyb pri konverzi mezi datovymi typy

   WHEN OTHERS THEN

      -- osetreni vsech ostatnich chyb

END;

Předefinovaných druhů výjimek je více, detaily lze nalézt v dokumentaci databázového systému. Často však vystačíme pouze s ramenem WHEN OTHERS, kdy chceme pouze zaregistrovat jakoukoliv vzniklou chybu. Reakcí na chybu může být provedení INSERT příkazu do tabulky chyb nebo nastavení výstupních parametrů tak, aby signalizovali volající proceduře vznik chyby. Pokud chyba není ošetřena v sekci výjimek, propaguje se do nadřízeného bloku kódu nebo do volající procedury, kde opět je možné její zpracování. Pokud není zachycena a ošetřena nikde, vykonávání celé procedury je ukončeno s chybovým hlášením. Pokud je výjimka zachycena, pokračuje vykovávání programu za sekcí výjimek za koncovým END, tedy vykonávání kódu se již nevrátí na původní místo, kde chyba vznikla. Jelikož však bloky kódu mohou být zanořené, můžeme elegantně navázat na chybový stav a pokračovat dále ve vykonávání procedury:

BEGIN

     BEGIN

                 INSERT INTO tab1....

                 INSERT INTO tab2.....

                 INSERT INTO tab3....

                 DBMS_OUTPUT.PUT_LINE('Všechno OK' );

     EXCEPTION

                 WHEN OTHERS THEN

                             DBMS_OUTPUT.PUT_LINE('Něco se nepovedlo, ale..' );           

     END;

     DBMS_OUTPUT.PUT_LINE('... jedeme dál' );

     ...

END;

Pokud v uvedeném příkladu dojde k chybě při provádění některého z INSERT příkazů, přeskočí vykonávání do sekce EXCEPTION a následně pokračuje program dál. Pokud k chybě nedojde, je sekce EXCEPTION přeskočena.  

 Výjimky generuje databáze při vzniku chyb, je možné ale také výjimku vyvolat cíleně přímo v kódu voláním příkazu RAISE.