Show page

Differences

This shows you the differences between two versions of the page.

--- bd2:laboratoare:07 [2019/11/10 17:24]
apetrescu0506
+++ bd2:laboratoare:07 [2020/11/19 15:40] (current)
ciprian.truica [Variabile]
@@ Line 1: / Line 1: @@
-====== Laboratorul 07 - PL/SQL Triggers ======
+====== Laboratorul 07 - SQL Server : Interogari, Variabile si XML ======
 ===== Conținut =====
-  * Triggeri PL/SQL
+În acest laborator vom învăța numeroase noțiuni de SQL Server 2017 precum:
-  * Crearea unui trigger
-  * Triggeri de tip BEFORE
-  * Triggeri de tip AFTER
-  * Restricții în clauza WHEN
-  * Predicate condiționale
-  * Triggeri cu opțiunea INSTEAD OF
-  * Informații din dicționarul bazei de date
+  * Structura unei interogări
+  * Variabile
+  * Lucrul cu date de tip XML
-==== Triggeri PL/SQL ====
+<note>
-Un trigger este un bloc PL/SQL stocat pe server care se execută la apariția unui eveniment care modifică starea anumitor obiecte ale bazei de date. Termenul corespondent în literatura de specialitate românească este declanșator, dar este rar folosit și de aceea în continuare se va folosi termenul în limba engleză. Tipuri de evenimente care pot determina execuția unui trigger sunt:
+Î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.
-  * Comenzi INSERT, UPDATE, DELETE pe o tabelă
+</note>
-  * Comenzi INSERT, UPDATE, DELETE pe un view cu opțiunea INSTEAD OF
-  * Comenzi CREATE, ALTER, DROP la nivel de schemă sau bază de date
-  * Comenzi SHUTDOWN, LOGON, LOGOFF la nivel de schemă sau bază de date
-În general, triggerii se folosesc pentru:
+===== Cerințe Preliminarii =====
-  * Gestionarea restricțiilor complexe de integritate
-  * Monitorizarea tranzacțiilor
-  * Efectuarea de replicări de tabele situate în diferite noduri ale unei baze de date distribuite
-  * Păstrarea semnăturii userilor care au efectuat operații pe baza de date
-  * Prelucrarea de informații statistice în legătură cu accesul tabelelor
-  * Jurnalizarea transparentă a evenimentelor
-Printre avantajele utilizării triggerilor, se pot menționa:
+  - Dorința de a învăța
-  * Declanșarea automată, la apariția evenimentului monitorizat
+  - Executarea scriptului pentru crearea tabelelor necesare
-  * Lansarea în execuție a unor proceduri stocate specifice
+  - Noțiuni de bază despre SQL
-  * Posibilitatea modificării în cascadă a mai multor obiecte corelate în baza de date
+  - Citirea scriptului de crearea a bazelor de date
-  * Transparența față de utilizator
-Sintaxa unui trigger este:
+===== Interogări =====
+Ca și în orice alt limbaj de tip SQL avem instrucțiunea **SELECT** care are numeroase posibilități pentru a obține sau formata date. Ordinea clauzelor este foarte importantă și este următoarea:
 <code sql>
-CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
+[WITH] --CTE
-	{BEFORE | AFTER | INSTEAD OF}
+SELECT
-	{DELETE | INSERT | UPDATE [OR {DELETE | INSERT | UPDATE }…]
+[INTO] -- Putem folosi rezultatul instruțiunii select pentru a popula o tabelă deja existentă
-		[OF column[, column …] ]}
+[FROM] -- Sursa sau sursele de date de date
-	ON [schema.]tabel _name
+[[[CROSS]|[INNER]|[LEFT|RIGHT|FULL OUTER]] JOIN] -- Putem folosi mai multe surse de date cu condiții speciale
-	[referencing_clauses]
+[WHERE] -- Condițiile de filtrare a datelor deja existente
-	[FOR EACH ROW]
+[GROUP BY] -- Agregarea datelor deja existente până în acest punct
-	[WHEN (condition) ]
+[HAVING] -- Condiții pentru noile grupări
-	DECLARE
+[ORDER BY] -- Ordonare, se pot folosi alias  * uri
-		trigger_variables
-	BEGIN
-		trigger_body
-	END
 </code>
