DDL utasítások használata

DDL utasítások használata táblák létrehozásához és kezeléséhez

A séma több adatbázis-objektum gyűjteménye,amelyeket sémaobjektumoknak nevezünk.Ezekhez az objektumokhoz a tulajdonos séma közvetlen hozzáférést biztosít.Az alábbi táblázat a sémaobjektumokat sorolja fel.

• Tábla – adatok tárolására

• Nézet – adatok kivetítése egy vagy több táblából a kívánt formátumban

• Sorozat – numerikus értékek előállítására

• Index -. a táblákon történő lekérdezések teljesítményének javítása

• Szinonim – egy objektum alternatív neve

Az adatbázis létrehozásának egyik első lépése a szervezet adatait tároló táblák létrehozása.Az adatbázis-tervezés magában foglalja a rendszer felhasználói követelményeinek meghatározását a különböző szervezeti rendszerek, például a rendelésbevitel, a készletgazdálkodás és a számlakövetelések számára. Az adatbázis méretétől és összetettségétől függetlenül minden adatbázis táblákból áll.

Tábla létrehozása

Az adatbázisban egy tábla létrehozásához a DBA-nak rendelkeznie kell bizonyos információkkal – a tábla neve, az oszlop neve, az oszlop adattípusai és az oszlopméretek. Mindezen információk később DDL-parancsok segítségével módosíthatók.

Táblaelnevezési konvenciók –

• A táblának választott névnek az alábbi szabványos szabályokat kell követnie:

• A névnek A-Z vagy a- betűvel kell kezdődnie.z

• Számokat és aláhúzásokat tartalmazhat

• Felső vagy kisbetűs lehet

• Legfeljebb 30 karakter hosszúságú lehet

.

• Nem használhatja egy másik, a sémában már létező objektum azonos nevét

• Nem lehet SQL fenntartott szó

A fenti irányelveket követve, Az ‘EMP85’ egy érvényes táblanév lehet.De a 85EMP nem.Hasonlóképpen az UPDATE sem választható táblanévnek, mivel ez egy SQL-fenntartott kulcsszó.

CREATE TABLE utasítás

A CREATE TABLE egy DDL utasítás, amely táblák létrehozására szolgál az adatbázisban.A tábla a CREATE TABLE szkript végrehajtását követően jön létre, és a továbbiakban készen áll az adatok tárolására.A felhasználónak rendelkeznie kell a CREATE TABLE rendszerjogosultsággal a tábla létrehozásához a saját sémájában.De ahhoz, hogy bármelyik felhasználó sémájában létrehozzon egy táblát, a felhasználónak rendelkeznie kell a CREATE ANY TABLE sémával.

Itt egy alapvető CREATE TABLE utasítás szintaxisa.Számos további kikötés lehet a tárolási specifikációk vagy szegmensértékek explicit megadásához.

CREATE TABLE table ( { column datatype ... | table_constraint} ... | table_constraint} ]...) 

A fenti szintaxisban a DEFAULT alapértelmezett értéket ad meg, amely az INSERT utasítás során használható, ha az oszlopot figyelmen kívül hagyjuk. Nem tartalmazhat hivatkozásokat más táblaoszlopokra vagy pszeudooszlopokra (CURRVAL, NEXTVAL, LEVEL és ROWNUM), kivéve a SYSDATE és USER oszlopokat, illetve a nem teljesen meghatározott dátumkonstansokat.

A korlátozások az oszlop- vagy táblaszinten opcionálisan definiált szabályok (erről később lesz szó ebben a fejezetben).Ezeket a szabályokat a táblán végzett bármely adatművelet (beszúrás, frissítés) során ellenőrzik, és hibát vetnek fel a művelet megszakítására, ha megsértik.

Az alábbi CREATE TABLE utasítás például létrehoz egy EMP_TEST táblát. Figyeljük meg az oszlopok specifikációit, az adattípust és a pontosságot.

