· 9 years ago · Nov 28, 2016, 06:54 AM
1use master
2GO
3
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[ AMitrofanova]'
8)
9ALTER DATABASE AMitrofanova set single_user with rollback immediate
10GO
11
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'AMitrofanova'
16)
17DROP DATABASE AMitrofanova
18GO
19
20IF OBJECT_ID('AMitrofanova') IS NOT NULL
21DROP DATABASE AMitrofanova
22GO
23
24CREATE DATABASE AMitrofanova
25GO
26
27USE AMitrofanova
28GO
29
30
31CREATE TABLE NameVal
32(
33valLetters NVARCHAR(3) NOT NULL,
34valName NVARCHAR(80) NOT NULL,
35CONSTRAINT PK_let PRIMARY KEY (valLetters)
36)
37
38
39CREATE TABLE Rate
40(
41valFrom NVARCHAR(3) NOT NULL,
42valTo NVARCHAR(3) NOT NULL,
43valueR FLOAT NOT NULL
44CONSTRAINT PK_exchange PRIMARY KEY (valFrom, valTo)
45CONSTRAINT FK_to FOREIGN KEY (valTo) REFERENCES NameVal (valLetters),
46CONSTRAINT FK_from FOREIGN KEY (valFrom) REFERENCES NameVal (valLetters)
47)
48
49
50
51CREATE TABLE Pocket (
52val NVARCHAR(3) NOT NULL,
53amount FLOAT NOT NULL,
54CONSTRAINT PK_Pocket_Currency PRIMARY KEY (val),
55CONSTRAINT FK_pocket_cur FOREIGN KEY (val) REFERENCES NameVal (valLetters)
56)
57
58
59INSERT INTO NameVal VALUES
60(N'USD', N'Доллары СШÐ')
61, (N'GBP', N'Фунт Ñтерлингов')
62, (N'JPY', N'Иена')
63, (N'CHF', N'ШвейцарÑкий франк')
64, (N'CAD', N'КанадÑкий доллар')
65, (N'DKK', N'ДатÑÐºÐ°Ñ ÐºÑ€Ð¾Ð½Ð°')
66, (N'SEK', N'ШведÑÐºÐ°Ñ ÐºÑ€Ð¾Ð½Ð°')
67, (N'EUR', N'Евро')
68, (N'AUD', N'ÐвÑтралийÑкий доллар')
69, (N'NZD', N'ÐовозеландÑкий доллар')
70GO
71
72
73CREATE TRIGGER InsertRate ON Rate
74AFTER INSERT
75AS
76BEGIN
77 DECLARE @val FLOAT
78 DECLARE @t1 NVARCHAR(3)
79 DECLARE @t2 NVARCHAR(3)
80 DECLARE c CURSOR FOR SELECT *
81 FROM INSERTED
82 OPEN c
83 FETCH NEXT FROM c
84 INTO @t1, @t2, @val
85 WHILE @@fetch_status = 0
86 BEGIN
87 INSERT INTO Rate VALUES (
88 @t2, @t1, ROUND(1 / @val, 4)
89 )
90 FETCH NEXT FROM c
91 INTO @t1, @t2, @val
92 END
93 CLOSE c
94 DEALLOCATE c
95END
96go
97
98
99CREATE TRIGGER UpdateRate ON Rate
100AFTER UPDATE
101AS
102BEGIN
103 DECLARE @val FLOAT
104 DECLARE @t1 NVARCHAR(3)
105 DECLARE @t2 NVARCHAR(3)
106 DECLARE c CURSOR FOR SELECT *
107 FROM INSERTED
108 OPEN c
109 FETCH NEXT FROM c
110 INTO @t1, @t2, @val
111 WHILE @@fetch_status = 0
112 BEGIN
113 UPDATE Rate
114 SET valueR = 1 / @val
115 WHERE valFrom = @t2 AND valTo = @t1
116 FETCH NEXT FROM c
117 INTO @t1, @t2, @val
118 END
119 CLOSE c
120 DEALLOCATE c
121END
122
123ALTER TABLE Rate ENABLE TRIGGER InsertRate
124GO
125ALTER TABLE Rate ENABLE TRIGGER UpdateRate
126GO
127
128
129INSERT INTO Rate VALUES
130(N'USD', N'JPY', 78.065)
131, (N'USD', N'CHF', 0.9011)
132, (N'USD', N'CAD', 1.0134)
133, (N'USD', N'DKK', 5.4064)
134, (N'USD', N'SEK', 6.562)
135, (N'USD', N'EUR', 0.7263)
136, (N'USD', N'AUD', 0.9639)
137, (N'USD', N'NZD', 1.2549)
138, (N'GBP', N'JPY', 125.31)
139, (N'GBP', N'CHF', 1.4467)
140, (N'GBP', N'CAD', 1.6268)
141, (N'GBP', N'DKK', 8.6788)
142, (N'GBP', N'SEK', 10.5285)
143, (N'GBP', N'EUR', 1.1658)
144, (N'GBP', N'AUD', 1.5473)
145, (N'GBP', N'NZD', 2.0144)
146, (N'JPY', N'CAD', 0.0115)
147, (N'JPY', N'CHF', 0.013)
148, (N'JPY', N'DKK', 0.0692)
149, (N'JPY', N'SEK', 8.4)
150, (N'JPY', N'EUR', 0.0093)
151, (N'JPY', N'AUD', 0.0123)
152, (N'JPY', N'NZD', 0.0161)
153, (N'CHF', N'CAD', 1.1246)
154, (N'CHF', N'DKK', 0.06)
155, (N'CHF', N'SEK', 7.2825)
156, (N'CHF', N'EUR', 0.8059)
157, (N'CHF', N'AUD', 1.0696)
158, (N'CHF', N'NZD', 1.3924)
159, (N'CAD', N'DKK', 5.335)
160, (N'CAD', N'SEK', 6.4754)
161, (N'CAD', N'EUR', 0.7166)
162, (N'CAD', N'AUD', 0.9512)
163, (N'CAD', N'NZD', 1.2383)
164, (N'DKK', N'SEK', 1.2137)
165, (N'DKK', N'EUR', 0.1343)
166, (N'DKK', N'AUD', 0.1783)
167, (N'DKK', N'NZD', 0.2322)
168, (N'SEK', N'EUR', 0.1107)
169, (N'SEK', N'AUD', 0.1469)
170, (N'SEK', N'NZD', 0.1912)
171, (N'EUR', N'AUD', 1.3273)
172, (N'EUR', N'NZD', 1.7279)
173, (N'AUD', N'NZD', 1.3018)
174go
175
176
177INSERT INTO Rate VALUES
178(N'GBP', N'USD', 7.003 )
179go
180
181
182
183CREATE PROCEDURE CostPocket(@out_cur NVARCHAR(3))
184AS
185BEGIN
186 DECLARE @sum FLOAT
187 SET @out_cur = UPPER(@out_cur)
188 SET @sum = 0
189 DECLARE @cur NVARCHAR(3)
190 DECLARE @amount FLOAT
191 DECLARE @t FLOAT
192 DECLARE @m INT
193 DECLARE @ma NVARCHAR(80)
194 SET @m = (SELECT count(valLetters)
195 FROM NameVal
196 WHERE valLetters = @out_cur)
197 SET @ma = (SELECT valName
198 FROM NameVal
199 WHERE valLetters = @out_cur)
200 IF (@m <> 1)
201 BEGIN
202 RAISERROR (N'Валюта %s не ÑущеÑтвует', 16, 1, @out_cur)
203 RETURN
204 END
205 DECLARE c CURSOR FOR
206 SELECT *
207 FROM pocket
208 OPEN c
209 FETCH NEXT FROM c
210 INTO @cur, @amount
211 WHILE @@fetch_status = 0
212 BEGIN
213 SELECT @t = valueR
214 FROM Rate
215 WHERE valFrom = @cur AND valTo = @out_cur
216 IF (@@rowcount <> 1 AND @cur <> @out_cur)
217 BEGIN
218 RAISERROR (N'Ðет курÑа Ð´Ð»Ñ %s %s ', 16, 1, @cur, @out_cur)
219 RETURN
220 END
221 IF (@out_cur = @cur)
222 SET @t = 1
223 SET @sum += @t * @amount
224 FETCH NEXT FROM c
225 INTO @cur, @amount
226 END
227 CLOSE c
228 DEALLOCATE c
229 PRINT N'В кошельке: ' + convert(NVARCHAR, round(@sum, 2)) + ' (' + convert(NVARCHAR, @ma) + ')'
230END
231GO
232
233
234CREATE PROCEDURE AddMoney(@sum float, @val NVARCHAR(3))
235as
236IF (@sum < 0)
237 BEGIN
238 RAISERROR (N'ÐÐµÐ»ÑŒÐ·Ñ Ð´Ð¾Ð±Ð°Ð²Ð¸Ñ‚ÑŒ отрицательную Ñумму', 16 ,1)
239 RETURN
240 END
241declare @qq float
242select @qq = amount from Pocket where val = @val
243 IF (@@ROWCOUNT <> 1)
244 BEGIN
245 RAISERROR (N'Данной валюты нет в Ñправочнике', 16 ,1)
246 RETURN
247 END
248UPDATE Pocket
249 SET amount = amount + @sum
250where val = @val
251go
252
253
254
255CREATE PROCEDURE PrintTable
256AS
257BEGIN
258 DECLARE @idList VARCHAR(500)
259 SELECT @idList = COALESCE(@idList + ', ', '') + valLetters
260 FROM NameVal
261 DECLARE @k NVARCHAR(1024)
262 SET @k = '
263 SELECT *
264 FROM ( SELECT *
265 FROM Rate
266 UNION SELECT valLetters, valLetters, 1
267 FROM NameVal) a
268 PIVOT (max(valueR) FOR valTo IN (' + @idList + ')) as PivotTable'
269 EXEC (@k)
270END
271GO
272
273
274CREATE PROCEDURE TakeMoney(@amount INT, @valLet nvarchar(3))
275AS
276 BEGIN
277 IF (@amount < 0)
278 BEGIN
279 RAISERROR (N'Сумма денег отрицательна', 16, 1)
280 RETURN
281 END
282 declare @t nvarchar(3)
283 SELECT @t = valLetters
284 FROM NameVal
285 WHERE valLetters = + @valLet
286 IF (@@ROWCOUNT <> 1)
287 BEGIN
288 RAISERROR (N'Данной валюты нет в Ñправочнике', 16 ,1)
289 RETURN
290 END
291 DECLARE @rr nvarchar(3)
292 SELECT @rr = @valLet
293 FROM Pocket
294 WHERE val = @valLet
295 IF (@@ROWCOUNT = 1)
296 BEGIN
297 DECLARE @r nvarchar(3)
298 SELECT @r = @valLet
299 FROM Pocket
300 WHERE val = @valLet AND amount < @amount
301 IF (@@ROWCOUNT = 1)
302 BEGIN
303 RAISERROR (N'Ð’ кошельке Ñтолько нет', 16 ,1)
304 RETURN
305 END
306 ELSE
307 BEGIN
308
309 print N'amount'
310 UPDATE Pocket SET amount = amount - @amount
311 WHERE val = @valLet
312
313
314 delete from Pocket where amount = 0
315 END
316 END
317 ELSE
318 BEGIN
319 RAISERROR (N'Данной валюты нет в кошельке', 16, 1)
320 RETURN
321 END
322 END
323GO
324
325CREATE PROCEDURE SeeMoney
326as
327begin
328select *
329from Pocket
330group by amount, val
331end
332go
333
334
335INSERT INTO Pocket VALUES
336(N'EUR', 40)
337,(N'NZD', 10)
338,(N'USD', 14)
339,(N'AUD', 50)
340,(N'CAD', 7)
341,(N'CHF', 8)
342,(N'DKK', 45)
343,(N'SEK', 60)
344,(N'JPY', 9)
345go
346
347
348
349exec AddMoney 400, N'NZD'
350go
351
352
353exec AddMoney 7, N'CAD'
354go
355
356exec SeeMoney
357go
358
359
360EXEC TakeMoney 14, N'CAD'
361GO
362
363exec SeeMoney
364go
365
366EXEC TakeMoney 10, N'SEK'
367GO
368
369EXEC TakeMoney 7, N'EUR'
370GO
371
372select * from Pocket
373go
374
375EXEC PrintTable
376go
377
378EXEC CostPocket N'USD'