· 9 years ago · Dec 19, 2016, 05:44 AM
1USE Master
2GO
3IF EXISTS (
4 SELECT name
5 FROM sys.databases
6 WHERE name = N'Lab3'
7)
8ALTER DATABASE [Lab3] set single_user with rollback immediate
9GO
10IF EXISTS (
11 SELECT name
12 FROM sys.databases
13 WHERE name = N'Lab3'
14)
15DROP DATABASE [Lab3]
16GO
17CREATE DATABASE [Lab3]
18Go
19USE [Lab3]
20GO
21
22CREATE TABLE Rate_info (
23 rate_id int NOT NULL identity primary key,
24 base_currency_id int NOT NULL,
25 quoted_currency_id int NOT NULL,
26 rate numeric(8,4) NOT NULL
27)
28GO
29
30CREATE TRIGGER Re_entry
31 on Rate_info
32 for insert
33 as
34 begin
35 if exists(
36 select rate_id
37 from Rate_info as r1
38 where rate_id in (
39 select rate_id from Rate_info as r2
40 where r1.rate_id != r2.rate_id and
41 r1.base_currency_id = r2.base_currency_id
42 and r1.quoted_currency_id = r2.quoted_currency_id
43 )
44 )
45 begin
46 ROLLBACK TRAN
47 PRINT
48 'Re-entry'
49 end
50 end
51GO
52
53CREATE TABLE Currency(
54 currency_id int NOT NULL primary key,
55 currency_name char(3) NOT NULL
56);
57GO
58
59insert into Currency values
60 (1, 'USD'),
61 (2, 'GBP'),
62 (3, 'JPN'),
63 (4, 'CHF'),
64 (5, 'CAD'),
65 (6, 'DKK'),
66 (7, 'SEK'),
67 (8, 'EUR'),
68 (9, 'AUD'),
69 (10, 'NZD');
70go
71
72insert into Rate_info values
73(1, 1, 1), (1, 2, 0.623), (1, 3, 78.065), (1, 4, 0.9011), (1, 5, 1.0134), (1, 6, 5.4064), (1, 7, 6.562), (1, 8, 0.7263), (1, 9, 0.9639), (1, 10, 1.2549),
74(2, 1, 1.6052), (2, 2, 1), (2, 3, 125.31), (2, 4, 1.4467), (2, 5, 1.6268), (2, 6, 8.6788), (2, 7, 10.5285), (2, 8, 1.1658), (2, 9, 1.5473), (2, 10, 2.0144),
75(3, 1, 0.0128), (3, 2, 0.008), (3, 3, 1), (3, 4, 0.0115), (3, 5, 0.013), (3, 6, 0.0692), (3, 7, 8.4), (3, 8, 0.0093), (3, 9, 0.0123), (3, 10, 0.0161),
76(4, 1, 1.1098), (4, 2, 0.6912), (4, 3, 86.6275), (4, 4, 1), (4, 5, 1.1246), (4, 6, 0.06), (4, 7, 7.2825), (4, 8, 0.8059), (4, 9, 1.0696), (4, 10, 1.3924),
77(5, 1, 0.9868), (5, 2, 0.6147), (5, 3, 77.0345), (5, 4, 0.8892), (5, 5, 1), (5, 6, 5.335), (5, 7, 6.4754), (5, 8, 0.7166), (5, 9, 0.9512), (5, 10, 1.2383),
78(6, 1, 0.185), (6, 2, 0.1152), (6, 3, 14.442), (6, 4, 16.67), (6, 5, 0.1874), (6, 6, 1), (6, 7, 1.2137), (6, 8, 0.1343), (6, 9, 0.1783), (6, 10, 0.2322),
79(7, 1, 0.1524), (7, 2, 0.095), (7, 3, 0.119), (7, 4, 0.1373), (7, 5, 0.1544), (7, 6, 0.8239),(7, 7, 1), (7, 8, 0.1107), (7, 9, 0.1469), (7, 10, 0.1912),
80(8, 1, 1.3769), (8, 2, 0.8578), (8, 3, 107.495), (8, 4, 1.2409), (8, 5, 1.3954), (8, 6, 7.4443), (8, 8, 9.0357), (4, 4, 1), (8, 9, 1.3273), (8, 10, 1.7279),
81(9, 1, 1.0374), (9, 2, 0.6463), (9, 3, 80.979), (9, 4, 0.9349), (9, 5, 1.0513), (9, 6, 5.6086), (9, 7, 6.8074), (9, 9, 0.7534), (9, 8, 1), (9, 10, 1.3018),
82(10, 1, 0.7969), (10, 2, 0.4964), (10, 3, 62.1118), (10, 4, 0.7182), (10, 5, 0.8076), (10, 6, 4.3066), (10, 7, 5.2293), (10, 8, 0.5787), (10, 9, 0.7682), (10, 10, 1)
83go
84
85CREATE TABLE Purse(
86 pocket_id int NOT NULL identity primary key,
87 currency_id int NOT NULL,
88 count_money numeric(8,4)
89)
90GO
91
92insert into Purse values
93 (1, 20),
94 (3, 115),
95 (4, 5),
96 (7, 1000),
97 (9, 33)
98go
99
100alter table Rate_info ADD
101 CONSTRAINT FKCurrency_ foreign key (base_currency_id)
102 references Currency (currency_id)
103
104alter table Rate_info ADD
105 CONSTRAINT FKCur_ foreign key (quoted_currency_id)
106 references Currency (currency_id)
107
108alter table Purse ADD
109 CONSTRAINT FKCurr_ foreign key (currency_id)
110 references Currency (currency_id)
111
112
113select SUM(Purse.count_money * rate) as Cost
114 INTO Cost FROM Rate_info
115 inner join Purse on Rate_info.base_currency_id = Purse.currency_id
116 WHERE EXISTS
117 (select rate from Purse
118 WHERE Rate_info.base_currency_id = Purse.currency_id and Rate_info.quoted_currency_id = 1
119 )
120select * from Cost
121go
122
123create procedure add_or_take_money
124 @target char(3),
125 @currency_id int,
126 @count_money numeric
127as
128 if @target in ('out', 'in')
129 if @target = 'in'
130 if exists
131 (select currency_id from Purse
132 where currency_id = @currency_id
133 )
134 begin
135 update Purse
136 set count_money = count_money + @count_money
137 WHERE currency_id = @currency_id
138 end
139 else
140 begin
141 insert into Purse values
142 (@currency_id, @count_money)
143 end
144 else
145 if exists
146 (select currency_id from Purse
147 where currency_id = @currency_id
148 and count_money >= @count_money
149 )
150 begin
151 update Purse
152 set count_money = count_money - @count_money
153 WHERE currency_id = @currency_id
154 delete from Purse
155 where currency_id = @currency_id and count_money = 0
156 end
157 else
158 RAISERROR ('ÐедоÑтаточно ÑредÑтв.',16,1, 'add_or_take_money')
159 else
160 RAISERROR (15600,-1,-1, 'add_or_take_money')
161go
162
163EXECUTE add_or_take_money @target = 'out', @currency_id = 1, @count_money = 20;
164
165select SUM(Purse.count_money * rate) as Cost
166 INTO Cost2 FROM Rate_info
167 inner join Purse on Rate_info.base_currency_id = Purse.currency_id
168 WHERE EXISTS
169 (select rate from Purse
170 WHERE Rate_info.base_currency_id = Purse.currency_id and Rate_info.quoted_currency_id = 1
171 )
172select * from Cost2
173
174
175select Currency.currency_name as ' ', Rate_info.rate as 'USD'
176into a from Rate_info
177 inner join Currency on Rate_info.base_currency_id = Currency.currency_id
178 where Rate_info.quoted_currency_id = 2
179select * from a