CREATE TABLE SCOTT.EMP_TEST(EMPID NUMBER,ENAME VARCHAR2(100),DEPARTMENT_ID NUMBER,SALARY NUMBER,JOB_ID VARCHAR2(3),HIREDATE DATE,COMM NUMBER);

A felhasználó hivatkozhat a táblákra más felhasználó sémájából, ha a felhasználónevet vagy a sémát a tábla nevéhez előtaggal jelöli.Például egy felhasználó GUEST le szeretné kérdezni az alkalmazott nevét és fizetését az EMP_TEST táblából, amely a SCOTT tulajdonában van. Az alábbi lekérdezést adhatja ki –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

A tábla létrehozásakor az oszlop tartalmazhat alapértelmezett értéket.Ez segít korlátozni a NULL értékek bejutását az oszlopba. Az alapértelmezett érték levezethető egy literalból, kifejezésből vagy SQL függvényből, amelynek az oszlophoz kompatibilis adattípust kell visszaadnia. Az alábbi CREATE TABLE utasításban figyeljük meg, hogy a LOCATION_ID oszlop alapértelmezett értéke 100.

CREATE TABLE SCOTT.DEPARTMENT(DEPARTMENT_ID NUMBER, DNAME VARCHAR2 (100), LOCATION_ID NUMBER DEFAULT 100);

CTAS – Tábla létrehozása alkérdéssel

A tábla létrehozható az adatbázisban meglévő táblából az alkérdés opció segítségével.A tábla szerkezetét és az adatokat is másolja a táblából. Az adatok feltételek alapján is másolhatók.Az oszlopok adattípus-definíciói, beleértve a kifejezetten előírt NOT NULL megkötéseket is, átmásolódnak az új táblába.

A lenti CTAS szkript létrehoz egy új EMP_BACKUP táblát. A 20. osztály dolgozóinak adatai átmásolódnak az új táblába.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Adattípusok

Az adattípusokat a táblázatban lévő oszlopok alapvető viselkedésének meghatározására használják.Az oszlopok viselkedése tágabb értelemben a szám, a karakter vagy a dátum családba tartozhat.Több más altípus is létezik, amelyek ezekhez a családokhoz tartoznak.

Szám adattípus

A NUMBER adattípus magában foglalja mind az egész, mind a fixpontos, mind a lebegőpontos numerikus értékeket.Az Oracle korai verziói különböző adattípusokat definiáltak az egyes számtípusokhoz, de most a NUMBER adattípus mindezeket a célokat szolgálja.A NUMBER adattípust akkor válassza, ha egy oszlopnak matematikai számításokhoz használható numerikus adatokat kell tárolnia.Esetenként a NUMBER adattípust olyan azonosító számok tárolására használják, amelyeket az adatbázis-kezelő rendszer sorszámként generál.

NUMBER (p, s), ahol p a pontosság 38 számjegyig, s pedig a skála (a tizedesvesszőtől jobbra eső számjegyek száma).A skála -84 és 127 között lehet.

NUMBER (p),egy fixpontos szám nullás skálával és p pontossággal.

FLOAT ,ahol p a bináris pontosság, amely 1 és 126 között lehet. Ha p nincs megadva, az alapértelmezett érték a bináris 126.

Date adattípus

Minden DATE adattípus esetében a század, az év, a hónap, a nap, az óra, a perc és a másodperc tárolódik az adatbázisban. Minden adatbázis-rendszer rendelkezik egy alapértelmezett dátumformátummal, amelyet az NLS_DATE_FORMAT inicializálási paraméter határoz meg. Ez a paraméter általában DD-MON-YY.Ha nem adunk meg időpontot, az alapértelmezett időpont 12:00:00 óra.

Charakter adattípus

