Conținut

• Crearea unui tabele
• Crearea unei tabele printr-o cerere select
• Constrângeri de integritate
• Comanda ALTER TABLE
• Comanda DROP TABLE
• Comanda TRUNCATE
• Comenda INSERT
• Comanda UPDATE
• Comanda DELETE
• Comenzile COMMIT și ROLLBACK
• SEQUENCE
• INDEX
• VIEW
• Alte comenzi

Comenzile pentru crearea și definirea de structuri tabelare sunt comenzi de definire a datelor (Data Definition Language - DDL) și permit crearea dar și relaționarea lor într-o bază de date. Structura unei tabele este data de următoarele specificații de definire:

• Definirea coloanelor
• Definirea constrângerilor de integritate
• Definirea tablespace-ului unde se creeaza
• Definirea parametrilor

Sintaxa

CREATE TABLE [ schema.]TABLE_NAME [table_constraint]
	COLUMN datatype [DEFAULT expr] [column_constraints]
	[table_constraints] [TABLESPACE tablespace]
	[storage parameters]
	[ENABLE|DISABLE CLAUSE] [AS subquery]

Sintaxa completă

• schema – este schema unde se creează tabela (specifică utilizatorul și baza de date)
• tabel_name – este numele tabelei
• column – este numele coloanei
• datatype – reprezintă tipul coloanei
• DEFAULT expr – specifică valoarea implicită a coloanei
• column_constraint – definește constrângerile de integritate pe coloană
• tablespace - specifică în ce tablespace al bazei de date se creează tabela
• storage_parameters - definește parametrii de creare și pot fi:
  • PCTFREE - procentaj de spațiu rezervat pentru update
  • PCTUSED - procentaj minim folosit pentru un bloc de date
  • INITRANS - numărul inițial de tranzacții pentru fiecare bloc (1-255)
  • MAXTRANS - numărul maxim de tranzacții concurente (1-255)
  • CLUSTER - specifică dacă tabela face parte dintr-un cluster
• ENABLE/DISABLE clause - activare / dezactivare de constrângeri
• AS subquery - inserare de date dintr-o altă tabelă obținute printr-o interogare

Tipurile de date care pot fi asociate coloanelor unei tabele pot fi :

• tipuri numerice
• tipuri alfanumerice
• tipuri pentru data calendaristică și timp
• tipuri compuse (matrice sau tabelă)

Câteva dintre cele mai uzuale tipuri de date sunt:

• NUMBER - număr real de dimensiune variabilă (maxim 38 cifre)
• NUMBER(n) - număr întreg de n cifre
• NUMBER(n,m) - număr real de n cifre, dintre care m zecimale
• CHAR(n) - șir de caractere de lungime fixă n (1-2000)
• NCHAR(n) - analog cu CHAR, dar poate stoca șiruri de caractere Unicode
• VARCHAR2(n) - șir de caractere de lungime variabilă n
• NVARCHAR2(n) - analog cu VARCHAR dar poate stoca șiruri de caractere Unicode
• LONG - șir de caractere de maxim 2 la puterea 31 octeți
• LONG RAW - similar cu LONG dar conține date binare
• ROWID - poate stoca identificatorul unei linii din tabelă
• DATE - data calendaristică
• TIMESTAMP(n) - extensie pentru tipul DATE care conține și fracțiuni de secundă pe n zecimale

Reguli pentru crearea unei tabele:

• Userul trebuie să aibă drepturi de crearea a unei tabele.
• Numele trebuie să fie unic în contul în care se creeaza și nu este case sentitive.
• Numele trebuie să aibă maxim 30 de caractere continue, să înceapă cu o literă și să nu fie cuvânt rezervat Oracle.
• O tabelă poate fi creată oricând dar nu poate fi alterată când este accesată de un alt user.
• La creare nu este necesar să se specifice dimensiunea tabelei dar trebuie estimat ce spațiu va ocupa în tablespace.
• Structura tabelelor poate fi modificată și ulterior (prin adăugarea sau ștergerea coloanelor, constrângerilor, indecși, etc.).
• Dacă dimensiunea ințială este insuficientă i se alocă automat mai mult spațiu în limita tablespace-ului (care la rândul lui poate fi extins cu un nou data file).

