PL/SQL alapok

Doksi: PL/SQL User’s Guide

1. fejezetet (Overview) jó áttekintést ad mindenről

Lexikális elemek: delimeterek, azonosítók (köztük a foglalt szavak), literálok, commentek

Delimiterek: egyszerű és összetett szimbólumok pl. “+” illetve “>=” ... stb.

Azonosítók: betűvel kezdődik, kötőjel, slash, szóköz tilos, de lehet úgynevezett idézőjeles azonosító pl. ”A + B ” ilyenkor a kisbetű/nagybetű nem ugyanaz.

Literálok: numerikus, karakter, karakterlánc, dátum, timestamp, logikai (TRUE és FALSE lehet)

Megjegyzések: Lehet egysoros (-- után) és több soros /* ... */ között

Adattípusok: BINARY_INTEGER fontos lesz a PL/SQL tábláknál

(vannak altípusai is pl. NATURAL)

NUMBER (vannak altípusai pl. REAL)

CHAR, RAW, VARCHAR2, ... és altípusaik

DATE

BOOLEAN

Altípusok: ugyanazok a műveletek alkalmazhatók rájuk mint az alaptípusra,

az értelmezési tartományuk kisebb.

SUBTYPE altípus_neve IS alaptípus;

nem adható meg megszorítás az alaptípusra pl.

SUBTYPE v1 IS VARCHAR2(10) rossz

Adatkonverzió: van explicit és implicit konverzió

Deklarációk: PL/SQL blokk, alprogram vagy package deklaratív részében adhatók meg.

Fontos (!) beágyazott blokknak is lehet deklaratív része. Példa az alábbi:

<<cimke1>>

DECLARE

a NUMBER;

BEGIN

a:=2;

<<cimke2>>

DECLARE

a number;

BEGIN

a:=4;

dbms_output.put_line(cimke1.a);

dbms_output.put_line(cimke2.a);

dbms_output.put_line(a);

END;

dbms_output.put_line(a);

END;

Futtatás előtt SET SERVEROUTPUT ON [SIZE n]

Ennek hatására íródik ki képernyőre a dbms_output.put_line() kimenete.

Értékadás a deklarációval egyidejűleg v BOOLEAN := FALSE;

vagy ezzel egyenértékű v BOOLEAN DEFAULT FALSE;

NOT NULL is megadható a deklarációval egyidejűleg, de ilyenkor kötelező a kezdeti értékadás.

v NUMBER NOT NULL := 2;

Konstans deklaráció v CONSTANT NUMBER := 2; ilyenkor is kötelező az értékadás

Érvényesség és láthatóság: A szokásos, erre jó példa a fenti programrészlet.

Nevek feloldása: Lokális változók elsőbbséget élveznek a táblanevekkel szemben, de az oszlopnevek elsőbbséget élveznek a lokális változókkal szemben.

Pl. UPDATE emp SET ... -- rossz ha van emp nevű változó is.

Megoldás a minősítés. Minősíteni lehet cimkével vagy alprogram nevével.

%TYPE: Két legfőbb előnye: 1. Nem kell ismernem az objektum típusát

2. Ha később változik a típus a kód maradhat

Példa:

v tablanev.oszlopnev%TYPE;

%ROWTYPE: A deklarációban nem szerepelhet inicializáció, de rekordok közötti értékadás megengedett pl. rek1 := rek2;

Példa:

rec tablanev%ROWTYPE;

rec2 kurzornev%ROWTYPE;

Értékadás: két formája van ”:=” és SELECT (FETCH) INTO az utóbbival nem lehet logikai változónak értéket adni.

Példa:

v := ’Blabla’;

SELECT oszlop1, oszlop2 INTO v1, v2 FROM … ;

SELECT oszlop1, oszlop2 INTO rec FROM …;

FETCH kurzornev INTO v1, v2;

FETCH kurzornev INTO rec;

Kifejezések: Műveletek precedenciája (csökkenő sorrendben):

(**, NOT) hatványozás, logikai tagadás

(+, - ) azonosság, negatív

(*, /) szorzás, osztás

(+, -, ||) összeadás, kivonás, konkatenáció

(=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN)

(AND) és

(OR) vagy

használjunk zárójeleket

Beépített függvények: Nem keverendők össze az SQL függvényekkel. A beépített függvények elsősorban procedurális utasításokban használhatók és nem SQL utasításokban. Azért a legtöbb használható SQL utasításban is, de van ami nem pl. SQLCODE, SQLERRM. Ami viszont nem beépített függvény az nem használható csak SQL utasításban pl. az aggregátor fv-ek.

