DDL (Data Definition Language) ALTER PROCEDURE ALTER TABLE ANALYZE (a teljesítmény statisztikáját gyűjti) ALTER TABLE ADD CONSTRAINT CREATE TABLE

DDL (Data Definition Language) • ALTER PROCEDURE • ALTER TABLE • ANALYZE (a teljesítmény statisztikáját gyűjti) • ALTER TABLE ADD CONSTRAINT • CREATE TABLE • CREATE INDEX • DROP INDEX • DROP TABLE • GRANT • TRUNCATE • REVOKE 1

DML (Data Manipulation Language) • INSERT • DELETE • UPDATE • SELECT • COMMIT WORK • ROLLBACK

2

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, 3

• 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 karakterkészletben. 4

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.

5

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.

6

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

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. 8

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);

9

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; ALTER TABLE testtab DROP (col1; col2) 10

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 11

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, 12

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

13

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ó. 14

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 15

• CHECK • ENABLE VALIDATE • DISABLE Az oszlopmegszorítások: • NULL • NOT NULL •… A többi oszlopmegszorítások a táblamegszorításokkal azonosak.

16

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

17

Beépített függvények Típusok konvertálása • TO_CHAR() vagy TO_CHAR(, ) • TO_CHAR() vagy TO_CHAR(, ) • TO_DATE(<string>) vagy TO_DATE(<string>, ) • TO_NUMBER(<string>)

18

Naptári függvények • ADD_MONTHS(, n), a dátumhoz hozzáad n hónapot • ROUND() ROUND(, ) Numerikus függvények • ABS(n) • COS(n) • COSH(n) • SIN(n) • SINH(n) • TAN(n) • TANH(n) 19

• LN(n) • LOG(, n) - 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) 20

• 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) 21

• 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’

22

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.

23

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. 24

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;

25

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.

26

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.

27

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%’;

28

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

29

Táblák módosítása DML • 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

30

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.

31

Példa. 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ásaINSERT INTO testtab (col1, col2) VALUES (’szöveg’, NULL); vagy INSERT INTO testtab (col1) VALUES (’szöveg’);

32

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;

33

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%’;

34

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;

35

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)); 36

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 TRUNCATE paranccsal: TRUNCATE TABLE emp_copy; A parancs végrehajtása után a tábla struktúrája megmarad, de az egyetlen sort sem fog tartalmazni.

37

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.

38

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.

39

Példa. SAVEPOINT MyPoint; 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. 40

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. 41

SELECT emp_no, e_name FROM emp; EMP_NO E_NAME -------------------------7369 smith 7499 allen 7566 jones 7788 scott 7654 martin 5 rows selected.

42

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 43

engedélyezett műveteket végrehajtatja a felsorolt ABobjektumaival. 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 44

• SELECT • UPDATE •… A következő táblázat tartalmazza egyes privilégiumok és ABobjektumok közti lehetséges kapcsolatokat. Privilégium Table View Sequence Procedure + + ALTER + + DELETE + EXECUTE + INDEX + + INSERT REFERENCES + + + + SELECT + + UPDATE 45

A INSERT, UPDATE és REFERENCES privilégiumokat lehet a tábla egyes oszlopaihoz kötni. Ha meg akarjuk tudni, hogy milyen rendszer privilégiumokat lehet alkalmazni, végre kell hajtani a következő parancsot: SELECT UNIQUE privilege FROM dba_sys_priv;

46

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]; 47

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;

48

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;

49

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. 50

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.

51

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 felh_1; GRANT kozos TO felh_2; akkor rendelkezni fognak az összes kozos szerepkörhöz tartozó jogosultságokkal. 52

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

53

• CREATE CLUSTER • CREATE PROCEDURE • CREATE SEQUENCE • CREATE TABLE 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.

54

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 55

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;

56

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.

57

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.

58

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) ; 59

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

60

ALTER TABLE sample_3 DISABLE 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; 61

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; 62

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; 63

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. 64

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. 65

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; 66

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.

67

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. 68

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); 69

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 70

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,... Figyelembe kell venni, hogy a ROLLBACK parancs a szekvencia kezdő állapotát nem állítja vissza, mely a tranzakció elején volt aktuális.

71

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;

72

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.

73

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:

74

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);

75

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);

76

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;

77

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: 78

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.

79

Az egyszerű nézet INSERT, DELETE, UPDATE parancsokkal módosítható. A módosítások azokban a 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 80

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 81

tartalmazza, a felhasználó, pedig megkapja a megfelelő privilégiumokat erre a nézetre.

82

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. 83

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. 84