shopping.sql

用存储过程写建的数据库

    IF (@today<=@maxday or @maxday is null)
    BEGIN
        SET @sql = (SELECT @today + right(10001 +
        isnull(right(max(id),4),0),4) FROM OrderForm WITH (XLOCK,PAGLOCK))
    END
    ELSE
    BEGIN
        SET @sql = (SELECT @today + right(10000 +
        isnull(right(min(id),4),0),4) FROM OrderForm WITH (XLOCK,PAGLOCK))
    END

    INSERT INTO OrderForm VALUES (@sql,@username,@time)

    
    WHILE len(@idconvene)<>0
    BEGIN
        IF charindex(',',@idconvene)<>0
        BEGIN
            SET @productid = substring(@idconvene,1,
            charindex(',',@idconvene)-1)
            SET @amount = substring(@amountconvene,1,
            charindex(',',@amountconvene)-1)

            SET @idconvene = substring(@idconvene,
            charindex(',',@idconvene)+1,len(@idconvene))
            SET @amountconvene = substring(@amountconvene,
            charindex(',',@amountconvene)+1,len(@amountconvene))
        END
        ELSE
        BEGIN
            SET @productid = substring(@idconvene,1,len(@idconvene))
            SET @amount = substring(@amountconvene,1,len(@amountconvene))

            SET @idconvene = substring(@idconvene,
            len(@idconvene)+1,len(@idconvene))
            SET @amountconvene = substring(@amountconvene,
            len(@idconvene)+1,len(@amountconvene))
        END

        SET @temp = CONVERT(int,@amount)

        BEGIN TRAN

        UPDATE Product SET amount = amount - @temp
        FROM Product WHERE [id] = @productid

        INSERT INTO Detail VALUES (@sql,@productid,@temp)

        IF(SELECT amount FROM Product WHERE [id] = @productid)<0
        BEGIN
            ROLLBACK TRAN
        END
        ELSE
        BEGIN
            COMMIT TRAN
        END
    END
    RETURN
END
GO

--插入商品订单&详细订单
BEGIN TRAN
    EXEC pro_Order 'hao','PR000001,PR000002,PR000011,PR000006,PR000013','12,15,23,9,11'
    EXEC pro_Order 'hao','PR000003,PR000004,PR000014,PR000005,PR000012','12,15,23,9,11'
    EXEC pro_Order 'hao','PR000010,PR000007,PR000008,PR000009,PR000014','12,15,23,9,11'
COMMIT TRAN
GO

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



--========================================盈利状态表========================================

--删除盈利状态表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.Payoff')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[Payoff]
GO

--创建盈利状态表
CREATE TABLE Payoff (
    [dates] varchar(25) PRIMARY KEY,             --盈利日期
    [saleamounts] numeric(10,2) DEFAULT 0.00,    --出售总额
    [stockamounts] numeric(10,2) DEFAULT 0.00,   --进货总额
    [netincome] numeric(10,2) DEFAULT 0.00       --净收入
)
GO

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



--=========================================商品利润=========================================

--删除商品利润表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.Profit')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[Profit]
GO

--创建商品利润表
CREATE TABLE Profit (
    PRIMARY KEY (months,productid),
    [months] varchar(25),                        --盈利月份
    [productid] char(8)
    REFERENCES Product(id),                      --商品编号
    [saleamounts] numeric(10,2) DEFAULT 0.00,    --出售总额
    [stockamounts] numeric(10,2) DEFAULT 0.00,   --进货总额
    [netincome] numeric(10,2) DEFAULT 0.00       --净收入
)
GO

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



--=========================================客户留言=========================================

--删除客户留言表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.LeaveWord')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[LeaveWord]
GO

--创建客户留言表
CREATE TABLE LeaveWord (
    [dates] datetime
    DEFAULT getdate(),                           --留言时间
    [productid] char(8),                         --商品编号
    [username] varchar(20),                      --客户账号
    [leaveword] varchar(500)                     --客户留言
)
GO

--插入信用卡
BEGIN TRAN
    INSERT INTO LeaveWord (productid,username,leaveword)
    VALUES ('PR000001','hao','不错~支持！')
COMMIT TRAN
GO

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



--==========================================信用卡==========================================

--删除信用卡表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.Ebank')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[Ebank]
GO

--创建信用卡表
CREATE TABLE Ebank (
    [username] varchar(25),                      --银行账号
    [password] varchar(25),                      --密码
    [money] numeric(10,2)                        --余额
)
GO

