Anvendelse af DDL-statements

Anvendelse af DDL-statements til at oprette og administrere tabeller

Et skema er en samling af flere databaseobjekter, som er kendt som skemaobjekter.Disse objekter har direkte adgang af deres ejer skema.Nedenstående tabel viser skemaobjekterne.

• Tabel – til at gemme data

• View – til at projicere data i et ønsket format fra en eller flere tabeller

• Sequence – til at generere numeriske værdier

• Index – til at generere numeriske værdier

• Index – til at generere data i et ønsket format fra en eller flere tabeller

• . for at forbedre ydeevnen ved forespørgsler på tabellerne

• Synonym – alternativt navn på et objekt

Et af de første skridt i oprettelsen af en database er at oprette de tabeller, der skal gemme en organisations data.Databasedesign indebærer identifikation af systembrugerkrav til forskellige organisatoriske systemer som f.eks. ordreindgang, lagerstyring og debitorbogholderi. Uanset databasens størrelse og kompleksitet består hver database af tabeller.

Skabelse af tabellen

For at oprette en tabel i databasen skal en DBA have visse oplysninger i hånden – tabellens navn, kolonnens navn, kolonnens datatyper og kolonnestørrelser. Alle disse oplysninger kan senere ændres ved hjælp af DDL-kommandoer.

Navnekonventioner for tabeller –

• Det navn, du vælger til en tabel, skal følge disse standardregler:

• Navnet skal begynde med et bogstav A-Z eller a-z

• Kan indeholde tal og understregninger

• Kan være med store eller små bogstaver

• Kan have en længde på op til 30 tegn

• Kan ikke bruge det samme navn på et andet eksisterende objekt i dit skema

• Må ikke være et SQL-reserveret ord

Følger ovenstående retningslinjer, ‘EMP85’ kan være et gyldigt tabelnavn.Men det er 85EMP ikke.På samme måde kan UPDATE ikke vælges som et bordnavn, da det er et reserveret nøgleord i SQL.

CREATE TABLE statement

CREATE TABLE er et DDL statement, som bruges til at oprette tabeller i databasen.Tabellen bliver oprettet, så snart CREATE TABLE scriptet er udført, og er klar til at indeholde data fremover.Brugeren skal have CREATE TABLE systemprivilegiet for at kunne oprette tabellen i sit eget skema.Men for at oprette en tabel i enhver brugers skema skal brugeren have CREATE ANY TABLE-skemaet.

Her er syntaksen for en grundlæggende CREATE TABLE-erklæring.Der kan være mange yderligere klausuler for eksplicit at give lagringsspecifikationerne eller segmentværdierne.

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

I ovenstående syntaks angiver DEFAULT standardværdien, som kan bruges under INSERT-erklæringen, hvis kolonnen ignoreres. Den kan ikke indeholde henvisninger til andre tabelkolonner eller pseudokolonner (CURRVAL, NEXTVAL, LEVEL og ROWNUM) undtagen SYSDATE og USER, eller datokonstanter, som ikke er fuldt specificeret.

Begrænsninger er de regler, der er defineret valgfrit på kolonne- eller tabelniveau (behandles senere i dette kapitel).Disse regler kontrolleres under enhver datahandling (Indsæt, opdatering) på tabellen og giver anledning til fejl for at afbryde handlingen ved overtrædelse.

Til eksempel opretter CREATE TABLE-anvisningen nedenfor en tabel EMP_TEST. Bemærk kolonnespecifikationerne, datatype og præcision.

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

En bruger kan henvise til tabellerne fra andre brugeres skemaer ved at sætte brugernavnet eller skemaet i præfiks med tabelnavnet, f.eks. ønsker en bruger GUEST at forespørge medarbejderens navn og løn fra tabellen EMP_TEST, som er ejet af SCOTT. Han kan sende nedenstående forespørgsel –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

En kolonne kan have en standardværdi, når tabellen oprettes.Det hjælper med at begrænse NULL-værdier, der kommer ind i kolonnen. Standardværdien kan udledes af enten et bogstav, et udtryk eller en SQL-funktion, som skal returnere en datatype, der er kompatibel med kolonnen. I nedenstående CREATE TABLE-anvisning skal du bemærke, at LOCATION_ID-kolonnen har standardværdien 100.

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

