petshop_schema.sql

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

    @langpref            varchar(80),
    @favcategory         varchar(80),
    @mylistopt           int,
    @banneropt           int
)
AS

    -- SET NOCOUNT to ON and no longer display the count message
    SET NOCOUNT ON

    DECLARE @CurrentError int

    -- start transaction, inserting into three tables
    BEGIN TRANSACTION

    -- create a login
    INSERT INTO Signon (username, [password])
    VALUES (@userid, @password)

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- create a new profile for the user
    INSERT INTO Profile (userid, langpref, favcategory, mylistopt, banneropt)
    VALUES (@userid, @langpref, @favcategory, @mylistopt, @banneropt)

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- upadte the account record
    INSERT INTO Account (userid, email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone)
    VALUES (@userid, @email, @firstname, @lastname, 'OK', @addr1, @addr2, @city, @state, @zip, @country, @phone)

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- end of transaction
    COMMIT TRANSACTION

    -- Reset SET NOCOUNT to OFF
    SET NOCOUNT OFF

    -- return 0 to indicate success, otherwise the raised error will be returned
    RETURN 0

    ERROR_HANDLER:
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF
        RETURN @CurrentError
    GO

-------------------------------------------------------------------------------
-- upAccountGetAddress
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountGetAddress
(
    @userID              varchar(80)
)
AS

    SELECT email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone
    FROM Account
    WHERE userid = @userID
    GO

-------------------------------------------------------------------------------
-- upAccountGetDetails
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountGetDetails
(
    @userID              varchar(80)
)
AS

    SELECT Account.userid, Account.email, Account.firstname, Account.lastname, 
           Account.status, Account.addr1, Account.addr2, Account.city, 
           Account.state, Account.zip, Account.country, Account.phone, 
           Profile.langpref, Profile.favcategory, Profile.mylistopt, Profile.banneropt
    FROM Account INNER JOIN Profile ON Account.userid = Profile.userid
    WHERE Account.userid = @userID
    GO

-------------------------------------------------------------------------------
-- upAccountLogin
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountLogin
(
    @username            varchar(25),
    @password            varchar(25),
    @CustomerID          varchar(25) OUTPUT
)
AS

    SELECT @CustomerID = username
    FROM signon
    WHERE username = @username AND password = @Password

    -- if the select didn't return any rows in the result,
    -- then set the customer id to an empty string to indicate a login 
    -- failure, otherwise the username will be returned to indicate
    -- a successful login	
    IF @@Rowcount < 1 
        SELECT @CustomerID = ''    
    GO

-------------------------------------------------------------------------------
-- upAccountUpdate
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountUpdate
(
    @userid              varchar(80),
    @email               varchar(80),
    @firstname           varchar(80),
    @lastname            varchar(80),    
    @addr1               varchar(80),
    @addr2               varchar(40),
    @city                varchar(80),
    @state               varchar(80),
    @zip                 varchar(20),
    @country             varchar(20),
    @phone               varchar(80),   
    @langpref            varchar(80),
    @favcategory         varchar(80),
    @mylistopt           int,
    @banneropt           int
)
AS

    -- SET NOCOUNT to ON and no longer display the count message.
    SET NOCOUNT ON

    DECLARE @CurrentError int

    -- start transaction, updating two tables
    BEGIN TRANSACTION

    UPDATE Account SET email = @email, firstname = @firstname, lastname = @lastname, addr1 = @addr1, addr2 = @addr2, city = @city,  
                       state = @state, zip = @zip, country = @country, phone = @phone
    WHERE userid = @userid

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
         END

    UPDATE Profile SET langpref = @langpref, favcategory = @favcategory, mylistopt = @mylistopt, banneropt = @banneropt
    WHERE userid = @userid

    select @CurrentError = @@Error

    IF @CurrentError != 0
        BEGIN
   	        GOTO ERROR_HANDLER
        END

    -- end of transaction
    COMMIT TRANSACTION

    -- Reset SET NOCOUNT to OFF
    SET NOCOUNT OFF

    -- return 0 to indicate success, otherwise the raised error will be returned
    RETURN 0

    ERROR_HANDLER:
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF
        RETURN @CurrentError
    GO

