Using DDL Statements

Using DDL Statements to Create and Manage Tables

Ein Schema ist eine Sammlung von mehreren Datenbankobjekten, die als Schema-Objekte bekannt sind und auf die der Eigentümer des Schemas direkten Zugriff hat.

• Tabelle – um Daten zu speichern

• Ansicht – um Daten in einem gewünschten Format aus einer oder mehreren Tabellen zu projizieren

• Sequenz – um numerische Werte zu erzeugen

• Index – zur Verbesserung der Leistung von Tabellenabfragen

• Synonym – alternativer Name eines Objekts

Einer der ersten Schritte bei der Erstellung einer Datenbank ist die Erstellung der Tabellen, in denen die Daten eines Unternehmens gespeichert werden sollen.Das Datenbankdesign umfasst die Ermittlung der Anforderungen der Systembenutzer für verschiedene Organisationssysteme wie Auftragserfassung, Bestandsverwaltung und Debitorenbuchhaltung. Unabhängig von der Größe und Komplexität der Datenbank besteht jede Datenbank aus Tabellen.

Tabellenerstellung

Um eine Tabelle in der Datenbank zu erstellen, muss ein DBA bestimmte Informationen zur Hand haben – den Tabellennamen, den Spaltennamen, die Datentypen der Spalten und die Spaltengrößen. Alle diese Informationen können später mit DDL-Befehlen geändert werden.

Tabellennamenskonventionen –

• Der Name, den Sie für eine Tabelle wählen, muss diesen Standardregeln entsprechen:

• Der Name muss mit einem Buchstaben A-Z oder a- beginnenz

• Kann Zahlen und Unterstriche enthalten

• Kann in Groß- oder Kleinschreibung sein

• Kann bis zu 30 Zeichen lang sein

• Kann nicht denselben Namen eines anderen bestehenden Objekts in Ihrem Schema verwenden

• Darf kein reserviertes SQL-Wort sein

Die obigen Richtlinien sind zu beachten, kann ‚EMP85‘ ein gültiger Tabellenname sein.Ähnlich kann UPDATE nicht als Tabellenname gewählt werden, da es ein reserviertes SQL-Schlüsselwort ist.

CREATE TABLE-Anweisung

Die CREATE TABLE-Anweisung ist eine DDL-Anweisung, die zum Erstellen von Tabellen in der Datenbank verwendet wird.Die Tabelle wird erstellt, sobald das CREATE TABLE-Skript ausgeführt wird, und ist bereit, die Daten aufzunehmen.Der Benutzer muss über das Systemprivileg CREATE TABLE verfügen, um die Tabelle in seinem eigenen Schema zu erstellen.Um jedoch eine Tabelle in einem beliebigen Benutzerschema zu erstellen, muss der Benutzer über das Schema CREATE ANY TABLE verfügen.

Hier ist die Syntax einer grundlegenden CREATE TABLE-Anweisung.

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

In der obigen Syntax gibt DEFAULT einen Standardwert an, der während der INSERT-Anweisung verwendet werden kann, wenn die Spalte ignoriert wird. Sie kann keine Verweise auf andere Tabellenspalten oder Pseudospalten (CURRVAL, NEXTVAL, LEVEL und ROWNUM) außer SYSDATE und USER oder nicht vollständig spezifizierte Datumskonstanten enthalten.

Einschränkungen sind die Regeln, die optional auf Spalten- oder Tabellenebene definiert werden (später in diesem Kapitel behandelt) und die bei jeder Datenaktion (Einfügen, Aktualisieren) in der Tabelle überprüft werden und bei Verletzung einen Fehler auslösen, um die Aktion abzubrechen.

Die folgende CREATE TABLE-Anweisung erstellt beispielsweise eine Tabelle EMP_TEST. Beachten Sie die Spaltenangaben, den Datentyp und die Genauigkeit.

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

Ein Benutzer kann auf Tabellen aus dem Schema eines anderen Benutzers verweisen, indem er dem Tabellennamen den Benutzernamen oder das Schema voranstellt.

Beispiel: Ein Benutzer GUEST möchte den Namen und das Gehalt eines Mitarbeiters aus der Tabelle EMP_TEST abfragen, die SCOTT gehört. Er kann die folgende Abfrage stellen –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Eine Spalte kann bei der Erstellung der Tabelle einen Standardwert enthalten, um die NULL-Werte in der Spalte zu begrenzen. Der Standardwert kann entweder aus einem Literal, einem Ausdruck oder einer SQL-Funktion abgeleitet werden, die einen mit der Spalte kompatiblen Datentyp zurückgeben muss. In der folgenden CREATE TABLE-Anweisung ist zu beachten, dass die Spalte LOCATION_ID den Standardwert 100 hat.

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

