SQL Összefoglaló óra

 

Táblák létrehozása

A táblákat a CREATE TABLE paranccsal lehet létrehozni. Egy táblában <=1000 oszlop lehet. Több adattípust is lehet alkalmazni, de gyakorlatilag csak néhányra van szükség.

• NUMBER(h, t) – numerikus adatok, hossza<=38, h a szám hosszát tartalmazza, t – a tizedes-jegyek számát;
• DATE  – dátum és idő típus;
• VARCHAR2(méret) – karakter típus, a hossza váltózó (max hossza<=4000);
• CHAR(méret) –  karakter típus, fix hosszúságú (max hossza<=2000);
• NCHAR(méret) –  azonos a CHAR típussal, de a max méret függ a karakterkészletétől,
• LOB (large objects) – bináris v. szöveges formátum. Lehet kép-, hang-, vagy nagy méretű szöveges állomány.

                     A LOB típuson belül lehetnek:

·        LONG   – szöveges adatok, max hossza<=2 Gbyte;

·        LONG RAW  – bináris adatok, max hossza<=2 Gbyte;

A LONG és LONG RAW típusokat csak egyszerű adatfeldolgozásban alkalmazzák.

·        CLOB,  NCLOB, BLOB – belső LOB típusok, mivel az AB-ban tárolódnak,  max hossza<=4 Gbyte;

·        CLOB – az adatokat tárolja az AB karakter-készlete alapján;

·        NCLOB – az adatokat tárolja a nemzeti karakter-készletben.

Az Oracle mindegyik táblába automatikusan helyez egy ROWID nevű pszeudooszlopot, amely a programozók ritkán szokták használni.

ROWID – pszeudooszlop, amely tartalmazza a sor logikai egyedi címét. A ROWID nem változható meg, de a SELECT parancsban lekérdezhető:

                                    SELECT rowid FROM tabla_1;

A Sor sorszáma –  olyan szám, amely akkor rendelődik a sorhoz, amikor az bekerül a táblába. Ez a szám része a ROWID-nek.

Csak az a felhasználó hozhat létre táblákat, aki CREATE TABLE vagy CREATE ANY TABLE privilégiummal rendelkezik.

A STORAGE paraméter alkalmazása a CREATE TABLE parancsban

Egy táblát a STORAGE memória-paraméterek nélkül is lehet létrehozni. Ebben az esetben a tábla az alapértelmezési értékeket kapja, ami nem mindig jó megoldás. A memória egységeket (szegmenseket), melyekben a táblák tárolódnak, extentnek nevezzük. Az első extent neve INITIAL, a többi extentek pedig másodlagos extentek.

CREATE TABLE testtab

            (col1 VARCHAR2(10))

            STORAGE (INITIAL 100K

                        NEXT 50K

                        MINEXTENTS 1

                        MAXEXTENTS 99

                        PCINCREASE 20);

Az első extent 100K memóriát kap, a második – 50K. A PCINCREASE 20 érték alapján mindegyik következő extent mérete 20% növekszik az előzőhez képest. MINEXTENTS 1 azt jelenti, hogy a tábla először 1extentent kap, a MAXEXTENTS 99 – a tábla maximálisan  99 extentet kaphat.

Táblák partíciónálása

A táblákat, mint az indextáblákat is, particionálhatjuk. A particionálás lehetőséget ad a nagyon nagy táblák és indextáblák kezelésére. A particionálás során a nagy táblát kisebb és könnyebben kezelhető részekre osztja a rendszer.

Partíciónált tábla – olyan tábla, amelynek sorai kisebb azonos szerkezetű táblákba, partíciókra, vannak szétosztva.

A partíciók fizikailag különböző helyeken tárolhatjuk.

A partíciónálás előnyei:

• Az I/O-terhelés jobb eloszlása
• Biztonsági mentés és visszaállítás egyszerűbb végrehajtása
• Az adatsérülés lehetőségének minimalizálása
• Az archiválási folyamat segítése.

Példa.

CREATE TABLE nagy_tabla

            (col1 VARCHAR2(10),

            (col2 VARCHAR2(10))

             )

            PARTITION BY RANGE (col1, col2)

                        (PARTITION p1 VALUES LESS THEN (…)  TABLESPACE p1,

                         PARTITION p2 VALUES LESS THEN (…)  TABLESPACE p2);

 Hivatkozás egy partícióra:

ALTER TABLE nagy_tabla DROP PARTITION p1;

ALTER TABLE parancs

Az oszlop hosszát lehet növelni még akkor is, ha a tábla adatokat tartalmaz. Az oszlop hosszát pedig csak akkor lehet csökkenteni, ha a tábla üres. 

ALTER TABLE testtab ADD col2 VARCHAR2(100);

ALTER TABLE testtab MODIFY col2 VARCHAR2(150);

ALTER TABLE testtab STORAGE ( NEXT 10K

     MAXEXTENTS 50);

ALTER TABLE testtab DROP COLUMN  col1;

A táblákat átnevezhetjük:

                        RENAME testtab TO testtab1;

és törölhetjük:

                        DROP TABLE testtab;

Példa.

CREATE TABLE students(

        id                     NUMBER(5) PRIMARY KEY,

       first_name        VARCHAR2(20),

       last_name         VARCHAR2(20),

       major                VARCHAR2(30),                -- szak

       current_credits  NUMBER(3) );                    -- leadott vizsgák száma

 

Az ORACLE-rendszer tartalmazz egy bemutató sémát, amelynek Scott a tulajdonosa:

     CREATE TABLE scott.dept

            (deptno  NUMBER(2) NOT NULL,

             dname  VARCHAR2(14),

             loc  VARCHAR2(13),

            CONSTRAINT pk_dept PRIMARY KEY (deptno));

     CREATE TABLE scott.emp

            (empno  NUMBER(4) NOT NULL,

             ename  VARCHAR2(10),

             job       VARCHAR2(9),

             mgr  NUMBER(4),

             hiredate  DATE,

             sal           NUMBER(7,2),

             comm           NUMBER(7,2),

             sal           NUMBER(7,2),

             deptno           NUMBER(2),

            CONSTRAINT pk_emp PRIMARY KEY (empno));

