执行语句为: declare @bjrs int, @bjzs int ,@i int ,@j int--bjrs班级人数/bjzs班级总数 select @bjzs=17 select @bjrs=cast((select max(ID) from px1 )/@bjzs as int)+1 select @i=1,@j=2 while (@i<=@bjzs) begin begin update px1 set fpbh=@i where [id]=@i
while (@j<=@bjrs)
BEGIN
if @j % 2=0
update px1 set fpbh=@i where [id]=@j*@bjzs-(@i-1) --print @j*@bjzs-(@i-1)
else
update px1 set fpbh=@i where [id]=(@j-1)*@bjzs+@i
select @j=@j+1 print '@j'+cast( @j as char) END end select @i=@i+1 print '@i'+cast(@i as char) end
以下的PB程序动态SQL实现了,但我想用存储过程 //用PB程序来更新分班的班号 //注意减号两端的空格 sqlca.autocommit=true integer bjrs,bjzs=17,maxrecno,mbrs,li_waitstate integer i=1,j=2 select max(id) into :maxrecno from px1; bjrs=int(maxrecno/bjzs)+1 for i=1 to bjzs //来至药库系统的进度条(gf_waiting_yk来至于yk_main.pbl,先在应用程序pbl中添加support.pbl) gf_waiting_yk("正在进行分班...",i,bjzs,10,li_waitstate) //update语句是动态执行的 string str_update3 str_update3="update px1 set fpbh="+string(i)+" where no="+string(i) execute immediate :str_update3; for j=2 to bjrs if mod(j,2)=0 then string str_update1 int intno2 intno2=j*bjzs - (i - 1) str_update1="update px1 set fpbh="+string(i)+" where no="+string(intno2) execute immediate :str_update1;
else string str_update2 int intno1 intno1=(j - 1)*bjzs+i str_update2="update px1 set fpbh="+string(i)+" where no="+string(intno1) execute immediate :str_update2; end if next next commit; sqlca.autocommit=false
此为px1表的导出sql,px1仅为一测试表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[px1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[px1]
GOCREATE TABLE [dbo].[px1] (
[SNO] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[SNAME] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[SYW] [float] NULL ,
[SSX] [float] NULL ,
[SYY] [float] NULL ,
[SWL] [float] NULL ,
[SHX] [float] NULL ,
[SZZ] [float] NULL ,
[SLS] [float] NULL ,
[SDL] [float] NULL ,
[SSW] [float] NULL ,
[SLJ] [float] NULL ,
[STY] [float] NULL ,
[SCZ] [float] NULL ,
[SZF] [float] NULL ,
[SMC] [float] NULL ,
[SBH] [float] NULL ,
[KSH] [float] NULL ,
[KHAO] [float] NULL ,
[KC] [float] NULL ,
[njpx] [float] NULL ,
[no] [int] NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL ,
[fpbh] [int] NULL
) ON [PRIMARY]
GO
declare @bjrs int, @bjzs int ,@i int ,@j int--bjrs班级人数/bjzs班级总数
select @bjzs=17
select @bjrs=cast((select max(ID) from px1 )/@bjzs as int)+1
select @i=1,@j=2
while (@i<=@bjzs)
begin
begin
update px1 set fpbh=@i where [id]=@i
while (@j<=@bjrs)
BEGIN
if @j % 2=0
update px1 set fpbh=@i where [id]=@j*@bjzs-(@i-1)
--print @j*@bjzs-(@i-1)
else
update px1 set fpbh=@i where [id]=(@j-1)*@bjzs+@i
select @j=@j+1
print '@j'+cast( @j as char)
END
end
select @i=@i+1
print '@i'+cast(@i as char)
end
要的结果为:
在fpbh例为
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,1,2,3,....
//用PB程序来更新分班的班号
//注意减号两端的空格
sqlca.autocommit=true
integer bjrs,bjzs=17,maxrecno,mbrs,li_waitstate
integer i=1,j=2
select max(id) into :maxrecno from px1;
bjrs=int(maxrecno/bjzs)+1
for i=1 to bjzs
//来至药库系统的进度条(gf_waiting_yk来至于yk_main.pbl,先在应用程序pbl中添加support.pbl)
gf_waiting_yk("正在进行分班...",i,bjzs,10,li_waitstate)
//update语句是动态执行的
string str_update3
str_update3="update px1 set fpbh="+string(i)+" where no="+string(i)
execute immediate :str_update3; for j=2 to bjrs
if mod(j,2)=0 then
string str_update1
int intno2
intno2=j*bjzs - (i - 1)
str_update1="update px1 set fpbh="+string(i)+" where no="+string(intno2)
execute immediate :str_update1;
else
string str_update2
int intno1
intno1=(j - 1)*bjzs+i
str_update2="update px1 set fpbh="+string(i)+" where no="+string(intno1)
execute immediate :str_update2; end if
next
next
commit;
sqlca.autocommit=false