SQLSERVER存储过程返回游标的处理

1. 存储过程返回游标

USE [TEST_DB]
GO

/****** [PRT].[Move_Data_Return_Cursor]   Script Date: 03/08/2012 17:38:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************
*把数据从表 DATA_SOURCE_TABLE 移动到表 TEST_TABLE .
返回游标供Move_Data_Handle_Cursor供Move_Data_Handle_Cursor存储过程使用.
*DataServer: 110.110.110.110
*DataBase:   TEST_DB
*Name:       [Move_Data_Return_Cursor]
*Function:   
*Input:	     @overTimeHour INT
*Output:	   @CURSOR_PriceChangeRecord CURSOR
*Creator:    GREATWQS 2012-02-23
*Updated:    GREATWQS 2012-03-08  UPDATE NEW REQUIREMENT
**********************************************************************************/
ALTER PROCEDURE [PRT].[Move_Data_Return_Cursor] 
	-- Add the parameters for the stored procedure here	
	@overTimeHour INT,
	@CURSOR_PriceChangeRecord CURSOR VARYING OUTPUT
AS

BEGIN
	  -- SET NOCOUNT ON added to prevent extra result sets from
	  -- interfering with SELECT statements.
	  SET NOCOUNT ON;
	  	
	  -- print @overTimeHour;
	  
	  -- 1. 声明游标: DECLARE CURSOR_PriceChangeRecord
	  SET @CURSOR_PriceChangeRecord = CURSOR
	  FORWARD_ONLY  STATIC  
      FOR 
         SELECT ItemNo,
                ItemName,
                ItemColor,
                ItemSize,
                ItemMadeIn,
                InDate 
         FROM   PRT.DATA_SOURCE_TABLE  WITH(NOLOCK)
         WHERE  InDate > dateadd(HOUR, -@overTimeHour, getdate())
         -- 在这里进行时间的限定. 
         
    -- 2. 打开游标
    OPEN @CURSOR_PriceChangeRecord
    
END

 

2. 存储过程处理返回游标

 

USE [TEST_DB]
GO

/****** [PRT].[Move_Data_RHandle_Cursor]  Script Date: 03/08/2012 17:39:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************
*把数据从表 DATA_SOURCE_TABLE 移动到表 TEST_TABLE
*把游标中的数据,插入到表TEST_TABLE.
*DataServer: 110.110.110.110
*DataBase:   TEST_DB
*Name:       [Move_Data_Handle_Cursor]
*Function:   
*Input:	     @overTimeHour INT
*Output:	 
*Creator:    GREATWQS 2012-02-23
*Updated:    GREATWQS 2012-03-08  UPDATE NEW REQUIREMENT
**********************************************************************************/
ALTER PROCEDURE [PRT].[Move_Data_Handle_Cursor] 
    -- 超时时间(小时)
    @overTimeHour INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    DECLARE
         -- 需要插入表TEST_TABLE,数据来源声明
         @ItemNumber_Insert       CHAR(20),
         @ItemName_Insert         CHAR(50),
         @ItemColor_Insert        CHAR(10),
         @ItemSize_Insert         CHAR(5),
         @ItemWhereMadeIn_Insert  CHAR(20),
         @createTime_Insert       DATETIME,
         @changeTime_Insert       DATETIME,
         @lastChangeTime_Insert   DATETIME,
         @priority_Insert         INT,
         @itemType_Insert         INT,
         @active_Insert           INT,
         -- ItemNumber Record num in table
         @totalNum_SelectDB       INT,
         @changeTime_SelectDB     DATETIME
    
    -- Set Default Value
    SET @ItemWhereMadeIn_Insert   = 0
    SET @createTime_Insert        = getdate()
    SET @lastChangeTime_Insert    = null
    SET @priority_Insert          = 0
    SET @itemType_Insert          = 0
    SET @active_Insert            = 1
    
    -- 1. 声明游标: 在过程内部自己定义有游标时,调用游标前面不加@符号
    DECLARE @CURSOR_Result CURSOR
      
    -- 2. 得到上个游标, 上个游标已经打开
    EXEC Move_Data_Return_Cursor @overTimeHour, 
         @CURSOR_PriceChangeRecord = @CURSOR_Result OUTPUT
      
    -- 3. 抓取游标中的数据: FETCH CURSOR_Result
    FETCH NEXT  FROM  @CURSOR_Result
    INTO  @ItemNumber_Insert, 
          @ItemName_Insert, 
          @ItemColor_Insert, 
          @ItemSize_Insert, 
          @changeTime_Insert
    
    -- 4. 对游标中的每一个记录进行处理: 循环
    WHILE (@@FETCH_STATUS = 0)
    BEGIN  
         -- 查看此ItemNumber_Insert是否已经存在表中
         SELECT TOP 1 @totalNum_SelectDB = COUNT(*) 
         FROM PRT.TEST_TABLE WITH(NOLOCK)
         WHERE ItemNumber = @ItemNumber_Insert         
         
         -- 如果表不存在此@ItemNumber_Insert, 则插入
         IF @totalNum_SelectDB = 0
         BEGIN
             INSERT INTO PRT.TEST_TABLE(
                    [ItemNumber],
                    [ItemName],
                    [ItemColor],
                    [ItemSize],
                    [MadeIn],
                    [createTime],
                    [changeTime],
                    [lastChangeTime],
                    [priority],
                    [itemType],
                    [active])
              VALUES ( 
                    @ItemNumber_Insert,
                    @ItemName_Insert,
                    @ItemColor_Insert,
                    @ItemSize_Insert,
                    @ItemWhereMadeIn_Insert,
                    @createTime_Insert,
                    @changeTime_Insert,
                    @lastChangeTime_Insert,
                    @priority_Insert,
                    @itemType_Insert,
                    @active_Insert
                   )
         END
         -- 如果此ItemNumber存在于表中
         ELSE 
         BEGIN
             -- 查看此ItemNumber_Insert的记录
             SELECT TOP 1 @changeTime_SelectDB = changeTime
             FROM PRT.TEST_TABLE WITH(NOLOCK)
             WHERE ItemNumber = @ItemNumber_Insert
             
             -- If item has exists in table, and changeTime<=newItem.changeTime 
             -- fresh the changeTime = newItem.changeTime, set active=1;
             IF @changeTime_SelectDB < @changeTime_Insert  
             BEGIN
                 UPDATE PRT.TEST_TABLE
                 SET    changeTime = @changeTime_Insert, 
                        active = 1
                 WHERE  ItemNumber = @ItemNumber_Insert
             END
         END
         
         -- FETCH NEXT RECORD FROM @CURSOR_Result
         FETCH NEXT  FROM  @CURSOR_Result
         INTO  @ItemNumber_Insert, 
               @ItemName_Insert, 
               @ItemColor_Insert, 
               @ItemSize_Insert, 
               @changeTime_Insert
    END
    
    -- 5. 关闭游标
    CLOSE @CURSOR_Result
    
    -- 6. 删除游标
    DEALLOCATE @CURSOR_Result    
    
    -- Delete overtime  item, set active=0：changeTime<getdate()-48；
    UPDATE PRT.TEST_TABLE
    SET    active=0
    WHERE  changeTime < dateadd(HOUR, -@overTimeHour, getdate())
    
END