Använda DDL-satser

Använda DDL-satser för att skapa och hantera tabeller

Ett schema är en samling av flera databasobjekt som kallas schemaobjekt.Dessa objekt har direkt åtkomst av sitt ägarschema.Nedanstående tabell visar schemaobjekten.

• Tabell – för att lagra data

• Vy – för att projicera data i ett önskat format från en eller flera tabeller

• Sekvens – för att generera numeriska värden

• Index – för att skapa ett schema. för att förbättra prestanda för sökningar på tabellerna

• Synonym – alternativt namn på ett objekt

Ett av de första stegen i skapandet av en databas är att skapa de tabeller som ska lagra organisationens data.Databasdesign innebär att identifiera systemanvändarkrav för olika organisatoriska system såsom orderingång, lagerhantering och kundreskontra. Oavsett databasens storlek och komplexitet består varje databas av tabeller.

Skapa tabellen

För att skapa en tabell i databasen måste en DBA ha viss information i handen – tabellnamn, kolumnnamn, kolumndatatyper och kolumnstorlekar. All denna information kan ändras senare med hjälp av DDL-kommandon.

Namnkonventioner för tabeller –

• Det namn du väljer för en tabell måste följa dessa standardregler:

• Namnet måste börja med bokstaven A-Z eller a-z

• Kan innehålla siffror och understrykningar

• Kan vara med stora eller små bokstäver

• Kan vara upp till 30 tecken långt

• Kan inte använda samma namn som ett annat befintligt objekt i ditt schema

• Måste inte vara ett reserverat SQL-ord

Följande ovanstående riktlinjer, ”EMP85” kan vara ett giltigt tabellnamn.Men 85EMP är det inte.UPDATE kan inte heller väljas som ett bordsnamn eftersom det är ett reserverat nyckelord i SQL.

CREATE TABLE-anvisning

CREATE TABLE är en DDL-anvisning som används för att skapa tabeller i databasen.Tabellen skapas så snart CREATE TABLE-skriptet har utförts och är redo att innehålla data.Användaren måste ha systembehörigheten CREATE TABLE för att kunna skapa tabellen i sitt eget schema.Men för att skapa en tabell i någon användares schema måste användaren ha CREATE ANY TABLE-schemat.

Här är syntaxen för ett grundläggande CREATE TABLE-meddelande.Det kan finnas många ytterligare klausuler för att explicit tillhandahålla lagringsspecifikationer eller segmentvärden.

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

I syntaxen ovan specificerar DEFAULT standardvärdet som kan användas under INSERT-meddelandet om kolumnen ignoreras. Den kan inte innehålla referenser till andra tabellkolumner eller pseudokolumner (CURRVAL, NEXTVAL, LEVEL och ROWNUM) utom SYSDATE och USER, eller datumkonstanter som inte är fullständigt specificerade.

Begränsningar är de regler som definieras valfritt på kolumn- eller tabellnivå (behandlas senare i det här kapitlet).Dessa regler kontrolleras under varje dataåtgärd (infoga, uppdatera) på tabellen och ger upphov till fel för att avbryta åtgärden vid överträdelse.

Till exempel skapar CREATE TABLE-angivelsen nedan en tabell EMP_TEST. Observera kolumnspecifikationerna, datatyp och precision.

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

En användare kan hänvisa till tabeller från andra användares scheman genom att prefixera användarnamnet eller schemat med tabellens namn. till exempel, en användare GUEST vill fråga efter den anställdes namn och lön från tabellen EMP_TEST som ägs av SCOTT. Han kan ställa nedanstående fråga –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

En kolumn kan ha ett standardvärde när tabellen skapas.Det hjälper till att begränsa NULL-värden som kommer in i kolumnen. Standardvärdet kan härledas från antingen en bokstav, ett uttryck eller en SQL-funktion som måste returnera en datatyp som är kompatibel med kolumnen. I CREATE TABLE-anvisningen nedan noterar du att LOCATION_ID-kolumnen har standardvärdet 100.

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

CTAS – Skapa tabell med hjälp av subquery

En tabell kan skapas från en befintlig tabell i databasen med hjälp av ett subquery-alternativ.Den kopierar tabellens struktur och även data från tabellen. Data kan också kopieras baserat på villkor.Kolonnens datatypdefinitioner, inklusive de uttryckligen införda NOT NULL-begränsningarna, kopieras till den nya tabellen.

Det nedanstående CTAS-skriptet skapar en ny tabell EMP_BACKUP. Uppgifter om anställda på avdelning 20 kopieras till den nya tabellen.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Datatyper

Datatyper används för att specificera det grundläggande beteendet för en kolumn i tabellen.I stort sett kan kolonnens beteende antingen tillhöra en siffer-, tecken- eller datafamilj.Det finns flera andra undertyper som hör till dessa familjer.

Datatypen NUMBER

