Verwenden von DDL-Anweisungen

Verwenden von DDL-Anweisungen zum Erstellen und Verwalten von Tabellen

Ein Schema ist die Sammlung mehrerer Datenbankobjekte, die als Schemaobjekte bezeichnet werden. Diese Objekte haben direkten Zugriff über ihr Eigentümerschema. In der folgenden Tabelle sind die Schemaobjekte aufgeführt.

Tabelle - zum Speichern von Daten
Ansicht - um Daten in einem gewünschten Format aus einer oder mehreren Tabellen zu projizieren
Sequenz - um numerische Werte zu generieren
Index - zur Verbesserung der Leistung von Abfragen in den Tabellen
Synonym - alternativer Name eines Objekts

Einer der ersten Schritte beim Erstellen einer Datenbank besteht darin, die Tabellen zu erstellen, in denen die Daten einer Organisation gespeichert werden. Beim Datenbankdesign werden die Systembenutzeranforderungen für verschiedene Organisationssysteme wie Auftragserfassung, Bestandsverwaltung und Debitorenbuchhaltung ermittelt. Unabhängig von Datenbankgröße und -komplexität besteht jede Datenbank aus Tabellen.

Tabelle erstellen

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

Namenskonventionen für Tabellen -

Der Name, den Sie für eine Tabelle wählen, muss den folgenden Standardregeln entsprechen:
Der Name muss mit einem Buchstaben AZ oder az beginnen
Kann Zahlen und Unterstriche enthalten
Kann in Großbuchstaben geschrieben werden
Kann bis zu 30 Zeichen lang sein
Es kann nicht derselbe Name eines anderen vorhandenen Objekts in Ihrem Schema verwendet werden
Darf kein reserviertes SQL-Wort sein

Gemäß den obigen Richtlinien kann 'EMP85' ein gültiger Tabellenname sein. 85EMP ist dies jedoch nicht. Ähnlich kann UPDATE nicht als Tabellenname ausgewählt werden, da es sich um ein reserviertes SQL-Schlüsselwort handelt.

Anweisung CREATE TABLE

Die CREATE TABLE ist eine DDL-Anweisung, mit der Tabellen in der Datenbank erstellt werden. Die Tabelle wird erstellt, sobald das Skript CREATE TABLE ausgeführt wird und bereit ist, die Daten weiter zu speichern. Der Benutzer muss über die Systemberechtigung CREATE TABLE verfügen, um sie erstellen zu können Die Tabelle in einem eigenen Schema. Um jedoch eine Tabelle in einem Benutzerschema zu erstellen, muss der Benutzer über das Schema CREATE ANY TABLE verfügen.

Hier ist die Syntax einer grundlegenden CREATE TABLE-Anweisung. Es kann viele zusätzliche Klauseln geben, um die Speicherspezifikationen oder Segmentwerte explizit anzugeben.

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

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

Einschränkungen sind die Regeln, die optional auf Spalten- oder Tabellenebene definiert werden (weiter unten in diesem Kapitel behandelt). Diese Regeln werden während jeder Datenaktion (Einfügen, Aktualisieren) in der Tabelle überprüft und lösen einen Fehler aus, um die Aktion bei ihrer Verletzung abzubrechen.

Mit der folgenden Anweisung CREATE TABLE wird beispielsweise eine Tabelle EMP_TEST erstellt. Beachten Sie die Spaltenspezifikationen, 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 die Tabellen aus dem Schema eines anderen Benutzers verweisen, indem er dem Benutzernamen oder Schema den Tabellennamen voranstellt. Beispielsweise möchte ein Benutzer GUEST den Namen und das Gehalt des 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 während der Tabellenerstellung einen Standardwert enthalten. Dies hilft, die NULL-Werte einzuschränken, die in die Spalte gelangen. Der Standardwert kann entweder aus einer Literal-, Ausdrucks- oder SQL-Funktion abgeleitet werden, die einen kompatiblen Datentyp an die Spalte zurückgeben muss. Beachten Sie in der folgenden Anweisung CREATE TABLE, 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 Unterabfrage erstellen

