PLSQL alapok Adatbziskezels Jellsek n n n Az

PL/SQL alapok Adatbázis-kezelés

Jelölések n n n Az SQL, PL/SQL utasítások, nevek stb. (terminálisok) nagybetűvel szerepelnek, pl. : SELECT. A nemterminálisok kisbetűvel, pl. : eredmeny. Az alternatívákat függőleges vonal választja el egymástól ( | ). Az opcionális elemek szögletes zárójelben állnak ( [ ] ), pl. : [NOT NULL]. A kötelezően megadandó alternatívákat kapcsos zárójelek fogják közre ( { } ), pl. : {kifejezes | feltetel}. Az iteráció jelölésére három pont szolgál (. . . ), pl. : utasitas [utasitas]. . .

A példákhoz használt adatbázis n n n n automata (azon, penz, reszl_azon, varos) ugyfel (azon, nev, szuletes, keret) szamla (azon, ugyf_azon, reszl_azon, osszeg, lejarat) reszleg (azon, varos, tartalek) tranzakcio (azon, aut_azon, szaml_azon, ugyf_azon, datum, osszeg) Itt az automata tábla reszl_azon attribútuma azt adja meg, hogy melyik bank részleghez tartozik az adott automata, a penz pedig az automatán rendelkezésre álló pénzösszeget tárolja. A tranzakcio tábla osszeg attribútumának egy-egy értéke, ha negatív előjelű, pénzlevételt, ha pozitív, számlafeltöltést jelöl. A példa adatbázisban a Zseton Bank részlegeiről van szó.

Blokk n n A PL/SQL alapvető programegysége a blokk. Bárhol megjelenhet, ahol végrehajtható utasítás állhat. Felépítése: [DECLARE deklaraciok] BEGIN utasitas [utasitas]. . . [EXCEPTION kivetelkezelo] END; n Megjegyzés: A címkék és GOTO utasítás használatához lásd Oracle dokumentáció.

Összetett típus, rekord n n n Az összetett típust mindig felhasználói adattípusként kell létrehozni. A rekord heterogén összetett típus, ezzel szemben az index alapú tábla, beágyazott tábla, dinamikus tömb minden eleme azonos típusú. Rekord szintaxisa: TYPE nev IS RECORD ( mezonev tipus [[NOT NULL] {: = | DEFAULT} kifejezes] [, mezonev tipus [[NOT NULL] {: = | DEFAULT} kifejezes]]…); q q q A mezonev a rekord mezőinek neve. A típus REF CURSOR kivételével bármi lehet. Ha egy NOT NULL mező NULL értéket kap VALUE_ERROR kivétel váltódik ki.

Példa rekordra n Példa: DECLARE TYPE film IS RECORD ( cim VARCHAR 2(20) NOT NULL, datum DATE DEFAULT SYSDATE, hossz NUMBER, studio VARCHAR 2(15));

%TYPE, %ROWTYPE n %TYPE: egy korábban már deklarált kurzorváltozó, mező, rekord, adatbázistábla oszlop vagy változó típusát veszi át és ezzel a típussal deklarálja a változót. Tábla oszlopának típusa is definiálható így egy korábban megadott táblát használva. n %ROWTYPE: olyan rekordtípust szolgáltat, amely egy adatbázistábla sort vagy kurzor által kezelt sort reprezentál. n Mindkét esetben, ha a hivatkozott objektumban változás lép fel, ezt a %TYPE, %ROWTYPE segítségével deklarált változó is követi.

%TYPE, %ROWTYPE példa CREATE TABLE pelda AS ( nev VARCHAR 2(20), id CHAR(3) NOT NULL); DECLARE sor pelda%ROWTYPE, valtozo pelda. id%TYPE, …

Feltételes kifejezés n A legtöbb kifejezés az SQL tárgyalásánál szerepelt. n Feltételes kifejezés alakja: CASE szelektor WHEN kifejezes THEN eredmeny [WHEN kifejezes THEN eredmeny]. . . [ELSE eredmeny] END

Példa feltételes kifejezésre DECLARE datum DATE : = DATE '1983 -05 -29'; evszak VARCHAR 2(6); BEGIN evszak : = CASE WHEN TO_CHAR(datum, 'MM') IN ('01', '02', '12') THEN 'tel' WHEN TO_CHAR(datum, 'MM') IN ('03', '04', '05') THEN 'tavasz' WHEN TO_CHAR(datum, 'MM') IN ('06', '07', '08') THEN 'nyar' WHEN TO_CHAR(datum, 'MM') IN ('09', '10', '11') THEN 'osz' ELSE 'Hiba' END; DBMS_OUTPUT. PUT_LINE (evszak); END;

Üres utasítás, feltételes utasítás n n n Üres utasítás használható általában mindenütt, ahol végrehajtható utasítás állhat. Alkalmazásakor a vezérlés a következő utasításra kerül. Használható pl. rekurzív függvények „zárásaként”. Alakja: NULL; Feltételes utasítás alakja: IF feltetel THEN utasitas [utasitas]. . [ELSIF feltetel THEN utasitas [utasitas]. . . [ELSE utasitas [utasitas]. . ] END IF;

Példa feltételes utasításra DECLARE szam NUMBER : = -5; absz NUMBER; BEGIN IF szam >= 0 THEN absz : = szam; ELSE absz: = -1 * szam; END IF; DBMS_OUTPUT. PUT_LINE (szam || ' abszolút értéke: '|| absz); END;

CASE utasítás n A CASE utasítás alakja: CASE [szelektor_kifejezes] WHEN {kifejezes | feltetel} THEN utasitas [utasitas]. . . [WHEN {kifejezes | feltetel} THEN utasitas [utasitas]. . . [ELSE utasitas [utasitas]. . . ] END CASE;

Példa CASE utasításra DECLARE osztalyzat NUMBER(1) : = 4; BEGIN CASE osztalyzat WHEN 1 THEN DBMS_OUTPUT. PUT_LINE('elégtelen'); WHEN 2 THEN DBMS_OUTPUT. PUT_LINE('elégséges'); WHEN 3 THEN DBMS_OUTPUT. PUT_LINE('közepes'); WHEN 4 THEN DBMS_OUTPUT. PUT_LINE('jó'); WHEN 5 THEN DBMS_OUTPUT. PUT_LINE('jeles'); ELSE DBMS_OUTPUT. PUT_LINE('Nincs ilyen jegy. '); END CASE; END;

Alapciklus n n Alakja: LOOP utasitas [utasitas]. . . END LOOP; Példa: DECLARE fakt NUMBER: =1; i PLS_INTEGER: =2; BEGIN LOOP fakt: =fakt*i; EXIT WHEN fakt > 1000; i: =i+1; END LOOP; DBMS_OUTPUT. PUT_LINE('A faktorialis: ' || fakt); END;

WHILE ciklus n Alakja: WHILE feltétel LOOP utasitas [utasitas]. . . END LOOP; n Példa: DECLARE ossz NUMBER : =1; BEGIN WHILE ossz < 10000 LOOP ossz : = 2*ossz; END LOOP; DBMS_OUTPUT. PUT_LINE (ossz); END;

FOR ciklus n Alakja: FOR ciklusvaltozo IN [REVERSE] also_hatar. . felso_hatar LOOP utasitas [utasitas]. . . END LOOP; n A ciklusvaltozo PLS_INTEGER típusú változó lesz, hatásköre a ciklusmag. Rendre felveszi az also_hatar, felso_hatar egészértékű kifejezések által definiált tartomány minden értékét. n Az EXIT utasítással itt is bármikor kiléphetünk a ciklusból.

Példa FOR ciklusra DECLARE ossz NUMBER : = 0; BEGIN FOR i in 1. . 100 LOOP ossz : = ossz + i; END LOOP; DBMS_OUTPUT. PUT_LINE('Az első száz szám összege: ' || ossz); END;

A SELECT INTO utasítás I. n Példa I. : DECLARE reszl_varos reszleg. varos%TYPE; BEGIN SELECT varos INTO reszl_varos FROM reszleg WHERE azon = 'B 01'; DBMS_OUTPUT. PUT_LINE('A varos: ' || reszl_varos); END;

A SELECT INTO utasítás II. n Példa II. : DECLARE sor reszleg%ROWTYPE; BEGIN SELECT * INTO sor FROM reszleg WHERE azon = 'B 01'; END; n n A SELECT, FROM záradékok ugyanúgy viselkednek, mint a „normális” SQL lekérdezések esetében. Gondoskodni kell, hogy a lekérdezés végeredménye megfeleljen az INTO záradékban megadott változó, rekord típusának. A lekérdezés nem adhat vissza több sort végeredményként ebben az esetben.

Tranzakció n n n A logikailag egybetartozó utasításokat általában egy munkamenetként kezelik. Az utasításoknak vagy mindegyike végrehajtódik vagy egyik sem. Az ilyen munkameneteket tranzakciónak nevezzük. Egy-egy adatbáziskezelő általában több párhuzamos tranzakciót kezel egyszerre. Nemzetközi példák: helyfoglalás egy-egy repülőgép járatra, bankjegyautomaták. Kívánalmak. q q Atomosság: egy tranzakció vagy teljes egészében végrehajtódik vagy egyik része sem hajtódik végre; Konzisztencia: minden adatbázisban adottak bizonyos konzisztenciafeltételek. Pl. egy járat ugyanazon helyét nem foglalhatja le ugyanaz a két utas. A tranzakciók befejeződése után az adatbázisnak konzisztensnek kell lennie. Elkülönítés: a tranzakciók konkurencia-vezérlését úgy kell megoldani, mintha a tranzakciók egymás után futnának. Tartósság: ha egy tranzakció befejezte működését, annak eredménye nem veszhet el azonnali rendszerhiba esetén sem.

Zárak, visszagörgetés n n n A zár átmeneti tulajdonosi jogkört biztosít egy felhasználónak egyegy adatbázisbeli objektum: pl. tábla, tábla bizonyos sorai fölött. Más felhasználó nem módosíthatja az adatokat mindaddig, amíg a zárolás fennáll. Ha F 1 felhasználó egy táblát éppen lekérdez, F 2 ugyanazt módosítja, akkor az Oracle egy nézetet hoz létre F 1 számára, F 2 módosításai pedig a tábla egy pillanatfelvételén történnek meg. Az Oracle egy visszagörgető szegmenst használ a módosítások tárolására és az olvasási konzisztencia biztosítására. Sikeres végrehajtás esetén a visszagörgető szegmens információi átkerülnek az adatbázisba, véglegesítődnek. Ha hiba történik, a tranzakció „visszagörgetődik”, az adatbázis tranzakció előtti állapota nem változik meg.

COMMIT, SAVEPOINT, ROLLBACK n n n n Tranzakció véglegesítése: COMMIT [WORK] Megjegyzés: a WORK alapszó csak a jobb olvashatóságot biztosíthatja. A mentési pontok egy tranzakció részleges visszagörgetését szolgálhatják: SAVEPOINT nev; Visszagörgetés: ROLLBACK [WORK] [TO [SAVEPOINT] mentesi_pont]; Az egyszerű ROLLBACK érvényteleníti a teljes tranzakció hatását, oldja a zárakat, törli a mentési pontokat. A TO utasítással rendelkező ROLLBACK a megadott mentési pontig görgeti vissza a tranzakciót. A mentési pont után a zárak feloldásra kerülnek, a tranzakció a mentési ponttól folytatódik. Megjegyzés: az INSERT, DELETE, UPDATE utasítások előtt az Oracle egy implicit mentési pontot helyez. Ha az utasítás sikertelen, visszagörgetés történik. Kezeletlen kivétel esetén a gazdakörnyezet dönt. Emiatt érdemes explicit módon elhelyezni a fenti utasításokat.

Feladatok 1. Írassuk ki a „Szeretlek világ!” üzenetet. 2. Írassuk ki Kismiska születési dátumát. 3. 4. 5. 6. Írassuk ki Malvinka azonosítóját és születési dátumát. Írassuk ki, hogy hány tranzakció történt 2007. április 27 -én. Írassuk ki a legkisebb, 10000 -nél nagyobb, 7 -tel osztható számot. Írassuk ki a 100 -nál kisebb prímszámokat.