+Tot ce ați văzut între **[paranteze pătrate]** poate să lipsească din interogare și clauza să fie validă, spre exemplu:
-Unde:
-  * trigger_name – numele triggerului PL/SQL
-  * schema – specifică schema pe care se definește triggerul sau în care există obiectele, în mod implicit este aleasă schema utilizatorului curent
-  * tabel _name – numele tabelul/view-ul pe care se monitorizează evenimentul
-  * column – numele coloanei (coloanelor) din tabelul/view-ul pe care se monitorizează evenimentul
-  * condition – reprezintă o condiție pentru  executarea triggerului, fiind admise corelări dar nu și interogări
-  * trigger_variables – secțiunea de declarare a variabilelor locale ale triggerului
-  * trigger_body – reprezintă corpul triggerului
-  * BEFORE | AFTER – specifică momentul executării triggerului: înainte sau după apariția evenimentului
-  * INSTEAD OF – specifică că este permisă o operație de inserare, ștergere, modificare pe view-uri, pentru care nu este permisă operația în mod firesc
-  * INSERT | UPDATE | DELETE – specifică evenimentul pe care se declanșează triggerul
-  * FOR EACH ROW – specifică dacă execuția triggerului se face pentru fiecare linie afectată, cu respectarea condiției din WHEN
-Sintaxa referencing_clauses:
 <code sql>
-REFERENCING {OLD [AS] old_variable NEW [AS] new_variable |
+SELECT  N'Aceasta este o clauză cât se poate de validă'
-			NEW [AS] new_variable OLD [AS] old_variable}
 </code>
-Se folosesc nume corelate pentru a specifica valorile noi și vechi ale rândului curent pentru care se declanșează triggerul. Aceste valori se pot folosi atât în clauza WHEN (old_variable.row_field, new_variable.row_field) cât și în blocul PL/SQL folosindu-se prefixate de două puncte : (de exemplu :old_variable.row_field, :new_variable.row_field).
+<color red>Ex. 1</color> Scrieți o clauză T-SQL care întoarce răspunsul la întrebarea: De ce am folosit litera **N** înainte șirul de caractere constant?
-<note>
+===== Comentarii =====
-Numele implicite sunt OLD și NEW.
+Ca în orice alt limbaj de programare avem dreptul de a folosi explicații pentru codul nostru. Se pot face în 2 moduri:
-</note>
+<code sql>
+-- Primul
+-- mod
+-- Aici trebuie să avem caractele speciale '--' pe fiecare rând.
-Sintaxa pentru crearea unui trigger de sistem este următoarea:
+/*
+Al
+doilea
+mod
-<code sql>
+Aici trebuie doar să deschidem comentariul înainte de primul rând
-	CREATE [OR REPLACE] TRIGGER [schema.]trigger_name
+și să-l închidem după ultimul.
-		{BEFORE | AFTER}
+Nu este nevoie să avem vreun string special pe fiecare rând.
-		{DDL_event_list | DB_event_list}
+*/
-		ON {DATABASE | SCHEMA}
-		[WHEN (condition) ]
-	DECLARE
-		trigger_variables
-	BEGIN
-		trigger_body
-	END
 </code>
