Výukový program pre spúšťač Oracle PL / SQL: Namiesto, Compound (Príklad)

Obsah:

Anonim

Čo je spúšťač v PL / SQL?

TRIGGERS sú uložené programy, ktoré sú spúšťané motorom Oracle automaticky, keď sa v tabuľke vykonajú príkazy DML, ako sú vloženie, aktualizácia, odstránenie, alebo k niektorým udalostiam. Kód, ktorý sa má vylúčiť v prípade spúšťača, je možné definovať podľa požiadavky. Môžete si zvoliť udalosť, pri ktorej je potrebné spustiť spúšťač, a načasovanie vykonania. Účelom spúšťača je zachovať integritu informácií v databáze.

V tomto návode sa naučíte

  • Výhody spúšťačov
  • Typy spúšťačov v systéme Oracle
  • Ako vytvoriť spúšťač
  • : NOVINKA a: STARÁ doložka
  • NAMIESTO Spúšte
  • Zložený spúšťač

Výhody spúšťačov

Nasledujú výhody spúšťačov.

  • Automatické generovanie niektorých odvodených hodnôt stĺpcov
  • Presadzovanie referenčnej integrity
  • Protokol udalostí a ukladanie informácií o prístupe k stolu
  • Auditovanie
  • Synchrónna replikácia tabuliek
  • Ukladanie bezpečnostných povolení
  • Predchádzanie neplatným transakciám

Typy spúšťačov v systéme Oracle

Spúšťače je možné klasifikovať na základe nasledujúcich parametrov.

  • Klasifikácia založená na načasovaní
    • PRED Spúšťačom: Spustí sa skôr, ako dôjde k zadanej udalosti.
    • PO SPUŠTE: Spustí sa po zadaní udalosti.
    • NAMIESTO Spúšte: Špeciálny typ. Dozviete sa viac o ďalších témach. (iba pre DML)
  • Klasifikácia založená na úrovni
    • Spúšťač úrovne STATEMENT: Spustí sa jednorazovo pre zadaný výpis udalosti.
    • Spúšťač úrovne ROW: Spustí sa pre každý záznam, ktorý bol ovplyvnený v zadanej udalosti. (iba pre DML)
  • Klasifikácia na základe udalosti
    • Spúšťač DML: Spustí sa, keď je zadaná udalosť DML (INSERT / UPDATE / DELETE)
    • Spúšťač DDL: Spustí sa, keď je zadaná udalosť DDL (CREATE / ALTER)
    • Spúšťač DATABÁZY: Spustí sa, keď je zadaná databázová udalosť (LOGON / LOGOFF / STARTUP / SHUTDOWN).

Každý spúšťač je teda kombináciou vyššie uvedených parametrov.

Ako vytvoriť spúšťač

Nižšie je uvedená syntax pre vytvorenie spúšťača.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Vysvetlenie syntaxe:

  • Vyššie uvedená syntax zobrazuje rôzne voliteľné príkazy, ktoré sú prítomné pri vytváraní spúšťača.
  • BEFORE / AFTER určí časovanie udalostí.
  • INSERT / UPDATE / LOGON / CREATE / atď. určí udalosť, pre ktorú je potrebné spustiť spúšťač.
  • Klauzula ON určí, na ktorý objekt je vyššie uvedená udalosť platná. Bude to napríklad názov tabuľky, na ktorej môže nastať DML udalosť v prípade DML Trigger.
  • Príkaz „FOR EACH ROW“ určí spúšťač úrovne ROW.
  • Klauzula KEDY určí ďalšiu podmienku, v ktorej musí spúšťač vystreliť.
  • Deklaračná časť, exekučná časť, časť na spracovanie výnimiek je rovnaká ako časť ostatných blokov PL / SQL. Časť vyhlásenia a časť zaoberajúca sa výnimkami sú voliteľné.

: NOVINKA a: STARÁ doložka

V spúšťači na úrovni riadkov sa spúšťač spúšťa pre každý súvisiaci riadok. A niekedy je potrebné poznať hodnotu pred a po vyhlásení DML.

Spoločnosť Oracle poskytla dve spúšťače na úrovni RECORD na uchovanie týchto hodnôt. Tieto klauzuly môžeme použiť na označenie starých a nových hodnôt v tele spúšťača.

  • : NOVINKA - Počas vykonávania spúšťača obsahuje novú hodnotu pre stĺpce základnej tabuľky / zobrazenia
  • : STARÝ - Počas vykonávania spúšťača obsahuje starú hodnotu stĺpcov základnej tabuľky / zobrazenia

