User Defined Type (UDT) adattípusok az Oracle-ben ------------------------------------------------- Objektum típusok ================ CREATE OR REPLACE TYPE cim_typ AS OBJECT (utca VARCHAR2(40), varos VARCHAR2(30), orszag VARCHAR2(20), irszam NUMBER(4) ); CREATE OR REPLACE TYPE szemely_typ AS OBJECT (nev VARCHAR2(40), cim cim_typ ); Ahhoz, hogy más is használhassa az objektum típust (és annak metódusait), végrehajtási jogot kell rá adnunk: GRANT EXECUTE ON cim_typ TO public; GRANT EXECUTE ON szemely_typ TO public; Annak, aki hivatkozik az objektum típusra, annak tulajdonosát is meg kell adnia NIKOVITS.cim_typ módon vagy szinonimát is létrehozhat rá. CREATE synonym cim_typ FOR nikovits.cim_typ; Olyan tábla létrehozása, amelynek objektum típusú oszlopa is van: CREATE TABLE vevo (vevo_azon NUMBER(8), szemely szemely_typ); INSERT INTO vevo VALUES (1, szemely_typ('Kiss Gizi', cim_typ('Kicsi', 'Bp', 'Magyar', 1234))); INSERT INTO vevo VALUES (2, szemely_typ('Nagy Ede', cim_typ('Nagy', 'Arad', 'Romania', 4321))); A beszúráskor a fenti utasításban a típus konstruktorát kellett használnunk. SQLPLUS-ban a következőképpen nézhetjük meg az objektum szerkezetét: SQL> SET DESCRIBE DEPTH 3 SQL> DESCRIBE vevo Név Üres? Típus ----------------------------------------- -------- ---------------------------- VEVO_AZON NUMBER(8) SZEMELY SZEMELY_TYP NEV VARCHAR2(40) CIM CIM_TYP UTCA VARCHAR2(40) VAROS VARCHAR2(30) ORSZAG VARCHAR2(20) IRSZAM NUMBER(4) A tábla lekérdezése: Mennyi az irányítószáma az 1-es azonosítójú vevőnek? SELECT v.szemely.cim.irszam FROM vevo v WHERE vevo_azon=1; SZEMELY.CIM.IRSZAM ------------------ 1234 Megj: A korrelációs változó (v) használata nélkül nem tudjuk lekérdezni. Adjuk meg a személy összes adatát SELECT v.szemely FROM vevo v WHERE vevo_azon=1; SZEMELY(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ---------------------------------------------------------------- SZEMELY_TYP('Kiss Gizi', CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234)) Megj: a fenti lekérdezés ereménye is objektum típusú lesz, így azt akár rögtön be is szúrhatnánk egy megfelelő táblába Adjuk meg az aradi személyek neveit SELECT v.szemely.nev FROM vevo v WHERE v.szemely.cim.varos='Arad'; A gyorsabb keresés céljából indexet is létrehozhatunk az attribútumra hasonló módon, mint egy oszlopra: CREATE INDEX vevo_varos ON vevo(szemely.cim.varos); Vajon mit látunk az indexről a katalógusban? SELECT index_name, table_name, column_name FROM user_ind_columns WHERE index_name = 'VEVO_VAROS'; INDEX_NAME TABLE_NAME COLUMN_NAME -------------------- --------------- ----------------------- VEVO_VAROS VEVO "SZEMELY"."CIM"."VAROS" Vagy akár összetett (több oszlopos) indexet is létrehozhatunk: CREATE INDEX vevo_nev_varos ON vevo(szemely.nev, szemely.cim.varos); Módosítani is tudjuk az objektumot (akár a belsejében levő adatot is) hasonló hivatkozással, mint a SELECT-ben láttuk. Módosítsuk Kiss Gizi irányítószámát. UPDATE VEVO v SET v.szemely.cim.irszam=5678 WHERE v.szemely.nev='Kiss Gizi'; Egy típus eldobása előtt az összes táblát és típust el kell dobnunk, amelyek használják az eldobni kívánt típust. -- DROP TYPE CIM_TYP; Objektum-relációs és relációs táblák átalakítása egymásba. Az objektum-relációs táblát könnyen átalakíthatjuk relációssá: CREATE TABLE vevo_rel(vevo_azon, nev, utca, varos, orszag, irszam) AS SELECT v.vevo_azon, v.szemely.nev, v.szemely.cim.utca, v.szemely.cim.varos, v.szemely.cim.orszag, v.szemely.cim.irszam FROM vevo v; De azt is megtehetjük, hogy nem hozunk létre újabb táblát, hanem csak egy nézetet, amely már hagyományos relációs szerkezetben mutatja az adatokat. CREATE VIEW vevo_v(vevo_azon, nev, utca, varos, orszag, irszam) AS SELECT v.vevo_azon, v.szemely.nev, v.szemely.cim.utca, v.szemely.cim.varos, v.szemely.cim.orszag, v.szemely.cim.irszam FROM vevo v; Sőt a hagyományos nézet módosításával módosíthatjuk is az objektum-relációs táblát UPDATE vevo_v SET utca='Kicsike' WHERE irszam=1234; A beszúrás azonban már nem lehetséges íly módon. Ennek az az oka, hogy a struktúra szétszedése különálló attribútumokra mindig egyértelmű, az összerakás azonban nem. -- INSERT INTO vevo_v VALUES(3, 'Gipsz Jakab', 'Akacos', 'Bp', 'Magyar', 2345) A fordított irányú átalakítás módja. (Most csak a nézetet adjuk meg, a tábla létrehozás ugyanígy működik, csupán a VIEW kulcsszó helyett TABLE-t kell megadnunk.) CREATE VIEW vevo_ov(vevo_azon, szemely) AS SELECT vevo_azon, szemely_typ(nev, cim_typ(utca,varos, orszag, irszam)) FROM vevo_rel; Ebbe a nézetbe be is szúrhatunk sorokat, és módosíthatjuk is, aminek eredménye a vevo_rel relációs táblában jelenik meg. INSERT INTO vevo_ov VALUES (3, szemely_typ('Gipsz Jakab', cim_typ('Patak', 'Kassa', 'Szlovak', 2345))); UPDATE vevo_ov v SET v.szemely.cim.utca='Pataki' WHERE v.vevo_azon=3; Ha a fenti módon létrehozunk egy táblát, akkor jó lenne utólag kideríteni minden információt az objektumtípusokról, amik a tábla oszlopaiban előfordulhatnak. Ha például kiváncsiak vagyunk a vevo tábla szerkezetére, használhatjuk a fenti describe-ot is, de nyilván a describe is a katalógusból veszi az információkat. Nézzük meg, milyen oszlopai vannak a táblának. SELECT column_name, data_type, data_type_owner FROM user_tab_columns WHERE table_name='VEVO'; COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER ------------------------------ ------------------------- ---------------- VEVO_AZON NUMBER SZEMELY SZEMELY_TYP NIKOVITS A szemely_typ egy úgynevezett 'USER DEFINED' típus. Hogyan tudjuk a rendszerkatalógusból megnézni az információit? Egyelőre még azt sem tudhatjuk, hogy objektum típus, mert látni fogjuk, hogy vannak másféle user defined 'TYPE'-ok is. A következő katalógusokból szinte minden fontos információt megtudhatunk. DBA_OBJECTS, DBA_TYPES, DBA_TYPE_ATTRS, DBA_TYPE_METHODS, DBA_METHOD_PARAMS, DBA_METHOD_RESULTS, DBA_TYPE_VERSIONS, DBA_OBJ_COLATTRS (mely tábláknak van objektum oszlopa) SELECT owner, object_type FROM dba_objects WHERE object_name='SZEMELY_TYP'; OWNER OBJECT_TYPE ------------------------------ ------------------- NIKOVITS TYPE SELECT typecode, attributes FROM dba_types WHERE type_name='SZEMELY_TYP'; TYPECODE ATTRIBUTES ------------------------------ ---------- OBJECT 2 Látjuk, hogy objektum, aminek két attribútuma van. Nézzük meg, hogy mik az attribútumai. SELECT attr_no, attr_name, attr_type_name, attr_type_owner FROM dba_type_attrs WHERE type_name='SZEMELY_TYP' AND owner='NIKOVITS' ORDER BY 1; ATTR_NO ATTR_NAME ATTR_TYPE_NAME ATTR_TYPE_OWNER ---------- ------------------------------ ------------------------------ --------------------------- 1 NEV VARCHAR2 2 CIM CIM_TYP NIKOVITS A második attribútum még további vizsgálatot igényel az előzőekhez hasonlóan. SELECT owner, object_type FROM dba_objects WHERE object_name='CIM_TYP'; SELECT typecode, attributes FROM dba_types WHERE type_name='CIM_TYP'; SELECT attr_no, attr_name, attr_type_name, attr_type_owner FROM dba_type_attrs WHERE type_name='CIM_TYP' AND owner='NIKOVITS' ORDER BY 1; ATTR_NO ATTR_NAME ATTR_TYPE_NAME ---------- ------------------------------ ------------------ 1 UTCA VARCHAR2 2 VAROS VARCHAR2 3 ORSZAG VARCHAR2 4 IRSZAM NUMBER Ezek már mind ismerős típusok, így készen vagyunk. Kollekció: ---------- Dinamikus tömb (VARRAY) ======================= CREATE OR REPLACE TYPE cimlista_var AS VARRAY(5) OF VARCHAR2(20); CREATE TABLE kolcsonzes2(nev VARCHAR2(20), datum date, konyvek cimlista_var); INSERT INTO kolcsonzes2 VALUES('Bolcs Leo', to_date('2007-11-01', 'yyyy-mm-dd'), cimlista_var('Biblia', 'Talmud', 'Mahabharata', 'Odusszeia')); INSERT INTO kolcsonzes2 VALUES('Okos Akos', to_date('2007-11-02', 'yyyy-mm-dd'), cimlista_var('Rokonok', 'Fekete Gyemantok')); A maximális elemszámot később módosíthatjuk: ALTER TYPE ... MODIFY LIMIT SELECT konyvek FROM kolcsonzes2 WHERE nev like 'Okos%'; KONYVEK ------------------------------------------- CIMLISTA_VAR('Rokonok', 'Fekete Gyemantok') A lekérdezés eredménye is dinamikus tömb lesz. A TABLE függvénnyel táblává is alakíthatjuk a tömböt: SELECT * FROM TABLE(SELECT konyvek FROM kolcsonzes2 k WHERE nev like 'Okos%'); COLUMN_VALUE ----------------- Rokonok Fekete Gyemantok Ha a dinamikus tömböt táblaként szeretnénk használni, mint az iménti lekérdezésben, akkor valahogy hivatkoznunk kell annak oszlopára. Ha objektumokból állna a dinamikus tömb (lásd lejjebb), akkor nincs probléma, hiszen az objektumnak vannak attribútumai, és ezek nevét használhatjuk. Ha viszont egyszerű típusú elemekből áll a tömb, akkor nincs oszlopnév. Ilyenkor használható a COLUMN_VALUE. SELECT nev, datum, t.column_value FROM kolcsonzes2 k, TABLE(k.konyvek) t; NEV DATUM COLUMN_VALUE -------------------- ---------- ---------------- Bolcs Leo 2007-11-01 Biblia Bolcs Leo 2007-11-01 Talmud Bolcs Leo 2007-11-01 Mahabharata Bolcs Leo 2007-11-01 Odusszeia Okos Akos 2007-11-02 Rokonok Okos Akos 2007-11-02 Fekete Gyemantok Nem kell WHERE feltétel, anélkül is csak a megfelelő párosítások lesznek az eredményben. PL/SQL-ben ciklusban is feldolgozható a dinamikus tömb, hasonlóan a plsql-beli tömbhöz (TABLE OF). A plsql-beli TABLE OF egy plsql nyelvben használható tömb típus, a VARRAY (és a későbbi NESTED TABLE) ezzel szemben SQL nyelv-beli típus, vagyis plsql-en kívül is használható. LIMIT -> maximális elemszám COUNT -> aktuális elemszám (FIRST, LAST, NEXT(i) ...stb.) NULL is lehet az elemek közt: INSERT INTO kolcsonzes2 VALUES('Fura Feri', to_date('2007-11-03', 'yyyy-mm-dd'), cimlista_var('Zabhegyezo', NULL, 'Szeretetkonyv')); BEGIN FOR rec IN (SELECT * FROM kolcsonzes2) LOOP dbms_output.put_line(rec.nev||' -> könyvek: '||rec.konyvek.COUNT||'/'||rec.konyvek.LIMIT); FOR i IN 1..rec.konyvek.COUNT LOOP dbms_output.put_line(TO_CHAR(i, '999')||' '||rec.konyvek(i)); END LOOP; END LOOP; END; Bolcs Leo -> könyvek: 4/5 1 Biblia 2 Talmud 3 Mahabharata 4 Odusszeia Okos Akos -> könyvek: 2/5 1 Rokonok 2 Fekete Gyemantok Fura Feri -> könyvek: 3/5 1 Zabhegyezo 2 3 Szeretetkonyv Bonyolultabb eleme is lehet a tömbnek, pl. objektum: CREATE OR REPLACE TYPE szemely_lista_var AS VARRAY(10) OF szemely_typ; CREATE TABLE csapat(nev VARCHAR2(10), tagok szemely_lista_var); INSERT INTO csapat VALUES('Fradi', szemely_lista_var(szemely_typ('Albert Flori', cim_typ('Kicsi', 'Bp', 'Magyar', 1234)), szemely_typ('Novak Dezso', cim_typ('Nagy', 'Vac', 'Magyar', 4321)) ) ); INSERT INTO csapat VALUES('Ujpest', szemely_lista_var(szemely_typ('Bene Ferenc', cim_typ('Lakatos', 'Tata', 'Magyar', 1234)), szemely_typ('Fazekas LAszlo', cim_typ('Tatai', 'Bp', 'Magyar', 4321)) ) ); Nézzünk néhány lekérdezést arra, hogy hogyan férhetünk hozzá az egyes részekhez. SELECT tagok FROM csapat cs WHERE nev='Fradi'; -- 1 dinamikus tömböt kapunk vissza TAGOK(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ------------------------------------------------------------------------------------- SZEMELY_LISTA_VAR(SZEMELY_TYP('Albert Flori', CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234)), SZEMELY_TYP('Novak Dezso', CIM_TYP('Nagy', 'Vac', 'Magyar', 4321))) SELECT nev, cim FROM TABLE(SELECT tagok FROM csapat cs WHERE nev='Fradi'); -- a dinamikus tömb elemeit kapjuk vissza külön sorokban NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) ----------------------------------------------------- Albert Flori CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234) Novak Dezso CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Adjuk meg egy csapat tagjainak nevét: SELECT nev FROM TABLE(SELECT tagok FROM csapat cs WHERE nev='Fradi'); NEV ------------ Albert Flori Novak Dezso Adjuk meg egy csapat tagjainak városait (a címükből kivéve) SELECT t1.cim.varos FROM TABLE(SELECT tagok FROM csapat cs WHERE nev='Fradi') t1; CIM.VAROS --------- Bp Vac Adjuk meg egy csapat tagjainak nevét és városát SELECT nev, t1.cim.varos FROM TABLE(SELECT tagok FROM csapat cs WHERE nev='Fradi') t1; -- A nev előtt nem kötelező a minősítés (t1.nev), de oda is írhatjuk. NEV CIM.VAROS ---------------------------------------- ---------- Albert Flori Bp Novak Dezso Vac SELECT cs.nev, t.nev FROM csapat cs, TABLE(cs.tagok) t; NEV NEV ---------- --------------- Fradi Albert Flori Fradi Novak Dezso Ujpest Bene Ferenc Ujpest Fazekas LAszlo Információk a katalógusból: DBA_COLL_TYPES, DBA_VARRAYS SELECT column_name, data_type, data_type_owner FROM user_tab_columns WHERE table_name='CSAPAT'; COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER ------------------------------ -------------------- --------------- NEV VARCHAR2 TAGOK SZEMELY_LISTA_VAR NIKOVITS SELECT owner, object_type FROM dba_objects WHERE object_name='SZEMELY_LISTA_VAR'; SELECT typecode, attributes FROM dba_types WHERE type_name='SZEMELY_LISTA_VAR'; TYPECODE ATTRIBUTES ------------------------------ ---------- COLLECTION 0 Ebből látszik, hogy kollekció, ezért most itt keresünk tovább -> DBA_COLL_TYPES SELECT coll_type, upper_bound, elem_type_name, elem_type_owner FROM dba_coll_types WHERE type_name='SZEMELY_LISTA_VAR'; COLL_TYPE UPPER_BOUND ELEM_TYPE_NAME ELEM_TYPE_OWNER ----------------- ----------- ------------------------ --------------- VARYING ARRAY 10 SZEMELY_TYP NIKOVITS És innen a már megismert módon haladuk tovább. Beágyazott tábla (NESTED TABLE) =============================== Hasonló a dinamikus tömbhöz, de azért vannak különbségek CREATE OR REPLACE TYPE szemelyek_nt AS TABLE OF szemely_typ; CREATE TABLE projekt2(nev VARCHAR2(15), resztvevok szemelyek_nt) TABLESPACE users STORAGE (INITIAL 200K) NESTED TABLE resztvevok STORE AS resztvevok_ntab (TABLESPACE users STORAGE (INITIAL 100K)) ; A beágyazott tábla egy külön táblában lesz tárolva, aminek a megadása kötelező!!! A fizikai tárolással kapcsolatos paraméterek (pl. táblatér) külön-külön megadhatók a két táblára. Ebből is látszik, hogy a beágyazott tábla adatai nem a fő tábla soraiban lesznek tárolva, ellentétben a dinamikus tömbökkel. A beágyazott táblába akármennyi sort beszúrhatunk, a dinamikus tömbnek van felső határa!!! INSERT INTO projekt2 VALUES('Utepites', szemelyek_nt(szemely_typ('Kovacs Attila', cim_typ('Kicsi', 'Bp', 'Magyar', 1234)), szemely_typ('Kiss Istvan', cim_typ('Nagy', 'Vac', 'Magyar', 4321)) ) ); INSERT INTO projekt2 VALUES('Lakopark', szemelyek_nt(szemely_typ('Kiss Istvan', cim_typ('Nagy', 'Vac', 'Magyar', 4321)), szemely_typ('Takacs Bela', cim_typ('Rovid', 'Szeged', 'Magyar', 3456)) ) ); A beágyazott táblák lekérdezésének szintaxisa hasonló a dinamikus tömbökéhez. Az alábbi lekérdezés 1 sorral, abban beágyazott tábla típussal tér vissza. SELECT resztvevok FROM projekt2 WHERE nev='Utepites'; RESZTVEVOK(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ------------------------------------------------------------------------------------ SZEMELYEK_NT(SZEMELY_TYP('Kovacs Attila', CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234)), SZEMELY_TYP('Kiss Istvan', CIM_TYP('Nagy', 'Vac', 'Magyar', 4321)) Most is táblává alakíthatjuk, és ekkor két sort kapunk vissza: SELECT * FROM TABLE(SELECT resztvevok FROM projekt2 WHERE nev='Utepites'); NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) ----------------- ------------------------------------------ Kovacs Attila CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234) Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Ha egy konkrét beágyazott tábla adatait szeretném lekérdezni, akkor az alábbi szintaxisát kell használni a TABLE függvénynek. Ekkor a zárójelbeli lekérdezés egyetlen sort adhat csak vissza többet nem. SELECT nev, t1.cim.varos FROM TABLE(SELECT resztvevok FROM projekt2 WHERE nev='Utepites') t1; NEV CIM.VAROS ---------------------------------------- --------- Kovacs Attila Bp Kiss Istvan Vac Ha pedig az összes beágyazott tábla adatait szeretném megkapni, akkor az alábbi a szintaxis. Ekkor viszont a külső tábla (p) használata nem nélkülözhető, amit implicit módon a megfelelő sorokhoz fog kapcsolni a rendszer. SELECT p.nev, t.nev FROM projekt2 p, TABLE(p.resztvevok) t; NEV NEV --------------- --------------- Utepites Kovacs Attila Utepites Kiss Istvan Lakopark Kiss Istvan Lakopark Takacs Bela Most sem kell WHERE feltétel, anélkül is csak a megfelelő párosításokat adja vissza. Most azonban a beágyazott táblák tartalmát módosíthatjuk insert, update vagy delete-tel, amit dinamikus tömbök esetén nem tehetünk meg!!! Vegyük fel az útépítésre Bölcs Leót. A zárójelen belüli WHERE feltétel megadása nélkül hibás lenne az utasítás, vagyis nem tudunk egyszerre több beágyazott táblába beszúrni. INSERT INTO TABLE (SELECT resztvevok FROM projekt2 WHERE nev='Utepites') VALUES(szemely_typ('Bolcs Leo', cim_typ('Rovid', 'Pecs', 'Magyar', 3456))); SELECT p.nev, t.nev, t.cim FROM projekt2 p, TABLE(p.resztvevok) t; NEV NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) --------------- --------------- -------------------------------------------- Utepites Bolcs Leo CIM_TYP('Rovid', 'Pecs', 'Magyar', 3456) Utepites Kovacs Attila CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234) Utepites Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Takacs Bela CIM_TYP('Rovid', 'Szeged', 'Magyar', 3456) Módosítsuk Bolcs Leo címét Debrecenre. A zárójelen belüli WHERE feltétel most is kötelező. Ha a külső WHERE feltételt hagynánk el, akkor az útépítésen mindenki címe módosulna. UPDATE TABLE (SELECT resztvevok FROM projekt2 WHERE nev='Utepites') r SET r.cim.varos='Debrecen' WHERE r.nev='Bolcs Leo'; NEV NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) --------------- --------------- --------------------------------------------- Utepites Bolcs Leo CIM_TYP('Rovid', 'Debrecen', 'Magyar', 3456) Utepites Kovacs Attila CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234) Utepites Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Takacs Bela CIM_TYP('Rovid', 'Szeged', 'Magyar', 3456) Végül töröljük ki Bölcs Leót. DELETE FROM TABLE (SELECT resztvevok FROM projekt2 WHERE nev='Utepites') r WHERE r.nev='Bolcs Leo'; NEV NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) --------------- --------------- ------------------------------------------ Utepites Kovacs Attila CIM_TYP('Kicsi', 'Bp', 'Magyar', 1234) Utepites Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Kiss Istvan CIM_TYP('Nagy', 'Vac', 'Magyar', 4321) Lakopark Takacs Bela CIM_TYP('Rovid', 'Szeged', 'Magyar', 3456) Információk a katalógusból: A DESCRIBE most is kiírja a szerkezetet Név Üres? Típus ----------------------------------------- -------- ---------------- NEV VARCHAR2(15) RESZTVEVOK SZEMELYEK_NT NEV VARCHAR2(40) CIM CIM_TYP UTCA VARCHAR2(40) VAROS VARCHAR2(30) ORSZAG VARCHAR2(20) IRSZAM NUMBER(4) SELECT column_name, data_type, data_type_owner FROM user_tab_columns WHERE table_name='PROJEKT2'; COLUMN_NAME DATA_TYPE DATA_TYPE_OWNER ------------------------------ -------------------- --------------- NEV VARCHAR2 RESZTVEVOK SZEMELYEK_NT NIKOVITS SELECT owner, object_type FROM dba_objects WHERE object_name='SZEMELYEK_NT'; SELECT typecode, attributes FROM dba_types WHERE type_name='SZEMELYEK_NT'; TYPECODE ATTRIBUTES ------------------------------ ---------- COLLECTION 0 SELECT coll_type, upper_bound, elem_type_name, elem_type_owner FROM dba_coll_types WHERE type_name='SZEMELYEK_NT'; COLL_TYPE UPPER_BOUND ELEM_TYPE_NAME ELEM_TYPE_OWNER ---------- ----------- -------------- --------------- TABLE SZEMELY_TYP NIKOVITS További információk: Mely tábláknak van beágyazott tábla oszlopa, a tároló táblák, és azok oszlopai: DBA_NESTED_TABLES, DBA_NESTED_TABLE_COLS Dinamikus tömb és beágyazott tábla típusú oszlopok feltöltése adatokkal ----------------------------------------------------------------------- Amikor magunk soroljuk fel az adatokat, akkor ezt megtehetjük a konstruktor függvény neve után. Ha azonban egy lekérdezés adatait szeretnénk beszúrni, akkor a lekérdezés eredményéből tömb típust illetve dinamikus tömb típust kell létrehoznunk. Erre jó a CAST függvény, ami típusátalakítást végez. Pl. SELECT CAST(hiredate AS VARCHAR2(15)), CAST(sal AS VARCHAR2(10)) FROM emp; Dinamikus tömb és beágyazott tábla típust is létrehozhatunk a CAST(COLLECT()) és a CAST(MULTISET()) segítségével, lásd az alábbi példát. CREATE OR REPLACE TYPE v30_var AS VARRAY(20) OF VARCHAR2(30); CREATE OR REPLACE TYPE v30_tab AS TABLE OF VARCHAR2(30); CREATE TABLE tomb_proba(azon NUMBER(4), nevek_var v30_var, nevek_nt v30_tab) NESTED TABLE nevek_nt STORE AS v30_ntab; INSERT INTO tomb_proba(azon, nevek_var) SELECT 1, CAST(COLLECT(ename) AS v30_var) FROM emp WHERE sal > 2500; INSERT INTO tomb_proba(azon, nevek_nt) SELECT 2, CAST(MULTISET(SELECT ename FROM emp WHERE sal > 2000) AS v30_tab) FROM dual; Beágyazott táblára használható függvények (néhány dinamikus tömbre is működik). CARDINALITY - számosságot adja vissza SELECT CARDINALITY(resztvevok) FROM projekt2; MULTISET EXCEPT [ALL | DISTINCT] MULTISET UNION [ALL | DISTINCT] MULTISET INTERSECT [ALL | DISTINCT] Példák: SELECT CAST(t1.nevek_var AS v30_tab) MULTISET INTERSECT t2.nevek_nt FROM tomb_proba t1, tomb_proba t2 WHERE t1.azon=1 AND t2.azon=2; -------------------------------------------------- V30_TAB('JONES', 'BLAKE', 'SCOTT', 'KING', 'FORD') SELECT CAST(t1.nevek_var AS v30_tab) MULTISET UNION DISTINCT t2.nevek_nt FROM tomb_proba t1, tomb_proba t2 WHERE t1.azon=1 AND t2.azon=2 ----------------------------------------------------------- V30_TAB('JONES', 'BLAKE', 'SCOTT', 'KING', 'FORD', 'CLARK') SELECT t2.nevek_nt MULTISET EXCEPT CAST(t1.nevek_var AS v30_tab) FROM tomb_proba t1, tomb_proba t2 WHERE t1.azon=1 AND t2.azon=2 ----------------- V30_TAB('CLARK') Beágyazott táblákra is lehet indexeket, constrainteket létrehozni, lehet kombinálni a beágyazott, partícionált, index-szervezett tulajdonságokat, de azért vannak ezekre vonatkozó korlátozások. ... stb. a többit -> lásd a doksiban (elég azt tudni, ami ebben az irományban van :)) Metódusok --------- Az objektumoknak nem csak attribútumai, hanem metódusai is lehetnek. A metódusok túlterhelhetők (overload), lásd az alábbi példát. CREATE OR REPLACE TYPE PointType AS OBJECT (x NUMBER, y NUMBER ); CREATE OR REPLACE TYPE LineType AS OBJECT ( end1 PointType, end2 PointType, hossz NUMBER, MEMBER FUNCTION getLength(scale IN NUMBER) RETURN NUMBER, MEMBER FUNCTION getLength RETURN NUMBER, MEMBER PROCEDURE setLength, PRAGMA RESTRICT_REFERENCES(getLength, WNDS) ); CREATE OR REPLACE TYPE BODY LineType AS MEMBER FUNCTION getLength(scale NUMBER) RETURN NUMBER IS BEGIN RETURN scale * SQRT((SELF.end1.x-SELF.end2.x)*(SELF.end1.x-SELF.end2.x) + (SELF.end1.y-SELF.end2.y)*(SELF.end1.y-SELF.end2.y)); END; MEMBER FUNCTION getLength RETURN NUMBER IS BEGIN RETURN SELF.hossz; END; MEMBER PROCEDURE setLength IS BEGIN SELF.hossz := SQRT((SELF.end1.x-SELF.end2.x)*(SELF.end1.x-SELF.end2.x) + (SELF.end1.y-SELF.end2.y)*(SELF.end1.y-SELF.end2.y)); END; END; CREATE TABLE Lines (lineID INT, line LineType); INSERT INTO Lines VALUES(1, LineType(PointType(0.0, 0.0), PointType(3.0, 4.0),null)); INSERT INTO Lines VALUES(2, LineType(PointType(0.0, 0.0), PointType(6.0, 6.0),null)); A metódusok közül a függvényeket SQL utasításban is lehet használni, a procedúrát viszont csak plsql-ben, mint ahogy ez a hagyományos procedúrákkal is így van. DECLARE v_line LineType; BEGIN SELECT line INTO v_line FROM lines WHERE lineID=1; v_line.setLength; UPDATE lines SET line = v_line WHERE lineID=1; COMMIT; END; SELECT lineid, l.line.getLength() FROM lines l; LINEID L.LINE.GETLENGTH() ------ ------------------ 1 5 2 SELECT lineid, l.line.getLength(1) FROM lines l; LINEID L.LINE.GETLENGTH(1) ------ ------------------- 1 5 2 8,4853 A metódusokról is kaphatunk információkat a katalógusból, vagy a korábban már említett DBA_TYPE_METHODS, DBA_METHOD_PARAMS, DBA_METHOD_RESULTS szótárakból vagy pedig az alábbi sokkal kényelmesebb módon. SELECT text FROM dba_type_versions WHERE type_name='LINETYPE' ORDER BY line; TEXT ------------------------------------------------------------- TYPE LineType AS OBJECT ( end1 PointType, end2 PointType, hossz NUMBER, MEMBER FUNCTION getLength(scale IN NUMBER) RETURN NUMBER, MEMBER FUNCTION getLength RETURN NUMBER, MEMBER PROCEDURE setLength, PRAGMA RESTRICT_REFERENCES(getLength, WNDS) ); Néhány további információ az objektumokról és metódusokról ---------------------------------------------------------- Öröklés: CREATE TYPE Address_t AS OBJECT(...) NOT INSTANTIABLE NOT FINAL; CREATE TYPE USAddress_t UNDER Address_t(...); Final -> nem lehet örökölni Egy metódus a következő kategóriákba eshet: MEMBER - csak konkrét objektumra alkalmazható, van egy implicit SELF paramétere STATIC - nem kell hozzá példány, hanem típusnév.f1 formában hívható COMPARISON - MAP (egy számmal tér vissza) vagy ORDER (két objektumot hasonlít össze) Példa az utóbbi kettőre: CREATE TYPE rectangle_typ AS OBJECT ( len NUMBER, wid NUMBER, MAP MEMBER FUNCTION area RETURN NUMBER ); CREATE TYPE BODY rectangle_typ AS MAP MEMBER FUNCTION area RETURN NUMBER IS BEGIN RETURN len * wid; END area; END; CREATE TYPE location_typ AS OBJECT ( building_no NUMBER, city VARCHAR2(40), ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER ); CREATE TYPE BODY location_typ AS ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER IS BEGIN IF building_no < l.building_no THEN RETURN -1; -- any negative number will do ELSIF building_no > l.building_no THEN RETURN 1; -- any positive number will do ELSE RETURN 0; END IF; END; END; A metódusokra szintén megadható a FINAL. Ez azt jelenti, hogy az altípus nem írhatja felül őket. CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), FINAL MAP MEMBER FUNCTION get_idno RETURN NUMBER) NOT FINAL; Objektumtábla ============= DBA_OBJECT_TABLES, DBA_REFS A korábbi objektumokat egy tábla oszlopába tettük bele. Létrehozhatunk olyan táblát is, ami csak az objektumokat tartalmazza, más oszlopot nem. Ilyen esetben úgynevezett sorobjektumok lesznek a táblában. A sorobjektumoknak van egy egyedi objektum azonosítója (OID), amit az Oracle ad nekik a sor létrehozásakor. Kicsit hasonlatos ez a korábbi ROWID-hez, amit szintén a rendszer adott (azáltal, hogy fizikailag elhelyezte a sort). Használjuk a korábbi személy_typ típust objektumtábla létrehozására. CREATE TABLE szemely_ot OF szemely_typ; INSERT INTO szemely_ot VALUES (szemely_typ('Albert Flori', cim_typ('Kicsi', 'Bp', 'Magyar', 1234))); INSERT INTO szemely_ot VALUES (szemely_typ('Bene Feri', cim_typ('Nagy', 'Vac', 'Magyar', 4321))); A szemely_typ attribútumaira úgy hivatkozhatunk, mintha a tábla oszlopai lennének. UPDATE szemely_ot sz SET sz.cim.irszam=5678 WHERE nev='Albert Flori'; A sorváltozó használata kötelező!!! Lekérdezések a táblából: SELECT * FROM szemely_ot WHERE nev='Albert Flori'; NEV CIM(UTCA, VAROS, ORSZAG, IRSZAM) -------------- -------------------------------------- Albert Flori CIM_TYP('Kicsi', 'Bp', 'Magyar', 5678) A fenti lekérdezés 2 oszlopban adja vissza az eredményt. Ha objektum típusként szeretnénk az eredményt megkapni, akkor a VALUE függvényt kell használnunk, vagy az OBJECT_VALUE hivatkozást. SELECT VALUE(sz) FROM szemely_ot sz WHERE nev='Albert Flori'; VALUE(SZ)(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ------------------------------------------------------------------- SZEMELY_TYP('Albert Flori', CIM_TYP('Kicsi', 'Bp', 'Magyar', 5678)) SELECT OBJECT_VALUE FROM szemely_ot sz WHERE nev='Albert Flori'; VALUE(SZ)(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ------------------------------------------------------------------- SZEMELY_TYP('Albert Flori', CIM_TYP('Kicsi', 'Bp', 'Magyar', 5678)) Az objektum azonosítókat le is kérdezhetjük, az eredmény persze nem sokat mond: SELECT REF(sz) FROM szemely_ot sz; REF(SZ) -------------------------------------------------------------------------------------- 00002802095B6F2D9B82040762E040B59D66A575FD5B6F2D9B82030762E040B59D66A575FD0100F9C20000 00002802095B6F2D9B82050762E040B59D66A575FD5B6F2D9B82030762E040B59D66A575FD0100F9C20001 Viszont ezeket a hivatkozásokat pl. egy másik táblában eltárolhatjuk, vagy egy PLSQL-beli program változójába tehetjük, hogy később felhasználjuk. CREATE TABLE csapat2(nev VARCHAR2(10), vezeto REF szemely_typ SCOPE IS szemely_ot) A SCOPE azt korlátozza, hogy a referenciák csak a megadott táblabeli objektumra mutathatnak. INSERT INTO csapat2 SELECT 'Fradi', REF(sz) FROM szemely_ot sz WHERE nev='Albert Flori'; INSERT INTO csapat2 SELECT 'Ujpest', REF(sz) FROM szemely_ot sz WHERE nev='Bene Feri'; Majd a DEREF függvénnyel, aminek paramétere egy OID, megkaphatjuk az objektumot. SELECT DEREF(vezeto) FROM csapat2 WHERE nev = 'Fradi'; DEREF(VEZETO)(NEV, CIM(UTCA, VAROS, ORSZAG, IRSZAM)) ------------------------------------------------------------------- SZEMELY_TYP('Albert Flori', CIM_TYP('Kicsi', 'Bp', 'Magyar', 5678)) Lógó mutatók, amelyek nem mutatnak sehova. (IS DANGLING) Ezek nem azonosak a NULL mutatóval. INSERT INTO szemely_ot VALUES (szemely_typ('Detari Lajos', cim_typ('Kossuth', 'Baja', 'Magyar', 1357))); INSERT INTO csapat2 SELECT 'Honved', REF(sz) FROM szemely_ot sz WHERE nev='Detari Lajos'; INSERT INTO csapat2 VALUES ('Vasas', NULL); DELETE FROM szemely_ot WHERE nev='Detari Lajos'; SELECT nev, DEREF(vezeto) FROM csapat2 WHERE vezeto is null; NEV ----- Vasas SELECT nev, DEREF(vezeto) FROM csapat2 WHERE vezeto is dangling; NEV ------ Honved Visszatesszük a kitörölt sort, és helyreállítjuk a mutatót. INSERT INTO szemely_ot VALUES (szemely_typ('Detari Lajos', cim_typ('Kossuth', 'Baja', 'Magyar', 1357))); UPDATE csapat2 cs SET vezeto=(SELECT REF(sz) FROM szemely_ot sz WHERE nev='Detari Lajos') WHERE nev='Honved';