Oracle PLSQL és analitikus függvények és használatuk (11G)

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

  1. 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;

 

 

 

AZ ANALITIKUS FÜGGVÉNYEKRŐL ÁLTALÁNOSAN

Az analitikus függvények általános formája a következő:
Függvény_név(arg1,…, argn) OVER ( [PARTITION BY <…>] [ORDER BY <….>] [<ablak_definíció>] )
Az analitikus függvények kiértékelése az összes join és a where ágon felsorolt
feltételek kiértékelése után történik meg.
A formula az alkalmazni kívánt függvény nevével és annak paramétereivel kezdődik.

 

1. Az ORDER BY rész
Egy partíción belül a rekordok sorrendjét az order by rész segítségével tudjuk
befolyásolni. Egyes függvények (pl.: Lead, Lag, Rank, stb.) kimenetét befolyásolja a
rekordok partíción belüli sorrendje, másokét nem (pl. Sum, Avg, Min, stb.)

Az order by rész általános formája a következő:
ORDER BY <sql_kif> [ASC|DESC] NULLS [FIRST|LAST]
Az [ASC | DESC] résszel tudjuk befolyásolni, hogy a halmaz rendezettsége növekvő
vagy csökkenő legyen.

A NULLS [FIRST | LAST] résszel pedig azt mondjuk meg, hogy a rendezettség
szerint a null értékek a halmaz elejére vagy végére kerüljenek.
2. A PARTITION BY rész
A partition by használatával az eredmény halmaza csoportosítható, mely csoportokon
aggregációk hajthatók végre. Jogosan merül fel a kérdés, hogy akkor mi a különbség
a partition by záradékkal ellátott analitikus függvény és egy group by záradékkal
ellátott lekérdezés között.

A legfontosabb különbség talán az, hogy míg a group by záradékkal ellátott
lekérdezés select ágán nem szerepelhet olyan oszlop definíció, amely nem szerepel
a group by ágon, addig az analitikus függvényeknél nincs ilyen megkötés, viszont az
aggregáció ugyan úgy elvégezhető. Azaz az analitikus függvények úgy végzik el a
csoportosítást és rajtuk az aggregációt, hogy a megjelenő eredményhalmaz
ténylegesen nem lesz csoportosítva.
3. Az ablak-definíciós rész
Néhány analitikus függvény támogatja az ablak-definíció használatát, melynek
segítségével tovább szűkíthetjük a partíción belüli rekordok számát, oly módon, hogy
a partíción belül meghatározzuk az ablak kezdetét és a végét. Ezeket a határokat az
aktuális sorhoz képest relatívan tudjuk megadni. Kétféle ablaktípus létezik, a ROW és
a RANGE.

Az ablak definíció általános szintaxisa a következő:
[ ROW | RANGE ] BETWEEN <kezdőpont_kif> AND <végpont_kif>
ahol a <kezdőpont_kif> a következőképpen nézhet ki:
(UNBOUNDED PRECEDING | CURRENT ROW | <sql_kif> [PRECEDING | FOLLOWING] )
ahol a <végpont_kif> a következőképpen nézhet ki:
(UNBOUNDED FOLLOWING | CURRENT ROW | <sql_kif> [PRECEDING | FOLLOWING] )

3.1 ROW típus esetén
Az UNBOUNDED PECEDING jelentése az aktuális sort megelőző partíción belüli
első sor. Ennek analógiájára az UNBOUNDED FOLLOWING az aktuális sort követő
partíción belüli utolsó sort fogja jelenteni.

A CURRENT ROW az aktuális sort jelenti.

Az <sql_kif> PRECEDING az aktuális sort <sql_kif>-el megelőző sort, az <sql_kif>
FOLLOWING pedig az aktuális sort követő <sql_kif>-dik sort jelenti, ahol az <sql_kif>
értékének pozitív egésznek kell lenni.

A kezdőpontnak mindig kisebbnek kell lenni a végpontnál.