Amikor egy olyan táblára (vagy más Oracle-objektumra) hivatkozunk, melynek nem mi vagyunk a tulajdonosa, akkor a tábla nevén kívül meg kell adni a tulajdonos nevét is

                                   tulajdonos.tábla_név

CONSTRAINT integritási megszorítás alkalmazása

Hivatkozási integritás biztosítja, hogy egy adott oszlop csak olyan értékeket vehessen fel, amelyek megjelennek egy másik oszlopban. Integritási megszorításként lehet előírni.

Megszorítás – egy szabály vagy korlátozás az adatok egy részén kerül ellenőrzésre.

Integritási megszorítás – olyan szabály, amely egy vagy több oszlop lehetséges értékeinek körét határolják be. A megszorítást a tábla létrehozásánál az oszlopokhoz lehet hozzárendelni.

A CONSTRAINT záradékot a CREATE TABLE vagy ALTER TABLE parancsokban használhatjuk. A legtöbb megszorítás a CONSTRAINT záradék nélkül is megadható.

 Léteznek:

• táblamegszorítások
• oszlopmegszorítások

Táblamegszorítás – olyan integritási megszorítás, amelyet egy tábla több oszlopára alkalmazunk. Oszlopmegszorítás pedig egy olyan integritási megszorítás, amelyet egy tábla oszlopához rendelünk.

A táblamegszorítások listája:

• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• REFERENCES
• ON DELETE CASCADE
• CHECK
• ENABLE VALIDATE
• DISABLE

Az oszlopmegszorítások:

• NULL
• NOT NULL
• …

A többi oszlopmegszorítások a táblamegszorításokkal azonosak.

A DISABLE opciót a integritási megszorítás vagy trigger kikapcsolására (nem törlésére) alkalmazhatjuk. Leggyakrabban az ALTER TABLE parancsban alkalmazzák.

ALTER TABLE tabla DISABLE UNIQUE oszlop, oszlop,..

ALTER TABLE tabla DISABLE PRIMARY KEY

ALTER TABLE tabla DISABLE CONSTRAINT megszorítás_név

DISABLE ALL TRIGGERS

Beépített függvények

Típusok konvertálása

• TO_CHAR(<number>)   vagy

TO_CHAR(<number>, <format>)

• TO_CHAR(<date>)  vagy

TO_CHAR(<date>, <format>)

• TO_DATE(<string>)  vagy

      TO_DATE(<string>, <format>)

• TO_NUMBER(<string>)

Naptári függvények

• ADD_MONTHS(<date>, n), a dátumhoz hozzáad n hónapót
• ROUND(<date>)

      ROUND(<date>, <format>)

Numerikus függvények

• ABS(n)
• COS(n)
• COSH(n)
• SIN(n)
• SINH(n)
• TAN(n)
• TANH(n)
• LN(n)
• LOG(<base>, n)   <base> - a logaritmus alapja
• MOD(m, n)  - m/n az osztás maradéka (mod(7,5):     2)
• POWER(x,y)  hatványozás           (power(3,2):        9)
• ROUND(n [,decimal_degits])
• SIGN(n)         (értékek: -1, ha n<0; 0, ha n=0; 1, ha n>0;)
• CEIL(n)                (ceil(10.7) :          11)
• FLOOR(n)            (floor(10.7):         10)
• SQRT(n)                (sqtr(25) :            5)

Karakteres függvények

• ASCII(charakter)
• CHR(n)
• INITCAP(string)     (Initcap(’szabó józsef’):  Szabó József)
• INSTR(input_string, sub_string [,n, [,m]])

Az n-ik karaktertől kezdve keresi az m-ik sub_string előfordulását az input_string-ben. Ha talált, visszaadja annak a helyét, ha nem talált, akkor az eredmény 0 lesz.

• LENGTH (string)      (Length(’Oracle’):     6)
• LOWER (string)       (Lower(’ABC’):  abc)
• UPPER (string)
• LPAD (string, n)
• LPAD (string, n[,pad_chars])
• RPAD (string, n)
• RPAD (string, n[,pad_chars])
• LTRIM (string)
• LTRIM (string [,set_of_chars])
• RTRIM (string)
• RTRIM (string [,set_of_chars])
• SUBSTR (string, start [,length]) 

(Substr(’abcdef’, 3, 2):  cd)

• TRANSLATE (string, search_set, replace_set)

A string-ben a search_set karaktereket becseréli a megfelelő replace_set karakterekre.

         TRANSLATE (’eger’, ’e’ , ’a’)=’agar’

Univerzális függvények

• NVL(kifejezés, replace_value) - ha a kifejezés nem üres, akkor annak az értékét adja vissza. Ha a kifejezés NULL értékű, akkor  a replace_value értéket adja vissza.
• DECODE(expression, val_1, trans_1, val_2, trans_2,... val_n, trans_n, default)

A expression  adatokban (pl. a Tabla oszlopában egy SELECT parancsban) a val_k értékeket helyetesíti a trans_k értékekkel. (k= 1,2,...,n). default – a többi értékeket nem változtatja meg.

Példa.

SELECT DECODE(job, ’clerk’, ’Ügyintéző’, ’president’ ’Elnök’, job)        FROM emp;

JOB

-----

Elnök

ANALIST

Ügyintéző

Ügyintéző

ANALIST

Az Analist értéke nem változott meg, mivel az nem volt megadva a DECODE paraméterei között.

 Fa-struktúrák alkalmazása a SELECT parancsban

Példa.

SELECT emp_no, e_name, job, fonok_no FROM emp;

emp_no    e_name      job                 fonok_no

            ----------    ----------   -----------        ------------

           7369           Szabó      clerk                 7902

           7499           Kiss        salesman           7698

           7521           Nagy       salesman           7698

           7698           Rontó     manager           7839

           7902           Orosz      analist              7839

           7839           Klapka    president       

Feladat. Egy olyan eredmény-táblát akarunk szerkeszteni, mely tartalmazza minden dolgozó és főnöke nevét. Lehet látni, hogy az eredmény struktúrája fa-szerkezetű. A hagyományos megoldás

SELECT  f1.e_name, job, fonok_no FROM emp f1, emp f2

                     WHERE f1.fonok_no=f2.emp_no;

