sybase iq 如何在游标循环中Commit

对于sybase IQ , 在游标循环中，主要想每更新100条提交一次 , 但当提交后就会导致关闭游标

ALTER PROCEDURE "ETL_tbl_test1"()
as
/* RESULT( column_name column_type, ... ) */
BEGIN

declare @a integer
declare @b integer
declare @c varchar(12)
declare @d char(4)
declare @n integer
declare @cn integer

declare v_name cursor for select * from tbl_test for update of a,b

---set close on endtran off
set @n =1

open v_name
fetch v_name into @a,@b,@c,@d

while (@@sqlstatus=0)
begin
print '@a....'||@a
print '@b....'||@b
print '@c....'||@c
print '@d....'||@d

set @a=@a*11
set @b=@b*22

update tbl_test set a=@a,b=@b where current of v_name

set @cn=mod(@n,100)

----主要想每更新100条提交一次
if (@cn=0)
begin
commit
end

set @n=@n+1

fetch v_name into @a,@b,@c,@d

end

close v_name
deallocate v_name

--set close on endtran on
END

[ 本帖最后由 wejie_2000 于 2009

ITPub博客

Sybase

sybase iq 如何在游标循环中Commit

最新文章