SQLSERVER存储过程返回游标的处理
1. 存储过程返回游标
USE [TEST_DB]GO/****** [PRT].[Move_Data_Return_Cursor] Script Date: 03/08/2012 17:38:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/**********************************************************************************把数据从表 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 OUTPUTASBEGIN -- 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 ONGOSET QUOTED_IDENTIFIER ONGO/**********************************************************************************把数据从表 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 INTASBEGIN -- 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
?
