În acest laborator vom învăța numeroase noțiuni de SQL Server 2019 precum:
Precum majoritatea limbajelor de tip SQL, T-SQL oferă instrucțiuni precum:
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ț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:
puțin un subaltern)
(obținut prin intermediul funcției [dbo].[GET_SUBORDINATES])
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:
SQL Server permite două tipuri de TRIGGER:
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ă?