这样的存储过程怎么写？

[kevin_gao]

在asp中用如下语句实现的功能，在存储过程中怎么写？

Set objRs1=objDbcn.Execute("select * from myTable")

while not objRs1.eof
sStartTime1=objRs1("StartTime")
sEndTime1=objRs1("EndTime")
sRoomId1=objRs1("roomid")

if sStarTime1>sEndTime1 then
'asdfasdf
end if

objRs1.moveNext
wend


其实就是想问，怎么在存储过程中写一个类似while的循环，并且要能控制游标一步步往下走。

[kevin_gao]

顶

[kevin_gao]

ffff

[xjdawu]

for aaa in (select * from tabName) loop

... ...(循环体，引用tabName中的字段:aaa.colName)

end loop

[xjdawu]

循环体中每条语句后面要带分号

跳出用

exit;

[kevin_gao]

是sqlServer中的语法吗？怎么
ncorrect syntax near the keyword 'for'.

[pegtop]

定义cursor
将值赋给变量就可以了。
declare cusor cur
as select * from table;
open cur
while (!@@NOTFOUND)
赋值

close;

[kevin_gao]

嗯，用游标再加fetch好像可以实现，研究中！

[kevin_gao]

哈哈，终于成功解决时间合并这个问题，全部代码如下。呵呵：

drop table #tmp_result

set nocount on

declare @startTime datetime;
declare @endTime datetime;
declare @roomid int;
declare @startTime1 datetime;
declare @endTime1 datetime;
declare @roomid1 int;

select top 1 @startTime=starttime,@endTime=endtime,@roomid=roomid from myTable order by roomid,starttime;


select * into #tmp_result from myTable where 1<>1;

DECLARE my_cursor CURSOR FOR
SELECT starttime, endtime,roomid FROM myTable
ORDER BY roomid,starttime

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @starttime, @endtime,@roomid

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM my_cursor
INTO @startTime1, @endtime1,@roomid1

if(@startTime1<=@EndTime)and(@roomId1=@roomId)
begin
if(@EndTime1>@EndTime)
Set @EndTime=@EndTime1
end

else
begin
insert into #tmp_result(starttime,endtime,roomid) values(@starttime,@endtime,@roomid);
Set @StartTime=@StartTime1
Set @EndTime=@EndTime1
Set @RoomId=@RoomId1
end



END

insert into #tmp_result(starttime,endtime,roomid) values(@starttime,@endtime,@roomid);

CLOSE my_cursor
DEALLOCATE my_cursor

select * from #tmp_result;

GO

[ww_xiaohua]

create or replace procedure pro_sql
as
StartTime varchar2(20);
EndTime varchar2(20);
roomid varchar2(20);
cursor c1 is select StartTime,EndTime,roomid from mytable;
begin
open c1;
loop fetch c1 into StartTime,EndTime,roomid
exit when c1%notfound;
if(StartTime>EndTime) then
'asdfasdf
end if;
end loop;
close c1;
end ;

[kevin_gao]

我好像自己解决了

set nocount on

declare @startTime datetime;
declare @endTime datetime;
declare @roomid int;
declare @startTime1 datetime;
declare @endTime1 datetime;
declare @roomid1 int;

select top 1 @startTime=starttime,@endTime=endtime,@roomid=roomid from myTable order by roomid,starttime;


select * into #tmp_result from myTable where 1<>1;

DECLARE my_cursor CURSOR FOR
SELECT starttime, endtime,roomid FROM myTable
ORDER BY roomid,starttime

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @starttime, @endtime,@roomid

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM my_cursor
INTO @startTime1, @endtime1,@roomid1

if(@startTime1<=@EndTime)and(@roomId1=@roomId)
begin
if(@EndTime1>@EndTime)
Set @EndTime=@EndTime1
end

else
begin
insert into #tmp_result(starttime,endtime,roomid) values(@starttime,@endtime,@roomid);
Set @StartTime=@StartTime1
Set @EndTime=@EndTime1
Set @RoomId=@RoomId1
end



END

insert into #tmp_result(starttime,endtime,roomid) values(@starttime,@endtime,@roomid);

CLOSE my_cursor
DEALLOCATE my_cursor

select * from #tmp_result;

GO

[ww_xiaohua]

oracle中的语法。