· 9 years ago · Nov 27, 2016, 11: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 tr_exchange_insert 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
98CREATE TRIGGER tr_exchange_update ON Rate
99AFTER UPDATE
100AS
101BEGIN
102 DECLARE @val FLOAT
103 DECLARE @t1 NVARCHAR(3)
104 DECLARE @t2 NVARCHAR(3)
105 DECLARE c CURSOR FOR SELECT *
106 FROM INSERTED
107 OPEN c
108 FETCH NEXT FROM c
109 INTO @t1, @t2, @val
110 WHILE @@fetch_status = 0
111 BEGIN
112 UPDATE Rate
113 SET valueR = 1 / @val
114 WHERE valFrom = @t2 AND valTo = @t1
115 FETCH NEXT FROM c
116 INTO @t1, @t2, @val
117 END
118 CLOSE c
119 DEALLOCATE c
120END
121
122ALTER TABLE Rate ENABLE TRIGGER tr_exchange_insert
123GO
124ALTER TABLE Rate ENABLE TRIGGER tr_exchange_update
125GO
126
127
128INSERT INTO Rate VALUES
129(N'USD', N'JPY', 78.065)
130, (N'USD', N'CHF', 0.9011)
131, (N'USD', N'CAD', 1.0134)
132, (N'USD', N'DKK', 5.4064)
133, (N'USD', N'SEK', 6.562)
134, (N'USD', N'EUR', 0.7263)
135, (N'USD', N'AUD', 0.9639)
136, (N'USD', N'NZD', 1.2549)
137, (N'GBP', N'JPY', 125.31)
138, (N'GBP', N'CHF', 1.4467)
139, (N'GBP', N'CAD', 1.6268)
140, (N'GBP', N'DKK', 8.6788)
141, (N'GBP', N'SEK', 10.5285)
142, (N'GBP', N'EUR', 1.1658)
143, (N'GBP', N'AUD', 1.5473)
144, (N'GBP', N'NZD', 2.0144)
145, (N'JPY', N'CAD', 0.0115)
146, (N'JPY', N'CHF', 0.013)
147, (N'JPY', N'DKK', 0.0692)
148, (N'JPY', N'SEK', 8.4)
149, (N'JPY', N'EUR', 0.0093)
150, (N'JPY', N'AUD', 0.0123)
151, (N'JPY', N'NZD', 0.0161)
152, (N'CHF', N'CAD', 1.1246)
153, (N'CHF', N'DKK', 0.06)
154, (N'CHF', N'SEK', 7.2825)
155, (N'CHF', N'EUR', 0.8059)
156, (N'CHF', N'AUD', 1.0696)
157, (N'CHF', N'NZD', 1.3924)
158, (N'CAD', N'DKK', 5.335)
159, (N'CAD', N'SEK', 6.4754)
160, (N'CAD', N'EUR', 0.7166)
161, (N'CAD', N'AUD', 0.9512)
162, (N'CAD', N'NZD', 1.2383)
163, (N'DKK', N'SEK', 1.2137)
164, (N'DKK', N'EUR', 0.1343)
165, (N'DKK', N'AUD', 0.1783)
166, (N'DKK', N'NZD', 0.2322)
167, (N'SEK', N'EUR', 0.1107)
168, (N'SEK', N'AUD', 0.1469)
169, (N'SEK', N'NZD', 0.1912)
170, (N'EUR', N'AUD', 1.3273)
171, (N'EUR', N'NZD', 1.7279)
172, (N'AUD', N'NZD', 1.3018)
173go
174
175
176INSERT INTO Rate VALUES
177(N'GBP', N'USD', 7.003 )
178go
179
180INSERT INTO Pocket VALUES
181(N'EUR', 40)
182,(N'NZD', 10)
183,(N'USD', 14)
184,(N'AUD', 50)
185,(N'CAD', 7)
186,(N'CHF', 8)
187,(N'DKK', 45)
188,(N'SEK', 60)
189,(N'JPY', 9)
190go
191
192
193CREATE PROCEDURE CostPocket(@out_cur NVARCHAR(3))
194AS
195BEGIN
196 DECLARE @sum FLOAT
197 SET @out_cur = UPPER(@out_cur)
198 SET @sum = 0
199 DECLARE @cur NVARCHAR(3)
200 DECLARE @amount FLOAT
201 DECLARE @t FLOAT
202 DECLARE @m INT
203 DECLARE @ma NVARCHAR(80)
204 SET @m = (SELECT count(valLetters)
205 FROM NameVal
206 WHERE valLetters = @out_cur)
207 SET @ma = (SELECT valName
208 FROM NameVal
209 WHERE valLetters = @out_cur)
210 IF (@m <> 1)
211 RAISERROR (N'Валюта %s не ÑущеÑтвует', 16, 1, @out_cur)
212 DECLARE c CURSOR FOR
213 SELECT *
214 FROM pocket
215 OPEN c
216 FETCH NEXT FROM c
217 INTO @cur, @amount
218 WHILE @@fetch_status = 0
219 BEGIN
220 SELECT @t = valueR
221 FROM Rate
222 WHERE valFrom = @cur AND valTo = @out_cur
223 IF (@out_cur = @cur)
224 SET @t = 1
225 IF (@@rowcount <> 1 AND @cur <> @out_cur)
226 RAISERROR (N'Ðет курÑа Ð´Ð»Ñ %s %s ', 16, 1, @cur, @out_cur)
227 SET @sum += @t * @amount
228 FETCH NEXT FROM c
229 INTO @cur, @amount
230 END
231 CLOSE c
232 DEALLOCATE c
233 PRINT N'В кошельке: ' + convert(NVARCHAR, @sum) + ' (' + convert(NVARCHAR, @ma) + ')'
234END
235GO
236
237
238
239CREATE PROCEDURE PrintTable
240AS
241BEGIN
242 DECLARE @idList VARCHAR(500)
243 SELECT @idList = COALESCE(@idList + ', ', '') + valLetters
244 FROM NameVal
245 DECLARE @k NVARCHAR(1024)
246 SET @k = '
247 SELECT *
248 FROM ( SELECT *
249 FROM Rate
250 UNION SELECT valLetters, valLetters, 1
251 FROM NameVal) a
252 PIVOT (max(valueR) FOR valTo IN (' + @idList + ')) as PivotTable'
253 EXEC (@k)
254END
255GO
256
257EXEC PrintTable
258go
259
260EXEC CostPocket N'USD'