Laborator11 BD

Universitatea Politehnica Bucureti

Facultatea de Automatic i Calculatoare

Catedra de Calculatoare

Laborator 11 Crearea, definirea, modificarea i tergerea obiectelor din baza de

date Autori

Conf. Dr. Ing. Alexandru Boicea

As. Drd. Ing. Ciprian-Octavian Truic

Crearea unui tabele

Crearea unei tabele printr-o cerere select

Constrngeri 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

Cuprins

07.03.2015 Laborator 11 2

Comenzile pentru crearea i definirea de structuri tabelare sunt comenzi de definire a datelor (Data Definition Language - DDL) i permit crearea dar i relaionarea lor ntr-o baz de date.

Structura unei tabele este data de urmtoarele specificaii de definire: Definirea coloanelor

Definirea constrngerilor de integritate

Definirea tablespace-ului unde se creeaza

Definirea parametrilor

Crearea unei tabele

07.03.2015 Laborator 11 3

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

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#SQLRF54416

Crearea unei tabele

07.03.2015 Laborator 11 4

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 definete constrngerile de integritate pe coloan

tablespace - specific n ce tablespace al bazei de date se creeaz tabela

Crearea unei tabele

07.03.2015 Laborator 11 5

storage_parameters - definete parametrii de creare i pot fi:

PCTFREE - procentaj de spaiu rezervat pentru update

PCTUSED - procentaj minim folosit pentru un bloc de date

INITRANS - numrul iniial de tranzacii pentru fiecare bloc (1-255)

MAXTRANS - numrul maxim de tranzacii concurente (1-255)

CLUSTER - specific dac tabela face parte dintr-un cluster

ENABLE/DISABLE clause - activare / dezactivare de constrngeri

AS subquery - inserare de date dintr-o alt tabel obinute printr-o interogare

Crearea unei tabele

07.03.2015 Laborator 11 6

Opiunea DEFAULT

Este folosit pentru a da o valaore implicit unei coloane;

Previne apariia de valori NULL atunci cnd o linie este inserat fr o valoare din coloan ;

Valorile implicite pot fi iruri de caractere, numere, i funcii dar nu poate conine numele unei alte coloane.

Crearea unei tabele

07.03.2015 Laborator 11 7

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)

Crearea unei tabele

07.03.2015 Laborator 11 8

Cteva dintre cele mai uzuale tipuri de date sunt:

NUMBER - numr real de dimensiune variabil (maxim 38 cifre)

NUMBER(n) - numr ntreg de n cifre

NUMBER(n,m) - numr 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 octei

LONG RAW - similar cu LONG dar conine date binare

ROWID - poate stoca identificatorul unei linii din tabel

DATE - data calendaristic

TIMESTAMP(n) - extensie pentru tipul DATE care conine i fraciuni de secund pe n zecimale

Crearea unei tabele

07.03.2015 Laborator 11 9

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 cuvnt rezervat Oracle ;

O tabel poate fi creat oricnd dar nu poate fi alterat cnd este accesat

de un alt user ;

La creare nu este necesar s se specifice dimensiunea tabelei dar trebuie

estimat ce spaiu va ocupa n tablespace ;

Structura tabelelor poate fi modificat i ulterior (prin adugarea sau

tergerea coloanelor, constrngerilor, indeci, etc.) ;

Dac dimensiunea inial este insuficient i se aloc automat mai mult spaiu n limita tablespace-ului (care la rndul lui poate fi extins cu un nou data file).

Crearea unei tabele

07.03.2015 Laborator 11 10

Ex. 1. S se creeze o tabel pentru evindena studentilor.

Crearea unei tabele

07.03.2015 Laborator 11 11

Sunt dou posibiliti de crea o tabel folosind o astfel de construcie:

Comanda CREATE TABLE nu conine descrierea structurii tabelare ;

Comanda CREATE TABLE conine descrierea structurii tabelare.


07.03.2015 Laborator 11 12

Comanda CREATE TABLE nu conine descrierea structurii tabelare:

Se creeaza o tabel cu o structur identic cu cmpurile specificate n cererea SELECT ;

Tipurile coloanelor se pstreaz ;

Dac cererea conine o expresie sau funcie trebuie s i atribuie un alias valid ;

Noua tabel nu motenete nicio constrngere de integritate de la vechile tabele, cu excepia NOT NULL.


07.03.2015 Laborator 11 13