3.2 RANGE típus esetén
A RANGE típus esetén a szintaxis ugyan az mint a ROW típus esetén, csupán
értelmezésbeli különbség van, valamit további megkötések, melyek a következők.
· Az order by záradék csak egy kifejezést tartalmazhat.
· A <kezdőpont_kif> és a <végpont_kif>-ben szereplő <sql_kif> típusának
kompatibilisek kell lenni az order by záradékban szereplő kifejezés
típusával. Ez azt jelenti, hogy ha a kifejezés típusa number, akkor az order by
kifejezésnek number vagy date típusúnak kell lenni. Ha a kifejezés egy
intervallum típus, akkor az order by kifejezésnek date típusúnak kell lenni.
A RANGE típus értelmezésében a kezdő és a végpont nem más, mint az order by
által meghatározott oszlop aktuális értékének a kifejezéssel történő eltolása.
4 ANALITIKUS FÜGGVÉNYEK HASZNÁLATA

4.1 A LAG és a LEAD függvények
A LAG és a LEAD függvények segítségével egy halmaz bármely során állva elkérhetjük
egy az általunk definiált rendezési reláció szerinti az adott sort x-el megelőző (Lag) illetve
követő (Lead) sor egy oszlopának értékét.
LEAD (<sql_kifejezés>, <eltolás>, <alapértelmezett_érték>)
OVER ([PARTITION BY <…>] [ORDER BY <….>])
LAG (<sql_kifejezés>, <eltolás>, <alapértelmezett_érték>)
OVER ([PARTITION BY <…>] [ORDER BY <….>])

Mindkét függvénynek 3 paramétere van:
· sql kifejezés: Egy szabványos sql kifejezés, mely ki lesz értékelve a megelőző
vagy a következő soron.
· eltolás: Egy egész szám, mely megmondja hány sorral megelőző illetve követő
soron értékelődjön ki az első paraméter.
· alapértelmezett érték: Egy alapértelmezett érték mely akkor kerül visszaadásra,
ha a kiértékelt kifejezés eredménye null.
Példa a LAG és a LEAD függvények használatára:
Tegyük fel, hogy szükségünk van a dolgozók nevének és fizetésének listájára abc
sorrendben úgy, hogy minden sorba oda kell írnunk azt is, hogy az előző és a
következő sorban mennyi volt a fizetés. Amennyiben nincs megelőző vagy következő
sor úgy 0-t írjunk a megfelelő helyre. Ez a probléma a következőképpen oldható meg.

SELECT dolg.név “Név”,dolg.fizetés “Fizetés”,
LAG(dolg.fizetés,1,0) OVER (ORDER BY dolg.név) “Előző sor fizetése”,
LEAD(dolg.fizetés,1,0) OVER (ORDER BY dolg.név) “Következő sor fizetése”
FROM aa dolg;

 

4.2 A ROW_NUMBER, RANK és DENSE_RANK függvények

A függvények szintaxisa a következő:
ROW_NUMBER () OVER ([PARTITION BY <…>] [ORDER BY <….>])
RANK () OVER ([PARTITION BY <…>] [ORDER BY <….>])
DENSE_RANK () OVER ([PARTITION BY <…>] [ORDER BY <….>])

Az említett függvények mindegyike egy sorszámot ad az eredmény halmaz minden egyes
sorának egy rendezési relációt alapul véve. A rendezési relációt a már fentebb említett
order by záradék segítségével tudjuk definiálni. Az eltérés a sorszámok kiosztásába van,
melyet a következő szabály határoz meg.
· ROW_NUMBER() : A row_number esetén a sorszámok szigorúan monoton
növekvő sort alkotnak, ahol az N. elem a halmazban az N. sorszámot kapja.
· RANK() : A rank esetén a sorszámok monoton növekvő sort alkotnak. Abban az
esetben különbözik a row_number-től, ha a rendezési reláció szerint a halmaz
tartalmaz azonos sorokat. Ilyen esetben, ha az N. és az N+1. elem a rendezési
reláció szerint egyenlő, akkor az N. és az N+1. elem is N. sorszámot kapja,
azonban a rendezési reláció szerinti következ N+2. eltérő elem az N+2.
sorszámot kapja.

