Utilizarea declarațiilor DDL

Utilizarea declarațiilor DDL pentru crearea și gestionarea tabelelor

O schemă este o colecție de mai multe obiecte ale bazei de date, care sunt cunoscute sub numele de obiecte de schemă.Aceste obiecte au acces direct de către schema proprietarului lor.Tabelul de mai jos enumeră obiectele de schemă.

• Table – pentru a stoca date

• View – pentru a proiecta date în formatul dorit din unul sau mai multe tabele

• Sequence – pentru a genera valori numerice

• Index – pentru a genera valori numerice. pentru a îmbunătăți performanța interogărilor asupra tabelelor

• Sinonim – nume alternativ al unui obiect

Unul dintre primii pași în crearea unei baze de date este crearea tabelelor care vor stoca datele unei organizații.Proiectarea bazei de date implică identificarea cerințelor utilizatorilor sistemului pentru diferite sisteme organizaționale, cum ar fi introducerea comenzilor, gestionarea stocurilor și conturile de încasat. Indiferent de dimensiunea și complexitatea bazei de date, fiecare bază de date este alcătuită din tabele.

Crearea tabelului

Pentru a crea un tabel în baza de date,un DBA trebuie să aibă la îndemână anumite informații – numele tabelului, numele coloanei, tipurile de date ale coloanelor și dimensiunile coloanelor. Toate aceste informații pot fi modificate ulterior cu ajutorul comenzilor DDL.

Convenții de denumire a tabelelor –

• Numele pe care îl alegeți pentru o tabelă trebuie să respecte aceste reguli standard:

• Numele trebuie să înceapă cu o literă A-Z sau a-.z

• Poate conține numere și liniuțe de subliniere

• Poate fi în majuscule sau minuscule

• Poate avea o lungime de până la 30 de caractere

.

• Nu poate folosi același nume al unui alt obiect existent în schema dumneavoastră

• Nu trebuie să fie un cuvânt rezervat SQL

Să urmeze liniile directoare de mai sus, „EMP85” poate fi un nume de tabel valid.Dar 85EMP nu este.În mod similar, UPDATE nu poate fi ales ca nume de tabel, deoarece este un cuvânt cheie rezervat SQL.

Declarația CREATE TABLE

Declarația CREATE TABLE este o instrucțiune DDL care este utilizată pentru a crea tabele în baza de date.Tabelul este creat imediat ce scriptul CREATE TABLE este executat și este gata să conțină date mai departe.Utilizatorul trebuie să aibă privilegiul de sistem CREATE TABLE pentru a crea tabelul în propria sa schemă.Dar pentru a crea un tabel în schema oricărui utilizator, utilizatorul trebuie să aibă schema CREATE ANY TABLE.

Iată sintaxa unei instrucțiuni CREATE TABLE de bază.Pot exista multe clauze suplimentare pentru a furniza în mod explicit specificațiile de stocare sau valorile segmentului.

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

În sintaxa de mai sus, DEFAULT specifică valoarea implicită care poate fi utilizată în timpul instrucțiunii INSERT dacă coloana este ignorată. Nu poate conține referințe la alte coloane de tabel sau pseudo coloane (CURRVAL, NEXTVAL, LEVEL și ROWNUM), cu excepția SYSDATE și USER, sau constante de date care nu sunt complet specificate.

Constrângerile sunt regulile definite opțional la nivel de coloană sau la nivel de tabel (abordate mai târziu în acest capitol). aceste reguli sunt verificate în timpul oricărei acțiuni de date (Insert, update) asupra tabelului și generează erori pentru a anula acțiunea în cazul încălcării lor.

De exemplu, instrucțiunea CREATE TABLE de mai jos creează un tabel EMP_TEST. Observați specificațiile coloanelor, tipul de date și precizia.

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

Un utilizator poate face referire la tabelele din schema altui utilizator prin prefixarea numelui de utilizator sau a schemei cu numele tabelului.De exemplu, un utilizator GUEST dorește să interogheze numele și salariul angajatului din tabelul EMP_TEST, care este deținut de SCOTT. El poate lansa interogarea de mai jos –

SELECT ENAME, SALARY,FROM GUEST.EMP_TEST;

