Korzystanie z instrukcji DDL

Używanie instrukcji DDL do tworzenia tabel i zarządzania nimi

Schemat jest zbiorem wielu obiektów bazy danych, zwanych obiektami schematu. Te obiekty mają bezpośredni dostęp za pomocą schematu właściciela. Poniżej tabela zawiera listę obiektów schematu.

Tabela - do przechowywania danych
Widok - aby wyświetlić dane w żądanym formacie z jednej lub kilku tabel
Sekwencja - do generowania wartości liczbowych
Indeks - w celu poprawy wydajności zapytań w tabelach
Synonim - alternatywna nazwa obiektu

Jednym z pierwszych kroków tworzenia bazy danych jest utworzenie tabel, które będą przechowywać dane organizacji. Projekt bazy danych obejmuje identyfikację wymagań użytkowników systemu dla różnych systemów organizacyjnych, takich jak wprowadzanie zamówień, zarządzanie zapasami i rozrachunki z odbiorcami. 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, administrator musi mieć pewne informacje - nazwę tabeli, nazwę kolumny, typy danych i rozmiary kolumn. Wszystkie te informacje można później zmodyfikować za pomocą poleceń DDL.

Konwencje nazewnictwa tabel -

Nazwa, którą wybierzesz dla tabeli, musi być zgodna z następującymi standardowymi zasadami:
Nazwa musi zaczynać się od litery AZ lub az
Może zawierać cyfry i podkreślenia
Może być pisany WIELKIMI małymi literami
Może mieć maksymalnie 30 znaków
Nie można użyć tej samej nazwy innego istniejącego obiektu w schemacie
Nie może być słowem zastrzeżonym SQL

Zgodnie z powyższymi wskazówkami, `` EMP85 '' może być prawidłową nazwą tabeli, ale 85EMP nią nie jest. Podobnie, UPDATE nie może być wybraną jako nazwa tabeli, ponieważ jest to zarezerwowane słowo kluczowe SQL.

Instrukcja CREATE TABLE

CREATE TABLE jest instrukcją DDL, która służy 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ć uprawnienie systemowe CREATE TABLE do tworzenia tabela we własnym schemacie, ale aby utworzyć tabelę w schemacie dowolnego użytkownika, użytkownik musi mieć schemat CREATE ANY TABLE.

Oto składnia podstawowej instrukcji CREATE TABLE. Może istnieć wiele dodatkowych klauzul, które jawnie określają specyfikacje pamięci lub wartości segmentów.

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

W powyższej składni DEFAULT określa wartość domyślną, której można użyć podczas instrukcji INSERT, jeśli kolumna jest ignorowana. Nie może zawierać odniesień do innych kolumn tabeli lub pseudo kolumn (CURRVAL, NEXTVAL, LEVEL i ROWNUM) z wyjątkiem SYSDATE i USER ani stałych dat, które nie zostały w pełni określone.

Ograniczenia to reguły definiowane opcjonalnie na poziomie kolumny lub tabeli (omówione w dalszej części tego rozdziału). Reguły te są sprawdzane podczas dowolnej akcji danych (wstawianie, aktualizowanie) na tabeli i powodują błąd powodujący przerwanie akcji po jej naruszeniu.

Na przykład poniższa instrukcja CREATE TABLE tworzy tabelę EMP_TEST. Zwróć uwagę na specyfikacje 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łać się do tabel ze schematu innego użytkownika, poprzedzając nazwę użytkownika lub schemat nazwą tabeli. Na przykład użytkownik GUEST chce zapytać o nazwisko pracownika i jego wynagrodzenie z tabeli EMP_TEST, której właścicielem jest SCOTT. Może wysłać poniższe zapytanie -

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

Kolumna może przechowywać wartość domyślną w czasie tworzenia tabeli, co pomaga ograniczyć wartości NULL przedostające się do kolumny. Wartość domyślną można wywnioskować z literału, wyrażenia lub funkcji SQL, która musi zwrócić zgodny typ danych do kolumny. W poniższej instrukcji CREATE TABLE zwróć uwagę, ż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 - Utwórz tabelę za pomocą podzapytania

Tabelę można utworzyć z istniejącej tabeli w bazie danych za pomocą opcji podzapytania, która kopiuje zarówno strukturę tabeli, jak i 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 pracowników działu 20 zostaną skopiowane do nowej tabeli.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

Typy danych

Typy danych służą do określenia podstawowego zachowania kolumny w tabeli. W szerszym ujęciu zachowanie kolumny może należeć do rodziny liczb, znaków lub dat. Istnieje wiele innych podtypów, które należą do tych rodzin.

Typ danych liczbowych

Typ danych NUMBER obejmuje zarówno wartości liczbowe całkowite, stałoprzecinkowe, jak i zmiennoprzecinkowe. Wcześniejsze wersje Oracle definiowały różne typy danych dla każdego z tych różnych typów liczb, ale teraz typ danych NUMBER służy wszystkim tym celom. 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, gdzie te numery są generowane przez DBMS jako numery sekwencyjne.

