Het gebruik van DDL-statements

Het gebruik van DDL-statements voor het maken en beheren van tabellen

Een schema is de verzameling van meerdere database-objecten, die bekend staan als schema-objecten.Deze objecten hebben directe toegang van hun eigenaar schema.Onderstaande tabel geeft een overzicht van de schema-objecten.

• Tabel – om gegevens op te slaan

• View – om gegevens in een gewenst formaat uit een of meer tabellen te projecteren

• Sequence – om numerieke waarden te genereren

• Index – om de prestaties van query’s op de tabellen te verbeteren

• Synoniem – alternatieve naam van een object

Eén van de eerste stappen bij het maken van een database is het maken van de tabellen waarin de gegevens van een organisatie zullen worden opgeslagen.Het ontwerpen van een database houdt in dat de gebruikerseisen voor verschillende organisatorische systemen, zoals orderinvoer, voorraadbeheer en debiteurenadministratie, worden vastgesteld. Ongeacht de grootte en complexiteit van de database, elke database bestaat uit tabellen.

Creëren van de tabel

Om een tabel in de database te creëren, moet een DBA bepaalde informatie bij de hand hebben – de tabelnaam, kolomnaam, kolomdatatypes, en kolomgroottes. Al deze informatie kan later worden gewijzigd met DDL-commando’s.

Tabelnaamconventies –

• De naam die u voor een tabel kiest, moet aan deze standaardregels voldoen:

• De naam moet beginnen met een letter A-Z of a-z

• Kan cijfers en underscores bevatten

• Kan in hoofdletters of kleine letters zijn

• Kan maximaal 30 tekens lang zijn

• Kan niet dezelfde naam van een ander bestaand object in uw schema gebruiken

• Mag geen SQL gereserveerd woord zijn

Volgt de bovenstaande richtlijnen, kan “EMP85” een geldige tabelnaam zijn.Maar 85EMP is dat niet. Ook UPDATE kan niet als tabelnaam worden gekozen omdat het een gereserveerd SQL-sleutelwoord is.

CREATE TABLE statement

De CREATE TABLE is een DDL statement dat wordt gebruikt om tabellen in de database aan te maken.De tabel wordt aangemaakt zodra het CREATE TABLE script wordt uitgevoerd en is klaar om de gegevens op te slaan.De gebruiker moet de CREATE TABLE systeemprivilege hebben om de tabel in zijn eigen schema aan te maken.Maar om een tabel in een willekeurig gebruikersschema te maken, moet de gebruiker de systeemprivilege CREATE ANY TABLE hebben.

Hier volgt de syntaxis van een basis CREATE TABLE statement.Er kunnen veel extra clausules zijn om expliciet de opslagspecificaties of segmentwaarden op te geven.

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

In de bovenstaande syntaxis specificeert DEFAULT de standaardwaarde die kan worden gebruikt tijdens INSERT statement als de kolom wordt genegeerd. Het kan geen verwijzingen bevatten naar andere tabel kolommen of pseudo kolommen (CURRVAL, NEXTVAL, LEVEL, en ROWNUM) behalve SYSDATE en USER, of datum constanten die niet volledig zijn gespecificeerd.

Constraints zijn de regels die optioneel op kolom- of tabelniveau zijn gedefinieerd (later in dit hoofdstuk behandeld). Deze regels worden gecontroleerd tijdens elke gegevensactie (invoegen, bijwerken) in de tabel en geven een foutmelding om de actie af te breken wanneer deze wordt overtreden.

Voorbeeld: met het onderstaande CREATE TABLE statement wordt een tabel EMP_TEST gecreëerd. Let op de kolom specificaties, het gegevenstype en de precisie.

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

Een gebruiker kan tabellen uit het schema van een andere gebruiker opvragen door de gebruikersnaam of het schema aan de tabelnaam toe te voegen. Bijvoorbeeld, een gebruiker GUEST wil de naam en het salaris van een werknemer opvragen in de tabel EMP_TEST, die eigendom is van SCOTT. Hij kan de volgende query uitvoeren –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Een kolom kan een standaard waarde hebben tijdens het maken van de tabel. Het helpt om de NULL waarden die in de kolom komen te beperken. De standaardwaarde kan worden afgeleid uit een letterwoord, uitdrukking of SQL-functie die een compatibel gegevenstype met de kolom moet teruggeven. In het onderstaande CREATE TABLE statement heeft de kolom LOCATION_ID de default waarde 100.

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