· DENSE_RANK() : A dense_rank esetén a sorszámok monoton növekvő sort
alkotnak. Ez is abban az esetben különbözik a row_number-től, ha a rendezési
reláció szerint a halmaz tartalmaz azonos sorokat. Abban különbözik a rank
függvénytől, hogy itt a rendezési reláció szerinti következő N+2. eltérő elem az
N+1. sorszámot kapja.
Példa a ROW_NOMBER, RANK és DENSE_RANK függvények használatára:

A három függvény segítségével rangsoroljuk a dolgozókat, a fizetésük szerinti
rendezettségük alapján.

SELECT row_number() OVER(ORDER BY dolg.fizetés) row_number,
rank() OVER(ORDER BY dolg.fizetés) rank,
dense_rank() OVER(ORDER BY dolg.fizetés) dense_rank,
dolg.név,
dolg.fizetés
FROM aa dolg;
Amennyiben ugyan ezt a rangsorolást a telephelyen belül szeretnénk megtenni, úgy
használnunk kell a partition by záradékot.

SELECT row_number() OVER(PARTITION BY dolg.telep ORDER BY dolg.fizetés) row_number,
rank() OVER(PARTITION BY dolg.telep ORDER BY dolg.fizetés) rank,
dense_rank() OVER(PARTITION BY dolg.telep ORDER BY dolg.fizetés) dense_rank,
dolg.név,
dolg.fizetés,
dolg.telep
FROM aa dolg;

4.2.2 Futtatási tervek a DENSE_RANK használata esetén
A dense_rank függvény jól használható a következő probléma megoldásához. Adjuk
vissza azokat a szolgáltatásokat, amelyeket azon a napon rögzítettek, amikor az utolsó
rögzítés történt. Az általános megoldás a következőképpen néz ki.

SELECT ID
FROM service s1
WHERE TRUNC(s1.rec_time) = (SELECT MAX(TRUNC(s2.rec_time))
FROM service s2);
Ha megnézzük a végrehajtási tervet, jól látszik, ami a lekérdezésből várható, hogy a
service tábla kétszer is végig lesz olvasva teljesen. Egyszer, mikor kiválasztjuk a
maximumát a rögzítési időknek, majd még egyszer mikor kiválasztjuk a maximum alapján
az aznapi rögzítéseket.

Nézzük hogyan oldható meg ez a probléma a DENSE_RANK használatával.
SELECT ID
FROM (SELECT ID,
DENSE_RANK() OVER(ORDER BY TRUNC(s.rec_time) DESC NULLS LAST) rnk
FROM service s)
WHERE rnk = 1;
Ha most is megnézzük a végrehajtási tervet látjuk, hogy eltűnt az egyik TABLE ACCESS
FULL sor.

Természetesen a table access full egy index elhelyezésével elkerülhető, azonban a
hangsúly az egyszeri végrehajtáson van, hisz a lekérdezés nem mindig ilyen egyszerű,
hiszen a service tábla helyett használhatnánk akár egy nézetet is ami 10 tábla
összekapcsolásából áll elő. Ebben az esetben már nem mindegy hogy hányszor olvassuk
végig a táblát.

A fentebbi példá kipróbáltam egy 3 470 680 sort tartalmazó táblán. A rec_time oszlopon
nem volt index.

A futási idő az első megoldás esetén 10,657 sec.
A futási idő a második megoldás esetén 8,938 sec.
4.3 A FIRST_VALUE és a LAST_VALUE függvények

A first_value és a last_value függvények a képzett csoport meghatározott sorrendjének
első illetve utolsó rekordjának megfogására szolgál.