SELECT MAX(o) INTO valtozo FROM tábla; -- Ez így rendben

valtozo := MAX(o); -- Ez így hibás

SELECT SYSDATE INTO valtozo FROM dual; -- Ez így rendben

valtozo := sysdate; -- Ez is rendben

Rekordok: (Lehet a %ROWTYPE segítségével és lehet saját rekordtípussal)

Rekord típus definiálása:

TYPE rekord_típus_neve IS RECORD (mező1, mező2 ...)

ahol a mezők megadása a következő:

mezőnév adattípus [[NOT NULL] { :=| DEFAULT} kifejezés ]

megadhatunk beágyazot rekordokat is

Rekord deklarálása: rekord_név rekord_típus_neve;

Hivatkozás rekord mezőire: rekor_név.mező

beágyazott rekord esetén: rekord_név.mező.mező2

fv által visszaadott rekord esetén: fv(paraméter).mező

(!!!) paraméter nélküli fv esetén nem használható a fenti jelölés pl. fv().mező -- rossz

Példa:

DECLARE

TYPE rektip IS RECORD(m1 INTEGER, m2 VARCHAR2(10));

rec rektip;

BEGIN

rec.m1 := 1; rec.m2 := 'Bubu';

DBMS_OUTPUT. PUT_LINE(rec.m2);

END;

PL/SQL táblák: Lehetnek fv-ek paraméterei is vagy a fv által visszaadott érték is lehet tábla típusú.

Tábla típus definiálása:

TYPE tábla_típus_neve IS TABLE OF adattípus INDEX BY BINARY_INTEGER;

az adattípus lehet %TYPE vagy %ROWTYPE-al megadva, lehet előre definiált típus vagy általunk definiált rekordtípus.

Tábla deklarálása: tábla_név tábla_típus_neve;

Hivatkozás a tábla elemeire: tábla_név(index)

Az index lehet kifejezés is

rekordokból álló tábla esetén: tábla_név(index).mező

fv által visszaadott tábla esetén: fv(paraméter)(index)

ha a tábla rekordokból áll: fv(paraméter)(index).mező

(!!!) paraméter nélküli fv esetén nem használható a fenti jelölés pl. fv()(index) -- rossz

Amíg egy táblaelemnek nem adtunk értéket addig az nem létezik. Ha hivatkozunk rá akkor a NO_DATA_FOUND kivételt generálja a rendszer.

PL/SQL tábla attribútumai: EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, DELETE

hivatkozás rá: tábla_név.attribútum

táblanév.EXISTS(n) létezik-e az n-edik elem

tábla_név.COUNT hány eleme van a táblának

FIRST és LAST a legkisebb és legnagyobb index (NULL ha a tábla üres)

PRIOR(n) NEXT(n) az n index előtti index (NULL ha nincs előtte már)

Az indexeknek nem kell egymás utáni számoknak lenniük (!)

táblanév.DELETE(n) az n-edik elemet törli (felszabadítja az erőforrást is)

táblanév.DELETE(m, n) m-től n-ig törli

táblanév.DELETE az egész táblát törli

Példa: -> lásd lejjebb a kurzoroknál

Elágazás

END IF; külön írni, utána pontosvessző

ELSIF egybeírni és hiányzik belőle egy “E” betű (nem ELSEIF)

IF ... THEN

utasítás1

ELSIF ... THEN

utasítás2

ELSIF ... THEN

utasítás3

END IF;

Ciklus

1. forma LOOP utasítások END LOOP; (végtelen ciklus kilépés EXIT-tel.)

EXIT-tel kilépni csak ciklusból lehet, PL/SQL blokkból nem (-> RETURN)

EXIT másik formája az EXIT WHEN feltétel

Ciklusoknak címke adható, hasonlóan mint a PL/SQL blokkoknak

<<címke1>>

LOOP

utasítások

LOOP

EXIT cimke1

END LOOP;

END LOOP;

A fenti módon címke segítségével beágyazott ciklusok mélyéről is kiléphetük.

2. forma WHILE felt LOOP utasítások END LOOP;

3. forma FOR számláló IN alsó..felső LOOP utasítások END LOOP;

Az alsó és felső határ lehet literál, változó vagy kifejezés, de kiértékelés után egésznek kell lennie. A lépésköz csak 1 lehet. Az utóbbi két ciklusnak is adható címke és EXIT-tel ki is lehet lépni belőlük.

