数据库--存储过程示例

-- =============================================
-- 名称 : sp_sale_member_statistic 
--
-- 功能 ：会员购物信息统计
--
-- 参数 : Begindate 统计起始日期
--           Enddate 统计结束日期
--
-- 作者 ：shmilylff
--
-- 日期 ：2005年8月9日
-- =============================================

USE KingSun
GO

IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = 'sp_sale_member_statistic' 
    AND    type = 'P')
    DROP PROCEDURE sp_sale_member_statistic
GO


USE KingSun
GO

CREATE PROCEDURE sp_sale_member_statistic 
  @BeginDate char(8),
        @EndDate char(8)
AS
 -- 不存在sale_member_statistic,则创建
        IF NOT EXISTS(SELECT name 
     FROM   sysobjects 
     WHERE  name = 'sale_member_statistic' 
     AND    type = 'U')
 BEGIN
              CREATE TABLE [dbo].[sale_member_statistic] (
   [sort_id] [int] NOT NULL ,
   [customer] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
   [customer_per] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
   [money] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
   [money_per_total] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
   [money_per_customer] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
  ) ON [PRIMARY]

        END

        -- 删除目的表中的记录
        DELETE FROM  sale_member_statistic

 -- 统计指定日期内会员的消费信息
        EXECUTE sp_sale_statistic_member @Begindate,@Enddate

        -- 定义要用变量
        DECLARE @sortid decimal(18,2)
        DECLARE @customer decimal(18,0),@customer_per decimal(18,2)
        DECLARE @money decimal(18,2),@money_per_total decimal(18,2),@money_per_customer decimal(18,2)  

        DECLARE @customer_s varchar(50),@customer_per_s varchar(50)
        DECLARE @money_s varchar(50),@money_per_total_s varchar(50),@money_per_customer_s varchar(50)

 --=================================================================================================
 --统计会员总的消费信息
        --=================================================================================================
        DECLARE @total_customer decimal(18,0),@protype_customer decimal(18,0)
        DECLARE @total_money decimal(18,2),@protype_money decimal(18,2)

        SET @sortid = 0

        -- 会员总的消费者次数
        SELECT @customer = count(*) 
    FROM sale_and_shoptime_member      
       
  -- 会员总的消费金额和平均消费金额
        SELECT @money = sum(price*discount*num) 
        FROM sale_and_shoptime_member
         
        IF @money IS NULL SET @money = 0

        IF @customer=0 
            SET @money_per_customer = 0
        ELSE
            SET @money_per_customer = @money/@customer

       SET @customer_s = '会员总消费次数：' + CONVERT(varchar(50),@customer) + '次'
       SET @customer_per_s = ''
       SET @money_s = '总消费金额：' + CONVERT(varchar(50),@money) + '元'
       SET @money_per_total_s = ''
       SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
       PRINT('插入会员总的消费信息')
       
 --将会员总的消费信息插入表中
       INSERT INTO sale_member_statistic  
       VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

       SET @total_customer = @customer
       SET @total_money = @money

  --=================================================================================================
 -- 按品种统计会员消费
        --=================================================================================================
        -- 定义存放品种编号和品种名称的变量
        DECLARE @protype_id int,@protype_name varchar(10)
      
        -- 定义品种的游标
 DECLARE protype_cursor CURSOR FOR 
 SELECT distinct protype_id
 FROM sale_and_shoptime_member
        ORDER BY protype_id
 
 -- 打开游标
 OPEN protype_cursor
 
 -- 取得第一个品种的编号
 FETCH NEXT FROM protype_cursor 
 INTO @protype_id
 
 --===========================================================================================
 -- 针对指定品种的统计  
        --===========================================================================================
 WHILE @@FETCH_STATUS = 0
 BEGIN
           -- 获取品种名称
           SELECT @protype_name = protype_name
    FROM pro_type_name
           WHERE num = @protype_id

           --===========================================================================================
    -- 针对指定品种的会员总的消费信息统计
           --===========================================================================================
           -- 品种会员总的消费次数和消费总金额
           SELECT @customer = count(*),@money = sum(price*discount*num)
           FROM sale_and_shoptime_member
           WHERE protype_id = @protype_id

           IF @money IS NULL SET @money = 0
        
           -- 品种会员总的消费次数比重
           IF @total_customer=0 
              SET @customer_per = 0
           ELSE
              SET @customer_per = @customer/@total_customer*100

           -- 品种会员总的消费金额比重
           IF @total_money=0 
              SET @money_per_total = 0
           ELSE
              SET @money_per_total = @money/@total_money*100

           -- 品种会员总的平均金额
           IF @customer=0 
              SET @money_per_customer = 0
           ELSE
              SET @money_per_customer = @money/@customer
  
           SET @customer_s = '  [' + @protype_name + ']会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
           SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
           SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
           SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
           SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
           PRINT(@protype_name + '插入品种会员总的消费信息')
       
    --将品种会员总的消费信息插入表中
           SET @sortid = @sortid + 1

           INSERT INTO sale_member_statistic  
           VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

           -- 指定品种会员总的消费次数和消费金额
           SET @protype_customer = @customer
           SET @protype_money = @money  

          --===========================================================================================
   -- 针对指定品种的会员按次数消费信息统计
          --===========================================================================================
         DECLARE @LOOP int

         SET @LOOP = 1

         WHILE @LOOP <= 3
         BEGIN
     -- 品种会员按次数消费次数和消费总金额
            SELECT @customer = count(*),@money = sum(price*discount*num)
            FROM sale_and_shoptime_member
            WHERE protype_id = @protype_id and shop_time = @LOOP

            IF @money IS NULL SET @money = 0
        
            -- 品种会员按次数消费次数比重
           IF @protype_customer=0 
              SET @customer_per = 0
           ELSE
              SET @customer_per = @customer/@protype_customer*100

           -- 品种会员按次数消费金额比重
           IF @protype_money=0 
              SET @money_per_total = 0
           ELSE
              SET @money_per_total = @money/@protype_money*100

           -- 品种会员按次数消费平均金额
           IF @customer=0 
              SET @money_per_customer = 0
           ELSE
              SET @money_per_customer = @money/@customer
  
           SET @customer_s = '    其中' + CONVERT(char(1),@LOOP) + '次消费次数：' + CONVERT(varchar(50),@customer) + '次'
           SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
           SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
           SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
           SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
           PRINT(@protype_name + '插入品种会员按次数消费信息')
       
     --将品种会员按次数消费信息插入表中
           SET @sortid = @sortid + 1

           INSERT INTO sale_member_statistic  
           VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

           SET @LOOP = @LOOP + 1
         END  
         
         -- 取得下一个品种的编号
  FETCH NEXT FROM protype_cursor 
  INTO @protype_id      
        END

        -- 关闭游标
 CLOSE protype_cursor

        -- 释放游标
 DEALLOCATE protype_cursor


        --===========================================================================================
        -- 按入会时间统计会员消费信息
 --===========================================================================================
        -- 入会日期
        DECLARE @indate char(4)

        -- 定义品种的游标
 DECLARE indate_cursor CURSOR FOR 
 SELECT distinct indate
 FROM v_sale_member
        ORDER BY indate
 
 -- 打开游标
 OPEN indate_cursor
 
 -- 取得最早的入会日期
 FETCH NEXT FROM indate_cursor 
 INTO @indate

       WHILE @@FETCH_STATUS = 0
 BEGIN
          --===========================================================================================
    -- 针对指定入会日期统计会员消费信息
           --===========================================================================================
           -- 品种会员总的消费次数和消费总金额
           SELECT @customer = count(*),@money = sum(price*discount*num)
           FROM v_sale_member
           WHERE indate = @indate

           IF @money IS NULL SET @money = 0
        
           -- 品种会员总的消费次数比重
           IF @total_customer=0 
              SET @customer_per = 0
           ELSE
              SET @customer_per = @customer/@total_customer*100

           -- 品种会员总的消费金额比重
           IF @total_money=0 
              SET @money_per_total = 0
           ELSE
              SET @money_per_total = @money/@total_money*100

           -- 品种会员总的平均金额
           IF @customer=0 
              SET @money_per_customer = 0
           ELSE
              SET @money_per_customer = @money/@customer
  
           SET @customer_s = '[' + @indate + ']入会会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
           SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
           SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
           SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
           SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
           PRINT(@protype_name + '插入指定日会日期会员消费信息')
       
    --将指定日会日期会员消费信息插入表中
           SET @sortid = @sortid + 1

           INSERT INTO sale_member_statistic  
           VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

   -- 取得下一个的入会日期
   FETCH NEXT FROM indate_cursor 
   INTO @indate
        END

        -- 关闭游标
        CLOSE protype_cursor

        -- 释放游标
 DEALLOCATE protype_cursor

         --===========================================================================================
        -- 按年龄统计会员消费信息
 --===========================================================================================
        -- 30岁以下会员消费信息
        SELECT @customer = count(*),@money = sum(price*discount*num)
        FROM v_sale_member
        WHERE age < 30

        IF @money IS NULL SET @money = 0
        
        -- 30岁以下会员会员总的消费次数比重
        IF @total_customer=0 
           SET @customer_per = 0
        ELSE
           SET @customer_per = @customer/@total_customer*100

        -- 30岁以下会员会员总的消费金额比重
        IF @total_money=0 
           SET @money_per_total = 0
        ELSE
           SET @money_per_total = @money/@total_money*100

        -- 30岁以下会员会员总的平均金额
        IF @customer=0 
           SET @money_per_customer = 0
        ELSE
           SET @money_per_customer = @money/@customer
            
        SET @customer_s = '[30岁以下]会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
        SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
        SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
        SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
        SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
        PRINT(@protype_name + '插入30岁以下会员消费信息')
       
   --将30岁以下会员会员消费信息插入表中
        SET @sortid = @sortid + 1

        INSERT INTO sale_member_statistic  
        VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

         -- 30岁－40岁会员消费信息
        SELECT @customer = count(*),@money = sum(price*discount*num)
        FROM v_sale_member
        WHERE age >= 30 and age <=40 

        IF @money IS NULL SET @money = 0
        
        -- 30岁－40岁会员总的消费次数比重
        IF @total_customer=0 
           SET @customer_per = 0
        ELSE
           SET @customer_per = @customer/@total_customer*100

        -- 30岁－40岁会员总的消费金额比重
        IF @total_money=0 
           SET @money_per_total = 0
        ELSE
           SET @money_per_total = @money/@total_money*100

        -- 30岁－40岁会员总的平均金额
        IF @customer=0 
           SET @money_per_customer = 0
        ELSE
           SET @money_per_customer = @money/@customer
            
        SET @customer_s = '[30岁至40岁]会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
        SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
        SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
        SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
        SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
        PRINT(@protype_name + '插入30岁至40岁会员消费信息')
       
   --将30岁至40岁会员消费信息插入表中
        SET @sortid = @sortid + 1

        INSERT INTO sale_member_statistic  
        VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)
       
        -- 40岁以上会员消费信息
        SELECT @customer = count(*),@money = sum(price*discount*num)
        FROM v_sale_member
        WHERE age > 40

        IF @money IS NULL SET @money = 0
        
        -- 40岁以上会员总的消费次数比重
        IF @total_customer=0 
           SET @customer_per = 0
        ELSE
           SET @customer_per = @customer/@total_customer*100

        --  40岁以上会员总的消费金额比重
        IF @total_money=0 
           SET @money_per_total = 0
        ELSE
           SET @money_per_total = @money/@total_money*100

        --  40岁以上会员总的平均金额
        IF @customer=0 
           SET @money_per_customer = 0
        ELSE
           SET @money_per_customer = @money/@customer
            
        SET @customer_s = '[40岁以上]会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
        SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
        SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
        SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
        SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
        PRINT(@protype_name + '插入40岁以上会员消费信息')
       
   --将40岁以上会员消费信息插入表中
        SET @sortid = @sortid + 1

        INSERT INTO sale_member_statistic  
        VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

 --===========================================================================================
        -- 按地区统计会员消费信息
 --===========================================================================================
        -- 本地会员消费信息
        SELECT @customer = count(*),@money = sum(price*discount*num)
        FROM v_sale_member
        WHERE area = '0'

        IF @money IS NULL SET @money = 0
        
        -- 本地会员会员总的消费次数比重
        IF @total_customer=0 
           SET @customer_per = 0
        ELSE
           SET @customer_per = @customer/@total_customer*100

        -- 本地会员会员总的消费金额比重
        IF @total_money=0 
           SET @money_per_total = 0
        ELSE
           SET @money_per_total = @money/@total_money*100

        -- 本地会员会员总的平均金额
        IF @customer=0 
           SET @money_per_customer = 0
        ELSE
           SET @money_per_customer = @money/@customer
            
        SET @customer_s = '[本地]会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
        SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
        SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
        SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
        SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
        PRINT(@protype_name + '插入本地会员消费信息')
       
   --将本地会员会员消费信息插入表中
        SET @sortid = @sortid + 1

        INSERT INTO sale_member_statistic  
        VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)

        -- 非本地会员消费信息
        SELECT @customer = count(*),@money = sum(price*discount*num)
        FROM v_sale_member
        WHERE area = '1'

        IF @money IS NULL SET @money = 0
        
        -- 非本地会员会员总的消费次数比重
        IF @total_customer=0 
           SET @customer_per = 0
        ELSE
           SET @customer_per = @customer/@total_customer*100

        -- 非本地会员会员总的消费金额比重
        IF @total_money=0 
           SET @money_per_total = 0
        ELSE
           SET @money_per_total = @money/@total_money*100

        -- 非本地会员会员总的平均金额
        IF @customer=0 
           SET @money_per_customer = 0
        ELSE
           SET @money_per_customer = @money/@customer
            
        SET @customer_s = '[周边地区]会员消费次数：' + CONVERT(varchar(50),@customer) + '次'
        SET @customer_per_s = '占 ' + CONVERT(varchar(50),@customer_per) + '%'
        SET @money_s = '消费金额：' + CONVERT(varchar(50),@money) + '元'
        SET @money_per_total_s = '占 ' + CONVERT(varchar(50),@money_per_total) + '%'
        SET @money_per_customer_s = '平均消费金额：' + CONVERT(varchar(50),@money_per_customer) + '元'
       
        PRINT(@protype_name + '插入非本地会员消费信息')
       
   --将非本地会员会员消费信息插入表中
        SET @sortid = @sortid + 1

        INSERT INTO sale_member_statistic  
        VALUES(@sortid,@customer_s,@customer_per_s,@money_s,@money_per_total_s,@money_per_customer_s)
GO