Szintaxisa a következő:
FIRST_VALUE(<sql_kifejezés>) OVER ([PARTITION BY <…>] [ORDER BY <….>[<ablak_definíció>]])
LAST_VALUE(<sql_kifejezés>) OVER ([PARTITION BY <…>] [ORDER BY <….>[<ablak_definíció>]])

Példa a FIRST_VALUE használatára

Adott a következő probléma. Írassuk ki telephelyenként a dolgozók fizetésének a
telephely legkisebb fizetésétől való eltérését.
SELECT t.név “Név”,
t.telep “Telephely”,
t.fizetés “Fizetés”,
t.first_v “Legkisebb fiz.”,
t.fizetés – t.first_v “Eltérés”
FROM (
SELECT dolg.név,
dolg.telep,
dolg.fizetés,
first_value(dolg.fizetés) OVER(PARTITION BY dolg.telep ORDER BY dolg.fizetés) first_v
FROM aa dolg )t;
4.4 A KEEP FIRST és a KEEP LAST kulcsszavak
Ezen két analitikus függvény elég speciális és valószínűleg használatuk sem lesz túl
gyakori, azonban szükség esetén rengeteg fáradtságtól megkímélheti a programozót. A
függvények szintaxisa is eltér az általános formától.

A szintaxis a következő:

Függvénynév() KEEP (DENSE_RANK FIRST ORDER BY <sql_kif>) OVER ([PARTITION BY <…>])
Függvénynév() KEEP (DENSE_RANK LAST ORDER BY <sql_kif>) OVER ([PARTITION BY <…>])
Látható, hogy az order by záradék kikerült az over() részből és átkerült a FIRST vagy a
LAST kulcsszó után. Az over() rész csupán a partition by záradékot tartalmazza. A
DENSE_RANK pedig jelen esetben egy kulcsszó nem a már előzőekben tárgyalt
függvény, még sem véletlen a hasonlóság.

A függvény a következőképpen működik. A partition by záradék által meghatározott
csoportot rangsorolja a dense_rank-nál leírt szabályok alapján, majd a rangsorolás
szerinti első (FIRST esetén) vagy utolsó (LAST esetén) rangsorba eső rekordokon
végrehajtja az aggregáló függvényt.

Példa a KEEP FIRST használatára :
Adott a következő probléma. Írassuk ki minden dolgozóhoz a telephelyén dolgozó
legalacsonyabb szintű dolgozók átlagfizetését.

SELECT dolg.név,
dolg.telep,
dolg.szint,
dolg.fizetés,
AVG(dolg.fizetés) KEEP (DENSE_RANK FIRST ORDER BY dolg.szint)
OVER (PARTITION BY dolg.telep) Avg_fiz_szint
FROM aa dolg
ORDER BY dolg.telep, dolg.szint
4.5 A ratio_to_report függvény

A függvény szintaxisa a következő:

RATIO_TO_REPORT(<sql_kifejezés>) OVER ([PARTITION BY <…>])
A ratio_to_report függvény egy halmaz minden elemére megmondja, hogy azok hány
százaléka a halmaz elemeinek összegének. A halmaz méretét a partition by
záradékkal tudjuk szabályozni, a halmaz értékeit pedig paraméterül kapja a függvény.
A függvény nem támogatja az order by záradékot és az ablak definíciót sem.

Példa a ratio_to_report függvény használatára:
SELECT d.*,
ratio_to_report(d.a2) over (partition by d.a1) as ratio_to_report
FROM test d

 

4.6 A NTILE függvény

A függvény szintaxisa a következő:
NTILE(kosarak száma) OVER ([PARTITION BY <…>] ORDER BY<…>)
Az NTILE függvény a rendezettséget alapul véve a partíció elemeit a paramétereként
kapott számú kosárba osztja szét. Amennyiben a partíción belül a sorok száma nem
többszöröse a kosarak számának, úgy mindig az alacsonyabb sorszámú kosarak
kerülnek először feltöltésre.