CASE

CASE

WHEN felt THEN

utasítások

WHEN felt THEN

utasítások

ELSE

utasítások

END CASE;

GOTO címke utáni utasításra ugrik (címke csak végrehajtható utasítás előtt lehet, így nem lehet pl. az END LOOP előtt, de ilyenkor segíthet a NULL utasítás.)

IF-be, ciklusba és blokkba nem lehet belépni vele

kivételkeelőből nem lehet az aktuális blokkba lépni

A blokkból a külső blokkba lehet lépni

Kurzorok Több formája lehet

1. -> deklaráció (CURSOR c_név IS ...), OPEN, FETCH, CLOSE

2. deklaráció, FOR c_rec IN c_név LOOP ... END LOOP;

3. FOR c_rec IN (SELECT ... ) LOOP ... END LOOP;

Az első két formánál paraméter adható meg a kurzornév után c_név(param) formában. Az első forma esetén a paraméter az OPEN-nél adható meg.

A két utóbbi formánál a ciklusváltozó mindenképpen rekord típusú még akkor is ha a lekérdezésnek egyetlen oszlopa van csak. Így a hivatkozás rá ez esetben is c_rec.oszlop.

Kurzor attribútumok: %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

Ugyanezek az attribútumok implicit kurzor attribútumként is használhatók, ekkor a legutóbbi SQL utasításra vonatkozóan adnak információt. Formájuk: SQL%attribútum.

Példa: (cursor_tomb.sql)

DECLARE

v_nev VARCHAR2(20);

CURSOR emp_cur IS

SELECT deptno, ename FROM emp;

rec emp_cur%ROWTYPE;

TYPE tab_tip IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;

tabla tab_tip;

i NUMBER(4);

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur INTO rec;

EXIT WHEN emp_cur%NOTFOUND;

i:= emp_cur%ROWCOUNT;

tabla(i) := rec;

dbms_output.put_line(to_char(tabla(i).deptno)||' - '||tabla(i).ename);

END LOOP;

CLOSE emp_cur;

END;

Módosítás kurzor sorain végighaladva

deklaráció -> (CURSOR c_név IS ... FOR UPDATE)

UPDATE tábla ... WHERE CURRENT OF c_név;

Kurzor típusú változók (a változó egy kurzorra mutat, az OPEN utasításkor fogjuk megadni a lekérdezést) Lehet pl. alprogram paramétere is.

Kurzor típus definiálása

TYPE cursor_tipus IS REF CURSOR [RETURN rekordtípus]

A rekordtípus megadása történhet %TYPE, %ROWTYPE, vagy saját rekordtípussal. Van egy generikus rekordtípus is, amikor nem adjuk meg a RETURN részt.

Változó deklarálása

c_változó cursor_tipus

Kurzor megnyitása, olvasása, lezárása

OPEN c_változó FOR SELECT ...

FETCH c_változó INTO ...

CLOSE c_változó

Ha a változót paraméterül adjuk át egy alprogramnak, amelyik megnyitja vagy lezárja a kurzort, akkor a paraméter IN OUT kell hogy legyen. Ha a paraméterül kapott kurzor változó nem megfelelő típusú akkor a ROWTYPE_MISMATCH hibát generálja a rendszer.

Package-ben nem deklarálhatunk REF CURSOR típusú változót, mert ezek a változók nem maradnak életben a session egész időtartama alatt, ellentétben a többi típusú változóval.

Hiba és kivételkezelés

Ha valami olyan dolog történik futás közben, ami megsérti az Oracle szabályait akkor a rendszer egy hibakódot és egy hibaeseményt generál. Ezeket kezelhetjük le a hibakezelő részben, ami a blokk végén szerepelhet.

Nélkülük minden utasítás után ellenőrizni kellene, hogy nem volt-e hiba. (pl. C-be ágyazásnál ezt tettük) Így elég egyszer megírni a hibaellenőrzést a blokk végén.

Viszont ha egy helyen van több lehetséges hiba kezelése akkor nem tudjuk pontosan, hogy hol merült fel a hiba.

A hibakezelő részben név szerint hivatkozhatunk a hibaeseményre, így csak olyan hibát tudunk lekezelni, aminek van neve. A felhasználó is létrehozhat névvel ellátott eseményt és vannak olyan hibaesemények, amiknek már van neve. (előre definiált hibaesemények)

Kivételek deklarálása:

kivétel_név EXCEPTION;