Az ORACLE9i-ben fa-szerkezetű lekérdezéseket lehet létrehozni. Ebben az esetben a SELECT parancs a következő opciókat tartalmazhatja:

• CONNECT  BY – egy táblán belül megadja az oszlopok közti kapcsolatot;
• START WITH –meghatározza azt a sort, amelytől kezdődik a fa építése

SELECT emp_no, e_name, job, fonok_no FROM emp

              CONNECT BY fonok_no= PRIOR emp_no

              START WITH fonok_no IS NULL;

Ez a parancs a tábla sorait így rendezi át

emp_no    e_name   job                 fonok_no

            ----------    ----------   -----------        ------------

           7839           Klapka    president       

           7698           Rontó     manager           7839

           7902           Orosz      analist              7839

           7369           Szabó      clerk                7902

           7499           Kiss        salesman          7698

           7521           Nagy       salesman          7698

          

NULL értékek

A parancsokban a  NULL értékeket csak az IS NULL vagy IS NOT NULL operátorokkal lehet alkalmazni:

SELECT emp_no, e_name FROM  emp  WHERE comm IS NULL;          

Ezért a következő parancs hibát tartalmaz:

SELECT emp_no, e_name FROM  emp  WHERE comm=NULL;          

Példa.

SELECT e_name, sal+comm FROM emp;

ha a comm értéke NULL, akkor a sal+comm kifejezés értéke is NULL lesz, még ha adott is a sal érétke. A helyes megoldás a NVL függvény alkalmazása         

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

és ha a comm értéke üres (NULL), akkor a NVL(comm, 0) függvény 0 értéket ad vissza. Lehet látni, hogy ilyen esetekben nélkülözhetetlen a NVL függvény.

9Külső összekapcsolás

Összekapcsolás – két vagy több tábla adatainak együttes feldolgozása egy SELECT parancsban. Külső összekapcsolás – olyan összekapcsolás, amikor az eredményben azok a sorok is jelennek meg, amelyeknek nincs megfelelője a másik táblában. A jelölése: (+).

SELECT d.dept_no, d.d_name, e.e_name

             FROM  dept d,  emp e

             WHERE d.dept_no (+) = e.dept_no;          

A (+) művelet alkalmazása azt eredményezi, hogy az eredménytáblába kerül az összes dolgozó neve (e_name), és a részleg neve (d_name), de azoknak a részlegeknek a nevei is ki lesznek írva, melyekben jelenleg nem dolgozik senki.  

Azonos feladat esetén, egy SELECT parancs gyorsabban hajtódik végre, ha külső összekapcsolást tartalmaz, mint a NOT IN-t tartalmazó konstrukció.

Például:

SELECT e_name, dept_no   FROM  emp

                       WHERE dept_no NOT IN (SELECT dept_no FROM dept);          

vagy

SELECT e_name, dept_no FROM emp e, dept d

                          WHERE e.dept_no = e.dept_no(+) AND d.dept_no IS NULL;          

Halmaz műveletek

• UNION (egyesítés) – két lekérdezés eredményeinek unióját képezi. Az eredmény-táblába olyan sorok kerülnek, melyeket legalább az egyik tábla tartalmaz, de ismétlődő sorokat nem tartalmaz az eredmény-tábla;
• INTERSECT (metszet) – két lekérdezés eredményeinek metszetét képezi. Olyan sorokat kapunk, melyek mindkét táblában előfordulnak;
• MINUS (kivonás) – két lekérdezés eredményeinek különbségét képezi. Azokat a sorokat kapjuk, melyeket az első tábla tartalmazza, a másik – pedig nem.

SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’          

UNION

SELECT d_name  FROM dept  WHERE d_name LIKE ’%o%’;          

 SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’           

INTERSECT

SELECT d_name  FROM dept   WHERE d_name LIKE ’%o%’;          

SELECT d_name  FROM dept  WHERE d_name LIKE ’%s%’          

MINUS

SELECT d_name FROM dept  WHERE d_name LIKE ’%o%’;          

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

• INSERT – új sor beszúrása
• UPDATE – adatok módosítása a már létező sorokban
• DELETE – sorok törlése
• TRUNCATE – az összes sorok gyors törlése

Sorok beszúrása a táblába (INSERT parancs)

Ha az adatokat a sor összes mezőjébe akarjuk beszúrni, akkor a mező-listát nem szükséges megadni. A mezők neveit akkor kell megadni, amikor csak egyes mezőkbe akarunk adatokat beszúrni. A NULL (üres értékeket) az adatok közt lehet megadni, de ha az adat-lista nem tartalmaz egy mezőt, akkor is az NULL értéket fog kapni.

Példa.

Először az SQL*Plus-ban ellenőrizzük a testtab tábla struktúráját:

                        DESCRIBE testtab;

                        NAME          NULL ?               TYPE

                        ---------          ----------                --------

                         COL1                                      VARCHAR2(10) 

                         COL2                                       NUMBER(38)

INSERT INTO testtab(col1, col2) VALUES(’szöveg’, 123);

Ugyan ezt az eredményt kapjuk, ha az

                        INSERT INTO testtab VALUES (’szöveg’, 123);

parancsot alkalmazzuk.

A NULL-értékeket beszúrása-

                        INSERT INTO testtab (col1, col2) VALUES (’szöveg’, NULL);

vagy

                        INSERT INTO testtab (col1) VALUES (’szöveg’);

Arra is van lehetőség, hogy a táblába az INSERT parancs egyszerre több sort szúrjon be (e célból alkalmazzuk a beágyazott SELECT parancsot)

                        INSERT INTO testtab (col1, col2)

SELECT e_name, emp_no FROM emp;

Tábla létrehozása egy másik tábla alapján

CREATE TABLE emp_copy AS

            SELECT * FROM emp;

CREATE TABLE emp_copy2 AS

            SELECT emp_no, e_name FROM emp

WHERE e_name LIKE ’%a%’;

SELECT * FROM emp_copy2;

     EMP_NO        E_NAME

               --------------        -------------

                  7499                  allen

                  7654                  martin

Adatok módosítása (UPDATE parancs)

