· 9 years ago · Nov 28, 2016, 06:32 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 if(@r = @amount)
310 begin
311 delete from Pocket where @r = @amount
312 print N'233'
313 end
314 else
315 begin
316 UPDATE Pocket SET amount = amount - @amount
317 WHERE val = @valLet
318 end
319 END
320 END
321 ELSE
322 BEGIN
323 RAISERROR (N'Данной валюты нет в кошельке', 16, 1)
324 RETURN
325 END
326 END
327GO
328
329CREATE PROCEDURE SeeMoney
330as
331begin
332select *
333from Pocket
334group by amount, val
335end
336go
337
338
339INSERT INTO Pocket VALUES
340(N'EUR', 40)
341,(N'NZD', 10)
342,(N'USD', 14)
343,(N'AUD', 50)
344,(N'CAD', 7)
345,(N'CHF', 8)
346,(N'DKK', 45)
347,(N'SEK', 60)
348,(N'JPY', 9)
349go
350
351
352
353exec AddMoney 400, N'NZD'
354go
355
356
357exec AddMoney 7, N'CAD'
358go
359exec SeeMoney
360go
361
362
363EXEC TakeMoney 7, N'CAD'
364GO
365exec SeeMoney
366go
367
368EXEC TakeMoney 10, N'SEK'
369GO
370
371EXEC TakeMoney 7, N'EUR'
372GO
373
374select * from Pocket
375go
376
377EXEC PrintTable
378go
379
380EXEC CostPocket N'USD'