CTAS – Create table using subquery

Een tabel kan worden aangemaakt vanuit een bestaande tabel in de database met behulp van een subquery optie.Het kopieert zowel de tabelstructuur als de gegevens uit de tabel. Gegevens kunnen ook worden gekopieerd op basis van voorwaarden.De kolom datatype definities inclusief de expliciet opgelegde NOT NULL constraints worden gekopieerd naar de nieuwe tabel.

Het onderstaande CTAS script maakt een nieuwe tabel EMP_BACKUP. Werknemer gegevens van afdeling 20 worden gekopieerd naar de nieuwe tabel.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Gegevens typen

Gegevens typen worden gebruikt om het basis gedrag van een kolom in de tabel te specificeren.Op een bredere basis, kolom gedrag kan ofwel behoren tot nummer, karakter of een datum familie.Er zijn meerdere andere subtypen die behoren tot deze families.

Gegevens type NUMBER

Het datatype NUMBER omvat zowel gehele, fixed-point, en floating-point numerieke waarden.Vroege versies van Oracle definieerden verschillende datatypes voor elk van deze verschillende soorten getallen, maar nu dient het datatype NUMBER voor al deze doeleinden.Kies het datatype NUMBER wanneer een kolom numerieke gegevens moet opslaan die in wiskundige berekeningen kunnen worden gebruikt.Af en toe wordt het datatype NUMBER gebruikt om identificatienummers op te slaan waarbij deze nummers door het DBMS als opeenvolgende nummers worden gegenereerd.

GETAL (p, s), waarbij p de precisie is tot 38 cijfers en s de schaal (aantal cijfers rechts van de decimale punt).De schaal kan liggen tussen -84 en 127.

GETAL (p), is een fixed-point getal met een schaal van nul en een precisie van p.

FLOAT , waarbij p de binaire precisie is die kan liggen tussen 1 en 126. Als p niet wordt opgegeven, is de standaardwaarde binair 126.

Datum gegevenstype

Voor elk DATUM-gegevenstype worden Centurie, Jaar, Maand, Dag, Uur, Minuut, Seconde in de database opgeslagen. Elk databasesysteem heeft een standaard datumformaat dat wordt gedefinieerd door de initialisatieparameter NLS_DATE_FORMAT. Deze parameter is meestal ingesteld op DD-MON-YY.Als u geen tijd opgeeft, is de standaardtijd 12:00:00 a.m.

Character datatype

Oracle ondersteunt drie voorgedefinieerde character datatypes waaronder CHAR, VARCHAR, VARCHAR2, en LONG.VARCHAR en VARCHAR2 zijn eigenlijk synoniem, en Oracle raadt aan VARCHAR2 te gebruiken in plaats van VARCHAR.Gebruik het CHAR datatype wanneer de kolom karakterwaarden met een vaste lengte zal opslaan. Bijvoorbeeld, een Sociale Zekerheidsnummer (SSN) in de Verenigde Staten wordt toegekend aan elke burger en is altijd 9 karakters groot (ook al is een SSN strikt samengesteld uit cijfers, de cijfers worden behandeld als karakters), en zou worden gespecificeerd als CHAR(9). Gebruik het datatype VARCHAR2 om alfanumerieke gegevens met variabele lengte op te slaan. Zo zal bijvoorbeeld de naam of het adres van een klant aanzienlijk variëren wat betreft het aantal op te slaan karakters. De maximumgrootte van een VARCHAR2-kolom is 4.000 karakters.

LOB datatype

Oracle biedt verschillende LOB datatypen, waaronder CLOB (character large object) en BLOB (binary large object).In kolommen van deze datatypen kunnen ongestructureerde gegevens worden opgeslagen, waaronder tekst, afbeeldingen, video en ruimtelijke gegevens.Het CLOB-datatype kan tot acht terabyte aan tekengegevens opslaan met gebruikmaking van de CHAR-databasekarakterset.Het BLOB-datatype wordt gebruikt voor de opslag van ongestructureerde binaire grote objecten zoals die welke worden geassocieerd met beeld- en videogegevens waarbij de gegevens gewoon een stroom van “bit”-waarden zijn.Een BLOB datatype kan tot acht terabyte aan binaire gegevens opslaan.Het NCLOB datatype kan grote tekenobjecten opslaan in multibyte nationale tekenset tot 8TB tot 128TB.De BFILE datatype waarde werkt als een bestandszoeker of pointer naar een bestand op het bestandssysteem van de server. De maximaal ondersteunde bestandsgrootte is 8TB tot 128TB.

