create 1,000 products.sql

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

/******************************************************************************
**        File: Create 1,000 Products.sql
**        Name: Benchmark Data Load
**        Desc: Adds 1,000 products to the Product table. Sample Output:
**
**        productid  category   name    descn                                                                                                                                                                                                                                                           
**        ---------- ---------- ------- -------------------------------------------------
**        AV-CB-1    BIRDS      Bird 1  <image src="images/bird1.gif">Bird Description 1
**              
**        Date: 11/6/2001
**
*******************************************************************************/

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

DECLARE @productid char(10)
DECLARE @category char(10)
DECLARE @name varchar(80)
DECLARE @descn varchar(255)

-- counters
DECLARE @petCategory int
DECLARE @nBirds int
DECLARE @nFish int
DECLARE @nCats int
DECLARE @nDogs int
DECLARE @nReptiles int

-- initialize counters
SELECT @petCategory = 1
SELECT @nBirds = 1
SELECT @nFish = 1
SELECT @nCats = 1
SELECT @nDogs = 1
SELECT @nReptiles = 1

DECLARE @i integer
SELECT @i = 1

PRINT 'Inserting into the Product table: ' + convert(varchar(255), getdate());

-- the case statements below are used to evenly distribute the new products
WHILE (@i <= 1000)
BEGIN
    -- generate a unique ProductID
    select @productid = CASE
         WHEN @petCategory = 1 THEN rtrim('AV-CB-' + convert(varchar(10), @nBirds))
         WHEN @petCategory = 2 THEN rtrim('FI-FW-' + convert(varchar(10), @nFish))
         WHEN @petCategory = 3 THEN rtrim('FL-DLH-' + convert(varchar(10), @nCats))
         WHEN @petCategory = 4 THEN rtrim('K9-BD-' + convert(varchar(10), @nDogs))
         WHEN @petCategory = 5 THEN rtrim('RP-LI-' + convert(varchar(10), @nReptiles))
      END
        
    -- category
    select @category = CASE
         WHEN @petCategory = 1 THEN 'BIRDS'
         WHEN @petCategory = 2 THEN 'FISH'
         WHEN @petCategory = 3 THEN 'CATS'
         WHEN @petCategory = 4 THEN 'DOGS'
         WHEN @petCategory = 5 THEN 'REPTILES'
      END
    
    -- pet name
    select @name = CASE
         WHEN @petCategory = 1 THEN rtrim('Bird ' + convert(varchar(10), @nBirds))
         WHEN @petCategory = 2 THEN rtrim('Fish ' + convert(varchar(10), @nFish))
         WHEN @petCategory = 3 THEN rtrim('Cats ' + convert(varchar(10), @nCats))
         WHEN @petCategory = 4 THEN rtrim('Dogs ' + convert(varchar(10), @nDogs))
         WHEN @petCategory = 5 THEN rtrim('Reptiles ' + convert(varchar(10), @nReptiles))
      END

    -- pet description
    select @descn = CASE
         WHEN @petCategory = 1 THEN rtrim('<image src="DataImages/bird1.jpg"><br>Bird Description ' + convert(varchar(10), @nBirds))
         WHEN @petCategory = 2 THEN rtrim('<image src="DataImages/fish1.jpg"><br>Fish Description' + convert(varchar(10), @nFish))
         WHEN @petCategory = 3 THEN rtrim('<image src="DataImages/cat1.jpg"><br>Cat Description ' + convert(varchar(10), @nCats))
         WHEN @petCategory = 4 THEN rtrim('<image src="DataImages/dog1.jpg"><br>Dog Description ' + convert(varchar(10), @nDogs))
         WHEN @petCategory = 5 THEN rtrim('<image src="DataImages/lizard1.jpg"><br>Reptile Description ' + convert(varchar(10), @nReptiles))
      END
    	
	-- insert the new product
    EXEC upProductAdd @productid, @category, @name, @descn

    -- update the counters    	
    IF @petCategory = 1 SELECT @nBirds = @nBirds + 1
    IF @petCategory = 2 SELECT @nFish = @nFish + 1
    IF @petCategory = 3 SELECT @nCats = @nCats + 1
    IF @petCategory = 4 SELECT @nDogs = @nDogs + 1
    IF @petCategory = 5 SELECT @nReptiles = @nReptiles + 1
            	
    IF @petCategory % 5 = 0 
        SELECT @petCategory = 1
    ELSE
        SELECT @petCategory = @petCategory + 1
	
    -- output a progress message    
    IF @i % 1000 = 0 PRINT @i

    SELECT @i = @i + 1
END

SET NOCOUNT OFF

PRINT 'Completed: ' + convert(varchar(255), getdate());