-------------------------------------------------------------------------------
-- upCategoryGetList
-------------------------------------------------------------------------------
CREATE PROCEDURE upCategoryGetList

AS

    SELECT catid, [name]
    FROM Category
    ORDER BY catid
    GO

-------------------------------------------------------------------------------
-- upInventoryAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upInventoryAdd
( 
    @itemid              char(10),
    @qty                 int
)
AS

    -- insert into the item table
    INSERT INTO Inventory (itemid, qty)
    VALUES (@itemid, @qty)
    GO

-------------------------------------------------------------------------------
-- upInventoryGetList
-------------------------------------------------------------------------------
/******************************************************************************   
	Get the qty for the items in the xml document.

	Sample usage:
		
	declare @xml varchar(8000)
	set @xml = 
		'
		<Inventory>
		  <LineItem itemid="EST-1" />
		  <LineItem itemid="EST-2" />
		  <LineItem itemid="EST-3" />
		</Inventory>
		'

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

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

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

    -- return qty for items specified in xml
    SELECT i.itemid, i.qty 
    FROM OPENXML(@idoc, '/Inventory/LineItem') 
    WITH (itemid char(10)) AS x
    INNER JOIN Inventory AS i
    ON x.itemid = i.itemid

    -- done with xml doc, remove it from memory
    EXEC sp_xml_removedocument @idoc
    GO

-------------------------------------------------------------------------------
-- upItemAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemAdd
( 
    @itemid              char(10),
    @productid           char(10),
    @listprice           decimal(10, 2),
    @unitcost            decimal(10, 2),
    @supplier            int,
    @status              char(2),
    @attr1               varchar(80),
    @attr2               varchar(80),
    @attr3               varchar(80),
    @attr4               varchar(80),
    @attr5               varchar(80)
)
AS

    -- insert into the item table
    INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1, attr2, attr3, attr4, attr5)
    VALUES (@itemid, @productid, @listprice, @unitcost, @supplier, @status, @attr1, @attr2, @attr3, @attr4, @attr5)
    GO

-------------------------------------------------------------------------------
-- upItemGetDetails
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetDetails
(
    @itemID              char(10),
    @price               smallmoney output,
    @qty                 int output,
    @itemName            varchar(80) output,
    @itemAttr            varchar(80) output,
    @desc                varchar(255) output   
) 
AS

    SELECT @price = Item.listprice, @qty = Inventory.qty, 
           @itemName = Product.name, @itemAttr = Item.attr1, @desc = Product.descn
    FROM Item INNER JOIN
             Inventory ON Item.itemid = Inventory.itemid INNER JOIN
                 Product ON Item.productid = Product.productid
    WHERE (Item.itemid = @itemID)
    GO

-------------------------------------------------------------------------------
-- upItemGetList
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetList
(
    @prodid              varchar(10)
)
AS

    SELECT itemid, listprice, attr1
    FROM Item
    WHERE productid = @prodid 
    GO  

-------------------------------------------------------------------------------
-- upItemGetList_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetList_ListByPage
(
    @prodid              varchar(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 itemid 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,
        itemid              char(10)               NOT NULL,   
        listprice           decimal(10, 2)         NULL, 
        attr1               varchar(80)            NULL,
    )

    -- copy the search results into the temporary table
    INSERT INTO #SearchResultsTempTable (itemid, listprice, attr1)
    SELECT itemid, listprice, attr1
    FROM Item
    WHERE productid = @prodid 

    -- 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 itemid, listprice, attr1
    FROM #SearchResultsTempTable
    WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)
    GO

-------------------------------------------------------------------------------
-- upOrdersAdd
-------------------------------------------------------------------------------
/******************************************************************************
	Add order to database. Example of using this stored proc is shown below.
	
	declare @xml varchar(8000)
	set @xml = 
		'
		<Orders
		userid="j2ee" orderdate="1/1/2001" 
		shipaddr1="1234 West Branch" shipaddr2=""
		shipcity="Liveoak" shipstate="Texas"
		shipzip="12345" shipcountry="USA"