Table of Contents

Laborator 10 - Crearea, definirea, modificarea și ștergerea obiectelor din baza de date

Conținut

Crearea unei tabele

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:

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ă

Opțiunea DEFAULT Este folosită pentru a da o valaore implicită unei coloane. Previne apariția de valori NULL atunci când o linie este inserată fără o valoare din coloană. Valorile implicite pot fi șiruri de caractere, numere, și funcții dar nu poate conține numele unei alte coloane.

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

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

Reguli pentru crearea unei tabele:

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:

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:

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

Constrângeri de integritate

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:

Caracteristici ale constrângerilor de integritate:

Dacă o constrângere are nume dat de utilizator atunci este mai ușor să fie modificata.

Constrângerea NOT NULL

Sintaxa: 

column_name datatype [CONSTRAINT constraint_name] NOT NULL

Constrângerea UNIQUE

Sintaxă la nivel de coloană: 

column_name datatype [CONSTRAINT constraint_name] UNIQUE

Sintaxă la nivel de tabelă: 

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

Constrângere PRIMARY KEY

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.

Constrângerea FOREIGN 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

Opțiunea ON DELETE CASCADE

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

Constrângerea CHECK

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

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

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

Sintaxa: 

DROP TABLE TABLE_NAME

Comanda TRUNCATE

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

După ce am dat această comandă, comnda ROLLBACK nu funcționează.

Această comandă este mult mai rapidă decât comanda DELETE.

Sintaxa: 

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

DROP STORAGE – eliberează spațiul rezultat din ștergerea liniilor. REUSE STORAGE – păstrează spațiul rezultat din ștergerea liniilor alocate tabelei.

Comanda INSERT

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}

Comanda UPDATE

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]

Când se face actualizarea datelor într-o tabelă se verifică automat și constrângerile de integritate definite pe tabela respectivă, altfel se va genera un cod de eroare și tranzacția va eșua.

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

Comanda DELETE

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

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

Clauza WHERE permite folosirea de subcereri.

SEQUENCE

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

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

INDEX

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

VIEW

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

Pe un VIEW care extrage date dintr-o singură tabelă se pot utiliza comenzile INSERT, UPDATE, DELETE. Dacă VIEW-UL extrage informații din mai multe tebele atunci comenzile INSERT, UPDATE, DELETE nu se pot folosi.

Alte comenzi

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