O coloană poate avea o valoare implicită în momentul creării tabelului.Aceasta ajută la restricționarea valorilor NULL care intră în coloană. Valoarea implicită poate fi dedusă fie dintr-un literal, o expresie sau o funcție SQL care trebuie să returneze un tip de date compatibil cu coloana. În instrucțiunea CREATE TABLE de mai jos, observați că coloana LOCATION_ID are valoarea implicită 100.

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

CTAS – Crearea unui tabel utilizând o subinterogare

Un tabel poate fi creat dintr-o tabelă existentă în baza de date utilizând o opțiune de subinterogare.Aceasta copiază structura tabelului, precum și datele din tabel. Datele pot fi, de asemenea, copiate pe baza unor condiții.Definițiile tipurilor de date ale coloanelor, inclusiv constrângerile NOT NULL impuse în mod explicit, sunt copiate în noul tabel.

Scriptul CTAS de mai jos creează un nou tabel EMP_BACKUP. Datele angajaților din departamentul 20 sunt copiate în noul tabel.

CREATE TABLE EMP_BACKUPASSELECT * FROM EMP_TESTWHERE department_id=20;

Tipuri de date

Tipurile de date sunt utilizate pentru a specifica comportamentul de bază al unei coloane din tabel.Pe o bază mai largă, comportamentul coloanelor poate aparține fie familiei numerelor, fie familiei caracterelor, fie familiei datelor.Există multe alte subtipuri care aparțin acestor familii.

Tipul de date NUMBER

Tipul de date NUMBER cuprinde atât valori numerice întregi, cu virgulă fixă, cât și valori numerice cu virgulă mobilă.Primele versiuni ale Oracle au definit diferite tipuri de date pentru fiecare dintre aceste tipuri diferite de numere, dar acum tipul de date NUMBER servește tuturor acestor scopuri.Alegeți tipul de date NUMBER atunci când o coloană trebuie să stocheze date numerice care pot fi utilizate în calcule matematice.Ocazional,tipul de date NUMBER este utilizat pentru a stoca numere de identificare în cazul în care aceste numere sunt generate de SGBD ca numere secvențiale.

NUMBER (p, s), unde p este precizia de până la 38 de cifre și s este scara (numărul de cifre din dreapta punctului zecimal).Scara poate fi cuprinsă între -84 și 127.

NUMBER (p),este un număr cu virgulă fixă cu o scală de zero și o precizie de p.

FLOAT ,unde p este precizia binară care poate fi cuprinsă între 1 și 126. Dacă p nu este specificat, valoarea implicită este 126 binar.

Date data type

Pentru fiecare tip de date DATE, Century, Year, Month, Day, Hour, Minute, Second sunt stocate în baza de date. Fiecare sistem de baze de date are un format de dată implicit care este definit de parametrul de inițializare NLS_DATE_FORMAT. Acest parametru este de obicei setat la DD-MON-YY.Dacă nu specificați o oră, ora implicită este 12:00:00 a.m.

Tip de date de caractere

Oracle acceptă trei tipuri de date de caractere predefinite, inclusiv CHAR, VARCHAR, VARCHAR2 și LONG.VARCHAR și VARCHAR2 sunt de fapt sinonime, iar Oracle recomandă utilizarea VARCHAR2 în loc de VARCHAR.Utilizați tipul de date CHAR atunci când coloana va stoca valori de caractere care au o lungime fixă.De exemplu, un număr de securitate socială (SSN) în Statele Unite este atribuit fiecărui cetățean și are întotdeauna o dimensiune de 9 caractere (chiar dacă un SSN este compus strict din cifre,cifrele sunt tratate ca și caractere), și ar fi specificat ca CHAR(9). Utilizați tipul de date VARCHAR2 pentru a stoca date alfanumerice de lungime variabilă.De exemplu, numele sau adresa unui client variază considerabil în ceea ce privește numărul de caractere care trebuie stocate.Dimensiunea maximă a unei coloane VARCHAR2 este de 4.000 de caractere.

Tipul de date LOB

Oracle oferă mai multe tipuri de date LOB diferite, inclusiv CLOB (character large object) și BLOB (binary large object).Coloanele din aceste tipuri de date pot stoca date nestructurate, inclusiv date text, imagine, video și spațiale.Tipul de date CLOB poate stoca până la opt terabytes de date de caractere utilizând setul de caractere al bazei de date CHAR.Tipul de date BLOB este utilizat pentru a stoca obiecte mari binare nestructurate, cum ar fi cele asociate cu datele de imagine și video, unde datele sunt pur și simplu un flux de valori „bit”.Un tip de date BLOB poate stoca până la opt terabytes de date binare.Tipul de date NCLOB poate stoca obiecte mari de caractere în set de caractere naționale multibyte de până la 8TB până la 128TB.Valoarea tipului de date BFILE funcționează ca un localizator de fișiere sau un pointer către un fișier din sistemul de fișiere al serverului. Dimensiunea maximă a fișierului acceptată este cuprinsă între 8TB și 128TB.

