petshop_schema.sql

基于微软的 ASP.NET+C#开发的PETSHOP(网上宠物店)项目,在性能及开发效率上明显优于基于SUN J2EE框架开发的PETSHOP. 项目包括所有源码及数据库建库脚本,是不错的学习 AS

		billaddr1="5678 East Branch" billaddr2="Building C"
		billcity="Madrone" billstate="Utah"
		billzip="99999" billcountry="USA"
		courier="UPS" totalprice="57.50"
		billtofirstname="Fred" billtolastname="Derfy"
		shiptofirstname="Bob" shiptolastname="Black"
		creditcard="111-222-333" exprdate="9/2002"
		cardtype="Visa" locale="US_en">
		  <LineItem itemid="EST-1" linenum="1" quantity="4" unitprice="5.00" />
		  <LineItem itemid="EST-2" linenum="2" quantity="5" unitprice="7.00" />
		  <LineItem itemid="EST-3" linenum="3" quantity="2" unitprice="1.25" />
		</Orders>
		'

	exec upOrderAdd @xml
	
*******************************************************************************/
CREATE PROCEDURE upOrdersAdd
(
    @xml                 varchar(8000)
)
AS

    DECLARE @idoc int		-- xml doc
    DECLARE @orderid int	-- new order

    -- parse xml doc
    EXEC sp_xml_preparedocument @idoc output, @xml


    SET NOCOUNT ON
    DECLARE @CurrentError int

    -- start transaction, updating three tables
    BEGIN TRANSACTION

    -- add new order to Orders table
    INSERT INTO Orders
    SELECT userid, orderdate, shipaddr1, shipaddr2, shipcity, shipstate, 
           shipzip, shipcountry, billaddr1, billaddr2, billcity, billstate, billzip,
           billcountry, courier, totalprice, billtofirstname, billtolastname,
           shiptofirstname, shiptolastname, creditcard, exprdate, cardtype, locale
    FROM OpenXML(@idoc, '/Orders')
    WITH Orders

    -- check for error
    SELECT @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- get new order id
    SELECT @orderid = @@IDENTITY

    -- add line items to LineItem table
    INSERT INTO LineItem
    SELECT @orderid, linenum, itemid, quantity, unitprice
    FROM OpenXML(@idoc, '/Orders/LineItem')
    WITH LineItem

    -- check for error
    SELECT @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- add status to OrderStatus table
    INSERT INTO OrderStatus
    SELECT @orderid, @orderid, getdate(), 'P'

    -- check for error
    SELECT @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- update inventory
    UPDATE Inventory
    SET Inventory.qty = Inventory.qty - LineItem.quantity
    FROM OpenXML(@idoc, '/Orders/LineItem')
    WITH LineItem
    WHERE Inventory.itemid=LineItem.itemid

    -- check for error
    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- end of transaction
    COMMIT TRANSACTION

    SET NOCOUNT OFF

    -- done with xml doc
    EXEC sp_xml_removedocument @idoc

    -- return the new order
    RETURN @orderid

    ERROR_HANDLER:
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF    
        RETURN 0    
    GO

-------------------------------------------------------------------------------
-- upOrdersGet
-------------------------------------------------------------------------------
CREATE PROCEDURE upOrdersGet
(
    @userid              varchar(80)
) 
AS

    SELECT orderid, orderdate 
    FROM orders 
    WHERE userid = @userid 
    GO

-------------------------------------------------------------------------------
-- upOrdersGetDetails
-------------------------------------------------------------------------------
CREATE PROCEDURE upOrdersGetDetails 
(
    @OrderNumber         integer,
    @OrderStatus         varchar(30) output,
    @OrderDate           datetime output,
    @ShipToAddress       varchar(80) output,
    @ShipToCity          varchar(80) output,
    @ShipToState         varchar(80) output,
    @ShipToZip           varchar(20) output,
    @BillToAddress       varchar(80) output,
    @BillToCity          varchar(80) output,
    @BillToState         varchar(80) output,
    @BillToZip           varchar(20) output,
    @TotalPrice          numeric output
) 
AS

    BEGIN TRANSACTION

    SELECT @OrderStatus = orderstatus.status, @OrderDate = orders.orderdate, @ShipToAddress =  orders.shipaddr1,
           @ShipToCity  = orders.shipcity, @ShipToState = orders.shipstate, @ShipToZip = orders.shipzip,
           @BillToAddress = orders.billaddr1, @BillToCity = orders.billcity, @BillToState = orders.billstate,
           @BillToZip = orders.billzip, @TotalPrice = orders.totalprice
    FROM ORDERS INNER JOIN
             ORDERSTATUS ON ORDERS.orderid = ORDERSTATUS.orderid
    WHERE (ORDERS.orderid = @OrderNumber);


    SELECT lineitem.linenum,
         lineitem.itemid,
         lineitem.quantity,
         lineitem.unitprice
    FROM LINEITEM
    WHERE (LINEITEM.orderid = @OrderNumber);

    COMMIT TRANSACTION;
    GO

 -------------------------------------------------------------------------------
