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.

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

MÍSTO Spouště

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.

MÍSTO Spouště

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.

MÍSTO Spouště

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.

MÍSTO Spouště

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

MÍSTO Spouště

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

MÍSTO Spouště

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.

Složený spouštěč

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.

Složený spouštěč

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