DDL - Data Definition Language. Instructiunile limbajului DDL sunt grupate in 3 parti. In prima grupa intra instructiunile pentru crearea obiectelor, in a doua grupa intra instructiunile pentru modificarea obiectelor si in a treia grupa intra instructiunile de stergere al obiectelor
1. Creare obiecte BD
- In organizarea BD intra o multime de obecte. toate aceste obiecte sunt ori fizice ori logice. Obiectele fizice sunt fisierele si grupurile de fisiere. Obectele logice sunt reprezentarile BD (tabele, coloane, viziuni)
- Primul obiect care trebuie creat este insasi BD. Database Engine administreaza atit BD din sistem cit si cele create de utilizator.. BD utilizator pot fi create de utilizatorii autorizati, iar BD de sistem se creaza in momentul instalarii sistemului BD
2. Creare BD
CREATE DATABASE db_name
[ON [PRIMARY] {file_spec1},.]
[LOG ON {file_spec2},.]
[COLLATE collation_name]
[FOR {ATTACH ATTACH_REBUILD_LOG}]
Parametrul db_name - este numele BD (max.128 simb.). Toate BD se pastreaza in fisiere, care pot fi indicate direct de catre administrator sau indirect de sistema. Daca instructiune CREATE DATABASE contine parametrul ON, toate fisierele se indica direct. Parametrul file_spec1 reprezinta specificatia fisierului si poate contine optiuni adaugatoare precu: numele logic, numele fizic si marimea. Parametrul PRIMARY lipseste, adica in calitate de fisier initial se ia fisierul indicat in specificatie. Optiunea LOG ON al parametrului dbo determina unul sau mai multe fisiere in care se pastreaza tranzactiile BD. Optiunea COLATE indica ordinea de sortare. Optiunea FOR ATTACH indica ca BD se creaza din contul includerii fisierelor existente.
EX. Creare BD simpla
USE master;
CREATE DATABASE sample;
3. Creare copie BD
EX. Creare copie a BD Adventure Works
USE master;
CREATE DATABASE AdventurWorks_snapshot
ON (NAME = 'AdventureWorks_Date' ,
FILENAME = 'C:\temp\snapshot_DB.mdf')
AS SNAPSHOT OF AdventureWorks;
- Copia unei baze de date existente se poate doar de citit
4. Unire si dezunire BD
- Toate datele se pot dezuni, si pe urma din nou de unit la acest sau la alt server BD. Aceasta functie se foloseste la schimbarea locatiei BD
- Pentru dezunire se foloseste procedura de sistem - sp_detach_db.
- Pentru unire se foloseste instructiunea CREATE DATABASEI cu propunerea FOR ATTACH.
5. Instructiunea CREATE TABLE: forma de baza
- Creaza un tabel nou cu toate coloanele si tipurile de date
EX. Creare tabel
USE sample;
CREATE TABLE employee2 (emp_no INTEGER NOT NULL,
emp_fname CHAR(20) NOT NULL,
emp_lname CHAR(20) NOT NULL,
dept_no CHAR(4) NULL,
project_no CHAR(4) NOT NULL);
6. Instructiunea CREATE TABLE si limitarile
- Limitarile care se folosesc pentru verificarea datelor cind acestea sunt modificate sunt numite Limite pentru pastrarea integritatii.
- Prioritati in folosirea limitarilor
- siguranta datelor
- se reduce timpul pentru programare
- simplifica deservirea tehnica
- Sistemul de control al BD propune doua tipuri de limitari pentru pastrarea integritatii
- declarative (CREATE TABLE , ALTER TABLE). Se pot grupa
- DEFAULT
- UNIQUIE
- PRIMARY KEY
- CHECK
- FOREIGN KEY
- procedurale
7. Operatorul PRIMARY KEY
- Cheia primara al tabelului este coloana sau grupa de coloane, valoarea corora este diferita in fiecare rind
- Nu permite valori NULL
- Are valoarea by default CLUSTERED
EX.
USE sample;
DROP TABLE employee2
CREATE TABLE employee2 (emp_no INTEGER NOT NULL,
emp_fname CHAR(20) NOT NULL,
emp_lname CHAR(20) NOT NULL,
dept_no CHAR(4) NULL,
project_no CHAR(4) NOT NULL,
CONSTRAINT prim_empl PRIMARY KEY (emp_no));
8. Operatorul FOREIGN KEY
- Cheia externa este acea coloana sau grupe de coloane, care contin valori ce sunt identice cu valoarea cheii primare dinacelasi tabel sau din alte tabele.
- Se foloseste in combinatie cu REGERENCES - se indica numele tabelului
- FOREIGN KEY determina toate coloanele care intra in cheia externa
EX.
USE sample;
DROP TABLE works_on
CREATE TABLE works_on (emp_no INTEGER NOT NULL,
project_no CHAR(4) NOT NULL,
Job CHAR(15) NULL,
enter_date DATE NULL,
CONSTRAINT prim_works PRIMARY KEY (emp_no, project_no),
CONSTRAINT foreign_works FOREIGN KEY(emp_no)
REFERENCES employee (emp_no));
9. Operatorul UNIQUE
- Uneori citeva coloane sau grupuri de coloane au valori unice, ceia ce permite sa le folosim in calitate de cheie primara. Aceste coloane se numesc chei canditate
- Fiecare cheie candidat se determina folosind operatorul UNIQUE in instructiunea CREATE TABLE sau ALTER TABLE.
- Optiunea CONSTRAINT in operatorul UNIQUE atribuie valoare concreta cheii canditate.
EX.
USE sample;
DROP TABLE project
CREATE TABLE project (project_no CHAR(4) DEFAULT 'p1',
project_name CHAR(15) NOT NULL,
budget FLOAT NULL,
CONSTRAINT uniquie_no UNIQUE (project_no));
10. Operatorul CHECK
- Verifica cinditia pentru datele introduse in tabel.
USE sample;
CREATE TABLE customer
(cust_no INTEGER NOT NULL,
cust_group CHAR(3) NULL,
CHECK (cust_group IN ('c1', ' c2 ', 'c10')));
11. Operatorul DEFAULT
- introduce in tabel valoare, daca aceasta nu a fost introdusa de utilizator
12. Introducere tupluri
EX.
USE sample;
DROP TABLE customer;
CREATE TABLE customer
(cust_no INTEGER NOT NULL,
cust_group CHAR(20) NOT NULL);
INSERT INTO customer VALUES (1, 'Group Nr.1');
INSERT INTO customer VALUES (2, 'Group Nr.2');
INSERT INTO customer VALUES (2, 'Group Nr.3');
SELECT * FROM customer;
13. Instructiunea ALTER TABLE
- Se foloseste pentru a modifica tabelele
- Tipuri de modificari
- Adaugare si stergere coloane
- schimba proprierati coloane
- aduga si sterge limitari pentru asigurarea integritatii
- permite sau nu permite limitarile
- redenumire tabele si alte obiecte
EX. Adaugare coloana
USE sample;
ALTER TABLE employee
ADD telephone_no CHAR(12) NULL;
EX. Stergere coloana
USE sample;
ALTER TABLE employee
DROP COLUMN telephone_no;
EX. Schimbare proprietati colloana
USE sample;
ALTER TABLE departament
ALTER COLUMN location CHAR(25) NOT NULL;
EX. Adaugare limitare
USE sample;
CREATE TABLE sales
(order_no INTEGER NOT NULL,
order_date DATE NOT NULL,
ship_date DATE NOT NULL);
ALTER TABLE sales
ADD CONSTRAINT order_check CHECK (order_date <= ship_date);
EX. Determinare cheie primara
USE sample;
ALTER TABLE sales
ADD CONSTRAINT primary_sales PRIMARY KEY (order_no);
EX. Stergere limitare
USE sample;
ALTER TABLE sales
DROP CONSTRAINT order_check;
EX. Intrerupere limitare
USE sample;
ALTER TABLE sales
NOCHECK CONSTRAINT ALL;
EX. Redenunmire tabel
USE sample;
EXEC sp_rename @objname = departament, @newname = subdivision
EX. Redenunmire coloana
USE sample;
EXEC sp_rename @objname = 'sales.order_no', @newname = ordernumber
13. Instructiunea AS
EX.
USE sample;
CREATE TABLE orders
(
orderid INT NOT NULL,
price MONEY NOT NULL,
quantity INT NOT NULL,
orderdate DATETIME NOT NULL,
total AS price * quantity,
shippeddate AS DATEADD (DAY, 7, orderdate)
);
EX.Introducere tuplu valoarea caruia se calculează automat
USE sample;
DROP TABLE orders;
CREATE TABLE orders
(
price INT NOT NULL,
quantity INT NOT NULL
);
INSERT INTO orders(price, quantity) VALUES (50,530);
ALTER TABLE orders
ADD total AS price * quantity
SELECT * FROM orders;
EX.Introducere tuplu valoarea căruia este NOT NULL
USE sample;
DROP TABLE orders;
CREATE TABLE orders
(
price INT NOT NULL,
quantity INT NOT NULL
);
INSERT INTO orders(price, quantity) VALUES (50,530);
ALTER TABLE orders
ADD total AS price * quantity
ALTER TABLE orders
ADD pierderi INT NOT NULL DEFAULT 0
SELECT * FROM orders;
EX.Instrucțiunea DROP TABLE – șterge tabelul orders
USE sample;
DROP TABLE orders;
SELECT * FROM orders;
14. Afisarea variabilelor globale
EX. Afișarea variabilelor globale
USE sample;
SELECT @@CONNECTIONS, @@CPU_BUSY, @@LANGUAGE;
15. Tratarea exceptiilor cu instructiile TRY, CATCH si THROW
- Exceptiile sunt problemele (de obiecei erorile), care nu permite programei sa continuie. Acest lucru se intimpla din lipsa de informatie suficienta si de aceea controlul se transmite altei parti al programului,
- Rolul instructiunii TRY este de a intercepta exceptia. Daca in blocul TRY apare exceptia, componentul sistemei care prelucreaza exceptiile, transmite aceasta exceptie altei parti ale rogramului. Aceata parte a programului se numeste CATH.
- Prelucrarea erorilor cu blocul TRY..CATCH ofera prioritatile:
- reprezinta un mijloc simplu de determinare a erorilor
- prezinta mecanizmul de indicare directa a erorii
- programatorul poate vedea exceptia si sa o verifice in procesul de compilare
- In SQL Server 2012 este inclus inca oinstructiune TROW care are legatura cu tratarea erorilor. Aceasta instructiune permite de apelat exceptia care se determina in blocul de tratare a erorilor
16. Functiile determinate de utilizator
- Pachete (batch) - secvenat de instructiuni si proceduri
- Subprograma (routine) - functie determinata de utilizator (UDF)
17. Extensii ale procedurilor
- Instructiunile DDL si DML pot fi grupate in pachete
- Daca pachetul contine instructiunile: CREATE VIEW, CREATE PROCEDURE sau CREATE TRIGER, atunci aceste nu poate avea alte instructiuni
- Instructiunile de caracterizare a datelor se separa cu cuvintul cheie GO
18. Blocuri de instructiuni
- Poate contine una sau mai multe instructiuni T-SQL
- Fiecare bloc incepe cu instructiunea BEGIN si se termina cu instructiunea END
- Blocul se poate de introdus in instructiunea IF, pentru ca in dependenta de conditia determinata sa se permita executartea uneia sau a mai multor instructiuni
19. Instructiunea IF
- Executa unul sau mai multe blocuri de instructiuni daca IF returneaza true
- Daca returneaza false se executa a diua grupa de instructiuni
EX.
USE sample;
IF (SELECT COUNT(*)
FROM works_on
WHERE project_no = 'p1'
GROUP BY project_no ) > 3
PRINT 'Numarul de angajati la proiectul p1 este 4 sau mai mult'
ELSE BEGIN
PRINT 'Urmatorii angajati lucreaza la proiectul p1'
SELECT emp_fname, emp_lname
FROM employee, works_on
WHERE employee.emp_no = works_on.emp_no
AND project_no = 'p1'
END
20. Instructiunea WHILE
- Executa una sau mai multe instructiuni dintr-un bloc, atita timp cit WHILE returneaza true
- Blocul din WHILE poate contine una sau doua instructiuni: BREAK sau CONTINUE
EX.
USE sample;
WHILE (SELECT SUM(vinzari)
FROM project) < 500000
BEGIN
UPDATE project SET vinzari = vinzari * 1.1
IF (SELECT MAX (vinzari)
FROM project) > 240000
BREAK
ELSE CONTINUE
END
21. Variabile locale
- Sunt extensii ale procedurilor importante
- Se folosesc pentru pastrarea oricarui tip in pachete si subprograme
- Se numes locale deoarece pot fi folosite doar in pachetul in care au fost declarate
- Toate variabilele locale din pachet se declara folosind instructiunea DECLARE
- Determinarea variabilei contine numele si tipul de date
- Numele variabilei locale intotdeauna se incepe cu @
- Atribuire de valori se face:
- folosind o forma speciala a instructiunii SELECT
- folosnd instructiunea SET
- In instructiunea DECLARE prin intermediul = (ex. @extra_budget MONEY = 1500)
EX.
USE sample;
DECLARE @avg_budget MONEY, @extra_budget MONEY
SET @extra_budget = 15000
SELECT @avg_budget = AVG(budget) FROM project
IF(SELECT budget
FROM project
WHERE project_no = 'p1') < @avg_budget
BEGIN
UPDATE project
SET budget = budget + @extra_budget
WHERE project_no = 'p1'
PRINT 'Bugetul pentru p1 a crescut cu @extra_budget '
END
ELSE PRINT 'Bugetul pentru p1 nu a fost schimbat'
22. Instructiuni procedurale adaugatoare
- RETURN
- GOTO
- RAISEERROR (afiseaza mesaj despre eroare valoarea carora se pastraeaza in @@error)
- WAITFOR (determina perioada de timp sau(cu DELAY) sau timpul (cu TIME) pe parcursul careia sistema trebuie sa astepte pna a executa urmatoarea instructiune)
23. Procedurile stocate
- Sunt un tip specia de pachete ale T-SQL
- Diferenta intre procedturile stocate si pachete este aceia ca procedurile stocate se pastreaza sub forma de obiecte
- Procedurile stocate se creaza cu ajutorul DDL
- La crearea unei proceduri stocate se poate de stabilit lista de parametri
- Returneaza valoarea sau mesaj de eroare
- Preventiv se compileaza, apoise pastreaza ca obiect
- Se creaza pentru scopurile:
- Controlul autorizatiei de acces
- creare audit cu lucrul cu tabelele
24. Creare si folosire proceduri stocate
- Se creaza cu instructiunea CREATE PROCEDURE
EX.
USE sample;
GO
CREATE PROCEDURE increase_budget (@percent int = 5)
AS UPDATE project
SET budget = budget + budget*@percent/100;