Použití příkazů DDL

Použití příkazů DDL k vytváření a správě tabulek

Schéma je kolekce více databázových objektů,které jsou známé jako objekty schématu.Tyto objekty mají přímý přístup podle schématu svého vlastníka.Následující tabulka uvádí seznam objektů schématu.

• Tabulka – pro ukládání dat

• Pohled – pro promítání dat v požadovaném formátu z jedné nebo více tabulek

• Posloupnost – pro generování číselných hodnot

• Index – pro generování číselných hodnot

• Synonymum – alternativní název objektu

Jedním z prvních kroků při vytváření databáze je vytvoření tabulek, v nichž budou uložena data organizace.Návrh databáze zahrnuje identifikaci požadavků uživatelů systému pro různé organizační systémy, jako je zadávání objednávek, řízení zásob a pohledávek. Bez ohledu na velikost a složitost databáze se každá databáze skládá z tabulek.

Vytvoření tabulky

Chce-li DBA vytvořit tabulku v databázi,musí mít k dispozici určité informace – název tabulky, název sloupce, datové typy sloupců a jejich velikosti. Všechny tyto informace lze později upravit pomocí příkazů DDL.

Konvence pojmenování tabulek –

• Název, který zvolíte pro tabulku, se musí řídit těmito standardními pravidly:

• Název musí začínat písmenem A-Z nebo a-.z

• Může obsahovat čísla a podtržítka

• Může být psán velkými nebo malými písmeny

• Může mít délku maximálně 30 znaků

• Nemůže používat stejný název jiného existujícího objektu ve vašem schématu

• Nesmí být rezervovaným slovem SQL

Podle výše uvedených pokynů, ‚EMP85‘ může být platným názvem tabulky.Ale 85EMP není. podobně UPDATE nelze zvolit jako název tabulky, protože se jedná o rezervované klíčové slovo jazyka SQL.

Příkaz CREATE TABLE

Příkaz CREATE TABLE je příkaz DDL, který se používá k vytváření tabulek v databázi. tabulka se vytvoří, jakmile je skript CREATE TABLE proveden, a je připravena uchovávat data dále. uživatel musí mít systémové oprávnění CREATE TABLE, aby mohl vytvořit tabulku ve vlastním schématu.Ale pro vytvoření tabulky v libovolném uživatelském schématu musí mít uživatel systémové oprávnění CREATE ANY TABLE.

Tady je syntaxe základního příkazu CREATE TABLE. může existovat mnoho dalších klauzulí pro explicitní zadání specifikací ukládání nebo hodnot segmentů.

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

V uvedené syntaxi určuje DEFAULT výchozí hodnotu, kterou lze použít při příkazu INSERT, pokud je sloupec ignorován. Nemůže obsahovat odkazy na jiné sloupce tabulky nebo pseudosloupce (CURRVAL, NEXTVAL, LEVEL a ROWNUM) s výjimkou SYSDATE a USER nebo konstanty data, které nejsou plně specifikovány.

Omezení jsou pravidla definovaná volitelně na úrovni sloupců nebo tabulky (o nich se dočtete dále v této kapitole). tato pravidla jsou kontrolována při jakékoli datové akci (vložení, aktualizace) s tabulkou a při jejich porušení vyvolávají chybu, která akci přeruší.

Například příkaz CREATE TABLE níže vytvoří tabulku EMP_TEST. Všimněte si specifikace sloupců, datového typu a přesnosti.

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

Uživatel se může odkazovat na tabulky ze schématu jiného uživatele tak, že před název tabulky předřadí uživatelské jméno nebo schéma. například uživatel GUEST se chce zeptat na jméno a plat zaměstnance z tabulky EMP_TEST, kterou vlastní SCOTT. Může zadat následující dotaz –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Sloupec může při vytváření tabulky obsahovat výchozí hodnotu, což pomáhá omezit, aby se do sloupce dostaly hodnoty NULL. Výchozí hodnotu lze odvodit buď z literálu, výrazu nebo funkce SQL, která musí vracet datový typ kompatibilní se sloupcem. V níže uvedeném příkazu CREATE TABLE si všimněte, že sloupec LOCATION_ID má výchozí hodnotu 100.

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

