1、建立二个表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hgctest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hgctest]
GO
CREATE TABLE [dbo].[hgc] (
[i_id] [int] IDENTITY (1, 1) NOT NULL ,
[iperiod] [tinyint] NOT NULL ,
[ccode] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mc] [money] NOT NULL ,
) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hgctest1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hgctest1]
GO
CREATE TABLE [dbo].[hgc] (
[i_id] [int] IDENTITY (1, 1) NOT NULL ,
[iperiod] [tinyint] NOT NULL ,
[ccode] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mc] [money] NOT NULL ,
) ON [PRIMARY]
GO
2、在hgctest表中插入2条记录
insert into hgctest(iperiod,ccode,mc) values(1,'1001',2000)
insert into hgctest(iperiod,ccode,mc) values(1,'1002',1230)3、使用游标使得hgctest1中自动产生24条记录,每条记录的iperiod会自加1,加到12为止,应得到如下记录
i-id,iperiod,ccode,mc
1 1 1001 2000.0000
2 2 1001 2000.0000
3 3 1001 2000.0000
4 4 1001 2000.0000
5 5 1001 2000.0000
6 6 1001 2000.0000
7 7 1001 2000.0000
8 8 1001 2000.0000
9 9 1001 2000.0000
10 10 1001 2000.0000
11 11 1001 2000.0000
12 12 1001 2000.0000
13 1 1002 1230.0000
14 2 1002 1230.0000
15 3 1002 1230.0000
16 4 1002 1230.0000
17 5 1002 1230.0000
18 6 1002 1230.0000
19 7 1002 1230.0000
20 8 1002 1230.0000
21 9 1002 1230.0000
22 10 1002 1230.0000
23 11 1002 1230.0000
24 12 1002 1230.00004、如何实现上面记录呢?我写了如下
BEGIN TRANSACTION
rollback tran
declare @iperiod varchar(20)
declare @ccode varchar(12)
declare @mc money
set @iperiod =0
declare cursor2 cursor
for
select iperiod,ccode,mc from hgctest where iperiod = 1
open cursor2
fetch next from cursor2 into @iperiod,@ccode,@mc --取得数据,存入变量中
while (@@FETCH_STATUS<>-1) --循环执行下面的语句
begin
while (@iperiod < 13)
begin
insert into hgc(iperiod,ccode,mc) values(@iperiod,@ccode,@mc)
set @iperiod = @iperiod +1
fetch next from cursor2 into @iperiod,@ccode,@mc
end --close cursor2
deallocate cursor2
end
commit tran我的提问:
1、得出的结果不一致,错在哪里了。
2、如果使用游标来更新每一行,如例子
i-id,iperiod,ccode,mc
1 1 1001 2000.0000
2 2 1001 3000.0000
3 3 1001 5000.0000
4 4 1001 6000.0000
5 5 1001 7000.0000
6 6 1001 3000.0000
7 7 1001 2000.0000
8 8 1001 2000.0000
9 9 1001 2000.0000
10 10 1001 2000.0000
11 11 1001 2000.0000
12 12 1001 2000.0000
13 1 1002 1230.0000
14 2 1002 430.0000
15 3 1002 4230.0000
16 4 1002 1230.0000
17 5 1002 1230.0000
18 6 1002 1230.0000
19 7 1002 1230.0000
20 8 1002 1230.0000
21 9 1002 1230.0000
22 10 1002 1230.0000
23 11 1002 1230.0000
24 12 1002 1230.0000
比较乱,如何用游标批量更新使得iperiod=1,ccode不同对应的值全部更新iperiod=1的对应记录,得到3中的记录行。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hgctest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hgctest]
GO
CREATE TABLE [dbo].[hgc] (
[i_id] [int] IDENTITY (1, 1) NOT NULL ,
[iperiod] [tinyint] NOT NULL ,
[ccode] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mc] [money] NOT NULL ,
) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hgctest1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hgctest1]
GO
CREATE TABLE [dbo].[hgc] (
[i_id] [int] IDENTITY (1, 1) NOT NULL ,
[iperiod] [tinyint] NOT NULL ,
[ccode] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mc] [money] NOT NULL ,
) ON [PRIMARY]
GO
2、在hgctest表中插入2条记录
insert into hgctest(iperiod,ccode,mc) values(1,'1001',2000)
insert into hgctest(iperiod,ccode,mc) values(1,'1002',1230)3、使用游标使得hgctest1中自动产生24条记录,每条记录的iperiod会自加1,加到12为止,应得到如下记录
i-id,iperiod,ccode,mc
1 1 1001 2000.0000
2 2 1001 2000.0000
3 3 1001 2000.0000
4 4 1001 2000.0000
5 5 1001 2000.0000
6 6 1001 2000.0000
7 7 1001 2000.0000
8 8 1001 2000.0000
9 9 1001 2000.0000
10 10 1001 2000.0000
11 11 1001 2000.0000
12 12 1001 2000.0000
13 1 1002 1230.0000
14 2 1002 1230.0000
15 3 1002 1230.0000
16 4 1002 1230.0000
17 5 1002 1230.0000
18 6 1002 1230.0000
19 7 1002 1230.0000
20 8 1002 1230.0000
21 9 1002 1230.0000
22 10 1002 1230.0000
23 11 1002 1230.0000
24 12 1002 1230.00004、如何实现上面记录呢?我写了如下
BEGIN TRANSACTION
rollback tran
declare @iperiod varchar(20)
declare @ccode varchar(12)
declare @mc money
set @iperiod =0
declare cursor2 cursor
for
select iperiod,ccode,mc from hgctest where iperiod = 1
open cursor2
fetch next from cursor2 into @iperiod,@ccode,@mc --取得数据,存入变量中
while (@@FETCH_STATUS<>-1) --循环执行下面的语句
begin
while (@iperiod < 13)
begin
insert into hgc(iperiod,ccode,mc) values(@iperiod,@ccode,@mc)
set @iperiod = @iperiod +1
fetch next from cursor2 into @iperiod,@ccode,@mc
end --close cursor2
deallocate cursor2
end
commit tran我的提问:
1、得出的结果不一致,错在哪里了。
2、如果使用游标来更新每一行,如例子
i-id,iperiod,ccode,mc
1 1 1001 2000.0000
2 2 1001 3000.0000
3 3 1001 5000.0000
4 4 1001 6000.0000
5 5 1001 7000.0000
6 6 1001 3000.0000
7 7 1001 2000.0000
8 8 1001 2000.0000
9 9 1001 2000.0000
10 10 1001 2000.0000
11 11 1001 2000.0000
12 12 1001 2000.0000
13 1 1002 1230.0000
14 2 1002 430.0000
15 3 1002 4230.0000
16 4 1002 1230.0000
17 5 1002 1230.0000
18 6 1002 1230.0000
19 7 1002 1230.0000
20 8 1002 1230.0000
21 9 1002 1230.0000
22 10 1002 1230.0000
23 11 1002 1230.0000
24 12 1002 1230.0000
比较乱,如何用游标批量更新使得iperiod=1,ccode不同对应的值全部更新iperiod=1的对应记录,得到3中的记录行。
--1、游标修改如下:
declare @iperiod varchar(20)
declare @ccode varchar(12)
declare @mc money
set @iperiod =1
declare cursor2 cursor for select iperiod,ccode,mc from hgctest where iperiod = 1
open cursor2
fetch next from cursor2 into @iperiod,@ccode,@mc --取得数据,存入变量中
while (@@FETCH_STATUS <>-1) --循环执行下面的语句
begin
while (@iperiod < 13)
begin
insert into hgctest1(iperiod,ccode,mc) values(@iperiod,@ccode,@mc)
set @iperiod = @iperiod +1
end
fetch next from cursor2 into @iperiod,@ccode,@mc
end
close cursor2
deallocate cursor2