Az UPDATE parancs módosítja az összes olyan sorokat, amelyekre teljesül a WHERE-feltétel. Ha az UPDATE parancs WHERE- feltételt nem tartalmaz, akkor a módosítás minden sorban végrehajtódik. Ez nagyon ritkán fordul elő.

Példa.

                        UPDATE emp_copy2  SET e_name=’Kovács’

                                                           WHERE emp_no=7499;

                         

 UPDATE emp_copy2  ec  SET (emp_no, e_name) =

                                               (SELECT emp_no, e_name FROM emp e

                                                                       WHERE e.emp_no=ec.emp_no)

    WHERE e_name LIKE ’I%’;

Az utolsó parancsban ec és e a táblák másodlagos nevei. Az utolsó WHERE-feltétel az egész parancshoz tartozik (az UPDATE-hoz), és azokat a sorokat adja meg, amelyeket módosítani akarunk. A beágyazott SELECT parancs azokat az értékeket szerkeszti, melyek bekerülnek az emp_copy2  táblába. A SELECT-ben a WHERE-feltétel összekapcsolja a két tábla megfelelő sorait.

Sorok törlése (DELETE parancs)

DELETE FROM emp_copy2 WHERE emp_no=7876;

                       

DELETE FROM emp_copy2 WHERE emp_no IN

(SELECT emp_no FROM emp_copy);

Mindegyik SQL parancsban függvényeket lehet alkalmazni.

Példa.

UPDATE emp_copy  SET e_name=UPPER( SUBSTR(e_name, 1, 1))       || LOWER(SUBSTR(e_name, 2, LENGTH(e_name)-1));

Ugyan ezt az eredményt kapjuk, ha az INITCAP függvényt alkalmazzuk.

Adatok gyors törlése

A tábla összes sorait gyorsan lehet törölni a TRANCATE paranccsal:

TRANCATE TABLE emp_copy;

A parancs végrehajtása után a tábla struktúrája megmarad, de az egyetlen sort sem fog tartalmazni.

Tranzakciók

Tranzakció – SQL parancsoknak olyan sorozata, amelyet az Oracle egyetlen egységként kezel. A tranzakció által okozott változásokat az AB-ban a COMMIT parancs véglegesíti.

Véglegesítés – az adatok változásainak átvezetése az AB-ba. A változások érvénytelenítését a ROLLBACK parancs hajtja végre. Ez alapján az AB-ban történt változtatások vagy ténylegesen végrehajtódnak, vagy valami okból – nem.

Az elosztott tranzakció olyan adatokkal hajtódik végre, amelyek az elosztott AB-ban, hálózaton, több csomópontban tárolódnak.

Kétfázisú véglegesítés – az Oracle az elosztott tranzakciók kezelését a kétfázisú véglegesítéssel végzi. Ez biztosítja, hogy egy tranzakció a hálózat minden csomópontjában vagy véglegesítődik, vagy visszagörgetésre kerül.

Bankautomata működésének folyamata, mint egy tranzakció példája.

1.      Lekérdezzük a számlaegyenlegünket

SELECT account_balance FROM bank_table WHERE account_number=1111111

     AND account_type= ’SAVINGS’;

Az SQL-parancs az SGA-n keresztül a kiszolgáló-folyamathoz kerül, amely ellenőrzi, hogy a az SQL-terület tartalmazza-e parancs futtatható változatát. Ha a parancs nincs ott, akkor elemzi a parancsot, és azt a végrehajtható formában az SQL-területre helyezi és utána végrehajtja. A kliens gyorsító-tárból leolvassa az egyenleget és közli azt a felhasználóval. Például, legyen az 250000 Ft.

Utána kérünk 20000 forintot.  A kliens-folyamat fogadja a kérésünket, és az alapján szerkeszt egy SQL-parancsot

UPDATE bank_table SET account_balance=230000  WHERE WHERE        account_number=1111111 AND Account_type= ’SAVINGS’;

Ezt a folyamatot bővebben lépésenként így lehet leírni:

1.      A kliens-folyamat a parancsot az SGA-n keresztül eljuttatja a kiszolgáló-folyamathoz.

2.      A kiszolgáló-folyamat ellenőrzi, hogy rendelkezésére áll-e a parancs végrehajtható formája. Ha Igen, akkor a 4 lépésben, ha Nem – akkor a 3 lépésben folytatódik a feldolgozás.

3.       Létrehozza a parancs végrehajtható formáját és az SQL-területre helyezi.

4.      Végrehajtja a parancsot.

5.      A manipulált adatok az adatgyorsítóban vannak? Ha Igen - 7, ha Nem – 6.

6.      Az adatokat az Oracle beolvassa az adatállományból az adatgyorsítóba.

7.      Oracle rögzíti az adat régi értékét a visszagörgető szegmensbe (25000).

8.      Oracle másolatokat készít a tranzakcióról a változásnaplóban.

9.      Az adatgyorsító-tárban lévő adatot megváltoztatja az új értékre (23000).

10.  A bankautomata jelzi, hogy befejezte a tranzakciót.

11.  Az az esemény, hogy befejeződött a tranzakció, rögzítésre kerül a változásnaplóban.

12.  Az Oracle törli a visszagörgető szegmensben tárolt visszaállítási információt.

13.  Az automata kiadja a pénzt.

Az első tranzakció akkor kezdődik, amikor a felhasználó belép a rendszerbe és elindít egy  művelet sorozatot az AB-ban. A következő tranzakciók a COMMIT vagy a ROLLBACK paranccsal kezdődik.

A COMMIT parancs a tranzakció által végzett módosítások véglegesítésére használható. A COMMIT explicit használata nélkül a program egészének végrehajtása egyetlen tranzakcióként tekintendő és nem kerül véglegesítésre a program befejeződéséig. Ennek az a hátránya, hogy ez idő alatt a többi felhasználó hozzáférését gátolja. A COMMIT parancsot ajánlatos használni minél gyakrabban.

A tranzakció végét a COMMIT vagy a ROLLBACK parancsok jelzik. A tranzakció alatt végrehajtott módosítások véglegesen a táblákban a csak a COMMIT parancs által történnek. A ROLLBACK parancs esetén ezek a változtatások nem lesznek rögzítve a táblákban, ami azt jelenti, hogy a ROLLBACK parancs után a táblák tartalma a tranzakció kezdeti állapotában kerül.

