SQL Server CURSOR

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

--Create a new VSC_BI_CUSTOMER_STATUS to save the analysis data

USE [DW]

GO

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 @[email protected]_ID_NEXT

   set @CUSTOMER_NAME = @CUSTOMER_NAME_NEXT

   set @INVOICE_DATE = @INVOICE_DATE_NEXT

  END

 SET @[email protected]_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('[email protected]_ID+','+''''[email protected]_NAME+''''+','+'NEW'+','[email protected]_DATE+')'

   --print @sql

   --exec(@sql)

   insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values([email protected]_ID,@CUSTOMER_NAME,'NEW',@INVOICE_DATE)

  END

 ELSE --不是一个客户的第一条记录

  --set @sql='delete from  '[email protected]

  --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 (@[email protected]_ID_NEXT and @[email protected]_NAME_NEXT)  --表示还是同一个客户

   --BEGIN

    [email protected][email protected]_DATE>180

    if DATEDIFF(day,@INVOICE_DATE,@INVOICE_DATE_NEXT)>180

     begin

      --set @[email protected]_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([email protected]_ID_NEXT,@CUSTOMER_NAME_NEXT,'LOST',@INVOICE_DATE_LOST)

      --插入恢复的月份信息

      insert into VSC_BI_CUSTOMER_STATUS(CUSTOMER_ID,CUSTOMER_NAME,STATUS,INVOICE_DATE)values([email protected]_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([email protected]_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 [email protected]_ID and[email protected]_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'

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

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个字节存储一个字符，不管是英文字母还是汉字等。