Az Oracle három előre definiált karakteres adattípust támogat, köztük a CHAR, VARCHAR, VARCHAR2 és LONG.A VARCHAR és a VARCHAR2 tulajdonképpen szinonimák, és az Oracle a VARCHAR helyett a VARCHAR2 használatát ajánlja.A CHAR adattípust akkor használja, ha az oszlop rögzített hosszúságú karakterértékeket tárol.Például a társadalombiztosítási számot (SSN) az Egyesült Államokban minden állampolgárhoz hozzárendelik, és mindig 9 karakteres (bár az SSN szigorúan számjegyekből áll, a számjegyeket karakterként kezelik), és CHAR(9)-ként kell megadni. A VARCHAR2 adattípust olyan alfanumerikus adatok tárolására használja, amelyek változó hosszúságúak.Például egy ügyfél neve vagy címe jelentősen eltér a tárolandó karakterek számát tekintve.A VARCHAR2 oszlop maximális mérete 4000 karakter.

LOB adattípus

Az Oracle több különböző LOB adattípust biztosít, köztük a CLOB (character large object) és a BLOB (binary large object) adattípusokat.Ezen adattípusok oszlopai strukturálatlan adatokat tárolhatnak, beleértve a szöveget, képet, videót és térbeli adatokat.A CLOB adattípus akár nyolc terabájtnyi karakteres adatot is tárolhat a CHAR adatbázis karakterkészletének használatával.A BLOB adattípus strukturálatlan bináris nagy objektumok tárolására szolgál, mint például a kép- és videóadatok, ahol az adatok egyszerűen “bit” értékek folyamai.A BLOB adattípus akár nyolc terabájt bináris adatot is tárolhat.Az NCLOB adattípus karakteres nagy objektumokat tárolhat többbájtos nemzeti karakterkészletben 8TB-től 128TB-ig.A BFILE adattípus értéke fájlkeresőként vagy a kiszolgáló fájlrendszerében lévő fájlra mutató mutatóként működik. A támogatott maximális fájlméret 8TB és 128TB között van.

Constraints

A korlátozások az Oracle táblákban az adatintegritás biztosítása érdekében meghatározott szabályok összessége.Ezek a szabályok minden egyes oszlopra vagy oszlopcsoportra érvényesek.Amikor a táblázat részt vesz az adatműveletben, ezek a szabályok érvényesítésre kerülnek, és megsértésük esetén kivételt jelentenek. A rendelkezésre álló korlátozási típusok a NOT NULL, az elsődleges kulcs, az egyedi, az ellenőrző és az idegen kulcs.

Az alábbi szintaxis használható a korlátozások oszlopszinten történő bevezetésére.

Szintaxis:

column constraint_type

A NOT NULL kivételével minden korlátozás definiálható táblaszinten is. Összetett korlátozások csak táblaszinten adhatók meg.

NOT NULL korlátozás

A NOT NULL korlátozás azt jelenti, hogy egy adatsornak NOT NULL értékkel kell rendelkeznie az adott oszlopban.Ha egy oszlop NOT NULL értékkel van megadva, az Oracle RDBMS nem engedi, hogy az alkalmazott táblába olyan sorokat tároljanak, amelyek megsértik ezt a korlátozást.Csak oszlopszinten adható meg, táblaszinten nem.

Syntax:

COLUMN 

UNIQUE constraint

Néha szükség van az egyediség érvényesítésére egy olyan oszlopérték esetében, amely nem elsődleges kulcs oszlop.Az UNIQUE constraint használható e szabály érvényesítésére, és az Oracle elutasít minden olyan sort, amely sérti az egyedi korlátozást.Az egyedi korlátozás biztosítja, hogy az oszlopértékek megkülönböztethetők legyenek, duplikátumok nélkül.

Syntax:

Column Level:

Column Level:

Syntax:

Column Level:

Megjegyzés: Az Oracle belsőleg egyedi indexet hoz létre, hogy megakadályozza az oszlopértékek duplikálódását.Az indexeket később tárgyaljuk a PL/SQL-ben.

CREATE TABLE TEST( ... , NAME VARCHAR2(20) CONSTRAINT TEST_NAME_UK UNIQUE, ... );

Az összetett egyedi kulcs esetében a tábla szintjén kell definiálni, az alábbiak szerint.