Például, ha egy UPDATE parancs több mint egy sort módosít, és hiba csak az egyik sorban történt, akkor is a ROLLBACK parancs a tábla egyetlen sora sem kerül módosításra.

A tranzakció végrehajtása alatt az Oracle zárolhatja a tábla sorait. Az Oracle csak azokat a sorokat zárolja, amelyekkel az INSERT, UPDATE, DELETE parancsok hajtják végre a műveleteket. A zárolás automatikusan történik, és a felhasználónak nem kell azzal foglalkozni. Mivel a zárolás csak egyes sorokat érint (és nem az egész táblát), ez idő alatt a táblával a többi felhasználó párhuzamosan dolgozhat.

Az  Oracle-ban lehetőség van a visszalépést a tranzakció bár-melyik pontjára végrehajtani. E célból a SAVEPOINT parancsot alkalmazhatjuk. Ha létrehoztunk egy SAVEPOINT pontot, akkor a visszalépést a ROLLBACK TO SAVEPOINT paranccsal lehet végrehajtani.

Példa.

SAVEPOINT MyPoimt;

  Savepoint created.

SELECT emp_no, e_name FROM emp;

     EMP_NO        E_NAME

               --------------        -------------

                  7369                 smith

                  7499                  allen

                  7566                  jones 

                  7788                  scott

                  7654                  martin

            5 rows selected.

DELETE FROM emp WHERE e_name LIKE ’%a%’;

  2 rows deleted.

SELECT emp_no, e_name FROM emp;

     EMP_NO        E_NAME

               --------------        -------------

                  7369                 smith

                  7566                  jones 

                  7788                  scott

             3 rows selected.

            ROLLBACK TO SAVEPOINT MyPoint;

                        Rollback complete.

SELECT emp_no, e_name FROM emp;

     EMP_NO        E_NAME

               --------------        -------------

                  7369                 smith

                  7499                  allen

                  7566                  jones 

                  7788                  scott

                  7654                  martin

            5 rows selected.

Privilégiumok

Privilégium (jogosultság) a felhasználó számára lehetővé teszi, hogy az AB-ban bizonyos műveleteket hajthasson végre.

A privilégium kéttípusú lehet:

·        rendszer-privilégium;

·        objektum-privilégium.

A rendszer-privilégiumok lehetővé teszik adatdefiníciós és adatvezérlő parancsok végrehajtását, és az AB-ba való belépést. Az objektum-privilégiumok az AB objektumokkal való műveletekhez adnak jogosultságot. A felhasználó a létrehozása után egyetlen privilégiummal sem rendelkezik, és a privilégiumokat az AB adminisztrátortól később kapja. Amikor a felhasználó kap egy privilégiumot, utána az engedélyezett műveteket végrehajtatja a felsorolt AB-objektumaival.

Egy AB-hoz általános esetben több felhasználó is kapcsolódhat. Az Oracle-ban minden objektumnak van felhasználó-tulajdonosa. A nem tulajdonos-felhasználó csak akkor végezhet bizonyos műveleteket az objektumokkal, ha megkapta a megfelelő privilégiumokat. Létezik több mint nyolcvan rendszer-privilégium:

• ALTER
• DELETE
• EXECUTE
• INDEX
• INSERT
• REFERENCES
• SELECT
• UPDATE
• …

A következő táblázat tartalmazza egyes privilégiumok és AB-objektumok közti lehetséges kapcsolatokat.

Privilégium	Table	View	Sequence	Procedure
ALTER	+		+
DELETE	+	+
EXECUTE				+
INDEX	+
INSERT	+	+
REFERENCES	+
SELECT	+	+	+
UPDATE	+	+

A INSERT, UPDATE és REFERENCES privilégiumokat lehet a tábla egyes oszlopaihoz kötni. Ha megakarjuk tudni, hogy milyen rendszer privilégiumokat lehet alkalmazni, végre kell hajtani a következő parancsot:

            SELECT UNIQUE privilege FROM dba_sys_priv;

Privilégiumok adományozása

Az AB adminisztrátor a privilégiumokat a GRANT paranccsal adhatja meg a felhasználóknak:

GRANT  privilégium_lista ON objektum_lista TO felhasználó_lista

[WITH GRANT OPTION];

GRANT  privilégium_lista ON objektum_lista TO szerepkör_lista

[WITH GRANT OPTION];

GRANT  privilégium_lista ON objektum_lista TO PUBLIC

[WITH GRANT OPTION];

A WITH GRANT OPTION esetén a felhasználó az adott privilégiumot más felhasználónak is tovább adhatja.

Példa.

            GRANT SELECT, UPDATE ON emp_copy TO test_user;

A test_user felhasználó az emp_copy táblával végrehajthatja a SELECT és UPDATE parancsokat. Ezekben a parancsokban nem elég a tábla nevét emp_copy megadni, mivel a tábla tulajdonosát is meg kell adni. Például, ha a scott felhasználó az emp_copy tábla tulajdonosa, akkor a helyes hivatkozás:

SELECT * FROM scott.emp_copy;

Bizonyos esetekben ez a követelmény problémát is okozhat. Például, ha a tábla tulajdonosa a program létrehozása és futtatási ideje között megváltozott, akkor ezt figyelembe kell venni az összes táblára való hivatkozásban. Ilyen esetekben célszerű alkalmazni a tábla szinonimáját:

                        CREATE SYNONIM test FOR  scott.emp_copy;

A scott.emp_copy tábla megkapta a test szinonimát. Ha ezek után megváltozik a tábla tulajdonosa, akkor továbbra is a táblára lehet hivatkozni a szinonima segítségével

                        SELECT * FROM test;

A PUBLIC opció esetén az adott privilégiumokat az összes felhasználóra érvényes, ami azt jelenti, hogy az objektum nyilvános. A Nyilvános objektum elérhető, látható az összes felhasználó számára.

            GRANT SELECT, UPDATE ON emp_copy TO PUBLIC;

Ha a privilégium tartalmazza az ANY opciót, akkor az az AB összes táblájára érvényes. A következő privilégiumok esetén:

DELETE  ANY TABLE

UPDATE ANY TABLE

INSERT   ANY TABLE,

a felhasználó az AB összes tábláit módosíthatja, még akkor is, ha nem tulajdonosa a táblának. Ezt a legnagyobb lehetséges jogosultság, és csak az adminisztrátor szintű felhasználó kaphatja azt.

            A SESSION_PRIVS nézetben megtalálhatók az aktuális privilégiumok. Az ALL_TAB_PRIVS és ALL_COL_PRIVS nézetekből pedig megtudhatjuk, hogy milyen privilégiumokkal rendelkeznek a felhasználók.

Szerepkörök (ROLE)

Szerepkör – a privilégiumok együttese, és lehetőség a felhasználók csoportosítására. A felhasználókat egy csoportba lehet beosztani, és a csoporton belül mindegyik felhasználó azonos jogosultsággal rendelkezik. Például, létrehozunk egy szerepkört

CREATE ROLE kozos;

utána a szerepkörhöz privilégiumokat kapcsolunk

GRANT INSERT ON table_a TO kozos;

GRANT INSERT ON table_b TO kozos;

GRANT INSERT, DELETE  ON table_c  TO kozos;

GRANT UPDATE ON table_d TO kozos;

GRANT DELETE ON  table_e TO kozos;

GRANT SELECT  ON table_f TO kozos;

Ha a felh_1 és felh_2  felhasználók megkapják a kozos szerepkört

GRANT kozos  TO x;

GRANT kozos  TO y;

akkor rendelkezni fognak az összes kozos szerepkörhöz tartozó jogosultságokkal.

Beépített szerepkörök

Az Oracle-ban léteznek beépített szerepkörök is:

CONNECT-

·        ALTER SESSION

·        CREATE CLUSTER

·        CREATE DATABASE LINK

·        CREATE SEQUENCE

·        CREATE SESSION

·        CREATE SYNONIM

·        CREATE TABLE

·        CREATE VIEW

RESOURSE-

·        CREATE CLUSTER

·        CREATE PROCEDURE

·        CREATE  SEQUENCE

·        CREATE TABLE

9.12.4 Jogosultság a programok végrehajtására

Az AB-ban tárolódhatók 

·        Eljárások (PROCEDURE)

·        Csomagok (PACKAGE)

·        Függvények  (FUNCTION).

Ezek az objektumok csak akkor érhetők el, ha a felhasználó EXECUTE privilégiummal rendelkezik.

Példa.

GRANT EXECUTE  ON my_package TO PUBLIC;

GRANT EXECUTE  ON my_func TO felh_2;

GRANT EXECUTE  ON my_proc TO felh_1;

Privilégiumok  visszavonása

Az AB adminisztrátor a privilégiumot a felhasználótól a REVOKE parancs által visszavonhat:

REVOKE privilégium ON objektum FROM felhasználó

[CASCADE CONSTRAINTS];

A CASCADE CONSTRAINTS a REFERENCES privilégium megvonása esetén törli az összes hivatkozási integritási megszorítást, amelyet a felhasználó hozott létre (CASCADE – lépcsőzetes).

            REVOKE UPDATE ON emp_copy FROM test_user;

Ez a parancs után a test_user felhasználó nem módosíthatja a emp_copy táblát, de a SELECT parancsot továbbra is alkalmazhatja.

REVOKE  SELECT ON classes FROM user_1;

REVOKE ALTER TABLE, EXECUTE ANY PROCEDURE FROM user_2;

REVOKE  kozos FROM felh_1;

Indexek

Indexeket azzal a céllal hozzák létre, hogy a tábla bizonyos oszlopainak értékei ne ismétlődjenek, vagy (és) hogy gyorsabban történjen az adatok keresése. Az index létrehozása után azok a parancsok, melyek módosítják a táblát (UPDATE, DELETE, INSERT), az indexek szerinti sorrendjét továbbra is aktuális állapotban tartják. Ez időt igényel, és ezért minél több indexe van egy táblának, annál lassabban hajtódnak végre az UPDATE, DELETE, INSERT parancsok. A SELECT parancs a WHERE része alapján határozza meg a szükséges és felhasználandó indexeket, és ez biztosítja a parancs leggyorsabb végrehajtását.

Az indexeket létre lehet hozni:

·        direkt módon, a CREATE INDEX paranccsal,

·        automatikusan, a CREATE TABLE parancsban,

·        ALTER TABLE paranccsal.

Használhatók:

·        egyedi (nem ismétlődő) indexek, az értékei különbözőek, amikor a CREATE TABLE parancs a PRIMARY KEY vagy UNIQUE megszorításokat tartalmazza;

·        ismétlődő értékeket tartalmazó indexek.

Példa.

CREATE INDEX Test_ind ON Test(col1)

TABLESPACE users

STORAGE (INITIAL 20K

                     NEXT 20K

                     PCTINCREASE 75);

Példa.

CREATE TABLE sample_3

 (col_a    VARCHAR(30),

 col_b    VARCHAR(30),

 col_c    VARCHAR(30));

 CREATE INDEX col_c_ind ON sample_3(col_c) ;

  CREATE UNIQUE INDEX col_a_ind ON sample_3(col_a) ;

A cola_ind index nem tartalmazhat ismétlődő értékeket, a colc_ind index pedig tartalmazhat.

A táblák indexeit a user_indexes nézetből lehet lekérdezni.

Példa.

            SELECT index_name FROM user_indexes

                        WHERE table_name=’SAMLE_3’;

            INDEX_NAME

            -------------------

COL_A_IND

COL_B_IND

   

Láthatjuk, hogy a sample_3 táblához két index tartozik COL_A_IND, COL_B_IND. A következő parancs megsemmisíti (DESABLE) a COL_A_IND indexet.         

ALTER TABLE semple_3 DESABLE CONSTRAINT COL_A_IND;

SELECT index_name FROM user_indexes

                        WHERE table_name=’SAMPLE_3’;

            INDEX_NAME

            -------------------

COL_B_IND

Most a semple_3 táblához csak egy COL_B_IND index tartozik. A következő parancs visszaállítja  (ENABLE) a COL_A_IND indexet:         

 ALTER TABLE semple_3 ENABLE CONSTRAINT COL_A_IND;

