Oracle PL / SQL trigera apmācība: nevis savienojums (piemērs)

Satura rādītājs:

Anonim

Kas ir aktivizētājs PL / SQL?

TRIGGERS ir saglabātas programmas, kuras Oracle dzinējs automātiski iedarbina, kad uz galda tiek izpildīti DML paziņojumi, piemēram, ievietošana, atjaunināšana, dzēšana vai notiek daži notikumi. Kodu, kas jāizpilda sprūda gadījumā, var definēt atbilstoši prasībai. Jūs varat izvēlēties notikumu, kurā jāiedarbina sprūda, un izpildes laiku. Sprūda mērķis ir saglabāt informācijas integritāti datu bāzē.

Šajā apmācībā jūs uzzināsiet

  • Trigeru priekšrocības
  • Oracle aktivizētāju veidi
  • Kā izveidot aktivizētāju
  • : JAUNA un: VECA klauzula
  • Trigera vietā
  • Saliktais trigeris

Trigeru priekšrocības

Tālāk ir norādītas ierosinātāju priekšrocības.

  • Dažu atvasinātu kolonnu vērtību ģenerēšana automātiski
  • Atsauces integritātes ieviešana
  • Notikumu reģistrēšana un informācijas glabāšana par piekļuvi tabulai
  • Revīzija
  • Tabulu sinhrona replikācija
  • Drošības pilnvaru uzlikšana
  • Nederīgu darījumu novēršana

Oracle aktivizētāju veidi

Aktivizētājus var klasificēt, pamatojoties uz šādiem parametriem.

  • Klasifikācija, pamatojoties uz laiku
    • PIRMS aktivizētāja: tas tiek aktivizēts, pirms ir noticis norādītais notikums.
    • PĒC aktivizētāja: tas tiek aktivizēts pēc norādītā notikuma iestāšanās.
    • Trigera vietā: īpašs tips. Jūs uzzināsiet vairāk par turpmākajām tēmām. (tikai attiecībā uz DML)
  • Klasifikācija, pamatojoties uz līmeni
    • STATEMENT līmeņa trigeris: tas tiek aktivizēts vienu reizi norādītajam notikuma paziņojumam.
    • ROW līmeņa aktivizētājs: tas tiek aktivizēts katram ierakstam, kas tika ietekmēts norādītajā notikumā. (tikai attiecībā uz DML)
  • Klasifikācija, pamatojoties uz notikumu
    • DML trigeris: tas tiek aktivizēts, kad ir norādīts DML notikums (INSERT / UPDATE / DELETE)
    • DDL trigeris: tas tiek aktivizēts, kad ir norādīts DDL notikums (CREATE / ALTER)
    • DATU BĀZES trigeris: tas tiek aktivizēts, kad ir norādīts datu bāzes notikums (LOGON / LOGOFF / STARTUP / SHUTDOWN)

Tātad katrs trigeris ir iepriekš minēto parametru kombinācija.

Kā izveidot aktivizētāju

Zemāk ir aktivatora izveides sintakse.

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

Sintakses skaidrojums:

  • Iepriekš minētā sintakse parāda dažādus izvēles paziņojumus, kas atrodas aktivizētāja izveidē.
  • PIRMS / PĒC norādīs notikuma laiku.
  • INSERT / UPDATE / LOGON / CREATE / utt. norādīs notikumu, par kuru ir jāizdara sprūda.
  • Klauzula ON norāda, uz kuru objektu iepriekšminētais notikums ir derīgs. Piemēram, tas būs tabulas nosaukums, uz kura DML notikums var notikt DML trigera gadījumā.
  • Komanda "Katrai rindai" norādīs ROW līmeņa trigeri.
  • KAD klauzulā tiks norādīts papildu nosacījums, kurā aktivizētājam ir jāiedarbojas.
  • Deklarācijas daļa, izpildes daļa, izņēmumu apstrādes daļa ir tāda pati kā pārējiem PL / SQL blokiem. Deklarācijas daļa un izņēmumu apstrādes daļa nav obligāta.

: JAUNA un: VECA klauzula

Rindas līmeņa aktivizatorā aktivizētājs tiek aktivizēts katrai saistītajai rindai. Dažreiz ir jāzina vērtība pirms un pēc DML paziņojuma.