CTAS – Vytvoření tabulky pomocí poddotazu

Tabulku lze vytvořit z existující tabulky v databázi pomocí možnosti poddotazu. kopíruje strukturu tabulky i data z tabulky. Data lze kopírovat také na základě podmínek. do nové tabulky se zkopírují definice datových typů sloupců včetně explicitně uložených omezení NOT NULL.

Níže uvedený skript CTAS vytvoří novou tabulku EMP_BACKUP. Do nové tabulky se zkopírují data zaměstnanců oddělení 20.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Datové typy

Datové typy slouží k určení základního chování sloupce v tabulce. v širším měřítku může chování sloupce patřit buď do rodiny čísel, znaků nebo data. existuje několik dalších podtypů, které patří do těchto rodin.

Datový typ číslo

Datový typ ČÍSLO zahrnuje jak celočíselné, tak číselné hodnoty s pevnou a pohyblivou řádovou čárkou. dřívější verze systému Oracle definovaly pro každý z těchto různých typů čísel různé datové typy, ale nyní datový typ ČÍSLO slouží ke všem těmto účelům.Datový typ NUMBER zvolte v případě, že sloupec musí uchovávat číselná data, která lze použít při matematických výpočtech.Příležitostně se datový typ NUMBER používá k ukládání identifikačních čísel, pokud jsou tato čísla generována DBMS jako pořadová čísla.

NUMBER (p, s), kde p je přesnost až 38 číslic a s je stupnice (počet číslic napravo od desetinné čárky). stupnice se může pohybovat v rozmezí -84 až 127.

NUMBER (p),je číslo s pevnou řádovou čárkou s nulovou stupnicí a přesností p.

FLOAT ,kde p je binární přesnost, která se může pohybovat v rozmezí 1 až 126.

NUMBER (p),je číslo s pevnou řádovou čárkou. Pokud není p zadáno, je výchozí hodnotou binární hodnota 126.

Datový typ DATE

Pro každý datový typ DATE jsou v databázi uloženy Century, Year, Month, Day, Hour, Minute, Second. Každý databázový systém má výchozí formát data, který je definován inicializačním parametrem NLS_DATE_FORMAT. Tento parametr je obvykle nastaven na DD-MON-YY. pokud nezadáte čas, je výchozí čas 12:00:00.

Znakový datový typ

Oracle podporuje tři předdefinované znakové datové typy včetně CHAR, VARCHAR, VARCHAR2 a LONG. VARCHAR a VARCHAR2 jsou vlastně synonyma a Oracle doporučuje používat VARCHAR2 místo VARCHAR.Datový typ CHAR použijte v případě, že sloupec bude uchovávat znakové hodnoty, které mají pevnou délku.Například číslo sociálního pojištění (SSN) je ve Spojených státech přiděleno každému občanovi a má vždy 9 znaků (i když se SSN skládá výhradně z číslic, číslice jsou považovány za znaky) a bylo by zadáno jako CHAR(9). Datový typ VARCHAR2 použijte k ukládání alfanumerických dat, která mají proměnlivou délku.Například jméno zákazníka nebo adresa se budou značně lišit počtem znaků, které je třeba uložit.Maximální velikost sloupce VARCHAR2 je 4 000 znaků.

Datový typ LOB

Společnost Oracle poskytuje několik různých datových typů LOB, včetně CLOB (character large object) a BLOB (binary large object). sloupce těchto datových typů mohou uchovávat nestrukturovaná data včetně textu, obrázků, videa a prostorových dat.Datový typ CLOB může ukládat až osm terabajtů znakových dat pomocí znakové sady databáze CHAR. datový typ BLOB se používá k ukládání nestrukturovaných binárních velkých objektů, jako jsou objekty spojené s obrazovými a video daty, kde jsou data jednoduše proudem „bitových“ hodnot.Datový typ BLOB může ukládat až osm terabajtů binárních dat. datový typ NCLOB může ukládat znakové velké objekty ve vícebajtové národní znakové sadě až do velikosti 8 TB až 128 TB. hodnota datového typu BFILE funguje jako lokátor souboru nebo ukazatel na soubor v souborovém systému serveru. Maximální podporovaná velikost souboru je 8TB až 128TB.

