Otus, Project, 25.07.08, Project, SQL
D:\VC25\Otus\CS\Projects\Shop\Scripts\250707\2
Shop_02.sql
------------------
USE Shop
GO
BEGIN TRANSACTION;
DROP TABLE IF EXISTS dbo.OrderDetails;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.Products;
DROP TABLE IF EXISTS dbo.Users;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP PROCEDURE IF EXISTS dbo.AddProduct;
DROP PROCEDURE IF EXISTS dbo.ProcessOrderDetail;
DROP PROCEDURE IF EXISTS dbo.AddItemToOrderByName;
DROP PROCEDURE IF EXISTS dbo.GetUserReport;
DROP PROCEDURE IF EXISTS dbo.TotalCostCalculate
DROP PROCEDURE IF EXISTS dbo.AddNewUser;
DROP PROCEDURE IF EXISTS dbo.CreateOrder;
DROP PROCEDURE IF EXISTS dbo.AddItemToOrder;
DROP PROCEDURE IF EXISTS dbo.GetUserReport;
DROP PROCEDURE IF EXISTS dbo.CreateOrderByEmail;
COMMIT TRANSACTION;
GO
-- Таблица Products
CREATE TABLE Products (
ProductID INT IDENTITY(1,1) PRIMARY KEY,
ProductName NVARCHAR(255) NOT NULL,
Description NVARCHAR(MAX),
Price NUMERIC(10, 2) NOT NULL CONSTRAINT CK_Product_Price CHECK (Price > 0),
QuantityInStock INT NOT NULL CONSTRAINT CK_Product_Qty CHECK (QuantityInStock >= 0)
);
GO
-- Таблица Users
CREATE TABLE Users (
UserID INT IDENTITY(1,1) PRIMARY KEY,
UserName NVARCHAR(255) NOT NULL,
Email NVARCHAR(255) UNIQUE NOT NULL,
RegistrationDate DATETIME DEFAULT GETDATE()
);
GO
-- Таблица Orders
CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
UserID INT FOREIGN KEY REFERENCES Users(UserID),
OrderDate DATETIME DEFAULT GETDATE(),
Status NVARCHAR(50) NOT NULL
);
GO
CREATE TABLE OrderDetails (
OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
OrderID INT NOT NULL FOREIGN KEY REFERENCES Orders(OrderID),
ProductID INT NOT NULL FOREIGN KEY REFERENCES Products(ProductID),
Quantity INT NOT NULL CONSTRAINT CK_OrderDetails_Quantity CHECK (Quantity > 0),
TotalCost NUMERIC(10, 2) NULL -- Разрешаем NULL-значения
);
GO
-- ----------------------------------------------------------
CREATE PROCEDURE dbo.AddProduct
@ProductName NVARCHAR(100),
@Description NVARCHAR(255),
@Price DECIMAL(18, 2),
@QuantityInStock INT
AS
BEGIN
INSERT INTO Products (ProductName, Description, Price, QuantityInStock)
VALUES (@ProductName, @Description, @Price, @QuantityInStock);
END;
GO
-- ----------------------
CREATE PROCEDURE AddNewUser
@UserName NVARCHAR(100),
@Email NVARCHAR(100)
AS
BEGIN
INSERT INTO Users(UserName, Email)
VALUES (@UserName, @Email);
RETURN SCOPE_IDENTITY(); -- Возвращаем идентификатор нового пользователя
END;
GO
CREATE PROCEDURE CreateOrder
@UserID INT
AS
BEGIN
INSERT INTO Orders(UserID, Status)
VALUES (@UserID, 'pending');
RETURN SCOPE_IDENTITY(); -- Возвращаем идентификатор нового заказа
END;
GO
CREATE PROCEDURE AddItemToOrder
@OrderID INT,
@ProductID INT,
@Quantity INT
AS
BEGIN
DECLARE @CurrentStock INT;
SET @CurrentStock = (SELECT QuantityInStock FROM Products WHERE ProductID = @ProductID);
IF @CurrentStock >= @Quantity BEGIN
UPDATE Products SET QuantityInStock = QuantityInStock - @Quantity WHERE ProductID = @ProductID;
INSERT INTO OrderDetails(OrderID, ProductID, Quantity)
VALUES (@OrderID, @ProductID, @Quantity);
END ELSE BEGIN
PRINT 'Недостаточно товара на складе!';
END
END;
GO
CREATE PROCEDURE TotalCostCalculate
AS
BEGIN
UPDATE OrderDetails
SET TotalCost = Quantity * p.Price
FROM OrderDetails od
INNER JOIN Products p ON od.ProductID = p.ProductID;
END;
GO
CREATE PROCEDURE GetUserReport
@UserID INT
AS
BEGIN
SELECT
O.OrderID,
O.OrderDate,
O.Status,
OD.ProductID,
OD.Quantity,
OD.TotalCost,
P.ProductName,
P.Description
FROM Orders O
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Products P ON OD.ProductID = P.ProductID
WHERE O.UserID = @UserID;
END;
GO
-- ----------------------------------------
-- В магазин поступили товары
-- -----------------------------------------------------------------------------------------------------------------------------------------
EXEC dbo.AddProduct @ProductName = 'jackson usa', @Description = 'USA Signature guitar', @Price = 1500.00, @QuantityInStock = 13;
EXEC dbo.AddProduct @ProductName = 'Gibson LP', @Description = 'Gibson Les Paul Tribute', @Price = 1340.00, @QuantityInStock = 15;
EXEC dbo.AddProduct @ProductName = 'Schecter Plt', @Description = 'Schecter Platinium', @Price = 1700.00, @QuantityInStock = 18;
EXEC dbo.AddProduct @ProductName = 'Fender Am', @Description = 'Fender American Profi', @Price = 1000.00, @QuantityInStock = 26;
EXEC dbo.AddProduct @ProductName = 'Ibanez 550', @Description = 'Ibanez RG550', @Price = 1400.00, @QuantityInStock = 14;
EXEC dbo.AddProduct @ProductName = 'Gibson SG', @Description = 'Gibson SG Standard', @Price = 1100.00, @QuantityInStock = 24;
EXEC dbo.AddProduct @ProductName = 'Schecter BJ', @Description = 'Schecter Black Jack', @Price = 1600.00, @QuantityInStock = 16;
EXEC dbo.AddProduct @ProductName = 'Schecter Rpr', @Description = 'Schecter Reaper usa', @Price = 1400.00, @QuantityInStock = 11;
GO
-- ------------------------------------------------------------------------------------------------------------------------------------------
-- В Магазин пришли Rony James Dio и Janis Joplin.
-- Регистрация Покупателей
-- Регистрация Rony
DECLARE @UserID_Rony INT;
EXEC @UserID_Rony = Shop.dbo.AddNewUser 'Rony James Dio', 'rony@jamesdio.com';
-- Регистрация Janis
DECLARE @UserID_Janis INT;
EXEC @UserID_Janis = Shop.dbo.AddNewUser 'Janis Joplin', 'janis@joplin.com';
-- Каждый Покупатель создал один заказ
-- Rony создал Заказ
DECLARE @OrderID_Rony INT;
EXEC @OrderID_Rony = Shop.dbo.CreateOrder @UserID_Rony;
-- Janis создал Заказ
DECLARE @OrderID_Janis INT;
EXEC @OrderID_Janis = Shop.dbo.CreateOrder @UserID_Janis;
-- Каждый Покупатель выбрал товары для Заказа
-- Rony выбрал два разных Gibson
-- 2 товарные позиции в Заказе
EXEC Shop.dbo.AddItemToOrder @OrderID_Rony, 5, 2; -- 2 шт. Gibson LP
EXEC Shop.dbo.AddItemToOrder @OrderID_Rony, 1, 1; -- 1 шт. Gibson SG
-- Janis выбрала три разных позиции Schecter, Jackson , Fender
-- 3 товарные позиции в Заказе
EXEC Shop.dbo.AddItemToOrder @OrderID_Janis, 3, 2; -- 2 шт. Schecter BJ
EXEC Shop.dbo.AddItemToOrder @OrderID_Janis, 6, 3; -- 3 шт. Jackson USA
EXEC Shop.dbo.AddItemToOrder @OrderID_Janis, 8, 1; -- 1 шт. Fender Am
-- Расчет стоимости каждой товарной позиции
EXEC TotalCostCalculate
-- Магазин выдал квитанцию по каждому заказу с товарными позициями обоим Покупателям
EXEC Shop.dbo.GetUserReport @UserID_Rony;
EXEC Shop.dbo.GetUserReport @UserID_Janis;
GO
Комментариев нет:
Отправить комментарий