· 9 years ago · Nov 27, 2016, 05:00 PM
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
181INSERT INTO Pocket VALUES
182(N'EUR', 40)
183,(N'NZD', 10)
184,(N'USD', 14)
185,(N'AUD', 50)
186,(N'CAD', 7)
187,(N'CHF', 8)
188,(N'DKK', 45)
189,(N'SEK', 60)
190,(N'JPY', 9)
191go
192
193
194CREATE PROCEDURE CostPocket(@out_cur NVARCHAR(3))
195AS
196BEGIN
197 DECLARE @sum FLOAT
198 SET @out_cur = UPPER(@out_cur)
199 SET @sum = 0
200 DECLARE @cur NVARCHAR(3)
201 DECLARE @amount FLOAT
202 DECLARE @t FLOAT
203 DECLARE @m INT
204 DECLARE @ma NVARCHAR(80)
205 SET @m = (SELECT count(valLetters)
206 FROM NameVal
207 WHERE valLetters = @out_cur)
208 SET @ma = (SELECT valName
209 FROM NameVal
210 WHERE valLetters = @out_cur)
211 IF (@m <> 1)
212 BEGIN
213 RAISERROR (N'Валюта %s не ÑущеÑтвует', 16, 1, @out_cur)
214 RETURN
215 END
216 DECLARE c CURSOR FOR
217 SELECT *
218 FROM pocket
219 OPEN c
220 FETCH NEXT FROM c
221 INTO @cur, @amount
222 WHILE @@fetch_status = 0
223 BEGIN
224 SELECT @t = valueR
225 FROM Rate
226 WHERE valFrom = @cur AND valTo = @out_cur
227 IF (@@rowcount <> 1 AND @cur <> @out_cur)
228 BEGIN
229 RAISERROR (N'Ðет курÑа Ð´Ð»Ñ %s %s ', 16, 1, @cur, @out_cur)
230 RETURN
231 END
232 IF (@out_cur = @cur)
233 SET @t = 1
234 SET @sum += @t * @amount
235 FETCH NEXT FROM c
236 INTO @cur, @amount
237 END
238 CLOSE c
239 DEALLOCATE c
240 PRINT N'В кошельке: ' + convert(NVARCHAR, round(@sum, 2)) + ' (' + convert(NVARCHAR, @ma) + ')'
241END
242GO
243
244
245
246CREATE PROCEDURE PrintTable
247AS
248BEGIN
249 DECLARE @idList VARCHAR(500)
250 SELECT @idList = COALESCE(@idList + ', ', '') + valLetters
251 FROM NameVal
252 DECLARE @k NVARCHAR(1024)
253 SET @k = '
254 SELECT *
255 FROM ( SELECT *
256 FROM Rate
257 UNION SELECT valLetters, valLetters, 1
258 FROM NameVal) a
259 PIVOT (max(valueR) FOR valTo IN (' + @idList + ')) as PivotTable'
260 EXEC (@k)
261END
262GO
263
264
265CREATE PROCEDURE TakeMoney(@amount INT, @valLet nvarchar(3))
266AS
267 BEGIN
268 IF (@amount < 0)
269 BEGIN
270 RAISERROR (N'Сумма денег отрицательна', 16, 1)
271 RETURN
272 END
273 declare @t nvarchar(3)
274 SELECT @t = valLetters
275 FROM NameVal
276 WHERE valLetters = + @valLet
277 IF (@@ROWCOUNT <> 1)
278 BEGIN
279 RAISERROR (N'Данной валюты нет в Ñправочнике', 16 ,1)
280 RETURN
281 END
282 DECLARE @rr nvarchar(3)
283 SELECT @rr = @valLet
284 FROM Pocket
285 WHERE val = @valLet
286 IF (@@ROWCOUNT = 1)
287 BEGIN
288 DECLARE @r nvarchar(3)
289 SELECT @r = @valLet
290 FROM Pocket
291 WHERE val = @valLet AND amount < @amount
292 IF (@@ROWCOUNT = 1)
293 BEGIN
294 RAISERROR (N'Ð’ кошельке Ñтолько нет', 16 ,1)
295 RETURN
296 END
297 ELSE
298 BEGIN
299 UPDATE Pocket SET amount = amount - @amount
300 WHERE val = @valLet
301 END
302 END
303 ELSE
304 BEGIN
305 RAISERROR (N'Данной валюты нет в кошельке', 16, 1)
306 RETURN
307 END
308 END
309GO
310
311select * from Pocket
312go
313
314EXEC TakeMoney 10, N'USD'
315GO
316
317EXEC TakeMoney 10, N'SEK'
318GO
319
320EXEC TakeMoney 7, N'EUR'
321GO
322
323select * from Pocket
324go
325
326EXEC PrintTable
327go
328
329EXEC CostPocket N'USD'