SQL en Datanormalisatie

SQL
Les 12
19 July 2017
1
Agenda

Herhaling
Stored Procedures
Oefeningen
User Defined Functions
Triggers

Oefeningen




19 July 2017
2
Herhaling

DML
SELECT - statement
 SINGLE ROW FUNCTIONS
 JOIN

OUTER JOIN
 INNER JOIN
 SELF JOIN

GROUP FUNCTIONS
 SUBQUERIES

19 July 2017
3
Herhaling

DDL
CREATE
 ALTER
 DROP


TABLE
19 July 2017
4
Herhaling

DML
INSERT
 UPDATE
 DELETE


TRANSACTIONS
BEGIN TRAN
 COMMIT
 ROLLBACK

19 July 2017
5
Herhaling

DDL
VIEW
 PROCEDURELE EXTENSIONS
 STORED PROCEDURES
 USER DEFINED FUNCTIONS
 TRIGGERS

19 July 2017
6
View

Er zijn 2 soorten “tabellen”:
Fysieke tabellen
 VIEWS = Logische tabellen gebaseerd op een
andere tabel of een andere view. (Derived tables)





Bevat geen data!
= venster
In de databank opgeslaan als een SELECTstatement
Restricted DMLs possible
Procedural extensions

Batch
Sequence van SQL Statements
 Verzonden naar de database om samen uitgevoerd te
worden
 Om DDL statements van elkaar te scheiden, dient
gebruik gemaakt te worden van GO
 Gebruik BEGIN en END om een block van
statements aan te geven

19 July 2017
8
Procedural extensions

IF Statement

WHILE Statement
BREAK: stopt de complete while loop
 CONTINUE: stopt de huidige uitvoering van de
while loop
19 July 2017

9
Procedural extensions

CASE Statement
19 July 2017
10
Procedural extensions

Locale variabelen


DECLARE @variable DATATYPE


Kunnen enkel gebruikt worden in de huidige batch
Geheugen reserveren
Waarde toekennen
SET @variable = DesiredValue
OF
 SELECT @variable = expression FROM table


@@ variabelen zijn global variables
19 July 2017
11
Specific statements

RETURN
~ BREAK in WHILE loop
 Beëindigt de batch


GOTO
19 July 2017
12
Specific statements (2)

RAISERROR
Genereer een user-defined error message
 > 50000


WAITFOR

DELAY


TIME


Een bepaalde periode wachten
Tot een bepaald tijdstip wachten
TIMEOUT
19 July 2017

Wachten op een message op een queue
13
Example WAITFOR
declare @counter int
set @counter = 1
while @counter <= 10
begin
WAITFOR DELAY '00:01:00'
print @counter
print getdate()
set @counter = @counter + 1
end
19 July 2017
14
Output Example WAITFOR
19 July 2017
15
Example RAISERROR
EXEC sp_addmessage 50005, 10,
N'Unvalid System user!';
if SYSTEM_USER = 'EMEA\SSCK2M'
print 'APPROVED'
else
RAISERROR (50005, -- Message id.
10, -- Severity,
1 -- State
)
19 July 2017
16
Example GOTO (from MSDN)
DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
SELECT @Counter
SET @Counter = @Counter + 1
IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
IF @Counter = 5 GOTO Branch_Two --This will never execute.
END
Branch_One:
SELECT 'Jumping To Branch One.'
GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
SELECT 'Jumping To Branch Two.'
Branch_Three:
SELECT 'Jumping To Branch Three.'
19 July 2017
17
ERROR Handling




Opvangen van fouten in programma’s
Generen van duidelijk error meldingen
‘Proper’ stoppen met de uitvoering van een
programma
Exception:

Een probleem dat het uitvoeren van een programma
blokkeert
19 July 2017
18
ERROR Handling


TRY/CATCH
TRY:


Opvangen van de exception
CATCH:

Behandelen van de exception
19 July 2017
19
Example TRY/CATCH
USE sample;
BEGIN TRY
BEGIN TRANSACTION
insert into employee values(11111, 'Ann', 'Smith','d2');
insert into employee values(22222, 'Matthew', 'Jones','d4'); -- referential integrity error
insert into employee values(33333, 'John', 'Barrimore', 'd2');
COMMIT TRANSACTION
PRINT 'Transaction committed'
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'Transaction rolled back'
END CATCH
19 July 2017
20
Stored Procedure

Batch van statements

als object bewaard in de database
19 July 2017
21
Stored Procedure

System stored procedures


Eg sp_rename
Komen we later op terug
19 July 2017
22
Stored Procedure

Gebruik optie OUTPUT om een return waarde
te gebruiken
19 July 2017
23
Stored Procedure and CLR
19 July 2017
24
Oefening





Op DB MYFIRSTDB
Schrijf een stored procedure delete_afd met als
argument een afdelingsnummer.
Breidt de procedure uit zodat de afdeling enkel
wordt verwijderd uit de tabel indien de afdeling
minstens 5 medewerkers bevat
Test de Stored Procedure uit.
Schrijf een stored procedure insert_afd die
records toevoegt in de tabel afd.
19 July 2017
25
Oefening