Oracle ir nodrošinājis divas klauzulas RECORD līmeņa aktivizatorā, lai saglabātu šīs vērtības. Mēs varam izmantot šīs klauzulas, lai atsauktos uz vecajām un jaunajām vērtībām sprūda korpusā.

  • : NEW - Sprūda izpildes laikā tai ir jauna vērtība bāzes tabulas / skata kolonnām
  • : VECA - sprūda izpildes laikā tā satur veco pamatgalda / skata kolonnu vērtību

Šī klauzula jāizmanto, pamatojoties uz DML notikumu. Zemāk esošajā tabulā tiks norādīts, kura klauzula ir derīga kādam DML priekšrakstam (INSERT / UPDATE / DELETE).

IELIKT ATJAUNINĀT DZĒST
: JAUNS DERĪGS DERĪGS NEDERĪGS. Dzēšanas gadījumā nav jaunas vērtības.
: VECA NEDERĪGS. Ievietojuma reģistrā nav vecas vērtības DERĪGS DERĪGS

Trigera vietā

"INSTEAD OF trigger" ir īpašs aktivizētāja veids. To lieto tikai DML izraisītājos. To lieto, ja sarežģītajā skatā notiks kāds DML notikums.

Apsveriet piemēru, kurā skats tiek veidots no 3 bāzes tabulām. Kad šajā skatā tiks izdots kāds DML notikums, tas kļūs nederīgs, jo dati tiek ņemti no 3 dažādām tabulām. Tātad šajā vietā tiek izmantots sprūda. Sprūda INSTEAD OF tiek izmantota, lai tieši modificētu bāzes tabulas, nevis modificētu skatu attiecīgajam notikumam.

1. piemērs : Šajā piemērā mēs izveidosim sarežģītu skatu no divām bāzes tabulām.

  • Table_1 ir emp tabula un
  • Table_2 ir nodaļas tabula.

Tad mēs redzēsim, kā tiek izmantots INSTEAD OF trigeris, lai šajā sarežģītajā skatā izdotu UPDATE atrašanās vietas detalizācijas paziņojumu. Mēs arī redzēsim, kā: NEW un: OLD ir noderīgi aktivizētājos.

  • 1. darbība: izveidojiet tabulas “emp” un “dept” ar atbilstošām kolonnām
  • 2. darbība. Tabulas aizpildīšana ar parauga vērtībām
  • 3. darbība: skata izveide iepriekš izveidotajai tabulai
  • 4. darbība: skata atjaunināšana pirms aktivizētāja vietā
  • 5. solis: Sprūda vietā izveidošana
  • 6. darbība: skata atjaunināšana pēc aktivizētāja

1. solis) Tabulas “emp” un “dept” izveidošana ar atbilstošām kolonnām

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

Kods Paskaidrojums

  • Kodu rinda 1-7 : tabulas 'emp' izveide.
  • Kodu rinda 8-12 : tabulas “dept” izveide.

Rezultāts

Izveidota tabula

2. solis. Tagad, kopš esam izveidojuši tabulu, mēs aizpildīsim šo tabulu ar paraugu vērtībām un skatu izveide iepriekš minētajām tabulām. 

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

Kods Paskaidrojums

  • Kodu rinda 13-19 : datu ievietošana tabulas tabulā.
  • Kodu rinda 20-26: datu ievietošana tabulā 'emp'.

Rezultāts

PL / SQL procedūra pabeigta

3. solis) Skata izveidošana iepriekš izveidotajai tabulai. 

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;

Kods Paskaidrojums

  • Kodu rinda 27-32: skata “guru99_emp_view” izveide.
  • 33. koda rindiņa: vaicājums guru99_emp_view.

Rezultāts

Skats ir izveidots

DARBINIEKA VĀRDS DEPT_NAME VIETA
ZZZ HR ASV
YYY PĀRDOŠANA Lielbritānija
XXX FINANŠU JAPĀNA

4. solis . Skata atjaunināšana pirms aktivizētāja vietā.

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

Kods Paskaidrojums

  • Koda rinda 34-38: atjauniniet "XXX" atrašanās vietu uz "FRANCE". Tas izvirzīja izņēmumu, jo DML paziņojumi nav pieļaujami kompleksajā skatījumā.

Rezultāts

ORA-01779: nevar modificēt kolonnu, kas kartē tabulu, kas nav saglabāta ar atslēgu

ORA-06512: 2. līnijā

5. solis. Lai izvairītos no kļūdām iepriekšējā skata atjaunināšanas skata laikā, šajā solī mēs izmantosim "trigera vietā".

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