Constraints

Constraints zijn de set van regels die in Oracle tabellen zijn gedefinieerd om data integriteit te waarborgen.Deze regels worden afgedwongen voor elke kolom of set van kolommen.Wanneer de tabel deelneemt aan data actie, worden deze regels gevalideerd en roepen ze een exception op bij overtreding. De beschikbare constraint types zijn NOT NULL, Primary Key, Unique, Check, en Foreign Key.

De onderstaande syntax kan worden gebruikt om constraint op te leggen op kolom niveau.

Syntax:

column constraint_type

Alle constraints, behalve NOT NULL, kunnen ook op tabelniveau worden gedefinieerd. Samengestelde constraints kunnen alleen op tabelniveau worden gespecificeerd.

NOT NULL Constraint

Een NOT NULL constraint betekent dat een gegevensrij een waarde moet hebben voor de kolom die als NOT NULL is gespecificeerd.Als een kolom als NOT NULL is gespecificeerd, zal het Oracle RDBMS niet toestaan dat rijen in de werknemerstabel worden opgeslagen die deze constraint schenden.Deze constraint kan alleen op kolomniveau worden gedefinieerd, en niet op tabelniveau.

Syntax:

COLUMN 

UNIQUE constraint

Soms is het nodig om uniciteit af te dwingen voor een kolomwaarde die geen primaire sleutelkolom is.De UNIQUE constraint kan worden gebruikt om deze regel af te dwingen en Oracle zal rijen die de unique constraint schenden weigeren.Unique constraint zorgt ervoor dat de kolomwaarden verschillend zijn, zonder duplicaten.

Syntax:

Kolom Niveau:

COLUMN 

Tabelniveau: CONSTRAINT UNIQUE (kolomnaam)

Note: Oracle maakt intern een unieke index om duplicatie in de kolomwaarden te voorkomen.Indexen worden later in PL/SQL besproken.

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

In het geval van een samengestelde unieke sleutel, moet deze op tabelniveau worden gedefinieerd zoals hieronder.

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

Primary Key

Elke tabel moet normaliter een kolom of kolommenverzameling bevatten die rijen gegevens die in de tabel zijn opgeslagen op unieke wijze identificeert.Deze kolom of kolommenverzameling wordt de primaire sleutel genoemd.De meeste tabellen hebben een enkele kolom als primaire sleutel.Primaire sleutelkolommen zijn beperkt tegen NULL’s en dubbele waarden.

Punten om op te merken –

• Een tabel kan slechts één primaire sleutel hebben.

• Meerdere kolommen kunnen worden samengevoegd onder een samengestelde primaire sleutel.

• Oracle maakt intern een unieke index om duplicatie in de kolomwaarden te voorkomen.Indexen worden later in PL/SQL besproken.

Syntax:

Kolumniveau:

COLUMN 

Tabelniveau:

CONSTRAINT PRIMARY KEY 

Het volgende voorbeeld laat zien hoe PRIMARY KEY constraint op kolomniveau kan worden gebruikt.

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

Het volgende voorbeeld laat zien hoe u een samengestelde primaire sleutel definieert met behulp van PRIMARY KEY constraint op tabelniveau.

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

Foreign Key

Wanneer twee tabellen de ouder-kind relatie delen op basis van een specifieke kolom, staat de verbindende kolom in de kind-tabel bekend als Foreign Key.Deze eigenschap van corresponderende kolom in de parent tabel staat bekend als referentiële integriteit.Foreign Key kolom waarden in de kind tabel kan ofwel null of moet de bestaande waarden van de parent tabel.Let op dat alleen primaire sleutel kolommen van de tabel waarnaar wordt verwezen in aanmerking komen voor referentiële integriteit af te dwingen.