A CREATE, ALTER parancsokban meg lehet adni, hogy az Oracle az indexek tárolására menyi memóriát adjon:

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (emp_no)

            USING INDEX

                          INITRANS 5

                          MAXTRANS 10

                          TABLESPACE user_date

                          STORAGE ( INITIAL 20K

                                               NEXT 20K

                                               MINEXTENTS 1

                                               MAXEXTENTS 99

                                               PCTINCREASE 0)

                                   PCTFREE 10;

Ha egy táblában a FOREIGN KEY vagy REFERENCES megszorításokkal külső kulcsokat hozunk létre, akkor azokat is kell indexelni, hogy a táblák közti kapcsolatot létrehozása után gyorsabban lehessen végrehajtani az adatok keresését:

ALTER TABLE emp ADD CONSTRAINT fk_demt FOREIGN KEY (dept_no)

            REFERENCES dept(dept_no);

CREATE INDEX fk_dept_ind ON emp(dept_no) ;

A fk_dept_ind index ismétlődő értékeket tartalmazhat. Ezek után a következő parancs

gyorsabban hajtódik végre:

SELECT e.e_name, d.name FROM emp e, dept d

                                               WHERE e.dept_no=d.dept_no;

Nagy táblák esetén az indexek megsemmisítése és visszaállítása sok időt igényelhet. Az indexet ideiglenesen is kikapcsolhatjuk (KEEP opcióval), úgy hogy az index továbbra is létezzen. Például,

ALTER TABLE emp KEEP INDEX  pk_emp;

 A DROP INDEX parancs az indexet véglegesen megsemmisíti, ha az nem PRIMARY KEY vagy UNIQUE index.

                        DROP INDEX Test_ind;

Sok esetben a programok indexelés nélkül is alkalmazhatók, de a végrehajtásuk lassabb lesz (legalább is elméletileg). Ne felejtsük, hogy gyakorlati szempontból mindegyik táblában kell, hogy legyen legalább egy PRIMARY KEY vagy UNIQUE index. Az indexeket az  indexszegmens tartalmazza.

Táblák integritása

A UNIQUE és PRIMARY KEY indexeket az Oracle automatikusan hoz létre, amikor a CREATE TABLE parancsot végrehajtja

CREATE TABLE test (test_no NUMBER(5), …,

      PRIMARY KEY(test_no), …);

A már létező táblában is létrehozhatunk elsődleges kulcsot:

ALTER TABLE test ADD

       PRIMARY KEY(test_no);

Figyelem! Ha a tábla már tartalmaz sorokat, és azoknak az adatai a létrehozandó indexel konfliktusban vannak, akkor az ALTER parancs hibát jelez.

Az indexeket lehet törölni, vagy kikapcsolni törlés nélkül. Ezeket a műveleteket nem lehet végrehajtani, ha egy másik tábla idegen kulcsa hivatkozik az adott kulcsra. 

Az ENABLE opció által egy indexet bekapcsolhatunk, az index kikapcsolására a DISABLE opció szükséges. Az alapértelmezés alapján (ha nincs megadva sem az ENABLE sem a DISABLE opció) az Oracle az ENABLE opciót fogja értelmezni.

ALTER TABLE test DISABLE PRIMARY KEY;

Ha ki akarjuk kapcsolni egy kapcsolatban levő idegen kulcsot, akkor ezt így lehet megvalósítani 

ALTER TABLE test DISABLE PRIMARY KEY CASCADE;

Az index törlését hasonló képen lehet végrehajtani:

ALTER TABLE test DROP PRIMARY KEY;

Ha az index az ENABLE állapotban van, akkor a táblák közti integritási kapcsolatok minden módosítási művelet után is érvényesek maradnak. A DISABLE állapotban az integritási kapcsolatok  megsérülhetnek. Ha az integritási kapcsolat egyszer megsérül, akkor az Oracle az indexet az ENABLE állapotába már nem képes visszaállítani.

 Szekvenciák (sorozatok)

Ha egy Oracle alkalmazásban szükség van egy numerikus adatsorozatra (például, elsődleges kulcsra), melynek az értékei egyediek legyenek, akkor szekvenciákat lehet alkalmazni.

Szekvencia – egy olyan Oracle objektum, amely képes számokat generálni, és elérhetővé téve őket a sorszámozott elsődleges kulcsok számára.

Szekvenciák létrehozása:

CREATE SEQUENCE sor  START WITH n

A START WITH n – megadja szekvencia kezdő értékét.

A szekvencia aktuális értékét a

                        sor.CURRVAL

attribútum adja vissza.

A szekvencia  következő értékét (elemét) a

sor. NEXTVAL

attribútummal lehet megkapni.

Példa.

Ha a student tábla id mezője elsődleges kulcs, akkor annak az értékeit a student_sequence szekvencia által kapjuk meg:

CREATE SEQUENCE student_sequence  START WITH 10000;

INSERT INTO students (id, first_name, last_name, major,  current_credits)

  VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith', 'Computer Science', 0);

INSERT INTO students (id, first_name, last_name, major, current_credits)

  VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason', 'History', 0);

SELECT id FROM students;

     ID

  -------

10001

10002

A szekvenciát általánosabb formában is létre lehet hozni:

CREATE SEQUENCE test_seq 

INCREMENT BY 4

START WITH 50

MAXVALUE   70

                                   MINVALUE    50

                                   CYCLE

                                   CACHE 3;

A test_seq szekvencia a következő sorozatot fogja generálni

50, 54, 58, 62, 66, 70, 50, 54,...

Az INCREMENT BY 4 az jelenti, hogy a szekvencia értékei néggyel fognak növekedni, hanem néggyel,

a MAXVALUE értékének elérése után a CYRCLE újból visszaállítja a MINVALUE értéket. A CACHE 3 egyszerre három generált számot fog elhelyezni a SGA-kesbe.

Figyelembe kell venni, hogy a ROLLBACK parancs a szekvencia kezdő állapotát nem állítja vissza, mely a tranzakció elején volt aktuális. 

A szekvencia módosítása:

ALTER SEQUENCE test_seq 

INCREMENT BY 10

