În acest laborator vom învăța numeroase noțiuni de SQL Server 2019 precum:

• DML și DDL
• Cursori
• SQL Dinamic
• Funcții, Proceduri Stocate și DML TRIGGER

1. Dorința de a învăța
2. Executarea scriptului pentru crearea tabelelor necesare
3. Noțiuni de bază despre SQL
4. Citirea scriptului de crearea a bazelor de date

Precum majoritatea limbajelor de tip SQL, T-SQL oferă instrucțiuni precum:

• INSERT - DML
• UPDATE - DML
• DELETE - DML
• TRUNCATE - DDL
• MERGE - DML

Poate fi folosită atât în modul clasic unde inserăm un set de valori definite de noi, cât și care rezultat al unei clauze. INSERT INTO va încerca să introducă datele într-o tabelă deja existentăm, în timp ce SELECT … INTO va crea o nouă tabelă, ceea ce funcționează maxim o singură dată fără eroare.

-- EXEMPLU CLASIC
-- În ambele cazuri trebuie să avem grijă la constrângeri

-- Exemplu clasic, valorile sunt inserate în ordinea de creare a coloanelor
INSERT INTO [dbo].[employees] -- Dacă omitem coloanele, trebuie să respectăm tipul de date
VALUES (
	3 -- Valoare coloana 1
	,'NEW' -- Valoare coloana 2
	,'EMPLOYEE' -- Valoare coloana 3
	,'newemployee@exhample.com' -- Valoare coloana 4
	,'+40712345679' -- Valoare coloana 5
	,'2020-11-22' -- Valoare coloana 6
	,'FI_MGR' -- Valoare coloana 7
	,15002 -- Valoare coloana 8
	,NULL -- Valoare coloana 9
	,NULL -- Valoare coloana 10
	,100 -- Valoare coloana 11
	)
GO

-- Când specificăm numele coloanei, ordinea o alegem noi
INSERT INTO [dbo].[employees]
           ([employee_id]
           ,[first_name]
           ,[last_name]
           ,[email]
           ,[phone_number]
           ,[hire_date]
           ,[job_id]
           ,[salary]
           ,[commission_pct]
           ,[manager_id]
           ,[department_id]) -- Putem să precizăm una sau mai multe coloane
VALUES (
	2 -- Valoare coloana 1
	,'NEW' -- Valoare coloana 2
	,'EMPLOYEE' -- Valoare coloana 3
	,'new.employee@exhample.com' -- Valoare coloana 4
	,'+40712345678' -- Valoare coloana 5
	,'2020-11-22' -- Valoare coloana 6
	,'FI_MGR' -- Valoare coloana 7
	,15000 -- Valoare coloana 8
	,NULL -- Valoare coloana 9
	,NULL -- Valoare coloana 10
	,100 -- Valoare coloana 11
	)
GO

Ca să nu stricăm și mai tare structura bazei de date, ne vom clona tabela [dbo].[EMPLOYEE] folosing codul de mai jos.

-- Tabela clonă va avea coloane care au numele și tipul de date al rezultatului
-- Toate constrângerile și toți indecșii se pierd (nu se știe de existența lor)
SELECT *
INTO [dbo].[EMPLOYEE_CLONE]
FROM [dbo].[EMPLOYEES]

Dacă dorim să repopulăm tabela clonă cu datele noi din cea originală am putea folosi o instrucțiune de tipul celei de mai joi.

INSERT INTO [dbo].[EMPLOYEE_CLONE]
SELECT *
FROM [dbo].[EMPLOYEES]

Ex. 1 Corectați clauza astfel încât să insereze doare ce este nou, din punct de vedere al [EMPLOYEE_ID].

Această instrucțiune modifică datele dintr-o tabelă deja existentă, respectând constrângerile. Acceptă și JOIN-uri pentru satisfacerea unor condiții mai complicate.

Ex. 2 Să se scrie o cerere care mărește salariul angajaților (din tabela [dbo].[EMPLOYEE]) cu 15% doar dacă sunt într-un departament ce conține un număr par de angajați .