A kivételekre ugyanazok az érvényességi szabályok vonatkoznak mint a változókra. Egy blokkban deklarált kivétel a blokkra nézve lokális, cimkézett blokk esetén hivatkozhatunk a kivételre cimke.kivétel_név módon ... stb.

Az előre definiált kivételek a STANDARD package-ben vannak deklarálva az alábbi módon.

CURSOR_ALREADY_OPEN exception;

pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');

Nem minden belső hibának van neve. Ezeket nem tudjuk lekezelni hacsak nem adunk nekik nevet. Ezt egy fordítónak szóló direktívával tehetjük meg, aminek a neve EXCEPTION_INIT.

pragma EXCEPTION_INIT(hiba_név, hibakód);

A STANDARD package-ben ilyen direktívák is vannak, lásd a fenti példát.

A legfontosabb előre definiált kivételek a következők:

CURSOR_ALREADY_OPEN exception;

DUP_VAL_ON_INDEX exception;

TIMEOUT_ON_RESOURCE exception;

-TRANSACTION_BACKED_OUT exception;

INVALID_CURSOR exception;

NOT_LOGGED_ON exception;

LOGIN_DENIED exception;

NO_DATA_FOUND exception;

ZERO_DIVIDE exception;

INVALID_NUMBER exception;

TOO_MANY_ROWS exception;

STORAGE_ERROR exception;

PROGRAM_ERROR exception;

VALUE_ERROR exception;

A fentieket a következő utasítással listázhatjuk ki:

SELECT text FROM dba_source WHERE type = ‘PACKAGE’

AND name = ‘STANDARD’ AND text LIKE ‘%exception%’;

A kivételek meghívása:

A belső hibákat a rendszer automatikusan meghívja ha előfordul az esemény, és ha névvel láttuk el őket akkor ez egyben az adott nevű hibaesemény előfordulását is jelenti.

Az általunk deklarált eseményeket explicit módon meg kell hívni.

RAISE hibanév;

A fenti módon előre definiált (és névvel ellátott) eseményt is meghívhatunk.

Kivételek lekezelése:

A kivétel hívásakor (explicit vagy implicit módon) a vezérlés az aktuális blokk kivételkezelő részére adódik. Ha ott nincs lekezelve a kivétel akkor a külső blokknak adódik tovább, addig, amíg valahol le lesz kezelve. (ellenkező esetben hibaüzenet a futtató környezetnek)

EXCEPTION

WHEN kivétel_név OR kivétel_név2 THEN

utasítások

A WHEN OTHERS megadásával minden hibát lekezelhetünk (a név nélkülieket is)

Néhány apró tudnivaló:

A deklarációban felmerülő hibákat rögtön a külső blokk fogja megkapni.

Nyitott kurzor esetén felmerülő hiba lekezelése előtt a kurzor automatikusan bezáródik, így arra hivatkozni nem lehet a hibakezelőben.

Ha a kivételkezelő részben felmerül egy hiba akkor a vezérlés rögtön a külső blokk hibakezelő részére adódik.

Hiba lekezelése majd továbadása a külső blokknak -> RAISE; (kivételnév nélkül) Ilyen formában csak a kivételkezelőben fordulhat elő a raise.

Mi legyen ha a SELECT INTO nem ad vissza egyetlen sort sem? (NO_DATA_FOUND)

Megoldás -> alblokkba írás

Alprogramok hibáinak lekezelése:

A DBMS_STANDARD package raise_application_error(hibakód, hibaüzenet) procedúrájával az alprogramokból úgy térhetünk vissza, hogy egy megfelelő hibakódot adunk vissza a hívónak, amit az lekezelhet, ha a deklarációjában adott neki egy nevet. (A megadható hibakódok –20000 és –20999 között kell hogy legyenek.)

Enélkül csak a WHEN OTHERS résszel tudnánk lekezelni az alprogram hibáit, és így nem tudnánk megállapítani a hiba fajtáját.

SQLCODE és SQLERRM fv-ek

A felhasználó által definiált hibára +1-et ad vissza az SQLCODE, a belső hibákra pedig negatív számot. (kivétel +100 -> NO_DATA_FOUND)

Milyen hibaüzenetei vannak a rendszernek?

FOR err_num IN 1..9999 LOOP

dbms_output.put_line(SQLERRM(-err_num));

END LOOP;

A fenti két fv. nem használható közvetlenül SQL utasításban (pl. VALUES(SQLCODE)), értékükek lokális változóba kell tenni. (err_num := SQLCODE)