Datatypen NUMBER omfattar både heltals-, fastpunkts- och flyttalvärden.Tidiga versioner av Oracle definierade olika datatyper för var och en av dessa olika typer av tal, men nu tjänar datatypen NUMBER alla dessa syften.Välj datatypen NUMBER när en kolumn måste lagra numeriska data som kan användas i matematiska beräkningar.Ibland används datatypen NUMBER för att lagra identifieringsnummer där dessa nummer genereras av DBMS som sekventiella nummer.

NUMMER (p, s), där p är precisionen upp till 38 siffror och s är skalan (antal siffror till höger om decimaltecknet). skalan kan ligga mellan -84 och 127.

NUMMER (p),är ett fixpunktstal med skalan noll och precisionen p.

FLOAT ,där p är den binära precisionen som kan ligga mellan 1 och 126. Om p inte anges är standardvärdet binärt 126.

Datatypen DATE

För varje datatyp DATE lagras århundrade, år, månad, dag, timme, minut och sekund i databasen. Varje databassystem har ett standarddatumformat som definieras av initialiseringsparametern NLS_DATE_FORMAT. Den här parametern är vanligtvis inställd på DD-MON-YY.Om du inte anger någon tid är standardtiden 12:00:00.

Teckendatatyper

Oracle har stöd för tre fördefinierade teckendatatyper, inklusive CHAR, VARCHAR, VARCHAR2 och LONG.VARCHAR och VARCHAR2 är faktiskt synonyma och Oracle rekommenderar att man använder VARCHAR2 istället för VARCHAR.Använd datatypen CHAR när kolumnen ska lagra teckenvärden som har en fast längd, t.ex. tilldelas varje medborgare i USA ett socialförsäkringsnummer (SSN) som alltid består av 9 tecken (även om ett SSN består av siffror behandlas siffrorna som tecken), och skulle specificeras som CHAR(9). Använd datatypen VARCHAR2 för att lagra alfanumeriska data med varierande längd, t.ex. ett kundnamn eller en adress som varierar avsevärt när det gäller antalet tecken som ska lagras.Den maximala storleken på en VARCHAR2-kolumn är 4 000 tecken.

LOB-datatypen

Oracle tillhandahåller flera olika LOB-datatyper, bland annat CLOB (character large object) och BLOB (binary large object).Kolumner av dessa datatyper kan lagra ostrukturerade data, bland annat text, bild, video och rumsliga data.Datatypen CLOB kan lagra upp till åtta terabyte teckendata med hjälp av CHAR-databasens teckenuppsättning.Datatypen BLOB används för att lagra ostrukturerade binära stora objekt, t.ex. de som är förknippade med bild- och videodata, där data helt enkelt är en ström av ”bitvärden”.En BLOB-datatyp kan lagra upp till åtta terabyte binära data.NCLOB-datatypen kan lagra stora teckenobjekt i multibyte nationell teckenuppsättning på upp till 8TB till 128TB.BFILE-datatypen fungerar som en fillokaliserare eller pekare till en fil i serverns filsystem. Den maximala filstorleken som stöds är 8TB till 128TB.

Constraints

Constraints är en uppsättning regler som definieras i Oracle-tabeller för att säkerställa dataintegritet.Dessa regler verkställs för varje kolumn eller uppsättning kolumner.Närhelst tabellen deltar i dataåtgärden valideras dessa regler och ger upphov till ett undantag vid överträdelse. De tillgängliga begränsningstyperna är NOT NULL, Primary Key, Unique, Check och Foreign Key.

Den nedanstående syntaxen kan användas för att införa begränsningar på kolumnnivå.

Syntax:

column constraint_type

Alla begränsningar utom NOT NULL kan också definieras på tabellnivå. Sammansatta begränsningar kan endast anges på tabellnivå.

NOT NULL-begränsning

En NOT NULL-begränsning innebär att en datarad måste ha ett värde för den kolumn som anges som NOT NULL.Om en kolumn anges som NOT NULL tillåter Oracle RDBMS inte att rader lagras i tabellen Employee som bryter mot begränsningen.Den kan endast definieras på kolumnnivå, inte på tabellnivå.

Syntax:

COLUMN 

UNIQUE constraint

I vissa fall är det nödvändigt att upprätthålla unikhet för ett kolumnvärde som inte är en primärnyckelkolumn.UNIQUE constraint kan användas för att upprätthålla den här regeln och Oracle kommer att avvisa alla rader som bryter mot unique constraint.Unique constraint säkerställer att kolumnvärdena är distinkta, utan några dubbletter.

Syntax:

Kolumnnivå:

COLUMN 

Tabellnivå: Oracle skapar internt ett unikt index för att förhindra dubblering av kolumnvärdena.Index diskuteras senare i PL/SQL.

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

Om det finns en sammansatt unik nyckel måste den definieras på tabellnivå enligt nedan.

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

Primärnyckel

Varje tabell måste normalt innehålla en kolumn eller en uppsättning kolumner som unikt identifierar de datarader som lagras i tabellen.Denna kolumn eller uppsättning kolumner kallas primärnyckel.De flesta tabeller har en enda kolumn som primärnyckel.Primärnyckelkolumner är begränsade mot NULLs och dubbla värden.

Punkter att notera –

• En tabell kan bara ha en primärnyckel.