Ca și în alte limbaje, este o instrucțiune folosită pentru a șterge date dintr-o tabelă. Acceptă și JOIN-uri pentru satisfacerea unor condiții mai complicate. Este destul de important să avem condiție când folosim instrucțiunea DELETE pentru că altfel obținem un TRUNCATE suboptim care doar poate fi executat pe tabele ce nu au chei primare.

Ex. 3 Să se scrie o cerere care șterge angajații (din tabela [dbo].[EMPLOYEE_CLONE]) care au departamentul situat în “US”.

Această instrucțiune este un DELETE total și mult mai rapid. Dezavantajul este că nu poate să șteargă tabele unde există chei primare.

TRUNCATE TABLE <NAME>

Ex. 4 Clonați tabela [dbo].[DEPARTMENT] doar pentru departamentele care au litera E în numele lor. Folosiți instrucțiunea TRUNCATE pe tabela rezultată.

Este o instrucțiune foarte interesantă deoarece, în funcție de condiția de potrivire, în cazul în care există rânduri ce pot fi legate se poate face UPDATE pe acestea sau DELETE, iar în caz contrar se poate face INSERT.

Dorim să aducem datele noi în tabela clonă, [dbo].[EMPLOYEE_CLONE], iar în cazul în care angajatul deja există îi da un super bonus salarial, mai exact salariul pe care îl are în tabela [dbo].[EMPLOYEE].

MERGE INTO [dbo].[EMPLOYEE_CLONE] AS [Target  ]
USING [dbo].[employees] AS [Source]
	ON Target.[employee_id] = [Source].[employee_id]
WHEN MATCHED
	THEN
		UPDATE
		SET [Target].[salary] = [Source].[SALARY]
WHEN NOT MATCHED
	THEN
		INSERT
		VALUES ([employee_id]
           ,[first_name]
           ,[last_name]
           ,[email]
           ,[phone_number]
           ,[hire_date]
           ,[job_id]
           ,[salary]
           ,[commission_pct]
           ,[manager_id]
           ,[department_id]);
-- Din păcate este nevoie să precizăm toate coloanele/valorile pentru INSERT

Ex. 5 Dorim să aducem datele modificate înapoi în tabela [dbo].[EMPLOYEE] (doar salariul și bonusul), folosind MERGE clonă, din tabela [dbo].[EMPLOYEE_CLONE], doar pentru angajații ce au (în tabela [dbo].[EMPLOYEE]) un [JOB] ce conține cel puțin un A, iar în caz contrar să nu facem nimic.

Cursorul este răspunsul la întrebarea “Există instruțiuni repeptitive în limbajul SQL?”, el fiind un “WHILE”. Cum ne aflăm într-o paradigmă centrată pe date, cursorii se folosesc niște tabele temporare stocate în memorie și, din motive de utilizare optimă a resurselor, este foarte important să închidem și dealocăm orice cursor pe care îl creăm.

SQL-ul dinamic este o metodă prin care putem să scriem ceva generic, folosindu-ne de variabile de tip NVARCHAR. Codul rezultat trebuie să respected toate regulile unui SQL clasic, dar avantajul este că poate fi scris în mod iterativ și folosind variabile. Un dezavantaj al acestei metode este performanța.

Exemplu: Ne dorim să facem curățenie în baza de date, adică să scăpăm de clone. Pentru asta vom scrie un SQL Dinamic, folosind un cursor, și vom aplica comanda TRUNCATE tuturor tabelelor clonă.

Ex. 6 Ne dăm seama că nu este suficient să ștergem tabelele, dorim și să scăpăm de ele. Scrieți o clauză care face asta. (folosiți DROP)

DECLARE @SQL NVARCHAR(500)
 
DECLARE @Cursor CURSOR
 
