· 7 years ago · Dec 09, 2018, 06:08 PM
1USE master
2
3GO
4
5IF EXISTS (
6 SELECT name
7 FROM sys.databases
8 WHERE name = N'KB301_Eganov'
9)
10ALTER DATABASE KB301_Eganov set single_user with rollback immediate
11GO
12
13IF EXISTS (
14 SELECT name
15 FROM sys.databases
16 WHERE name = N'KB301_Eganov'
17)
18DROP DATABASE KB301_Eganov
19GO
20
21CREATE DATABASE KB301_Eganov
22GO
23
24USE KB301_Eganov
25GO
26
27IF EXISTS(
28 SELECT *
29 FROM sys.schemas
30 WHERE name = N'Eganov'
31)
32 DROP SCHEMA Eganov
33GO
34
35CREATE SCHEMA Eganov
36GO
37
38
39USE KB301_Eganov
40GO
41
42IF OBJECT_ID('Eganov.ExchangeRates', 'U') IS NOT NULL
43 DROP TABLE Eganov.ExchangeRates
44GO
45
46CREATE TABLE Eganov.ExchangeRates
47(
48 BuyCurrencyName nvarchar(4),
49 SellCurrencyName nvarchar(4),
50 Rate Decimal(5, 2),
51)
52GO
53
54IF OBJECT_ID('AddRate', 'TR') IS NOT NULL
55 DROP TRIGGER AddRate;
56GO
57
58CREATE TRIGGER AddRate ON Eganov.ExchangeRates
59AFTER INSERT
60AS
61 INSERT INTO Eganov.ExchangeRates
62 SELECT SellCurrencyName, BuyCurrencyName, ROUND(1 / Rate, 2) FROM inserted
63 WHERE SellCurrencyName != BuyCurrencyName
64GO
65
66IF OBJECT_ID('CheckRate', 'TR') IS NOT NULL
67 DROP TRIGGER CheckRate
68GO
69
70CREATE TRIGGER CheckRate ON Eganov.ExchangeRates
71INSTEAD OF INSERT
72AS
73 IF EXISTS(SELECT * FROM inserted
74 INNER JOIN Eganov.ExchangeRates
75 ON inserted.SellCurrencyName = Eganov.ExchangeRates.SellCurrencyName
76 AND inserted.BuyCurrencyName = Eganov.ExchangeRates.BuyCurrencyName)
77 BEGIN
78 RAISERROR (15600, -1, -1, 'Currency is already exists');
79 END
80 ELSE
81 INSERT INTO Eganov.ExchangeRates SELECT * FROM inserted;
82GO
83
84
85INSERT INTO Eganov.ExchangeRates
86 (BuyCurrencyName, SellCurrencyName, Rate)
87VALUES
88 ('rub', 'usd', 66.0),
89 ('rub', 'rub', 1.0),
90 ('rub', 'eur', 76.0),
91 ('usd', 'eur', 1.14),
92 ('usd', 'usd', 1.0),
93 ('eur', 'eur', 1.0),
94 ('rub', 'cny', 9.6),
95 ('usd', 'cny', 0.15),
96 ('eur', 'cny', 0.13),
97 ('cny', 'cny', 1.0)
98
99
100IF OBJECT_ID('Eganov.wallet', 'U') IS NOT NULL
101 DROP TABLE Eganov.wallet
102GO
103
104CREATE TABLE Eganov.wallet
105(
106 CurrencyName nvarchar(4) NOT NULL,
107 Value MONEY
108)
109GO
110
111
112IF OBJECT_ID('WalletInsert', 'TR') IS NOT NULL
113DROP TRIGGER WalletInsert
114GO
115
116CREATE TRIGGER WalletInsert ON Eganov.wallet
117INSTEAD OF INSERT
118AS
119 IF EXISTS(SELECT * FROM inserted
120 INNER JOIN Eganov.wallet
121 ON inserted.CurrencyName = Eganov.wallet.CurrencyName)
122 BEGIN
123 RAISERROR (15600, -1, -1, 'Currency is already in wallet');
124 END
125 ELSE
126 INSERT INTO Eganov.wallet SELECT * FROM inserted;
127GO
128
129IF OBJECT_ID('PutMoney', 'P') IS NOT NULL
130 DROP PROCEDURE PutMoney
131GO
132
133CREATE PROC PutMoney
134 @CurrencyName nvarchar,
135 @Value MONEY
136AS BEGIN
137 UPDATE Eganov.wallet
138 SET Value = Value + @Value
139 WHERE CurrencyName = @CurrencyName
140END
141GO
142
143IF OBJECT_ID('WithdrawMoney', 'P') IS NOT NULL
144 DROP PROCEDURE WithdrawMoney
145GO
146
147CREATE PROC WithdrawMoney
148 @CurrencyName nvarchar,
149 @Value MONEY
150AS BEGIN
151 IF EXISTS(SELECT * FROM Eganov.wallet
152 WHERE CurrencyName = @CurrencyName AND Value >= @Value)
153 BEGIN
154 SET @Value = -@Value;
155 EXEC PutMoney @CurrencyName, @Value;
156 END
157 ELSE
158 BEGIN
159 RAISERROR (15600, -1, -1, 'Not enough money in wallet');
160 END
161END
162GO
163
164IF OBJECT_ID('Balance', 'P') IS NOT NULL
165 DROP PROCEDURE Balance
166GO
167
168CREATE PROC Balance
169 @CurrencyName nvarchar
170AS BEGIN
171 SELECT ROUND(SUM(Value * er.Rate), 2) FROM Eganov.wallet
172 INNER JOIN Eganov.ExchangeRates as er
173 ON wallet.CurrencyName = er.SellCurrencyName AND er.BuyCurrencyName = @CurrencyName
174END
175GO
176
177INSERT INTO Eganov.wallet
178 (CurrencyName, Value)
179VALUES
180 ('rub', 11.0)
181
182
183BEGIN TRY
184 INSERT INTO Eganov.wallet
185 (CurrencyName, Value)
186 VALUES
187 ('rub', 3.0)
188END TRY
189BEGIN CATCH
190 PRINT(ERROR_MESSAGE())
191END CATCH
192
193
194INSERT INTO Eganov.wallet
195 (CurrencyName, Value)
196VALUES
197 ('usd', 1.0),
198 ('gbp', 1.0)
199
200
201EXEC PutMoney 'rub', 33.5;
202EXEC PutMoney 'usd', 9;
203EXEC PutMoney 'eur', 6;
204
205
206-- EXEC WithdrawMoney 'rub', 12.0;
207
208SELECT * FROM Eganov.wallet;
209SELECT * FROM Eganov.ExchangeRates ORDER BY BuyCurrencyName, SellCurrencyName;
210
211EXEC Balance 3;
212
213DECLARE @cols varchar(1000)
214
215SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.BuyCurrencyName)
216 FROM Eganov.ExchangeRates c
217 FOR XML PATH(''), TYPE
218 ).value('.', 'NVARCHAR(1000)')
219 ,1,1,'')
220SELECT @cols;
221
222DECLARE @query varchar(1000)
223
224set @query = 'SELECT [cur] as "Currency", ' + @cols + ' from
225 (
226 select BuyCurrencyName as cur, SellCurrencyName, Rate FROM Eganov.ExchangeRates
227 ) er
228 pivot
229 (
230 SUM(Rate)
231 for SellCurrencyName in (' + @cols + ')
232 ) p '
233
234execute(@query)