Constraints

Constraints reprezintă setul de reguli definite în tabelele Oracle pentru a asigura integritatea datelor.Aceste reguli sunt aplicate pentru fiecare coloană sau set de coloane.Ori de câte ori tabelul participă la o acțiune asupra datelor, aceste reguli sunt validate și ridică o excepție în caz de încălcare. Tipurile de constrângeri disponibile sunt NOT NULL, Primary Key, Unique, Check și Foreign Key.

Sintaxa de mai jos poate fi utilizată pentru a impune constrângeri la nivel de coloană.

Sintaxa:

column constraint_type

Toate constrângerile, cu excepția NOT NULL, pot fi definite și la nivel de tabel. Constrângerile compuse pot fi specificate numai la nivel de tabel.

Constrângerea NOT NULL

O constrângere NOT NULL înseamnă că un rând de date trebuie să aibă o valoare pentru coloana specificată ca NOT NULL.Dacă o coloană este specificată ca NOT NULL,RDBMS Oracle nu va permite stocarea în tabelul angajaților a rândurilor care încalcă această constrângere.Aceasta poate fi definită numai la nivel de coloană, și nu la nivel de tabel.

Sintaxa:

COLUMN 

Constrângere UNICĂ

Câteodată este necesar să se impună unicitatea pentru o valoare de coloană care nu este o coloană cheie primară.Constrângerea UNICĂ poate fi utilizată pentru a impune această regulă, iar Oracle va respinge orice rânduri care încalcă constrângerea unică.Constrângerea unică asigură faptul că valorile coloanelor sunt distincte, fără duplicate.

Sintaxa:

Nivel coloană:

COLUMN 

Table Level:

COLUMN 

Table Level: CONSTRAINT UNIQUE (numele coloanei)

Nota: Oracle creează intern un index unic pentru a preveni duplicarea valorilor coloanelor.Indexurile vor fi discutate mai târziu în PL/SQL.

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

În cazul unei chei unice compuse, aceasta trebuie definită la nivel de tabel, după cum urmează.

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

Cheie primară

În mod normal, fiecare tabel trebuie să conțină o coloană sau un set de coloane care identifică în mod unic rândurile de date care sunt stocate în tabel.Această coloană sau set de coloane este denumită cheie primară.Majoritatea tabelelor au o singură coloană ca cheie primară.Coloanele cu cheie primară sunt restricționate împotriva NULL-urilor și a valorilor duplicate.

Puncte de reținut –

• Un tabel poate avea o singură cheie primară.

• Multe coloane pot fi grupate sub o cheie primară compozită.

• Oracle creează în mod intern un index unic pentru a preveni duplicarea valorilor coloanelor.Indicii vor fi discutați mai târziu în PL/SQL.

Sintaxa:

Nivel coloană:

COLUMN 

Nivel tabel:

CONSTRAINT PRIMARY KEY 

Exemplul următor arată cum se utilizează constrângerea PRIMARY KEY la nivel de coloană.

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

Exemplul următor arată cum se definește cheia primară compozită utilizând constrângerea PRIMARY KEY la nivel de tabel.

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

Ceava străină

Când două tabele împart relația părinte-copil pe baza unei coloane specifice, coloana de îmbinare din tabelul copil este cunoscută sub numele de cheie străină.Această proprietate a coloanei corespunzătoare din tabelul părinte este cunoscută sub numele de integritate referențială.Valorile coloanei Foreign Key din tabelul copil pot fi fie nule, fie trebuie să fie valorile existente în tabelul părinte.Vă rugăm să rețineți că numai coloanele cu cheie primară ale tabelului de referință sunt eligibile pentru aplicarea integrității referențiale.