A le nem kezelt hibák esetén a rendszer különbözően viselkedik a futtató környezettől függően. Pl. C-be ágyazott program ROLLBACK-el, alprogram nem, és az OUT tipusú változóinak sem ad értéket.

Alprogramok (procdúrák, fv-ek)

PROCEDURE p_név(param) IS ... BEGIN ... END;

FUNCTION f_név(param) RETURN típus IS ... BEGIN ... END;

Mindkettő két részből áll specifikációból és body-ból. A specifikáció az IS kulcsszóig tart. A body-nak van deklarációs, végrehajtható és kivételkezelő része.

A paramétere NUMBER lehet de nem pl. NUMBER(3) és nem lehet NOT NULL megszorítás a paraméterre.

Az így létrehozott fv-ek SQL utasításban is használhatók. (Pl. IS_NUMBER() )

Visszatérés az alprogramból: RETURN (fv esetén visszatérési érték is kell)

Az alprogram deklarációk egy blokk deklarációs részének végén lehetnek csak (egyéb, pl. változó deklarációk után)

Lehetőség van az előre deklarációra (forward declaration)

Tárolt alprogramok: CREATE FUNCTION/PROCEDURE

Paraméterátadás pozíció illetve név szerint fv(p_név => érték)

Paraméter módok: IN, OUT, IN OUT

Paraméterek DEFAULT értéke p_nev IN típus DEFAULT érték

Package-beli alprogramok overload-olhatók ha a paraméterek száma vagy típusa eltérő

Ahhoz hogy egy fv-t SQL utasításban is használhassunk, az alábbi megszorításoknak kell eleget tennie:

1. tárolt fv legyen

2. egy sorra vonatkozó legyen és ne egy csoportra

3. csak IN módú paraméterei legyenek

4. paramétereinek típusa Oracle belső típus legyen

5. a visszaadott értékének típusa Oracle belső típus legyen

Package-ek

A package-ben lehetnek procedúrák, függvények, típus definíciók, változó deklarációk, konstansok, kivételek, kurzorok.

Két része a specifikációs rész és a törzs (body). A specifikációs részben vannak a publikus deklarációk. Ennek létrehozása (SQL utasítással):

CREATE OR REPLACE PACKAGE p_név IS

publikus típus és objektum deklarációk

alprogram specifikációk

END;

A body-ban vannak az alprogramok és a kurzorok implementációi. Csak ezeknek van implementációs része, így ha a package csak más objektumokat tartalmaz (változók, típusok, kivételek ... stb.) akkor nem is kell hogy body-ja is legyen.

A kurzorok kétféleképpen is megadhatók.

1. Vagy a specifikációban adjuk meg őket a szokásos módon, ekkor nem is szerepelnek az implementációs részben.

2. A specifikációs részben csak a nevét és a sortípusát adjuk meg

(CURSOR C1 RETURN <sortípus>) és az implementációs részben adjuk meg a SELECT-et.

CREATE OR REPLACE PACKAGE BODY p_név IS

privát típus és objektum deklarációk

alprogramok törzse (PROCEDURE ... IS ...)

kurzorok (CURSOR C1 RETURN <sortípus> IS SELECT ...)

[BEGIN inicializáló utasítások ]

END;

A body-ban vannak az implementációk és lehet neki inicializációs része is (BEGIN ... END között), ami csak egyszer fut le, amikor a package-re először hivatkoznak.

A package specifikációs részében szereplő objektumok lokálisak az adatbázissémára nézve és globálisak a package-re nézve. hivatkozás package-beli objektumokra: p_név.obj

a STANDARD package-beli objektumokra hivatkozhatunk a p_név nélkül.

Lehet azonos a neve két package-ben levő alprogramnak, amelyeknek más a paraméterezése. Ilyenkor híváskor derül ki, hogy melyik fog futni a formális és aktuális paraméterek egyeztetésekor (overloading). Például a STANDARD package-ben van több verzió is a TO_CHAR fv-re.

A package-ek legfontosabb előnyei:

Modularítás

Információ elrejtés

Egészben töltődik be a memóriába minden objektuma az első hivatkozáskor.

A package-ben deklarált változók és kurzorok a session végéig léteznek, így közösen használhatják azokat a többi programok. (Kivétel a REF CURSOR, ami package-ben nem deklarálható.)

Túlterhelt alprogramok írhatók (a lokális alprogramok is túlterhelhetők, csak a tároltak nem)

A package-ek forrásszövege a DBA_SOURCE táblában megnézhető.

A legfontosabb package-ek:

STANDARD Beépített függvények és alprogramok ebben vannak

DBMS_SQL DDL és dinamikus SQL végrehajtására

DBMS_OUTPUT pl. put_line()

DBMS_STANDARD az alkalmazás és az Oracle közötti interakciót segíti

UTL_FILE op. rendszer fájlok írása, olvasása

A PL/SQL nyelv használata SQL*PLUS környezetben:

PL/SQL procedúrákat a következőképpen hívhatunk meg SQL*PLUS-ból:

EXECUTE proc(param); -- vagy CALL proc(param);

A fenti mód ekvivalens azzal, mintha a következő pl/sql blokkot írnánk be:

BEGIN

proc(param);

END;

SQL*PLUS-ban definiálhatunk úgynevezett session változót, ami a session végéig él. Ezt használhatjuk pl/sql blokkban is, úgy, mintha az egy host változó lenne (:változó). Pl. egy függvény által visszaadott értéket tehetünk bele, lehet egy procedúra IN OUT paramétere ... stb. Végül az aktuális értékét kiírhatjuk a képernyőre (vagy fájlba -> SPOOL)

Létrehozása: VARIABLE v_név típus

Kiírása: PRINT v_név

Használata különböző helyzetekben:

EXECUTE :v_név := érték; (BEGIN :v_név := érték; END;)

EXECUTE :v_név := fv(param); (BEGIN :v_név := fv(param); END;)

EXECUTE proc(:v_név) (BEGIN proc(:v_név; END;)

Függvényhívás szintaxisa:

Séma.Package.Fv_név@Db_link(paraméterek)

Megszorítások fv-ekre:

Ahhoz, hogy egy fv-t SQL utasításban lehessen használni a következő kritériumokat kell teljesítenie:

Tárolt fv legyen

Az argumentumai csak egyszerű típusúak lehetnek (nem lehet pl. oszlop)

Az összes formális paramétere IN módú legyen

Az összes formális paramétere belső Oracle adattípusú legyen

A visszatérési értéke belső Oracle típusú legyen

Mellékhatások fv-ekben:

Mellékhatást okozhat ha egy fv adatbázis táblára vagy package változóra hivatkozik. Az ilyen függvényeket nem használhatjuk tetszőleges SQL utasításban. Pl.

create or replace function rossz_fv return number is

begin

INSERT INTO emp(ename) VALUES('kiss');

RETURN 11;

end;

select rossz_fv from dual;

06571: Function ROSSZ_FV does not guarantee not to update database

Vagyis végrehajtáskor hibaüzenetet kapunk.

A tárolt fv-ek esetén az Oracle ellenőrizni tudja, hogy milyen mellékhatásai lehetnek a fv-nek és ennek megfelelően engedi meg a függvény használatát különböző esetekben. A package-beli függvények viszont rejtve vannak így ezekre nekünk kell közölni a rendszerrel, hogy milyen mellékhatásai lehetnek a fv-nek. Ezt a package specifikációban egy PRAGMA-val tesszük meg, ami a deklaráció után kell hogy szerepeljen.

PRAGMA RESTRICT_REFERENCES(fv_név, WNDS [,WNPS] [,RNDS] [,RNPS]);

Ahol WNDS: writes no database state (nem módosítja az adatbázist)

WNPS: writes no package state (nem módosítja package változók értékét)

RNDS: reads no database state (nem kérdez le táblát)

RNPS: reads no package state (nem hivatkozik package változókra)

Az első megadása kötelező, a többi opcionális. Ezzel mondjuk meg az Oracle-nek, hogy a fv milyen mellékhatásokkal rendelkezhet (mennyire „tiszta” a fv) és az Oracle ez alapján dönti el, hogy milyen környezetekben fogja engedni a fv használatát.

Ha be akarjuk csapni és ”szebbnek” mondjuk a fv-t mint amilyen azt fordításkor észreveszi a fordító és szól:

0/0 PL/SQL: Compilation unit analysis terminated

2/3 PLS-00452: Subprogram 'ROSSZ_FV' violates its associated pragma

Triggerek

A triggert SQL utasítással hozhatjuk létre (CREATE TRIGGER), de a trigger végrehajtható részét PL/SQL nyelven kell megírnunk. A trigger valamilyen esemény hatására automatikusan elindul és végrehajtja a PL/SQL blokkban megadott utasításokat. Ezen utasítások végrehajtásához a trigger tulajdonosának kell, hogy joga legyen, méghozzá közvetlenül és nem role-okon keresztül. Az esemény lehet DML utasítás (pl. insert, update), DDL utasítás (pl. create, drop), vagy adatbázis esemény (pl. startup, login).

DML triggerek

CREATE [OR REPLACE] TRIGGER [schema.]trigger

{BEFORE | AFTER | INSTEAD OF}

{DELETE | INSERT | UPDATE [OF column [, column] ...]}

[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}] ...