-Unde:
+==== Coloane, TOP și ALIAS ====
-  * DDL_event_list - CREATE, DROP, ALTER
+Putem să avem oricâte coloane în clauza noastră și va trebui să le declarăm prin una din următoarele 2 metode:
-  * DB_event_list - STARTUP, SHUTDOWN, LOGON, LOGOFF, SERVERERROR, SUSPEND
-==== Crearea unui trigger ====
+  * Explicit: listă delimitată prin virgule
-Există două tipuri de triggeri:
+  * Implicit pe toate, folosind operatorul *****
-  * Triggeri pe o comandă – sunt executați o singură dată pentru evenimentul declanșator. De exemplu dacă se execută o comandă INSERT de mai multe linii, triggerul este executat o singură dată. În acest caz, nu este limitare la numărul de linii afectate de eveniment;
+Următoarele 2 clauze sunt echivalente (și generate de SQL Management Studio):
-  * Triggeri pe o linie – este executat ori de câte ori o linie a unei tabele este afectată de evenimentul declanșator. De exemplu, dacă se execută o comandă UPDATE care actualizează k linii, atunci triggerul este executat  de k ori.
-Un trigger poate fi executat înainte ca un eveniment să aibă loc (opțiunea BEFORE) sau după ce evenimentul s-a consumat (opțiunea AFTER). În general, triggerii de tip BEFORE sunt folosiți pentru:
+{{:bd2:laboratoare:sqlserverlab01ex02a.png?nolink&740|}}
-  * A salva valorile coloanelor înaintea executării unei comenzi UPDATE
-  * A decide dacă acțiunea triggerului trebuie sau nu executată (aceasta poate îmbunătăți performanțele serverului prin eliminarea procesării inutile)
-Triggeri de tip AFTER sunt, în general, folosiți atunci când:
-  * Se dorește ca executarea triggerului să se facă după ce comanda s-a efectuat cu succes
-  * Nu au apărut erori de procesare care ar impune o comandă ROLLBACK pentru respectiva tranzacție
-  * Trebuie alterate și alte date corelate cu cele deja afectate
-==== Triggeri de tip BEFORE ====
+{{:bd2:laboratoare:sqlserverlab01ex02b.png?nolink&740|}}
-Triggerii de tip BEFORE se declanșează la apariția unui eveniment, dar înainte ca evenimentul să se termine;
-<color red>Ex. 1</color> Să se scrie un trigger de tip BEFORE care printează un mesaj ori de câte ori se face un insert în tabela emp.
+Ne lovim de necunoscut, clauza **TOP**, care reduce numărul de linii returnate la **minimumul(numărul specificat, câte rezultate sunt într-adevăr)**. Aceasta nu are nevoie în mod deosebit de **(paranteze rotunde)** pentru a delimita numărul dar ne este mai ușor să ne dăm seama când avem de aface și cu constante.
-{{:bd2:laboratoare:l07ex01.png?nolink&740|}}
+<note>
+După cum puteți observa avem o sintaxă clasică de SQL, până acum. Majoritatea bazelor de date relaționale folosesc același trunchi comun, SQL, duoă care fiecare adaugă "jucării noi".
+</note>
-Să verificăm cum lucrează făcând un insert în tabela EMP:
+Alias-urile sunt un mod de a oferi un nume unei coloane (câteodata este obigatoriu să avem unul, vom vedea asta). Dacă dorim să avem spațiu pentru un alias este nevoie să folosim (N)VARCHAR. Un ALIAS se poate folsi astfel:
 <code sql>
-INSERT INTO EMP(EMPNO, ENAME, SAL)
+SELECT  'Un Alias poate fi specificat în acest mod'  [Modul1]--Cu sau fără spațiu/Paranteze Drepte
-VALUES(999, 'Preda', 1500);
-</code>
-Se observă că triggerul s-a declanșat și operația s-a făcut cu succes.
-Să veden ce se întâmplă dacă forțăm o eroare la inserare(exemplul necesită ca empno să aibă constrângerea not null):
+,'Sau așa'  AS  'Modul 2'
-<code sql>
-INSERT INTO EMP(ENAME, SAL)
-VALUES('Tache', 1500);
 </code>
