Oracle PL/SQL-i käivitamise õpetus: liite asemel [näide]

Autor: Fiona Brown Fiona Brown
Hours Ajakohastatud

Mis on Trigger PL/SQL-is?

TRIGGERID on salvestatud programmid, mis käivitatakse Oracle mootor automaatselt, kui tabelis käivitatakse DML-i laused, nagu lisamine, värskendamine, kustutamine või mõned sündmused. Käivituse korral käivitatava koodi saab määratleda vastavalt nõudele. Saate valida sündmuse, mille korral päästik käivitada, ja täitmise aja. Päästiku eesmärk on säilitada andmebaasis oleva teabe terviklikkus.

Päästikute eelised

Allpool on toodud päästikute eelised.

  • Mõne tuletatud veeru väärtuse automaatne genereerimine
  • Viite terviklikkuse jõustamine
  • Sündmuste logimine ja teabe salvestamine tabelijuurdepääsu kohta
  • Auditeerimine
  • Synctabelite kroonne replikatsioon
  • Turvavolituste kehtestamine
  • Kehtetute tehingute vältimine

Käivitajate tüübid Oracle

Päästikuid saab klassifitseerida järgmiste parameetrite alusel.

  • Klassifikatsioon põhineb ajastamine
  • ENNE Päästikut: see käivitub enne määratud sündmuse toimumist.
  • AFTER Päästiku: see käivitub pärast määratud sündmuse toimumist.
  • Päästiku ASEMEL: eritüüp. Edasiste teemade kohta saate lisateavet. (ainult DML-i jaoks)
  • Klassifikatsioon põhineb tase
  • STATEMENT taseme päästik: see käivitub määratud sündmuse avalduse korral üks kord.
  • ROW taseme päästik: see käivitub iga kirje puhul, mida määratud sündmus mõjutas. (ainult DML-i jaoks)
  • Klassifikatsioon põhineb sündmus
  • DML-i päästik: see käivitub, kui DML-sündmus on määratud (INSERT/UPDATE/DELETE)
  • DDL-i päästik: see käivitub, kui DDL-sündmus on määratud (CREATE/ALTER)
  • ANDMEBAASI päästik: see käivitub, kui andmebaasi sündmus on määratud (LOGON/LOGOFF/STARTUP/SHUTDOWN)

Seega on iga päästik ülaltoodud parameetrite kombinatsioon.

Päästiku loomine

Allpool on päästiku loomise süntaks.

Loo Trigger

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;

Süntaksi selgitus:

  • Ülaltoodud süntaks näitab erinevaid valikulisi lauseid, mis on päästiku loomisel olemas.
  • ENNE/ PÄRAST määrab sündmuste ajastused.
  • SISESTAMINE/VÄRSKENDAMINE/LOGUN/LOO/jne. määrab sündmuse, mille puhul päästik tuleb käivitada.
  • ON-klausel määrab, millisel objektil ülalmainitud sündmus kehtib. Näiteks on see tabeli nimi, millel DML-i käivitaja korral võib DML-i sündmus toimuda.
  • Käsk „FOR EACH ROW” määrab ROW taseme päästiku.
  • WHEN-klausel määrab lisatingimuse, mille korral päästik peab käivituma.
  • Deklaratsiooniosa, täitmise osa, erandite käsitlemise osa on samad, mis teisel PL/SQL plokid. Deklaratsiooniosa ja erandi käsitlemise osa on valikulised.

SEOTUD ARTIKLID

:NEW ja :OLD klausel

Reataseme päästik käivitub iga seotud rea puhul. Ja mõnikord on vaja teada väärtust enne ja pärast DML-lauset.

Oracle on nende väärtuste hoidmiseks lisanud RECORD-taseme päästikusse kaks klauslit. Neid klausleid saame kasutada vanadele ja uutele väärtustele viitamiseks päästiku keha sees.

  • :NEW – see hoiab käivitamise ajal põhitabeli/vaate veergude jaoks uut väärtust
  • :OLD – see hoiab käivitamise ajal põhitabeli/vaate veergude vana väärtust

