· 9 years ago · Dec 19, 2016, 05:36 AM
1USE master
2
3IF EXISTS (
4 SELECT name
5 FROM sys.databases
6 WHERE name = N'Mamonov'
7)
8ALTER DATABASE [Mamonov] set single_user with rollback immediate
9GO
10
11if EXISTS (
12 SELECT name
13 FROM sys.databases
14 WHERE name = N'Mamonov'
15)
16DROP DATABASE [Mamonov]
17GO
18
19CREATE DATABASE [Mamonov]
20GO
21
22USE [Mamonov]
23GO
24
25IF EXISTS (
26 SELECT *
27 FROM sys.schemas
28 WHERE name = N'Валюты'
29)
30DROP SCHEMA [Валюты]
31GO
32
33CREATE SCHEMA [Валюты]
34GO
35
36IF OBJECT_ID('[Mamonov].[Валюты].change', 'U') IS NOT NULL
37 DROP TABLE [Mamonov].[Валюты].change
38GO
39
40CREATE TABLE [Mamonov].[Валюты].change
41(
42 Sell nvarchar(3) NOT NULL,
43 Buy nvarchar(3) NOT NULL,
44 Value float NOT NULL
45)
46GO
47
48IF OBJECT_ID('[Валюты].duplicate_trigger', 'TR') IS NOT NULL
49 DROP TRIGGER [Валюты].duplicate_trigger
50GO
51
52CREATE TRIGGER [Валюты].duplicate_trigger
53ON [Mamonov].[Валюты].change
54AFTER INSERT
55AS
56IF EXISTS (
57 SELECT this.Sell, this.Buy
58 FROM [Mamonov].[Валюты].change this, [Mamonov].[Валюты].change that
59 WHERE this.Sell = that.Sell AND this.Buy = that.Buy
60 GROUP BY this.Sell, this.Buy
61 HAVING count(*) > 1
62)
63BEGIN
64 RAISERROR ('Введена Ð¿Ð¾Ð²Ñ‚Ð¾Ñ€Ð½Ð°Ñ Ð·Ð°Ð¿Ð¸ÑÑŒ', 10, 1)
65 ROLLBACK TRANSACTION
66 RETURN
67END
68GO
69
70INSERT INTO [Mamonov].[Валюты].change (Sell, Buy, Value)
71VALUES
72 ('USD', 'USD', 1),
73 ('USD', 'GBP', 0.623),
74 ('USD', 'JPY', 78.065),
75 ('USD', 'CHF', 0.9011),
76 ('USD', 'CAD', 1.0134),
77 ('USD', 'DKK', 5.4064),
78 ('USD', 'SEK', 6.562),
79 ('USD', 'EUR', 0.7263),
80 ('USD', 'AUD', 0.9639),
81 ('USD', 'NZD', 1.2549),
82 ('GBP', 'USD', 1.6052),
83 ('GBP', 'GBP', 1),
84 ('GBP', 'JPY', 125.31),
85 ('GBP', 'CHF', 1.4467),
86 ('GBP', 'CAD', 1.6268),
87 ('GBP', 'DKK', 8.6788),
88 ('GBP', 'SEK', 10.5285),
89 ('GBP', 'EUR', 1.1658),
90 ('GBP', 'AUD', 1.5473),
91 ('GBP', 'NZD', 2.0144),
92 ('JPY', 'USD', 0.0128),
93 ('JPY', 'GBP', 0.008),
94 ('JPY', 'JPY', 1),
95 ('JPY', 'CHF', 0.0115),
96 ('JPY', 'CAD', 0.013),
97 ('JPY', 'DKK', 0.0692),
98 ('JPY', 'SEK', 8.4),
99 ('JPY', 'EUR', 0.0093),
100 ('JPY', 'AUD', 0.0123),
101 ('JPY', 'NZD', 0.0161),
102 ('CHF', 'USD', 1.1098),
103 ('CHF', 'GBP', 0.6912),
104 ('CHF', 'JPY', 86.6275),
105 ('CHF', 'CHF', 1),
106 ('CHF', 'CAD', 1.1246),
107 ('CHF', 'DKK', 0.06),
108 ('CHF', 'SEK', 7.2825),
109 ('CHF', 'EUR', 0.8059),
110 ('CHF', 'AUD', 1.0696),
111 ('CHF', 'NZD', 1.3924),
112 ('CAD', 'USD', 0.9868),
113 ('CAD', 'GBP', 0.6147),
114 ('CAD', 'JPY', 77.0345),
115 ('CAD', 'CHF', 0.8892),
116 ('CAD', 'CAD', 1),
117 ('CAD', 'DKK', 5.335),
118 ('CAD', 'SEK', 6.4754),
119 ('CAD', 'EUR', 0.7166),
120 ('CAD', 'AUD', 0.9512),
121 ('CAD', 'NZD', 1.2383),
122 ('DKK', 'USD', 0.185),
123 ('DKK', 'GBP', 0.1152),
124 ('DKK', 'JPY', 14.442),
125 ('DKK', 'CHF', 16.67),
126 ('DKK', 'CAD', 0.1874),
127 ('DKK', 'DKK', 1),
128 ('DKK', 'SEK', 1.2137),
129 ('DKK', 'EUR', 0.1343),
130 ('DKK', 'AUD', 0.1783),
131 ('DKK', 'NZD', 0.2322),
132 ('SEK', 'USD', 0.1524),
133 ('SEK', 'GBP', 0.095),
134 ('SEK', 'JPY', 0.119),
135 ('SEK', 'CHF', 0.1373),
136 ('SEK', 'CAD', 0.1544),
137 ('SEK', 'DKK', 0.8239),
138 ('SEK', 'SEK', 1),
139 ('SEK', 'EUR', 0.1107),
140 ('SEK', 'AUD', 0.1469),
141 ('SEK', 'NZD', 0.1912),
142 ('EUR', 'USD', 1.3769),
143 ('EUR', 'GBP', 0.8578),
144 ('EUR', 'JPY', 107.495),
145 ('EUR', 'CHF', 1.2409),
146 ('EUR', 'CAD', 1.3954),
147 ('EUR', 'DKK', 7.4443),
148 ('EUR', 'SEK', 9.0357),
149 ('EUR', 'EUR', 1),
150 ('EUR', 'AUD', 1.3273),
151 ('EUR', 'NZD', 1.7279),
152 ('AUD', 'USD', 1.0374),
153 ('AUD', 'GBP', 0.6463),
154 ('AUD', 'JPY', 80.979),
155 ('AUD', 'CHF', 0.9349),
156 ('AUD', 'CAD', 1.0513),
157 ('AUD', 'DKK', 5.6086),
158 ('AUD', 'SEK', 6.8074),
159 ('AUD', 'EUR', 0.7534),
160 ('AUD', 'AUD', 1),
161 ('AUD', 'NZD', 1.3018),
162 ('NZD', 'USD', 0.7969),
163 ('NZD', 'GBP', 0.4964),
164 ('NZD', 'JPY', 62.1118),
165 ('NZD', 'CHF', 0.7182),
166 ('NZD', 'CAD', 0.8076),
167 ('NZD', 'DKK', 4.3066),
168 ('NZD', 'SEK', 5.2293),
169 ('NZD', 'EUR', 0.5787),
170 ('NZD', 'AUD', 0.7682),
171 ('NZD', 'NZD', 1)
172GO
173
174IF OBJECT_ID('[Mamonov].[Валюты].purse', 'U') IS NOT NULL
175 DROP TABLE [Mamonov].[Валюты].purse
176GO
177
178CREATE TABLE [Mamonov].[Валюты].purse
179(
180 Currency nvarchar(3) NOT NULL,
181 Value money NOT NULL
182)
183GO
184
185INSERT INTO [Mamonov].[Валюты].purse (Currency, Value)
186VALUES
187 ('EUR', 1),
188 ('USD', 5)
189GO
190
191IF OBJECT_ID('[Валюты].getMoney', 'FN') IS NOT NULL
192 DROP FUNCTION [Валюты].getMoney
193GO
194
195-- Содержимое кошелька
196CREATE FUNCTION [Валюты].getMoney()
197RETURNS TABLE
198AS
199RETURN (
200 SELECT Currency AS 'Валюта', ROUND(Value, 2) AS 'КоличеÑтво'
201 FROM [Mamonov].[Валюты].purse
202)
203GO
204
205SELECT * FROM [Валюты].getMoney()
206GO
207
208IF OBJECT_ID('[Валюты].getMoneyInCurrency', 'FN') IS NOT NULL
209 DROP FUNCTION [Валюты].getMoneyInCurrency
210GO
211
212-- ПодÑчёт Ñодержимого кошелька в заданной валюте
213CREATE FUNCTION [Валюты].getMoneyInCurrency (@currency nvarchar(3))
214RETURNS TABLE
215AS
216RETURN (
217 SELECT ROUND(SUM(purse.Value * change.Value), 2) AS 'Сумма'
218 FROM [Mamonov].[Валюты].purse AS purse
219 INNER JOIN [Mamonov].[Валюты].change AS change
220 ON purse.Currency = change.Sell AND change.Buy = @currency
221)
222GO
223
224SELECT * FROM [Mamonov].[Валюты].getMoneyInCurrency('USD')
225GO
226
227IF OBJECT_ID('[Валюты].noteDown', 'P') IS NOT NULL
228 DROP PROCEDURE [Валюты].noteDown
229GO
230
231-- ЗанеÑение ÑредÑтв в кошелёк
232CREATE PROCEDURE [Валюты].noteDown (@currency nvarchar(3), @value money)
233AS
234 IF NOT EXISTS (SELECT Sell FROM [Mamonov].[Валюты].change WHERE Sell = @currency)
235 BEGIN
236 RAISERROR ('ÐеизвеÑÑ‚Ð½Ð°Ñ Ð²Ð°Ð»ÑŽÑ‚Ð°', 10, 1)
237 RETURN
238 END
239 IF EXISTS (SELECT Currency FROM [Mamonov].[Валюты].purse WHERE Currency = @currency)
240 UPDATE [Mamonov].[Валюты].purse
241 SET Value = Value + @value
242 WHERE Currency = @currency
243 ELSE
244 INSERT INTO [Mamonov].[Валюты].purse (Currency, Value)
245 VALUES (@currency, @value)
246GO
247
248EXEC [Валюты].noteDown 'GBP', 10
249GO
250
251IF OBJECT_ID('[Валюты].withdraw', 'P') IS NOT NULL
252 DROP PROCEDURE [Валюты].withdraw
253GO
254
255-- СпиÑание ÑредÑтв из кошелька
256CREATE PROCEDURE [Валюты].withdraw (@currency nvarchar(3), @value money)
257AS
258 DECLARE @value_in_purse money = (SELECT Value FROM [Mamonov].[Валюты].purse WHERE Currency = @currency)
259 IF @value_in_purse IS NULL
260 BEGIN
261 RAISERROR ('Такой валюты нет в кошельке', 10, 1)
262 RETURN
263 END
264 IF @value > @value_in_purse
265 BEGIN
266 RAISERROR ('Ðтой валюты Ñлишком мало', 10, 1)
267 RETURN
268 END
269 UPDATE [Mamonov].[Валюты].purse
270 SET Value = Value - @value
271 WHERE Currency = @currency
272 IF ((SELECT Value FROM [Mamonov].[Валюты].purse WHERE Currency = @currency) = 0)
273 DELETE FROM [Mamonov].[Валюты].purse
274 WHERE Currency = @currency
275GO
276
277EXEC [Валюты].withdraw 'GBP', 10
278GO
279
280DECLARE @idList varchar(500) = ''
281SELECT @idList +=
282 CASE
283 WHEN @idList NOT LIKE '%' + Sell + '%' THEN Sell + ', '
284 ELSE ''
285 END
286FROM [Mamonov].[Валюты].change
287SET @idList = SUBSTRING(@idList, 0, LEN(@idList))
288DECLARE @run varchar(500) =
289'SELECT *
290FROM [Mamonov].[Валюты].change
291PIVOT (
292 SUM(Value)
293 FOR Buy
294 IN (' + @idList + ')
295) AS pvt'
296EXEC (@run)
297GO