SQL Server CURSOR

好久没有写带CURSOR的存储过程了，语法的有点生疏了。今天写了个玩玩。呵呵！该功能是计算数据仓库里的客户的丢失和恢复分析信息的。

 

/*
Definition:
Customer had sales before, but there is no sales in the last six month.
e.g:
Before or in June has sales
From July to Dec – no sales
Customer lost in Jan
*/

/*
Following is the Solution:
*/

--Create a new VSC_BI_CUSTOMER_STATUS to save the analysis data

USE [DW]
GO

/****** Object:  StoredProcedure [dbo].[Setting_Customers_Status]    Script Date: 2015/1/30 16:29:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 

--Create  Procedure [dbo].[Setting_Customers_Status]  --创建存储过程
ALTER Procedure [dbo].[Setting_Customers_Status]  --修改存储过程

as

--判断表是否存在
IF NOT EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'VSC_BI_CUSTOMER_STATUS') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
BEGIN
 CREATE TABLE [dbo].[VSC_BI_CUSTOMER_STATUS](
  --[CUSTOMER_NAME] [nvarchar](100) Collate Chinese_PRC_CI_AS NOT NULL,
  [CUSTOMER_NAME] [nvarchar](100) NOT NULL,
  [CUSTOMER_ID] [int] NOT NULL,
  [YEAR] [nvarchar](4) NOT NULL,
  [MONTH] [nvarchar](2) NOT NULL,
  [STATUS] [nvarchar](20) NOT NULL,
  [INVOICE_DATE] [date] NOT NULL,
  [LOST] [int] NOT NULL,
  [NORMAL] [int] NOT NULL,
  [NEW] [int] NOT NULL,
  [RECOVER] [int] NOT NULL
 ) ON [PRIMARY]

 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMERR_STATUS_YEAR]  DEFAULT ('') FOR [YEAR]
 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMER_STATUS_MONTH]  DEFAULT ('') FOR [MONTH]
 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMER_STATUS_LOST]  DEFAULT ((0)) FOR [LOST]
 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMER_STATUS_NORMAL]  DEFAULT ((0)) FOR [NORMAL]
 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMER_STATUS_NEW]  DEFAULT ((0)) FOR [NEW]
 ALTER TABLE [dbo].[VSC_BI_CUSTOMER_STATUS] ADD  CONSTRAINT [DF_VSC_BI_CUSTOMER_STATUS_RECOVER]  DEFAULT ((0)) FOR [RECOVER]
END

--Use the Cursor to get the target data

--测试的时候需要把表里的数据删除了
truncate table VSC_BI_CUSTOMER_STATUS

declare @CUSTOMER_ID int
declare @CUSTOMER_NAME nvarchar(100)   --定要定义成NVARCHAR的类型，否则后面有汉字的时候，有可能会出现乱码
declare @INVOICE_DATE date
--记录游标里的下一条记录
declare @CUSTOMER_ID_NEXT int
declare @CUSTOMER_NAME_NEXT nvarchar(100)  --定要定义成NVARCHAR的类型，否则后面有汉字的时候，有可能会出现乱码
declare @INVOICE_DATE_NEXT date

--记录丢失客户的那个的月份
declare @INVOICE_DATE_LOST date

--记录循环次数
declare @LOOP_COUNT INT
SET @LOOP_COUNT=0

declare @sql char(5000)

declare table_customer_cursor cursor for

select c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]
--COUNT(*) TransTimes
from [dbo].[VSC_BI_CUSTOMER] c,[dbo].[VSC_BI_INV_HEADERS] h
where c.[CUSTOMER_ID]=h.[CUSTOMER_ID] and h.[INVOICE_DATE]>'2012-03-31'
group by c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]
--having count(*)>=1
order by c.[CUSTOMER_ID],c.CUSTOMER_NAME,h.[INVOICE_DATE]

open table_customer_cursor

fetch next from table_customer_cursor into @CUSTOMER_ID,@CUSTOMER_NAME,@INVOICE_DATE

WHILE @@FETCH_STATUS = 0
BEGIN
 --第一进入循环的时候，@CUSTOMER_ID_NEXT是空的，这个时候直接用上面这个语句取到的值fetch next from table_customer_cursor into @CUSTOMER_ID,@CUSTOMER_NAME,@INVOICE_DATE
 if @CUSTOMER_ID_NEXT<>''
  BEGIN
   set @CUSTOMER_ID=@CUSTOMER_ID_NEXT
   set @CUSTOMER_NAME = @CUSTOMER_NAME_NEXT
   set @INVOICE_DATE = @INVOICE_DATE_NEXT
  END

 SET @LOOP_COUNT=@LOOP_COUNT+1
 print  @LOOP_COUNT
 --第一条记录插入到[dbo].[CUSTOMER_STATUS]表里，而且STATUS=NEW
 IF @LOOP_COUNT=1
  BEGIN
   --set @sql='insert into CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE) values('+@CUSTOMER_ID+','+''''+@CUSTOMER_NAME+''''+','+'NEW'+','+@INVOICE_DATE+')'
   --print @sql
   --exec(@sql)
   insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID,@CUSTOMER_NAME,'NEW',@INVOICE_DATE)
  END
 ELSE --不是一个客户的第一条记录
  --set @sql='delete from  '+@tblname
  --exec(@sql)
  print @CUSTOMER_ID
  print @CUSTOMER_NAME
  print @INVOICE_DATE

  fetch next from table_customer_cursor into @CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,@INVOICE_DATE_NEXT
  --set @CUSTOMER_ID_NEXT = @CUSTOMER_ID
  --set @CUSTOMER_NAME_NEXT = @CUSTOMER_NAME
  --set @INVOICE_DATE_NEXT = @INVOICE_DATE

  --由于HK的服务器数据库的编码方式导致了汉字显示乱码，所以用一个N'标识，表示加N前缀指定后面的字符串为UNICODE常量，
  print N'显示下一行的记录'
  print @CUSTOMER_ID_NEXT
  print @CUSTOMER_NAME_NEXT
  print @INVOICE_DATE_NEXT

   --计算逻辑如下：
   --如果相邻的两条记录是一个相同的客户，则需要比较两条记录的时间间隔，
   --1，如果时间间隔大于6个月，则表示这个客户在上一个时间的基础上加6个月份丢失的，这个时候需要把该客户在那个丢失的月份插入到CUSTOMER_STATUS表里
   --同时还要把此期间的下区间记录插入到CUSTOMER_STATUS表，表示此客户在此条记录的月份RECOVERY
   --2, 如果时间间隔小于6个月在插入此记录到CUSTOMER_STATUS表里，同时STATUS是NORMAL
   --总上所述，除了需要把循环的表记录都要插入CUSTOMER_STATUS表里外，还要插入丢失的那个月份的信息

   --如果一个客户循环到最后一条记录，此时要判断该记录距离getdate的时间是否大于6个月，如果是则是丢失LOST(这个时候需要再插入一条丢失的记录，
   --即最后一次交易的时间加6个月)，否则是正常NORMAL

   --Notes:如果客户只有一次交易，则需要特别的考虑，这个时候需要插入两条记录，一条是NEW，一条是丢失的记录
  IF (@CUSTOMER_ID=@CUSTOMER_ID_NEXT and @CUSTOMER_NAME=@CUSTOMER_NAME_NEXT)  --表示还是同一个客户
   --BEGIN
    --@INVOICE_DATE_NEXT-@INVOICE_DATE>180
    if DATEDIFF(day,@INVOICE_DATE,@INVOICE_DATE_NEXT)>180
     begin
      --set @INVOICE_DATE_LOST=@INVOICE_DATE+180
      set @INVOICE_DATE_LOST=dateadd(day,180,@INVOICE_DATE)
      --插入客户丢失的月份信息
      insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'LOST',@INVOICE_DATE_LOST)
      --插入恢复的月份信息
      insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'RECOVER',@INVOICE_DATE_NEXT) 
     end 
    else
     insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID_NEXT,@CUSTOMER_NAME_NEXT,'NORMAL',@INVOICE_DATE_NEXT) 
      
  ELSE  --不是同一个客户
   BEGIN
    SET @LOOP_COUNT=0  --是为了把每个客户的第一次交易时，他们会是我们的新客
    --此时客户循环到最后一条记录，此时要判断该记录距离getdate的时间是否大于6个月，如果是则是丢失LOST(这个时候需要再插入一条丢失的记录，
       --即最后一次交易的时间加6个月)，否则是正常NORMAL
    if DATEDIFF(day,@INVOICE_DATE,getdate())>180
     BEGIN
      insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values(+@CUSTOMER_ID,@CUSTOMER_NAME,'LOST',dateadd(day,180,@INVOICE_DATE))
      --update CUSTOMER_STATUS set STATUS='LOST' where CUSTOMER_ID=@CUSTOMER_ID andCUSTOMER_NAME=@CUSTOMER_NAME and INVOICE_DATE=dateadd(day,180,@INVOICE_DATE)
     END
   END
END

close table_customer_cursor
deallocate table_customer_cursor

--更新下YEAR和MONTH,其实BI里可以直接用INOVICE_DATE就可以关联到YEAR，MONTH，QUARTER您自己看吧，需要就加上。
update VSC_BI_CUSTOMER_STATUS set YEAR=YEAR(INVOICE_DATE),MONTH=MONTH(INVOICE_DATE)

--更新LOST，RECOVER，NEW，NORMAL数量
update VSC_BI_CUSTOMER_STATUS set LOST=1 WHERE STATUS='LOST'
update VSC_BI_CUSTOMER_STATUS set RECOVER=1 WHERE STATUS='RECOVER'
update VSC_BI_CUSTOMER_STATUS set NEW=1 WHERE STATUS='NEW'
update VSC_BI_CUSTOMER_STATUS set NORMAL=1 WHERE STATUS='NORMAL'

/*调用执行Procedure
--EXEC [dbo].[Setting_Customers_Status]
*/