CTAS – Opret tabel ved hjælp af subquery

En tabel kan oprettes fra en eksisterende tabel i databasen ved hjælp af en subquery-mulighed.Den kopierer både tabellens struktur og dataene fra tabellen. Data kan også kopieres baseret på betingelser.Definitionerne af kolonnedatatyper, herunder de eksplicit pålagte NOT NULL-begrænsninger, kopieres ind i den nye tabel.

Det nedenstående CTAS-script opretter en ny tabel EMP_BACKUP. Medarbejderdata for afdeling 20 kopieres til den nye tabel.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Datatyper

Datatyper bruges til at angive den grundlæggende adfærd for en kolonne i tabellen.På et bredere grundlag kan kolonneadfærd enten tilhøre en tal-, tegn- eller datofamilie.Der er flere andre undertyper, som hører til disse familier.

Tal-datatatype

Datatypen NUMBER omfatter både heltals-, fixed-point- og floating-point-numeriske værdier.Tidlige versioner af Oracle definerede forskellige datatyper for hver af disse forskellige typer af tal,men nu tjener datatypen NUMBER alle disse formål.Vælg datatypen NUMBER, når en kolonne skal gemme numeriske data, der kan bruges i matematiske beregninger.Indimellem bruges datatypen NUMBER til at gemme identifikationsnumre, hvor disse numre genereres af DBMS’et som fortløbende numre.

NUMBER (p, s), hvor p er præcisionen op til 38 cifre og s er skalaen (antal cifre til højre for decimalkommaet).Skalaen kan ligge mellem -84 og 127.

NUMBER (p),er et fastkomma-tal med en skala på nul og en præcision på p.

FLOAT ,hvor p er den binære præcision, der kan ligge mellem 1 og 126. Hvis p ikke er angivet, er standardværdien binær 126.

Datadatatype

For hver datatype DATE gemmes århundrede, år, måned, dag, time, minut og sekund i databasen. Hvert databasesystem har et standarddatformat, der er defineret af initialiseringsparameteren NLS_DATE_FORMAT. Denne parameter er normalt indstillet til DD-MON-YY.Hvis du ikke angiver et tidspunkt, er standardtidspunktet 12:00:00.

Karakterdatatype

Oracle understøtter tre foruddefinerede karakterdatatyper, herunder CHAR, VARCHAR, VARCHAR2 og LONG.VARCHAR og VARCHAR2 er faktisk synonyme, og Oracle anbefaler, at du bruger VARCHAR2 i stedet for VARCHAR.Brug CHAR-datatypen, når kolonnen skal lagre karakterværdier, der har en fast længde.F.eks. tildeles et socialsikringsnummer (SSN) i USA til alle borgere og har altid en størrelse på 9 tegn (selv om et SSN udelukkende består af cifre, behandles cifrene som tegn), og det angives som CHAR(9). Brug datatypen VARCHAR2 til at gemme alfanumeriske data med variabel længde, f.eks. vil et kundenavn eller en adresse variere betydeligt med hensyn til antallet af tegn, der skal gemmes, og den maksimale størrelse af en VARCHAR2-kolonne er 4.000 tegn.

LOB-datatatype

Oracle tilbyder flere forskellige LOB-datatyper, herunder CLOB (character large object) og BLOB (binary large object).Kolonner af disse datatyper kan gemme ustrukturerede data, herunder tekst, billeder, video og rumlige data.CLOB-datatypen kan lagre op til otte terabyte tegnedata ved hjælp af CHAR-databasens tegnsæt, og BLOB-datatypen bruges til at lagre ustrukturerede binære store objekter, f.eks. i forbindelse med billed- og videodata, hvor dataene blot er en strøm af “bit”-værdier.En BLOB-datatype kan lagre op til otte terabyte binære data.NCLOB-datatypen kan lagre store karakterobjekter i multibyte nationale tegnsæt på op til 8 TB til 128 TB.BFILE-datatypen fungerer som en fillokalisator eller en pegepind til en fil i serverens filsystem. Den maksimale filstørrelse, der understøttes, er 8TB til 128TB.