Kods Paskaidrojums

  • 39. koda rindiņa: “UPDATE” notikuma INSTEAD OF izveide skatā “guru99_emp_view” ROW līmenī. Tas satur atjaunināšanas paziņojumu, lai atjauninātu atrašanās vietu pamata tabulā “dept”.
  • 44. koda rindiņa: Atjaunināšanas paziņojumā tiek izmantotas “: NEW” un “: OLD”, lai atrastu kolonnu vērtību pirms un pēc atjaunināšanas.

Rezultāts

Aktivizētājs ir izveidots

6. darbība . Skata atjaunināšana pēc aktivizētāja vietā. Tagad kļūda nenotiks, jo "trigera vietā" apstrādās šī sarežģītā skata atjaunināšanas darbību. Kad kods būs izpildīts, darbinieka XXX atrašanās vieta tiks atjaunināta uz “Francija” no “Japāna”.

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

Koda skaidrojums:

  • Kodu rinda 49–53: “XXX” atrašanās vietas atjaunināšana uz “FRANCE”. Tas ir veiksmīgi, jo trigeris 'INSTEAD OF' ir apturējis faktisko atjaunināšanas pārskatu skatā un veicis bāzes tabulas atjaunināšanu.
  • Koda rinda 55: Atjauninātā ieraksta pārbaude.

Izeja:

PL / SQL procedūra ir veiksmīgi pabeigta

DARBINIEKA VĀRDS DEPT_NAME VIETA
ZZZ HR ASV
YYY PĀRDOŠANA Lielbritānija
XXX FINANŠU FRANCIJA

Saliktais trigeris

Saliktais trigeris ir sprūda, kas ļauj norādīt darbības katram no četriem laika punktiem vienā sprūda korpusā. Četri dažādi laika punkti, kurus tā atbalsta, ir šādi.

  • PIRMS PAZIŅOJUMA - līmenis
  • PIRMS RIVAS - līmenis
  • PĒC RINAS - līmenis
  • PĒC DEKLARĀCIJAS - līmenis

Tas nodrošina iespēju apvienot darbības dažādos laikposmos vienā un tajā pašā aktivizatorā.

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;

Sintakses skaidrojums:

  • Iepriekš minētā sintakse parāda “COMPOUND” trigera izveidi.
  • Deklaratīvā sadaļa ir izplatīta visiem izpildes blokiem sprūda ķermenī.
  • Šie 4 laika bloki var būt jebkurā secībā. Visiem šiem 4 laika blokiem nav obligāti jābūt. Mēs varam izveidot SAVIENOTU trigeri tikai nepieciešamajiem laikiem.

1. piemērs : Šajā piemērā mēs izveidosim trigeri, lai algas kolonna tiktu automātiski aizpildīta ar noklusējuma vērtību 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;

Koda skaidrojums:

  • Kodu rinda 2-10 : salikta trigera izveide. Tas ir izveidots laika noteikšanai PIRMS RINDU līmeņa, lai aizpildītu algu ar noklusējuma vērtību 5000. Tas pirms ieraksta ievietošanas tabulā mainīs algu uz noklusējuma vērtību '5000'.
  • Kodu rinda 11-14 : ievietojiet ierakstu tabulā 'emp'.
  • 16. koda rindiņa : ievietotā ieraksta pārbaude.

Izeja:

Aktivizētājs ir izveidots

PL / SQL procedūra ir veiksmīgi pabeigta.

EMP_NAME EMP_NO ALGA VADĪTĀJS DEPT_NO
CCC 1004 5000 AAA 30

Aktivizētāju iespējošana un atspējošana

Aktivizētājus var iespējot vai atspējot. Lai iespējotu vai atspējotu aktivizētāju, aktivizatoram, kas to atspējo vai iespējo, ir jāsniedz ALTER (DDL) priekšraksts.

Zemāk ir sintakse aktivizētāju iespējošanai / atspējošanai.

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

Sintakses skaidrojums:

  • Pirmajā sintaksē parādīts, kā iespējot / atspējot vienu sprūdu.
  • Otrais paziņojums parāda, kā iespējot / atspējot visus aktivizētājus konkrētā tabulā.

Kopsavilkums

Šajā nodaļā mēs uzzinājām par PL / SQL izraisītājiem un to priekšrocībām. Mēs esam iemācījušies arī dažādas klasifikācijas un apsprieduši trigera un COMPONUND trigera vietā.