Oracle Výukový program spouštění PL/SQL: Místo toho, Compound [Příklad]
Co je Trigger v PL/SQL?
Spouštěče jsou uložené programy, které jsou spouštěny Oracle engine automaticky, když jsou na stole provedeny příkazy DML jako insert, update, delete nebo dojde k nějaké události. Kód, který se má provést v případě spuštění, lze definovat podle požadavku. Můžete si vybrat událost, při které má být spouštěč spuštěn, a načasování provedení. Účelem spouštěče je zachovat integritu informací v databázi.
Výhody spouštěčů
Níže jsou uvedeny výhody spouštěčů.
- Automatické generování některých odvozených hodnot sloupců
- Prosazování referenční integrity
- Protokolování událostí a ukládání informací o přístupu k tabulce
- Audit
- Synchronou replikaci tabulek
- Vynucování bezpečnostních oprávnění
- Prevence neplatných transakcí
Typy spouštěčů v Oracle
Spouštěče lze klasifikovat na základě následujících parametrů.
- Klasifikace založená na načasování
- BEFORE Trigger: Spustí se předtím, než dojde k zadané události.
- AFTER Trigger: Spustí se poté, co nastala zadaná událost.
- MÍSTO spouště: Speciální typ. O dalších tématech se dozvíte více. (pouze pro DML)
- Klasifikace založená na úroveň
- Úroveň STATEMENT Trigger: Spustí se jednou pro zadaný příkaz události.
- Spouštěč úrovně ROW: Spustí se pro každý záznam, který byl ovlivněn v zadané události. (pouze pro DML)
- Klasifikace založená na událost
- Spouštěč DML: Spustí se, když je specifikována událost DML (INSERT/UPDATE/DELETE)
- Spouštěč DDL: Spustí se, když je specifikována událost DDL (CREATE/ALTER)
- Spouštěč DATABASE: Spustí se, když je zadána událost databáze (LOGON/LOGOFF/STARTUP/SHUTDOWN)
Každý trigger je tedy kombinací výše uvedených parametrů.
Jak vytvořit spouštěč
Níže je uvedena syntaxe pro vytvoření spouštěče.
CREATE [ OR REPLACE ] TRIGGER <trigger_name> [BEFORE | AFTER | INSTEAD OF ] [INSERT | UPDATE | DELETE......] ON<name of underlying object> [FOR EACH ROW] [WHEN<condition for trigger to get execute> ] DECLARE <Declaration part> BEGIN <Execution part> EXCEPTION <Exception handling part> END;
Vysvětlení syntaxe:
- Výše uvedená syntaxe ukazuje různé volitelné příkazy, které jsou přítomny při vytváření triggeru.
- BEFORE/ AFTER určí časování události.
- INSERT/UPDATE/LOGON/CREATE/atd. určí událost, pro kterou je potřeba aktivační událost spustit.
- Klauzule ON specifikuje, na kterém objektu je výše zmíněná událost platná. Například to bude název tabulky, na které může dojít k události DML v případě spouštění DML.
- Příkaz „PRO KAŽDÝ ŘÁDEK“ určí spouštěcí úroveň ROW.
- Klauzule WHEN určí další podmínku, při které se musí spouštěč spustit.
- Část deklarace, část provádění, část zpracování výjimek je stejná jako část druhé PL/SQL bloky. Část deklarace a část zpracování výjimek jsou volitelné.
:NOVÁ a :STARÁ klauzule
U pravidla na úrovni řádku se pravidlo spouští pro každý související řádek. A někdy je nutné znát hodnotu před a za příkazem DML.
Oracle poskytla dvě klauzule ve spouštěči na úrovni RECORD pro udržení těchto hodnot. Tyto klauzule můžeme použít k odkazování na staré a nové hodnoty uvnitř těla spouště.
- :NEW – Uchovává novou hodnotu pro sloupce základní tabulky/pohledu během provádění spouštěče
- :OLD – Uchovává starou hodnotu sloupců základní tabulky/pohledu během provádění triggeru
Tato klauzule by měla být použita na základě události DML. Níže uvedená tabulka udává, která klauzule je platná pro který příkaz DML (INSERT/UPDATE/DELETE).
INSERT | UPDATE | DELETE | |
---|---|---|---|
:NOVÝ | PLATNÝ | PLATNÝ | NEPLATNÝ. V případě odstranění není žádná nová hodnota. |
:STARÝ | NEPLATNÝ. Ve vložce není žádná stará hodnota | PLATNÝ | PLATNÝ |
MÍSTO Spouště
„MÍSTO spouště“ je speciální typ spouště. Používá se pouze v DML triggerech. Používá se, když na komplexním pohledu dojde k jakékoli události DML.
Zvažte příklad, ve kterém je pohled vytvořen ze 3 základních tabulek. Když je nad tímto pohledem vydána jakákoli událost DML, stane se neplatnou, protože data jsou převzata ze 3 různých tabulek. Takže v tomto se používá spoušť MÍSTO. Spouštěč INSTEAD OF se používá k přímé úpravě základních tabulek namísto úpravy pohledu pro danou událost.
Příklad 1: V tomto příkladu vytvoříme komplexní pohled ze dvou základních tabulek.
- Tabulka_1 je emp tabulka a
- Table_2 je tabulka oddělení.
Poté se podíváme, jak se spouštěč MÍSTO OF používá k vydání příkazu UPDATE podrobností o umístění v tomto komplexním zobrazení. Také uvidíme, jak jsou :NEW a :OLD užitečné ve spouštěčích.
- Krok 1: Vytvoření tabulky 'emp' a 'dept' s příslušnými sloupci
- Krok 2: Vyplnění tabulky vzorovými hodnotami
- Krok 3: Vytvoření pohledu pro výše vytvořenou tabulku
- Krok 4: Aktualizace zobrazení před spouštěčem namísto spouštěče
- Krok 5: Vytvoření spouštěče namísto spouštěče
- Krok 6: Aktualizace zobrazení po aktivaci namísto spuštění
Krok 1) Vytvoření tabulky 'emp' a 'dept' s příslušnými sloupci
CREATE TABLE emp( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager VARCHAR2(50), dept_no NUMBER); / CREATE TABLE dept( Dept_no NUMBER, Dept_name VARCHAR2(50), LOCATION VARCHAR2(50)); /
Vysvětlení kódu
- Řádek kódu 1-7: Vytvoření tabulky 'emp'.
- Řádek kódu 8-12: Vytvoření tabulky „oddělení“.
Výstup
Tabulka vytvořena
Krok 2) Nyní, když jsme vytvořili tabulku, naplníme tuto tabulku ukázkovými hodnotami a vytvořením pohledů pro výše uvedené tabulky.
BEGIN INSERT INTO DEPT VALUES(10,‘HR’,‘USA’); INSERT INTO DEPT VALUES(20,'SALES','UK’); INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); COMMIT; END; / BEGIN INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30); INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ; INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); COMMIT; END; /
Vysvětlení kódu
- Řádek kódu 13-19: Vkládání dat do tabulky 'odd.
- Řádek 20–26 kódu: Vkládání dat do tabulky 'emp'.
Výstup
Procedura PL/SQL dokončený
Krok 3) Vytvoření pohledu pro výše vytvořenou tabulku.
CREATE VIEW guru99_emp_view( Employee_name:dept_name,location) AS SELECT emp.emp_name,dept.dept_name,dept.location FROM emp,dept WHERE emp.dept_no=dept.dept_no; /
SELECT * FROM guru99_emp_view;
Vysvětlení kódu
- Řádek 27–32 kódu: Vytvoření pohledu 'guru99_emp_view'.
- Řádek kódu 33: Dotaz na guru99_emp_view.
Výstup
Pohled vytvořen
JMÉNO ZAMĚSTNANCE | DEPT_NAME | MÍSTO |
---|---|---|
Zzz | HR | Spojené státy americké |
Yyy | PRODEJ | UK |
XXX | FINANČNÍ | JAPONSKO |
Krok 4) Aktualizace zobrazení před spouštěčem.
BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’; COMMIT; END; /
Vysvětlení kódu
- Řádek 34–38 kódu: Aktualizujte umístění „XXX“ na „FRANCE“. Vznesl výjimku, protože DML prohlášení nejsou povoleny v komplexním zobrazení.
Výstup
ORA-01779: nelze upravit sloupec, který se mapuje na tabulku bez klíčů
ORA-06512: na řádku 2
Krok 5)Abychom se vyhnuli chybě při aktualizaci zobrazení v předchozím kroku, v tomto kroku použijeme „místo spouštěče“.
CREATE TRIGGER guru99_view_modify_trg INSTEAD OF UPDATE ON guru99_emp_view FOR EACH ROW BEGIN UPDATE dept SET location=:new.location WHERE dept_name=:old.dept_name; END; /
Vysvětlení kódu
- Řádek kódu 39: Vytvoření spouštěče INSTEAD OF pro událost 'UPDATE' v pohledu 'guru99_emp_view' na úrovni ŘÁDKU. Obsahuje příkaz aktualizace pro aktualizaci umístění v základní tabulce 'odd.
- Řádek kódu 44: Příkaz Update používá ':NEW' a ': OLD' k nalezení hodnoty sloupců před a po aktualizaci.
Výstup
Spouštěč vytvořen
Krok 6) Aktualizace zobrazení po aktivaci namísto spouštěče. Nyní se chyba neobjeví, protože operaci aktualizace tohoto komplexního pohledu zvládne „místo spouštěče“. A když se kód spustí, umístění zaměstnance XXX bude aktualizováno na „Francie“ z „Japonska“.
BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; COMMIT; END; /
SELECT * FROM guru99_emp_view;
Vysvětlení kódu:
- Řádek 49–53 kódu: Aktualizace umístění „XXX“ na „FRANCE“. Je úspěšná, protože spouštěč 'INSTEAD OF' zastavil aktuální příkaz aktualizace v zobrazení a provedl aktualizaci základní tabulky.
- Řádek kódu 55: Ověření aktualizovaného záznamu.
Výstup:
Procedura PL/SQL byla úspěšně dokončena
JMÉNO ZAMĚSTNANCE | DEPT_NAME | MÍSTO |
---|---|---|
Zzz | HR | Spojené státy americké |
Yyy | PRODEJ | UK |
XXX | FINANČNÍ | FRANCIE |
Složený spouštěč
Složený spouštěč je spouštěč, který vám umožňuje určit akce pro každý ze čtyř časových bodů v jediném těle spouště. Čtyři různé časové body, které podporuje, jsou uvedeny níže.
- PŘED PROHLÁŠENÍM – úroveň
- BEFORE ROW – úroveň
- AFTER ROW – úroveň
- PO VÝKAZU – úroveň
Poskytuje možnost kombinovat akce pro různé načasování do stejného spouštěče.
CREATE [ OR REPLACE ] TRIGGER <trigger_name> FOR [INSERT | UPDATE | DELET.......] ON <name of underlying object> <Declarative part> BEFORE STATEMENT IS BEGIN <Execution part>; END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN <Execution part>; END EACH ROW; AFTER EACH ROW IS BEGIN <Execution part>; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN <Execution part>; END AFTER STATEMENT; END;
Vysvětlení syntaxe:
- Výše uvedená syntaxe ukazuje vytvoření spouštěče 'COMPOUND'.
- Deklarativní část je společná pro všechny prováděcí bloky v těle spouště.
- Tyto 4 bloky časování mohou být v libovolném pořadí. Není povinné mít všechny tyto 4 časové bloky. Můžeme vytvořit COMPOUND trigger pouze pro časování, která jsou vyžadována.
Příklad 1: V tomto příkladu vytvoříme spouštěč pro automatické vyplnění sloupce platu výchozí hodnotou 5000.
CREATE TRIGGER emp_trig FOR INSERT ON emp COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :new.salary:=5000; END BEFORE EACH ROW; END emp_trig; /
BEGIN INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); COMMIT; END; /
SELECT * FROM emp WHERE emp_no=1004;
Vysvětlení kódu:
- Řádek kódu 2-10: Vytvoření složeného spouštěče. Je vytvořen pro načasování BEFORE ROW-level pro naplnění mzdy výchozí hodnotou 5000. Tím se mzda před vložením záznamu do tabulky změní na výchozí hodnotu '5000'.
- Řádek kódu 11-14: Vložte záznam do tabulky 'emp'.
- Řádek kódu 16: Ověření vloženého záznamu.
Výstup:
Spouštěč vytvořen
Procedura PL/SQL byla úspěšně dokončena.
EMP_NAME | EMP_NO | SALARY | MANAGER | DEPT_NO |
---|---|---|---|---|
CCC | 1004 | 5000 | AAA | 30 |
Povolení a zakázání spouštěčů
Spouštěče lze povolit nebo zakázat. Chcete-li aktivovat nebo deaktivovat spouštěč, je třeba zadat příkaz ALTER (DDL) pro spouštěč, který jej deaktivuje nebo povolí.
Níže je uvedena syntaxe pro aktivaci/deaktivaci spouštěčů.
ALTER TRIGGER <trigger_name> [ENABLE|DISABLE]; ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;
Vysvětlení syntaxe:
- První syntaxe ukazuje, jak povolit/zakázat jednoduchý spouštěč.
- Druhý příkaz ukazuje, jak povolit/zakázat všechny spouštěče v konkrétní tabulce.
Shrnutí
V této kapitole jsme se dozvěděli o PL/SQL triggerech a jejich výhodách. Také jsme se naučili různé klasifikace a diskutovali jsme MÍSTO spouště a spouštěcí spouště.