Schrijf een stored procedure die een datetime als
parameter meekrijgt.
De stored procedure moet wachten tot het
tijdstip in de parameter en vervolgens start naar
het scherm schrijven.
Test de stored procedure
19 July 2017
26
Oefening

Schrijf een stored procedure die de referentiële
integriteit op de MYFIRSTDB checkt.
19 July 2017
27
User Defined Functions


Gelijkaardig aan Stored Procedures
Een output parameter is verplicht
19 July 2017
28
User Defined Functions

Twee soorten

Scalar-valued


Eén resultaat
Table-valued

19 July 2017
Een set van rijen als resultaat
29
TABLE Data Type

Vaak gebruikt bij TABLE valued functions
CREATE FUNCTION employees_in_project
(@pr_number CHAR(4))
RETURNS TABLE
AS
RETURN
(SELECT emp_fname, emp_lname
FROM works_on, employee
WHERE employee.emp_no = works_on.emp_no
AND project_no = @pr_number)
19 July 2017
30
Table – Valued Functions en
APPLY

Vergelijkbaar met een join van tabellen
CROSS APPLY: ~INNER JOIN
 OUTER APPLY: ~OUTER JOIN

create function dbo.fn_getjob(@empid AS INT)
RETURNS TABLE AS
RETURN
SELECT job
FROM works_on
WHERE emp_no = @empid
AND job IS NOT NULL AND project_no = 'p1';
SELECT E.emp_no, emp_fname, emp_lname, job
FROM employee as E
CROSS
APPLY dbo.fn_getjob(E.emp_no) AS A
19 July 2017
31
Trigger



Een opzichzelf bestaande routine afhankelijk
van een bepaalde tabel of view
Trigger zorgt voor het uitvoeren van een actie
indien er een event gebeurt op een tabel of view
3 delen:
Trigger event
 Trigger condition
 Trigger action


Event kan een DML of DDL statement zijn
19 July 2017
32
Trigger (2)

Nadelen van triggers
Triggers zijn onzichtbaar voor client application
 Het is niet altijd makkelijk de logica te volgen
(before/after)
 Triggers kunnen al eens vergeten worden. Zeker als
er geen documentatie over bestaat
 Triggers lopen steeds als een DML commando op
een tabel wordt uitgevoerd. Dit kan gevolgen hebben
voor de performance

19 juli 2017
33
DML Trigger

Table_name | view_name:


FOR | AFTER | INSTEAD OF:


Object waarop de trigger gebouwd wordt
Wanneer moet de actie uitgevoerd worden?
INSERT | UPDATE | DELETE

Na welk event moet de actie gebeuren?
19 July 2017
34
Voorbeeld trigger

Veronderstel:

We hebben een tabel emp

We hebben een tabel emp_audit, waarin we de
operaties op emp willen bijhouden
Voorbeeld trigger (2)
Inserted and Deleted tables

Twee tabellen onderhouden door het systeem

Inserted:


Deleted:


Bevat copies van de inserted rijen in de triggered tabel
Bevat copies van de deleted rijen in de triggered tabel
Een UPDATE statement wordt behandeld als
een DELETE gevolgd door een INSERT
19 July 2017
37
Toepassingen van DML triggers



Audit van tabellen
Implementeren van business rules
Referentiële integriteit forceren

Meer flexibiliteit dan de referentiële integriteit op
tabel level
19 July 2017
38
Sequence van triggers

sp_settriggerorder
System stored procedure
 Zetten van de order van de triggers op een tabel
 Eerst of laatste trigger aanduiden voor een statement
type (INSERT, UPDATE, DELETE, …)


sp_helptrigger


Tonen van eigenschappen van een trigger
Niet van toepassing voor INSTEAD OF
triggers
19 July 2017
39
DDL Triggers

ALL SERVER | DATABASE:


Scope van de trigger (op een database of op de
gehele server)
EVENT_TYPE

http://msdn.microsoft.com/enus/library/bb522542.aspx
19 July 2017
40
DDL Triggers

EVENT_GROUP


http://msdn.microsoft.com/enus/library/bb510452.aspx
Verschillen met DML triggers
Geen INSERTED en DELETED tabellen
 Geen INSTEAD OF
 Scope op een database of Server en niet op een tabel
of view

19 July 2017
41
Example DDL Trigger
USE sample;
GO
CREATE TRIGGER prevent_drop_triggers
ON DATABASE FOR DROP_TRIGGER
AS PRINT 'You must disable "prevent_drop_triggers" to drop any trigger'
ROLLBACK
19 July 2017
42
Oefeningen op Triggers

p. 385
Beginners Guide to SQL Server 2008
19 July 2017
43
Oefeningen

Herhalingsoefeningen
19 juli 2017
44