-Se observă că triggerul s-a declanșat normal, nu a ținut cont că inserarea nu a fost efectuată și a generat o eroare.
-<note>
+<color red>Ex. 2</color> Scrieți o instrucțiune care întoarce 2 coloane, cu alias, și răspunde următoarei întrebări: De ce nu mai avem diacritice în clauza de mai sus?
-Dacă compilarea unui trigger se face cu succes, apare mesajul: "Trigger created"
-În caz că apar erori la crearea unui trigger apare mesajul de avertizare: "Warning: Trigger created with compilation errors."
+===== Surse de date, WHERE și JOIN =====
+Clauza **WHERE** este la fel ca în toate limbajele, filtrează date. Ce este important este că NU ține cont de ALIAS-uri și este foarte importană ordinea operatorilor booleani. În funcție de natura clauzei procesul din spate poate să difere:
-Pentru a vedea erorile de compilare se va folosi comanda: "show errors;"
+  * Datele sunt aduse și după filtrate
+  * Datele aduse sunt deja filtrate
-</note>
+În T-SQL avem mai multe tipuri de **JOIN** (+- cele teoretice care se reduc tot la acestea):
-==== Triggeri de tip AFTER ====
+  * **CROSS JOIN**, produs cartezian
-Triggerii  de tip AFTER se declanșează după ce evenimentul declanșator se termină.
+  * **INNER JOIN**, produs cartezian și condițiile
+  * **OUTER JOIN**, cu cele 3 variante **LEFT** **RIGHT** și **FULL**
-<color red>Ex. 2</color> Să se creeze un trigger de tip AFTER care afișează un mesaj ori de câte ori se face o modificare în tabela EMP.
+Produsul cartezian se poate genera în următorul mod:
-{{:bd2:laboratoare:l07ex02.png?nolink&740|}}
+{{:bd2:laboratoare:sqlserverlab01ex030.png?nolink&740|}}
-Să verificăm cum lucrează triggerul făcând o modificare  de comision în tabela EMP:
+Aici pentru fiecare angajat am adus fiecare manager, ne-am dori să aducem doar managerul lui așa că vom scrie un (SELF) **INNER JOIN** în 2 moduri:
-<code sql>
-UPDATE EMP
-SET COMM = 100
-WHERE EMPNO = 7902;
-</code>
-Se observă că triggerul a fost declanșat la apariția evenimentului, în acest caz comanda UPDATE și s-a afișat mesajul de avertizare. Triggerul se declanșează chiar dacă nu este găsită nicio înregistrare care să îndeplinească condițiile din clauza WHERE.
-<code sql>
+{{:bd2:laboratoare:sqlserverlab01ex03a.png?nolink&740|}}
-UPDATE EMP
-SET COMM = 100
-WHERE EMPNO = 9999;
-</code>
-Deoarece nu există niciun angajat cu empno=9999, nu a fost alterată nicio linie, totuși triggerul s-a declanșat, deoarece nu a apărut nicio eroare de execuție.
-Dacă însă operația se termină cu o eroare, triggerul nu se mai declanșează.
+{{:bd2:laboratoare:sqlserverlab01ex03b.png?nolink&740|}}
-<code sql>
-UPDATE EMP
-SET EMPNO = NULL
-WHERE EMPNO = 7902;
-ROLLBACK;
-</code>
-Am încercat să atribuim valoarea NULL pentru empno, ceea ce este interzis prin definirea tabelei, ca urmare s-a generat o eroare de sistem pe constrângerea respectivă și triggerul nu s-a declanșat.
 <note>
-Putem trage concluzia că un trigger de tip BEFORE se declanșează necondiționat de rezultatul comenzii SQL (chiar dacă se generează o eroare), pe când cel de tip AFTER nu se declanșează dacă comanda SQL generează o eroare.
+După cum puteți observa, cuvântul **INNER** poate să lipsească cu desăvârșire. Același lucru se aplică și pentru clauza **OUTER**, fiind nevoie doar de **LEFT**, **RIGHT** sau  **FULL**.
 </note>
