数据块游标事例如下:
begin
declare @item_code varchar(32)--定义变量
declare @item_name varchar(32)
declare @invest_money_sum float
--定义游标
declare my_cursor cursor
for
select item_code,item_name,invest_money_sum from zftz_project_sheji_result
--打开游标
open my_cursor
fetch next from my_cursor into @item_code,@item_name,@invest_money_sum
while(@@fetch_status=0)
begin
update zftz_project_item_info1 set mainamount=@invest_money_sum where code=@item_code
fetch next from my_cursor into @item_code,@item_name,@invest_money_sum
end
--关闭游标
close my_cursor
deallocate my_cursor --卸载游标
end
存储过程实例如下:
drop procedure test_test;
go
create procedure test_test
@parm1 varchar(20),
@parm2 varchar(20)
as
begin transaction
declare @item_code varchar(32)--定义变量
declare @item_name varchar(32)
declare @invest_money_sum float
--定义游标
declare my_cursor cursor
for
select item_code,item_name,invest_money_sum from zftz_project_sheji_result
--打开游标
open my_cursor
fetch next from my_cursor into @item_code,@item_name,@invest_money_sum
while(@@fetch_status=0)
begin
update zftz_project_item_info1 set mainamount=@invest_money_sum where code=@item_code
fetch next from my_cursor into @item_code,@item_name,@invest_money_sum
end
set @parm2=@parm1+','+@parm2
print @parm2
--关闭游标
close my_cursor
deallocate my_cursor --卸载游标
--可以添加返回值与添加事务控制
if(@@error>0)
begin
rollback tran
select 0
return
end
else
begin
commit tran
select 1
return
end
--end
exec test_test 'ab','cd' --执行存储过程
查询sqlserver数据库的所有数据表:
select name from sysobjects where xtype='U'----查询数据库的所有数据表