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個位元組存儲一個字元，不管是英文字母還是漢字等。