Ex. 1. Să se creeze o tabelă pentru evindența studentilor.

Crearea unei tabele printr-o cerere select

Sunt două posibilități de crea o tabelă folosind o astfel de construcție:

• Comanda CREATE TABLE nu conține descrierea structurii tabelare.
• Comanda CREATE TABLE conține descrierea structurii tabelare.

Comanda CREATE TABLE nu conține descrierea structurii tabelare:

• Se creează o tabelă cu o structură identică cu câmpurile specificate în cererea SELECT.
• Tipurile coloanelor se păstrează.
• Dacă cererea conține o expresie sau funcție trebuie să i se atribuie un alias valid.
• Noua tabelă nu moștenește nicio constrângere de integritate de la vechile tabele, cu excepția NOT NULL.

Ex. 2. Să se creeze o tabelă care va conține veniturile angajațiilor din departamentul 20.

Comanda CREATE TABLE conține descrierea structurii tabelare:

• Numărul coloanelor trebuie să coincidă cu numărul coloanelor din cererea SELECT.
• Nu se va specifica tipul coloanelor.
• Nu se pot specifica tipuri de coloane în comandă.
• Se poate adăuga constrângerea NOT NULL și opțiunea DEFAULT în comandă.
• Dacă cererea conține o expresie sau funcție nu trebuie să i se atribuie un alias deoarece definirea coloanei respective este impusă de comandă și nu de cerere.
• Noua tabelă nu moștenește nicio constrângere de integritate de la vechile coloane.

Ex. 3. Să se creeze o tabelă cu o prima de 15% din venitul lunar pentru angajații din departamentul 30.

Constrângerile de integritate sunt anumite reguli care trebuie respectate la nivel de tabelă sau în relațiile cu alte tabele. Aceste reguli sunt verificate automat în cazul operațiilor de inserare, ștergere și modificare și, în cazul în care nu se validează, se generează o eroare și tranzacția nu se efectuează.

Constrângerile de integritate pot fi:

• NOT NULL - înregistrările nu pot conține valori nule
• UNIQUE - definește o cheie unică pe una sau mai multe coloane (nu pot fi mai multe înregistrări cu aceleași valori pe coloanele respective
• PRIMARY KEY - definește o cheie primară la nivel de coloană sau tabelă (nu pot fi mai multe înregistrări cu aceeași cheie primară)
• FOREIGN KEY - definește o cheie externă (tabela se relaționează cu altă tabelă pe o cheie unică sau cheie primară)
• CHECK - forțează o condiție pe coloană

Caracteristici ale constrângerilor de integritate:

• Fiecare constrângere va avea un nume dat de user sau generat de sistem.
• Constrângerile pot fi activate sau dezactivate cu comanda ALTER TABLE.
• Constrângerile pot fi adăugate sau șterse și după ce o tabela a fost creată.
• Informațiile legate de constrângeri se păstrează în dicționarul de date.

• Se aplică la nivel de coloane și verifică dacă înregistrările au valorea null pe coloanele respective, forțând un cod de eroare care anulează tranzacția.
• Cand se creează constrangeri pe o cheie primară se creează automat și o constrângere NOT NULL pe coloanele respective (o cheie primara nu trebuie să conțină valori nule pe coloanele care o definesc).

Sintaxa:

column_name datatype [CONSTRAINT constraint_name] NOT NULL

• Se folosește când vrem ca o coloană, sau perechi de coloane, să nu conțină valori duplicare.
• Verificarea se face numai pentru înregistrările cu valori nenule deoarece constrângerea permite inserarea de valori nule în coloanele respective.
• În mod automat se creează și un index pe coloanele define cheie unice, ceea ce duce la mărirea vitezei de integritate pe tabelă.
• Dacă constrângerea se face pe o pereche de coloane atunci setul trebuie să fie unic.

Sintaxă la nivel de coloană:

column_name datatype [CONSTRAINT constraint_name] UNIQUE

Sintaxă la nivel de tabelă:

[, CONSTRAINT constraint_name] UNIQUE (col1[, col2, [...]])

• Se folosește pentru definirea cheii primare pe o coloană sau pereche de coloane.
• O tabelă poate avea o singură constrângere de tip PRIMARY KEY și nu acceptă valori nule pentru colona sau perechea de coloane care o definesc.
• Când se creează o cheie primară se creează în mod automat și o constângere de timp NOT NULL și UNIQUE.
• Când se creează o cheie primară se creează în mod automat și un index pentru a scurta timpul de răspuns în cazul unei interogări.

Sintaxa la nivel de coloană:

column_name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Sintaxă la nivel de tabelă:

[, CONSTRAINT constraint_name] PRIMARY KEY(col1[, col2, [...]])

Ex. 4. Să se creeze un nomenclator de funcții în care fiecare funcție să aibă un cod unic.

Ex. 5. Să se creeze o tabelă pentru a păstra date despre o persoană. Cheia primară să se facă pe seria CI, cod CI și CNP.

• Se folosește pentru a relaționarea unei talele cu una sau mai multe tabele, verificând dacă valorile conținute în coloanele definite de FOREIGN KEY (cheie străină sau cheie externă) sunt cuprinse în valorile coloanelor altei tabele care trebuie să fie definite ca UNIQUE sau PRIMARY KEY.

Sintaxa la nivel de coloană:

column_name datatype [CONSTRAINT constraint_name] 
	REFERENCES TABLE(COLUMN)
	[ON DELETE CASCADE | DELETE SET NULL]

Sintaxă la nivel de tabelă:

[, CONSTRAINT constraint_name] FOREIGN KEY(col1[, col2, [...]])
	REFERENCES TABLE(COLUMN)
	[ON DELETE CASCADE | DELETE SET NULL]

Reguli pentru FOREIGN KEY

• Inserarea unei linii într-o tabelă relațională (pe care am definit FOREIGN KEY) nu se poate face dacă valoarea pe care vrem să o inserăm pe coloana pe care avem foreign key nu există în tabela referită (în care am definit PRIMARY KEY sau UNIQUE) corespunzător coloanelor de relaționare. Se poate totuși insera valoarea null.
• Ștergerea unei linii din tabela de referință nu se poate face atâta timp cât există linii relaționale pe linia respectivă în tabela relațională.
• Regulile de mai sunt valabile și în cazul relaționării pe coloane.

Opțiunea ON DELETE CASCADE

• Pentru a putea șterge în tabela de referință linii referite în alte tabele se folosește opțiunea ON DELETE CASCADE. În acest caz, când se șterge o linie în tabela de referință se vor șterge toate liniile din tabelele relaționate care sunt în relație cu linia respectivă.
• În cazul unei tabele relaționată cu ea însăși, ștergerea unei linii care este referită duce la apariția de valori nule pe toate coloanele în liniile relaționate. Spre exemplu, în tabela angajati, când se șterge linia aferentă unui șef, toți angajații care au șeful respectiv vor primi valoare null pe coloana id_sef.
• Folosind opțiunea ON DELETE SET NULL, coloanele de relație din tabela relaționată devin nule și nu sunt șterse liniile relaționate atunci când se șterge o linie din tabela de referință.

Ex. 6. Să se creeze tabela angajați și să se adauge constrângerile de integritate de tip FOREIGN KEY și PRIMARY KEY.

• Se folosește pentru a forța valorile unei coloane să verifice o condiție
• Condiția poate să conțină și funcții, cu unele excepții (sysdate, user, etc.)

Sintaxa la nivel de coloană: column_name datatype [CONSTRAINT constraint_name] CHECK (expr) Sintaxă la nivel de tabelă:

[, CONSTRAINT constraint_name] CHECK (expr)

Ex. 7.Să se creeze tabela angajați astfel înât să se verifice dacă salariul este mai mare ca 0 , comisionul nu depășeste salariul și numele este scris doar cu litere mari.

Comanda ALTER TABLE este folosită atunci când structura unei tabele trebuie modificată, sau unele proprietăți trebuie să fie șterse sau adăugate.

Sintaxa:

ALTER TABLE [schema.]TABLE_NAME
	ADD {(COLUMN datatype) [DEFAULT EXP] | (column_constaints) } |
	MODIFY {(COLUMN datatype) [DEFAULT EXP] | (column_constaints) } |
	DROP drop_clause |
	ENABLE enable_clause |
	DISABLE disable_clause |
	ALLOCATE EXTENT SIZE INTEGER [K, M] 
				DATAFILE ‘filename’
				INSTANCE INTEGER 
	[storage_parameters]

Pentru a adăuga o nouă coloană se folosește comanda:

Comanda DROP TABLE este folosită pentru a șterge tabela din dictionarul bazei de date.

Sintaxa:

DROP TABLE TABLE_NAME

Comanda TRUNCATE este folosită pentru golirea unui tabel. Această comandă șterge toate liniile dintr-o tabelă.

Sintaxa:

TRUNCATE TABLE [schema.]TABLE [{DROP|REUSE} STORAGE]

Această comandă este folosită pentru inserarea de linii într-o tabelă.

Sintaxa:

INSERT INTO [shema.]{TABLE_NAME|view_name} [(col_1, col_2, ...)]
	{VALUES(exp_1, expr_2, ...) | subquery}

• table_name|view_name – numele tabelei sau view-ului în care se face inserarea
• col_i – numele de coloană în care se face inserare
• expr_i – valorile aferente coloanelor
• subquery – este o subcerere care va întoarce valori ce vor fi utilizate la inserare

Comanda UPDATE este folosită pentru a actualiza datele din baza de date.

Sintaxa:

UPDATE [shema.]{TABLE_NAME|view_name} 
	SET 
		column_1 = {expr | subquery}, ... , column_n = {expr | subquery}  |
		{column_1, ... , column_n} = subquery
	[WHERE conditions]

• table_name|view_name – numele tabelei sau view-ului care se modifică
• column_i – numele de coloană în care se modifică
• expr – valorile aferente coloanelor
• subquery – este o subcerere care va întoarce o linie și un număr de coloane egal cu numărul de coloane care va fi modificat
• conditions – condiția care trebuie îndeplinită pentru modificarea datelor

Situații în care pot apărea erori:

• Noile valori sunt duplicare de cheie primară sau unică.
• Actualizarea valorii cu o valoare nulă când coloana este NOT NULL.
• Valorile noi nu respectă constrângerea CHECK.
• Valorile noi nu respectă constrângerea FOREIGN KEY.
• Valorile vechi erau referite de alte tabele printr-o constrângere FOREIGN KEY.
• Subcererea returnează mai multe înregistrări.

Comanda DELETE este folosită pentru ștergerea liniilor dintr-o tabelă sau view. Sintaxa:

DELETE FROM [schema.] {TABLE_NAME|view_name} 
WHERE conditions

• table_name|view_name – numele tabelului sau viewului din care se șterg linii
• conditions – condițiile care trebuie îndeplinită pentru ștergerea liniilor

O secvență este un obiect al bazei de date care generează numere intregi unice. O secvență este folosită pentru a genera valori pentru cheia primară. Sintaxa:

CREATE SEQUENCE [schema. ]sequence_name
	 [[ INCREMENT BY INTEGER ]
	| [START WITH ] INTEGER 
	| [ MAXVALUE INTEGER | NOMAXVALUE ]
	| [ MINVALUE INTEGER | NOMINVALUE ]
	| [ CYCLE | NOCYCLE ]
	| [ CACHE INTEGER | NOCACHE } ]
	| [ ORDER | NOORDER ] ]

