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
- Ha később változik a típus a kód maradhat
%ROWTYPE: A deklarációban nem szerepelhet inicializáció, de rekordok közötti értékadás megengedett pl. rek1 := rek2;
Értékadás: két formája van ”:=” és SELECT (FETCH) INTO az utóbbival nem lehet logikai változónak értéket adni.
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
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
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.
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;
Dinamikus SQL futtatás
Szöveg szerkesztő mőveletekkel összerakjuk az SQL paramcs szöveget (string tipus) és excecute parancsal lefuttatjuk az adatbázison.
A futtatási parancs alakja:
– EXECUTE IMMEDIATE string
– EXECUTE IMMEDIATE string USING
– EXECUTE IMMEDIATE string INTO
– DBMS_SQL
Példa:
PROCEDURE process_lineitem (
line_in IN INTEGER)
IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN process_line'||
line_in ||'; END;';
END;
LOOP
counter := 1;
summa := 0;
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor, 'SELECT * FROM ' || name_array(i), dbms_sql.native);
dbms_sql.define_column(mycursor, col_array(i), first_col);
result_rows := dbms_sql.execute(mycursor);
LOOP
EXIT
WHEN dbms_sql.fetch_rows (mycursor) = 0;
dbms_sql.column_value(mycursor, col_array(i), first_col);
res.extend(counter);
res(counter) := first_col;
summa := summa + first_col;
counter := counter +1;
END LOOP;
dbms_sql.close_cursor(mycursor);
Analitikus függvények adattárházakban
Az Oracle analitikus függvényei lehetővé teszik az adattárházakban a
* rang és százalékos eloszlás kiszámítását,
* mozgó ablak számításokat,
* megelőzés/lemaradás analízist,
* első/utolsó analízist,
* lineáris regressziós statisztikák kiszámítását.
Analitikus függvények végrehajtása
Az analitikus függvényeket tartalmazó lekérdezések három lépésben kerülnek végrehajtásra. Először az összes JOIN, WHERE, GROUP BY, HAVING utasításrészek hajtódnak végre. Ezután a létrejövő eredményhalmazon történik az analitikus függvények kiszámítása. Végül, ha a lekérdezés tartalmazott ORDER BY utasításrészt, az kerül végrehajtásra.
Partíciók - Az analitikus függvények lehetővé teszik, hogy az eredményhalmaz sorait további csoportokra ún. partíciókra bontsuk. Ezek a partíciók nem egyeznek meg a táblák partícióival. A partíciók a GROUP BY utasításrész végrehajtása után jönnek létre.
Ablakok - A partíció minden sorára definiálható egy csúszó ablak. Ez az ablak határozza meg, hogy az aktuális sorra mely adatok felhasználásával kell a számításokat elvégezni. Az ablak méretét meghatározhatja a sorok száma vagy egy intervallum pl. idő. Az ablak rendelkezik egy kezdő sorral és egy befejező sorral; mozoghat az ablak egyik vége vagy mindkét vége. Az ablak lehet akkora, mint a partíció, de lehet akár egy sor is. Abban az esetben, ha az ablak a partíció valamelyik széléhez van közel, akkor az ablak kisebb lehet a definiáltnál (mindig a partícióba esik).
Aktuális sor - analitikus függvények esetén minden számítás a partíción belüli aktuális soron alapul. Az aktuális sor szolgál referenciapontként az ablak kezdő sorának és befejező sorának meghatározásakor.
Az analitikus függvények párhuzamosan is végrehajthatók, ezzel növelve a számítások teljesítményét.
Rangfüggvények
A rangfüggvények egy rekord rangját adják meg az eredményhalmazban más rekordokhoz viszonyítva valamilyen mérőszámok szerint.
A RANK és a DENSE_RANK függvények
Ezek a függvények sorok rangsorolását adják meg egy csoporton belül.
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
A különbség a két függvény közt, hogy a DENSE_RANK folyamatos számokat ad, míg a RANK esetén kimaradnak értékek (holtverseny esetén). Jellemzők:
* a növekvő az alapértelmezett rendezés
* az opcionális PARTITION BY-ban szereplő kifejezések felosztják az eredményhalmazt csoportokra, melyekre a számítás történik
* ha a particionálás hiányzik, a teljes eredményhalmazra történik a számítás
* az ORDER BY adja meg a sorrendet, mely alapján a rang számításra kerül, minden csoport/partíció esetén 1-től kezdődően
Példa
select cikk,
sum(mennyiseg),
rank() over(order by sum(mennyiseg) desc) R,
dense_rank() over(order by sum(mennyiseg) desc) DR
from eladas
group by cikk;
CIKK SUM(MENNYISEG) R DR
1 alma 28 1 1
2 citrom 13 2 2
3 banán 10 3 3
4 körte 8 4 4
5 eper 3,5 5 5
6 füge 3 6 6
7 ananász 3 6 6
8 datolya 3 6 6
9 szeder 2,5 9 7
10 málna 2 10 8
11 mangó 1 11 9
12 dinnye 1 11 9
13 s.dinnye 1 11 9
Az esetleges holtversenyt több rendező kifejezés használatával kerülhetjük el:
select cikk,
sum(mennyiseg),
sum(ertek),
rank() over(order by sum(mennyiseg) desc, sum(ertek) desc) R,
dense_rank() over(order by sum(mennyiseg) desc, sum(ertek) desc) DR
from eladas
group by cikk;
CIKK SUM(MENNYISEG) SUM(ERTEK) R DR
1 alma 28 6800 1 1
2 citrom 13 4600 2 2
3 banán 10 4350 3 3
4 körte 8 3650 4 4
5 eper 3,5 4900 5 5
6 füge 3 3600 6 6
7 datolya 3 1700 7 7
8 ananász 3 1400 8 8
9 szeder 2,5 6000 9 9
10 málna 2 4300 10 10
11 s.dinnye 1 500 11 11
12 mangó 1 250 12 12
13 dinnye 1 100 13 13
Egy lekérdezés több rangfüggvényt tartalmazhat, mindegyik lehet különbözőképpen particionálva:
select bolt,
to_char(datum,'YYYY.MM') evho,
sum(ertek),
rank() over(partition by bolt order by sum(ertek) desc) R_Bolt,
rank() over(partition by to_char(datum,'YYYY.MM') order by sum(ertek) desc) R_EvHo
from eladas
group by bolt, to_char(datum,'YYYY.MM');
BOLT EVHO SUM(ERTEK) R_BOLT R_EVHO
1 1 2012.03 6400 1 1
2 1 2011.03 4200 2 2
3 1 2010.03 3750 3 3
4 2 2010.03 5300 1 1
5 2 2012.03 4600 2 3
6 2 2011.03 4300 3 1
7 3 2012.03 5500 1 2
8 3 2011.03 4100 2 3
9 3 2010.03 4000 3 2
ROW_NUMBER
A ROW_NUMBER függvény egy egyedi sorszámot ad (1-től kezdődően, szekvenciálisan) minden sornak a partíción belül. A függvény nem determinisztikus, holtverseny esetén a sorszám az egyes lekérdezések során változhat.
ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )
Példa
select vasarlo,
sum(ertek),
row_number() over(order by sum(ertek) desc) PR
from eladas
group by vasarlo;
VASARLO SUM(ERTEK) PR
1 2 11300 1
2 3 7700 2
3 1 7450 3
4 4 5800 4
5 5 5000 5
6 6 3900 6
7 7 1000 7
Ablakfüggvények
Az ablakfüggvények lehetővé teszik halmozott, mozgó vagy centrális aggregátumok kiszámítását. Ezek a tábla minden sorára visszaadnak egy értéket, mely érték a sorhoz tartozó ablaktól (soroktól) függnek. Felhasználhatjuk többek közt a MIN, MAX, COUNT, SUM, AVG függvényeket. Csak a SELECT illetve az ORDER BY utasításrészben használhatjuk ezeket. Ide tartozik a FIRST_VALUE illetve a LAST_VALUE függvény, amik egy ablakhoz tartozó első illetve az utolsó értéket adják meg. Ezek a függvények a tábla több sorához biztosítanak hozzáférést self-join nélkül. Az ablakfüggvények szintaxisa:
analytic_function([ arguments ])
OVER (analytic_clause)
ahol
analytic_clause =
[ query_partition_clause ]
[ order_by_clause [ windowing_clause ] ]
query_partition_clause =
PARTITION BY
{ value_expr[, value_expr ]...
}
windowing_clause =
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
|
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
Ablak megadása
Logikai eltolást a RANGE kulcsszóval adhatunk meg (RANGE x PRECEDING), ahol x számkonstans vagy numerikus kifejezés, ami konstansra értékelődik ki; vagy x INTERVAL N DAY/MONTH/YEAR PRECEDING. Logikai eltolás esetén az ORDER BY általában csak egy kifejezést tartalmazhat, ennek típuskompatibilisnek kell lenni az eltolással. Ha az ablak definíciója RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW vagy RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, akkor az ORDER BY több kifejezést tartalmazhat. Fizikai eltolást a ROWS kulcsszóval adunk meg.
Példa: halmozott érték vásárlónként
select vasarlo,
datum,
sum(ertek),
sum(sum(ertek)) over (partition by vasarlo order by vasarlo, datum rows unbounded preceding)H_Ertek
from eladas
group by vasarlo, datum
order by vasarlo, datum;
VASARLO DATUM SUM(ERTEK) H_ERTEK
1 1 2010.03.20. 2250 2250
2 1 2011.03.20. 3100 5350
3 1 2012.03.20. 2100 7450
4 2 2010.03.20. 4000 4000
5 2 2011.03.20. 2900 6900
6 2 2012.03.20. 4400 11300
7 3 2010.03.20. 1700 1700
8 3 2011.03.20. 4100 5800
9 3 2012.03.20. 1900 7700
10 4 2010.03.20. 1300 1300
11 4 2012.03.20. 4500 5800
12 5 2010.03.20. 2100 2100
13 5 2011.03.20. 1300 3400
14 5 2012.03.20. 1600 5000
15 6 2010.03.20. 1700 1700
16 6 2011.03.20. 1200 2900
17 6 2012.03.20. 1000 3900
18 7 2012.03.20. 1000 1000
Figyeljük meg a példában az egymásba ágyazott SUM-okat!
Mozgó átlag esetén az ablak definíciója: pl. ...rows 2 preceding. Bizonyos esetekben szükséges lehet, hogy az ablak mérete soronkét változzon (pl. csak munkanapok szerepelnek a táblánkban, és mozgó átlagot szeretnénk számítani 3 napra). Ebben az esetben az ablak definíciója lehet ... OVER (ORDER BY datum RANGE fn(datum) PRECEDING), ahol fn egy függvény, ami az adott dátumtól függően megadja hogy hány nap kell 2 munkanaphoz.
Abban az esetben, ha fizikai eltolást használunk (ROWS), a determinisztikus eredményhez az szükséges, hogy a rendező kifejezés(ek)nek egyedinek kell lenni.
Riportálás
Miután egy lekérdezés feldolgozásra került, az aggregált értékek, mint pl. egy oszlop átlaga, könnyen kiszámíthatók a partíciókra és elérhetők más riport függvények számára. A riport aggregáló függvények ugyanazt az értéket adják minden sorra egy partíció esetén. A szintaxis:
{SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE ... }
([ALL | DISTINCT] {kifejezés1 [,...] | *})
OVER ([PARTITION BY kifejezés2[,...]])
A PARTITION BY utasításrész határozza meg azokat a csoportokat, amikre a függvény értékét ki kell számítani. Ha hiányzik a PARTITION BY utasításrész, akkor a teljes eredményhalmazon kerül kiszámításra. A riport függvények csak a SELECT vagy az ORDER BY utasításrészben szerepelhetnek. Előnyük, hogy gyorsítják a lekérdezéseket, mivel többször felhasználhatók a már kiszámított adatok.
Példa: cikkenként keressük meg, hogy mely bolt(ok)ban volt az eladás maximális
select cikk, bolt, s_ertek
from
(select cikk as cikk,
bolt,
sum(ertek) as s_ertek,
max(sum(ertek)) over (partition by cikk) as max_bolt_ertek
from eladas
group by cikk, bolt
)
where s_ertek=max_bolt_ertek
CIKK BOLT S_ERTEK
1 alma 1 3550
2 ananász 2 1400
3 banán 1 3550
4 citrom 1 2100
5 datolya 2 1700
6 dinnye 1 100
7 eper 1 2300
8 füge 3 2400
9 körte 3 1800
10 málna 3 2200
11 mangó 1 250
12 s.dinnye 3 500
13 szeder 3 3600
RATIO_TO_REPORT függvény
A függvény egy érték arányát számítja ki értékek összegéhez viszonyítva.
RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )
Példa: boltok részesedése az eladásokból
select bolt,
sum(ertek) bolt_ertek,
sum(sum(ertek)) over () ossz_ertek,
ratio_to_report(sum(ertek)) over() "Arány"
from eladas
group by bolt;
BOLT BOLT_ERTEK OSSZ_ERTEK Arány
1 1 14350 42150 0,340450771055753
2 2 14200 42150 0,336892052194543
3 3 13600 42150 0,322657176749703
LEAD/LAG
A LAG és LEAD függvények értékek összehasonlítására használhatók, amikor a sorok relatív pozíciója megbízhatóan ismert. Az aktuális sor és a cél sor közti távolságot kell megadni sorokban. Mivel a függvények egyszerre több sor elérését teszik lehetővé self-join nélkül, gyorsíthatják a feldolgozást. A LAG függvény az aktuális sor előtti sorhoz tartozó értéket adja meg, a LEAD az aktuális sor utáni sorhoz tartozó értéket adja meg. Ezek a függvények tekinthetők az NTH_VALUE függvény egyszerűbb változatának. Csak fizikai eltolást használhatunk ezekkel a függvényekkel.
{LAG | LEAD} ( value_expr [, offset] [, default] ) [RESPECT NULLS|IGNORE NULLS]
OVER ( [query_partition_clause] order_by_clause )
Az offset opcionális paraméter, az eltolást adja meg, alapértelmezett értéke 1. A default opcionális paraméter, és a visszatérési értéket adja meg, ha ablakon kívüli sorra történik a hivatkozás. IGNORE NULLS esetén a NULL értékek kimaradnak az eltolás számításánál.
Példa: LEAD/LAG
select to_char(datum,'YYYY') "Év",
sum(ertek),
lag(sum(ertek),1) over (order by to_char(datum,'YYYY')) lag1,
lead(sum(ertek),1) over (order by to_char(datum,'YYYY')) lead1
from eladas
group by to_char(datum,'YYYY');
Év SUM(ERTEK) LAG1 LEAD1
1 2010 13050 12600
2 2011 12600 13050 16500
3 2012 16500 12600
FIRST_VALUE, LAST_VALUE függvények
Ezek a függvények egy ablakon belüli első illetve utolsó sort adják meg. Ezek az értékek gyakran szükségesek viszonyítási alapként.
FIRST_VALUE|LAST_VALUE ( ) [RESPECT NULLS|IGNORE NULLS] OVER (analytic clause );
Példa: FIRST_VALUE
A következő példa boltonként, vásárlónként megadja a vásárlások értékének arányát az adott boltban legkevesebbet költő vásárlóhoz.
select vasarlo,
bolt,
sum(ertek),
sum(ertek)/first_value(sum(ertek)) over (partition by bolt order by sum(ertek) ) arány
from eladas
group by vasarlo, bolt
order by bolt, vasarlo;
VASARLO BOLT SUM(ERTEK) ARÁNY
1 1 1 4350 1,5
2 2 1 7100 2,44827586206897
3 5 1 2900 1
4 1 2 3100 1,63157894736842
5 2 2 3200 1,68421052631579
6 3 2 1900 1
7 5 2 2100 1,10526315789474
8 6 2 3900 2,05263157894737
9 2 3 1000 1
10 3 3 5800 5,8
11 4 3 5800 5,8
12 7 3 1000 1
További aggregáló függvények
LISTAGG függvény
A LISTAGG függvény rendezi az adatokat az ORDER BY utasításrésznek megfelelően minden csoportra, és összefűzi a megadott oszlop értékeit.
LISTAGG (expr [, delimiter]) WITHIN GROUP (ORDER BY oby_expression_list)
Példa: LISTAGG mint aggregáló függvény
select tranz, listagg(cikk,' ') within group (order by ertek desc,mennyiseg desc) cikkek
from eladas
where to_char(datum,'YYYY')='2010'
group by tranz;
TRANZ CIKKEK
1 1 banán alma körte citrom mangó dinnye
2 2 banán alma citrom
3 3 datolya ananász banán alma
4 4 alma eper füge citrom
5 5 málna banán citrom
6 6 eper alma
7 7 szeder alma citrom
8 8 körte s.dinnye
LISTAGG mint riport aggregáló függvény
select cikk,
bolt,
min(ertek),
listagg(min(ertek),';') within group (order by cikk) over (partition by bolt) ert_list
from eladas
where to_char(datum,'YYYY')='2011'
group by bolt, cikk;
CIKK BOLT MIN(ERTEK) ERT_LIST
1 alma 1 500 500;450;350;700;1100
2 banán 1 450 500;450;350;700;1100
3 citrom 1 350 500;450;350;700;1100
4 eper 1 700 500;450;350;700;1100
5 málna 1 1100 500;450;350;700;1100
6 alma 2 250 250;700;450;2400
7 eper 2 700 250;700;450;2400
8 körte 2 450 250;700;450;2400
9 szeder 2 2400 250;700;450;2400
10 eper 3 700 700;1200;2200
11 füge 3 1200 700;1200;2200
12 málna 3 2200 700;1200;2200
FIRST/LAST függvények
A FIRST/LAST függvények lehetővé teszik, hogy egy adathalmazt rendezzünk, és az első/utolsó sorával dolgozzunk tovább - egy aggregáló függvényt alkalmazhatunk az első/utolsó sor(ok) egy tetszőleges oszlopára. Azaz az A oszlop szerint rendezzük a sorokat, és az eszerinti első/utolsó sor(sorok) egy B oszlopára alkalmazunk egy aggregáló függvényt. Így nincs szükség self-join-ra vagy allekérdezésre, ezáltal nő a teljesítmény.
aggregate_function KEEP ( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]
Példa: FIRST/LAST
Boltonként az első és utolsó értékesítési napra az eladások összege.
select cikk,
sum(ertek) keep (dense_rank first order by datum) "First",
sum(ertek) keep (dense_rank last order by datum) "Last"
from eladas
group by cikk;
CIKK First Last
1 alma 2400 2400
2 ananász 400 1000
3 banán 2400 1500
4 citrom 1500 2400
5 datolya 500 1200
6 dinnye 100 100
7 eper 1200 1600
8 füge 600 1800
9 körte 1200 2000
10 mangó 250 250
11 málna 1000 3300
12 s.dinnye 500 500
13 szeder 1000 2600
Pivot műveletek
PIVOT
A SELECT utasítás PIVOT utasításrésze lehetővé teszi, hogy olyan lekérdezéseket írjunk, amik a sorokat oszlopokká forgatják valamint a forgatás során aggregálást is végeznek. Ez alapvető technika az adattárházak esetén.
Példa: alap PIVOT művelet
select *
from
(
select cikk, bolt, ertek
from
(
select cikk, bolt, extract(year from datum) ev,
sum(ertek) ertek,
sum(mennyiseg) mennyiseg
from eladas
group by cikk, bolt, extract(year from datum)
)
) S pivot (sum(ertek)
for bolt in (1 as "1. bolt", 2 as "2. bolt", 3 as "3. bolt")
)
order by cikk;
CIKK 1. bolt 2. bolt 3. bolt
1 alma 3550 2150 1100
2 ananász 1400
3 banán 3550 800
4 citrom 2100 1800 700
5 datolya 1700
6 dinnye 100
7 eper 2300 1300 1300
8 füge 1200 2400
9 körte 1400 450 1800
10 málna 1100 1000 2200
11 mangó 250
12 s.dinnye 500
13 szeder 2400 3600
Példa: több oszlop alapján történő pivot
select *
from
(
select cikk, bolt, ev, ertek
from
(
select cikk, bolt, extract(year from datum) ev,
sum(ertek) ertek,
sum(mennyiseg) mennyiseg
from eladas
group by cikk, bolt, extract(year from datum)
)
) S pivot (sum(ertek)
for (ev,bolt) in ((2010,1) as "2010-1. bolt",
(2010,2) as "2010-2. bolt",
(2010,3) as "2010-3. bolt",
(2011,1) as "2011-1. bolt",
(2011,2) as "2011-2. bolt",
(2011,3) as "2011-3. bolt",
(2012,1) as "2012-1. bolt",
(2012,2) as "2012-2. bolt",
(2012,3) as "2012-3. bolt"
)
)
order by cikk;
CIKK 2010-1. bolt 2010-2. bolt 2010-3. bolt 2011-1. bolt 2011-2. bolt 2011-3. bolt 2012-1. bolt 2012-2. bolt 2012-3. bolt
1 alma 800 800 800 1250 750 1500 600 300
2 ananász 400 1000
3 banán 1600 800 450 1500
4 citrom 600 600 300 700 800 1200 400
5 datolya 500 1200
6 dinnye 100
7 eper 600 600 700 700 700 1600
8 füge 600 1200 600 1200
9 körte 400 800 450 1000 1000
10 málna 1000 1100 2200
11 mangó 250
12 s.dinnye 500
13 szeder 1000 2400 2600
Példa: több oszlopra történő aggregálás
select *
from
(
select cikk, bolt, ertek, mennyiseg
from
(
select cikk, bolt, extract(year from datum) ev,
sum(ertek) ertek,
sum(mennyiseg) mennyiseg
from eladas
group by cikk, bolt, extract(year from datum)
)
) S pivot (sum(ertek) as ert, sum(mennyiseg) as meny
for bolt in (1,2,3)
)
order by cikk;
CIKK 1_ERT 1_MENY 2_ERT 2_MENY 3_ERT 3_MENY
1 alma 3550 14 2150 9 1100 5
2 ananász 1400 3
3 banán 3550 8 800 2
4 citrom 2100 6 1800 5 700 2
5 datolya 1700 3
6 dinnye 100 1
7 eper 2300 1,5 1300 1 1300 1
8 füge 1200 1 2400 2
9 körte 1400 3 450 1 1800 4
10 málna 1100 0,5 1000 0,5 2200 1
11 mangó 250 1
12 s.dinnye 500 1
13 szeder 2400 1 3600 1,5
UNPIVOT
Az UNPIVOT művelet nem fordít vissza egy PIVOT műveletet. Csak az adatoszlopokból készít sorokat, de a PIVOT során elvégzett aggregálásokat nem fordítja vissza!
Példa: unpivot
with pivoted_table as
(
select *
from
(
select cikk, bolt, ertek
from
(
select cikk, bolt, extract(year from datum) ev,
sum(ertek) ertek,
sum(mennyiseg) mennyiseg
from eladas
group by cikk, bolt, extract(year from datum)
)
) S pivot (sum(ertek) s_ertek
for bolt in (1 as B1, 2 as B2, 3 as B3 )
)
)
select *
from pivoted_table
unpivot include nulls
(s_ertek
for bolt in (B1_s_ertek,B2_s_ertek,B3_s_ertek))
order by cikk,bolt;
CIKK BOLT S_ERTEK
1 alma B1_S_ERTEK 3550
2 alma B2_S_ERTEK 2150
3 alma B3_S_ERTEK 1100
4 ananász B1_S_ERTEK
5 ananász B2_S_ERTEK 1400
6 ananász B3_S_ERTEK
7 banán B1_S_ERTEK 3550
8 banán B2_S_ERTEK 800
9 banán B3_S_ERTEK
10 citrom B1_S_ERTEK 2100
11 citrom B2_S_ERTEK 1800
12 citrom B3_S_ERTEK 700
.. ..... .....
Az egyszerűség kedvéért a pivotált adatokat tartalmazó tábla helyett allekérdezés faktorizáció szerepel a példában. Az unpivot során "új" oszlopok keletkeznek - a példában s_ertek és bolt, a bolt oszlop értékei a pivotált táblában szereplő oszlopnevek.
Gyakori adatcsoportok
Sok esetben felmerülő igény, hogy vásárlási szokásokat kell elemezni, pl. milyen termékeket vásárolnak együtt, általában mit vásárol még az, aki sört vesz. Ilyen elemzésekhez rendelkezésre áll a DBMS_FREQUENT_ITEMSET csomag. A csomag FI_TRANSACTIONAL táblafüggvénye adja vissza a gyakran előforduló elemeket; az eredmény egy nested table. (Részletesen: Oracle Database PL/SQL Packages and Types Reference)
Példa a dbms_frequent_itemset használatára
Szükségünk van egy típusra:
create or replace type fi_varchar_nt as table of varchar2(30);
A lekérdezés:
with x as
(
select itemset, elofordulas, hossz, rnk, tranzakcio_szam
from (select itemset,
elofordulas,
hossz,
rank() over (order by elofordulas desc) rnk,
tranzakcio_szam
from (select cast(itemset as fi_varchar_nt) itemset, support as elofordulas, length as hossz, total_tranx as tranzakcio_szam
from table(dbms_frequent_itemset.fi_transactional
-- a tranzakciós adatok
(cursor(select tranz, cikk from eladas),
-- minimális relatív gyakoriság
0.1,
-- minimális elemszám
2,
-- maximális elemszám
5,
-- cursor, amilyen elemeknek kell szerepelni
null,
-- cursor, amilyen elemek nem szerepelhetnek pl. cursor(select * from table(fi_varchar_nt('eper')))
null
--
)
)
)
)
)
select x.elofordulas, x.hossz, x.rnk,
listagg(xt.column_value,';') within group (order by xt.column_value) cikkek
from x, table(x.itemset) xt
group by x.elofordulas,x.hossz,x.rnk,ora_hash(x.itemset)
order by hossz,rnk
ELOFORDULAS HOSSZ RNK CIKKEK
1 8 2 1 alma;citrom
2 6 2 2 alma;banán
3 5 2 3 alma;eper
4 4 2 4 banán;citrom
5 3 2 5 alma;körte
6 3 2 5 ananász;datolya
7 3 2 5 alma;szeder
8 3 3 5 alma;banán;citrom
Megjegyzések
Megjegyzés küldése