Eine Tabelle kann aus einer vorhandenen Tabelle in der Datenbank mithilfe einer Unterabfrageoption erstellt werden. Sie kopiert die Tabellenstruktur sowie die Daten aus der Tabelle. Daten können auch basierend auf Bedingungen kopiert werden. Die Spalten-Datentypdefinitionen einschließlich der explizit auferlegten NOT NULL-Einschränkungen werden in die neue Tabelle kopiert.

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

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

Datentypen

Datentypen werden verwendet, um das grundlegende Verhalten einer Spalte in der Tabelle anzugeben. Auf einer breiteren Basis kann das Spaltenverhalten entweder zu einer Zahl, einem Zeichen oder einer Datumsfamilie gehören. Es gibt mehrere andere Untertypen, die zu diesen Familien gehören.

Nummer Datentyp

Der Datentyp NUMBER umfasst sowohl numerische Ganzzahl-, Festkomma- als auch Gleitkommawerte. Frühe Versionen von Oracle definierten unterschiedliche Datentypen für jeden dieser unterschiedlichen Zahlentypen. Jetzt dient der Datentyp NUMBER all diesen Zwecken. Wählen Sie den Datentyp NUMBER, wenn In einer Spalte müssen numerische Daten gespeichert werden, die für mathematische Berechnungen verwendet werden können. Gelegentlich wird der Datentyp NUMBER zum Speichern von Identifikationsnummern verwendet, bei denen diese Nummern vom DBMS als fortlaufende Nummern generiert werden.

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

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

FLOAT [(p)], wobei p die binäre Genauigkeit ist, die von 1 bis 126 reichen kann. Wenn p nicht angegeben ist, ist der Standardwert binär 126.

Datumsdatentyp

Für jeden DATE-Datentyp werden Jahrhundert, Jahr, Monat, Tag, Stunde, Minute, Sekunde in der Datenbank gespeichert. Jedes Datenbanksystem verfügt über ein Standard-Datumsformat, das durch den Initialisierungsparameter NLS_DATE_FORMAT definiert wird. Dieser Parameter wird normalerweise auf TT-MON-JJ gesetzt. Wenn Sie keine Zeit angeben, ist die Standardzeit 12:00:00 Uhr

Zeichendatentyp

Oracle unterstützt drei vordefinierte Zeichendatentypen, darunter CHAR, VARCHAR, VARCHAR2 und LONG.VARCHAR und VARCHAR2. Oracle empfiehlt die Verwendung von VARCHAR2 anstelle von VARCHAR. Verwenden Sie den CHAR-Datentyp, wenn in der Spalte Zeichenwerte mit fester Länge gespeichert werden Beispielsweise wird jedem Bürger eine Sozialversicherungsnummer (SSN) in den USA zugewiesen, die immer 9 Zeichen groß ist (obwohl eine SSN ausschließlich aus Ziffern besteht, werden die Ziffern als Zeichen behandelt) und als CHAR angegeben (9). Verwenden Sie den Datentyp VARCHAR2, um alphanumerische Daten mit variabler Länge zu speichern. Beispielsweise variiert ein Kundenname oder eine Kundenadresse erheblich in Bezug auf die Anzahl der zu speichernden Zeichen. Die maximale Größe einer VARCHAR2-Spalte beträgt 4.000 Zeichen.

LOB-Datentyp

