sgDde131

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