PROGRAMARE BAZE DE DATE ORACLE

Capitolul 1

Prof. Cojocar-Horobăț Luminița

Crearea unui tabel – comanda

CREATE TABLE Comanda CREATE TABEL permite crearea unui

nou tabel în schema proprie (mulţimea tuturor obiectelor din proprietatea sa) şi are sintaxa: CREATE TABLE <nume tabel> (<nume coloana 1 > <tip coloana

1 [DEFAULT <valoare sau expresie>]>

[CONSTRAINT] [DISABLE] <contrângere>, <nume coloana n> <tip coloana n>)

sau

CREATE TABLE <nume tabel> AS

SELECT <coloana 1>, <coloana 2>, <coloana n>

FROM <tabel>

Reguli

• Numele tabelului nu poate depăşi 30 de

caractere, poate conţine doar litere, cifre şi

caracterele _,$ şi #. Începe obligatoriu cu

literă, nu se pot folosi cuvinte rezervate

Oracle.

• Regulile de mai sus se aplică şi în cazul

coloanelor.

• Proprietarul are toate drepturile asupra obiectelor

lui.

• Dacă se doreşte crearea unui tabel în orice

schemă, se foloseşte comanda:

CREATE ANY TABLE

Tabelele utilizatorilor sunt înregistrate în dicţionarul

bazei de date (catalogul BD). Acest dicţionar se

creează odată cu crearea bazei de date şi are în

spate nişte tabele interne.

Din dicţionarul BD se pot afla toate

tabelele existente cu comanda :

SELECT * FROM DICTIONARY

DBA *

ALL *

USER *

Toate obiectele din BD

Toate obiectele din schema proprie şi acele obiecte din alte scheme pt. care am privilegii de acces

Tables, views, queries, etc….

Continuare – CREATE

TABLE

• Coloanele pot avea valori implicite (Default Value). Valorile implicite pot fi valori literale, expresii sau funcţii SQL (SYSDATE, USER), dar nu pot fi numele altor coloane, pseudocoloane (NEXTVAL sau CURRVAL). Bineînţeles, valorile implicite trebuie să fie compatibile cu tipul datei coloanei respective.

Continuare – CREATE

TABLE

• O altă metodă de a crea un nou tabel este

aceea de a folosi o interogare.

CREATE TABLE <nume tabel> AS

SELECT <coloana 1>, <coloana 2>, <coloana

n>

FROM <tabel>

Observaţii: 1. numai definiţiile tipurilor şi