CTAS – Tabelle mit Hilfe einer Subquery erstellen

Eine Tabelle kann aus einer bestehenden Tabelle in der Datenbank mit Hilfe einer Subquery-Option erstellt werden, wobei sowohl die Tabellenstruktur als auch die Daten der Tabelle kopiert werden. Die Spalten-Datentyp-Definitionen einschließlich der explizit auferlegten NOT NULL-Beschränkungen werden in die neue Tabelle kopiert.

Das folgende CTAS-Skript erstellt eine neue Tabelle EMP_BACKUP. Die Mitarbeiterdaten der Abteilung 20 werden in die neue Tabelle kopiert.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Datentypen

Datentypen werden verwendet, um das grundlegende Verhalten einer Spalte in der Tabelle zu spezifizieren.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Auf einer breiteren Basis kann das Spaltenverhalten entweder zur Zahlen-, Zeichen- oder Datumsfamilie gehören.

Es gibt mehrere andere Untertypen, die zu diesen Familien gehören.

Datentyp NUMBER

Der Datentyp NUMBER umfasst sowohl Ganzzahl-, Festkomma- als auch Fließkommazahlen.Frühe Versionen von Oracle definierten verschiedene Datentypen für jede dieser verschiedenen Arten von Zahlen, aber jetzt dient der Datentyp NUMBER allen diesen Zwecken.Wählen Sie den Datentyp NUMBER, wenn eine Spalte numerische Daten speichern muss, die in mathematischen Berechnungen verwendet werden können. Gelegentlich wird der Datentyp NUMBER verwendet, um Identifikationsnummern zu speichern, die vom DBMS als fortlaufende Nummern generiert werden.

NUMBER (p, s), wobei p die Genauigkeit bis zu 38 Stellen und s die Skalierung (Anzahl der Stellen rechts vom Dezimalpunkt) ist. Die Skalierung kann zwischen -84 und 127 liegen.

NUMBER (p), ist eine Festkommazahl mit einer Skalierung von Null und einer Genauigkeit von p.

FLOAT , wobei p die binäre Genauigkeit ist, die zwischen 1 und 126 liegen kann. Wenn p nicht angegeben wird, ist der Standardwert 126.

Datumsdatentyp

Für jeden DATE-Datentyp werden Jahrhundert, Jahr, Monat, Tag, Stunde, Minute und Sekunde in der Datenbank gespeichert. Jedes Datenbanksystem hat ein Standard-Datumsformat, das durch den Initialisierungsparameter NLS_DATE_FORMAT festgelegt wird. Wenn Sie keine Zeit angeben, ist die Standardzeit 12:00:00 Uhr

Zeichen-Datentyp

Oracle unterstützt drei vordefinierte Zeichen-Datentypen, darunter CHAR, VARCHAR, VARCHAR2 und LONG.VARCHAR und VARCHAR2 sind eigentlich synonym, und Oracle empfiehlt die Verwendung von VARCHAR2 anstelle von VARCHAR.Verwenden Sie den Datentyp CHAR, wenn die Spalte Zeichenwerte mit fester Länge speichert, wie z.B. die Sozialversicherungsnummer (SSN), die in den Vereinigten Staaten jedem Bürger zugewiesen wird und immer 9 Zeichen lang ist (obwohl eine SSN ausschließlich aus Ziffern besteht, werden die Ziffern als Zeichen behandelt), und als CHAR(9) angegeben wird. Verwenden Sie den Datentyp VARCHAR2, um alphanumerische Daten mit variabler Länge zu speichern, z. B. einen Kundennamen oder eine Adresse, die in der Anzahl der zu speichernden Zeichen stark variieren. Die maximale Größe einer VARCHAR2-Spalte beträgt 4.000 Zeichen.

LOB-Datentyp

Oracle bietet verschiedene LOB-Datentypen an, darunter CLOB (Character Large Object) und BLOB (Binary Large Object). In Spalten dieser Datentypen können unstrukturierte Daten wie Text, Bilder, Videos und räumliche Daten gespeichert werden.Der Datentyp CLOB kann bis zu acht Terabyte an Zeichendaten unter Verwendung des CHAR-Datenbankzeichensatzes speichern, während der Datentyp BLOB zum Speichern unstrukturierter binärer großer Objekte verwendet wird, wie z. B. Bild- und Videodaten, bei denen die Daten einfach ein Strom von „Bit“-Werten sind.Ein BLOB-Datentyp kann bis zu acht Terabyte an Binärdaten speichern, der NCLOB-Datentyp kann große Objekte in nationalen Multibyte-Zeichensätzen von 8 bis 128 TB speichern, der BFILE-Datentyp dient als Dateilokator oder Zeiger auf eine Datei im Dateisystem des Servers. Die maximal unterstützte Dateigröße beträgt 8TB bis 128TB.

