第一次使用游标
简单的操作
1 /* 测试用表*/ 2 if(OBJECT_ID('ta') is not null) 3 drop table ta 4 CREATE TABLE [dbo].[ta]( 5 [id] [nchar](100) NULL, 6 [a] [nchar](100) NULL, 7 [b] [nchar](100) NULL 8 ) 9 10 insert into ta values(1,'aa1','ab1') 11 insert into ta values(2,'aa2','ab2') 12 insert into ta values(3,'aa3','ab3') 13 /* 测试用表*/ 14 if(OBJECT_ID('tb') is not null) 15 drop table tb 16 CREATE TABLE [dbo].[tb]( 17 [id] [nchar](100) NULL, 18 [a] [nchar](100) NULL, 19 [b] [nchar](100) NULL 20 ) 21 22 insert into tb values(1,'ba1','bb1') 23 insert into tb values(2,'ba2','bb2') 24 insert into tb values(3,'ba3','bb3') 25 /* 测试用表*/ 26 if(OBJECT_ID('tc') is not null) 27 drop table tc 28 CREATE TABLE [dbo].[tc]( 29 [id] [nchar](100) NULL, 30 [a] [nchar](100) NULL, 31 [b] [nchar](100) NULL 32 ) 33 34 insert into tc values(1,'ca1','cb1') 35 insert into tc values(2,'ca2','cb2') 36 insert into tc values(3,'ca3','cb3') 37 38 39 if(OBJECT_ID('proc_test') is not null) 40 drop procedure proc_test 41 42 go 43 44 /* 创建存储过程*/ 45 create procedure proc_test 46 as 47 /* 创建第一个游标*/ 48 declare cur_test cursor local read_only 49 for 50 select * from ta 51 /* 打开游标*/ 52 open cur_test; 53 54 declare @ID nvarchar(20); 55 declare @a nvarchar(20); 56 declare @b nvarchar(20); 57 58 /* 读取游标*/ 59 fetch next from cur_test into @id,@a,@b 60 /* 遍历游标*/ 61 while(@@FETCH_STATUS =0) 62 begin 63 /* 嵌套创建游标*/ 64 declare cur_test2 cursor local read_only 65 for select * from tb 66 open cur_test2; 67 declare @bID nvarchar(20); 68 declare @ba nvarchar(20); 69 declare @bb nvarchar(20); 70 71 fetch next from cur_test2 into @bid,@ba,@bb 72 while(@@FETCH_STATUS =0) 73 begin 74 /* 更新操作*/ 75 update tc set a=@a+@ba, b=@b+@bb where id=@bid 76 fetch next from cur_test2 into @bid,@ba,@bb 77 end 78 close cur_test2; 79 deallocate cur_test2; 80 81 fetch next from cur_test into @id,@a,@b 82 end 83 84 close cur_test; 85 deallocate cur_test; 86 87 /* 执行*/ 88 exec proc_test
性能不好，寻找代替方法。