1. 存储过程返回游标
sql代码
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. 存储过程处理返回游标
sql代码
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