Dacă o cheie externă este definită pe coloana din tabelul copil, atunci Oracle nu permite ștergerea rândului părinte, dacă acesta conține rânduri copil.Cu toate acestea, dacă opțiunea ON DELETE CASCADE este dată în momentul definirii cheii externe, Oracle șterge toate rândurile copil în timp ce se șterge rândul părinte.În mod similar,ON DELETE SET NULL indică faptul că atunci când un rând din tabelul părinte este șters, valorile cheii străine sunt setate la zero.

Sintaxa:

Nivelul coloanei:

COLUMN 

Nivelul tabelei:

CONSTRAINT 

Exemplul următor arată cum se utilizează constrângerea FOREIGN KEY la nivel de coloană.

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

Utilizarea clauzei ON DELETE CASCADE

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

Constrângere de verificare

Câteodată, valorile datelor stocate într-o anumită coloană trebuie să se încadreze într-un anumit interval acceptabil de valori.O constrângere de VERIFICARE necesită ca condiția de verificare specificată să fie adevărată sau necunoscută pentru fiecare rând stocat în tabel.Constrângerea de verificare permite impunerea unei reguli condiționale asupra unei coloane, care trebuie validată înainte ca datele să fie inserate în coloană. Condiția nu trebuie să conțină o subinterogare sau o pseudo coloană CURRVAL NEXTVAL, LEVEL, ROWNUM sau SYSDATE.

Oracle permite ca o singură coloană să aibă mai mult de o constrângere CHECK. De fapt, nu există o limită practică a numărului de constrângeri CHECK care pot fi definite pentru o coloană.

Syntaxă:

Nivel coloană:

COLUMN CONSTRAINT 

Nivel tabel:

CONSTRAINT CHECK (condition)

Exemplul următor arată cum se utilizează constrângerea CHECK la nivel de coloană.

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

Exemplul următor arată cum se utilizează constrângerea CHECK la nivel de tabel.

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

Instrucțiunea ALTER TABLE

Un DBA poate face modificări la structura tabelului sau la definițiile coloanelor după ce tabelul a fost creat în baza de date.Comanda DDL ALTER TABLE este utilizată pentru a efectua astfel de acțiuni. comanda ALTER TABLE oferă mai multe utilități exclusive pentru obiecte de schemă. instrucțiunea ALTER TABLE este utilizată pentru a adăuga, elimina, redenumi și modifica o coloană într-un tabel.

Instrucțiunea ALTER TABLE de mai jos redenumește tabelul EMP în EMP_NEW.

ALTER TABLE EMP RENAME TO EMP_NEW;

Instrucțiunea ALTER TABLE de mai jos adaugă o nouă coloană TESTCOL la tabela EMP_NEW

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

Instrucțiunea ALTER TABLE de mai jos redenumește coloana TESTCOL în TESTNEW.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

Declarația ALTER TABLE de mai jos elimină coloana TESTNEW din tabelul EMP_NEW

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

Declarația ALTER TABLE de mai jos adaugă cheia primară pe coloana EMPLOYEE_ID.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

Declarația ALTER TABLE de mai jos elimină cheia primară.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

Declarația ALTER TABLE de mai jos comută modul tabelului în modul numai citire.

ALTER TABLE EMP_NEW READ ONLY;

Tabele de numai citire

Tabele de numai citire au apărut ca o îmbunătățire în Oracle 11g.Aceasta permite ca tabelele să fie utilizate numai pentru citire. În versiunile anterioare ale Oracle, tabelele au fost făcute numai pentru citire prin acordarea privilegiului SELECT celorlalți utilizatori, dar proprietarul avea în continuare privilegiul de citire și scriere.Dar acum, dacă o tabelă este setată ca fiind numai pentru citire, nici măcar proprietarul nu are acces la manipularea datelor.

Sintaxa:

ALTER TALE READ ONLY

ALTER TALE READ WRITE

Ilustrație

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.

Declarația DROP TABLE

Declarația DROP TABLE este utilizată pentru a elimina un tabel din baza de date. Tabelul eliminat și datele sale rămân indisponibile pentru selecție. tabelul eliminat poate fi recuperat cu ajutorul utilitarului FLASHBACK,dacă este disponibil în recyclebin.Eliminarea unui tabel elimină indexul și declanșatoarele asociate cu acesta.

Sintaxa:

DROP TABLE 

Explicația de mai jos va renunța la tabel și îl va plasa în recyclebin.

DROP TABLE emp_new;

Explicația de mai jos va renunța la tabel și îl va elimina și din recyclebin.

DROP TABLE emp_new PURGE;

.