Táto doložka by sa mala použiť na základe udalosti DML. V nasledujúcej tabuľke sa uvedie, ktorá klauzula je platná pre ktorý príkaz DML (INSERT / UPDATE / DELETE).

VLOŽTE AKTUALIZÁCIA ODSTRÁNIŤ
:NOVÝ PLATNÉ PLATNÉ NEPLATNÉ. V prípade odstránenia nie je žiadna nová hodnota.
: STARÉ NEPLATNÉ. V prípade vloženia nie je žiadna stará hodnota PLATNÉ PLATNÉ

NAMIESTO Spúšte

„INSTEAD OF trigger“ je špeciálny typ spúšťača. Používa sa iba v spúšťačoch DML. Používa sa, keď v komplexnom zobrazení dôjde k akejkoľvek udalosti DML.

Zvážte príklad, v ktorom je pohľad vytvorený z 3 základných tabuliek. Keď sa v tomto zobrazení zobrazí akákoľvek udalosť DML, stane sa neplatná, pretože údaje sú prevzaté z 3 rôznych tabuliek. Takže v tomto INSTEAD OF sa používa spúšť. Spúšťač INSTEAD OF sa používa na priamu úpravu základných tabuliek namiesto úpravy pohľadu pre danú udalosť.

Príklad 1 : V tomto príklade vytvoríme komplexný pohľad z dvoch základných tabuliek.

  • Table_1 je emp table a
  • Tabuľka_2 je tabuľka oddelenia.

Potom uvidíme, ako sa spúšťač INSTEAD OF použije na vydanie UPDATE príkazu detailu umiestnenia v tomto komplexnom zobrazení. Ďalej sa pozrieme na to, ako: NEW a: OLD je užitočné pri spúšťačoch.

  • Krok 1: Vytvorenie tabuľky „emp“ a „odd“ s príslušnými stĺpcami
  • Krok 2: Vyplnenie tabuľky vzorovými hodnotami
  • Krok 3: Vytvorenie zobrazenia pre vyššie vytvorenú tabuľku
  • Krok 4: Aktualizácia zobrazenia pred spúšťačom namiesto spúšťača
  • Krok 5: Vytvorenie spúšťača namiesto
  • Krok 6: Aktualizácia zobrazenia po namiesto spúšťača

Krok 1) Vytvorenie tabuľky „emp“ a „odd“ s príslušnými stĺpcami

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));/

Vysvetlenie kódu

  • Riadok kódu 1-7 : Vytvorenie tabuľky „emp“.
  • Riadok kódu 8-12 : Vytvorenie „oddelenia“ tabuľky.

Výkon

Tabuľka bola vytvorená

Krok 2) Teraz, keď sme vytvorili tabuľku, vyplníme túto tabuľku vzorovými hodnotami a vytvorením zobrazení pre vyššie uvedené tabuľky. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT 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;/

Vysvetlenie kódu

  • Riadok kódu 13-19 : Vkladanie údajov do tabuľky „odd“.
  • Riadok kódu 20-26: Vkladanie údajov do tabuľky „emp“.

Výkon

Procedúra PL / SQL je dokončená

Krok 3) Vytvorenie zobrazenia pre vyššie vytvorenú tabuľku. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

Vysvetlenie kódu

  • Riadok kódu 27-32: Vytvorenie zobrazenia „guru99_emp_view“.
  • Riadok kódu 33: Dopytovanie guru99_emp_view.

Výkon

Pohľad bol vytvorený

MENO ZAMESTNANCA DEPT_NAME POLOHA
ZZZ HR USA
RRRR PREDAJ UK
XXX FINANČNÉ JAPONSKO

Krok 4) Namiesto spúšťača aktualizácia zobrazenia pred.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/

Vysvetlenie kódu

  • Riadok kódu 34-38: Aktualizujte umiestnenie „XXX“ na „FRANCÚZSKO“. Zvýšila výnimku, pretože príkazy DML nie sú v komplexnom zobrazení povolené.

Výkon

ORA-01779: nie je možné upraviť stĺpec, ktorý sa mapuje na tabuľku bez ochrany kľúčov

ORA-06512: na linke 2