LICZBA (p, s), gdzie p to precyzja do 38 cyfr, a s to skala (liczba cyfr na prawo od przecinka dziesiętnego). Skala może wynosić od -84 do 127.

LICZBA (p) to liczba stałoprzecinkowa ze skalą zerową i dokładnością do p.

FLOAT [(p)], gdzie p jest binarną dokładnością mieszczącą się w zakresie od 1 do 126. Jeśli p nie jest określone, wartością domyślną jest binarne 126.

Data, typ danych

Dla każdego typu danych DATA w bazie danych przechowywane są wiek, rok, miesiąc, dzień, godzina, minuta i sekunda. Każdy system baz danych ma domyślny format daty zdefiniowany przez parametr inicjalizacji NLS_DATE_FORMAT. Ten parametr jest zwykle ustawiony na DD-MON-RR.Jeśli nie określisz czasu, domyślny czas to 12:00:00.

Typ danych znakowych

Oracle obsługuje trzy predefiniowane typy danych znakowych, w tym CHAR, VARCHAR, VARCHAR2 i LONG.VARCHAR i VARCHAR2 są w rzeczywistości synonimami, a Oracle zaleca użycie VARCHAR2 zamiast VARCHAR. Na przykład numer ubezpieczenia społecznego (SSN) w Stanach Zjednoczonych jest przypisywany każdemu obywatelowi i ma zawsze 9 znaków (nawet jeśli numer SSN składa się wyłącznie z cyfr, cyfry są traktowane jako znaki) i będzie określony jako CHAR (9). Użyj typu danych VARCHAR2 do przechowywania danych alfanumerycznych o zmiennej długości.Na przykład nazwa lub adres klienta będzie się znacznie różnić pod względem liczby przechowywanych znaków.Maksymalny rozmiar kolumny VARCHAR2 to 4000 znaków.

Typ danych LOB

Oracle zapewnia kilka różnych typów danych LOB, w tym CLOB (duży obiekt znakowy) i BLOB (duży obiekt binarny). 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 niestrukturalnych dużych obiektów binarnych, takich jak te związane z danymi obrazu i wideo, gdzie dane są po prostu strumieniem wartości „bitowych”. Typ danych BLOB może przechowywać do osiem terabajtów danych binarnych. Typ danych NCLOB może przechowywać duże obiekty znakowe w wielobajtowych znakach narodowych o wielkości od 8 TB do 128 TB. Wartość typu danych BFILE działa jako lokalizator plików lub wskaźnik do pliku w systemie plików serwera. Maksymalny obsługiwany rozmiar pliku to od 8 TB do 128 TB.

Ograniczenia

Ograniczenia to zestaw reguł zdefiniowanych w tabelach Oracle w celu zapewnienia integralności danych. Reguły te są wymuszane i umieszczane dla każdej kolumny lub zestawu kolumn. Gdy tabela uczestniczy w akcji na danych, reguły te są sprawdzane i zgłaszają wyjątek w przypadku naruszenia. Dostępne typy ograniczeń to NIE NULL, Klucz podstawowy, Unikatowy, Czek i Klucz obcy.

Poniższa składnia może służyć do narzucania ograniczeń na poziomie kolumny.

Składnia:

column [data type] [CONSTRAINT constraint_name] constraint_type

Wszystkie ograniczenia z wyjątkiem NOT NULL, można również zdefiniować na poziomie tabeli. Wiązania złożone można określić tylko na poziomie tabeli.

NOT NULL Constraint

Ograniczenie 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 zezwoli na zapisywanie wierszy w tabeli pracowników, które naruszają to ograniczenie. można zdefiniować tylko na poziomie kolumny, a nie na poziomie tabeli.

Składnia:

COLUMN [data type] [NOT NULL]

UNIKALNE ograniczenie

Czasami konieczne jest wymuszenie unikalności dla wartości kolumny, która nie jest kolumną klucza podstawowego. W celu wymuszenia tej reguły można użyć ograniczenia UNIQUE, a Oracle odrzuci wszystkie wiersze, które naruszają ograniczenie unikalności. Wyjątkowe ograniczenie zapewnia, że wartości kolumn są różne , bez duplikatów.

Składnia:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [nazwa ograniczenia] UNIQUE (nazwa kolumny)

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

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

W przypadku złożonego klucza unikatowego należy go zdefiniować na poziomie tabeli, jak poniżej.

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

Klucz podstawowy

Każda tabela musi zwykle zawierać kolumnę lub zestaw kolumn, które jednoznacznie identyfikują wiersze danych, które są przechowywane w tabeli.Ta kolumna lub zestaw kolumn jest nazywany kluczem podstawowym.Większość tabel ma jedną kolumnę jako klucz podstawowy. kolumny kluczy są ograniczone do wartości NULL i zduplikowanych wartości.

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