SET @Cursor = CURSOR FAST_FORWARD FOR SELECT  'DROP TABLE [' + TABLE_SCHEMA + '].[' +  TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%CLONE'
 
 
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @SQL
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
print(@SQL)
EXEC sp_executesql @SQL
FETCH NEXT FROM @Cursor INTO @SQL
END
 
CLOSE @Cursor 
 
DEALLOCATE @Cursor
 
GO

Ca și în alte limbaje de tip SQL, avem funcții și proceduri stocate. Funcțiile sunt READ-ONLY, nu au voie să modifice date, iar procedurile stocate au drepturi depline în ceea ce privește accesul la date. În T-SQL nu avem blocuri speciale pentru a declara variabile, așa că putem declara orice oriunde, singura restricție fiind vizibilitatea, ceea ce înseamnă că pentru o fucție sau o procedură vom încerca să declarăm mediul de lucrul la început.

În SQL Server, începând cu 2016, avem disponibilă sintaxa CREATE OR ALTER care oferă posibilitatea de a crea sau modifica, în caz că deja există, entitatea la care se face referință. Funcționează pentru:

• Funcții
• Proceduri Stocate
• TRIGGER-e
• VIEW-uri

Funcțiile pot să primească sau nu parametrii care pot avea valori implicite (constante). Trebuie să precizăm tipul valorii returnate de funcție și este indicat să să folosim variablile și să delimităm instrucțiunile cu BEGIN și END.

Vom crea o funcție care care primește ca parametru ID-ul unui angajat și un salariu (parametru opțional care are valoarea default egală cu salariul angatului respectiv) și întoarce numărul angajaților subordonați lui, care au salariul mai mic sau egal cu salariul dat:

Ex. 7 Scrieți o funcție care întoarce salariul unui angajat și modificați funcția de mai sus.

Procedurile stocate sunt un mod optim de a păstra un cod care va fi executat de mai multe ori, acestea fiind și optimizate în spate, în funcție de planul lor de execuție. Procedurile stocate conferă și proprietatea de “black-box” deoarece putem să oferim drepturi doar de execuție pentru a anonimiza baza nostră de date și de oferi acces granular.

Pentru proceduri stocate avem ca unele auxiliare blocuri de tipul BEGIN TRY \ END TRY și BEGIN CATCH \ END CATCH care ne vor proteja de excepții la care ne așteptăm în timpul execuției, ca de exemplu existența unei tabele care este ștearsă destul de frecvent.

Vom crea o procedură numită [dbo].[DEPT_MGR_INFO] care are ca input id-ul unui departament și ca output numele departamentului și inserează într-o tabelă istorică urmatoarele:

• ID-ul fiecărui manager care lucrează în acel departament (manager ⇒ are cel

puțin un subaltern)

• Numele managerului
• Numărul angajaților subordonați lui, care au salariul mai mic sau egal ca el

(obținut prin intermediul funcției [dbo].[GET_SUBORDINATES])

• Data executiei

Ex. 8 De ce credeți că este nevoie de SQL Dinamic?

Sunt o metodă de a executa anumite instrucțiuni în momentul în care este accesată o tabelă cu una din următarele instrucțiuni DML:

• INSERT, iar datele noi pot fi referite cu INSERTED
• DELETE, iar datele șterse pot fi referite cu DELETED
• UPDATE, iar datele noi pot fi referite cu INSERTED și cele șterse pot fi referite cu DELETED

SQL Server permite două tipuri de TRIGGER:

• AFTER, se execută după operația propriu-zisă și schimbările devin permanente doar dacă ambele au reușit
• INSTEAD OF, se execută în locul operației

Dorim să ne folosim de procedura creată anterior așa că o vom atașa de tabela [dbo].[EMPLOYEE].

CREATE TRIGGER [dbo].[INSERARE_ISTORIC_E] ON [dbo].[EMPLOYEE]
AFTER INSERT, UPDATE, DELETE
AS
EXEC [DEPT_MGR_INFO];
GO

Ex. 9 Are sens să o mai atașăm și de altă tabelă?