shopping.sql

用存储过程写建的数据库

GO

CREATE PROC pro_MainOfSecond
@MainID varchar(6)--商品主类
AS
BEGIN
    SELECT * FROM SecondarySort WHERE mainid = @MainID
END
GO

--【插入商品副类】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_SecondarySort')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SecondarySort]
GO

CREATE PROC [dbo].[pro_SecondarySort]
@Sort varchar(20),
@MainId char(6)
AS
BEGIN
    IF (@Sort not in (SELECT sort FROM SecondarySort)) and
    (@mainid in (SELECT [id] FROM MainSort))
    BEGIN
        BEGIN TRAN
        INSERT INTO SecondarySort (sort,mainid)
        VALUES (@Sort,@MainId)

        IF @Sort not in (SELECT sort FROM SecondarySort)
        BEGIN
            ROLLBACK TRAN
            RETURN 0--返回“0”插入失败
        END
        ELSE
        BEGIN
            COMMIT TRAN
            RETURN 1--返回“1”插入成功
        END
        
    END
    ELSE
    BEGIN
        RETURN 0--返回“0”插入失败
    END
END
GO

--插入商品副类
BEGIN TRAN
    EXEC pro_SecondarySort '苹果','MA0001'
    EXEC pro_SecondarySort '香蕉','MA0001'
    EXEC pro_SecondarySort '梨子','MA0001'
    EXEC pro_SecondarySort '菠萝','MA0001'
    EXEC pro_SecondarySort '西瓜','MA0001'

    EXEC pro_SecondarySort '白菜','MA0002'
    EXEC pro_SecondarySort '青菜','MA0002'
    EXEC pro_SecondarySort '番茄','MA0002'
    EXEC pro_SecondarySort '茄子','MA0002'
    EXEC pro_SecondarySort '冬瓜','MA0002'

    EXEC pro_SecondarySort '猪肉','MA0003'
    EXEC pro_SecondarySort '牛肉','MA0003'
    EXEC pro_SecondarySort '鸡肉','MA0003'
    EXEC pro_SecondarySort '鸭肉','MA0003'
    EXEC pro_SecondarySort '鱼肉','MA0003'
COMMIT TRAN
GO

--=========================================================================================



--=========================================商品信息=========================================

--删除商品信息表
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.Product')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE dbo.Product
GO

--创建流水账号
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.f_NextPR'))
DROP FUNCTION dbo.f_NextPR
GO

CREATE FUNCTION f_NextPR()
RETURNS char(8)
AS
BEGIN
    RETURN(SELECT 'PR' + right(1000001 + isnull(right(max(id),6),0),6)
    FROM Product WITH (XLOCK,PAGLOCK))
END
GO

--创建商品信息表
CREATE TABLE Product (
    [id] char(8) PRIMARY KEY
    DEFAULT dbo.f_NextPR(),                      --商品编号
    [name] varchar(50) not null,                 --商品名称
    [secondid] char(6)
    REFERENCES SecondarySort(id),                --副类编号
    [saleprice] numeric(8,2) not null,           --出售价格
    [stockprice] numeric(8,2) not null,          --入库价格
    [unit] varchar(10) ,			 --商品单位
    [amount] int not null,                       --商品数量
    [picture] varchar(200),                      --商品图片
    [introduction] varchar(300) ,		 --商品简介
    [markettime] varchar(25) not null
    DEFAULT CONVERT(varchar(10),getdate(),120)   --上市时间
)
GO

--【最新上市商品】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_ProductNew')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_ProductNew]
GO

CREATE PROC pro_ProductNew
AS
BEGIN
    DECLARE @SQL varchar(5000)
    SET @SQL ='SELECT DISTINCT TOP 6 Product.[id],[name],SecondSort = SecondarySort.sort,
    saleprice,stockprice,unit,amount,picture,introduction,markettime
    FROM Product,SecondarySort                                    
    WHERE Product.secondid = SecondarySort.[id]
    ORDER BY markettime DESC'
    EXEC (@SQL)
END
GO

--【查询所有商品信息】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_SelectProduct')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SelectProduct]
GO

CREATE PROC pro_SelectProduct
AS
BEGIN
    DECLARE @SQL varchar(5000)
    SET @SQL ='SELECT DISTINCT Product.[id],[name],SecondSort = SecondarySort.sort,
    saleprice,stockprice,unit,amount,picture,introduction,markettime
    FROM Product,SecondarySort                                    
    WHERE Product.secondid = SecondarySort.[id]'
    EXEC (@SQL)
END
GO

--【按照ID查询商品信息】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_SelectID')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SelectID]
GO

CREATE PROC pro_SelectID
@ProductID varchar(10)--商品编号
AS
BEGIN
    DECLARE @SQL varchar(5000)
    SET @SQL ='SELECT DISTINCT Product.[id],[name],SecondSort = SecondarySort.sort,
    saleprice,stockprice,unit,amount,picture,introduction,markettime
    FROM Product,SecondarySort                                    
    WHERE Product.secondid = SecondarySort.[id] and Product.[id] = '''+@ProductID+''''
    EXEC (@SQL)
END
GO

--【插入商品信息】
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.pro_InsertProduct')
and objectproperty(ID,N'IsProcedure') = 1)
DROP PROC [dbo].[pro_InsertProduct]
GO