Krok 5) Aby sme sa vyhli chybám pri aktualizácii zobrazenia v predchádzajúcom kroku, v tomto kroku použijeme „namiesto spúšťača“.

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Vysvetlenie kódu

  • Riadok kódu 39: Vytvorenie INSTEAD OF spúšťača udalosti „UPDATE“ v zobrazení „guru99_emp_view“ na úrovni ROW. Obsahuje vyhlásenie o aktualizácii na aktualizáciu umiestnenia v základnej tabuľke „odd“.
  • Riadok kódu 44: Vyhlásenie o aktualizácii používa na vyhľadanie hodnoty stĺpcov pred a po aktualizácii znaky „: NEW“ a „: OLD“.

Výkon

Spúšťač bol vytvorený

Krok 6) Aktualizácia pohľadu po namiesto spúšťača. Teraz chyba nepríde, pretože operácia aktualizácie tohto komplexného zobrazenia bude spracovaná pomocou príkazu „namiesto spúšťača“. Po vykonaní kódu bude poloha zamestnanca XXX aktualizovaná na „Francúzsko“ z „Japonska“.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Vysvetlenie kódu:

  • Riadok kódu 49-53: Aktualizácia umiestnenia „XXX“ na „FRANCÚZSKO“. Je úspešný, pretože spúšťač „INSTEAD OF“ zastavil aktuálny príkaz aktualizácie v zobrazení a vykonal aktualizáciu základnej tabuľky.
  • Riadok kódu 55: Overenie aktualizovaného záznamu.

Výkon:

Procedúra PL / SQL bola úspešne dokončená

MENO ZAMESTNANCA DEPT_NAME POLOHA
ZZZ HR USA
RRRR PREDAJ UK
XXX FINANČNÉ FRANCÚZSKO

Zložený spúšťač

Zložený spúšťač je spúšťač, ktorý umožňuje určiť akcie pre každý zo štyroch časových bodov v tele jediného spúšťača. Štyri rôzne časové body, ktoré podporuje, sú uvedené nižšie.

  • PRED VYHLÁSENÍM - úroveň
  • PRED RIADKOM - úroveň
  • PO RIADKU - úroveň
  • PO VYHLÁSENÍ - úroveň

Poskytuje možnosť kombinovať akcie pre rôzne načasovanie do rovnakého spúšťača.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Vysvetlenie syntaxe:

  • Vyššie uvedená syntax ukazuje vytvorenie spúšťača „COMPOUND“.
  • Deklaratívna časť je spoločná pre všetky bloky vykonávania v tele spúšťača.
  • Tieto 4 časové bloky môžu byť v ľubovoľnom poradí. Nie je povinné mať všetky tieto 4 časovacie bloky. Spúšťač ZLOŽKY môžeme vytvoriť iba pre požadované časovania.

Príklad 1 : V tomto príklade vytvoríme spúšťač na automatické vyplnenie stĺpca mzdy predvolenou hodnotou 5000.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;

Vysvetlenie kódu:

  • Riadok kódu 2-10 : Vytvorenie zloženého spúšťača. Je vytvorený na načasovanie pred úrovňou ROW na vyplnenie platu s predvolenou hodnotou 5 000. Pred vložením záznamu do tabuľky sa plat zmení na predvolenú hodnotu 5 000.
  • Riadok kódu 11-14 : Vložte záznam do tabuľky „emp“.
  • Riadok kódu 16 : Overenie vloženého záznamu.

Výkon:

Spúšťač bol vytvorený

Procedúra PL / SQL bola úspešne dokončená.

EMP_NAME EMP_NO PLATBA MANAŽÉR DEPT_NO
CCC 1004 5 000 AAA 30

Povolenie a zakázanie spúšťačov

Spúšťače je možné povoliť alebo zakázať. Ak chcete povoliť alebo zakázať spúšťač, je potrebné uviesť príkaz ALTER (DDL) pre spúšťač, ktorý ho deaktivuje alebo povolí.

Ďalej je uvedená syntax pre povolenie / zakázanie spúšťačov.

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Vysvetlenie syntaxe:

  • Prvá syntax ukazuje, ako povoliť / zakázať jeden spúšťač.
  • Druhé vyhlásenie ukazuje, ako povoliť / zakázať všetky spúšťače v konkrétnej tabuľke.

Zhrnutie

V tejto kapitole sme sa dozvedeli o spúšťačoch PL / SQL a ich výhodách. Taktiež sme sa naučili rôzne klasifikácie a diskutovali NAMIESTO spúšťača a zloženého spúšťača.