Constraints

Constraints sind eine Reihe von Regeln, die in Oracle-Tabellen definiert sind, um die Datenintegrität zu gewährleisten.Diese Regeln werden für jede Spalte oder jeden Satz von Spalten erzwungen.Wann immer die Tabelle an einer Datenaktion teilnimmt, werden diese Regeln überprüft und lösen bei Verletzung eine Ausnahme aus. Die verfügbaren Constraint-Typen sind NOT NULL, Primary Key, Unique, Check und Foreign Key.

Die folgende Syntax kann verwendet werden, um Constraints auf Spaltenebene zu erzwingen.

Syntax:

column constraint_type

Alle Beschränkungen außer NOT NULL können auch auf Tabellenebene definiert werden. Zusammengesetzte Beschränkungen können nur auf Tabellenebene angegeben werden.

NOT NULL-Beschränkung

Eine NOT NULL-Beschränkung bedeutet, dass eine Datenzeile einen Wert für die Spalte haben muss, die als NOT NULL angegeben ist.Wenn eine Spalte als NOT NULL angegeben ist, lässt das Oracle RDBMS nicht zu, dass Zeilen in der Mitarbeitertabelle gespeichert werden, die diese Beschränkung verletzen.Sie kann nur auf Spaltenebene, nicht aber auf Tabellenebene definiert werden.

Syntax:

COLUMN 

UNIQUE-Beschränkung

Manchmal ist es notwendig, die Eindeutigkeit eines Spaltenwertes zu erzwingen, der keine Primärschlüsselspalte ist.Die UNIQUE-Beschränkung kann verwendet werden, um diese Regel zu erzwingen, und Oracle weist alle Zeilen zurück, die die eindeutige Beschränkung verletzen.Die eindeutige Beschränkung stellt sicher, dass die Spaltenwerte eindeutig sind, ohne Duplikate.

Syntax:

Spaltenebene:

COLUMN 

Tabellenebene: CONSTRAINT UNIQUE (Spaltenname)

Hinweis: Oracle erstellt intern einen eindeutigen Index, um eine Duplizierung der Spaltenwerte zu verhindern.

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

Im Falle eines zusammengesetzten eindeutigen Schlüssels muss dieser auf Tabellenebene wie folgt definiert werden.

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

Primärschlüssel

Jede Tabelle muss normalerweise eine Spalte oder einen Satz von Spalten enthalten, die Datenzeilen, die in der Tabelle gespeichert sind, eindeutig identifizieren.Diese Spalte oder dieser Satz von Spalten wird als Primärschlüssel bezeichnet.Die meisten Tabellen haben eine einzige Spalte als Primärschlüssel.Primärschlüsselspalten sind gegen NULLs und doppelte Werte eingeschränkt.

Zu beachtende Punkte –

• Eine Tabelle kann nur einen Primärschlüssel haben.

• Mehrere Spalten können unter einem zusammengesetzten Primärschlüssel zusammengefasst werden.

• Oracle erstellt intern einen eindeutigen Index, um eine Duplizierung der Spaltenwerte zu verhindern.Indizes werden später in PL/SQL besprochen.

Syntax:

Spaltenebene:

COLUMN 

Tabellenebene:

CONSTRAINT PRIMARY KEY 

Das folgende Beispiel zeigt, wie man die PRIMARY KEY-Beschränkung auf Spaltenebene verwendet.

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

Das folgende Beispiel zeigt, wie ein zusammengesetzter Primärschlüssel unter Verwendung der PRIMARY KEY-Beschränkung auf Tabellenebene definiert wird.

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

Fremdschlüssel

Wenn zwei Tabellen die Eltern-Kind-Beziehung auf der Grundlage einer bestimmten Spalte teilen, wird die verbindende Spalte in der Kindtabelle als Fremdschlüssel bezeichnet.Diese Eigenschaft der entsprechenden Spalte in der übergeordneten Tabelle wird als referentielle Integrität bezeichnet.die Werte der Fremdschlüsselspalte in der untergeordneten Tabelle können entweder null sein oder müssen die vorhandenen Werte der übergeordneten Tabelle sein.bitte beachten Sie, dass nur Primärschlüsselspalten der referenzierten Tabelle zur Durchsetzung der referentiellen Integrität geeignet sind.

Wenn ein Fremdschlüssel für die Spalte in der untergeordneten Tabelle definiert ist, erlaubt Oracle nicht, dass die übergeordnete Zeile gelöscht wird, wenn sie untergeordnete Zeilen enthält. Wenn jedoch die Option ON DELETE CASCADE zum Zeitpunkt der Definition des Fremdschlüssels angegeben wird, löscht Oracle alle untergeordneten Zeilen, während die übergeordnete Zeile gelöscht wird.Gleichermaßen bedeutet ON DELETE SET NULL, dass die Werte des Fremdschlüssels auf Null gesetzt werden, wenn eine Zeile in der übergeordneten Tabelle gelöscht wird.