Seda klauslit tuleks kasutada DML-i sündmuse põhjal. Allolev tabel täpsustab, milline klausel millise DML-lause jaoks kehtib (INSERT/UPDATE/DELETE).

INSERT UPDATE Kustuta
:UUS kehtiv kehtiv KEHTETU. Kustutamise korral pole uut väärtust.
:VANA KEHTETU. Sisestatud tähises pole vana väärtust kehtiv kehtiv

Päästiku ASEMEL

"Päästiku asemel" on päästiku eritüüp. Seda kasutatakse ainult DML-i käivitajates. Seda kasutatakse juhul, kui kompleksvaates toimub mis tahes DML-sündmus.

Vaatleme näidet, kus vaade tehakse kolmest põhitabelist. Kui selle vaate kaudu väljastatakse mis tahes DML-sündmus, muutub see kehtetuks, kuna andmed võetakse kolmest erinevast tabelist. Nii et selle asemel kasutatakse päästikut. Päästikut INSTEAD OF kasutatakse põhitabelite otse muutmiseks antud sündmuse vaate muutmise asemel.

Näiteks 1: Selles näites loome kahest põhitabelist keeruka vaate.

  • Tabel_1 on emp tabel ja
  • Tabel_2 on osakondade tabel.

Seejärel näeme, kuidas selle keerulise vaate asukoha üksikasjade avalduse UPDATE väljastamiseks kasutatakse päästikut INSTEED OF. Samuti näeme, kuidas :NEW ja :OLD on päästikutes kasulikud.

  • 1. samm: vastavate veergudega tabelite 'emp' ja 'dept' loomine
  • 2. samm: täitke tabel näidisväärtustega
  • 3. samm: ülaltoodud tabeli vaate loomine
  • 4. toiming: värskendage vaadet enne päästiku asemel
  • 5. samm: päästiku asemel käivitaja loomine
  • 6. toiming: vaate värskendamine pärast päästiku asemel

Step 1) Tabelite 'emp' ja 'dept' loomine sobivate veergudega

Päästiku ASEMEL

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

Koodi selgitus

  • Koodirida 1-7: tabeli 'emp' loomine.
  • Koodirida 8-12: tabeli 'osakonna' loomine.

Väljund

Tabel loodud

Step 2) Nüüd, kuna oleme tabeli loonud, täidame selle tabeli ülaltoodud tabelite näidisväärtuste ja vaadete loomisega.

Päästiku ASEMEL

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

Koodi selgitus

  • Koodirida 13-19: andmete sisestamine 'osakonna' tabelisse.
  • Koodirida 20–26: Andmete sisestamine tabelisse 'emp'.

Väljund

PL/SQL protseduur valmis

Step 3) Vaate loomine ülaltoodud tabeli jaoks.

Päästiku ASEMEL

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;

Koodi selgitus

  • Koodirida 27–32: Vaate 'guru99_emp_view' loomine.
  • Koodirida 33: Päringu guru99_emp_view.

Väljund

Vaade loodud

EMPLOYEE_NAME DEPT_NAME ASUKOHT
Zzz HR USA
YYY SALES UK
XXX FINANCIAL Jaapan

Step 4) Vaate värskendamine enne päästikut.

Päästiku ASEMEL

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

Koodi selgitus

  • Koodirida 34–38: Värskendage „XXX” asukohaks „PRANTSUSMAA”. See tekitas erandi, kuna DML avaldused pole kompleksvaates lubatud.

Väljund

ORA-01779: ei saa muuta veergu, mis vastab võtmeta säilitatavale tabelile

ORA-06512: real 2

Step 5)Et vältida vigade ilmnemist eelmises etapis vaate värskendamisel, kasutame selles etapis käsku "päästiku asemel".

Päästiku ASEMEL

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

