Az SQL nyelv

Az SQL nyelv

Bevezetés

Rövid Történet

1974-75-ben kezdték a kifejlesztését az IBM-nél, az "eredeti" neve SEQUEL (Structured English QUEry Language);

1979-től több cég (pl. IBM, ORACLE Corp.) kereskedelmi forgalomban kapható termékeiben:

1987-től ANSI szabvány.

Jelentősége

Szabvány, amelyet jelenleg csaknem minden relációs adatbáziskezelő alkalmaz (kisebb-nagyobb módosításokkal);

Tömör, felhasználó közeli nyelv, alkalmas hálózatokon adatbáziskezelő szerver és kliensek közötti kommunikációra;

Nem procedurális programozási nyelv (legalábbis a lekérdezéseknél).

A példákban szereplő táblák

A leírás az ORACLE adatbáziskezelő SQL dialektuság ismerteti, ez többé- kevésbé megfelel az egyéb termékekben található nyelv variációknak. A nyelv termék- illetve hardverspecifikus elemeit nem, vagy csak futólag ismertetjük.

Az utasítások ismertetésénél a következő táblákat használjuk:

EMP Alkalmazottak adatainak tárolására

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80	800		20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB81	1250	500	30
7566	JONES	MANAGER	7839	02-APR81	2975		20
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-MAY-81	2850		30
7782	CLARK	MANAGER	7839	09-JUN-81	2450		10
7788	SCOTT	ANALYST	7566	21-JUL-85	3000		20
7839	KING	PRESIDEN		17-NOV-81	5000		10
7844	TURNER	SALESMAN	7698	08-SEP-81	1500		30
7876	ADAMS	CLERK	7788	24-AUG-85	1100		20
7900	JAMES	CLERK	7698	03-DEC-81	950		30
7902	FORD	ANALYST	7566	03-DEC-81	3000		20
7934	MILLER	CLERK	7782	23-JAN-82	1300		10

DEPT A cég részlegeinek adataival:

DEPTNO

DNAME

LOC

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAL

30

SALES

CHICAGO

40

OPERATION

BOSTON

Az utasítások csoportjai

A nyelv utasításait a következő csoportokra oszthatjuk:

Adatleíró nyelv DDL Data Definition Language (create)

Adatmódosító nyelv DML Data Manipulation Language (select, insert, delete, update)

Adatelérést vezérlő nyelv DCL Data Control Language (grant, revoke)

A nyelvben a szöveg literálok kivételével a kis- és nagybetűket nem különböztetjük meg. A megadott példáknál a könnyebb érthetőség miatt a nyelv alapszavait csupa nagy betűvel, míg a programozó saját neveit kis betűkkel írjuk. A parancsok több sorba is átnyúlnak, a sorokra tördelésnek nincs szemantikai jelentősége. Az SQL parancsokat a pontosvessző zárja le.

Lekérdezések

A lekérdezések általános szintaxisa

SELECT <jellemzők>

FROM <tábla(k)>

[WHERE <logikai kifejezés(ek)>]

[GROUP BY <csoportosító(k)>]

[HAVING < logikai kifejezés(ek)>]

[ORDER BY <rendezés(ek)>];

A lekérdezés művelete eredményül egy újabb táblát állít elő persze lehet, hogy az eredmény táblának csak 1 oszlopa és 1 sora lesz. Az eredmény tábla a lekérdezés után megjelenik vagy a tábla felhasználható pl. az adatmódosító utasításokban.

A <jellemzők> definiálják az eredmény tábla oszlopait, <táblák> adják meg a lekérdezésben résztvevő táblák nevét, a <logikai kifejezés> segítségével válogathatunk az eredmény sorai között. A <csoportosítás> az eredmény tábla sorait rendezi egymás mellé, illetve a <rendezés> a megjelenő sorok sorrendjét határozza meg.

Nézzük meg, hogy a lekérdezés műveletével hogyan lehet megvalósítani a relációs algebra alapműveleteit.

Vetítés (projection)

A vetítés művelete egy táblából adott oszlopokat válogat ki. A <jellemzők> között kell felsorolni a kívánt oszlopokat. Például:

Az alkalmazottak neve és fizetése:

SELECT ename, sal FROM emp;

Minden oszlop kiválasztása:

SELECT * FORM emp;

A <jellemzők> közé nem csak a FORM mögött megadott tábla oszlopainak nevét lehet megadni, hanem használhatjuk az SQL beépített műveleteit, pl. egyszerű aritmetikai kifejezéseket új érték előállítására.

A dolgozók egész éves fizetése:

SELECT ename, 12*sal FROM emp;

Amennyiben a dolgozó által kézhez kapott teljes összeget fizetést és prémiumot együtt akarjuk megkapni, hasonlóan járhatunk el. Az jelent csak problémát, hogy a comm érték nincs mindenhol kitöltve, az üres mezőket nem tudjuk hozzáadni a fizetéshez(az üres nem 0)! Ilyenkor használhatjuk az NVL(oszlop, érték) függvényt, amely az üres(NULL) mező helyett a megadott értéket adja vissza.

A dolgozó által felvett pénz:

SELECT ename, sal + NVL(comm, 0) FROM emp;

A kiválasztott oszlopokat tartalmazó táblákban lehetnek azonos sorok, ami ellentmond a relációs táblák egyik alapvető követelményének. Ennek ellenére a SELECT utasítás nem szűri ki automatikusan az azonos sorokat mert ez túlságosan időigényes művelet. A programozónak kell tudnia, hogy az előállított táblában lehetnek-e, zavarnak-e ilyen sorok. Ha kell, a következőképpen szűrhetjük ki ezeket:

Az összes különböző munka megnevezése:

SELECT DISTINCT job FROM emp;

Kizárás (restriction)

A kizárás műveletnél a tábla sorai közül válogatunk. A <logikai kifejezés> igaz értékeinek megfelelő sorok kerülnek be az eredmény táblába.

A 2000 dollárnál több fizetésű dolgozók:

SELECT ename, sal FROM emp WHERE sal > 2000;

Vizsgáljuk meg a logikai kifejezések szerkezetét! A kifejezések elemi összetevői.

Literálok különböző típusú értékekre: számok, szöveg, dátum; oszlopok nevei.

A fenti elemekből elemi adatműveletekkel képzet kifejezések:

Számoknál: aritmetikai műveletek, aritmetikai függvények;

Szövegnél: SUBSTR(), INSTR(), UPPER(), LOWER(), SOUNDEX(),;

Dátumoknál: +,-, konverziók;

Halmazok: pl.: (10, 20, 30);

Zárójelek között egy teljes SELECT utasítás (ld. később).

A fenti műveletekkel képzett adatokból logikai értéket következő műveletekkel állíthatunk elő:

Relációk: <, <=, =, !=, >=, >;

Intervallumba tartozás: BETWEEN..AND..;

NULL érték vizsgálata: IS NULL, IS NOT NULL;

Halmaz eleme: IN < halmaz >;

Szöveg vizsgálat: LIKE <minta>, ahol: % a tetszőleges karaktersorozat, _ a tetszőleges karakter jelzése;

Végül a logikai értékeket a zárójelezéssel illetve az AND, OR és NOT műveletekkel lehet tovább kombinálni.

Például:

A 82..89-es években felvett dolgozók:

SELECT ename, hiredate FROM emp

WHERE hiredate BETWEEN 01-JAN-82 AND 31-DEC-89;

2000 dollárnál kevesebbet kereső és prémiumot nem kapó dolgozók:

SELECT ename, sal FROM emp

WHERE sal < 2000 AND comm IS NULL;

Összekapcsolás (join)

A természetes összekapcsolás műveleténél 2 vagy több tábla soraiból hozunk össze egy-egy új rekordot akkor, ha a kér sor egy-egy mezőjének értéke megegyezik. A SELECT kifejezésben a <táblák> -ban kell megadni az érintett táblák neveit, a WHERE mögötti logikai kifejezés definiálja azokat az oszlopokat, amely értékei szerint történik meg az összekapcsolás.

Az egyes osztályok neve székhelye és a hozzájuk tartozó dolgozók:

SELECT dept.dname, dept.loc, emp.ename

FORM dept,emp

WHERE dept. Deptno = emp.deptno;

Látható, hogy mivel mindkét felhasznált táblában azonos az összekapcsolás megvalósító oszlop neve, a WHERE -t követő logikai kifejezésben az oszlop neve mellé meg kell adni a tábla nevét is. Hasonló helyzet előfordulhat a SELECT-et követő <jellemzők> között is.

Ha megvizsgáljuk a fenti példában kapott eredményt, láthatjuk, hogy 40-es osztály nem szerepel a listában. Ennek az az oka, hogy az osztálynak nincs egyetlen dolgozója sem, tehát az egyesítésnél nem találtunk az emp táblában egyetlen olyan sort sem, amelyet ehhez az osztályhoz kapcsolhattunk volna Ez lehet Kívánatos eredmény, azonban az SQL-ben lehetőség van arra is, hogy ezeket a sorokat is egyesítsük, azaz az egyesítésben az emp táblához hozzáképzeljünk egy üres sort is. Ezt külső egyesítésnek hívjuk. A módosított példa a következőképpen néz ki:

SELECT dept.dname, dept.loc, emp.ename

FROM dept, emp

WHERE dept.deptno = emp.deptno (+);

A (+) jel azt jelzi azt a táblát, amelyikhez az egyesítés előtt az üres mezőket tartozó sort hozzá kel venni.

Az egyesítésnél lehet ugyanarra a táblára többször is hivatkozni. Pl.:

Azon dolgozók, akik többet keresnek a főnöküknél:

SELECT e.ename, e.sal, m.ename, m.sal

FROM emp e, emp m

WHERE e.mgr = m.empno AND e.sal > m.sal;

A fenti példában ugyanazt a táblát kétszer is használjuk, a két tábla oszlopainak megkülönböztetésére a táblákat a FROM részében lokális névvel látjuk el. Lokális neveket természetesen különböző táblák esetén is használhatunk.

Látható, hogy az egyesítés mellett egyidejűleg más logikai kifejezéseket is használhatunk. A fizetések fenti vizsgálatát felfoghatjuk úgy is, mint a természetes egyesítés műveletének általánosított esetét, ahol nem csak az egyenlőség művelete használható(ami megengedett), valamint úgy is, hogy a már egyesített táblából zárjuk ki a fizetésekre szabott követelményeknek meg nem felelő sorokat. Az SQL programozónak általában fogalmi sincs, hogyan hajtódik végre a fenti kifejezés. Itt fedezhető fel a nyelv nem algoritmus jellege.

Oszlopfüggvények

A lekérdezés eredményeként előálló táblák egyes oszlopaiban lévő értékeken végrehajthatunk szokásos nyelven ciklussal kifejezhető műveleteket, amelyek egyetlen értéket állítanak elő.

Ilyenek:

AVG() átlag

SUM() összeg

COUNT() darabszám

MAX() maximális érték

MIN() minimális érték

Az üzletkötők átlagfizetése: SELECT AVG (sal) FROM amp WHERE job = SALESMAN;

Hány dolgozó van: SELECT COUNT(*) FROM emp;

Hány különböző beosztás van: SELECT COUNT(DISTINCT job) FROM emp;

Átlagos prémium: SELECT AVG(NVL(comm,0)) FROM emp;

Figyelem: az utolsó példa az NVL függvényt használva az összes dolgozóra átlagolja a kifizetett prémiumot, míg NVL nélkül csak azokra átlagolná, akik kaptak egyáltalán prémiumot. (Az oszlopfüggvények kiszámításánál a NULL értékű rekordok kimaradnak.)

Mivel az oszlopfüggvény eredménye egyetlen értéket állít elő, az oszlopfüggvény mellé vagy más oszlopfüggvényeket írhatunk, vagy olyan értéket írhatunk, amelyik az összes kiválasztott sorban azonos.

Például írhatnánk:

SELECT job, AVG(sal) FROM emp WHERE job =SELeSMAN;

SELECT COUNT (*), AVG(sal) FROM emp;

De hibás:

SELECT COUNT (*), ename FROM emp;

Egymásba ágyazott lekérdezések

A WHERE mögött álló logikai kifejezésben állhat egy teljes SELECT utasítás is. Például:

A nem New Yorkban dolgozók listája:

SELECT eneme FROM emp

WHERE deptno IN

(SELECT deptno FORM dept WHERE loc != NEW YORK;)

Azaz először kiválasztjuk azon osztályok azonosítóját, amelyek nem New York-ban vannak, majd azt vizsgáljuk, hogy az ezekből képzett halmazban található-e az adott dolgozó osztályának azonosítója. Mellesleg ugyanezt a listát megkaphatnánk az egyesítés műveletével is:

SELECT ename

FROM dept,emp

WHERE dept.deptno =emp.deptno

AND dept.loc != NEW YORK;

A beágyazott lekérdezés vagy egyetlen értéket azért mert egyetlen megfelelő sor egyetlen oszlopát választottuk ki, illetve oszlopfüggvényt használtunk -, vagy több értéket több sort állít elő. Az előbbi esetben a SELECT értékét az elemi értékekkel azonos módon használhatjuk. Több érték egy halmazt jelet, tehát halmazműveleteket használhatjuk. A korábban megismert IN() eleme művelet mellett használható az ANY() illetve az ALL() művelet, ahol a kívánt reláció a halmaz legalább egy, illetve valamennyi értékére igaz.

Legmagasabb fizetésű dolgozók(lehet több van!):

SELECT ename, sal FORM emp

WHERE sal >=ANY (SELECT sal FROM emp);

Illetve ugyanez a példa oszlopfüggvény felhasználásával:

SELECT ename, sal FORM emp

WHERE sal = (SELECT MAX(sal) FROM emp);

Csoportosítás

Az oszlopfüggvények a teljes kiválasztott táblára minden sorra lefutnak. Gyakran célszerű lenne a kiválasztott sorokat valamilyen szempont szerint csoportosítani és az oszlopfüggvényeket az egész tábla helyet ezekre a csoportokra alkalmazni.

Foglalkozásonkénti átlagfizetés:

SELECT job, AVG(SAL) FROM emp

GROUP BY job;

A fenti parancs az emp tábla sorait a job oszlop azonos értékei alapján csoportosítja. Természetesen az oszlopfüggvények a korábbi használatához hasonlóan a SELECT <jellemző> -k között csak a csoportosítás alapját képező oszlop neve illetve a csoportokra alkalmazott oszlopfüggvények szerepelhetnek.

A csoportosítás után az eredményből bizonyos csoportok kihagyhatók.

Foglalkozásonkénti átlagfizetés a 1000 és 3000 dollár közötti tartományban:

SELECT job, AVG(emp) FROM emp

GROUP BY job

HAVING AVG(sal) BETWEEN 1000 AND 3000;

A HAVING mögötti logikai kifejezésben természetesen csak egy- egy csoport közös jellemzőre vonatkozó értékek a csoportosítás alapját képező oszlop értéke, vagy oszlopfüggvények eredménye szerepelhet. Természetesen a csoportosítás előtt azért a WHERE feltételek használhatók. Célszerű gyorsabb - WHERE feltételeket használni mindenhol, ahol csak lehet, HAVING szerkezet csak akkor alkalmazni, mikor a teljes csoporttól függő értékeket akarjuk vizsgálni.

Rendezés

Az eddig tárgyalt lekérdezések eredményében a sorok véletlenszerű a programozó által nem megadható - sorrendben szerepeltek. A sorrendet lehet az ORDER BY által megadott rendezéssel szabályozni. A rendezés több oszlop értékeit szerint is történhet, ilyenkor az először megadott oszlop szerint rendezünk, majd az itt álló azonos értékek esetében használjuk a következőnek megadott oszlop(ok) értékét. Minden egyes oszlop esetében külön meg lehet adni a rendezés irányát, amely alap esetben emelkedő, de a DESC módosítóval csökkenő rendezés írható elő.

30-as osztály dolgozói(kicsit rendezve):

SELECT * FROM emp WHERE deptno =30

ORDER BY job, sal DESC;

Osztályok szerint átlagfizetés növekvő sorrendben:

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno

ORDER BY AVG(sal);

Egyéb nem részletezett műveletek

Az egyes lekérdezések által előállított táblák halmazként is felfoghatók, az SQL nyelv ezen táblák kombinálására tartalmaz szokásos halmazműveleteket is.

UNIO unió

INTERSECT metszet

MINUS különbség

A műveleteket két SELECT utasítás közé kell írni.

Relációs táblák segítségével le tudunk írni hierarchikus kapcsolatokat a különböző sorok között. Például az emp táblában az empno és az mgr oszlopok értékei alapján meg lehet konstruálni a vállalati hierarchiát. Erre a CONNECT BY PRIOR szerkezet szolgál.

Például:

SELECT ename, empno, job, deptno, mgr

FROM emp

CONNECT BY PRIOR empno = mgr

START WITH ename =KING

ORDER BY deptno;

Az utasítás kiírja a dolgozók fáját, az elnöktől kezdődően.

Nem tartoznak szorosan az SQL nyelvhez, de a legtöbb rendszer tartalmaz utasításokat, amelyekkel a lekérdezések által előállított táblázatok megjelenését pl. az oszlopok neveit, szélességét, adatformátumát, illesztését, tördelését- definiálhatjuk.

Táblák adatainak módosítása

A következő műveletek az adatbázis táblák adatainak módosítására szolgálnak.

Új sorokat egy meglévő táblába az

INSERT INTO <táblanév> [(<oszlopnév>,)]

VALUES (<kif. 1>,)

Illetve az:

INSERT INTO <táblanév> [(<oszlopnév>,)]

<SELECT >;

utasításokkal lehetséges. Míg az első szerkezet egyetlen sort, addig a második a lekérdezés által előállított összes sort beilleszti. (fegyelem: a táblákban az egye rekordok sorrendje tetszőleges, így a beillesztés sem feltétlenül a tábla végére történik.)

Amennyiben nem adtunk meg az oszlopok nevét, akkor a tábla deklarálásánál megadott sorrendben minden mezőnek értéket kell adni esetleg NULL-t - , ha viszont megadtuk egyes oszlopok neveit, akkor csak azoknak adunk értéket, mégpedig a felsorolásunk sorrendjében, a többi mező NULL értékű lesz.

Az adatbáziskezelő ellenőrzi, hogy az egyes mezőkbe ne kerülhessen a tábla definiziójával ellentétben NULL érték.

Sorokat törölni a

DELETE FROM <táblanév>

[WHERE <logikai kifejezés>];

paranccsal lehet. Ha a WHERE hiányzik, a tábla valamennyi sorát, egyébképnt alogikai kifejezés által kiválasztott sorokat töröljük.

Sorokban mezőket javítani az

UPDATE <táblanév>

SET <oszlopnév> = <kifejezés>,

[WHERE <logikai kifejezés>];

paranccsal lehet. Ha a WHERE hiányzik, a parancs valamennyi sorában módosít, egyébként csak a kiválasztott sorokban. Például:

az üzletkötőnek 20% fizetésemelés:

UPDATE emp SET sal = 1.2 * sal

WHERE job = SALESMAN;

A parancsban a kifejezés a szokásos operátorok és függvényeken felül egyes implementációk akár lekérdezést is megengednek.

Táblák, nézetek létrehozása

Táblák létrehozása

Új táblákat a

CREATE TABLE <táblanév> (<oszlopnév1><tipus1>[NOT NULL], );

paranccsal lehet létrehozni. A lehetséges adattípusok implementációkként változhatnak, általában a következő adattípusok megtalálgatók.

CHAR (n) max. n. karakter hosszú szöveg;

LONG (n) mint CHAR, de a hosszára általában nincs (nagyon nagy) felső korlát

NUMBER (w) Az előjellel együtt max. w karakter széles egész szám;

NUMBER (w,d) w a teljes szám, d a törtrész szélessége;

DATE dátum (és általában időpont)

Ha valamelyik oszlop definíciója tartalmazza a NOT NULL módosítót, a megfelelő mezőben mindig érték kell, hogy szerepeljen.

A felhasznált táblák definíciója a következők lehet:

CREATE TABLE emp

(empno NUMBER(4) NOT NULL,

ename CHAR(10),

job CHAR(3),

mgr NUMBER(4),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2) NOT NULL);

CREATE TABLE dept

(deptno NUMBER(2) NOT NULL

dname CHAR(14),

loc CHAR(13));

Nézet létrehozása

A nézetek olyan virtuális táblák, amelyek a fizikai táblákat felhasználva a tárolt adatok más és máslogikai modelljét, csoportosítását tükrözik. Nézetet a

CREATE VIEW <nézetnév> [(<oszlopnév1>, )] AS <lekérdezés>;

A lekérdezésre az egyedüli megkötés, hogy rendezést nem tartalmazhat. Amennyiben nem adunk meg oszlopneveket, a nézet oszlopai a SELECT után felsorolt oszlopok neveivel azonosak. Meg kell viszont adni az oszlopneveket, ha a SELECT számított értéket is előállít. Például:

CREATE VIEW dept_sal (deptno, avg_salary) AS

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno;

A nézetek a lekérdezésekben a táblákkal megegyező módon használhatók. Jelentőségük, hogy az adatok más modelljét fejezik ki, felhasználhatók a tárolt információ részeinek elrejtésére, pl. különböző felhasználók más nézeteken keresztül szemlélhetik az adatokat. A nézet általában csak olvasható, az adatmódosító műveletekben csak akkor szerepelhet, ha egyetlen táblából keletkezett és nem tartalmaz számított értékeket.

Index létrehozása

Az indexek a táblákban keresést gyorsítják meg. Létrehozásuk:

CREATE [UNIQUE] INDEX <indexnév> ON <táblanév> (<oszlopnév1> );

Az indexeket az adatbáziskezelő a táblák minden módosításánál felfrissíti. Amennyiben valamelyik indexet a UNIQUE kulcsszóval definiáltuk, a rendszer biztosítja, hogy az adott oszlopban minden mező egyedi értéket tartalmaz. Lehetséges több oszlopot egybefogó kombinált indexek létrehozására.

A lekérdezésekben nem jelenik meg, hogy a táblához tartozik-e index, az indexek a létrehozásuk után a felhasználó számára láthatatlanok, csak a lekérdezéseket gyorsítják. Indexeket azokra az oszlopokra érdemes definiálni, amelyek gyakran szerepelnek keresésekben. Szerencsés esetben az adattáblában nem kell egyáltalán keresni, a kívánt rekord az index alapján közvetlen kiválasztható. Pl. a

SELECT * FROM emp WHERE ename = JONES

Az emp táblában keresés nélkül kiválasztja a JONES rekordját, ha az ename oszlopra definiáltunk indexet.

Törlések

A fenti adatbázis objektumokat a DROP paranccsal lehet törölni.

DROP [TABLE | VIEW | INDEX] <név>;

Tábla definíciók módosítása

Már létező táblákat módosítani az

ALTER TABLE <táblanév>

[ADD | MODIFY] <oszlopnév> <típus>;

paranccsal, ahol az ADD egy új, NULL értékű oszlopot illeszt a táblához, míg a MODIFY paranccsal egy létező oszlop szélességét növelhetjük.

Adatelérések szabályozása

Az adatbáziskezelő rendszereket tipikusan több felhasználó használja, ezzel kapcsolatban újabb problémák merülnek fel.

Jogosultságok definiálása

Az egyes felhasználók részint az adatbáziskezelő rendszerrel, részint az egyes objektumaival különböző műveleteket végezhetnek. Ezeknek a megadására szolgálnak a GRAN utasítások. A

GRANT [DBA | CONNECT | RESOURCES]

TO <felhasználó>,

IDENTIFIED BY <jelszó>, ;

Parancs az egyes felhasználók az adatbázishoz hozzáférésnek jogát szabályozzák. A DBA jogosultság az adatbázis adminisztrátorokak (DataBase Administrator) definiálja, akiknek korlátlan jogai vannak az összes adatbázis objektum felett, nem csak létrehozhatja, módosíthatja illetve törölheti, de befolyásolhatja az objektum tárolásával, hozzáférésével kapcsolatos paramétereket is. A RESOURCE jogosultsággal rendelkező felhasználók létrehozhatnak, módosíthatnak ill. törölhetnek új objektumokat, míg a CONNECT jogosultság csak az adatbáziskezelőbe belépésre jogosít

Az egyes objektumokhoz táblák ill. nézetek a hozzáférést a

GRANT < jogosultság >,

ON < tábla vagy nézetnév>

TO <felhasználó>

[WITH GRANT OPTION];

parancs határozza meg. A <jogosultság> az objektumon végezhető műveleteket adja meg. Lehetséges érték:

ALL

SELECT

INSERT

UPDATE <oszlopnév> ,

DELETE

ALTER

INDEX

Az utolsó két művelet nézetekre nem alkalmazható. A felhasználó neve helyet PUBLIC is megadható, amely bármelyik felhasználóra vonatkozik. A WITH GRAN OPTION-nal megkapott jogosultságokat a felhasználók tovább is adhatják.

Tranzakciók

Az adatbázisok módosítása általában nem történhet meg egyetlen lépésben, hiszen legtöbbször egymódosítás során több táblában tárolt információn is változtatni akarunk, illetve egyszerre több rekordban akarunk módosítani, több rekordot akarunk beilleszteini. Előfordulhat, hogy módosítás közben meggondoljuk magunkat, vagy ami még súlyosabb következményekkel jár, hogy az adatbáziskezelő leáll. Ilyenkor a tárolt adatok inkonzisztens állapotba kerülhetnének, hiszen egyes módosításokat már elvégeztünk, ehhez szorosan hozzátartozó másokat viszont még nem.

Beállítható, hogy bizonyos műveletek automatikusan COMMIT műveletet hajtsanak végre.

SET AUTOCOMIT[ IMM | OFF ];

Az IMM állapotban az ALTER, CREATE, DROP, GRANT, EXIT utasítások sikeres végrehajtása COMMIT-ot is jelent.

A rendszer hardver hiba utáni újraindulásakor, illetve hibás INSERT, UPDATE vagy DELETE parancs hatására automatikusan ROLLBACK-et hajt végre. Érdemes hát biztonságos helyeken COMMIT parancsot kiadni, nehogy egyhibásan kiadott parancs visszavonja a korábbi módosításokat.

Párhuzamos hozzáférések szabályozása

Az adatbázis több felhasználó egyidejűleg is használhatja. Ilyenkor az egye táblákhoz a párhuzamos hozzáférést külön-külön lehet szabályozni.

LOCK TABLE <táblanév>,

IN [SHARE | SHARED UPDATE | EXCLUSIVE] MODE [NOWAIT];

A LOCK paranccsal egy felhasználó megadhatja, hogy az egye táblákhoz más felhasználóknak milyen egyidejű hozzáférést engedélyez. Az utasítás végrehajtásánál a rendszer ellenőrzi, hogy a LOCK utasításában igényelt felhasználási mód kompatíbilis-e a táblára érvényben lévő kizárással. Amennyiben megfelelő az utasítás visszatér és egyéb utasításokat lehet kiadni. Ha az igényelt kizárás nem engedélyezett, az utasítás várakozik, amíg az érvényes kizárást megszüntetik, ha csak a parancs nem tartalmazza a NOWAIT módosítót. Ebben az esetben a LOCK utasítás mindig azonnal visszatér, de visszaadhat hibajelzést is.

A táblához hozzáférést az első sikeres LOCK utasítás definiálja.

A SHARE módban megnyitott táblákat mások olvashatják, a SHARE UPDATE módban mások módosíthatják is, ilyenkor automatikusan kölcsönös kizárás teljesül egy-egy sorhoz hozzáférésnél. Az EXCLUSIVE mód kizárólagos hozzáférést biztosít.

Bővítések

Konzisztencia feltételek

A táblák definíciójánál eddig csak azt adhattuk meg, hogy milyen adattípusba tartozó értékeket lehet az egyes oszlopokban használni, illetve mely oszlopokban kell feltétlenül értéknek szerepelnie. Célszerű lenne a táblákhoz olyan feltételeket rendelni, amelyek szigorúbb feltételeket definiálnak az egyes adatokra, amelyeket aztán a rendszer a tábla minden módosításánál ellenőriz. Ilyenek például:

Az egyes adatok értékkészletének az általános adattípusnál pontosabb definíciója (pl. adott intervallumba tartozás, adott halmazba tartozás, ahol a halmaz lehet egy másik tábla egyik oszlopának értékei);

Az oszlop elődleges kulcs, azaz a tábla soraiban minden értéke különböző( hasonló hatás elérhető a UNIQUE indexvel is);

Az oszlop idegen kulcs, azaz értéke meg kell, hogy egyezzen egy másik tábla elsődleges kulcs oszlopának valamelyik létező elemével;

Amennyiben a táblák módosításánál valamelyik feltételt megsértenénk, a rendszer egy kivétel jelet (exception) generál, lefuttatja a hibajelhez tartozó kiszolgáló utasítást, ha van ilyen.

PL/SQL

A PL/SQL a nyelv olyan bővítése, amely segítségével

Több SQL utasítást össze lehet fogni programmá;

Szükség esetén vissza lehet csempészni az algoritmikus programozás elemeit: változókat, vezérlési szerkezeteket.

Az SQL SELECT utasítása több előre nem látható számú sort állít elő, amelyet a klaszikus algoritmikus programozási nyelvek nem tudnak kezelni. A PL/SQL bevezette a kurzor fogalmát, amely segítségével egy lekérdezés által előállított sorokon lehet végigpászátni.

Hosszas magyarázatok helyet nézzünk egy példát. A következő PL/SQL programrészlet kiszámolja a 20-as osztályban kifizetett összes pénzt (fizetés és prémium), megszámolja, hogy hányan kapnak 2000 $-nál nagyobb fizetést, illetve hány dolgozónak nagyobb a prémiuma, mint a fizetése. Az eredményeket egy átmeneti (temp) táblába rakja.

DECLARE

total-wages NUMBER: =0;

high_paid NUMBER: =0;

higher_comm NUMBER: =0;

BEGIN

FOR emp_record IN

(SELECT sal, comm FROM emp WHERE deptno =20)

LOOP

emp_record.comm := NVL(emp_record.comm, 0);

total_wages:= total_wages + emp_record.sal + emp_record.comm;

IF emp_record.sal > 2000 THEN

high_paid :=high_paid + 1

END IF;

IF emp_record.sal > emp_record.sal THEN

higer_comm :=higher_comm + 1

END IF;

END LOOP;

INSERT INTO temp VALUES ( high_paid, higher_comm,

total wages: || TO_CHAR(total_wages));

COMMIT;

END

Bár a fenti értékeket elő lehetne állítani 3 független lekérdezéssel, de a fenti program csak egyszer halad végig az EMP táblán, nem háromszor, ami nagy táblák esetén jelentős futási időkülönbséget jelenthetne.

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAL
30	SALES	CHICAGO
40	OPERATION	BOSTON