Ex. 2. S se creeze o tabel care va conine veniturile angajaiilor din departamentul 20.


07.03.2015 Laborator 11 14

Comanda CREATE TABLE conine descrierea structurii tabelare:

Numrul coloanelor trebuie s coincid cu numrul coloanelor din cererea SELECT ;

Nu se va specifica tipul coloanelor ;

Nu se pot specifica tipuri de coloane n comand ;

Se poate aduga constrngerea NOT NULL i opiunea DEFAULT n comand ;

Dac cererea conine o expresie sau funcie nu trebuie s i se atribuie un alias deoarece definirea coloanei respective este impus de comand i nu de cerere ;

Nou tabel nu motenete nicio constrngere de integritate de la vechile coloane.


07.03.2015 Laborator 11 15

Ex. 3. S se creeze o tabel cu o prima de 15% din venitul lunar pentru angajaii din departamentul 30.


07.03.2015 Laborator 11 16

Constrngerile de integritate sunt anumite reguli care trebuie respectate la nivel de tabel sau n relaiile cu alte tabele ;

Aceste reguli sunt verificate automat n cazul operaiilor de inserare, tergere i modificare i, n cazul n care nu se valideaz, se genereaz o eroare i tranzacia nu se efectueaz

Constrngerile de integritate pot fi:

NOT NULL - nregistrrile nu pot conine valori nule

UNIQUE - definete o cheie unic pe una sau mai multe coloane (nu pot fi mai multe nregistrri cu aceleai valori pe coloanele respective

PRIMARY KEY - definete o cheie primar la nivel de coloan sau tabel (nu pot fi mai multe nregistrri cu aceeai cheie primar)

FOREIGN KEY - definete o cheie extern (tabela se relaioneaz cu alt tabel pe o cheie unic sau cheie primar)

CHECK - foreaz o condiie pe coloan


07.03.2015 Laborator 11 17

Caracteristici ale constrngerilor de integritate:

Fiecare constrngere va avea un nume dat de user sau generat de sistem ;

Constrngerile pot fi activate sau dezactivate cu comanda ALTER TABLE ;

Constrngerile pot fi adugate sau terse i dup ce o tabela a fost creat ;

Informaiile legate de constrngeri se pstreaz n dicionarul de date.

Observatie: Dac o constrngere are nume dat de utilizator atunci este mai uor s fie modificata.


07.03.2015 Laborator 11 18

Constrngerea NOT NULL:

Se aplic la nivel de coloane i verific dac nregistrrile au valorea null pe coloanele respective, fornd un cod de eroare care anuleaz tranzacia ;

Cand se creeaz constrangeri pe o cheie primar se creeaz automat i o constrngere NOT NULL pe coloanele respective (o cheie primara nu

trebuie s conin valori nule pe coloanele care o definesc).

Sintaxa:

column_name datatype [CONSTRAINT constraint_name] NOT NULL


07.03.2015 Laborator 11 19

Constrngerea UNIQUE:

Se folosete cnd vrem ca o coloan, sau perechi de coloane, s nu conin valori duplicare ;

Verificarea se face numai pentru nregistrrile cu valori nenule deoarece constrngerea 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 mrirea vitezei de integritate pe tabel ;

Dac constrngerea 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, [...]])


07.03.2015 Laborator 11 20


07.03.2015 Laborator 11 21

Constrngere PRIMARY KEY:

Se folosete pentru definirea cheii primare pe o coloan sau pereche de coloane ;

O tabel poate avea o singur constrngere de tip PRIMARY KEY i nu accept valori nule pentru colona sau perechea de coloane care o definesc ;

Cnd se creeaz o cheie primar se creeaz n mod automat i o constngere de timp NOT NULL i UNIQUE ;

Cnd se creeaz o cheie primar se creeaz n mod automat i un index pentru a scurta timpul de rspuns n cazul unei interogri.

Sintaxa la nivel de coloan:

column_name datatype [CONSTRAINT constraint_name]

PRIMARY KEY


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


07.03.2015 Laborator 11 22

Ex. 4. S se creeze un nomenclator de funcii n care fiecare funcie s aib un cod unic.


07.03.2015 Laborator 11 23

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


07.03.2015 Laborator 11 24

Constrngerea FOREIGN KEY:

Se folosete pentru a relaionarea unei talele cu una sau mai multe tabele, verificnd dac valorile coninute n coloanele definite de FOREIGN KEY (cheie strin sau cheie extern) sunt cuprinse n valorile coloanelor altei tabele care trebuie s fie definite ca UNIQUE sau PRIMARY KEY ;