MAXVALUE   1000

                                   MINVALUE    50

                                   NOCYCLE

                                   CACHE 20;

és törlése:

DROP SEQUENCE test_seq; 

Klaszterek (Fürtök)

Klaszter – olyan táblák adatainak együttes tárolása, amelyek közös adatokat tartalmaznak, és egy időben kerülnek feldolgozásra.

Klaszterkulcs – a klaszterizált táblák közös oszlopai közül azok az oszlopok, amelyek a tárolás vagy elérés kulcsául szolgálnak.

Ha a lekérdezések gyakran hivatkoznak két táblából álló összekapcsolt tábla-együttesre, akkor az AB adminisztrátor úgy dönthet, hogy ezeket a táblákat közösen egy klaszterben tárolja. A klasztert a CREATE CLASTER paranccsal lehet létrehozni.

Példa.

  CREATE CLASTER Diak_Tanar (tantargy NUMBER(3))

  TABLESPACE users

  STORAGE( INITIAL 200K

                        NEXT 300K

                        MINEXTENTS 2

                        MAXEXTENTS 20);

A tantargy oszlop a klaszter kulcsa, és általa valósul meg a két tábla közti kapcsolat.

A már létező klaszterben táblákat lehet létrehozni:

CREATE TABLE Diak(tantargy NUMBER(3) PRIMARY KEY, …)

CLUSTER Diak_Tanar (tantargy);

CREATE TABLE Tanar(nev VARCHAR2(15) NOT NULL, …

    tantargy NUMBER(3) REFERENCES Diak)

CLUSTER Diak_Tanar (tantargy);

A klaszter indexét még akkor kell létrehozni, amikor a táblák üresek, egyetlen sort sem tartalmazhat.

            CREATE INDEX Diak_Tanar_Index

            ON CLUSTER Diak_Tanar

            TABLESPACE users

STORAGE( INITIAL 50K

                        NEXT 50K

                        MINEXTENTS 2

                        MAXEXTENTS 20);

A klasztert és az indexét különböző táblaterületben lehet tárolni. Ha töröljük a klaszter indexét, akkor a klaszter tábláit már nem lehet használni.

  A klaszter módosítása az ALTER CLUSTER paranccsal történik:

            ALTER CLUSTER Diak_Tanar

            PRTFREE 30;

Nézetek (VIEW)

            A nézet tartalmazza egy SQL-lekérdezés eredményét. A programokban ugyanúgy alkalmazhatjuk a nézeteket, mint a táblákat. Mivel a nézetek az AB-ban nem tárolódnak, úgy is lehet rájuk tekinteni, mint virtuális táblákra. Egy nézetet úgy is értelmezhetjük, mint egy tárolt SELECT parancs eredménytábláját, mely a nézet mindegyik megnyitásakor újból létrejön.

A nézet létrehozása:

CREATE VIEW v_customer_sales_rgn AS

     SELECT surname, sales_region  FROM customer;

A nézeteket a parancsokban úgy alkalmazhatjuk, mint a táblákat:

            SELECT * FROM v_customer_sales_rgn;

A nézetet akkor nevezzünk egyszerűnek, ha az nem tartalmaz:

• agregáló függvényt;
• felhasználó által létrehozott függvényt;
• beágyazott lekérdezést;
• lekérdezések unióját.

A nézet nem tartalmazhat

• ORDER BY
• FOR UPDATE

záradékokat.

Az egyszerű nézet INSERT, DELETE, UPDATE parancsokkal módosítható. A módosítások azokban az táblákban rögzítődnek, amelyek alapján a nézetet szerkesztették.

A nézeteket kényelmes alkalmazni az összetett lekérdezések helyet:

    CREATE OR REPLACE VIEW emp_dept_view AS

                        SELECT e.emp_no, e.e_name, d.dept_no, d.d_name

                                   FROM emp e, dept d

                                   WHERE e.dept_no=d.dept_no;

és

            SELECT * FROM emp_dept_view;

Előfordulhat, hogy a módosítások után a táblában olyan változások történnek, hogy a tábla egyes sorai már nem kerülnek be a következő VIEW-lekérdezésbe, mivel nem felelnek meg a WHERE-feltételnek.  Ilyen módosításokat meg lehet akadályozni, ha a nézet létrehozásának parancsa a WITH CHECK OPTION megszorítást tartalmazza. 

A nézetet az ALTER paranccsal nem lehet módosítani. A módosítást csak úgy lehet végrehajtani, hogy először töröljük a nézetet

DROP VIEW test;

és utána újból létrehozzuk a nézetet, de már a módosított változatában.   

A nézeteket biztonsági okokból is szokták alkalmazni. Például, ha egy felhasználónak egy tábla csak bizonyos oszlopait vagy sorait engedélyezzük elérni, akkor egy olyan nézetet hozunk létre, mely csak a megengedett adatokat tartalmazza, a felhasználó, pedig megkapja a megfelelő privilégiumokat erre a nézetre.

Szinonimák

Szinonima – egy olyan név, melyet táblához vagy nézethez lehet hozzárendelni, és az eredeti név helyett használni.

 A szinonima létrehozása után a parancsokban az objektum eredeti neve helyett a szinonimával lehet rá hivatkozni. 

            Léteznek személyes és nyilvános (PUBLIC) szinonimák. A személyes szinonimákat egy-egy felhasználó hozza létre, és a továbbiakban csak ő használhatja.

CONNECT test/test

            CREATE SYNONIM emp FOR scott.emp

A Test nevű felhasználó az emp táblára, melynek a tulajdonosa scott, létrehozott emp nevű szinonimát.

            A nyilvános szinonimákat csak az AB adminisztrátor hozhat létre, és az ilyen szinonimát mindegyik olyan felhasználó alkalmazhatja, aki megfelelő privilégiummal rendelkezik

            CREATE PUBLIC SYNONIM emp FOR scott.emp

A szinonima törlése:

            DROP SYNONIM emp;

A távoli AB objektumára is lehet szinonimát létrehozni

            CREATE PUBLIC SYNONIM synon FOR felh.aru@AB_1

A synon nevű nyilvános szinonimát hoztunk létre a távoli AB_1 AB-ban a felh.aru táblára.