ON [schema.]table

[ [REFERENCING { OLD [AS] old [NEW [AS] new]

| NEW [AS] new [OLD [AS] old] } ]

[ FOR EACH ROW

[WHEN (condition)] ]

pl/sql_block

A triggerhez tartozik egy kiváltó (elsütő) művelet (INSERT, DELETE, UPDATE).

A trigger egy objektumhoz (tábla vagy esetleg nézet) kötődik.

Időzítés: A trigger egy módosító művelet előtt vagy után (vagy helyette) fut le.

Trigger típusa: Ha megadjuk a FOR EACH ROW opciót akkor a trigger minden sorra egyszer végrehajtódik. Az ilyen triggereket sor-triggernek hívjuk. Ellenkező esetben csak utasításonként egyszer hajtódik végre a trigger. Ekkor a neve utasítás-trigger.

When feltétel csak sortriggerre adható meg. Ilyenkor a trigger csak azokra a sorokra fut le, amelyek kielégítik a feltételt.

Triggerek engedélyezhetők vagy letilthatók (ALTER TRIGGER)

Ha egy művelet több triggert is aktivizál akkor azok futási sorrendje nem garantált.

Triggeren belül nem adható ki tranzakciókezelő utasítás. COMMIT, ROLLBACK, SAVEPOINT

Az oszlopok régi és új értékére a PL/SQL blokkban úgy hivatkozhatunk mint host változókra. (kettőspont a változó előtt: :NEW.oszlop, :OLD.oszlop)

BEFORE triggerben az új értéket meg is változtathatjuk és ekkor ez kerül be majd az oszlopba. AFTER trigger esetén ezt nem tehetjük meg.

Egy AFTER trigger viszont már használhatja a ROWID-jét a sornak.

A triggerek aktivizálódási sorrendje:

1. BEFORE utasítás szintű triggerek

2. Minden egyes érintett sorra

a) a BEFORE sor szintű triggerek

b) maga a DML utasítás és az integritási feltételek ellenőrzése

c) az AFTER sor szintű triggerek

3. AFTER utasítás szintű triggerek

A trigger futása alatt a rendszer egy READ konzisztens állapotát garantálja minden hivatkozott táblának, így a trigger nem látja a futása alatt történt változásokat.

Mire vigyázzunk triggerek megadásakor?

Amit deklaratív módon is meg tud oldani az Oracle arra ne írjunk triggert.

Ne hozzunk létre rekurzív triggereket. (Pl. egy AFTER UPDATE trigger ne adjon ki update utasítást.)

INSERT esetén csak a NEW értékeknek van értelme, a régiek NULL-ok.

DELETE esetén csak az OLD értékeknek van értelme, az újak NULL-ok.

A WHEN után még nem kell kettőspont az OLD és NEW elé, csak a blokkban.

Ha több művelet elsütheti a triggert akkor így dönthetjük el melyik volt a tényleges:

IF INSERTING ... IF UPDATING [(‘oszlop’)]... IF DELETING ...

Update esetén még az oszlopot is megtudhatjuk.

Ha a trigger közben kivétel lép fel, amit nem kezeltek le akkor a trigger és az elsütő művelet is ROLLBACK-elve lesz. Így lehet pl. triggerből visszacsévélni az eredeti műveletet. Gyakran erre a célra a RAISE_APPLICATION_ERROR(hibakód, hibaüzenet) procedúrát használják, mert ekkor a kiváltó műveletet kiadó program kultúráltan lekezelheti a hibát.

DDL és tranzakció-kezelő utasítás nem lehet a triggerben.

Információk a triggerekről: DBA_TRIGGERS

Megszorítások a triggerek használatával kapcsolatban:

Hivatkozó tábla elsődleges és idegen kulcs oszlopát nem módosíthatja a trigger. (Hivatkozó tábla az, amelyik idegen kulcs hivatkozásban van a módosított táblával, amire épp sor szintű trigger fut.) Így az alábbi csak akkor működik ha nincs idegen kulcs definíció a két tábla között.

Ez a 9i verziótól már akkor is működik, ha van idegen kulcs.