--插入信用卡
BEGIN TRAN
    INSERT INTO Ebank VALUES ('43310119871206053','war120600','2086.53')
COMMIT TRAN
GO

--【登录电子银行】
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_Ebank]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Ebank]
GO

CREATE PROC [dbo].[pro_Ebank]
@UserName varchar(25),--用户帐号
@PassWord varchar(25)--用户密码
AS
BEGIN
    SELECT * FROM Ebank
    WHERE [username] = @UserName and [password] = @PassWord
END
GO

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



--=========================================友情链接=========================================

--删除友情链接表
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'dbo.Connects')
and objectproperty(ID,N'IsUserTable') = 1)
DROP TABLE [dbo].[Connects]
GO

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

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

--创建友情链接表
CREATE TABLE Connects (
    [id] char(8) PRIMARY KEY
    DEFAULT dbo.f_NextUR(),                      --连接编号
    [name] varchar(25),                          --网站名称
    [url] varchar(200)                           --URL地址
)
GO

--插入友情链接
BEGIN TRAN
    INSERT INTO Connects ([name],[url]) VALUES ('百度','http://www.baidu.com')
    INSERT INTO Connects ([name],[url]) VALUES ('新浪','http://www.sina.com.cn')
COMMIT TRAN
GO

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



--=========================================其他功能=========================================

--【分页设置】
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_Pagination]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Pagination]
GO

CREATE PROC pro_Pagination
@currPage int,--当前页
@pageSize int,--每页大小
@tableName varchar(50),--表名
@tableId varchar(50)--查询字段
AS
BEGIN
	DECLARE @SQL varchar(8000)
	SET @SQL = 'SELECT TOP ' + CONVERT(char(5),@pageSize) + ' * FROM ' +
        @tableName + ' WHERE ' + @tableId + ' not in '
	SET @SQL = @SQL + '(SELECT TOP '+CONVERT(char(5),@pageSize*(@currPage-1)) + 
        ' ' + @tableId + ' FROM ' + @tableName 
	SET @SQL = @SQL + ' ORDER BY '+@tableId+')' 

	EXEC(@SQL)
END
GO
exec pro_Pagination 2,2,'Product','id'

--【按照副类给商品分页】
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_PageCount]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_PageCount]
GO

CREATE PROC pro_PageCount
@currPage int,--当前页
@PageSize int,--每页大小
@SecondID varchar(10)--副类
AS
BEGIN

    DECLARE @SQL varchar(8000)
    SET @SQL = 'SELECT TOP ' + convert(char(5),@PageSize) + ' Product.id,name,
    SecondSort = SecondarySort.sort,saleprice,stockprice,unit,amount,picture,
    introduction,markettime FROM Product,SecondarySort WHERE Product.id not in '

    SET @SQL = @SQL + '(SELECT TOP ' + convert(char(5),@PageSize*(@currPage-1))
    + ' Product.id FROM Product,SecondarySort '

    SET @SQL = @SQL + 'WHERE Product.secondid = SecondarySort.id and secondid = 
    '''+@SecondID+''''

    SET @SQL = @SQL + ' ORDER BY Product.id) and Product.secondid = SecondarySort.id 
    and secondid = '''+@SecondID+''''

    EXEC(@SQL)
END
GO
exec pro_PageCount 1,1,'SE0001'

--【表的行数】
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_Rows]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_Rows]
GO

CREATE PROC pro_Rows
@TableName varchar(25)--表名
AS
BEGIN
    DECLARE @SQL varchar(1000)
    SET @SQL = 'SELECT count(*) FROM ' + @TableName
    EXEC (@SQL)
END
GO

EXEC pro_Rows 'SecondarySort'

--【含该副类的书籍】
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[pro_SecondRows]')
and OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROC [dbo].[pro_SecondRows]
GO

CREATE PROC pro_SecondRows
@SecondID varchar(6)--副类编号
AS
BEGIN
    DECLARE @SQL varchar(1000)
    SET @SQL = 'SELECT count(*) FROM Product,SecondarySort WHERE '
    SET @SQL = @SQL + 'Product.secondid = SecondarySort.id and '
    SET @SQL = @SQL + 'secondid = '''+@SecondID+''''
    EXEC (@SQL)
END
GO
EXEC pro_SecondRows 'SE0001'
--=========================================================================================