This shows you the differences between two versions of the page.
bd2:laboratoare:08 [2020/11/22 16:13] apetrescu0506 |
— (current) | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Laboratorul 08 - SQLServer: DML, Cursori si Programabilitate ====== | ||
- | |||
- | ==== Conținut ==== | ||
- | În acest laborator vom învăța numeroase noțiuni de SQL Server 2017 precum: | ||
- | |||
- | * DML | ||
- | * Cursori | ||
- | * SQL Dinamic | ||
- | * Funcții, Proceduri Stocate și DML TRIGGER | ||
- | |||
- | <note> | ||
- | Încercați să rulație fiecare instrucțiune din laborator chiar dacă nu vă este cerut în mod explicit acest lucru pentru a vă ajuta să înțelegeți. | ||
- | </note> | ||
- | |||
- | ===== Cerințe Preliminarii ===== | ||
- | |||
- | - Dorința de a învăța | ||
- | - Executarea scriptului pentru crearea tabelelor necesare | ||
- | - Noțiuni de bază despre SQL | ||
- | - Citirea scriptului de crearea a bazelor de date | ||
- | |||
- | ==== Data Manipulation Language (DML) ==== | ||
- | Precum majoritatea limbajelor de tip SQL, T-SQL oferă instrucțiuni precum: | ||
- | * **INSERT** | ||
- | * **UPDATE** | ||
- | * **DELETE** | ||
- | * **TRUNCATE** | ||
- | * **MERGE** | ||
- | |||
- | === Instrucțiunea INSERT === | ||
- | 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. | ||
- | <code> | ||
- | -- 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 | ||
- | </code> | ||
- | |||
- | Ca să nu stricăm și mai tare structura bazei de date, ne vom clona tabela **[dbo].[EMPLOYEE]** folosing codul de mai jos. | ||
- | |||
- | <code> | ||
- | -- 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].[EMPLOYEE] | ||
- | </code> | ||
- | |||
- | 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. | ||
- | <code> | ||
- | INSERT INTO [dbo].[EMPLOYEE_CLONE] | ||
- | SELECT * | ||
- | FROM [dbo].[EMPLOYEE] | ||
- | </code> | ||
- | <color red>Ex. 1</color> Corectați clauza astfel încât să insereze doare ce este nou, din punct de vedere al **[EMPLOYEE_ID]**. | ||
- | |||
- | |||
- | === Instrucțiunea UPDATE === | ||
- | 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. | ||
- | |||
- | {{:bd2:laboratoare:sqlserverlab02ex020.png?nolink&740|}} | ||
- | |||
- | <color red>Ex. 2</color> 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 . | ||
- | |||
- | === Instrucțiunea DELETE === | ||
- | 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. | ||
- | |||
- | {{:bd2:laboratoare:sqlserverlab02ex030.png?nolink&740|}} | ||
- | |||
- | <color red>Ex. 3</color> Să se scrie o cerere care șterge angajații (din tabela **[dbo].[EMPLOYEE_CLONE]**) care au grila salarială un număr impar. | ||
- | |||
- | === Instrucțiunea TRUNCATE === | ||
- | Această instrucțiune este un **DELETE** total și mult mai rapid. Dezavantajul este că nu poate să șteargă tabele unde există chei primare. | ||
- | |||
- | <code> | ||
- | TRUNCATE TABLE <NAME> | ||
- | </code> | ||
- | |||
- | <color red>Ex. 4</color> Clonați tabela **[dbo].[DEPARTMENT]** doar pentru departamentele care au litera **E** în numele lor. Folosiți instrucțiunea **TRUNCATE** pe tabela rezultată. | ||
- | |||
- | === Instrucțiunea MERGE === | ||
- | 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]**. | ||
- | <code> | ||
- | MERGE INTO [dbo].[EMPLOYEE_CLONE] AS [Target ] | ||
- | USING [dbo].[EMPLOYEE] AS [Source] | ||
- | ON Target.[EMPLOYEE_ID] = [Source].[EMPLOYEE_ID] | ||
- | WHEN MATCHED | ||
- | THEN | ||
- | UPDATE | ||
- | SET [Target].[BENEFIT] = [Source].[SALARY] | ||
- | WHEN NOT MATCHED | ||
- | THEN | ||
- | INSERT | ||
- | VALUES ( | ||
- | [EMPLOYEE_ID] | ||
- | ,[EMPLOYEE_NAME] | ||
- | ,[JOB] | ||
- | ,[MGR] | ||
- | ,[HIREDATE] | ||
- | ,[SALARY] | ||
- | ,[BENEFIT] | ||
- | ,[DEPARTMENT_ID] | ||
- | ); | ||
- | -- Din păcate este nevoie să precizăm toate coloanele/valorile pentru INSERT | ||
- | </code> | ||
- | <color red>Ex. 5</color> 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. | ||
- | |||
- | |||
- | ==== Cursori + SQL Dinamic ==== | ||
- | 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ă. | ||
- | |||
- | {{:bd2:laboratoare:sqlserverlab02ex060.png?nolink&740|}} | ||
- | |||
- | <color red>Ex. 6</color> 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**) | ||
- | |||
- | <code sql> | ||
- | 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 | ||
- | </code> | ||
- | |||
- | ==== Funcții, Proceduri Stocate și DML TRIGGER ==== | ||
- | 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ții === | ||
- | 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: | ||
- | |||
- | {{:bd2:laboratoare:sqlserverlab02ex070.png?nolink&740|}} | ||
- | |||
- | <color red>Ex. 7</color> Scrieți o funcție care întoarce salariul unui angajat și modificați funcția de mai sus. | ||
- | |||
- | === Proceduri stocate === | ||
- | 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 | ||
- | * Grila salarială în care se încadrează managerul | ||
- | * 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 | ||
- | | ||
- | {{:bd2:laboratoare:sqlserverlab02ex090.png?nolink&740|}} | ||
- | |||
- | Întrebare 1: De ce credeți că este nevoie de SQL Dinamic? | ||
- | |||
- | === DML TRIGGER === | ||
- | 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]**. | ||
- | <code> | ||
- | CREATE TRIGGER [dbo].[INSERARE_ISTORIC_E] ON [dbo].[EMPLOYEE] | ||
- | AFTER INSERT, UPDATE, DELETE | ||
- | AS | ||
- | EXEC [DEPT_MGR_INFO]; | ||
- | GO | ||
- | </code> | ||
- | Întrebare 2: Are sens să o mai atașăm și de altă tabelă? | ||
- | |||