给你看个我以前写的吧
declare tmpGlobalBillCursor cursor for
select Account from #tmpGlobalBill open tmpGlobalBillCursor
fetch next from tmpGlobalBillCursor into @tmpAccount
while @@FETCH_STATUS=0
begin
begin tran
--帐目
insert into HisFolio
select * from Folio where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from Folio where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
--单信息
insert into HisGlobalBill
select * from GlobalBill where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from GlobalBill where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
--商品
insert into HisGBillResvItem
select * from GBillResvItem where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from GBillResvItem where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
commit tran
ErrorHandler:
rollback tran fetch next from tmpGlobalBillCursor into @tmpAccount
end close tmpGlobalBillCursor
deallocate tmpGlobalBillCursor
declare tmpGlobalBillCursor cursor for
select Account from #tmpGlobalBill open tmpGlobalBillCursor
fetch next from tmpGlobalBillCursor into @tmpAccount
while @@FETCH_STATUS=0
begin
begin tran
--帐目
insert into HisFolio
select * from Folio where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from Folio where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
--单信息
insert into HisGlobalBill
select * from GlobalBill where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from GlobalBill where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
--商品
insert into HisGBillResvItem
select * from GBillResvItem where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
delete from GBillResvItem where Account=@tmpAccount
if @@error<>0 goto ErrorHandler
commit tran
ErrorHandler:
rollback tran fetch next from tmpGlobalBillCursor into @tmpAccount
end close tmpGlobalBillCursor
deallocate tmpGlobalBillCursor
解决方案 »
- 求助各操作系统对SQL软件的支持
- 高分求助触发器!
- 查询性能优化。。。。
- sql2005两表产生如下格式的表
- 有趣的问题:'如何在一个数据库中查找到含有某些数据的表'?
- 局域网有2台电脑访问远程主机特别慢
- SQL复制, 数据丢失及部分数据不能同步
- 请问在sql sever 2000是不是只能安装在win2000 sever版下?
- olap之查询语句(select..from..where)
- 请教数据库字段更改问题?
- 急问:数据库不小心恢复成其他数据库了,怎么还原回来?
- ███ 我有一个表字段多,每次插入记录Insert into ...values...时候都要从表中找对应的字段非常麻烦,有没有什么办法写一个存储过程自动
GO-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fnameOPEN authors_cursor-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @au_fname + ' ' + @au_lname
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO
declare @SqlStr varchar(8000)
declare KsCy_cur cursor for --定义游标
select name from sysobjects WHERE name LIKE 'KsCy45%'
open KsCy_cur --打开游标
fetch next from KsCy_cur --执行第一次取数操作
into @TableName --对变量赋值
set @SqlStr = 'select * from ' + @TableName
WHILE @@FETCH_STATUS = 0 --检查上一次操作的执行状态,若未完则循环取数
BEGIN
fetch next from KsCy_cur
into @TableName
set @SqlStr = @SqlStr +' UNION ALL ' + 'select * from ' + @TableName
end
close KsCy_cur --关闭游标
DEALLOCATE KsCy_cur
select ..... --声明游标open cursor_name --打开游标
fetch next from cursor_name --更新游标第一行
while @@fetch_status = 0 -- 游标更新成功
begin
... -- 需要做的操作
end
close cursor_name --关闭游标
deallocate cursor_name --释放游标
--
上述是基本结构