sqlserver2005游标的使用

从数据库里面逐行逐行取出来，然后再依次进行update，或者修改操作，这时候需要游标

SET NOCOUNT ON
USE MB
GO

--定义变量
DECLARE @Enterprise_ID    BigInt,
    @Contact_ID     BigInt,
    @Bilateral_ID    INT,
    @Activity_ID    INT,
    @Activity_Code    Char(36),
    @Next_Call_Date    datetime,

      @VIN      VarChar(30),
    @Contract_date    VarChar(30),
    @Account_Number      VarChar(30),
    @Purchase_date    VarChar(30),
    @Model      VarChar(30),
    @Branch_Number          VarChar(30),
    @NewOrUsed     VarChar(30),
    @Mature_date    VarChar(30),
    @Dealer_Number          VarChar(30),

    @Term_Number    VarChar(20),
    @Last_Update_By    VarChar(20)

SET @Activity_ID = 553    -- 553 --Production Activity_ID
SET @Last_Update_By = 'Napoleon'
SET @Next_Call_Date = Convert(DateTime, Convert(VarChar(10), DateAdd(Day, 1, GetDate()), 121) + ' 9:00:00')

---声明cursor

DECLARE tempInput CURSOR

FOR
SELECT
   c.Bilateral_ID,
   c.Enterprise_ID,
   c.Contact_ID,
   VIN = LEFT(t.VIN, 20),
   t.Purchase_Date,
   t.Decoded_Model,
   NewOrUsed =
   case
    when t.Number_Owners ='1' then 'New'
    else 'Used'
   end,
   t.Maturity_Date,
   t.Selling_Dealer_Code,
   Term =CONVERT(VarChar(20), dv.TERM)
FROM dragnet_5064_ticket_7561_20081103 t
left join napoleon_report..data_vehicles dv on
   t.vin = dv.vin
left join mb..Contacts_DealerContacts c on
   dv.owner_contact_id = c.contact_id
--打开游标  

--取出了游标里的数据，然后把取出来的数据放到变量里面
--//从游标里取出数据赋值到我们刚才声明的2个变量中

Open tempInput
FETCH NEXT FROM tempInput INTO @Bilateral_ID,@Enterprise_ID,@Contact_ID,@VIN,@Purchase_date,@Model,@NewOrUsed,@Mature_date,@Dealer_Number,@Term_Number

//判断游标的状态
//0 fetch语句成功
//-1 fetch语句失败或此行不在结果集中
//-2被提取的行不存在

--然后把变量的值给 插入到所需要的表中

While (@@FETCH_STATUS <> -1)（一个while相当与一个for循环）
BEGIN （begin对应与一个end）
   SET @Activity_Code = CONVERT(Char(36), NEWID())

   INSERT INTO act_item (Buying_Center_ID, Enterprise_ID, Contact_ID, Activity_ID, Activity_date, Activity_Code, Closed, Closed_Reason, Next_Call_Date, Next_Call_Time, Telemarketers, Bilateral_ID, rowguid, Last_Update_Date, Last_Update_By, created_by)
   VALUES (1, @Enterprise_ID, @Contact_ID, @Activity_ID, getdate(), @Activity_Code, 0, NULL, @Next_Call_Date, @Next_Call_Date, NULL, @Bilateral_ID, NEWID(), GetDate(), @Last_Update_By, @Last_Update_By)

   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'VIN', @VIN)
  
   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'Purchase Date', @Purchase_date)

   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'Model', @Model)
  
   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'New/Used', @NewOrUsed)

   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'Maturity Date', @Mature_date)
  
   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'Dealer Number', @Dealer_Number)

   INSERT INTO actd_dfo (Activity_Code, Activity_ID, Row_IDName, Data)
   VALUES (@Activity_Code, @Activity_ID, 'Term Number', @Term_Number)

--END（在处理程序的时候就是这个地方写错了，让我们那帮头找了很久，因为这句，他就提起end了,游标不会在往下面走了，陷入了死循环，end写在后面那个地方）

--用游标去取下一条记录
FETCH NEXT FROM tempInput INTO @Bilateral_ID,@Enterprise_ID,@Contact_ID,@VIN,@Purchase_date,@Model,@NewOrUsed,@Mature_date,@Dealer_Number,@Term_Number END
--//关闭游标
CLOSE tempInput
--//撤销游标
DEALLOCATE tempInput