CREATE OR REPLACE TRIGGER cascade_upd

AFTER UPDATE OF deptno ON dept

FOR EACH ROW

BEGIN

UPDATE emp SET emp.deptno = :new.deptno

WHERE emp.deptno = :old.deptno;

END;

Változás alatt lévő táblát nem olvashat a trigger. (Változás alatti az a tábla, amelynek módosítása alatt épp sor szintű trigger fut.) Vagyis egy sor szintű trigger nem olvashat egy éppen módosítás alatt levő táblából.

DDL és Adatbázis triggerek

Kiváltó eseményük lehet csak egy adott sémára, vagy az egész adatbázisra vonatkozó művelet.

Lehetséges DDL események: CREATE, DROP, RENAME, DDL …

Lehetséges adatbázis események: SERVERERROR, LOGON, STARTUP …

DBA_TRIGGERS.base_object_type -> database, schema, table, view

További infók: Application Developer’s Guide 9. fejezet

Alprogramok, triggerek karbantartása

Alprogramokkal kapcsolatos rendszerjogosultságok:

CREATE (ANY) PROCEDURE …

EXECUTE (ANY) PROCEDURE …

Alprogramokkal kapcsolatos katalógusok:

DBA_OBJECTS

CREATED amikor létrehozták

LAST_DDL utolsó módosítás

TIMESTAMP utolsó fordítás

STATUS VALID/INVALID

DBA_SOURCE -- ebből olvas az SQLPLUS DESCRIBE utasítása

DBA_ERRORS -- ebből olvas az SQLPLUS SHOW ERRORS utasítása

SELECT line|| '/' ||position POS,text

FROM user_errors

WHERE name = 'proc_nev'

ORDER BY line;

DBA_TRIGGERS (típus, esemény, tábla, when feltétel, státusz, forrás)

Adatbázis-objektumok között meglévő függőségek

Pl. egy procedúra hivatkozik egy táblára, egy függvényre, egy nézetre.

Ha a hivatkozott objektum megváltozik, akkor a hivatkozó INVALID állapotba kerül, és a legközelebbi hivatkozáskor újra fordítja a rendszer. Van közvetlen függőség és közvetett függőség.

USER_DEPENDENCIES és DBA_DEPENDENCIES nézetek

SELECT name, type, referenced_name, referenced_type

FROM user_dependencies

WHERE referenced_name IN ('EMP' , 'NEW_EMP' );

A fentinél elegánsabb módon nézhetők meg a függőségek két nézetből (DEPTREE, IDEPTREE). Ezek létrehozása és megfelelő feltöltése -> UTLDTREE.SQL

PUBLIC_DEPENDENCY (obj, hivatkozott_obj) nézet

PL/SQL komponensek újrafordítása

1. Automatikusan, amikor futás közben a rendszer INVALID-nak találja

2. Manuálisan

ALTER PROCEDURE <név> COMPILE

ALTER FUNCTION <név> COMPILE

ALTER PACKAGE <név> COMPILE {PACKAGE|BODY}

ALTER TRIGGER <név> COMPILE

Újrafordításkor először minden invalid objektumot újrafordít a rendszer, amitől az illető függ.

Feltételes fordítás (10.2-es verziótól)

Példák a dokumentációból.

BEGIN
$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
 $ERROR 'unsupported database release' $END
$ELSE
 DBMS_OUTPUT.PUT_LINE ('Release ' || DBMS_DB_VERSION.VERSION || '.' ||
 DBMS_DB_VERSION.RELEASE || ' is supported.');
 COMMIT WRITE IMMEDIATE NOWAIT;
$END
END;
/

CREATE PROCEDURE circle_area(radius my_pkg.my_real) IS
 my_area my_pkg.my_real;
 my_datatype VARCHAR2(30);
BEGIN
 my_area := my_pkg.my_pi * radius;
 DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius) 
 || ' Area: ' || TO_CHAR(my_area) );
 $IF $$my_debug $THEN -- if my_debug is TRUE, run some debugging code
 SELECT DATA_TYPE INTO my_datatype FROM USER_ARGUMENTS 
 WHERE OBJECT_NAME = 'CIRCLE_AREA' AND ARGUMENT_NAME = 'RADIUS';
 DBMS_OUTPUT.PUT_LINE('Datatype of the RADIUS argument is: ' || my_datatype);
 $END
END;
/

ALTER PROCEDURE circle_area COMPILE PLSQL_CCFLAGS = 'my_debug:TRUE' 
 REUSE SETTINGS;