• sequence_name – numele secvenței
• INCREMENT BY – specifică incrementul
• START WITH – specifică valoare de start
• MAXVALUE – specifică valoarea maximă a secvenței
• NOMAXVALUE – specifică ca valoarea maximă este 10^27 pentru secvențe crescătoare și -1 pentru cele descrescătoare, această valaoare este implicită
• MINVALUE – specifică valoare minimă a secvenței
• NOMINVALUE – specifică ca valoarea minimă este 1 pentru secvențe crescătoare și -10^26 pentru cele descrescătoare, această valaoare este implicită
• CYCLE – se folosește atunci când se dorește să se genereze valori chiar dacă s-a ajuns la valoarea minimă sau maximă ;
• NOCYCLE – se folosește atunci când nu se dorește să se genereze valori atunci când se ajunge la valoarea minimă sau maximă;
• CACHE – specifică câte valori se vor prealoca și se vor păstra în memorie ;
• NOCACHE – specifică să nu se pre-aloce valori și să nu se păstreze în memorie;
• ORDER – garantează că valorile se generează în ordine, folosit pentru RAC (Oracle Real Application Clusters);
• NOORDER – nu garantează că valorile se generează în ordine.

Comanda ALTER SEQUENCE

Sintaxa:

ALTER SEQUENCE [ schema. ] sequence_name
{ INCREMENT BY INTEGER 
| { MAXVALUE INTEGER | NOMAXVALUE } 
| { MINVALUE INTEGER | NOMINVALUE } 
| { CYCLE | NOCYCLE } 
| { CACHE INTEGER | NOCACHE } 
| { ORDER | NOORDER }

Comanda DROP SEQUENCE

DROP SEQUENCE sequence_name

Un index este o structură care îmbunătățește viteza de acces la datele dintr-o bază de date. Indecșii se creează automat în momentul în care se creează o cheie primară sau una unică. Sintaxa:

CREATE 
	[ONLINE|OFFLINE] 
	[UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
	ON TABLE_NAME(column_name)
	index_options

Ștergerea unui index:

DROP INDEX index_name

O vedere este o tabelă logică care extrage date dintr-o tabelă propriu-zisă sau dintr-o alta vedere. O vedere nu are date proprii, ci este ca o fereastră prin care datele din tabele pot fi actualizate sau vizualizate. O vedere poate să conțină toate datele dintr-o tabelă (sau mai multe) sau anumite coloane. Sintaxa:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [ (alias [, alias]...) ] 
  AS subquery 
[WITH CHECK OPTION [CONSTRAINT CONSTRAINT] ]
[WITH READ ONLY [CONSTRAINT CONSTRAINT] ];

Pentru a șterge o tabelă atunci se va folosi comanda DROP.

DROP VIEW view_name

ALTER DATABASE – pentru diverse operațiuni asupra bazei de date cum ar fi: deschiderea, inchiderea, montarea, crearea de noi fisiere de date, salvarea fisierelor de control, etc.

ALTER DATABASE {MOUNT | OPEN [RESETLOGS]}

ALTER SESSION – pentru schimbarea parametrilor unei sesiuni cum ar fi formatul de date, limba de afișare a mesajelor, setul de caractere utilizare, etc. Simbarea formatului de afișare a datei calendaristice

ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy';

ALTER SYSTEM – pentru schimbarea parametrilor de sistem cum ar fi: numărul maxim de useri, numărul maxim de sesiuni, activarea/dezactivarea unor parametri, închiderea unei sesiuni; Schimbarea numărului maxim de sesiuni pentru instanța curentă la 60 și avertizarea la 50 de sesiuni (trebuie să aveți privilegiile necesare pentru a face modificări).

ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 60 LICENSE_SESSIONS_WARNING = 50;

ALTER TABLESPACE – pentru modificarea parametrilor unui tablespace cum ar fi: adăugarea de noi fișiere de date, modificarea vechilor parametri, etc. Extinderea unui tablespace cu un nou fișier de date:

ALTER TABLESPACE ADD DATAFILE 'E:\student\file.dat'

ALTER USER – pentru modificarea parametrilor de user cum ar fi: schimbarea parolei, alocarea de roluri, specificarea tablespace-ului implicit, cota alocată în tablespace, etc. Atribuirea tuturor rolurilor pentru userul student mai puțin cel de administrator al bazei de date.

ALTER USER student DEFAULT ROLE ALL EXCEPT DBA

CONNECT – pentru conextarea la baza de date.

CONNECT sys AS SYSDBA

CREATE ROLE – pentru crearea rolurilor de acces la obiectele bazei de date.

CREATE ROLE admin IDENTIFY BY sef

CREATE TABLESPACE – pentru a crea un nou tablespace.

CREATE USER – pentru crearea unui user.

CREATE USER student
	IDENTIFIED BY student
	DEFAULT TABLESPACE student;

GRANT – pentru a grantifica privilegiile sau rolurile unor utilizatori.

GRANT {privilege|ROLE} [ON TABLE] TO username

REVOKE – pentru a revoca privilegiile sau rolurile unor utilizatori.

REVOKE {privilege|ROLE} [ON TABLE] FROM username