column_name datatype [CONSTRAINT constraint_name]

REFERENCES table(column)

[on DELETE CASCADE | DELETE SET NULL]


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

REFERENCES table(column)

[on DELETE CASCADE | DELETE SET NULL]


07.03.2015 Laborator 11 25

Reguli pentru FOREIGN KEY

Inserarea unei linii ntr-o tabel relaional (pe care am definit FOREIGN KEY) nu se poate face dac nu exist dect o singur linie n tabela de referint (n care am definit PRIMARY KEY sau UNIQUE) corespunztor coloanelor de relaionare ;

tergerea unei linii din tabela de referin nu se poate face atta timp ct exist linii relaionale pe linia respectiv n tabela relaional ;

Regulile de mai sunt valabile i n cazul relaionrii pe coloane.


07.03.2015 Laborator 11 26

Opiunea ON DELETE CASCADE: Pentru a putea terge n tabela de referin linii referite n alte tabele

se folosete opiunea ON DELETE CASCADE. n acest caz, cnd se terge o linie n tabela de referin se vor terge toate liniile din tabelele relaionate care sunt n relaie cu linia respectiv ;

n cazul unei tabele relaionat cu ea nsi, tergerea unei linii care este referit duce la apariia de valori nule pe toate coloanele n liniile relaionate. Spre exemplu, n tabela angajati, cnd se terge linia aferent unui ef, toi angajaii care au eful respectiv vor primi valoare null pe coloana id_sef ;

Folosind opiunea ON DELETE SET NULL, coloanele de relaie din tabela relaionat devin nule i nu sunt terse liniile relaionate atunci cnd se terge o linie din tabela de referin.


07.03.2015 Laborator 11 27

Ex. 6. S se creeze tabela angajai i s se adauge constrngerile de integritate de tip FOREIGN KEY i PRIMARY KEY.


07.03.2015 Laborator 11 28

Constrngerea CHECK:

Se folosete pentru a fora valorile unei coloane s verifice o condiie

Condiia poate s conin i funcii, cu unele excepii (sysdate, user, etc.)


column_name datatype [CONSTRAINT constraint_name] CHECK

(expr)


[, CONSTRAINT constraint_name] CHECK (expr)


07.03.2015 Laborator 11 29

Ex. 7. S se creeze tabela angajai astfel nt s se verifice dac salariul este mai mare ca 0 , comisionul nu depeste salariul i numele este scris doar cu litere mari.


07.03.2015 Laborator 11 30

Comanda ALTER TABLE este folosit atunci cnd structura unei tabele trebuie modificat, sau unele proprieti trebuie s fie terse sau adugate.

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]

Comanda ALTER TABLE

07.03.2015 Laborator 11 31

Comanda ALTER TABLE

07.03.2015 Laborator 11 32

Comanda ALTER TABLE

07.03.2015 Laborator 11 33

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

Sintaxa:

DROP TABLE table_name

Comanda DROP TABLE

07.03.2015 Laborator 11 34

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 funcioneaz;

Aceast comand este mult mai rapid dect comanda DELETE.

Sintaxa:

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

DROP STORAGE elibereaz spaiul rezultat din tergerea liniilor;

REUSE STORAGE pstreaz spaiul rezultat din tergerea liniilor alocate tabelei .

Comanda TRUNCATE

07.03.2015 Laborator 11 35

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 INSERT

07.03.2015 Laborator 11 36

Comanda INSERT

07.03.2015 Laborator 11 37

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 numr de coloane egal cu numrul de coloane care va fi modificat

conditions condiia care trebuie ndeplinit pentru modificarea datelor

Comanda UPDATE

07.03.2015 Laborator 11 38

Cnd se face actualizarea datelor ntr-o tabel se verific automat i constrngerile de integritate definite pe tabela respectiv, altfel se va genera un cod de eroare i tranzacia va eua.

Situaii n care pot aprea erori: Noile valori sunt duplicare de cheie primar sau unic ;

Actualizarea valorii cu o valoare nul cnd coloana este NOT NULL ;

Valorile noi nu respect constrngerea CHECK ;

Valorile noi nu respect constrngerea FOREIGN KEY ;

Valorile vechi erau referite de alte tabele printr-o constrngere FOREIGN KEY ;