Omezení

Omezení jsou souborem pravidel definovaných v tabulkách Oracle pro zajištění integrity dat. tato pravidla jsou vynucována umístěná pro každý sloupec nebo sadu sloupců. kdykoli se tabulka účastní datové akce, jsou tato pravidla ověřována a při porušení vyvolávají výjimku. Dostupné typy omezení jsou NOT NULL, primární klíč, jedinečný, kontrolní a cizí klíč.

Níže uvedenou syntaxi lze použít k zavedení omezení na úrovni sloupců.

Syntaxe:

column constraint_type

Všechna omezení kromě NOT NULL lze definovat také na úrovni tabulky. Složená omezení lze zadat pouze na úrovni tabulky.

Omezení NOT NULL

Omezení NOT NULL znamená, že datový řádek musí mít hodnotu sloupce zadanou jako NOT NULL. pokud je sloupec zadán jako NOT NULL, systém Oracle RDBMS nedovolí uložit do tabulky zaměstnanců řádky, které toto omezení porušují. lze jej definovat pouze na úrovni sloupce, nikoli na úrovni tabulky.

Syntaxe:

COLUMN 

Omezení UNIQUE

Někdy je nutné vynutit jedinečnost pro hodnotu sloupce, který není sloupcem primárního klíče. k vynucení tohoto pravidla lze použít omezení UNIQUE a Oracle odmítne všechny řádky, které toto omezení porušují. omezení Unique zajišťuje, že hodnoty sloupců jsou odlišné, bez duplicit.

Syntaxe:

Úroveň sloupce:

COLUMN 

Úroveň tabulky:

Poznámka: Společnost Oracle interně vytváří jedinečný index, aby zabránila duplicitám v hodnotách sloupců.Indexy by byly popsány později v PL/SQL.

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

V případě složeného jedinečného klíče musí být definován na úrovni tabulky, jak je uvedeno níže.

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

Primární klíč

Každá tabulka musí obvykle obsahovat sloupec nebo sadu sloupců, které jednoznačně identifikují řádky dat uložených v tabulce. tento sloupec nebo sada sloupců se označuje jako primární klíč. většina tabulek má jako primární klíč jeden sloupec. sloupce primárního klíče jsou omezeny proti NULL a duplicitním hodnotám.

Body, které je třeba si uvědomit –

• Tabulka může mít pouze jeden primární klíč.

• Více sloupců může být sdruženo pod složený primární klíč.

• Oracle interně vytváří jedinečný index, aby zabránil duplicitám v hodnotách sloupců.Indexům bychom se věnovali později v PL/SQL.

Syntaxe:

Úroveň sloupce:

COLUMN 

Úroveň tabulky:

CONSTRAINT PRIMARY KEY 

Následující příklad ukazuje, jak použít omezení PRIMARY KEY na úrovni sloupce.

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

Následující příklad ukazuje, jak definovat složený primární klíč pomocí omezení PRIMARY KEY na úrovni tabulky.

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

Cizí klíč

Když dvě tabulky sdílejí vztah rodič – potomek na základě určitého sloupce, spojující sloupec v podřízené tabulce se nazývá cizí klíč.Tato vlastnost odpovídajícího sloupce v rodičovské tabulce je známá jako referenční integrita. hodnoty sloupce Foreign Key v podřízené tabulce mohou být buď nulové, nebo se musí shodovat s existujícími hodnotami rodičovské tabulky. upozorňujeme, že referenční integritu lze vynutit pouze u sloupců primárního klíče odkazované tabulky.