-S-a utilizat comanda ROLLBACK pentru ca modificările să nu rămână permanente.
+<color red>Ex. 3</color> Ne dăm seama că nu sunt aduși toți angajații, de ce? Cine lipsește? De ce lipsește? Scrieți o clauză care rezolvă acest lucru!
+==== CTE ====
+**CTE** provine de la Common Table Expression și este un mod de a ne aduce date pentru o clauză înainte de a se executa efectiv acea clauză. Putem aveam oricâte astfel de expresii, le separăm prin virgulă.
+Exemplu:
-==== Restricții în clauza WHEN ====
+{{:bd2:laboratoare:sqlserverlab01ex04.png?nolink&740|}}
-Dacă vrem să introducem o restricție pentru declanșarea triggerului, putem să folosim clauza WHEN. Această clauză se poate folosi numai pentru triggerii de tip linie, deci poate fi folosită doar cu opțiunea FOR EACH ROW. În clauza WHEN  se acceptă numai condiționări directe sau corelate, nu se acceptă cereri sau subcereri.
-<color red>Ex. 3</color> Să se creeze un trigger de tip AFTER care afișează un mesaj ori de câte ori se face o modificare în tabela emp, dar doar pentru angajații care nu au funcția ‘MANAGER’.
+<color red>Ex. 4</color> Aceeași problemă ca mai sus. Se rezolvă la fel? Rescrieți clauza, în mod corect și complet, fără CTE.
-{{:bd2:laboratoare:l07ex03.png?nolink&740|}}
+==== Subclauze ====
+O subclauză este o clauză aflată în interiourl altei clauze și se poate folosi de valorile din clauzele superioare.. Nivelul 2 știe de Nivelul 1, Nivelul 3 știe de nivelele 1 și 2... Nivelul 32 știe de nivelele 1,2..31 și STOP.
-Să se modifice comisionul pentru angajatul cu empn = 7566
+<note>
-<code sql>
+este nivelul maxim, în cam tot ce presupune o oarecare recursivitate și T-SQL, nu doar pentru subclauze.
-UPDATE EMP
+</note>
-SET COMM = 100
-WHERE EMPNO = 7566;
-ROLLBACK;
+<color red>Ex. 5</color> Scrieți o clauză care întoarce numele angajaților care sunt manageri.
-</code>
-Se observă că triggerul nu a fost declanșat, cu toate că operația UPDATE s-a efectuat cu succes, deoarece angajatul are funcția ‘MANAGER’.
+==== Grupări, Agregate și HAVING ====
+Avem o mulțime de funcții de agregare predefinite pe care le găsiți [[https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15|aici]]. Pe lângă aceste funcții mai sunt și cele fare funcționează DOAR când apare clauza **GROUP BY**.
-<color red>Ex. 4</color> Să se scrie un trigger de tip BEFORE care afișează un mesaj ori de câte ori se face un insert în tabela EMP, doar dacă salariul noului angajat este strict mai mare de 500 și strict mai mic de 5000.
+Gruparea este la fel ca și în celelate limbaje de tip SQL: dispare noțiunea de individ și apre noțiunea de întreg. Pentru a filtra, în acest caz, se folosește **HAVING** care se referă la proprietățile unui grup. Se pot folosi funcții și în acest caz.
-{{:bd2:laboratoare:l07ex04.png?nolink&740|}}
+Exemplu:
-Să facem o inserare cu salariul în afara limitei:
+{{:bd2:laboratoare:sqlserverlab01ex06.png?nolink&740|}}
-<code sql>
-INSERT INTO EMP(EMPNO, ENAME, SAL)
-VALUES(1111, 'Frunza', 5500);
-ROLLBACK;
+<color red>Ex. 6</color> Dorim să îmbunătățim clauza prin adăugarea unei coloane în care vom preciza numele managerilor din acel departament. (**HINT: SUBCLAUZE**)
-</code>
-Se observă că inserarea s-a făcut cu succes, dar triggerul nu s-a declanșat.
+==== ORDER BY ====
+Această clauză sortează datele după obținerea lor. Clauza **TOP** ține cont de această clauză, de aceea veți obserca diferențe de performanță neliniare când aceastea două sunt combinate.
-<color red>Ex. 5</color> Să se scrie un trigger de tip AFTER care se declanșează dacă salariul unui angajat este majorat.
+Sortarea este fie:
-{{:bd2:laboratoare:l07ex05.png?nolink&740|}}
+  * Ascendentă, modul implicit, **ASC**
+  * Descendentă, trebuie specificat explicit, **DESC**
-Se observă că în clauza WHEN, variabilele old și new, se apelează fără caracterul ‘:’ , pe când la afișare trebuie introdus.
+<note>
+Se poate sorta după mai multe criterii, lista fiind separată prin virgulă.
+</note>
-Dacă se face o majorare de salariu pentru salariatul cu empno = 7566, care este rezultatul?
+Acestă clauză ține cont de ALIAS, deoare ce este executată tocmai la final, și de aceea ne putem folosi de ele. De exemplu dacă dorim să sortăm rezultatele din exemplul precedent în mod descrescător după numărul de angajați vom adăuga **ORDER BY NUMAR_ANG DESC** la final. Se poate folosi  și o condiție specială cum ar fi **ORDER  BY  COUNT(D.DNAME)  DESC**, care în cazul nostru face fix același lucru.
-<code sql>
-UPDATE EMP
-SET SAL = 3000,
-  COMM = 100
-WHERE EMPNO = 7566;
-ROLLBACK;
+==== UNION, EXCEPT, INTERSECT ====
-</code>
+Avem și posibilitatea de de a folosi operatori definiți special pentru mulțimi, având în vedere că rezultatul unei clauze este o mulțime de elemente. Singurul lucru care trebuie respectat este structura, adică:
-==== Predicate condiționate ====
+  * Fiecare coloană trebuie să fie de același tip
-În cazul în care se execută mai multe comenzi DML, se pot folosi predicate condiționate în corpul triggerului. Predicatele condiționate sunt:
+  * Ordinea coloanelor este foarte importantă, trebuie să coincidă pentru a avea rezultatul așteptat
-  * INSERTING – returnează TRUE dacă triggerul se declanșează pe o comandă INSERT;
+  * Rezultatul operației va avea drep coloane (numele coloanelor) ce a avut primul tabel din operație
-  * UPDATING – retunează TRUE dacă triggerul se  declanșează pe o comandă UPDATE;
-  * UPDATING (‘column_name’) – retunează TRUE dacă triggerul se declanșează pe o comandă UPDATE care modifică coloana specificată prin column_name;
-  * DELETING – returnează TRUE dacă triggerul se declanșează pe o comandă DELETE.
-<color red>Ex. 6</color> Să se scrie un trigger de tip AFTER care se declanșează în momentul în care se  face un insert, delete sau update pe coloanele sal și comm din tabela EMP. Să se insereze mesaje la declanșarea triggerului în tabela MESAJE.
+Exemplu:
-Pasul I – tabela mesaje
-{{:bd2:laboratoare:l07ex06a.png?nolink&740|}}
-Pasul II – creare trigger
-{{:bd2:laboratoare:l07ex06b.png?nolink&740|}}
-Pasul III – testare trigger
 <code sql>