Als een foreign key is gedefinieerd op de kolom in de kind tabel dan Oracle niet toestaan dat de bovenliggende rij wordt verwijderd, als het bevat geen kind rijen.Echter, als ON DELETE CASCADE optie wordt gegeven op het moment van het definiëren van een foreign key, Oracle verwijdert alle kind rijen terwijl de bovenliggende rij wordt verwijderd.Evenzo geeft ON DELETE SET NULL aan dat wanneer een rij in de parent tabel wordt verwijderd, de foreign key waarden op null worden gezet.

Syntax:

Kolomniveau:

COLUMN 

Tabelniveau:

CONSTRAINT 

Het volgende voorbeeld laat zien hoe de FOREIGN KEY constraint op kolomniveau kan worden gebruikt.

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

Gebruik van ON DELETE CASCADE-clausule

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

Check constraint

Soms moeten de gegevenswaarden die in een specifieke kolom zijn opgeslagen, binnen een acceptabel waardenbereik vallen.Een CHECK constraint vereist dat de gespecificeerde controlevoorwaarde waar of onbekend is voor elke rij die in de tabel is opgeslagen.Check constraint maakt het mogelijk om een voorwaardelijke regel aan een kolom op te leggen, die moet worden gevalideerd voordat gegevens in de kolom worden ingevoegd. De voorwaarde mag geen sub query of pseudo kolom CURRVAL NEXTVAL, LEVEL, ROWNUM, of SYSDATE bevatten.

Oracle staat toe dat een enkele kolom meer dan één CHECK constraint heeft. In feite is er geen praktische limiet aan het aantal CHECK constraints dat voor een kolom kan worden gedefinieerd.

Syntax:

Kolomniveau:

COLUMN CONSTRAINT 

Tabelniveau:

CONSTRAINT CHECK (condition)

Het volgende voorbeeld laat zien hoe u CHECK constraint op kolomniveau kunt gebruiken.

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

Het volgende voorbeeld laat zien hoe u CHECK constraint op tabelniveau kunt gebruiken.

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

ALTER TABLE statement

Een DBA kan wijzigingen aanbrengen in de tabelstructuur of kolomdefinities nadat de tabel in de database is aangemaakt.Het DDL commando ALTER TABLE wordt gebruikt om dergelijke acties uit te voeren.Alter commando biedt meerdere utilities exclusief voor schema objecten.Het ALTER TABLE statement wordt gebruikt om een kolom in een tabel toe te voegen, te laten vallen, te hernoemen, en te wijzigen.

Het onderstaande ALTER TABLE statement hernoemt de tabel EMP naar EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Het onderstaande ALTER TABLE statement voegt een nieuwe kolom TESTCOL toe aan de EMP_NEW tabel

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Het onderstaande ALTER TABLE statement hernoemt de kolom TESTCOL naar TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Het onderstaande ALTER TABLE statement laat de kolom TESTNEW uit de EMP_NEW tabel vallen

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Het onderstaande ALTER TABLE statement voegt primaire sleutel toe op de EMPLOYEE_ID kolom.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Het onderstaande ALTER TABLE statement laat de primaire sleutel vallen.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Het onderstaande ALTER TABLE statement zet de tabelmodus op alleen-lezen.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read only tables kwamen als een verbetering in Oracle 11g.Het staat de tabellen toe om te worden gebruikt voor alleen-lezen doeleinden. In eerdere Oracle-versies, tabellen werden alleen-lezen gemaakt door het verlenen van SELECT voorrecht aan de andere gebruikers, maar eigenaar had nog steeds de lezen schrijven voorrecht.Maar nu, als een tabel is ingesteld als Alleen-lezen, zelfs eigenaar heeft geen toegang op de gegevens manipulatie.

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 statement

Het DROP TABLE statement wordt gebruikt om een tabel uit de database te verwijderen. De tabel en zijn gegevens blijven niet langer beschikbaar voor selectie.Dropped tabel kan worden hersteld met behulp van FLASHBACK utility, indien beschikbaar in recyclebin.Dropping een tabel laat de index en triggers geassocieerd met het.

Syntax:

DROP TABLE 

Het onderstaande statement zal de tabel laten vallen en in de recyclebin plaatsen.

DROP TABLE emp_new;

Het onderstaande statement zal de tabel laten vallen en ook uit de recyclebin spoelen.

DROP TABLE emp_new PURGE;