CREATE TABLE TEST( ... , NAME VARCHAR2(20), STD VARCHAR2(20) , CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD) );

Primer Key

Minden táblának általában tartalmaznia kell egy oszlopot vagy oszlopcsoportot, amely egyedileg azonosítja a táblában tárolt adatsorokat.Ezt az oszlopot vagy oszlopcsoportot elsődleges kulcsnak nevezzük.A legtöbb táblában egyetlen oszlop az elsődleges kulcs.Az elsődleges kulcs oszlopok NULL és duplikált értékek ellen vannak korlátozva.

Megjegyzendő pontok –

• Egy táblának csak egy elsődleges kulcsa lehet.

• Egy összetett elsődleges kulcs alá több oszlop is besorolható.

• Az Oracle belsőleg egyedi indexet hoz létre az oszlopértékek duplikációjának megakadályozása érdekében.Az indexekről később lesz szó a PL/SQL-ben.

Szintaktika:

Oszlopszint:

COLUMN 

Táblaszint:

CONSTRAINT PRIMARY KEY 

A következő példa a PRIMARY KEY megkötés oszlopszintű használatát mutatja be.

CREATE TABLE TEST( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... ); 

A következő példa azt mutatja, hogyan definiálhatunk összetett elsődleges kulcsot a PRIMARY KEY megkötés használatával táblaszinten.

CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) ); 

Foreign Key

Ha két tábla egy adott oszlop alapján osztozik a szülő-gyermek kapcsolaton, akkor a gyermek táblában a csatlakozó oszlopot idegen kulcsnak nevezzük.A szülői tábla megfelelő oszlopának ezt a tulajdonságát nevezzük referenciális integritásnak.Az idegen kulcs oszlop értékei a gyermek táblában vagy nullák lehetnek, vagy a szülői tábla meglévő értékeinek kell lenniük.Vegye figyelembe, hogy csak a hivatkozott tábla elsődleges kulcs oszlopai jogosultak a referenciális integritás érvényesítésére.

Ha a gyermek tábla oszlopán idegen kulcs van definiálva, akkor az Oracle nem engedi törölni a szülői sort, ha az tartalmaz gyermek sorokat.Ha azonban az ON DELETE CASCADE opciót adjuk meg az idegen kulcs definiálásakor, akkor az Oracle törli az összes gyermek sort, miközben a szülői sort törli.Hasonlóképpen,ON DELETE SET NULL azt jelzi, hogy amikor a szülő tábla egy sora törlésre kerül, az idegen kulcs értékei nullára kerülnek.

Szintaktika:

oszlopszint:

COLUMN 

Táblaszint:

CONSTRAINT 

A következő példa azt mutatja, hogyan használjuk a FOREIGN KEY megkötést oszlopszinten.

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode), ...);

A ON DELETE CASCADE záradék használata

CREATE TABLE TEST(ccode varchar2(5) CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode) ON DELETE CASCADE, ...);

Check constraint

Néha egy adott oszlopban tárolt adatértékeknek egy elfogadható értéktartományba kell esniük.A CHECK constraint megköveteli, hogy a megadott ellenőrzési feltétel vagy igaz vagy ismeretlen legyen a táblázatban tárolt minden sorra.A Check constraint lehetővé teszi, hogy egy oszlopra feltételes szabályt alkalmazzunk, amelyet az oszlopba történő adatbevitel előtt érvényesíteni kell. A feltétel nem tartalmazhat al-lekérdezést vagy ál-oszlopot CURRVAL NEXTVAL, LEVEL, ROWNUM vagy SYSDATE.

Az Oracle lehetővé teszi, hogy egyetlen oszlophoz egynél több CHECK-kényszer legyen rendelve. Valójában nincs gyakorlati korlátja az egy oszlophoz definiálható CHECK-kényszerek számának.

Szintaktika:

Oszlopszint:

COLUMN CONSTRAINT 

Táblaszint:

CONSTRAINT CHECK (condition)

A következő példa azt mutatja, hogyan használhatjuk a CHECK-kényszert oszlopszinten.

