diff options
| -rw-r--r-- | task6_triggers.sql | 18 | ||||
| -rw-r--r-- | task7_procedures.sql | 171 |
2 files changed, 189 insertions, 0 deletions
diff --git a/task6_triggers.sql b/task6_triggers.sql index 1c1a5f6..c6519d8 100644 --- a/task6_triggers.sql +++ b/task6_triggers.sql @@ -63,6 +63,24 @@ GO DELETE FROM Deliveries WHERE driver_id=2 GO +-- Add + +INSERT INTO Drivers (city_id, salary, on_delivery) +VALUES (4, 35000, 0); +GO + +INSERT INTO Vehicles (name, tonnage, gas_consumption) +VALUES (N'KAMAZ', 30, 50); +GO + +INSERT INTO Routes (city1_id, city2_id, distance) +VALUES (1, 2, 900); +GO + +INSERT INTO Deliveries (driver_id, vehicle_id, route_id, date) +VALUES (1, 1, 1, '2021-09-08'); +GO + --------------- -- Задание 2 -- --------------- diff --git a/task7_procedures.sql b/task7_procedures.sql new file mode 100644 index 0000000..a93dd3f --- /dev/null +++ b/task7_procedures.sql @@ -0,0 +1,171 @@ +---------------------- +-- Первая процедура -- +---------------------- + +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 + +DECLARE @price INT; +EXEC calculate_order_price 12, @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 |