Using DDL Statements

Using DDL Statements to Create and Manage Tables

Schemat jest zbiorem wielu obiektów bazy danych, które są znane jako obiekty schematu. Obiekty te mają bezpośredni dostęp przez ich właściciela schemat.Poniższa tabela zawiera listę obiektów schematu.

• Table – do przechowywania danych

• View – do wyświetlania danych w żądanym formacie z jednej lub wielu tabel

• Sequence – do generowania wartości liczbowych

• Index – do zwiększania wydajności zapytań na tabelach

• Indeks – do generowania wartości liczbowych

• . w celu poprawy wydajności zapytań do tabel

• Synonim – alternatywna nazwa obiektu

Jednym z pierwszych kroków w tworzeniu bazy danych jest utworzenie tabel, które będą przechowywać dane organizacji.Projektowanie bazy danych obejmuje identyfikację wymagań użytkowników systemu dla różnych systemów organizacyjnych, takich jak wprowadzanie zamówień, zarządzanie zapasami i należnościami. Niezależnie od rozmiaru i złożoności bazy danych, każda baza danych składa się z tabel.

Tworzenie tabeli

Aby utworzyć tabelę w bazie danych, DBA musi mieć pewne informacje w ręku – nazwę tabeli, nazwę kolumny, typy danych kolumn i rozmiary kolumn. Wszystkie te informacje mogą być później modyfikowane za pomocą poleceń DDL.

Konwencje nazewnictwa tabel –

• Nazwa wybrana dla tabeli musi być zgodna z tymi standardowymi zasadami:

• Nazwa musi zaczynać się od litery A-Z lub a-z

• Może zawierać cyfry i znaki podkreślenia

• Może zawierać małe lub duże litery

• Może mieć długość do 30 znaków

• Czas trwania do 30 znaków

• Nie może używać tej samej nazwy innego istniejącego obiektu w twoim schemacie

• Nie może być słowem zastrzeżonym SQL

Podążając za powyższymi wytycznymi, EMP85″ może być poprawną nazwą tabeli.Ale 85EMP nie jest. Podobnie UPDATE nie może być wybrana jako nazwa tabeli, ponieważ jest to zarezerwowane słowo kluczowe SQL.

Konstrukcja CREATE TABLE

Konstrukcja CREATE TABLE jest instrukcją DDL, która jest używana do tworzenia tabel w bazie danych.Tabela jest tworzona natychmiast po wykonaniu skryptu CREATE TABLE i jest gotowa do przechowywania danych.Użytkownik musi mieć uprawnienia systemowe CREATE TABLE, aby utworzyć tabelę w swoim własnym schemacie.Ale aby utworzyć tabelę w schemacie dowolnego użytkownika, użytkownik musi mieć uprawnienie CREATE ANY TABLE.

Tutaj znajduje się składnia podstawowej instrukcji CREATE TABLE.Może być wiele dodatkowych klauzul, aby jawnie przekazać specyfikacje przechowywania lub wartości segmentów.

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

W powyższej składni, DEFAULT określa wartość domyślną, która może być użyta podczas instrukcji INSERT, jeśli kolumna jest ignorowana. Nie może zawierać odwołań do innych kolumn tabeli lub pseudokolumn (CURRVAL, NEXTVAL, LEVEL i ROWNUM) z wyjątkiem SYSDATE i USER lub stałych daty, które nie są w pełni określone.

Węższe ograniczenia są regułami zdefiniowanymi opcjonalnie na poziomie kolumny lub tabeli (omówione w dalszej części rozdziału). Reguły te są sprawdzane podczas każdej operacji na danych (wstawianie, aktualizacja) w tabeli i podnoszą błąd, aby przerwać akcję przy ich naruszeniu.

Na przykład poniższe polecenie CREATE TABLE tworzy tabelę EMP_TEST. Zwróć uwagę na specyfikację kolumn, typ danych i precyzję.

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

Użytkownik może odwoływać się do tabel ze schematu innego użytkownika poprzez poprzedzenie nazwy użytkownika lub schematu nazwą tabeli.Na przykład, użytkownik GUEST chce zapytać o nazwisko pracownika i wynagrodzenie z tabeli EMP_TEST, która jest własnością SCOTT. Może on wydać poniższe zapytanie –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Kolumna może posiadać wartość domyślną podczas tworzenia tabeli. Pomaga to ograniczyć dostawanie się wartości NULL do kolumny. Wartość domyślna może być wydedukowana z literału, wyrażenia lub funkcji SQL, która musi zwracać typ danych zgodny z typem kolumny. W poniższej instrukcji CREATE TABLE zauważ, że kolumna LOCATION_ID ma wartość domyślną 100.

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