Példa az NTILE függvény használatára:
A példa a dolgozókat osztja szét telephelyenként 3 kosárba, a dolgozók nevének
sorrendje alapján.
SELECT dolg.név “Név”,
dolg.telep “Telephely”,
NTILE(3) OVER (PARTITION BY dolg.telep ORDER BY dolg.név) “Kosár”
FROM aa dolg
Az eredményben látható, hogy a Debreceni partícióban az egyes kosárban került az
osztást követően kimaradt egy elem.
4.7 A PARTITION BY záradék az aggregációs függvények után
Bármelyik csoportosító függvény után használhatjuk a partition by záradékot. A partition
by segítségével a csoportosító függvényünket végrehajthatjuk a csoportosítás egy
részcsoportján.

A partition by szintaxisa a következő:
{SUM | AVG | MAX | MIN | COUNT | … } OVER ( [PARTITION BY sql_kif1[,…]] )

Példa a PARTITION BY záradék használatára:
Adott a következő feladat. Határozzuk meg, hogy a beosztások össz fizetése mennyivel
tér el az ugyan azon telephelyen található legmagasabb összfizetéssel rendelkező
beosztástól.
SELECT t.beosztás “Beosztás”,
t.telep “Telephely”,
t.sfiz “Össz. fizu”,
t.msfiz “Tel. beosz. max fizu”,
t.msfiz – t.sfiz “Eltérés”
FROM (SELECT dolg.beosztás,
dolg.telep,
sum(dolg.fizetés) sfiz,
MAX(SUM(dolg.fizetés)) OVER (PARTITION BY dolg.telep) msfiz
FROM aa dolg
GROUP BY dolg.beosztás, dolg.telep
ORDER BY dolg.telep) t
Példa a ROW típusú ablakdefiníció használatára:
Nézzünk néhány példát arra, hogy a mit kapunk eredményül az egyes esetekben, ha
a telephelyet választjuk partíciónak és a fizetés a sorrend. Sajnos a példa nem túl
életszerű, de a cél az egyszerűségen és a követhetőségen volt.

SELECT dolg.név “Név”,
dolg.telep “Telephely”,
dolg.fizetés “Fizetés”,
COUNT(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
ROWS BETWEEN 3 PRECEDING
AND 1 FOLLOWING) “Előz 3 – Köv 1”,
COUNT(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) “Előz – Aktuális”,
COUNT(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
ROWS BETWEEN 3 PRECEDING
AND 1 PRECEDING) “Előz 2 – Előz 1”,
COUNT(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
ROWS BETWEEN 1 FOLLOWING
AND 3 FOLLOWING) “Előz 1 – Köv 3”
FROM aa dolg
ORDER BY dolg.telep, dolg.fizetés
Példa a RANGE típusú ablakdefiníció használatára:
Ez a példa már egy kicsit életszerűbb. Határozzuk meg, hogy telephelyenként az
egyes dolgozók esetén, hány olyan dolgozó van, ahol a dolgozó fizetésének ötödével
többet vagy kevesebbet keresnek.

SELECT dolg.név “Név”,
dolg.telep “Telephely”,
dolg.fizetés “Fizetés”,
Count(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
RANGE BETWEEN UNBOUNDED PRECEDING
AND (dolg.fizetés/5) PRECEDING) “Kevesebb az ötödével”,
COUNT(*) OVER (PARTITION BY dolg.telep ORDER BY dolg.fizetés
RANGE BETWEEN (dolg.fizetés/5) FOLLOWING
AND UNBOUNDED FOLLOWING) “Több az ötödével”
FROM aa dolg
5 EGYÉB HASZNOS FÜGGVÉNYEK
Az analitikus függvényeken túl az Oracle biztosít még több hasznos függvényt,
melyek rangsorolnak vagy csoportosítási műveletekhez kapcsolódnak. Nézzünk ezek
közül is egy kettőt.

