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,@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,@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,getdate())
    
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!