CTAS – Tworzenie tabeli przy użyciu podzapytania

Tabela może być utworzona z istniejącej tabeli w bazie danych przy użyciu opcji podzapytania.Kopiuje strukturę tabeli, jak również dane z tabeli. Dane mogą być również kopiowane na podstawie warunków.Definicje typów danych kolumn, w tym jawnie nałożone ograniczenia NOT NULL są kopiowane do nowej tabeli.

Poniższy skrypt CTAS tworzy nową tabelę EMP_BACKUP. Dane pracownika z działu 20 są kopiowane do nowej tabeli.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Typy danych

Typy danych są używane do określenia podstawowego zachowania kolumny w tabeli.Na szerszej podstawie, zachowanie kolumny może należeć do rodziny liczb, znaków lub daty.Istnieje wiele innych podtypów, które należą do tych rodzin.

Typ danych NUMBER

Typ danych NUMBER obejmuje zarówno wartości liczbowe całkowite, stałoprzecinkowe, jak i zmiennoprzecinkowe.Wczesne wersje Oracle definiowały różne typy danych dla każdego z tych różnych typów liczb, ale obecnie typ danych NUMBER służy do wszystkich tych celów.Wybierz typ danych NUMBER, gdy kolumna musi przechowywać dane liczbowe, które mogą być użyte w obliczeniach matematycznych.Czasami typ danych NUMBER jest używany do przechowywania numerów identyfikacyjnych, które są generowane przez DBMS jako numery sekwencyjne.

LICZBA (p, s),gdzie p jest precyzją do 38 cyfr, a s jest skalą (liczba cyfr na prawo od kropki dziesiętnej).Skala może zawierać się w przedziale od -84 do 127.

LICZBA (p),jest liczbą stałoprzecinkową ze skalą zero i precyzją p.

LICZBA ,gdzie p jest precyzją binarną, która może zawierać się w przedziale od 1 do 126. Jeżeli p nie jest określone, domyślną wartością jest binarna 126.

Typ danych Data

Dla każdego typu danych DATE, Century, Rok, Miesiąc, Dzień, Godzina, Minuta, Sekunda są przechowywane w bazie danych. Każdy system baz danych posiada domyślny format daty, który jest określony przez parametr inicjalizacyjny NLS_DATE_FORMAT. Parametr ten jest zwykle ustawiony na DD-MON-YY.Jeśli nie określisz czasu, domyślnym czasem jest 12:00:00.

Znakowy typ danych

Oracle obsługuje trzy predefiniowane znakowe typy danych, w tym CHAR, VARCHAR, VARCHAR2 i LONG.VARCHAR i VARCHAR2 są właściwie synonimami, a Oracle zaleca używanie VARCHAR2 zamiast VARCHAR.Typu danych CHAR należy używać, gdy kolumna przechowuje wartości znakowe o stałej długości. Na przykład numer SSN (Social Security Number) w Stanach Zjednoczonych jest przypisany do każdego obywatela i ma zawsze rozmiar 9 znaków (mimo, że SSN składa się wyłącznie z cyfr, cyfry są traktowane jako znaki) i powinien być określony jako CHAR(9). Użyj typu danych VARCHAR2 do przechowywania danych alfanumerycznych o zmiennej długości. Na przykład nazwa klienta lub adres będą się znacznie różnić pod względem liczby znaków, które należy przechowywać. Maksymalny rozmiar kolumny VARCHAR2 wynosi 4000 znaków.

Typ danych LOB

Oracle udostępnia kilka różnych typów danych LOB, w tym CLOB (character large object) i BLOB (binary large object).Kolumny tych typów danych mogą przechowywać dane nieustrukturyzowane, w tym tekst, obraz, wideo i dane przestrzenne.Typ danych CLOB może przechowywać do ośmiu terabajtów danych znakowych przy użyciu zestawu znaków bazy danych CHAR. Typ danych BLOB jest używany do przechowywania nieuporządkowanych dużych obiektów binarnych, takich jak związane z obrazami i danymi wideo, gdzie dane są po prostu strumieniem wartości „bitowych”.Typ danych BLOB może przechowywać do ośmiu terabajtów danych binarnych.Typ danych NCLOB może przechowywać duże obiekty znakowe w wielobajtowym krajowym zestawie znaków do 8TB do 128TB.Wartość typu danych BFILE działa jako lokalizator pliku lub wskaźnik do pliku w systemie plików serwera. Maksymalny obsługiwany rozmiar pliku wynosi od 8TB do 128TB.