CREATE TABLE TEST( ..., GRADE char (1) CONSTRAINT TEST_CHK CHECK (upper (GRADE) in ('A','B','C')), ...);

A következő példa azt mutatja, hogyan használhatjuk a CHECK-kényszert táblaszinten.

CREATE TABLE TEST( ..., CONSTRAINT TEST_CHK CHECK (stdate < = enddate),);

ALTER TABLE utasítás

A DBA módosíthatja a táblaszerkezetet vagy az oszlopdefiníciókat, miután a táblát létrehozta az adatbázisban.Az ALTER TABLE DDL-parancs ilyen műveletek elvégzésére szolgál.Az Alter parancs több segédprogramot biztosít kizárólag a sémaobjektumok számára.Az ALTER TABLE utasítás egy táblázat oszlopának hozzáadására, törlésére, átnevezésére és módosítására szolgál.

Az alábbi ALTER TABLE utasítás az EMP táblát EMP_NEW-re nevezi át.

ALTER TABLE EMP RENAME TO EMP_NEW;

Az alábbi ALTER TABLE utasítás egy új TESTCOL oszlopot ad hozzá az EMP_NEW táblához

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Az alábbi ALTER TABLE utasítás a TESTCOL oszlopot TESTNEW-re nevezi át.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Az alábbi ALTER TABLE utasítás törli a TESTNEW oszlopot az EMP_NEW táblából

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Az alábbi ALTER TABLE utasítás elsődleges kulcsot ad az EMPLOYEE_ID oszlophoz.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Az alábbi ALTER TABLE utasítás elhagyja az elsődleges kulcsot.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Az alábbi ALTER TABLE utasítás csak olvasásra állítja át a tábla üzemmódját.

ALTER TABLE EMP_NEW READ ONLY;

Csak olvasható táblák

A csak olvasható táblák az Oracle 11g bővítéseként jelentek meg.Ez lehetővé teszi, hogy a táblákat csak olvasásra használjuk. A korábbi Oracle-verziókban a táblákat úgy lehetett csak olvashatóvá tenni, hogy SELECT jogosultságot adtak a többi felhasználónak, de a tulajdonos továbbra is rendelkezett írási-olvasási jogosultsággal.De most,ha egy tábla csak olvashatóvá van beállítva, még a tulajdonosnak sincs hozzáférése az adatmanipulációhoz.

Szintaxis:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Illusztráció

SQL>CREATE TABLE ORATEST (id NUMBER)SQL>INSERT INTO ORATEST VALUES (1);SQL>ALTER TABLE ORATEST READ ONLY;SQL> INSERT INTO ORATEST VALUES (2);INSERT INTO ORATEST VALUES (2) *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> UPDATE ORATEST SET id = 2;UPDATE ORATEST SET id = 2 *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> DELETE FROM ORATEST;DELETE FROM ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> TRUNCATE TABLE ORATEST;TRUNCATE TABLE ORATEST *ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));ALTER TABLE ORATEST ADD (description VARCHAR2 (50))*ERROR at line 1:ORA-12081: update operation not allowed on table "TEST"."ORATEST"SQL> ALTER TABLE ORATEST READ WRITE;Table altered.SQL> DELETE FROM ORATEST;1 row deleted.

DROP TABLE utasítás

A DROP TABLE utasítás egy tábla adatbázisból való eltávolítására szolgál. Az ejtett tábla és annak adatai többé nem maradnak elérhetőek a kiválasztáshoz.Az ejtett tábla a FLASHBACK segédprogrammal állítható vissza,ha a recyclebinben rendelkezésre áll.Egy tábla ejtésével a hozzá tartozó index és triggerek is törlődnek.

Szintaxis:

DROP TABLE 

Az alábbi utasítás eldobja a táblát, és a recyclebinbe helyezi.

DROP TABLE emp_new;

Az alábbi utasítás eldobja a táblát, és a recyclebinből is kiüríti.

DROP TABLE emp_new PURGE;