DDL-lausekkeiden käyttäminen

DDL-lausekkeiden käyttäminen taulukoiden luomisessa ja hallitsemisessa

Skeema on useiden tietokantaobjektien kokoelma,joita kutsutaan skeemaobjekteiksi.Näihin objekteihin pääsee käsiksi suoraan omistajansa skeema.Alla olevaan taulukkoon on listattu skeemaobjekteja.

• Taulukko – tietojen tallentamiseen

• Näkymä – tietojen projisoimiseen halutussa muodossa yhdestä tai useammasta taulusta

• Sekvenssi – numeeristen arvojen tuottamiseen

• Index -… parantaa taulukoihin kohdistuvien kyselyjen suorituskykyä

• Synonyymi – objektin vaihtoehtoinen nimi

Yksi ensimmäisistä vaiheista tietokannan luomisessa on luoda taulukot, joihin organisaation tiedot tallennetaan.Tietokannan suunnitteluun kuuluu järjestelmän käyttäjävaatimusten määrittäminen erilaisille organisaation järjestelmille, kuten tilausten kirjaamiselle, varastonhallinnalle ja kirjanpidolle. Tietokannan koosta ja monimutkaisuudesta riippumatta jokainen tietokanta koostuu taulukoista.

Taulukon luominen

Luodakseen tietokantaan taulukon DBA:lla on oltava käytössään tietyt tiedot – taulun nimi, sarakkeen nimi, sarakkeen tietotyypit ja sarakkeen koko. Kaikkia näitä tietoja voidaan myöhemmin muuttaa DDL-komennoilla.

Taulujen nimeämiskäytännöt –

• Taululle valitsemasi nimen on noudatettava näitä vakiosääntöjä:

• Nimen on alettava kirjaimella A-Z tai a-z

• voi sisältää numeroita ja alleviivauksia

• voi olla ylä- tai alamerkkejä

• voi olla enintään 30 merkkiä pitkä

.

• Ei voi käyttää samaa nimeä kuin jokin toinen olemassa oleva objekti skeemassasi

• Ei saa olla SQL:n varattu sana

Yllä olevien ohjeiden mukaan, ’EMP85’ voi olla kelvollinen taulukon nimi.Mutta 85EMP ei ole.Samoin UPDATE ei voi olla taulukon nimi, koska se on SQL:n varattu avainsana.

CREATE TABLE -lause

CREATE TABLE on DDL-lause, jota käytetään taulujen luomiseen tietokantaan.Taulu luodaan heti, kun CREATE TABLE -skripti suoritetaan, ja se on valmis pitämään tietoja jatkossa.Käyttäjällä täytyy olla CREATE TABLE -järjestelmäoikeus, jotta hän voi luoda taulun omaan skeemaansa.Mutta luodakseen taulukon minkä tahansa käyttäjän skeemaan, käyttäjällä on oltava CREATE ANY TABLE -skeema.

Tässä on CREATE TABLE -peruslauseen syntaksi.Siinä voi olla monia lisälausekkeita, joilla voidaan antaa nimenomaisesti tallennusmäärittelyt tai segmenttiarvot.

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

Yllä olevassa syntaksissa DEFAULT määrittää oletusarvon, jota voidaan käyttää INSERT-lauseen aikana, jos saraketta ei oteta huomioon. Se ei voi sisältää viittauksia muihin taulukon sarakkeisiin tai pseudosarakkeisiin (CURRVAL, NEXTVAL, LEVEL ja ROWNUM) paitsi SYSDATE ja USER tai päivämäärävakioihin, joita ei ole täysin määritelty.

Constraints ovat sääntöjä, jotka on määritelty valinnaisesti sarake- tai taulukkotasolla (käsitellään myöhemmin tässä luvussa).Nämä säännöt tarkistetaan minkä tahansa taulukkoon kohdistuvan tietotoimenpiteen (Insert, update) aikana, ja ne aiheuttavat virheen, joka keskeyttää toimenpiteen, jos sitä rikotaan.