Subcererea returneaz mai multe nregistrri.

Comanda UPDATE

07.03.2015 Laborator 11 39

Comanda UPDATE

07.03.2015 Laborator 11 40

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 condiiile care trebuie ndeplinit pentru tergerea liniilor

Observatie: Clauza WHERE permite folosirea de subcereri.

Comanda DELETE

07.03.2015 Laborator 11 41

Comanda DELETE

07.03.2015 Laborator 11 42

COMMIT este folosit pentru a face persistente informaiile din baza de date dup ce tranzaciile s-au terminat cu succes.

ROLLBACK readuce starea bazei de date la o stare anterioar.

Comenzile COMMIT i ROLLBACK

07.03.2015 Laborator 11 43

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

07.03.2015 Laborator 11 44

sequence_name numele secvenei

INCREMENT BY specific incrementul

START WITH specific valoare de start

MAXVALUE specific valoarea maxim a secvenei

NOMAXVALUE specific ca valoarea maxim este 10^27 pentru secvene cresctoare i -1 pentru cele descresctoare, aceast valaoare este implicit

MINVALUE specific valoare minim a secvenei

NOMINVALUE specific ca valoarea minim este 1 pentru secvene cresctoare i -10^26 pentru cele descresctoare, aceast valaoare este implicit

SEQUENCE

07.03.2015 Laborator 11 45

CYCLE se folosete atunci cnd se dorete s se genereze valori chiar dac s-a ajuns la valoarea minim sau maxim ;

NOCYCLE se folosete atunci cnd nu se dorete s se genereze valori atunci cnd se ajunge la valoarea minim sau maxim;

CACHE specific cte valori se vor prealoca i se vor pstra n memorie ;

NOCACHE specific s nu se pre-aloce valori i s nu se pstreze 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.

SEQUENCE

07.03.2015 Laborator 11 46

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 }

DROP SEQUNEC SINTAX

DROP SEQUENCE sequence_name

SEQUENCE

07.03.2015 Laborator 11 47

SEQUENCE

07.03.2015 Laborator 11 48

Un index este o structur care mbuntete viteza de acces la datele dintr-o baz de date;

Indeci 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

Pentru a modifica opiunile unui index se folosete comanda ALTER INDEX.

INDEX

07.03.2015 Laborator 11 49

INDEX

07.03.2015 Laborator 11 50

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

VIEW

07.03.2015 Laborator 11 51

Pe un VIEW care extrage date dintr-o singur tabel se pot utiliza comenzile INSERT, UPDATE, DELETE;

Dac VIEW-UL extrage informaii din mai multe tebele atunci comenzile INSERT, UPDATE, DELETE nu se pot folosi.

VIEW

07.03.2015 Laborator 11 52

ALTER DATABASE pentru diverse operaiuni 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 afiare a mesajelor, setul de caractere utilizare, etc.

Simbarea formatului de afiare a datei calendaristice

ALTER SESSION set NLS_DATE_FORMAT=dd-mm-yyyy;

Alte Comenzi

07.03.2015 Laborator 11 53

ALTER SYSTEM pentru schimbarea parametrilor de sistem cum ar fi: numrul maxim de useri, numrul maxim de sesiuni, activarea/dezactivarea unor

parametri, nchiderea unei sesiuni;

Schimbarea numrului maxim de sesiuni pentru instana curent la 60 i avertizarea la 50 de sesiuni (trebuie s avei privilegiile necesare pentru a face modificri).

ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 60

LICENSE_SESSIONS_WARNING = 50;

Alte Comenzi

07.03.2015 Laborator 11 54

ALTER TABLESPACE- pentru modificarea parametrilor unui tablespace cum ar fi: adugarea de noi fiiere de date, modificarea vechilor parametri, etc.

Extinderea unui tablespace cu un nou fiier 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 puin cel de administrator al bazei de date.

ALTER USER student DEFAULT ROLE ALL EXCEPT DBA

Alte Comenzi

07.03.2015 Laborator 11 55

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;

Alte Comenzi

07.03.2015 Laborator 11 56

GRANT {privilege|role} [ON TABLE] TO username - pentru a grantifica privilegiile sau

rolurile unor utilizatori.

REVOKE {privilege|role} [ON TABLE] FROM username pentru a revoca privilegiile sau rolurile unor utilizatori.

Alte Comenzi

07.03.2015 Laborator 11 57

Laborator11 BD

Documents

Transcript of Laborator11 BD