1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
----------------------
-- Первая процедура --
----------------------
CREATE PROCEDURE calculate_order_price
@order_id INT,
@price FLOAT OUTPUT
AS BEGIN
SET @price = 0.0;
DECLARE @delivery_id INT;
SELECT @delivery_id=delivery_id FROM Shipments WHERE order_id=@order_id;
IF @delivery_id IS NULL BEGIN
RAISERROR (N'Доставка для данного заказа ещё не оформлена', 10, 1);
RETURN;
END
DECLARE @gas_price FLOAT, @tonnage INT;
SELECT @gas_price=gas_consumption / 100.0 * distance, @tonnage=tonnage
FROM Deliveries
JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
JOIN Routes ON Deliveries.route_id = Routes.id;
DECLARE boxes_cursor CURSOR LOCAL
FOR SELECT volume, weight, price wrap_price
FROM Boxes JOIN Wrappings ON Boxes.wrapping_id = Wrappings.id
WHERE order_id=@order_id
OPEN boxes_cursor
DECLARE @volume FLOAT, @weight FLOAT, @wrap_price INT;
FETCH NEXT FROM boxes_cursor INTO @volume, @weight, @wrap_price;
WHILE @@FETCH_STATUS=0 BEGIN
SET @price = @price + @weight / CAST(@tonnage AS FLOAT) * @gas_price + @wrap_price;
FETCH NEXT FROM boxes_cursor INTO @volume, @weight, @wrap_price;
END
SET @price = @price + 1000;
END
GO
DROP PROCEDURE calculate_order_price
GO
INSERT INTO Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
VALUES (100020, 1, 1, 0.1, 10);
DECLARE @price INT;
EXEC calculate_order_price 100020, @price OUTPUT;
PRINT @price
GO
----------------------
-- Вторая процедура --
----------------------
CREATE PROCEDURE calculate_delivery_date
@route_id INT,
@order_date DATETIME,
@delivery_date DATETIME OUTPUT
AS BEGIN
DECLARE @distance FLOAT;
SELECT @distance=distance FROM Routes WHERE id=@route_id;
DECLARE @hours_driving FLOAT, @days_driving INT;
-- Скорость перевозки 70 километров в час
SET @hours_driving = @distance / 70.0;
-- 8 часов сна на 12 часов езды
SET @hours_driving = @hours_driving / 12.0 * 8.0;
SET @days_driving = CAST(@hours_driving / 24.0 + 1 AS INT);
-- Добавляем к дате заказа количество дней на поездку, один день
-- на обработку посылки в терминале отправки и один в терминале
-- приёма.
SET @delivery_date = DATEADD(day, @days_driving + 2, @order_date);
END
GO
DROP PROCEDURE calculate_delivery_date
GO
DECLARE @order_date DATETIME, @delivery_date DATETIME;
SET @order_date = '2019-09-07';
EXEC calculate_delivery_date 1, @order_date, @delivery_date OUTPUT;
PRINT @delivery_date
GO
----------------------
-- Третья процедура --
----------------------
CREATE PROCEDURE calculate_discount_for_customer
@customer_id INT
AS BEGIN
DECLARE @money_spent INT;
SELECT @money_spent=SUM(amount)
FROM Payments JOIN Orders ON Payments.order_id = Orders.id
WHERE customer_id=@customer_id;
IF @money_spent IS NULL BEGIN
RAISERROR (N'Данный клиент ещё не оплатил ни одного заказа', 10, 1);
RETURN 0;
END
SET @money_spent = @money_spent - 10000;
SET @money_spent = IIF(@money_spent < 0, 0, @money_spent);
SET @money_spent = IIF(@money_spent > 40000, 40000, @money_spent);
RETURN CAST(@money_spent / 40000 * 15 + 5 AS INT);
END
GO
DROP PROCEDURE calculate_discount_for_customer
GO
DECLARE @discount INT;
EXEC @discount = calculate_discount_for_customer 10;
PRINT @discount
GO
DECLARE @discount INT;
EXEC @discount = calculate_discount_for_customer 1;
PRINT @discount
GO
-------------------------
-- Четвёртая процедура --
-------------------------
CREATE PROCEDURE calculate_full_expenses_month
@date DATE,
@expenses FLOAT OUTPUT
AS BEGIN
SET @expenses = 0.0;
DECLARE @salary INT;
SELECT @salary=SUM(salary) FROM Drivers;
DECLARE @gas_price FLOAT = 0.0;
SELECT @gas_price=SUM(gas_consumption / 100.0 * distance)
FROM Deliveries
JOIN Routes ON Deliveries.route_id = Routes.id
JOIN Vehicles ON Deliveries.vehicle_id = Vehicles.id
WHERE DATEPART(year, date) = DATEPART(year, @date) AND
DATEPART(month, date) = DATEPART(month, @date);
SET @gas_price = IIF(@gas_price IS NULL, 0.0, @gas_price);
SET @expenses = CAST(@salary AS FLOAT) + @gas_price;
END
GO
DROP PROCEDURE calculate_full_expenses_month
GO
DECLARE @expenses FLOAT;
EXEC calculate_full_expenses_month '2021-12-01', @expenses OUTPUT;
PRINT CAST(@expenses AS INT)
GO
---------------------
-- Пятая процедура --
---------------------
CREATE PROCEDURE calculate_full_returns_month
@date DATE,
@returns FLOAT OUTPUT
AS SELECT @returns=SUM(amount) FROM Payments
WHERE DATEPART(year, date) = DATEPART(year, @date) AND
DATEPART(month, date) = DATEPART(month, @date);
GO
DROP PROCEDURE calculate_full_returns_month
GO
DECLARE @returns FLOAT;
EXEC calculate_full_returns_month '2021-12-01', @returns OUTPUT;
PRINT CAST(@returns AS INT)
GO
|