-INSERT INTO EMP(EMPNO, ENAME, SAL, COMM)
+-- Ordinea este vitală, de aici parantezele
-VALUES(1111, 'Frunza', 1500, 100);
+(
+  (
-UPDATE EMP
+    SELECT 'Ana are Mere'
-SET COMM = NVL(COMM, 0) + 100
+    UNION
-WHERE EMPNO = 999;
+    SELECT 'Dar are si Pere'
+  )
-UPDATE EMP
+  EXCEPT
-SET SAL = NVL(SAL, 0) + 500
+  (
-WHERE EMPNO = 999;
+    SELECT 'Ana nu are Mere'
+  )
-DELETE FROM EMP
+)
-WHERE EMPNO = 999;
+INTERSECT
+(
-SELECT *
+  SELECT 'Ana are Mere'
-FROM MESAJE;
+)
-ROLLBACK;
 </code>
-==== Triggere cu opțiunea INSTEAD OF ====
+<color red>Ex. 7</color> Să genereze textul, folosind **UNION** de 3 ori:
-Acest trigger se definește numai pe view-uri, nu și pe tabele. Unele view-uri nu pot fi modificate prin comenzi DML, dar folosind un trigger cu opțiunea INSTEAD OF acest lucru este realizabil. View-urile care nu pot fi modificate prin comenzile UPDATE, INSERT sau DELETE sunt cele create printr-o interogare care conține în construcție:
-  * Un operator SET sau DISTINCT
-  * O funcție de agregare sau o funcție analitică
-  * Clauzele GROUP BY, ORDER BY, CONNECT BY sau START WITH
-  * O expresie tip colecție într-o clauză SELECT
-  * O subcerere într-o clauză SELECT
-  * Unele metode de JOIN
-Orice view, aflat într-un astfel de caz, se poate face modificabil folosind un trigger cu opțiunea INSTEAD OF. Acest trigger trebuie să determine ce operație trebuie executată pentru modificarea tabelei pe care este creat view-ul respectiv. Dacă view-ul conține pseudocoloane sau expresii , acestea nu pot fi modificate direct printr-o comandă UPDATE, dar pot fi modificate prin trigger.
-<color red>Ex. 7</color> Să creăm un view sefi cu următoarea comandă:
-{{:bd2:laboratoare:l07ex07a.png?nolink&740|}}
-Să facem o inserare în view-ul creat
 <code sql>