CREATE PROC pro_InsertProduct
@name varchar(50),--商品名称
@secondid char(6),--商品类型（副）
@saleprice numeric(8,2),--出售价格
@stockprice numeric(8,2),--进货价格
@unit varchar(10),--商品单位
@amount int,--数量
@picture varchar(200),--商品图片
@introduction varchar(300)--商品简介
AS
BEGIN
    IF (@name not in (SELECT [name] FROM Product)) and
    (@secondid in(SELECT [id] FROM SecondarySort))
    BEGIN
        BEGIN TRAN
        INSERT INTO Product ([name],[secondid],[saleprice],[stockprice],
        [unit],[amount],[picture],[introduction])
        VALUES (@name,@secondid,@saleprice,@stockprice,@unit,@amount,
        @picture,@introduction)

        IF @name not in (SELECT [name] FROM Product)
        BEGIN
            ROLLBACK TRAN
            RETURN 0--返回“0”插入失败
        END
        ELSE
        BEGIN
            COMMIT TRAN
            RETURN 1--返回“1”插入成功
        END
    END
    ELSE
    BEGIN
        RETURN 0
    END
END
GO

BEGIN TRAN
    EXEC pro_InsertProduct '富士苹果A','SE0001','5.30','3.10','斤',260,'image\NEO.jpg','dddd'
    EXEC pro_InsertProduct '富士苹果B','SE0001','5.30','3.10','斤',260,'image\NEO.jpg','dd'
    EXEC pro_InsertProduct '富士苹果C','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '富士苹果D','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '富士苹果E','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '富士苹果F','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '富士苹果G','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '富士苹果H','SE0001','5.30','3.10','斤',260,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉A','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉B','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉C','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉D','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉E','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉F','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '日本香蕉G','SE0002','6.20','4.90','斤',180,'image\NEO.jpg',''
    EXEC pro_InsertProduct '浙江蜜梨','SE0003','5.20','3.20','斤',290,'image\NEO.jpg',''
    EXEC pro_InsertProduct '杭州菠萝','SE0004','6.30','4.90','斤',130,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆冬瓜','SE0005','4.10','2.80','斤',164,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆西瓜','SE0005','4.10','2.80','斤',164,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆南瓜','SE0005','4.10','2.80','斤',164,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆北瓜','SE0005','4.10','2.80','斤',164,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆黑瓜','SE0005','4.10','2.80','斤',164,'image\NEO.jpg',''

    EXEC pro_InsertProduct '北京白菜','SE0006','3.20','2.50','斤',350,'image\NEO.jpg',''
    EXEC pro_InsertProduct '苏州青菜','SE0007','2.50','1.20','斤',160,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新疆番茄','SE0008','4.60','2.30','斤',190,'image\NEO.jpg',''
    EXEC pro_InsertProduct '常德茄子','SE0009','3.20','2.40','斤',102,'image\NEO.jpg',''
    EXEC pro_InsertProduct '山东冬瓜','SE0010','3.60','2.50','斤',136,'image\NEO.jpg',''

    EXEC pro_InsertProduct '冷冻猪肉','SE0011','15.60','10.10','斤',268,'image\NEO.jpg',''
    EXEC pro_InsertProduct '新鲜牛肉','SE0012','16.50','12.20','斤',154,'image\NEO.jpg',''
    EXEC pro_InsertProduct '美国鸡肉','SE0013','19.30','14.20','斤',128,'image\NEO.jpg',''
    EXEC pro_InsertProduct '武陵鸭肉','SE0014','16.10','13.50','斤',185,'image\NEO.jpg',''
    EXEC pro_InsertProduct '东江鱼肉','SE0015','15.90','12.10','斤',162,'image\NEO.jpg',''
COMMIT TRAN
GO

--=========================================================================================



--=====================================商品订单&详细订单=====================================

--删除商品订单表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.OrderForm')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE dbo.OrderForm
GO

--创建商品订单表
CREATE TABLE OrderForm (
    [id] varchar(15) PRIMARY KEY,                --订单编号
    [username] varchar(20) not null,             --订货账号
    [time] varchar(25) not null                  --订货时间
)
GO

--删除详细订单表
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'dbo.Detail')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE dbo.Detail
GO

--创建详细订单表
CREATE TABLE Detail (
    [id] varchar(15)
    REFERENCES OrderForm(id),                    --订单编号
    [productid] char(8)
    REFERENCES Product(id),                      --商品编号
    [amount] int not null                        --订货数量
)
GO

--商品销售排行榜(当月)
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_ProductChart]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_ProductChart]
GO

CREATE PROC pro_ProductChart
AS
BEGIN
    SELECT TOP 10 productid,name,'sell amount' = SUM(Detail.amount) FROM OrderForm,Detail,Product
    WHERE OrderForm.id = Detail.id and product.id = productid and
    CONVERT(varchar(6),OrderForm.id) = CONVERT(varchar(6),getdate(),112)
    GROUP BY productid,name
    ORDER BY SUM(Detail.amount) DESC
END
GO

--删除存储过程
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_Order]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Order]
GO

--创建存储过程
CREATE PROC pro_Order
@username varchar(20),
@idconvene varchar(500),
@amountconvene varchar(500)
AS
BEGIN
    DECLARE @today varchar(12)--当前的时间(精确到天)
    DECLARE @maxday varchar(8)--表中最后记录时间
    DECLARE @sql varchar(1000)--保存SQL语句
    DECLARE @time varchar(25)--当前的时间(精确到秒)
    DECLARE @productid varchar(8)--商品账号
    DECLARE @amount varchar(8)--商品数量
    DECLARE @temp int

    SET @today = CONVERT(varchar(12),getdate(),112)
    SET @maxday = (SELECT MAX(id) FROM OrderForm)
    SET @time = CONVERT(varchar(20),getdate(),120)