• Flera kolumner kan samlas under en sammansatt primärnyckel.

• Oracle skapar internt ett unikt index för att förhindra dubblering av kolumnvärden.Index diskuteras senare i PL/SQL.

Syntax:

Kolumnnivå:

COLUMN 

Tabellnivå:

CONSTRAINT PRIMARY KEY 

Följande exempel visar hur man använder PRIMARY KEY-begränsningen på kolumnnivå.

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

Följande exempel visar hur man definierar sammansatt primärnyckel med hjälp av PRIMARY KEY-begränsning på tabellnivå.

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

Förändringsnyckel

När två tabeller delar föräldra- och barnförhållande baserat på en specifik kolumn kallas den sammanfogande kolumnen i barntabellen för en förändringsnyckel.Den här egenskapen hos motsvarande kolumn i föräldratabellen kallas referentiell integritet.Foreign Key-kolumnvärden i barntabellen kan antingen vara noll eller måste vara befintliga värden i föräldratabellen.Observera att endast primärnyckelkolumner i den refererade tabellen är berättigade att upprätthålla referentiell integritet.

Oracle tillåter inte att den överordnade raden raderas om den innehåller några underordnade rader.Om alternativet ON DELETE CASCADE anges när den överordnade nyckeln definieras raderar Oracle dock alla underordnade rader medan den överordnade raden raderas.På samma sätt anger ON DELETE SET NULL att när en rad i den överordnade tabellen tas bort sätts värdena för den främmande nyckeln till noll.

Syntax:

Skolumnnivå:

COLUMN 

Tabellnivå:

CONSTRAINT 

Följande exempel visar hur man använder FOREIGN KEY-begränsningen på kolumnnivå.

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

Användning av ON DELETE CASCADE-klausulen

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

Check-begränsning

I vissa fall måste de datavärden som lagras i en specifik kolumn ligga inom ett godtagbart värdeintervall.En CHECK-begränsning kräver att det angivna kontrollvillkoret antingen är sant eller okänt för varje rad som lagras i tabellen.Check-begränsning gör det möjligt att införa en villkorlig regel för en kolumn, som måste valideras innan data läggs in i kolumnen. Villkoret får inte innehålla en underfråga eller en pseudokolumn CURRVAL NEXTVAL, LEVEL, ROWNUM eller SYSDATE.

Oracle tillåter att en enskild kolumn har mer än en CHECK-begränsning. Det finns faktiskt ingen praktisk gräns för hur många CHECK-begränsningar som kan definieras för en kolumn.

Syntax:

Kolumnnivå:

COLUMN CONSTRAINT 

Tabellnivå:

CONSTRAINT CHECK (condition)

Följande exempel visar hur man använder CHECK-begränsning på kolumnnivå.

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

Följande exempel visar hur man använder CHECK-begränsning på tabellnivå.

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

ALTER TABLE-anvisning

En DBA kan göra ändringar i tabellstrukturen eller kolumndefinitionerna efter att tabellen har skapats i databasen.DDL-kommandot ALTER TABLE används för att utföra sådana åtgärder.Alter-kommandot tillhandahåller flera verktyg exklusivt för schemaobjekt.ALTER TABLE-kommandot används för att lägga till, ta bort, byta namn på och ändra en kolumn i en tabell.

Det nedanstående ALTER TABLE-kommandot byter namn på tabellen EMP till EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Med nedanstående ALTER TABLE-anvisning läggs en ny kolumn TESTCOL till tabellen EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Med nedanstående ALTER TABLE-anvisning döps kolumnen TESTCOL om till TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Med nedanstående ALTER TABLE-anvisning stryks kolumnen TESTNEW från tabellen EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Med nedanstående ALTER TABLE-anvisning läggs primärnyckel till för kolumnen EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Underliggande ALTER TABLE-anvisning släpper primärnyckeln.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Underliggande ALTER TABLE-anvisning ändrar tabellens läge till skrivskyddad.

ALTER TABLE EMP_NEW READ ONLY;

Lässkyddade tabeller

Lässkyddade tabeller är en förbättring i Oracle 11g.Det gör det möjligt att använda tabellerna för skrivskyddade ändamål. I tidigare Oracle-versioner gjordes tabellerna skrivskyddade genom att andra användare fick SELECT-privilegiet, men ägaren hade fortfarande läs- och skrivprivilegiet.Men nu har inte ens ägaren tillgång till datamanipulering om en tabell är inställd som skrivskyddad.

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

Den DROP TABLE-anvisningen används för att ta bort en tabell från databasen. Den borttagna tabellen och dess data är inte längre tillgängliga för urval.Den borttagna tabellen kan återställas med hjälp av FLASHBACK-verktyget, om det finns tillgängligt i återvinningskorgen.När man släpper en tabell släpper man index och triggers som är kopplade till den.

Syntax:

DROP TABLE 

Understående anvisning släpper tabellen och placerar den i recyclebin.

DROP TABLE emp_new;

Understående anvisning släpper tabellen och spolar ut den från recyclebin också.

DROP TABLE emp_new PURGE;