constrângerile NOT NULL sunt transmise tabelei nou create printr-un subquery (tabela va fi utilizată într-un nou context în care nu sunt relevante relaţiile pk-fk. 2. numele şi tipurile coloanelor din noua tabelă vor fi identice cu cele din tabela sursă.

Scrieţi o comandă SQL care crează tabelul

products table cu următoarea structură:

• product_id ,

• product_name,

• product_description ,

• purchase_date – valoare implicită data

curentă.

CREATE TABLE products

(product_id NUMBER(2),

product_name VARCHAR2(20),

product_description VARCHAR2(100),

purchase_date DATE DEFAULT

SYSDATE);

FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_ID

DEPART

MENT_ID

Steven King 24000 17-Jun-87 AD_PRES 90

Lex De Haan 17000 13-Jan-93 AD_VP 90

Alexander Hunold 9000 03-Jan-90 IT_PROG 60

Bruce Ernst 6000 21-May-91 IT_PROG 60

Diana Lorentz 4200 07-Feb-99 IT_PROG 60

Kevin Mourgos 5800 16-Nov-99 ST_MAN 50

Randall Matos 2600 15-Mar-98 ST_CLERK 50

Peter Vargas 2500 09-Jul-98 ST_CLERK 50

Eleni Zlotkey 10500 29-Jan-00 SA_MAN 80

Fie tabelul de mai sus, conţinând angajaţii

unei întreprinderi. Creaţi din acest tabel un

alt tabel, care va conţine numele, prenumele

şi data angajării angajaţilor care au salariul

mai mare de 1000 $.

CREATE TABLE copy_employees AS

(SELECT first_name, last_name,

hire_date

from employees

WHERE salary > 1000);

TIPURI DE DATE SQL

• Varchar2 – şir de caractere de lungime variabilă

de max. 4000 caractere; se precizează obligatoriu

lungimea şirului; nu pune blank-uri

– Ex: varchar2(80)

• Char – şir de caractere de lungime fixă până la

2000 de caractere; dacă utilizatorul furnizează

mai puţine caractere, se adaugă blank- uri la

stânga.

• Number – număr real de până la 38 cifre

– Ex: NUMBER(10,2) – partea întreagă formată din 8 cifre, iar partea zecimală din 2. Punctul zecimal nu se numără.

• Date – dată calendaristică, nu poate stoca fracţiuni de secundă

– Ex: 21-Aug-2007 13:12:10 este corect

– Ex: 21-Aug-2007 13:12:10.245 este incorect

Formatul implicit al datei calendaristice este

DD-MON-YY

• Timestamp – este o extensie a tipului Date

şi permite stocarea datelor până la ordinul

nanosecundelor.

– Ex: timestamp(3) alocă 3 cifre după secunde,

adică măsoară în milisecunde

• Timestamp with timezone – stochează

diferenţa de fus orar

• Timestamp with local timezone – este

raportat la diferenţa de fus orar al BD.

• Interval data types

– Returnează timpul scurs între două date

calendaristice

• Interval year(precizie_an) to month –

returnează intervalul de timp exprimat în

ani şi luni; precizie_an se referă la numărul

de cifre din reprezentarea anul şi are

valoarea implicită 2.

• Interval day(precizie_zile) to second

(precizie fracţiuni de secundă) - returnează

intervalul de timp exprimat în zile, ore,

minute şi secunde, unde precizie_zile se

referă la numărul maxim de cifre alocate

zilei şi are valoarea implicită 2, iar precizie

fracţiuni de secundă reprezintă numărul de

cifre zecimale din reprezentarea secundei,

având valoarea implicită 6.

• LONG – se foloseşte în cazul datelor

multimedia, caractere de dimensiune de

până la 2 G; stocarea este in-line, folosirea

acestui tip nu este recomandată.

• RAW– se foloseşte pentru date binare,

fişiere multimedia (jpg, mp3, wav) de

dimensiune variabilă de max. 2000 bytes;

• CLOB - caracter large object – tip de

caractere de dimensiune variabilă – max. 4

Giga

• BLOB – binary large object - tip

multimedia de dimensiune variabilă – max.

4 Giga

• BFILE – şir binar de 4 Giga, se reţine doar

directorul şi numele fisierului

Modificarea structurii unui tabel –

inserarea / ştergerea coloanelor

• Pentru a insera, şterge sau modifica o coloană în/dintr-un tabel, se foloseşte comanda ALTER TABEL, care are următoarea sintaxă:

ALTER TABLE <table>

ADD (<numele noii coloane> <tipul datei>)

DROP COLUMN <numele coloanei>

MODIFY (<coloana 1> <tipul datei> [DEFAULT <valoare sau expresie>], <coloana n> <tipul datei>)

ALTER • Adăugarea unei noi coloane

• Modificarea unei coloane existente

• Definirea unei valori DEFAULT pentru o coloană

• Ştergerea unei coloane

Inserarea unei coloane

• Se poate adăuga o nouă coloană într-un

tabel existent, folosind comanda

ALTER TABLE numetabel

ADD (numecoloana tipdata [default…],….)

OBSERVAŢII

• Coloanele noi se vor adăuga la sfârşitul tabelei

• Dacă tabela deja conţine linii, la adăugarea unei coloane, toate valorile coloanei vor fi NULL.

ADĂUGAREA UNEI COLOANE

Modificarea unei coloane Pentru a modifica o coloană, se foloseşte comanda

MODIFY. Cu ajutorul ei se poate:

• Mări dimensiunea unei coloane corespunzătoare unui câmp numeric sau şir de caractere.

• Micşora dimensiunea unei coloane doar dacă aceasta conţine numai valori nule sau tabelul nu conţine înregistrări (linii).

• Modifica tipul coloanei doar dacă coloana conţine valori nule.

Continuare - MODIFY

• converti tipul char în varchar2 sau varchar2

în char doar dacă coloana conţine valori

nule sau dacă nu se modifică dimensiunea

coloanei. O modificare a valorii implicite a

unei coloane va avea efect doar asupra

datelor nou-introduse.

Stergerea unei coloane

• Stergerea unei coloane se face cu comanda

ALTER TABLE numetabel

DROP COLUMN numecoloana

ŞTERGEREA UNEI COLOANE

• Coloana poate fi ştearsă chiar dacă conţine sau nu date

• Doar o coloană poate fi ştearsă la un moment dat • Nu se pot şterge toate coloanele unei tabele;

trebuie să rămână cel puţin una • După ştergere, datele nu mai pot fi recuperate

NEUTILIZAREA UNOR COLOANE • Coloanele se pot seta ca

invizibile, astfel încât datele rămân, dar sunt neutilizabile

• Se pot adăuga noi coloane cu acelaşi nume ca al celei neutilizate

DROP TABLE • Şterge definiţia unei

tabele Oracle

• Se vor şterge toate datele şi toţi indecşii asociaţi

• Descrierea tabelei este ştearsă din Data Dictionary

• Este ireversibilă!!!

Inserarea liniilor – Comanda

INSERT

Comanda INSERT permite inserarea unor linii

într-un tabel.

Este o comandă de tranzacţie, manipulare a

datelor (DML), la fel ca şi Update, Delete şi

Merge.

INSERT INTO <nume tabel>(<coloana 1>, <coloana 2>,

....<coloana n>)

VALUES (<valoare 1>, <valoare 2>, ....<valoare n>)

OBSERVAŢIE

Denumirile coloanelor pot fi scrise explicit,

sau pot lipsi (se recomandă numai în cazul în care

utilizatorul este foarte sigur asupra structurii

tabelului, dar trebuie să fie atent la valorile trecute

în clauza VALUES şi la tipul acestora, precum şi

la ordinea în care apar).

INSERAREA LINIILOR CU

VALORI NULL

Numele unei coloane poate fi omis din clauza

INSERT, numai dacă admite valori NULL

INSERAREA VALORILOR

SPECIALE

Se pot folosi SYSDATE(data şi ora curentă) şi

USER(utilizatorul din sesiunea curentă), funcţii şi

expresii de calcul.

INSERAREA VALORILOR DATĂ CALENDARISTICĂ

COPIEREA UNOR LINII FOLOSIND

SUBQUERY

• Numărul coloanelor şi tipul lor trebuie să coincidă

în clauza INSERT şi în subquery

• Subquery nu mai trebuie pus în paranteză, ca în

clauza WHERE a lui SELECT.

Nu putem insera toate liniile dintr-un

tabel într-ul alt tabel, decât dacă ambele

tabele au acelaşi număr de coloane, în

aceeaşi ordine şi au acelaşi tip.

INSERT INTO copy_d_events (ID, NAME, EVENT_DATE, DESCRIPTION, COST, VENUE_ID, PACKAGE_CODE, THEME_CODE,CLIENT_NUMBER)

VALUES (110, 'Ayako Anniversary', TO_DATE('07-JUL-04','DD-MON-RR'), 'Party for 50, sixties dress, decorations', 0, 245, 79, 240, 6655);

Modificarea unei coloane folosind

o subquery

Modificarea unei coloane folosind

2 subquery

Modificarea liniilor folosind

o altă tabelă

Observaţii - UPDATE

• Valoarea cheii străine trebuie să se regăsească între valorile cheii primare;

• Nu putem modifica cheia primară dacă are cheie străină; prima dată modificăm sau ştergem cheia străină, apoi putem modifica cheia primară.

• Nu se poate şterge o linie care conţine o cheie primară, care etse folosită ca şi cheie străină în altă tabelă.

Comanda DELETE

• Se foloseşte pentru a şterge linii dintr-un tabel.

• Are următoarea sintaxă:

DELETE FROM <nume tabel>

WHERE <condiţia pentru liniile selectate>

Exemple DELETE

1. DELETE FROM copy_f_customers

WHERE ID= 123;

(şterge clienţii cu codul 123)

2. DELETE FROM copy_f_customers

(şterge toate rândurile)

ŞTERGERI CU SUBQUERY

ŞTERGERI FOLOSIND O ALTĂ

TABELĂ

Observaţii - DELETE

La ştergeri, trebuie să respectăm restricţii de implementare:

• R- restrictive – nu se poate şterge părintele dacă are fii

• C – on delete cascade – dacă şterg părintele, se şterg şi fii

• N – on delete null – dacă şterg părintele, cheia străină trece pe NULL

• D – default – dacă şterg părintele, fii trec pe un părinte implicit

OBSERVAŢII - DELETE

• După ştergerea liniilor cu DELETE, rămâne doar

structura tabelei(numele coloanelor, cheile

străine, cheile primare şi tipurile de dată).

• Pentru a şterge totul, se foloseşte DROP TABLE.

ERORI – CONSTRÂNGERI DE

INTEGRITATE

- este violată constrângerea NOT NULL pentru

first_name, deoarece nu se găseşte id-ul 23 şi deci

subquery returnează NULL

Cazuri de violare a constrângerilor

foreign-key

• Ştergerea unei linii cheie primară

• Inserarea unei linii cheie străină

• Actualizarea unei coloane cheie primară din

tabela primară

• Actualizarea unei coloane cheie străină din tabela

străină

Observaţie

• La crearea unei copii a unei tabele cu

CREATE TABLE….AS(SELECT…)

se vor copia liniile şi constrângerile NOT NULL,

dar NU se vor copia constrângerile cheie primară-

cheie străină.

VALORI DEFAULT

• Previn setarea valorilor NULL pentru coloane, dacă o linie este inserată fără specificarea explicită a unei valori pentru coloană

• Poate fi un literal, o expresie, o funcţie SQL(SYSDATE, USER), dar nu poate fi numele ueni alte coloane

• Valoarea explicită trebuie să aibă acelaşi tip cu cel al coloanei

• Putem specifica DEFAULT pentru o coloană, atunci când tabela este creată

Comanda MERGE

• Se foloseşte la tabele de dimensiuni foarte mari.

• Permite scanarea tabelului sursă, iar în tabelul

destinaţie face ori insert, ori update. Dacă

înregistrarea există, face update, dacă nu există, o

inserează.

• Se pot folosi aliasuri

Sintaxa comenzii MERGE MERGE INTO <tabel destinaţie> USING <tabel sursă sau subquery>

ON <condiţie>

WHEN MATCHED THEN UPDATE SET

<coloana destinaţie 1> = <coloana sursă | expresie>, <coloana destinaţie 2> = <coloana sursă | expresie>,

<coloana destinaţie n> = <coloana sursă | expresie>

WHEN NOT MATCHED THEN INSERT

(<coloană destinaţie 1 | expresie>, < coloană destinaţie 2 | expresie >, < coloană destinaţie n | expresie >)

VALUES

(<valoare 1>, <valoare 2>, <valoare n>)

Efectul

• Se citeşte o linie din sursă, ale cărei valori se compară cu

liniile destinaţiei, pe baza condiţiei.

• Dacă în destinaţie găsim o linie care se potriveşte, valorile

ei se vor modifica cu linia din sursă.

• Dacă nu se găseşte nici o linie, în destinaţie se va insera

linia din sursă.

• Bibliografie

https://academy.oracle.com