Esimerkiksi alla olevalla CREATE TABLE-lauseella luodaan taulukko EMP_TEST. Huomaa sarakkeiden määrittelyt, tietotyyppi ja tarkkuus.

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

Käyttäjä voi viitata taulukoihin toisen käyttäjän skeemasta liittämällä käyttäjänimen tai skeeman etuliitteeksi taulukon nimen. esimerkiksi käyttäjä GUEST haluaa kysyä työntekijän nimeä ja palkkaa EMP_TEST-taulusta, jonka omistaa SCOTT. Hän voi tehdä alla olevan kyselyn –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

Sarakkeessa voi olla oletusarvo taulukon luomisen aikana.Se auttaa rajoittamaan NULL-arvojen pääsyä sarakkeeseen. Oletusarvo voidaan johtaa joko literaalista, lausekkeesta tai SQL-funktiosta, jonka on palautettava sarakkeen kanssa yhteensopiva tietotyyppi. Alla olevassa CREATE TABLE -lausekkeessa huomaa, että LOCATION_ID-sarakkeella on oletusarvo 100.

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

CTAS – Taulukon luominen alakyselyllä

Taulukko voidaan luoda tietokannan olemassa olevasta taulusta alakyselyvaihtoehdon avulla.Se kopioi taulun rakenteen sekä taulun tiedot. Tietoja voidaan kopioida myös ehtojen perusteella.Sarakkeiden tietotyyppimääritykset, mukaan lukien nimenomaisesti asetetut NOT NULL -rajoitukset, kopioidaan uuteen taulukkoon.

Alla oleva CTAS-skripti luo uuden taulukon EMP_BACKUP. Osaston 20 työntekijätiedot kopioidaan uuteen taulukkoon.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Tietotyypit

Tietotyyppejä käytetään taulukon sarakkeen peruskäyttäytymisen määrittelyyn.Yleisesti ottaen sarakkeen käyttäytyminen voi kuulua joko numero-, merkki- tai päivämäärätyyppiperheeseen.Näihin perheisiin kuuluu useita muita alatyyppejä.

Luku-tietotyyppi

NUMBER-tietotyyppi kattaa sekä kokonaisluku-, kiintopiste- että liukulukuarvot.Oraclen varhaisemmissa versioissa kullekin näistä erilaisista numerotyypeistä määriteltiin eri tietotyypit, mutta nykyään NUMBER-tietotyyppi palvelee kaikkia näitä tarkoituksia.Valitse NUMBER-tietotyyppi, kun sarakkeeseen on tallennettava numeerista tietoa, jota voidaan käyttää matemaattisissa laskutoimituksissa.Toisinaan NUMBER-tietotyyppiä käytetään tunnistenumeroiden tallentamiseen, kun tietokantajärjestelmä luo nämä numerot juoksevina numeroina.

NUMBER (p, s),jossa p on tarkkuus 38 numeroon asti ja s on asteikko (numeroiden määrä desimaalipisteen oikealla puolella).Asteikko voi olla välillä -84-127.

NUMBER (p),on kiintopistemäinen luku, jonka asteikko on nolla ja tarkkuus p.

FLOAT ,jossa p on binääritarkkuus, joka voi olla välillä 1-126. Jos p:tä ei määritetä, oletusarvo on binäärinen 126.

Date-tietotyyppi

Kunkin DATE-tietotyypin osalta tietokantaan tallennetaan vuosisata, vuosi, kuukausi, päivä, tunti, minuutti ja sekunti. Jokaisella tietokantajärjestelmällä on oletuspäivämäärämuoto, joka määritellään alustamisparametrilla NLS_DATE_FORMAT. Tämän parametrin arvoksi asetetaan yleensä DD-MON-YY.Jos aikaa ei määritetä, oletusaika on 12:00:00.

Merkkitietotyyppi