Koodi selgitus

  • Koodirida 39: ROW-tasemel vaates 'guru99_emp_view' sündmuse „UPDATE” päästiku INSTEEAD OF loomine. See sisaldab värskenduslauset asukoha värskendamiseks baastabeli 'osakonnas'.
  • Koodirida 44: Värskenduslause kasutab veergude väärtuste leidmiseks enne ja pärast värskendamist väärtusi „:NEW” ja „: OLD”.

Väljund

Päästik loodud

Step 6) Vaate värskendamine pärast päästiku asemel. Nüüd tõrget ei tule, kuna selle keeruka vaate värskendamise toiminguga tegeleb „päästiku asemel”. Ja kui kood on käivitatud, värskendatakse töötaja XXX asukohta "Jaapanist" "Prantsusmaa".

Päästiku ASEMEL

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Koodi selgitus:

  • Koodirida 49–53: „XXX” asukoha värskendamine PRANTSUSMAAKS. See on edukas, kuna päästik 'INSTEAD OF' on peatanud tegeliku värskendusavalduse kuvamisel ja viinud läbi baastabeli värskendamise.
  • Koodirida 55: Värskendatud kirje kontrollimine.

Väljund:

PL/SQL protseduur edukalt lõpule viidud

EMPLOYEE_NAME DEPT_NAME ASUKOHT
Zzz HR USA
YYY SALES UK
XXX FINANCIAL PRANTSUSMAA

Ühendpäästik

Ühendpäästik on päästik, mis võimaldab määrata toimingud iga nelja ajastuspunkti jaoks ühes päästikosas. Neli erinevat ajastuspunkti, mida see toetab, on järgmised.

  • ENNE AVALDUST – tase
  • ENNE RIDA – tase
  • PÄRAST RIDA – tase
  • PÄRAST AVALDUST – tase

See annab võimaluse kombineerida eri ajastustega seotud toiminguid samasse päästikusse.

Ühendpäästik

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;

Süntaksi selgitus:

  • Ülaltoodud süntaks näitab päästiku „COMPOUND” loomist.
  • Deklaratiivne jaotis on ühine kõigi päästiku keha täitmisplokkide jaoks.
  • Need 4 ajastusplokki võivad olla mis tahes järjestuses. Kõigi nende 4 ajastusplokki omamine ei ole kohustuslik. Saame luua COMPOUND-päästiku ainult vajalike ajastuste jaoks.

Näiteks 1: selles näites loome käivitaja, et täita palga veerg automaatselt vaikeväärtusega 5000.

Ühendpäästik

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;

Koodi selgitus:

  • Koodirida 2-10: liitpäästiku loomine. See on loodud ajastamiseks ENNE RIDA taset, et täita palk vaikeväärtusega 5000. See muudab palga enne kirje tabelisse lisamist vaikeväärtuseks '5000'.
  • Koodirida 11-14: Sisestage kirje tabelisse 'emp'.
  • Koodirida 16: sisestatud kirje kontrollimine.

Väljund:

Päästik loodud

PL/SQL protseduur edukalt lõpule viidud.

EMP_NAME EMP_NO SALARY MANAGER DEPT_NO
CCC 1004 5000 AAA 30

Päästikute lubamine ja keelamine

Päästikuid saab lubada või keelata. Päästiku lubamiseks või keelamiseks tuleb selle keelava või lubava päästiku kohta anda ALTER (DDL) avaldus.

Allpool on süntaks päästikute lubamiseks/keelamiseks.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Süntaksi selgitus:

  • Esimene süntaks näitab, kuidas üksikpäästikut lubada/keelata.
  • Teine avaldus näitab, kuidas lubada/keelata kõiki konkreetse tabeli päästikuid.

kokkuvõte

Selles peatükis oleme õppinud tundma PL/SQL-i käivitajaid ja nende eeliseid. Samuti oleme õppinud erinevaid klassifikatsioone ja arutanud päästiku ja LIIDIST päästiku ASEMEL.

Teile võib meeldida: