Inserarea liniilor – Comanda
INSERT
Comanda INSERT permite inserarea unor linii
într-un tabel.
Este o comandă de tranzacţie, la fel ca şi Update,
Delete şi Merge. Aceste comenzi nu au
commit automat, se poate reveni cu o comandă
de tip rollback.
Continuare - INSERT
Sintaxa comenzii INSERT este:
INSERT INTO <nume tabel>(<coloana 1>, <coloana
2>, ....<coloana n>)
VALUES (<valoare 1>, <valoare 2>, ....<valoare n>)
Continuare - INSERT
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).
DEPT
ID F_NA ME L_NA ME SA LA RY HIRE_DA TE _ID
63 Steven King 24000 17-Jun-87 90
22 Lex De Haan 17000 13-Jan-93 90
32 A lex Hunold 9000 3-Jan-90 60
10 Bruce Ernst 6000 21-May-91 60
9 Diana Lorentz 4200 7-Feb-99 60
12 Kevin Mourgos 5800 16-Nov-99 50
5 Randall Matos 2600 15-Mar-98 50
8 Peter V argas 2500 9-Jul-98 50
66 Eleni Zlotkey 10500 29-Jan-00 80
Fie tabelul d emai sus. Să adăugăm un nou angajat în tabel. Numele lui este
David Pop, codul 20 iar salarul de 21000 $. El s-a angajat astăzi la
departamentul cu codul 90.
INSERT INTO employees
VALUES( 20, ‘David’, ‘Pop’, 21000,
SYSDATE, 90);
Structura tabelului Customers
Primary
Name Type Length Precision Scale Nullable Default Comments
Key
1 CLIENT_NUMBER Number 5 0
FIRST_NAME Varchar2 25
LAST_NAME Varchar2 30
PHONE Number 15 0
EMAIL Varchar2 50
Următoarea comandă INSERT generează eroare.
De ce?
INSERT INTO customers (client_number,
first_name, last_name, email)
VALUES( 654,‘David’, ‘Pop’, ‘david@[Link]’);
Eroarea a fost generată de faptul că deşi există câmpul phone
în structura tabelului, în comanda de inserare a unei
înregistrări (INSERT) lipseşte valoarea aferentă acestui câmp.
Din structura tabelului se observa cum coloana phone nu
poate avea valori nule, deci valoarea acestui câmp trebuie
inclusă în comanda INSERT.
DEPT
ID F_NAME L_NAME SALARY HIRE_DATE_ID
63 Steven King 24000 17-Jun-87 90
22 Lex De Haan 17000 13-Jan-93 90
32 Alexander Hunold 9000 3-Jan-90 60
10 Bruce Erns t 6000 21-May-91 60
9 Diana Lorentz 4200 7-Feb-99 60
12 Kevin Mourgos 5800 16-Nov-99 50
5 Randall Matos 2600 15-Mar-98 50
8 Peter Vargas 2500 9-Jul-98 50
66 Eleni Zlotkey 10500 29-Jan-00 80
Ca urmare, care va fi salariul lui David?
INSERT INTO employees (ID, f_name, l_name,
hire_date, dept_id)
Values( 20, ‘David’, ‘Pop’, SYSDATE, 90);
Deoarece în clauza VALUES din comanda
INSERT nu este precizată o valoare anume
pentru coloana salary, rezultă că salariul va
avea valoarea NULL.
Structura tabelului D_Packages
Prim ary
Nam e Type Length Precision Scale
Key
1 CODE Number 10 0
LOW_RANGE Number 3 0
HIGH_RANGE Number 3 0
Ce este greşit în următoarea comandă INSERT?
INSERT INTO d_packages
VALUES( 2, 56,1000);
Deoarece câmpul high_range permite
întroducerea unei valori numerice de maxim 3
cifre, 1000 reprezintă o valoare prea mare
pentru această coloană.
Structura tabelului Customers
Primary
Name Type Length Precision Scale Nullable Default Comments
Key
1 CLIENT_NUMBER Number 5 0
FIRST_NAME Varchar2 25
LAST_NAME Varchar2 30
PHONE Number 15 0
EMAIL Varchar2 50
Dorim să-l adăugăm pe David în tabelul Customers. El nu are
adresă de e-mail. Care este cauza erorii?
INSERT INTO customers
VALUES( 654,‘David’, ‘Pop’, 1234567891452);
Comanda INSERT este greşită deoarece, dacă nu există o valoare
precizată pentru coloana e-mail, în clauza VALUES trebuie
introdusă obligatoriu valoarea NULL.
Observaţie
Comanda INSERT se poate executa pe acelaşi
tabel (folosind subquery), dar se va executa la
infinit – se tot inserează valoarea returnată de
subquery în ea însăşi.
Comanda UPDATE
Comanda UPDATE este folosită pentru a
modifica o valoare într-un tabel.
Dacă în momentul creării unei coloane,
aceasta nu are definită o valoare implicită,
Oracle introduce valoarea NULL în coloana
respectivă.
Sintaxa comenzii UPDATE
UPDATE <tabel> SET <coloana> = <valoare
sau subquery> WHERE <condiţie pt. linia
selectată>
Observaţii - UPDATE
Valoarea cheii externe trebuie să se regăsească
între valorile cheii primare;
Nu putem modifica cheia primară dacă
acesteia îi corespunde o cheie externă; în acest
caz modificăm sau ştergem cheia externă,
după care putem modifica cheia primară.
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>
Observaţii - DELETE
La ştergeri, trebuie să respectăm restricţiile de implementare:
R- Restrictive – nu se poate şterge “părintele” dacă are
“copil”
C – on delete Cascade – dacă şterg “părintele” , se şterg şi
“copiii”
N – on delete Null – dacă şterg “părintele” , cheia externă
devine NULL
D – Default – dacă şterg “părintele” , “copiii” trec pe
nivelul “părintelui”
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)
Comanda MERGE
În general se foloseşte la tabele de dimensiuni
foarte mari.
Actualizează datele din tabelul sursă, care au
corespondent în tabelul destinaţie
( UPDATE ) şi inserează în tabelul sursă
datele noi ( INSERT ) .
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>)
TIPURI DE DATE SQL
Varchar2 – şir de caractere de lungime
variabilă de max. 4000 caractere; se
precizează obligatoriu lungimea şirului;
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 anului ş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 dimesiune 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 –Binary File - şir binar de 4 Giga, se
reţine doar directorul şi numele fişierului
Crearea unui tabel – comanda
CREATE TABLE
Comanda CREATE TABEL permite crearea unui nou
tabel în schema proprie.
Prin schema proprie înţelegem mulţimea tuturor
obiectelor ce aparţin utilizatorului curent, numit şi
proprietar.
Comanda 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:
Atât numele tabelului cât şi numele coloanelor
sunt identificatori.
Reamintim ca un identificator ORACLE
- poate conţine doar litere, cifre şi
caracterele _,$ şi #;
- începe obligatoriu cu o literă;
- nu poate depăşi 30 de caractere;
- nu poate fi un cuvânt rezervat Oracle.
Proprietarul are toate drepturile asupra
obiectelor pe care le-a creat.
Pentru crearea unui tabel în altă schemă, se
foloseşte comanda:
CREATE ANY TABLE
Tabelele utilizatorilor sunt înregistrate în
dicţionarul bazei de date (catalogul BD).
Acest dicţionar apare în momentul creării
bazei de date, acesta conţine şi tabelele
interne.
În dicţionarul BD se pot vedea toate tabelele
existente, dacă folosim comanda :
SELECT * FROM DICTIONARY
DBA * Toate
obiectele din
BD
Toate obiectele
ALL *
din schema
proprie şi acele
USER * obiecte din alte
scheme pt. care
am privilegii de
acces
Tables, views,
queries, etc….
Coloanele unui tabel 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).
Valorile implicite trebuie să fie compatibile cu
tipul datei din coloana respectivă.
O altă modalitate de a crea un tabel este:
CREATE TABLE <nume tabel> AS
SELECT <coloana 1>, <coloana 2>, <coloana n>
FROM <tabel>
EXERCIŢIU:
Scrieţi o comandă SQL care crează tabelul
“products table” cu următoarea structură:
product_id column,
product_name,
product_description columns,
purchase_date – valoarea implicită a datei
curente.
CREATE TABLE products
(product_id NUMBER(2),
product_name VARCHAR2(20),
product_description VARCHAR2(100),
purchase_date DATE DEFAULT
SYSDATE);
DEPART
FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_ID MENT_ID
Steven King 24000 [Link].87 AD_PRES 90
Lex De Haan 17000 [Link].93 AD_VP 90
Alexander Hunold 9000 [Link].90 IT_PROG 60
Bruce Ernst 6000 [Link].91 IT_PROG 60
Diana Lorentz 4200 [Link].99 IT_PROG 60
Kevin Mourgos 5800 [Link].99 ST_MAN 50
Randall Matos 2600 [Link].98 ST_CLERK 50
Peter Vargas 2500 [Link].98 ST_CLERK 50
Eleni Zlotkey 10500 [Link].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);
Întrebare:
Dacă creăm un nou tabel prin copiere
din alt tabel, ce nu se va transmite în
noul tabel?
Răspuns:
Restricţiile de integritate (constraints) din
tabelul original nu vor fi preluate de noul
tabel.
Modificarea structurii unui tabel
Inserarea / Ştergerea coloanelor
Pentru a insera, şterge sau modifica o coloană
a unui tabel, se foloseşte comanda ALTER
TABLE, 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>)
Modificarea unei coloane
Pentru a modifica o coloană, se foloseşte
comanda MODIFY care permite:
mărirea dimensiunii unei coloane de tip
numeric sau şir de caractere;
micşorarea dimensiunii unei coloane, dacă
aceasta conţine numai valori nule sau tabelul
nu conţine înregistrări (linii);
modificarea tipului de date a unei coloane.
(Atenţie! Se poate realiza doar dacă coloana
conţine numai valori nule).
convertirea tipului char în varchar2 sau
varchar2 în char.
Această conversie se face doar dacă:
- coloana respectivă conţine numai valori nule;
- nu se modifică dimensiunea coloanei.
O modificare a valorii implicite a unei coloane
va avea efect doar asupra datelor nou
introduse.
EXERCIŢIU
DEPART
FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_ID MENT_ID
Steven King 24000 [Link].87 AD_PRES 90
Lex De Haan 17000 [Link].93 AD_VP 90
Alexander Hunold 9000 [Link].90 IT_PROG 60
Bruce Ernst 6000 [Link].91 IT_PROG 60
Diana Lorentz 4200 [Link].99 IT_PROG 60
Kevin Mourgos 5800 [Link].99 ST_MAN 50
Randall Matos 2600 [Link].98 ST_CLERK 50
Peter Vargas 2500 [Link].98 ST_CLERK 50
Eleni Zlotkey 10500 [Link].00 SA_MAN 80
Inseraţi coloana phone_number în tabelul
employees.
REZOLVARE
ALTER TABLE employees
ADD (phone_number NUMBER(20));
EXERCIŢIU
DEPART
FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_ID MENT_ID
Steven King 24000 [Link].87 AD_PRES 90
Lex De Haan 17000 [Link].93 AD_VP 90
Alexander Hunold 9000 [Link].90 IT_PROG 60
Bruce Ernst 6000 [Link].91 IT_PROG 60
Diana Lorentz 4200 [Link].99 IT_PROG 60
Kevin Mourgos 5800 [Link].99 ST_MAN 50
Randall Matos 2600 [Link].98 ST_CLERK 50
Peter Vargas 2500 [Link].98 ST_CLERK 50
Eleni Zlotkey 10500 [Link].00 SA_MAN 80
Scrieţi o comandă care modifică structura coloanei
salary, astfel încât aceasta să poate primi valori
formate din 6 cifre.
REZOLVARE
ALTER TABLE employees
MODIFY (salary number(6));
EXERCIŢIU
DEPART
FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_ID MENT_ID
Steven King 24000 [Link].87 AD_PRES 90
Lex De Haan 17000 [Link].93 AD_VP 90
Alexander Hunold 9000 [Link].90 IT_PROG 60
Bruce Ernst 6000 [Link].91 IT_PROG 60
Diana Lorentz 4200 [Link].99 IT_PROG 60
Kevin Mourgos 5800 [Link].99 ST_MAN 50
Randall Matos 2600 [Link].98 ST_CLERK 50
Peter Vargas 2500 [Link].98 ST_CLERK 50
Eleni Zlotkey 10500 [Link].00 SA_MAN 80
Scrieţi o comandă care şterge coloana
job_id din tabelul employees.
REZOLVARE
ALTER TABLE employees
DROP COLUMN job_id;
Întrebare:
Ce comandă putem folosi când
dorim ştergerea tuturor liniilor
dintr-un tabel, păstrând
structura acestuia?
Răspuns: TRUNCATE TABLE.
Păstrează structura tabelului, ştergând toate
liniile şi eliberând spaţiul de memorie.
Întrebare:
Ce comandă putem folosi când
dorim ştergerea definitivă a
unui tabel?
Prin ştergere definitivă
înţelegem ştergerea tuturor
liniilor tabelului, precum şi a
structurii acestuia.
Răspuns: DROP TABLE.
Şterge tabelul cu structură cu tot.