给阿勇的游标更新例子：
－－－－－－－－－－－－－－－
1
-- 1 建表
2 CREATE TABLE myTable1(
3 field1 VARCHAR(30) NOT NULL,
4 field2 INT NULL,
5 field3 INT NULL
6 CONSTRAINT [PK_CurrencyRate] PRIMARY KEY CLUSTERED
7 (
8 [field1]
9 ) ON [PRIMARY]
10 )ON [PRIMARY]
11 GO
12
13-- 2 插入测试数据
14 INSERT INTO myTable1(field1, field2)
15 VALUES('AAAaa',5)
16 INSERT INTO myTable1(field1, field2)
17 VALUES('AAAbb',5)
18 INSERT INTO myTable1(field1, field2)
19 VALUES('AAAcc',6)
20 INSERT INTO myTable1(field1, field2)
21 VALUES('AAA1k',6)
22 INSERT INTO myTable1(field1, field2)
23 VALUES('AAAdd',6)
24 INSERT INTO myTable1(field1, field2)
25 VALUES('AAAee',6)
26 INSERT INTO myTable1(field1, field2)
27 VALUES('AAAff',7)
28 INSERT INTO myTable1(field1, field2)
29 VALUES('AAAgg',8)
30 INSERT INTO myTable1(field1, field2)
31 VALUES('AAAhh',8)
32 INSERT INTO myTable1(field1, field2)
33 VALUES('AAAii',8)
34 INSERT INTO myTable1(field1, field2)
35 VALUES('AAAjj',8)
36 INSERT INTO myTable1(field1, field2)
37 VALUES('AAAkk',8)
38 INSERT INTO myTable1(field1, field2)
39 VALUES('AAAll',9)
40 INSERT INTO myTable1(field1, field2)
41 VALUES('AAAmm',9)
42 INSERT INTO myTable1(field1, field2)
43 VALUES('AAAnn',9)
44
45 ---SELECT * FROM myTable1 ORDER BY field2
46
47-- 3 用游标更新 field3
48
49 --赋初值
50 DECLARE @f1 VARCHAR(30), @f2 INT, @f3 INT,@Temp_F2 INT
51 SELECT @F1='',@F2=0,@F3=1,@Temp_F2=0 --@f3 需要 赋 初值 1 ,其它可随意
52 DECLARE CURSOR1 CURSOR FOR SELECT field1, field2 FROM myTable1 ORDER BY field2 -- 一定要注意排序
53
54 OPEN CURSOR1
55
56 FETCH CURSOR1 INTO @f1, @f2
57
58 WHILE @@FETCH_STATUS=0
59 BEGIN
60 IF @f2<>@Temp_F2
61 SELECT @f3=1, @Temp_F2=@f2 --如果Field2 发生变化, 则 @F3=1
62
63 UPDATE myTable1 SET field3=@f3 WHERE field1=@f1 AND field2=@f2
64 SELECT @f3=@f3 + 1
65
66 FETCH CURSOR1 INTO @f1, @f2
67 END
68
69 CLOSE CURSOR1
70 DEALLOCATE CURSOR1
posted on 2007-02-06 11:57
Victor.Stone 阅读(770)
评论(17) 编辑 收藏 引用