Tabela może mieć tylko jeden klucz podstawowy.
Wiele kolumn można połączyć pod złożonym kluczem podstawowym.
Oracle wewnętrznie tworzy unikalny indeks, aby zapobiec powielaniu wartości kolumn. Indeksy zostaną omówione później w PL / SQL.

Składnia:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

Poniższy przykład pokazuje, jak używać ograniczenia klucza podstawowego na poziomie kolumny.

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

Poniższy przykład pokazuje, jak zdefiniować złożony klucz podstawowy przy użyciu ograniczenia PRIMARY KEY na poziomie tabeli.

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

Klucz obcy

Gdy dwie tabele współdzielą relację nadrzędny-podrzędny na podstawie określonej kolumny, łącząca kolumna w tabeli podrzędnej jest znana jako klucz obcy. Ta właściwość odpowiedniej kolumny w tabeli nadrzędnej jest znana jako integralność referencyjna. Wartości kolumn klucza obcego w tabeli podrzędnej mogą musi mieć wartość null lub być istniejącymi wartościami tabeli nadrzędnej. Należy pamiętać, że tylko kolumny klucza podstawowego tabeli, do której się odwołuje, mogą wymusić integralność referencyjną.

Jeśli klucz obcy jest zdefiniowany w kolumnie w tabeli podrzędnej, Oracle nie zezwala na usunięcie wiersza nadrzędnego, jeśli zawiera wiersze potomne, natomiast jeśli w momencie definiowania klucza obcego podano opcję ON DELETE CASCADE, Oracle usuwa wszystkie wiersze podrzędne podczas usuwania wiersza nadrzędnego. Podobnie, ON DELETE SET NULL wskazuje, że po usunięciu wiersza w tabeli nadrzędnej wartości klucza obcego są ustawiane na null.

Składnia:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

Poniższy przykład pokazuje, jak używać ograniczenia KLUCZ OBCY 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,
   ...
);

Sprawdź ograniczenie

Czasami wartości danych przechowywane w określonej kolumnie muszą mieścić się w jakimś akceptowalnym zakresie wartości. Ograniczenie CHECK wymaga, aby określony warunek sprawdzenia był albo prawdziwy, albo nieznany dla każdego wiersza przechowywanego w tabeli. Ograniczenie sprawdzające pozwala narzucić regułę warunkową na kolumna, która musi zostać zweryfikowana przed wstawieniem danych do kolumny. Warunek nie może zawierać zapytania podrzędnego ani pseudokolumny CURRVAL NEXTVAL, LEVEL, ROWNUM ani SYSDATE.

Oracle pozwala pojedynczej kolumnie mieć więcej niż jedno ograniczenie CHECK. W rzeczywistości nie ma praktycznego ograniczenia liczby ograniczeń CHECK, które można zdefiniować dla kolumny.

Składnia:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

Poniższy 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')),
   ...
);

Poniższy przykład pokazuje, jak używać ograniczenia CHECK na poziomie tabeli.

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

Instrukcja ALTER TABLE

Administrator DBA może wprowadzać zmiany w strukturze tabeli lub definicjach kolumn po utworzeniu tabeli w bazie danych. Do wykonywania takich działań służy komenda DDL ALTER TABLE. Komenda Alter udostępnia wiele narzędzi wyłącznie dla obiektów schematu. Używana jest instrukcja ALTER TABLE. aby dodać, upuścić, zmienić nazwę i zmodyfikować kolumnę w tabeli.

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

ALTER TABLE EMP RENAME TO EMP_NEW;

Poniższa instrukcja ALTER TABLE dodaje nową kolumnę TESTCOL do tabeli EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Poniższa instrukcja ALTER TABLE zmienia nazwę kolumny TESTCOL na TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Poniższa instrukcja ALTER TABLE usuwa kolumnę TESTNEW z tabeli EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Poniższa instrukcja ALTER TABLE dodaje klucz podstawowy do kolumny EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Poniższa instrukcja ALTER TABLE usuwa klucz podstawowy.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Poniższa instrukcja ALTER TABLE przełącza tryb tabeli na 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, dzięki czemu mogą być używane tylko do odczytu. We wcześniejszych wersjach Oracle tabele były ustawiane tylko do odczytu przez przyznanie uprawnienia SELECT innym użytkownikom, ale właściciel nadal miał uprawnienie do odczytu i zapisu, ale teraz, jeśli tabela jest ustawiona jako tylko do odczytu, nawet właściciel nie ma dostępu do manipulacji danymi .

Składnia:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] 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.

Instrukcja DROP TABLE

Instrukcja DROP TABLE służy do usuwania tabeli z bazy danych. Usunięta tabela i jej dane nie są już dostępne do wyboru. Upuszczoną tabelę można odzyskać za pomocą narzędzia FLASHBACK, jeśli jest dostępne w recyclebin. Upuszczenie tabeli powoduje usunięcie indeksu i powiązanych z nią wyzwalaczy.