Węższe ograniczenia

Węższe ograniczenia są zbiorem reguł zdefiniowanych w tabelach Oracle w celu zapewnienia integralności danych.Reguły te są egzekwowane dla każdej kolumny lub zestawu kolumn.Za każdym razem, gdy tabela uczestniczy w operacji na danych, reguły te są sprawdzane i podnoszą wyjątek przy naruszeniu. Dostępne typy ograniczeń to NOT NULL, Primary Key, Unique, Check i Foreign Key.

Następująca składnia może być użyta do nałożenia ograniczenia na poziomie kolumny.

Syntaktyka:

column constraint_type

Wszystkie ograniczenia z wyjątkiem NOT NULL, mogą być również zdefiniowane na poziomie tabeli. Ograniczenia złożone mogą być określone tylko na poziomie tabeli.

NIE NULL Ograniczenie

Określenie NOT NULL oznacza, że wiersz danych musi mieć wartość dla kolumny określonej jako NOT NULL.Jeśli kolumna jest określona jako NOT NULL, Oracle RDBMS nie pozwoli na zapisanie wierszy do tabeli employee, które naruszają to ograniczenie.Może ono być określone tylko na poziomie kolumny, a nie na poziomie tabeli.

Syntaktyka:

COLUMN 

UNIQUE constraint

Czasami konieczne jest wymuszenie unikalności dla wartości kolumny, która nie jest kolumną klucza głównego.Do wymuszenia tej reguły można użyć ograniczenia UNIQUE, a Oracle odrzuci wszystkie wiersze, które naruszają ograniczenie unique constraint.Unique constraint zapewnia, że wartości kolumn są różne, bez żadnych duplikatów.

Syntaktyka:

Poziom kolumny:

COLUMN 

Table Level: CONSTRAINT UNIQUE (nazwa kolumny)

Uwaga: Oracle wewnętrznie tworzy unikalny indeks, aby zapobiec duplikacji wartości w kolumnie.Indeksy zostaną omówione później w PL/SQL.

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

W przypadku złożonego klucza unikalnego,musi on być zdefiniowany na poziomie tabeli, jak poniżej.

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

Klucz główny

Każda tabela musi zwykle zawierać kolumnę lub zestaw kolumn, które jednoznacznie identyfikują wiersze danych przechowywanych w tabeli.Ta kolumna lub zestaw kolumn jest określana jako klucz główny.Większość tabel ma jedną kolumnę jako klucz główny.Kolumny klucza głównego są ograniczone przeciwko NULL i zduplikowanym wartościom.

Punkty, na które należy zwrócić uwagę –

• Tabela może mieć tylko jeden klucz główny.

• Wielokrotne kolumny mogą być zgrupowane pod złożonym kluczem głównym.

• Oracle wewnętrznie tworzy unikalny indeks, aby zapobiec duplikacji wartości kolumn.Indeksy zostaną omówione później w PL/SQL.

Syntaktyka:

Poziom kolumny:

COLUMN 

Poziom tabeli:

CONSTRAINT PRIMARY KEY 

Następujący przykład pokazuje, jak używać ograniczenia PRIMARY KEY na poziomie kolumny.

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

Następujący przykład pokazuje jak zdefiniować złożony klucz główny używając ograniczenia PRIMARY KEY na poziomie tabeli.

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

Klucz obcy

Gdy dwie tabele dzielą relację rodzic – dziecko na podstawie określonej kolumny, kolumna łącząca w tabeli dziecka jest znana jako klucz obcy.Ta właściwość odpowiadającej kolumny w tabeli nadrzędnej jest znana jako integralność referencyjna.Wartości kolumn klucza obcego w tabeli dziecka mogą być albo zerowe albo muszą być istniejącymi wartościami tabeli nadrzędnej.Należy pamiętać, że tylko kolumny klucza głównego tabeli są uprawnione do egzekwowania integralności referencyjnej.