Oracle bietet verschiedene LOB-Datentypen an, darunter CLOB (Zeichen großes Objekt) und BLOB (binäres großes Objekt). Spalten dieser Datentypen können unstrukturierte Daten wie Text-, Bild-, Video- und Geodaten speichern. Der CLOB-Datentyp kann bis zu acht Terabyte speichern Der BLOB-Datentyp wird zum Speichern unstrukturierter binärer großer Objekte verwendet, z. B. von Bild- und Videodaten, wobei die Daten einfach ein Strom von "Bit" -Werten sind. Ein BLOB-Datentyp kann bis zu speichern Acht Terabyte Binärdaten. Der NCLOB-Datentyp kann zeichengroße Objekte in nationalen Multibyte-Zeichen mit einer Größe von 8 TB bis 128 TB speichern. Der Datentypwert BFILE fungiert als Dateisuche oder Zeiger auf eine Datei im Dateisystem des Servers. Die maximal unterstützte Dateigröße beträgt 8 TB bis 128 TB.

Einschränkungen

Einschränkungen sind die in Oracle-Tabellen definierten Regeln, um die Datenintegrität sicherzustellen. Diese Regeln werden für jede Spalte oder jeden Satz von Spalten erzwungen. Wenn die Tabelle an Datenaktionen teilnimmt, werden diese Regeln validiert und lösen bei Verstößen eine Ausnahme aus. Die verfügbaren Einschränkungstypen sind NICHT NULL, Primärschlüssel, Eindeutig, Prüfen und Fremdschlüssel.

Die folgende Syntax kann verwendet werden, um Einschränkungen auf Spaltenebene festzulegen.

Syntax:

column [data type] [CONSTRAINT constraint_name] constraint_type

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

NICHT NULL Einschränkung

Eine NOT NULL-Einschränkung bedeutet, dass eine Datenzeile einen Wert für die als NOT NULL angegebene Spalte haben muss. Wenn eine Spalte als NOT NULL angegeben wird, erlaubt das Oracle RDBMS nicht, dass Zeilen in der Mitarbeitertabelle gespeichert werden, die gegen diese Einschränkung verstoßen kann nur auf Spaltenebene und nicht auf Tabellenebene definiert werden.

Syntax:

COLUMN [data type] [NOT NULL]

Einzigartige Beschränkung

Manchmal ist es erforderlich, die Eindeutigkeit für einen Spaltenwert zu erzwingen, der keine Primärschlüsselspalte ist. Mit der UNIQUE-Einschränkung kann diese Regel erzwungen werden, und Oracle lehnt alle Zeilen ab, die gegen die eindeutige Einschränkung verstoßen. Die eindeutige Einschränkung stellt sicher, dass die Spaltenwerte unterschiedlich sind ohne Duplikate.

Syntax:

Column Level:

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

Table Level: CONSTRAINT [Einschränkungsname] UNIQUE (Spaltenname)

Hinweis: Oracle erstellt intern einen eindeutigen Index, um Doppelungen in den Spaltenwerten zu vermeiden. Indexe werden später in PL / SQL erläutert.

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 die in der Tabelle gespeicherten Datenzeilen eindeutig identifizieren. Diese Spalte oder Satz von Spalten wird als Primärschlüssel bezeichnet. Die meisten Tabellen haben eine einzelne Spalte als Primärschlüssel Schlüsselspalten sind auf NULL-Werte und doppelte Werte beschränkt.

Zu beachtende Punkte -

Eine Tabelle kann nur einen Primärschlüssel haben.
Unter einem zusammengesetzten Primärschlüssel können mehrere Spalten zusammengefasst werden.
Oracle erstellt intern einen eindeutigen Index, um Doppelungen in den Spaltenwerten zu vermeiden. Indexe werden später in PL / SQL erläutert.

Syntax:

Column level:

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

Table level:

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

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

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

Das folgende Beispiel zeigt, wie der zusammengesetzte Primärschlüssel mithilfe der PRIMARY KEY-Einschränkung auf Tabellenebene definiert wird.

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

Unbekannter Schlüssel

Wenn zwei Tabellen die untergeordnete untergeordnete Beziehung basierend auf einer bestimmten Spalte gemeinsam nutzen, wird die Verknüpfungsspalte in der untergeordneten Tabelle als Fremdschlüssel bezeichnet. Diese Eigenschaft der entsprechenden Spalte in der übergeordneten Tabelle wird als referenzielle Integrität bezeichnet. Ausländische Schlüsselspaltenwerte 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 berechtigt sind, die referenzielle Integrität zu erzwingen.