Syntax:

Spaltenebene:

COLUMN 

Tabellenebene:

CONSTRAINT 

Das folgende Beispiel zeigt, wie die FOREIGN KEY-Beschränkung auf Spaltenebene verwendet wird.

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

Verwendung der ON DELETE CASCADE-Klausel

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

Check-Beschränkung

Manchmal müssen die in einer bestimmten Spalte gespeicherten Datenwerte in einen akzeptablen Wertebereich fallen.Eine CHECK-Beschränkung erfordert, dass die angegebene Prüfbedingung für jede in der Tabelle gespeicherte Zeile entweder wahr oder unbekannt ist.Check-Beschränkung ermöglicht es, einer Spalte eine bedingte Regel aufzuerlegen, die überprüft werden muss, bevor Daten in die Spalte eingefügt werden. Die Bedingung darf keine Unterabfrage oder Pseudospalte CURRVAL NEXTVAL, LEVEL, ROWNUM oder SYSDATE enthalten.

Oracle erlaubt es, dass eine einzelne Spalte mehr als eine CHECK-Beschränkung haben kann. In der Tat gibt es keine praktische Begrenzung für die Anzahl der CHECK-Beschränkungen, die für eine Spalte definiert werden können.

Syntax:

Spaltenebene:

COLUMN CONSTRAINT 

Tabellenebene:

CONSTRAINT CHECK (condition)

Das folgende Beispiel zeigt, wie man die CHECK-Beschränkung auf Spaltenebene verwendet.

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

Das folgende Beispiel zeigt, wie man die CHECK-Beschränkung auf Tabellenebene verwendet.

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

ALTER TABLE-Anweisung

Ein DBA kann Änderungen an der Tabellenstruktur oder den Spaltendefinitionen vornehmen, nachdem die Tabelle in der Datenbank erstellt wurde.Der DDL-Befehl ALTER TABLE wird verwendet, um solche Aktionen durchzuführen.Alter Befehl bietet mehrere Dienstprogramme ausschließlich für Schemaobjekte.Die ALTER TABLE-Anweisung wird verwendet, um eine Spalte in einer Tabelle hinzuzufügen, zu löschen, umzubenennen und zu ändern.

Die folgende ALTER TABLE-Anweisung benennt die Tabelle EMP in EMP_NEW um.

ALTER TABLE EMP RENAME TO EMP_NEW;

Die folgende ALTER TABLE-Anweisung fügt eine neue Spalte TESTCOL zur Tabelle EMP_NEW hinzu

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Die folgende ALTER TABLE-Anweisung benennt die Spalte TESTCOL in TESTNEW um.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Die folgende ALTER TABLE-Anweisung löscht die Spalte TESTNEW aus der Tabelle EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Die folgende ALTER TABLE-Anweisung fügt einen Primärschlüssel für die Spalte EMPLOYEE_ID hinzu.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Die unten stehende ALTER TABLE-Anweisung löscht den Primärschlüssel.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Die unten stehende ALTER TABLE-Anweisung schaltet den Tabellenmodus auf Nur-Lesen um.

ALTER TABLE EMP_NEW READ ONLY;

Nur-Lesen-Tabellen

Nur-Lesen-Tabellen sind eine Verbesserung in Oracle 11g. Sie ermöglichen es, dass die Tabellen nur zum Lesen verwendet werden. In früheren Oracle-Versionen wurden Tabellen durch die Gewährung von SELECT-Rechten für andere Benutzer schreibgeschützt, aber der Eigentümer hatte immer noch die Lese- und Schreibrechte. Wenn eine Tabelle jetzt als schreibgeschützt festgelegt wird, hat nicht einmal der Eigentümer Zugriff auf die Datenmanipulation.

Syntax:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Illustration

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-Anweisung

Die DROP TABLE-Anweisung wird verwendet, um eine Tabelle aus der Datenbank zu entfernen. Die gelöschte Tabelle und ihre Daten stehen nicht mehr zur Auswahl. Die gelöschte Tabelle kann mit dem Dienstprogramm FLASHBACK wiederhergestellt werden, wenn sie im Papierkorb verfügbar ist. Das Löschen einer Tabelle löscht den Index und die mit ihr verbundenen Trigger.

Syntax:

DROP TABLE 

Die unten stehende Anweisung löscht die Tabelle und legt sie in den Recyclebin.

DROP TABLE emp_new;

Die unten stehende Anweisung löscht die Tabelle und entfernt sie ebenfalls aus dem Recyclebin.

DROP TABLE emp_new PURGE;