Je-li cizí klíč definován na sloupci v podřízené tabulce, pak Oracle nedovolí smazat nadřazený řádek, pokud obsahuje nějaké podřízené řádky. pokud je však při definici cizího klíče zadána volba ON DELETE CASCADE, Oracle smaže všechny podřízené řádky, zatímco nadřazený řádek je mazán.Podobně volba ON DELETE SET NULL znamená, že při odstranění řádku v nadřazené tabulce se hodnoty cizího klíče nastaví na null.

Syntaxe:

Úroveň sloupce:

COLUMN 

Úroveň tabulky:

CONSTRAINT 

Následující příklad ukazuje, jak použít omezení FOREIGN KEY na úrovni sloupce.

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

Použití klauzule ON DELETE CASCADE

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

Kontrolní omezení

Někdy musí hodnoty dat uložených v určitém sloupci spadat do nějakého přijatelného rozsahu hodnot. kontrolní omezení CHECK vyžaduje, aby zadaná kontrolní podmínka byla pro každý řádek uložený v tabulce buď pravdivá, nebo neznámá. kontrolní omezení umožňuje uvalit na sloupec podmíněné pravidlo, které musí být ověřeno před vložením dat do sloupce. Podmínka nesmí obsahovat dílčí dotaz nebo pseudosloupec CURRVAL NEXTVAL, LEVEL, ROWNUM nebo SYSDATE.

Oracle umožňuje, aby jeden sloupec měl více než jedno omezení CHECK. Ve skutečnosti neexistuje žádné praktické omezení počtu omezení CHECK, která lze pro sloupec definovat.

Syntaxe:

Úroveň sloupce:

COLUMN CONSTRAINT 

Úroveň tabulky:

CONSTRAINT CHECK (condition)

Následující příklad ukazuje, jak použít omezení CHECK na úrovni sloupce.

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

Následující příklad ukazuje, jak použít omezení CHECK na úrovni tabulky.

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

Příkaz ALTER TABLE

DBA může provádět změny struktury tabulky nebo definice sloupců poté, co byla tabulka v databázi vytvořena.K provedení takových akcí slouží DDL příkaz ALTER TABLE, který poskytuje více nástrojů výhradně pro objekty schématu.

Příkaz ALTER TABLE slouží k přidání, odstranění, přejmenování a úpravě sloupce v tabulce.

Níže uvedený příkaz ALTER TABLE přejmenovává tabulku EMP na EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Níže uvedený příkaz ALTER TABLE přidá do tabulky EMP_NEW nový sloupec TESTCOL

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Níže uvedený příkaz ALTER TABLE přejmenuje sloupec TESTCOL na TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Níže uvedený příkaz ALTER TABLE vypouští sloupec TESTNEW z tabulky EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Níže uvedený příkaz ALTER TABLE přidává primární klíč na sloupec EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Níže uvedený příkaz ALTER TABLE vypouští primární klíč.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Níže uvedený příkaz ALTER TABLE přepíná režim tabulky na pouze pro čtení.

ALTER TABLE EMP_NEW READ ONLY;

Tabulky pouze pro čtení

Tabulky pouze pro čtení přišly jako vylepšení v Oracle 11g. umožňuje používat tabulky pouze pro účely čtení. V dřívějších verzích Oracle byly tabulky vytvořeny pouze pro čtení přidělením oprávnění SELECT ostatním uživatelům, ale vlastník měl stále právo zápisu a čtení. nyní však, pokud je tabulka nastavena jako Read only, nemá ani vlastník přístup k manipulaci s daty.

Syntaxe:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Ilustrace

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.

Příkaz DROP TABLE

Příkaz DROP TABLE slouží k odstranění tabulky z databáze. Vypuštěná tabulka a její data již nejsou k dispozici pro výběr.Vypuštěnou tabulku lze obnovit pomocí nástroje FLASHBACK,pokud je k dispozici v recyklačním koši.Vypuštěním tabulky se zruší index a spouštěče s ní spojené.

Syntaxe:

DROP TABLE 

Níže uvedený příkaz zahodí tabulku a umístí ji do recyclebinu.

DROP TABLE emp_new;

Níže uvedený příkaz zahodí tabulku a vymaže ji také z recyclebinu.

DROP TABLE emp_new PURGE;