Oracle tukee kolmea ennalta määriteltyä merkkitietotyyppiä, mukaan lukien CHAR, VARCHAR, VARCHAR2 ja LONG.VARCHAR ja VARCHAR2 ovat itse asiassa synonyymejä, ja Oracle suositteleekin VARCHAR2:n käyttämistä VARCHAR:n sijaan.Käytä CHAR-tietotyyppiä, kun sarakkeeseen tallennetaan merkkiarvoja, jotka ovat kiinteän pituisia.Esimerkiksi sosiaaliturvatunnus (SSN) annetaan Yhdysvalloissa jokaiselle kansalaiselle, ja sen koko on aina 9 merkkiä (vaikka SSN-numero koostuu yksinomaan numeroista, numeroita käsitellään merkkeinä), ja se määritetään muodossa CHAR(9). Käytä VARCHAR2-tietotyyppiä aakkosnumeeristen tietojen tallentamiseen, joiden pituus vaihtelee.Esimerkiksi asiakkaan nimi tai osoite vaihtelee huomattavasti tallennettavien merkkien määrän suhteen.VARCHAR2-sarakkeen enimmäiskoko on 4 000 merkkiä.

LOB-tietotyyppi

Oracle tarjoaa useita erilaisia LOB-tietotyyppejä, kuten CLOB (character large object) ja BLOB (binary large object).Näiden tietotyyppien sarakkeisiin voidaan tallentaa jäsentymätöntä dataa, kuten tekstiä, kuvaa, videota ja paikkatietoa.CLOB-tietotyyppiin voidaan tallentaa jopa kahdeksan teratavua merkkidataa käyttäen CHAR-tietokannan merkistöjä.BLOB-tietotyyppiä käytetään strukturoimattomien binääristen suurten objektien tallentamiseen, kuten kuva- ja videodatan kaltaisten objektien tallentamiseen, joissa data on pelkkää ”bittiarvojen” virtaa.BLOB-tietotyyppi voi tallentaa jopa kahdeksan teratavua binääridataa.NCLOB-tietotyyppi voi tallentaa merkkimuotoisia suuria objekteja multibyte-kansallisella merkkijoukolla jopa 8TB-128TB.BFILE-tietotyypin arvo toimii tiedostojen paikantimena tai osoittimena tiedostoon palvelimen tiedostojärjestelmässä. Suurin tuettu tiedostokoko on 8TB-128TB.

Constraints

Constraints ovat Oracle-taulukoissa määriteltyjä sääntöjä, joilla varmistetaan tietojen eheys.Nämä säännöt pannaan täytäntöön jokaiselle sarakkeelle tai sarakkeiden joukolle.Aina, kun taulukko osallistuu datatoimintaan, nämä säännöt validoidaan ja niiden rikkoutuessa ne aiheuttavat poikkeuksen. Käytettävissä olevat rajoitustyypit ovat NOT NULL, Primary Key (ensisijainen avain), Unique (ainutkertainen), Check (tarkistus) ja Foreign Key (vieras avain).

Alla olevaa syntaksia voidaan käyttää rajoituksen asettamiseen saraketasolla.

Syntax:

column constraint_type

Kaikki rajoitukset NOT NULLia lukuun ottamatta voidaan määritellä myös taulukkotasolla. Yhdistelmärajoitukset voidaan määritellä vain taulukkotasolla.

NOT NULL -rajoitus

NOT NULL -rajoitus tarkoittaa, että tietorivillä on oltava NOT NULL -arvona määritellyn sarakkeen arvo.Jos sarakkeelle on määritetty NOT NULL -arvo, Oraclen RDBMS-järjestelmä ei salli työntekijätaulukkoon tallentuvan sellaisia rivejä, jotka ovat tämän rajoituksen vastaisia.Rajoitus voidaan määritellä vain sarakkeella, ei taulukkotasolla.

Syntax:

COLUMN 

UNIQUE-rajoitus

