· 8 years ago · Mar 19, 2018, 07:36 PM
1--Przydatne funkcje , typy danych
2--sysname -- typ danych które sa podobne do NVARCHARA, nie mogą być nullem
3---- RAISERROR('BRAK KLIENTA O PODANYM IDENTYFIKATORZE', 11, 1); //Wywowałnie błedy w bloku TRY
4 --PRINT 'BLAD (' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ') W PROCEDURZE: ' + ERROR_PROCEDURE(); // Wypisanie błędu z RAISERROR w bloku catch
5 -- PRINT ERROR_MESSAGE(); //TRY_CAST(ERROR_NUMBER() AS NVARCHAR(10))
6 -- ERROR_NUMBER() - numer kodu błędu
7 --ERROR_PROCEDURE() - nazwa procedury w ktorym wystąpił błąd
8 --ERROR_MESSAGE(); - wiadomość błędu
9 --THROW 52000, 'BLAD WSTAWIANIA DANYCH', 1 a w bloku catch -> catch
10 --CAST (cos as nvarchar)
11 --TRY_CAST
12 --TRY_CAST(ERROR_LINE() AS NVARCHAR(10)) -- błąd w linii
13 --ROW_NUMBER() OVER(PARTITION BY YEAR(ORDERDATE) order by count(*) desc)
14 --CREATE TRIGGER [dbo].[Lab3Zadanie1Trigger] ON [dbo].[Products] AFTER INSERT NOT FOR REPLICATION
15 --ROLLBACK
16 --ALTER TABLE [dbo].[Products] ENABLE TRIGGER [Lab3Zadanie1Trigger] // uruchomienie triggera
17
18 --KURSOSY
19 /*
20 DECLARE kursor CURSOR FOR
21 SELECT ProductId, UnitPrice FROM inserted;
22
23 OPEN kursor
24 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
25 WHILE @@FETCH_STATUS = 0
26 BEGIN
27 IF @nowaCena < 10
28 BEGIN
29 SELECT @staraCena = UnitPrice FROM deleted WHERE ProductID = @produkt;
30 UPDATE Products SET UnitPrice = @staraCena WHERE ProductID = @produkt;
31 END
32 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
33 END
34 */
35 -- CLOSE kursor
36 --DEALLOCATE kursor musi byc
37 --tabela
38 /*
39 CREATE TABLE [dbo].[Lab4Zadanie1_DDL_LOG](
40 [ActionID] [int] IDENTITY(1,1) NOT NULL,
41 [ActionDateTime] [datetime] NOT NULL,
42 [TableName] [nchar](50) NOT NULL,
43 [UserName] [nchar](50) NOT NULL
44) ON [PRIMARY]
45*/
46
47---- przypisujemy informacje o zdrzeniu do zmiennej xml
48-- DECLARE @eventdata xml = EVENTDATA(); -- inicjowanie obiektu
49
50/*
51 SELECT
52 EventNode.value('PostTime[1]', 'datetime'), //czas uruchomienia triggera
53 EventNode.value('ObjectName[1]', 'sysname'), //nazwa tabeli
54 EventNode.value('LoginName[1]', 'sysname') //login kolesia co chcial cos zrobic
55 FROM
56 @eventdata.nodes('/EVENT_INSTANCE') EventTable(EventNode);
57 */
58
59 /*
60 INSERT INTO dbo.DdlActionLog (
61 EventType,
62 PostTime,
63 LoginName,
64 UserName,
65 ServerName,
66 SchemaName,
67 DatabaseName,
68 ObjectName,
69 ObjectType,
70 CommandText
71 )
72 SELECT
73 EventNode.value('EventType[1]', 'nvarchar(200)'), --wszystkie dane sa w obiekcie @eventdata
74 EventNode.value('PostTime[1]', 'datetime'),
75 EventNode.value('LoginName[1]', 'sysname'),
76 EventNode.value('UserName[1]', 'sysname'),
77 EventNode.value('ServerName[1]', 'sysname'),
78 EventNode.value('SchemaName[1]', 'sysname'),
79 EventNode.value('DatabaseName[1]', 'sysname'),
80 EventNode.value('ObjectName[1]', 'sysname'),
81 EventNode.value('ObjectName[1]', 'sysname'),
82 EventNode.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')
83 FROM
84 @eventdata.nodes('/EVENT_INSTANCE') EventTable(EventNode);
85
86 */
87
88
89 /*
90 SELECT
91 distinct Country --nie ma duplikatów
92 FROM
93 Customers
94 WHERE
95 Country IS NOT NULL
96 ORDER BY
97 Country ASC;
98 */
99
100--Przykład procedur
101CREATE PROCEDURE [dbo].[Lab1Zadanie3]
102 @kolumna AS sysname
103AS
104BEGIN
105 DECLARE @msg AS NVARCHAR(500);
106
107 -- JEŻELI NIE PODANO NAZWY KOLUMNY
108 IF @kolumna IS NULL
109 BEGIN
110 SET @msg = 'PARAMETR @colname NIE MOŻE PRZYJMOWAĆ WARTOŚCI PUSTEJ (NULL).';
111 PRINT @msg;
112 -- MOÅ»NA TEÅ» ZGÅOSIC BÅÄ„D PONIÅ»SZYM POLECENIEM:
113 -- RAISERROR(@msg, 16, 1);
114 RETURN;
115 END
116
117 -- JEŻELI PODANO INNĄ NAZWĘ KOLUMNY
118 IF @kolumna NOT IN ('ShipperID', 'CompanyName', 'Phone')
119 BEGIN
120 SET @msg = 'PARAMETR @colname MOŻE PRZYJMOWAĆ NASTĘPUJĄCE WARTOŚCI: ShipperID, CompanyName, Phone';
121 PRINT @msg;
122 -- MOÅ»NA TEÅ» ZGÅOSIC BÅÄ„D PONIÅ»SZYM POLECENIEM:
123 -- RAISERROR(@msg, 16,1);
124 RETURN;
125 END
126
127 -- DOSTAWCY POSORTOWANIU WG. ZADANIEJ KOLUMNY
128 IF @kolumna = 'ShipperID'
129 SELECT ShipperID, CompanyName, Phone FROM Shippers ORDER BY ShipperID;
130 ELSE IF @kolumna = 'CompanyName'
131 SELECT ShipperID, CompanyName, Phone FROM Shippers ORDER BY CompanyName;
132 ELSE IF @kolumna = 'Phone'
133 SELECT ShipperID, CompanyName, Phone FROM Shippers ORDER BY Phone;
134END
135
136GO
137-- przykład wykorzystania case w order by
138CREATE PROCEDURE [dbo].[Lab1Zadanie4]
139 @kolumna AS sysname = NULL,
140 @porzadek AS CHAR(1) = 'A'
141AS
142BEGIN
143 SET NOCOUNT ON;
144
145 SELECT
146 ShipperID, CompanyName, Phone
147 FROM
148 Shippers
149 ORDER BY
150 CASE WHEN @kolumna = 'ShipperID' AND @porzadek = 'A' THEN ShipperID END,
151 CASE WHEN @kolumna = 'CompanyName' AND @porzadek = 'A' THEN CompanyName END,
152 CASE WHEN @kolumna = 'Phone' AND @porzadek = 'A' THEN Phone END,
153 CASE WHEN @kolumna = 'ShipperID' AND @porzadek = 'D' THEN ShipperID END DESC,
154 CASE WHEN @kolumna = 'CompanyName' AND @porzadek = 'D' THEN CompanyName END DESC,
155 CASE WHEN @kolumna = 'Phone' AND @porzadek = 'D' THEN Phone END DESC;
156END
157
158GO
159--przykład wykorzystania case w select
160SELECT ProductNumber, Category =
161 CASE ProductLine
162 WHEN 'R' THEN 'Road'
163 WHEN 'M' THEN 'Mountain'
164 WHEN 'T' THEN 'Touring'
165 WHEN 'S' THEN 'Other sale items'
166 ELSE 'Not for sale'
167 END,
168 Name
169FROM Production.Product
170ORDER BY ProductNumber;
171GO
172
173--Przykład z searched case
174USE AdventureWorks2012;
175GO
176SELECT ProductNumber, Name, "Price Range" =
177 CASE
178 WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
179 WHEN ListPrice < 50 THEN 'Under $50'
180 WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
181 WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
182 ELSE 'Over $1000'
183 END
184FROM Production.Product
185ORDER BY ProductNumber ;
186GO
187
188
189--Przykład z użyciem ROW_NUMBER
190CREATE PROCEDURE [dbo].[Lab2Zadanie5]
191 @rok AS INT = 1990
192AS
193BEGIN
194 SET NOCOUNT ON;
195
196 BEGIN TRY
197 IF NOT EXISTS (SELECT * FROM Orders WHERE YEAR(OrderDate) = @rok)
198 BEGIN
199 DECLARE @msg AS VARCHAR(100);
200 SET @msg = 'BRAK ZAMOWIEN W ROKU: ' + CONVERT(varchar(10), @rok);
201 RAISERROR(@msg, 11, 1);
202 END
203 ELSE
204 BEGIN
205 SELECT
206 ROW_NUMBER() OVER(PARTITION BY YEAR(O.OrderDate) ORDER BY COUNT(*) DESC) as Nr,
207 S.CompanyName,
208 YEAR(O.OrderDate) as Year,
209 COUNT(*) as Orders
210 FROM
211 dbo.Orders as O JOIN dbo.Shippers as S on O.ShipVia = S.ShipperID
212 WHERE
213 YEAR(o.OrderDate) = @rok
214 GROUP BY
215 S.CompanyName,
216 YEAR(O.OrderDate)
217 ORDER BY
218 Year,
219 Orders DESC;
220 END
221 END TRY
222 BEGIN CATCH
223 PRINT 'BLAD W PROCEDURZE: ' + ERROR_PROCEDURE()
224 PRINT 'BLAD W LINII: ' + TRY_CAST(ERROR_LINE() AS NVARCHAR(10))
225 PRINT 'KOMUNIKAT: ' + ERROR_MESSAGE()
226 PRINT 'KOD BLEDU: ' + TRY_CAST(ERROR_NUMBER() AS NVARCHAR(10))
227 END CATCH
228END
229
230GO
231
232
233--Przykład triggera z kursorami
234CREATE TRIGGER [dbo].[Lab3Zadanie2Trigger]
235 ON [dbo].[Products]
236 AFTER INSERT,UPDATE
237 NOT FOR REPLICATION
238AS
239BEGIN
240 DECLARE @ilosc INT;
241 DECLARE @staraCena MONEY;
242 DECLARE @nowaCena MONEY;
243 DECLARE @produkt INT;
244
245 SELECT @ilosc = COUNT(*) FROM deleted;
246
247 IF (@ilosc = 0)
248 BEGIN
249 -- był INSERT
250 DECLARE kursor CURSOR FOR
251 SELECT ProductId, UnitPrice FROM inserted;
252
253 OPEN kursor
254 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
255 WHILE @@FETCH_STATUS = 0
256 BEGIN
257 IF @nowaCena < 10
258 DELETE FROM Products WHERE ProductID = @produkt;
259 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
260 END
261
262 CLOSE kursor
263 DEALLOCATE kursor
264 END
265 ELSE
266 BEGIN
267 -- był UPDATE
268 DECLARE kursor CURSOR FOR
269 SELECT ProductId, UnitPrice FROM inserted;
270
271 OPEN kursor
272 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
273 WHILE @@FETCH_STATUS = 0
274 BEGIN
275 IF @nowaCena < 10
276 BEGIN
277 SELECT @staraCena = UnitPrice FROM deleted WHERE ProductID = @produkt;
278 UPDATE Products SET UnitPrice = @staraCena WHERE ProductID = @produkt;
279 END
280 FETCH NEXT FROM kursor INTO @produkt, @nowaCena;
281 END
282
283 CLOSE kursor
284 DEALLOCATE kursor
285 END
286END
287
288GO
289
290
291--przykład ważny
292CREATE TRIGGER [Lab4Zadanie1]
293ON DATABASE
294FOR DROP_TABLE
295AS
296BEGIN
297 -- przypisujemy informacje o zdrzeniu do zmiennej xml
298 DECLARE @eventdata xml = EVENTDATA();
299
300 -- wycofujemy transakcjÄ™
301 ROLLBACK TRANSACTION
302
303 -- wypisujemy komunikat
304 PRINT 'Nie masz uprawnień do usuwania tabel w bazie danych Northwind!';
305
306 -- zapisujemy informacje do tabeli
307 INSERT INTO
308 Lab4Zadanie1_DDL_LOG (ActionDateTime, TableName, UserName)
309 SELECT
310 EventNode.value('PostTime[1]', 'datetime'),
311 EventNode.value('ObjectName[1]', 'sysname'),
312 EventNode.value('LoginName[1]', 'sysname')
313 FROM
314 @eventdata.nodes('/EVENT_INSTANCE') EventTable(EventNode);
315END
316
317GO
318
319--kolejny przykład
320
321
322CREATE TRIGGER [Lab4Zadanie2]
323ON DATABASE
324FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
325AS
326BEGIN
327 -- przypisujemy informacje o zdrzeniu do zmiennej xml
328 DECLARE @eventdata xml = EVENTDATA();
329
330 -- wstaw wybrane informacje o zdarzeniu do tabeli logow
331 INSERT INTO dbo.DdlActionLog (
332 EventType,
333 PostTime,
334 LoginName,
335 UserName,
336 ServerName,
337 SchemaName,
338 DatabaseName,
339 ObjectName,
340 ObjectType,
341 CommandText
342 )
343 SELECT
344 EventNode.value('EventType[1]', 'nvarchar(200)'),
345 EventNode.value('PostTime[1]', 'datetime'),
346 EventNode.value('LoginName[1]', 'sysname'),
347 EventNode.value('UserName[1]', 'sysname'),
348 EventNode.value('ServerName[1]', 'sysname'),
349 EventNode.value('SchemaName[1]', 'sysname'),
350 EventNode.value('DatabaseName[1]', 'sysname'),
351 EventNode.value('ObjectName[1]', 'sysname'),
352 EventNode.value('ObjectName[1]', 'sysname'),
353 EventNode.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')
354 FROM
355 @eventdata.nodes('/EVENT_INSTANCE') EventTable(EventNode);
356
357END
358
359
360GO
361
362ENABLE TRIGGER [Lab4Zadanie2] ON DATABASE
363GO