-INSERT INTO sefi
+Ana are Mere
-VALUES(50, 'Mediu', 1234, 'Preda', sysdate, 2000. 100);
+Ana are Mere
+Ana are MERE
+Ana are MERE
+Ana are Mere
+Ana are Mere
 </code>
-Comanda INSERT a generat o eroare, deoarece nu se acceptă inserarea într-un astfel de view.
-Să creăm triggerul manager, cu opțiunea INSTEAD OF, care va face o inserare și o modificare în tabela EMP și  inserare în tabela DEPT.
+===== Exercițiu final Clauze =====
+<color red>Ex. 8. </color> Scrieți o clauză care pentru ficare departament în care deviația standard a grilei salariale este între 1 și 1.5 obține următoarele:
+- Numele departamentului
+- Numele angajaților din acel departament
+- Numele managerului cu cel mai mare salariu din departament
+- Grila salarială minimă din departament
+- Grila salarială maximă din deprtament
-{{:bd2:laboratoare:l07ex07b.png?nolink&740|}}
+===== Variabile =====
+În T-SQL putem să declarăm variabile oriunde, nu doar în blocuri speciale, dar trebuie să ținem cont de **SCOPE**, ca în orice alt limbaj de programare.
-Executăm din nou comanda insert de mai sus.
+<code sql>
+-- Putem să declarăm variabile fără valoare
+DECLARE @VAR_WITH_NO_VALUE NVARCHAR(MAX);
+-- Sau care au deja valoare
+DECLARE @VAR_WITH_VALUE NVARCHAR(MAX) = N'Se știe';
-Se observă că inserarea s-a făcut cu succes, de data aceasta, deoarece s-a declanșat triggerul manager care a executat operațiile echivalente comenzii INSERT, prin inserarea în tabelele DEPT și EMP.
+-- Se poate seta în mod explicit valoare unei variabile
+SET @VAR_WITH_NO_VALUE = NULL;-- Atenție la tip
-În corpul triggerului nu trebuie să fie cuprinse instrucțiuni care să afecteze starea tabelei sau view-ului pe care se monitorizează evenimentul.
+-- Se poate seta ca rezultat al unei clauze, oricare ar fi el
+SELECT TOP 1 @VAR_WITH_VALUE = N'Se știe'
+FROM [hr].[dbo].[EMPLOYEES];
-<color red>Ex. 8</color> Să construim un trigger comision care actualizează comisionul la 10% din salariu când se modifică salariul unui angajat:
+-- Putem afișa valoare unei variabile
+PRINT (@VAR_WITH_NO_VALUE);
-{{:bd2:laboratoare:l07ex08.png?nolink&740|}}
+PRINT (@VAR_WITH_VALUE);
-Să facem o modificare de salariu să vedem ce anume se întâmplă:
-<code sql>
-UPDATE EMP
-SET SAL = 5000
-WHERE EMPNO = 7566;
 </code>