Constraints

Constraints er det sæt regler, der er defineret i Oracle-tabeller for at sikre dataintegritet.Disse regler håndhæves placeret for hver kolonne eller sæt af kolonner.Når tabellen deltager i datahandling, valideres disse regler og giver anledning til en undtagelse ved overtrædelse. De tilgængelige begrænsningstyper er NOT NULL, Primary Key, Unique, Check og Foreign Key.

Den nedenstående syntaks kan bruges til at pålægge begrænsning på kolonniveau.

Syntaks:

column constraint_type

Alle begrænsninger, undtagen NOT NULL, kan også defineres på tabelniveau. Sammensatte begrænsninger kan kun angives på tabelniveau.

NOT NULL-begrænsning

En NOT NULL-begrænsning betyder, at en datarække skal have en værdi for den kolonne, der er angivet som NOT NULL.Hvis en kolonne er angivet som NOT NULL, tillader Oracle RDBMS ikke, at der gemmes rækker i medarbejdertabellen, som overtræder denne begrænsning.Den kan kun defineres på kolonneniveau og ikke på tabelniveau.

Syntaks:

COLUMN 

UNIQUE constraint

I nogle tilfælde er det nødvendigt at håndhæve entydighed for en kolonneværdi, der ikke er en primærnøglespalte.UNIQUE constraint kan bruges til at håndhæve denne regel, og Oracle vil afvise alle rækker, der overtræder den unikke begrænsning.Unique constraint sikrer, at kolonneværdierne er forskellige, uden dubletter.

Syntaks:

Spalteniveau:

COLUMN 

Tabelniveau:

COLUMN 

Tabelniveau: CONSTRAINT UNIQUE (kolonnens navn)

Note: Oracle opretter internt et unikt indeks for at forhindre duplikering af kolonneværdierne.Indekser vil blive diskuteret senere i PL/SQL.

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

Hvis der er tale om en sammensat unik nøgle, skal den defineres på tabelniveau som nedenfor.

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

Primær nøgle

Hver tabel skal normalt indeholde en kolonne eller et sæt af kolonner, der entydigt identificerer rækker af data, der er gemt i tabellen.Denne kolonne eller dette sæt af kolonner kaldes primærnøglen.De fleste tabeller har en enkelt kolonne som primærnøgle.Primærnøglekolonner er begrænset mod NULLs og duplikerede værdier.

Punkter, der skal bemærkes –

• En tabel kan kun have én primærnøgle.

• Flere kolonner kan samles under en sammensat primærnøgle.

• Oracle opretter internt et unikt indeks for at forhindre duplikering i kolonneværdierne.Indekser vil blive diskuteret senere i PL/SQL.

Syntaks:

Spalteniveau:

COLUMN 

Tabelleniveau:

CONSTRAINT PRIMARY KEY 

Det følgende eksempel viser, hvordan man bruger PRIMARY KEY-begrænsning på kolonniveau.

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

Det følgende eksempel viser, hvordan man definerer sammensat primær nøgle ved hjælp af PRIMARY KEY-begrænsning på tabelniveau.

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

Fremme nøgle

Når to tabeller deler forælder-barn-relationen baseret på en bestemt kolonne, kaldes den sammenføjede kolonne i undertabellen for en fremmed nøgle.Denne egenskab ved den tilsvarende kolonne i forældretabellen er kendt som referentiel integritet.Foreign Key-kolonneværdierne i undertabellen kan enten være nul eller skal være de eksisterende værdier i forældretabellen.Bemærk, at kun primærnøglespalterne i den refererede tabel er berettiget til at håndhæve referentiel integritet.

Hvis en fremmed nøgle er defineret på kolonnen i underordnede tabel, så tillader Oracle ikke, at den overordnede række slettes, hvis den indeholder nogen underordnede rækker.Men hvis ON DELETE CASCADE option er givet på tidspunktet for definition af fremmed nøgle, sletter Oracle alle underordnede rækker, mens den overordnede række slettes.På samme måde angiver ON DELETE SET NULL, at når en række i den overordnede tabel slettes, sættes værdierne for den fremmede nøgle til nul.

