Procedurální rozšíření SQL

PL/SQL je procedurálně rozšířené SQL. Kromě základních příkazu pro vytváření a modifikací dat obsahuje PL/SQL triggery, funkce, procedury, kurzory. To umožňuje přenést část aplikační logiky přímo do databází. 

Procedura

Syntaxe procedury

CREATE PROCEDURE jmeno_procedury (parametry)
   IS|AS
     definice lokalních proměnných
   BEGIN
     tělo  procedury
END;

Program v databázi jehož příkazy jsou uložen v databazi. 

Anonymní procedury - nepojmenované procedury které nejde volat. Mohou být uloženy v souboru nebo spuštěny přímo z konzole. Jsou pomalejší než pojmenované procedury, protože nemohou být předkompilovány.

Pojmenované procedury - obsahují hlavičku se jménem a parametry. Díky tomu se dají volat z jiných procedur či triggrů nebo spuštěny příkazem EXECUTE. Jelikož jsou kompilovány jen jendou, jsou rychlejší než anonymní.

Trigger

Trigger evidující mazání v tabulce Uzivatel

V případě že dojde ke smazání záznamu v tabulce Uživatel, do tabulky SmazUzivatel SE uloží login a email smazaného uživatele a údaje o mazateli.

CREATE TRIGGER smaz
BEFORE DELETE ON Uzivatel
FOR EACH ROW
BEGIN
  INSERT INTO SmazUzivate(login,email,rusitel)
     VALUES(:OLD.login,:OLD.email,USER);
END;

Trigger je v podstatě procedura spojená s tabulkou. Přesněji s operací nad tabukou, protože se spouští ve chvíli kdy je na tabulkou zavolaný příkaz INSERT, UPDATE, nebo DELETE (může se ještě specifikovat podmínkou WHERE).

BEFORE, AFTER - jsou příkazy triggeru definující zda se má trigger spustit před nebo po provedení aktivačního příkazu

FOR EACH ROW - tělo triggeru se provede pro každý řádek tabulky které se týká

OLD, NEW - označuje staré či nové hodnoty.

Funkce pro získání emailu uživatele

CREATE FUNCTION EmaiUzivatele( ulogin IN Uzivatel.login%TYPE)
   RETURN Uzivatel.email%TYPE AS v_email Uzivatel.email%TYPE;
   BEGIN
      SELECT email INTO v_email FROM Uzivatel
         WHERE login = ulogin;
      RETURN v_email;
END EmaiUzivatele;

Funkce

Na rozdíl od procedury vrací hodnotu. Kromě standardních funkcí (TO.CHAR, TO.DATE, SUBSTR, apod.) si můžeme definovat vlastní funkce.

Kurzor

Kurzor je ukazatel na řádek víceřádkového výběru. Je třeba jej v programu deklarovat pokud budeme zpracovávat víceřádkové výběry. Kurzorem mohu pohybovat a tak se dostanu na další řádky výběru. Jsou dva typy kurzoru:

Příklad explicitního kurzoru

CURSOR muj_kurzor IS Select * FROM Uzivatel;
Použití kurzoru - kurzor který prochází všechny e-maily

DECLARE
  CURSOR muj_kurzor IS Select * FROM Uzivatel;
     zaznam Uzivatel%ROWTYPE;
     radek INTEGER := 0;
BEGIN
   OPEN muj_kurzor;
   LOOP
       FETCH muj_kurzor INTO zaznam;
       EXIT WHEN muj_kurzor%NOTFOUND;
       radek := muj_kurzor%ROWCOUNT;
       DBMS_OUTPUT.PUT_LINE(radek || zaznam.email);
  END LOOP;
  CLOSE muj_kurzor;
END;
  • inplicitní - vytváří se automaticky po provedení příkazu INSERT, UPDATE, DELETE
  • explicitní - ručně vytvořený kurzor. Vytváří se nejčastěji ve spojením s příkazem selekt
Příkazy pro práci s kurzorem:
  • OPEN kurzor - otevře kurzor, tedy nastaví ho na první řádek
  • FETCH kurzor INTO promena - příkaz pro pohyb kurzoru. Načte aktuální záznam do proměnné a posune se na další záznam.
  • CLOSE kurzor - uzavře kurzor.

Statické a dynamické PL/SQL

  • Statické - klasické procedury, které mají vázané proměnné 
  • Dynamické - kód SQL příkazu je vytvářen dynamicky za běhu - vytvoření textového řetězce a jeho spuštění příkazem EXECUTE IMMEDIATE 
Kam dál?