· 9 years ago · Jan 18, 2017, 07:06 PM
1USE [Mobile.de]
2GO
3
4/* 1 **********************1 pogled – treba da sadrži složeni upit, koji se sastoji od više JOIN-a************************/
5
6 -- Ovaj pogled prikazuje sve boje automobila koji su postavljeni na sajtu --
7CREATE VIEW Boje
8AS
9SELECT
10 a.idAuta
11, m.NazivModela
12, ma.NazivMarke
13, b.NazivBoje
14FROM dbo.Auto AS a
15JOIN dbo.Model AS m
16 ON a.IdModel = m.IdModel
17JOIN dbo.Marka AS ma
18 ON m.IdMarka = ma.IdMarka
19JOIN dbo.Boja AS b
20 ON a.IdBoja = b.IdBoja
21GO
22
23-- UPIT IZ view-a , gde je boja braon i auto BMW --
24
25SELECT * FROM Boje
26WHERE NazivBoje = 'Braon'
27AND NazivMarke = 'BMW'
28
29--------------------------------------------------------------------------------------------------------------------------
30
31/* 2 **************** 3 procedure – po jednu za INSERT, UPDATE, DELETE naredbu;
32procedure treba da sadrže logiku pomoću koje se proverava ispravnost podataka i spreÄava pojava greÅ¡ke;
33za greÅ¡ke Äija se pojava ne može spreÄiti, uraditi obradu greÅ¡ke pomoću posebno kreirane procedure
34(koja će omogućiti ubacivanje informacija o greškama u tabelu; potrebno je da se kreira nova tabela) *********************/
35
36
37 -- Kreiranje tabele za cuvanje gresaka --
38CREATE TABLE [dbo].[ErrorLog](
39 [ErrorID] [int] IDENTITY(1,1) NOT NULL,
40 [ErrorTime] [datetime] NULL,
41 [ErrorNumber] [int] NOT NULL,
42 [ErrorSeverity] [int] NOT NULL,
43 [ErrorState] [int] NULL,
44 [ErrorProcedure] [nvarchar](126) NULL,
45 [ErrorLine] [int] NULL,
46 [ErrorMessage] [nvarchar](4000) NULL,
47 [UserName] [nvarchar](50) NULL,
48 [Resolved] [bit] NULL,
49 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED ( [ErrorID] ASC ));
50 GO
51
52 -- Kreiranje Stored Procedure za cuvanje informacija o greskama --
53CREATE PROCEDURE dbo.usp_ErrorHandler
54AS
55BEGIN
56 BEGIN TRY
57 INSERT INTO [dbo].[ErrorLog]
58 (
59 [ErrorNumber]
60 , [ErrorSeverity]
61 , [ErrorState]
62 , [ErrorProcedure]
63 , [ErrorLine]
64 , [ErrorMessage]
65 )
66 VALUES
67 (
68 ERROR_NUMBER()
69 , ERROR_SEVERITY()
70 , ERROR_STATE()
71 , ERROR_PROCEDURE()
72 , ERROR_LINE()
73 , ERROR_MESSAGE()
74 )
75 END TRY
76 BEGIN CATCH
77 THROW;
78 END CATCH
79END
80GO
81
82SELECT * FROM [dbo].[ErrorLog]
83----------
84
85/* 2.1. INSERT nove boje i sprecavanje pojave duplikata */
86ALTER PROCEDURE NovaBoja_INSERT
87@nazivBoje nchar (20)
88AS
89BEGIN
90 BEGIN TRY
91 IF NOT EXISTS (SELECT * FROM dbo.Boja WHERE NazivBoje = @nazivBoje)
92 BEGIN
93 INSERT INTO dbo.Boja VALUES(@nazivBoje)
94 /*DECLARE @ID int = SCOPE_IDENTITY();*/
95 END
96 END TRY
97 BEGIN CATCH
98 EXEC dbo.usp_ErrorHandler;
99 THROW;
100 END CATCH
101END
102GO
103-- Pozivanje procedure --
104EXEC NovaBoja_INSERT
105@nazivBoje = 'Crvena'
106GO
107------------------------
108SELECT * FROM dbo.Boja
109-------------------------------------------------------------------------
110
111/* 2.2. UPDATE kontkat telefona i eMail-a vlasnika po ID-u*/
112
113ALTER PROCEDURE UpdateTelefona
114(
115@IDVlasnika int,
116@eMail nvarchar(50),
117@Tel nvarchar(50)
118)
119AS
120BEGIN
121 BEGIN TRY
122 IF EXISTS (SELECT * FROM dbo.Vlasnik WHERE IdVlasnika = @IDVlasnika)
123 BEGIN
124 UPDATE dbo.Vlasnik SET eMail = @eMail, Telefon = @Tel WHERE IdVlasnika = @IDVlasnika;
125 END
126 END TRY
127 BEGIN CATCH
128 BEGIN
129 PRINT 'Greska u odabiru korisnika!';
130 EXECUTE dbo.usp_ErrorHandler;
131 THROW;
132 END
133 END CATCH
134
135END
136GO
137
138EXEC UpdateTelefona 50, 'jj@Gmail.com', '0644444444'
139GO
140
141SELECT * FROM dbo.Vlasnik
142-------------------------------------------------------------------------
143
144/* 2.3. DELETE*/
145--Brisanje Vlasnika i auta
146ALTER PROCEDURE ObrisiVlasnika
147@vlasnikID int
148AS
149BEGIN
150 SET NOCOUNT ON;
151 BEGIN TRY
152 IF EXISTS (SELECT * FROM dbo.Vlasnik WHERE IdVlasnika = @vlasnikID)
153 BEGIN
154 BEGIN TRANSACTION
155 DELETE FROM dbo.AutoOprema WHERE IdAuta = @vlasnikID;
156 DELETE FROM dbo.Auto WHERE IdVlasnika = @vlasnikID;
157 DELETE FROM dbo.Vlasnik WHERE IdVlasnika = @vlasnikID;
158 COMMIT TRANSACTION;
159 END
160 END TRY
161 BEGIN CATCH
162 IF @@TRANCOUNT > 0
163 BEGIN
164 ROLLBACK TRANSACTION;
165 EXEC dbo.usp_ErrorHandler;
166 THROW;
167 END
168 END CATCH
169END
170GO
171SELECT * FROM dbo.Vlasnik
172EXEC ObrisiVlasnika 6
173--------------------------------------------------------------------------
174
175/* 3. SKALARNA FUNKCIJA */
176
177--------------------------------------------------------------------------
178
179/* 4. TABLE-VALUED FUNKCIJA (kao parametrizovani pogled) */
180--IZLISTAVANJE NAJNOVIJIH, poslednjih n oglasa
181CREATE FUNCTION NajNovijiOglasi
182(@broj int)
183RETURNS TABLE
184AS
185 RETURN
186 (
187 SELECT TOP(@broj)
188 a.IdAuta
189 , a.PresaoKm
190 , a.Cena
191 , a.Datum
192 , a.Snaga
193 , a.RadnaZapremina
194 , a.Godiste
195 , a.Opis
196 , m.NazivModela
197 , ma.NazivMarke
198 , b.NazivBoje
199 , g.NazivTipaGoriva
200 , k.TipKaroserije
201 , klasa.NazivKlase
202 , v.Telefon
203 , v.eMail
204 , v.Drzava
205 FROM dbo.Auto AS a
206 JOIN dbo.Model AS m
207 ON a.IdModel = m.IdModel
208 JOIN dbo.Marka AS ma
209 ON m.IdMarka = ma.IdMarka
210 JOIN dbo.Boja AS b
211 ON a.IdBoja = b.IdBoja
212 JOIN dbo.TipGoriva AS g
213 ON a.IdTipGoriva = g.IdTipGoriva
214 JOIN dbo.TipKaroserije AS k
215 ON a.IdKaroserije = k.IdKaroserije
216 JOIN dbo.EmisionaKlasa AS klasa
217 ON a.IdEmisionaKlasa = klasa.IdEmisionaKlasa
218 JOIN dbo.Vlasnik AS v
219 ON a.IdAuta = v.IdVlasnika
220
221 );
222GO
223
224SELECT * FROM NajNovijiOglasi (15) ORDER BY Datum DESC;
225
226/*TABLE VALUED F-ja 2 */
227CREATE FUNCTION vratiGrad ( @grad nchar(20) )
228RETURNS table AS RETURN
229( SELECT Grad FROM dbo.Vlasnik WHERE Grad = @grad )
230GO
231SELECT * FROM vratiGrad('Beograd')
232--------------------------------------------------------------------------
233
234/* 5. AFTER OKIDAČ */
235
236--------------------------------------------------------------------------
237
238/* 6. INSTEAD OF OKIDAČ */
239
240--------------------------------------------------------------------------
241
242/* 7. DDL OKIDAČ */
243CREATE TRIGGER DropAlert
244ON DATABASE
245FOR DROP_TABLE
246AS
247 PRINT 'Morate onemoguciti trigger "DropAlert" da biste obrisali bazu!'
248 ROLLBACK;
249
250DROP TABLE dbo.Boja
251--------------------------------------------------------------------------
252
253/* 8. PROCEDURA KOJA SADRŽU OBRADU TRANSAKCIJE */
254-- Brisanje automobila po ID-u --
255
256ALTER PROCEDURE BrisanjeAutomobilaPoId
257@IdAuta int
258AS
259BEGIN
260SET NOCOUNT ON;
261BEGIN TRY
262 BEGIN TRANSACTION
263 DELETE FROM dbo.AutoOprema WHERE IdAuta = @IdAuta;
264 DELETE FROM dbo.Auto WHERE IdAuta = @IdAuta;
265 COMMIT TRANSACTION;
266END TRY
267BEGIN CATCH
268 IF @@TRANCOUNT > 0
269 BEGIN
270 PRINT 'Nije Sve Obrisano';
271 ROLLBACK TRANSACTION;
272
273 END;
274 THROW;
275END CATCH
276END
277GO
278
279SELECT * FROM dbo.Auto
280EXEC BrisanjeAutomobilaPoId 1
281SELECT * FROM dbo.Auto
282-------------------------------------------------------------------------
283
284/* 9. SCRIPT KOJI SIMULIRA POJAVU DEADLOCK-a Pravi se izmena brojeva telefona za dva razlicita vlasnika u isto vreme*/
285
286/**** SCRIPT 1 ****/
287
288--1.Korak
289USE [Mobile.de]
290GO
291--2. Korak
292BEGIN TRANSACTION;
293GO
294UPDATE dbo.Vlasnik
295SET Telefon = '061111111'
296WHERE IdVlasnika = 2;
297GO
298--5. Korak
299SELECT Telefon
300FROM dbo.Vlasnik
301WHERE IdVlasnika = 2;
302GO
303COMMIT TRAN;
304GO
305
306/**** SCRIPT 2 ****/
307
308--3. Korak
309USE [Mobile.de]
310GO
311--4. Korak
312BEGIN TRANSACTION;
313GO
314UPDATE dbo.Vlasnik
315SET Telefon = '062222222'
316WHERE IdVlasnika = 1;
317GO
318--6. Korak
319SELECT Telefon
320FROM dbo.Vlasnik
321WHERE IdVlasnika = 1;
322GO
323COMMIT TRAN;
324GO
325/*DECLARE @RetryCount int = 5;
326
327WHILE (@RetryCount > 0) -- retry update if deadlock
328BEGIN
329 BEGIN TRY
330 BEGIN TRANSACTION;
331 UPDATE dbo.Vlasnik SET Telefon = '061111111'
332 WHERE IdVlasnika = 2;
333
334 WAITFOR DELAY '00:00:05';
335
336 UPDATE dbo.Vlasnik SET Telefon = '062222222'
337 WHERE IdVlasnika = 1;
338 SET @RetryCount = 0;
339 COMMIT TRANSACTION;
340 END TRY
341 BEGIN CATCH
342 IF (ERROR_NUMBER() = 1205) -- deadlock
343 BEGIN
344 EXEC dbo.usp_ErrorHandler;
345 SET @RetryCount = @RetryCount - 1;
346 WAITFOR DELAY '00:00:02';
347 PRINT 'Warning: Deadlock occurred';
348 END
349 ELSE
350 SET @RetryCount = -1;
351 IF XACT_STATE() <> 0
352 ROLLBACK TRANSACTION;
353 END CATCH;
354END;
355*/
356
357
358-------------------------------------------------------------------------
359
360/* 10. SCRIPT, koji će simulirati rad sa podacima
361 ako je postavljen izolacioni nivo READ UNCOMMITTED
362
363 Menja se naziv odredjene(po iD-u) opreme*/
364
365/**** SCRIPT 1 ****/
366--1. Korak
367USE [Mobile.de]
368GO
369--2. Korak
370BEGIN TRANSACTION;
371GO
372UPDATE dbo.Oprema
373SET NazivOpreme = 'ASR'
374WHERE IdOpreme = 3;
375GO
376SELECT NazivOpreme
377FROM dbo.Oprema
378WHERE IdOpreme = 3;
379GO
380--5. Korak
381ROLLBACK TRAN;
382GO
383
384/**** SCRIPT 2 ****/
385--3. Korak
386USE [Mobile.de]
387GO
388--4. Korak
389SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
390GO
391SELECT NazivOpreme
392FROM dbo.Oprema
393WHERE IdOpreme = 3;
394GO
395
396--------------------------------------------------------------------------