SqlServer游标、存储过程及数据块执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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'
--执行存储过程

1
select name from sysobjects where xtype='U'----查询数据库的所有数据表

1