Wenn in der Spalte in der untergeordneten Tabelle ein Fremdschlüssel definiert ist, lässt Oracle nicht zu, dass die übergeordnete Zeile gelöscht wird, wenn sie untergeordnete Zeilen enthält. Wenn jedoch zum Zeitpunkt der Definition des Fremdschlüssels die Option ON DELETE CASCADE angegeben ist, wird Oracle gelöscht Alle untergeordneten Zeilen, während die übergeordnete Zeile gelöscht wird. Ähnlich gibt ON DELETE SET NULL an, dass beim Löschen einer Zeile in der übergeordneten Tabelle die Fremdschlüsselwerte auf null gesetzt werden.

Syntax:

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)]

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

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,
   ...
);

Überprüfen Sie die Einschränkung

Manchmal müssen die in einer bestimmten Spalte gespeicherten Datenwerte in einen akzeptablen Wertebereich fallen. Eine CHECK-Einschränkung erfordert, dass die angegebene Prüfbedingung für jede in der Tabelle gespeicherte Zeile entweder wahr oder unbekannt ist. Mit der Prüfbedingung kann a eine bedingte Regel auferlegt werden Spalte, die validiert 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 einer einzelnen Spalte, mehr als eine CHECK-Einschränkung zu haben. Tatsächlich gibt es keine praktische Begrenzung für die Anzahl der CHECK-Einschränkungen, die für eine Spalte definiert werden können.

Syntax:

Column level:

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

Table level:

CONSTRAINT [name] CHECK (condition)

Das folgende Beispiel zeigt, wie die CHECK-Einschränkung auf Spaltenebene verwendet wird.

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

Das folgende Beispiel zeigt, wie die CHECK-Einschränkung auf Tabellenebene verwendet wird.

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. Mit dem DDL-Befehl ALTER TABLE werden solche Aktionen ausgeführt. Der Befehl Alter bietet mehrere Dienstprogramme, die ausschließlich für Schemaobjekte gelten. Die Anweisung ALTER TABLE wird verwendet Hinzufügen, Löschen, Umbenennen und Ändern einer Spalte in einer Tabelle.

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

ALTER TABLE EMP RENAME TO EMP_NEW;

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

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

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

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

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

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

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

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Die folgende Anweisung ALTER TABLE löscht den Primärschlüssel.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Die folgende Anweisung ALTER TABLE schaltet den Tabellenmodus auf schreibgeschützt um.

ALTER TABLE EMP_NEW READ ONLY;

Nur-Lese-Tabellen

Schreibgeschützte Tabellen wurden als Erweiterung in Oracle 11g bereitgestellt. Sie ermöglichen die Verwendung der Tabellen für schreibgeschützte Zwecke. In früheren Oracle-Versionen wurden Tabellen schreibgeschützt, indem den anderen Benutzern die Berechtigung SELECT gewährt wurde. Der Eigentümer hatte jedoch weiterhin die Berechtigung zum Lesen und Schreiben. Wenn eine Tabelle jedoch als schreibgeschützt festgelegt ist, hat selbst der Eigentümer keinen Zugriff auf die Datenmanipulation .

Syntax:

ALTER TALE [TABLE NAME] READ ONLY

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

Mit der Anweisung DROP TABLE wird eine Tabelle aus der Datenbank entfernt. Die gelöschte Tabelle und ihre Daten stehen nicht mehr zur Auswahl zur Verfügung. Die gelöschte Tabelle kann mit dem Dienstprogramm FLASHBACK wiederhergestellt werden, sofern sie in der Papierkorbdatei verfügbar ist. Durch das Löschen einer Tabelle werden der Index und die damit verbundenen Trigger gelöscht.