/* 验证用的SQL
truncate table  CUSTOMER_STATUS

select * from CUSTOMER_STATUS
where 1=1
--AND status='RECOVERY'
AND CUSTOMER_ID=3698
order by customEr_id ,invoice_date

SELECT * FROM [dbo].[VSC_BI_CUSTOMER_STATUS] where CUSTOMER_ID=3698

SELECT * FROM [dbo].[VSC_BI_CUSTOMER_STATUS] where STATUS='RECOVER'

TRUNCATE TABLE [dbo].[VSC_BI_CUSTOMER_STATUS]

--汉字乱码
select * from [dbo].[VSC_BI_CUSTOMER_STATUS] WHERE CUSTOMER_ID=12101
select * from [dbo].[VSC_BI_CUSTOMER_STATUS] WHERE CUSTOMER_ID=49531
*/

 

在写上面的存储过程的时候出现了下面的小故事需要修改:

1，一开始我在创建表的时候，其中一个CUSTOMER_NAME的列我用的是char(100), 由于我们DW服务器的数据库字符集是：Chinese_HongKong_Stroke_90_CI_AS,这个导致我的汉字部分有出现问号乱码的情况出现。

2，直到上面的原因后，我把表的相关子段设计成NVARCHAR类型，但是在执行存储过程后还是发现和上面一样有问号乱码

3，再次查看存储过程，发现我存储过程的@CUSTOMRR_NAME的类型些的是char(100)，把类型改成nvarchar(100)后，再次执行存储过程，终于没有了乱码。

Lessons Learned:

如果数据库的数据只是英文和数字，可以用varchar类型，如果数据库需要程序很多国家的语言，尤其像汉字的话，就必须把字段类型设计成nvarchar.

其中N表示是National Language，国家语言。NVARCHAR表示用2个字节存储一个字符，不管是英文字母还是汉字等。