5.1 A LISTAGG függvény
A függvény szintaxisí a következő:
LISTAGG(sql_kif,elválasztó_kar) WITHIN GROUP (ORDER BY<…>) [OVER (PARTITION BY <…>)]

A Listagg függvény ugyan nem egy hagyományos értelemben vett analitikus
függvény, azonban használata nagyban hasonlít azokra és emellett sokszor nagyon
hasznos.

Mint már fentebb említettem, alap esetben egy group by záradékkal rendelkező
lekérdezés select ágán nem szerepelhet aggregációs függvény nélkül olyan oszlop, amely
nem szerepel a group by záradékban. A listagg függvény tulajdonképpen ezt oldja fel úgy,
hogy a paramétereként kapott oszlop csoportban szereplő értékeit egy elválasztó
karaktersorozatot használva összefűzi.

Két paramétere van:
· sql_kifejezés: ez egy olyan oszlopdefiníció, amely nem szerepel a group by
záradékban.
· elválasztó karaktersorozat: ezzel a karaktersorozattal lesz elválasztva a csoport
minden eleme az összefűzés során.

A függvényt a WITHIN GROUP kulcsszavak követik, majd zárójelben egy rendezési
relációt kell megadni, mely szerint az oszlop értékei rendezve lesznek a felsoroláson
belül.

Példa a LISTAGG függvény használatára:
SELECT dolg.fizetés “Fizetés”,
listagg(dolg.név,’, ‘) WITHIN GROUP (ORDER BY dolg.név) “Nevek”
FROM aa dolg
GROUP BY dolg.fizetés;
5.2 A WIDTH_BUCKET függvény
A függvény szintaxisa a következő:
WIDTH_BUCKET(sql_kif, alsó_határ, felső_határ, zsákok_száma)
A WIDTH_BUCKET egy hisztogramm függvény, megy egy kiértékelt kifejezés értékeit
szétosztja egy egyenlő részekre felosztott intervallumon.

Négy paramétere van:
· kifejezés: Ez a kifejezés adja az értéket melyet az intervallumon el kell helyezni.
· alsó határ: Az intervallum kezdete.
· felső határ: Az intervallum vége.
· zsákok száma: Hány részre osszuk fel az intervallumot.

Az első három paraméter lehet numerikus és dátum típusú. Más típus nem megengedett.
Az utolsó paraméternek egy pozitív egész számnak kell lenni.Ezek után az alsó és felső
határ közé eső intervallumot felosztjuk a zsákok számával. Ha az intervallumunk 1 –
20000 közé esik és a zsákok száma 4, akkor a felosztás a következőképpen fog kinézni.
Ez alapján lesz az első paraméterként megadott kifejezés elhelyezve valamelyik zsákba.
Az intervallum [0,5000) halmazokra van felosztva. Az ábrán látszik, hogy van egy 0-ás és
egy 5-ös zsák is. Értelemszerűen a 0-ás zsákba kerülnek azok az értékek, melyek
kisebbek az intervallum alsó határánál és az 5-ös zsákba kerülnek azok az értékek,
melyek nagyobbak az intervallum felső határánál.
Példa a WIDTH_BUCKET függvény használatára:
Tegyük fel, hogy a dolgozóinkat szeretnénk beosztani 4 csoportba a fizetésük szerint és
tudjuk, hogy a legalacsonyabb fizetés 60 000 Ft a legmagasabb pedig 130 000 Ft.
SELECT dolg.név “Név”,
dolg.fizetés “Fizetés”,
WIDTH_BUCKET(dolg.fizetés,60000,130000,4) “Csoport”
FROM aa dolg;
A lekérdezés eredménye a következő:
A példából látszik, hogy a balról zárt jobbról nyílt halmaz miatt Attila már felülcsordul és az
5-ös zsákba kerül.

 

 

 

A bejegyzés kategóriája: Informatika, Más, Oktatás
Kiemelt szavak: , , .
Közvetlen link.