Joskus on tarpeen pakottaa yksikäsitteisyys sarakearvolle, joka ei ole ensisijaisen avaimen sarake.UNIQUE-rajoitusta voidaan käyttää tämän säännön pakottamiseen, ja Oracle hylkää kaikki sellaiset rivit, jotka rikkovat yksikäsitteisyysrajoitusta.Yksikäsitteisyysrajoitus varmistaa, että sarakkeen arvot ovat erillisiä, eikä niissä ole kaksoiskappaleita.

Syntaksi:

Sarakkeen taso: CONSTRAINT UNIQUE (sarakkeen nimi)

Huomautus: Oracle luo sisäisesti uniikin indeksin estääkseen sarakkeen arvojen päällekkäisyyksien syntymisen.Indeksejä käsiteltäisiin myöhemmin PL/SQL:ssä.

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

Jos kyseessä on yhdistetty uniikki avain, se on määriteltävä taulukkotasolla seuraavasti.

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

Primääriavain

Jokaiseen taulukkoon on yleensä sisällytettävä sarake tai sarakkeiden joukko, joka yksilöi yksikäsitteisesti taulukkoon tallennetut tietorivit.Tätä saraketta tai sarakkeiden joukkoa kutsutaan ensisijaiseksi avaimeksi.Useimmissa taulukoissa on yksi sarake ensisijaisena avaimena.Ensisijaisen avaimen sarakkeisiin ei saa antaa NULL-arvoja eikä arvoja saa olla päällekkäisiä.

Huomioitavaa –

• Taulussa voi olla vain yksi ensisijainen avain.

• Monia sarakkeita voidaan yhdistää yhdistetyn ensisijaisen avaimen alle.

• Oracle luo sisäisesti yksilöivän indeksin estääkseen sarakkeen arvojen päällekkäisyyden.Indeksejä käsiteltäisiin myöhemmin PL/SQL:ssä.

Syntaksi:

Sarakkeen taso:

COLUMN 

Taulukon taso:

CONSTRAINT PRIMARY KEY 

Seuraavassa esimerkissä näytetään, miten PRIMARY KEY-rajoitusta käytetään saraketasolla.

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

Seuraavassa esimerkissä näytetään, miten määritetään yhdistetty ensisijainen avain käyttämällä PRIMARY KEY-rajoitusta taulukkotasolla.

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

Vierasavain

Kun kahdella taululla on tiettyyn sarakkeeseen perustuva yhteinen vanhempi-tytär-suhde, kutsutaan yhdistävää saraketta lapsitaulussa vierasavaimeksi.Tämä vanhemman taulun vastaavan sarakkeen ominaisuus tunnetaan nimellä Referential integrity.Foreign Key -sarakkeen arvot lapsitaulussa voivat olla joko nolla tai niiden on oltava vanhemman taulun olemassa olevia arvoja.Huomaa, että vain viitattavan taulun ensisijaisen avaimen sarakkeet ovat oikeutettuja toteuttamaan referential integrity -ominaisuutta.

Jos vierasavain on määritelty lapsitaulukon sarakkeelle, Oracle ei salli vanhemman rivin poistamista, jos se sisältää lapsirivejä.Jos kuitenkin ON DELETE CASCADE -vaihtoehto annetaan vieraan avaimen määrittelyn yhteydessä, Oracle poistaa kaikki lapsirivit, kun vanhemman rivi poistetaan.Vastaavasti ON DELETE SET NULL tarkoittaa, että kun vanhemman taulun rivi poistetaan, vieraan avaimen arvot asetetaan nollaksi.

Syntaksi:

Sarakkeen taso:

COLUMN 

Taulukon taso:

CONSTRAINT 

Seuraavassa esimerkissä näytetään, miten FOREIGN KEY-rajoitusta käytetään saraketasolla.

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

ON DELETE CASCADE -lausekkeen käyttö

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

Tarkistusrajoitus

