· 5 years ago · Mar 31, 2020, 06:40 AM
1USE Lista3;
2GO
3
4DROP TABLE IF EXISTS Towary;
5GO
6
7DROP TABLE IF EXISTS Kursy;
8GO
9
10CREATE TABLE Towary(
11 ID INT IDENTITY,
12 NazwaTowaru VARCHAR(30),
13 CONSTRAINT Towary_PK PRIMARY KEY (Id)
14)
15GO
16
17CREATE TABLE Kursy(
18 Waluta VARCHAR(3),
19 CenaPLN FLOAT,
20 CONSTRAINT Kursy_PK PRIMARY KEY (Waluta)
21)
22GO
23
24SET IDENTITY_INSERT Towary ON
25INSERT INTO Towary (ID,NazwaTowaru) VALUES
26 (1,'Bułka'),
27 (2,'Mleko'),
28 (3,'Masło'),
29 (4,'Ser'),
30 (5,'Jogurt'),
31 (6,'Banan'),
32 (7,'Jabłko'),
33 (8,'Pomidor'),
34 (9,'Ogórek'),
35 (10,'Makaron');
36SET IDENTITY_INSERT Towary OFF
37GO
38
39INSERT INTO Kursy (Waluta, CenaPLN) VALUES
40 ('PLN', 1.0000),
41 ('EUR', 4.5395),
42 ('USD',4.0933),
43 ('CHF', 4.2840),
44 ('GBP', 5.0698),
45 ('HRK', 0.5962),
46 ('HUF', 1.2708),
47 ('CZK', 0.1654),
48 ('MXN', 0.1733);
49GO
50
51DROP TABLE IF EXISTS Ceny;
52GO
53
54CREATE TABLE Ceny(
55 TowarID INT,
56 Waluta VARCHAR(3),
57 Cena FLOAT,
58 CONSTRAINT Ceny_PK PRIMARY KEY (TowarID, Waluta)
59)
60GO
61
62INSERT INTO Ceny (TowarID, Waluta, Cena) VALUES
63 (1,'PLN', 0.60),
64 (2,'PLN', 2.99),
65 (3,'PLN', 4.99),
66 (4,'PLN', 4.99),
67 (5,'PLN', 0.79),
68 (6,'PLN', 1.20),
69 (7,'PLN', 0.40),
70 (8,'PLN', 0.80),
71 (9,'PLN', 1.40),
72 (10,'PLN', 3.99);
73GO
74
75DROP PROCEDURE IF EXISTS uaktualnij_ceny
76GO
77
78CREATE PROCEDURE uaktualnij_ceny AS
79BEGIN
80 DELETE FROM Ceny
81 WHERE Waluta NOT IN(
82 SELECT Waluta FROM Kursy
83 )
84
85 DECLARE kursor_ceny CURSOR FOR
86 SELECT
87 TowarID,
88 Waluta
89 FROM Ceny
90 WHERE NOT Waluta = 'PLN'
91 ORDER BY TowarID, Waluta
92
93 DECLARE kursor_cenyPLN CURSOR FOR
94 SELECT
95 TowarID,
96 Cena
97 FROM
98 Ceny
99 WHERE Waluta = 'PLN'
100 ORDER BY TowarID
101
102 DECLARE kursor_waluty CURSOR FOR
103 SELECT
104 Waluta,
105 CenaPLN
106 FROM
107 Kursy
108 WHERE NOT Waluta = 'PLN'
109 ORDER BY Waluta
110
111 DECLARE @TowarID INT, @waluta VARCHAR(3)
112 DECLARE @TowarPLNID INT, @cenaPLN FLOAT
113 DECLARE @walutaID VARCHAR(3), @kurs FLOAT
114
115 open kursor_ceny
116 open kursor_cenyPLN
117 fetch next from kursor_ceny into @TowarID, @waluta
118 fetch next from kursor_cenyPLN into @TowarPLNID, @cenaPLN
119 while(@@FETCH_STATUS = 0) --@@fetch_status always contains status of most recent fetch
120 begin
121 open kursor_waluty
122 fetch next from kursor_waluty into @walutaID, @kurs
123 while(@@FETCH_STATUS = 0)
124 begin
125 IF @walutaID = @waluta
126 begin
127 UPDATE Ceny SET Cena= @cenaPLN * @kurs where current of kursor_ceny
128 fetch next from kursor_ceny into @TowarID, @waluta
129 fetch next from kursor_waluty into @walutaID, @kurs
130 end
131 ELSE
132 begin
133 INSERT INTO Ceny (TowarID, Waluta, Cena) VALUES (@TowarPLNID, @walutaID, @cenaPLN * @kurs)
134 fetch next from kursor_waluty into @walutaID, @kurs
135 end
136 end
137 close kursor_waluty
138 fetch next from kursor_cenyPLN into @TowarPLNID, @cenaPLN
139 end
140 close kursor_cenyPLN
141 close kursor_ceny
142
143 DEALLOCATE kursor_ceny
144 DEALLOCATE kursor_cenyPLN
145 DEALLOCATE kursor_waluty
146END
147GO
148
149SELECT
150 NazwaTowaru,
151 Waluta,
152 cast(Cena as decimal(10,2)) as Cena
153FROM
154 Towary tow
155 JOIN Ceny cen ON tow.ID = cen.TowarID
156GO
157
158EXEC uaktualnij_ceny
159
160SELECT
161 NazwaTowaru,
162 Waluta,
163 cast(Cena as decimal(10,2)) as Cena
164FROM
165 Towary tow
166 JOIN Ceny cen ON tow.ID = cen.TowarID
167GO
168
169UPDATE Kursy SET CenaPLN = 4.8023 WHERE Waluta = 'EUR'
170DELETE FROM Kursy WHERE Waluta = 'HRK'
171INSERT INTO Kursy (Waluta, CenaPLN) VALUES ('BRL', 0.8024)
172
173EXEC uaktualnij_ceny
174
175SELECT
176 NazwaTowaru,
177 Waluta,
178 cast(Cena as decimal(10,2)) as Cena
179FROM
180 Towary tow
181 JOIN Ceny cen ON tow.ID = cen.TowarID
182GO