Jeśli klucz obcy jest zdefiniowany na kolumnie w tabeli potomnej, Oracle nie pozwala na usunięcie wiersza nadrzędnego, jeśli zawiera on jakiekolwiek wiersze potomne. Jednak jeśli opcja ON DELETE CASCADE jest podana w momencie definiowania klucza obcego, Oracle usunie wszystkie wiersze potomne podczas usuwania wiersza nadrzędnego.Podobnie opcja ON DELETE SET NULL wskazuje, że gdy wiersz w tabeli nadrzędnej jest usuwany, wartości klucza obcego są ustawiane na wartość null.

Syntaktyka:

Poziom kolumny:

COLUMN 

Poziom tabeli:

CONSTRAINT 

Następujący przykład przedstawia sposób użycia ograniczenia FOREIGN KEY na poziomie kolumny.

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

Usage of ON DELETE CASCADE clause

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

Check constraint

Czasami wartości danych przechowywanych w określonej kolumnie muszą mieścić się w pewnym akceptowalnym zakresie wartości.Ograniczenie CHECK wymaga, aby określony warunek sprawdzający był albo prawdziwy albo nieznany dla każdego wiersza przechowywanego w tabeli.Ograniczenie Check constraint pozwala na nałożenie reguły warunkowej na kolumnę, która musi zostać sprawdzona przed wstawieniem danych do kolumny. Warunek nie może zawierać podzapytania lub pseudo kolumny CURRVAL NEXTVAL, LEVEL, ROWNUM lub SYSDATE.

Oracle zezwala, aby pojedyncza kolumna miała więcej niż jedno ograniczenie CHECK. W rzeczywistości nie ma praktycznego ograniczenia liczby ograniczeń CHECK, które można zdefiniować dla danej kolumny.

Syntaktyka:

Column level:

COLUMN CONSTRAINT 

Table level:

CONSTRAINT CHECK (condition)

Następujący przykład pokazuje, jak używać ograniczenia CHECK na poziomie kolumny.

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

Następujący przykład pokazuje, jak używać ograniczenia CHECK na poziomie tabeli.

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

ALTER TABLE statement

A DBA może dokonać zmian w strukturze tabeli lub definicjach kolumn po utworzeniu tabeli w bazie danych.Polecenie DDL ALTER TABLE jest używane do wykonywania takich czynności.Polecenie Alter udostępnia wiele narzędzi wyłącznie dla obiektów schematu.Instrukcja ALTER TABLE jest używana do dodawania, usuwania, zmiany nazwy i modyfikowania kolumny w tabeli.

Poniższa instrukcja ALTER TABLE zmienia nazwę tabeli EMP na EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Poniższe polecenie ALTER TABLE dodaje nową kolumnę TESTCOL do tabeli EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Poniższe polecenie ALTER TABLE zmienia nazwę kolumny TESTCOL na TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Następne polecenie ALTER TABLE usuwa kolumnę TESTNEW z tabeli EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Następne polecenie ALTER TABLE dodaje klucz główny do kolumny EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Poniższa instrukcja ALTER TABLE upuszcza klucz główny.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Poniższa instrukcja ALTER TABLE przełącza tryb tabeli na tryb tylko do odczytu.

ALTER TABLE EMP_NEW READ ONLY;

Tabele tylko do odczytu

Tabele tylko do odczytu pojawiły się jako ulepszenie w Oracle 11g.Pozwala to na używanie tabel tylko do odczytu. We wcześniejszych wersjach Oracle, tabele były ustawiane jako tylko do odczytu poprzez nadanie uprawnień SELECT innym użytkownikom, ale właściciel nadal posiadał uprawnienia do zapisu i odczytu. Teraz jednak, jeśli tabela jest ustawiona jako tylko do odczytu, nawet właściciel nie ma dostępu do manipulacji danymi.

Syntaktyka:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Ilustracja

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.

Określenie DROP TABLE

Określenie DROP TABLE jest używane do usunięcia tabeli z bazy danych. Usunięta tabela i jej dane nie są już dostępne do wyboru.Usunięta tabela może być odzyskana przy użyciu narzędzia FLASHBACK, jeśli jest dostępne w koszu.Usunięcie tabeli powoduje usunięcie indeksu i trigerów z nią związanych.

Syntaktyka:

DROP TABLE 

Poniższa instrukcja spowoduje usunięcie tabeli i umieszczenie jej w koszu na śmieci.

DROP TABLE emp_new;

Poniższa instrukcja spowoduje usunięcie tabeli i wypłukanie jej również z kosza na śmieci.

DROP TABLE emp_new PURGE;

.