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
- 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