database programming

DATABASE DATABASE PROGRAMMINGPROGRAMMING

DATABASE DATABASE PROGRAMMINGPROGRAMMING

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).

ID F_NAME L_NAME SALARY HIRE_DATEDEPT_ID

63 Steven King 24000 17-Jun-87 90

22 Lex De Haan 17000 13-Jan-93 90

32 Alex 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 Vargas 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);

Următoarea comandă INSERT generează eroare. De ce?

INSERT INTO customers (client_number, first_name, last_name, email)VALUES( 654,‘David’, ‘Pop’, ‘[email protected]’);

Primary Key

Name Type Length Precision Scale Nullable Default Comments

1 CLIENT_NUMBER Number 5 0

FIRST_NAME Varchar2 25

LAST_NAME Varchar2 30

PHONE Number 15 0

EMAIL Varchar2 50

Structura tabelului Customers

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.

ID F_NAME L_NAME SALARY HIRE_DATEDEPT_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 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 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.

Ce este greşit în următoarea comandă INSERT?

INSERT INTO d_packagesVALUES( 2, 56,1000);

Primary Key

Name Type Length Precision Scale

1 CODE Number 10 0

LOW_RANGE Number 3 0

HIGH_RANGE Number 3 0

Structura tabelului D_Packages

Deoarece câmpul high_range permite întroducerea unei valori numerice de maxim 3 cifre, 1000 reprezintă o valoare prea mare pentru această coloană.

Dorim să-l adăugăm pe David în tabelul Customers. El nu are adresă de e-mail. Care este cauza erorii?

INSERT INTO customersVALUES( 654,‘David’, ‘Pop’, 1234567891452);

Primary Key

Name Type Length Precision Scale Nullable Default Comments

1 CLIENT_NUMBER Number 5 0

FIRST_NAME Varchar2 25

LAST_NAME Varchar2 30

PHONE Number 15 0

EMAIL Varchar2 50

Structura tabelului Customers

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 DELETE1. 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 incorectFormatul 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>)sauCREATE TABLE <nume tabel> ASSELECT <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 *

ALL *

USER *

Toate obiectele din BDToate obiectele din schema proprie şi acele obiecte din alte scheme pt. care am privilegii de accesTables, 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);

FIRST_NAME LAST_NAME SALARY HIRE_DATE JOB_IDDEPARTMENT_ID

Steven King 24000 17.iun.87 AD_PRES 90Lex De Haan 17000 13.ian.93 AD_VP 90Alexander Hunold 9000 03.ian.90 IT_PROG 60Bruce Ernst 6000 21.mai.91 IT_PROG 60Diana Lorentz 4200 07.feb.99 IT_PROG 60Kevin Mourgos 5800 16.nov.99 ST_MAN 50Randall Matos 2600 15.mar.98 ST_CLERK 50Peter Vargas 2500 09.iul.98 ST_CLERK 50Eleni Zlotkey 10500 29.ian.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.



Inseraţi coloana phone_number în tabelul employees.

EXERCIŢIU

REZOLVARE

ALTER TABLE employees

ADD (phone_number NUMBER(20));



Scrieţi o comandă care modifică structura coloanei salary, astfel încât aceasta să poate primi valori formate din 6 cifre.

EXERCIŢIU

REZOLVARE


MODIFY (salary number(6));

Scrieţi o comandă care şterge coloana job_id din tabelul employees.



EXERCIŢIU

REZOLVARE


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.

database programming

Documents