Joskus tiettyyn sarakkeeseen tallennettujen tietojen arvojen on kuuluttava johonkin hyväksyttävään arvoalueeseen.TARKISTUSRAJOITUS edellyttää, että määritetty tarkistusehto on joko tosi tai tuntematon jokaiselle taulukkoon tallennetulle riville.Tarkistusrajoitus sallii sarakkeelle ehdollisen säännön, joka on tarkistettava, ennen kuin sarakkeelle lisätään tietoja. Ehto ei saa sisältää alakyselyä tai pseudosaraketta CURRVAL NEXTVAL, LEVEL, ROWNUM tai SYSDATE.

Oracle sallii, että yhdellä sarakkeella voi olla useampi kuin yksi CHECK-rajoitus. Itse asiassa sarakkeelle määritettävien CHECK-rajoitusten lukumäärälle ei ole käytännön rajoitusta.

Syntaksi:

Sarakkeen taso:

COLUMN CONSTRAINT 

Taulukon taso:

CONSTRAINT CHECK (condition)

Seuraavassa esimerkissä näytetään, miten CHECK-rajoitusta käytetään saraketasolla.

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

Seuraavassa esimerkissä näytetään, miten CHECK-rajoitusta käytetään taulukkotasolla.

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

ALTER TABLE -lausunto

DBA voi tehdä muutoksia taulukon rakenteeseen tai sarakemäärityksiin sen jälkeen, kun taulukko on luotu tietokantaan.Tällaisten toimien suorittamiseen käytetään DDL-komentoa ALTER TABLE.Alter-komento tarjoaa useita apuohjelmia, jotka koskevat yksinomaan skeemaobjekteja.ALTER TABLE -lauseketta käytetään taulun sarakkeen lisäämiseen, poistamiseen, uudelleennimeämiseen ja muokkaamiseen.

Alempana oleva ALTER TABLE -lauseke nimeää taulun EMP uudelleen muotoon EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Alta löytyvä ALTER TABLE -lauseke lisää uuden sarakkeen TESTCOL tauluun EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Alta löytyvä ALTER TABLE -lauseke nimeää sarakkeen TESTCOL uudelleen muotoon TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Alta löytyvä ALTER TABLE -lausunto pudottaa sarakkeen TESTNEW EMP_NEW-taulusta

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Alta löytyvä ALTER TABLE -lausunto lisää EMPLOYEE_ID-sarakkeen ensisijaisen avaimen.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Alhaalla oleva ALTER TABLE -lausunto pudottaa ensisijaisen avaimen.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Alhaalla oleva ALTER TABLE -lausunto vaihtaa taulun tilaksi vain luku.

ALTER TABLE EMP_NEW READ ONLY;

Read Only Tables

Read Only -taulukot tulivat parannuksena Oracle 11g:ssä.Sen avulla taulukoita voidaan käyttää vain lukutarkoituksiin. Aikaisemmissa Oracle-versioissa taulukoista tehtiin vain lukuoikeuksia myöntämällä SELECT-oikeudet muille käyttäjille, mutta omistajalla oli edelleen luku- ja kirjoitusoikeudet.Mutta nyt, jos taulukko on asetettu vain lukuoikeudeksi, edes omistajalla ei ole pääsyä tietojen käsittelyyn.

Syntaksi:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Kuvitus

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

DROP TABLE -lausumalla poistetaan taulukko tietokannasta. Pudotettu taulukko ja sen tiedot eivät ole enää valittavissa.Pudotettu taulukko voidaan palauttaa FLASHBACK-apuohjelmalla,jos se on käytettävissä recyclebinissä.Taulukon pudottaminen pudottaa siihen liittyvät indeksit ja triggerit.

Syntaksi:

DROP TABLE 

Alhaalla oleva lauseke pudottaa taulukon ja sijoittaa sen recyclebiniin.

DROP TABLE emp_new;

Alhaalla oleva lauseke pudottaa taulukon ja huuhtelee sen pois myös recyclebinistä.

DROP TABLE emp_new PURGE;