-Se observă că în execuția triggerului s-a generat o eroare.
-==== Informații din dicționarul bazei de date ====
+===== XML =====
-Informațiile despre triggeri se pot obține din dicționarul de date, ca pentru proceduri, funcții și pachete. De exemplu, dacă vrem să vedem toți triggerii creați de userul curent, data când au fost creați, data ultimei utilizări și starea lor, putem executa următoarea cerere SQL:
+SQL Server ne permită să facem diverse operațiuni cu date de tip XML, în afară de stocarea lor în mod optim în tabele.
-<code sql>
-SELECT OBJECT_NAME,
-  CREATED,
-  LAST_DDL_TIME,
-  STATUS
-FROM USER_OBJECTS
-WHERE OBJECT_TYPE = 'TRIGGER';
-</code>
-Un trigger se poate șterge din dicționar folosind comanda DDL :
+==== Din tabele în XML ====
-<code sql>
+După cum ați văzut în scriptul de creare, putem să generăm un XML prin sipla adăugare a clauzei **FOR  XML  AUTO** la finalul instrucțiuniilor din cladrul cererii **SELECT** (aici nu avem voie să folosim INTO). Rezultatul este un XML formatat în modul preferat de SQL Server, de aici provine **AUTO**.
-DROP TRIGGER trigger_name;
-</code>
-Pentru a modifica starea unui trigger se folosească comanda DDL:
+<note>
-<code sql>
+Puteți să vă definiți și voi schema/structura pentru XML, dar pentru început este recomandat să o folosiți pe cea implicită.
-ALTER TRIGGER trigger_name {ENABLE | DISABLE}
+</note>
-</code>
-Ex. 9. Să se scrie un trigger care face o inserare în tabela LOG ori de câte ori se face o operație pe coloana sal din tabela EMP.
+Exemplu Creare XML din Tabele:
-Tabela LOG are următoarea structură:
-{{:bd2:laboratoare:l07ex09a.png?nolink&740|}}
+{{:bd2:laboratoare:sqlserverlab01ex070.png?nolink&740|}}
-Triggerul este folosit pentru a păstra semnătura persoanei care a operat pe baza de date, operația pe care o făcut-o, pentru ce angajat a modificat salariul și la ce dată (în formatul yyyy-mm-dd hh-mi-ss).
+<color red>Întrebare 1</color> Ce face această clauză, dacă ignorăm partea de XML?
-Crearea triggerului:
-{{:bd2:laboratoare:l07ex09b.png?nolink&740|}}
+==== Interogare de XML pentru tabele ====
+Putem să interogăm conținutul unui XML pentru a obține date tabelare. În exemplul de mai jos vom lua XML-ul generat anterior și ne vom folosi doar de primul nivel pentru a obține datele legate direct de angajat.
-Testarea triggerului:
+{{:bd2:laboratoare:sqlserverlab01ex080.png?nolink&740|}}
-<code sql>
-INSERT INTO EMP(EMPNO, ENAME, SAL, COMM)
-VALUES(999, 'Preda', 1500, 100);
-UPDATE EMP
+==== Din XML în tabele ====
-SET SAL = NVL(SAL, 0) + 500
+Putem să revenim din XML înapoi la o tabelă (dacă ne permite structura) folosind operatorul **OUTER  APPLY**, despre care puteți să citiți mai multe [[https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15|aici]], dar el aplică anumite operațiuni pe TABELE, mai exact ce este în stânga lui și ce este în dreapta lui, la fel ca și **JOIN**. În exemplul de mai jos puteți să vedeți cum refacem datele tabelare transformate în XML.
-WHERE EMPNO = 999;
-DELETE FROM EMP
+{{:bd2:laboratoare:sqlserverlab01ex090.png?nolink&740|}}
-WHERE EMPNO = 999;
-SELECT *
+<color red>Întrebare 2</color> Ce credeți că returnează clauza dacă stricăm ordinea/nodurile?
-FROM LOG;
-ROLLBACK;
-</code>

Laboratoare

Proiect

Proiect Baze de Date 2

Resurse

bd2/laboratoare/07.1573399489.txt.gz · Last modified: 2019/11/10 17:24 by apetrescu0506

Show page Old revisions

Media Manager Back to top