-- upOrdersGetStatus
-------------------------------------------------------------------------------
CREATE PROCEDURE upOrderStatusGet
(
    @orderid             integer,
    @OrderStatus         char(2) OUTPUT
) 
AS

    SELECT @OrderStatus = status
    FROM OrderStatus
    WHERE (orderid = @orderid);
    GO

-------------------------------------------------------------------------------
-- upProductAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upProductAdd
( 
    @productid           char(10),
    @category            char(10),
    @name                varchar(80),
    @descn               varchar(255)
)
AS

    -- insert into the product table
    INSERT INTO Product (productid, category, [name],descn)
    VALUES(@productid, @category, @name, @descn)
    GO

-------------------------------------------------------------------------------
-- upProductGetList
-------------------------------------------------------------------------------
CREATE PROCEDURE upProductGetList
(
	    @cat_id              char(10)
)
AS

    SELECT productid, name
    FROM Product
    WHERE category = @cat_id
    ORDER BY name
    GO

-------------------------------------------------------------------------------
-- upProductGetList_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE upProductGetList_ListByPage
(
    @cat_id              char(10),
    @nCurrentPage        int,
    @nPageSize           int,
    @totalNumResults     int output
)
AS

    -- we are creating a temporary table to store the currently
    -- selected page of data. a rowid field has been added to allow
    -- us to track which page we are on (the productid didn't work
    -- in this case because it is a character data type and it is
    -- much easier to calculate the paging with an int)
    CREATE TABLE #ProductCategoryTempTable
    (
        rowid               int           IDENTITY PRIMARY KEY,
        productid           char(10)               NOT NULL,
        name                varchar(80)            NULL
    )

    -- copy the search results into the temporary table
    INSERT INTO #ProductCategoryTempTable (productid, name)
    SELECT productid, name
    FROM Product
    WHERE category = @cat_id
    ORDER BY name

    -- always return the total number of items found in the search
    SELECT @totalNumResults = @@ROWCOUNT

    -- calculate the current page
    DECLARE @nFirstPageRecord int
    DECLARE @nLastPageRecord int
    SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
    SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1)

    -- select the correct page of data with the given page size
    SELECT productid, name
    FROM #ProductCategoryTempTable
    WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)
    GO

-------------------------------------------------------------------------------
-- upProductSearch
-------------------------------------------------------------------------------
CREATE PROCEDURE upProductSearch
(
	    @Search              nvarchar(255)
)
AS
    DECLARE @_name varchar(50) 
    SET @_name = '%' + @Search + '%'

    SELECT productid, [name], descn
    FROM Product
    WHERE 
    (
        --search the product name field
        [name] like (@_name)
       OR
       
       -- search the products category field
       category like  (@_name)
    )
    GO

-------------------------------------------------------------------------------
-- upProductSearch_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE upProductSearch_ListByPage
(
    @searchText          varchar(255),
    @nCurrentPage        int,
    @nPageSize           int,
    @nSearchResults      int output
)
AS

    -- we are creating a temporary table to store the currently
    -- selected page of data. a rowid field has been added to allow
    -- us to track which page we are on (the productid didn't work
    -- in this case because it is a character data type and it is
    -- much easier to calculate the paging with an int
    CREATE TABLE #SearchResultsTempTable
    (
        rowid               int           IDENTITY PRIMARY KEY,
        productid           char(10)               NOT NULL,
        [name]              varchar(80)            NULL,
        descn               varchar(255)           NULL
    )

    -- copy the search results into the temporary table    
    DECLARE @_name varchar(50) 
    SET @_name = '%' + @searchText + '%'	
    INSERT INTO #SearchResultsTempTable (productid, [name], descn)
    SELECT productid, [name], descn
    FROM Product
    WHERE 
    (
        --search the product name field
        [name] like (@_name)
       OR
       
       -- search the products category field
       category like  (@_name)
    )
        
    -- always return the total number of items found in the search
    SELECT @nSearchResults = @@ROWCOUNT

    -- calculate the current page
    DECLARE @nFirstPageRecord int
    DECLARE @nLastPageRecord int
    SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
    SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1)

    -- select the correct page of data with the given page size
    SELECT productid, [name], descn
    FROM #SearchResultsTempTable
    WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)
    GO

-------------------------------------------------------------------------------
-- upProfileGetBannerOption
-------------------------------------------------------------------------------
CREATE PROCEDURE upProfileGetBannerOption
(
        @userID              varchar(80),
        @showBanner          int output,
        @bannerPath          varchar(80) output
)
AS

    SELECT @showBanner = Profile.banneropt, @bannerPath = BannerData.bannername
    FROM Profile INNER JOIN
            BannerData ON Profile.favcategory = BannerData.favcategory
    WHERE Profile.userid = @userID
    GO

-------------------------------------------------------------------------------
-- upProfileGetListOption
-------------------------------------------------------------------------------
CREATE PROCEDURE upProfileGetListOption
(
        @userID              varchar(80),
        @showList            int output,
        @cat                 varchar(80) output
)
AS

    SELECT @showList = Profile.mylistopt, @cat = favcategory
    FROM Profile
    WHERE Profile.userid = @userID 
    GO