Syntaks:

Spalteniveau:

COLUMN 

Tabelleniveau:

CONSTRAINT 

Det følgende eksempel viser, hvordan man bruger FOREIGN KEY-begrænsning på kolonneniveau.

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

Brug af ON DELETE CASCADE-klausulen

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

Check-begrænsning

I nogle tilfælde skal de dataværdier, der er gemt i en bestemt kolonne, falde inden for et acceptabelt værdiområde.En CHECK-begrænsning kræver, at den angivne kontrolbetingelse enten er sand eller ukendt for hver række, der er gemt i tabellen.Check-begrænsning gør det muligt at pålægge en betinget regel på en kolonne, som skal valideres, før der indsættes data i kolonnen. Betingelsen må ikke indeholde en underforespørgsel eller en pseudokolonne CURRVAL NEXTVAL, LEVEL, ROWNUM eller SYSDATE.

Oracle tillader, at en enkelt kolonne kan have mere end én CHECK constraint. Faktisk er der ingen praktisk grænse for antallet af CHECK-begrænsninger, der kan defineres for en kolonne.

Syntaks:

Kolonne-niveau:

COLUMN CONSTRAINT 

Tabel-niveau:

CONSTRAINT CHECK (condition)

Det følgende eksempel viser, hvordan man bruger CHECK-begrænsning på kolonne-niveau.

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

Det følgende eksempel viser, hvordan man bruger CHECK-begrænsning på tabel-niveau.

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

ALTER TABLE-anvisning

En DBA kan foretage ændringer i tabellens struktur eller kolonnedefinitioner, efter at tabellen er blevet oprettet i databasen.DDL-kommandoen ALTER TABLE bruges til at udføre sådanne handlinger.Alter-kommandoen indeholder flere hjælpeprogrammer eksklusivt for skemaobjekter.ALTER TABLE-anvisningen bruges til at tilføje, slette, omdøbe og ændre en kolonne i en tabel.

Den nedenstående ALTER TABLE-anvisning omdøber tabellen EMP til EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Den nedenstående ALTER TABLE-anvisning tilføjer en ny kolonne TESTCOL til tabellen EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Den nedenstående ALTER TABLE-anvisning omdøber kolonnen TESTCOL til TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Med nedenstående ALTER TABLE-anvisning slettes kolonnen TESTNEW fra tabellen EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Med nedenstående ALTER TABLE-anvisning tilføjes primærnøgle på kolonnen EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Med nedenstående ALTER TABLE-anvisning slettes primærnøglen.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Med nedenstående ALTER TABLE-anvisning skifter tabellen til skrivebeskyttet tilstand.

ALTER TABLE EMP_NEW READ ONLY;

Læsebeskyttede tabeller

Læsebeskyttede tabeller kom som en forbedring i Oracle 11g.Den gør det muligt at bruge tabellerne til skrivebeskyttede formål. I tidligere Oracle-versioner blev tabellerne gjort skrivebeskyttede ved at give SELECT-rettigheder til de andre brugere, men ejeren havde stadig læse- og skriverettigheder.Men nu, hvis en tabel er indstillet som skrivebeskyttet, har ejeren ikke engang adgang til datamanipulation.

Syntaks:

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-erklæring

Den DROP TABLE-erklæring bruges til at fjerne en tabel fra databasen. Den tabte tabel og dens data er ikke længere tilgængelige for udvælgelse.Den tabte tabel kan genoprettes ved hjælp af FLASHBACK-værktøjet, hvis det er tilgængeligt i genbrugsbeholderen.Ved at droppe en tabel slettes det indeks og de triggere, der er tilknyttet den.

Syntaks:

DROP TABLE 

Den nedenstående erklæring vil droppe tabellen og placere den i recyclebin.

DROP TABLE emp_new;

Den nedenstående erklæring vil også droppe tabellen og skylle den ud fra recyclebin.

DROP TABLE emp_new PURGE;