· 9 years ago · Nov 14, 2016, 07:06 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 CValue
32(
33IdFrom nvarchar(4) not null,
34IdTo nvarchar(4) not null,
35Rate float(6) not null,
36CONSTRAINT IdV PRIMARY KEY (IdFrom)
37)
38GO
39
40CREATE TABLE MyMoney
41(
42IdVal nvarchar(4) not null,
43SumMoney money not null,
44CONSTRAINT Id FOREIGN KEY (IdVal) REFERENCES CValue(IdFrom)
45)
46GO
47
48CREATE FUNCTION Converting () RETURN money
49AS
50BEGIN
51DECLARE @an money
52 SET @an = (SELECT Rate
53 FROM CValue
54 WHERE IdTo = N'USD')
55
56 set @mon = (SELECT SumMoney
57 FROM MyMoney
58 WHERE @mon = @an)
59RETURN (@an*@mon)
60END
61
62
63
64INSERT INTO CValue
65VALUES
66(N'USD', N'USD', 1)
67,(N'USD', N'GBP', 0.623)
68,(N'USD', N'JPY', 78.065)
69,(N'USD', N'USD', 0.9011)
70,(N'JPY', N'USD', 0.9011)
71,(N'GBP', N'USD', 0.9011)
72,(N'CHF', N'USD', 0.6)
73GO
74
75INSERT INTO MyMoney
76VALUES
77(N'GBP', 30.2)
78,(N'CHF', 14)
79,(N'JPY', 10)
80GO