@receiver这样在表格中 ,在下面的存储过程中用游标循环,其中在if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
这里判断的时候,当循环判断到第一个@receiver 在MSG_Sentbox 中已经exists的时候就直接跳出游标的循环了,怎么样才能让它跳过然后继续循环下一个呢?
if exists(select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2)
--else
begin
declare L cursor -- 创建游标 L
static
for select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2
open L --打开游标
fetch next from L into @LineNum--提取上次提取行的下一行
while(@@fetch_status=0)
begin
SET @sentContent='线路' + @LineNum + ':' + '多条塔杆同时收到断电短信,可能为线路停电或电线被盗,请注意确认!'
declare s cursor -- 创建游标 S
static
for select a from dbo.fn_split(@receiver,',')
open s --打开游标
fetch next from s into @str --提取上次提取行的下一行
while(@@fetch_status=0) --循环手机号码,并各自插入到MSG_Outbox中
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
-- return -2
-- else
begin
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) VALUES(@str,0,@sentContent,'',getdate())
fetch next from s into @str
end
end
close s --关闭游标
deallocate s --删除游标,释放资源
fetch next from L into @LineNum
end
close L --关闭游标
deallocate L --删除游标,释放资源
end
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
这里判断的时候,当循环判断到第一个@receiver 在MSG_Sentbox 中已经exists的时候就直接跳出游标的循环了,怎么样才能让它跳过然后继续循环下一个呢?
if exists(select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2)
--else
begin
declare L cursor -- 创建游标 L
static
for select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2
open L --打开游标
fetch next from L into @LineNum--提取上次提取行的下一行
while(@@fetch_status=0)
begin
SET @sentContent='线路' + @LineNum + ':' + '多条塔杆同时收到断电短信,可能为线路停电或电线被盗,请注意确认!'
declare s cursor -- 创建游标 S
static
for select a from dbo.fn_split(@receiver,',')
open s --打开游标
fetch next from s into @str --提取上次提取行的下一行
while(@@fetch_status=0) --循环手机号码,并各自插入到MSG_Outbox中
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
-- return -2
-- else
begin
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) VALUES(@str,0,@sentContent,'',getdate())
fetch next from s into @str
end
end
close s --关闭游标
deallocate s --删除游标,释放资源
fetch next from L into @LineNum
end
close L --关闭游标
deallocate L --删除游标,释放资源
end
把当前值写到一个临时表里面整个循环完成后,再扫描临时表,确定return
但是还是没有循环下一次呀
while(@@fetch_status=0) --循环手机号码,并各自插入到MSG_Outbox中
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
-- return -2
-- else
begin
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) VALUES(@str,0,@sentContent,'',getdate())
fetch next from s into @str
end
end这段错了,fetch next应该在begin end外面
在上面那个基础上加多这个判断
这段代码应该写在哪里 或者是应该怎么简化插入到上面的存储过程中
if exists(select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2)
在满足这个条件下
加多下面这段if exists (select * from UV_MsgInbox t3 where t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate()) and t3.sender in (select 号码 from Result group by 号码))
begin
declare @rece varchar(500)
declare @MsgTitle varchar(200)
declare @mmsLoc varchar(500)
declare M cursor --创建游标 M
static
for select MsgTitle,MMSContentLocation from UV_MsgInbox t3 where t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate()) and t3.sender in (select 号码 from Result group by 号码)
open M --打开游标
fetch next from M into @MsgTitle,@mmsLoc
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
while(@@fetch_status=0)
begin
declare sm cursor
static
for select a from dbo.fn_split(@receiver,',')
open sm
fetch next from sm into @rece
while(@@fetch_status=0)
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent and MMSContentLocation=@mmsLoc
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@rece,1,@MsgTitle,@mmsLoc,getDate())
fetch next from sm into @rece
end
close sm
deallocate sm
fetch next from M into @MsgTitle,@mmsLoc
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
end
close M
deallocate M
end
从SQL来看应该没问题,还是不能继续fetch?
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))这个判断的时候 我不知道存储过程里面到底怎么执行的
每次总是给我的感觉就是,判断完第一个if exists(select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2)里面那个双重游标后。后面那个if exists (select * from UV_MsgInbox t3 where t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate()) and t3.sender in (select 号码 from Result group by 号码))都没有执行了,也有可能就是直接判断第一个不满足就直接跳出游标的循环了
但是我真的不知道该怎么写才好
所以才想问下你 能不能帮我简化一下这段代码
那你能不能加一下QQ QQ上问你其他的问题?
declare @receiver varchar(500)
set @receiver ='13737740287,15878359061,15812405921'
select a from dbo.fn_split(@receiver,',')
if exists(select * from sysobjects where name='Result' )
drop table Resultdeclare @str varchar(500)--声明变量
declare @LineNum varchar(500)
declare @sentContent varchar(500)--声明变量select LineNum as 线路,SIM as 号码 into Result from UV_MsgInbox t1 where t1.MsgArrivedTime >= dateadd(minute,-5,getdate()) and MsgTitle='外接电源断电,启动内置电源' and not exists(select * from UV_MsgInbox t2 where t2.Sender = t1.SIM
and t2.MsgArrivedTime >= dateadd(minute,-5,getdate())
and t2.MsgTitle ='恢复正常供电')
group by t1.SIM,t1.LineNum if exists(select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2)
--else
begin
declare L cursor -- 创建游标 L
static
for select 线路 from Result group by 线路 having count(DISTINCT 号码)>=2
open L --打开游标
fetch next from L into @LineNum--提取上次提取行的下一行
while(@@fetch_status=0)
begin
SET @sentContent='线路' + @LineNum + ':' + '多条塔杆同时收到断电短信,可能为线路停电或电线被盗,请注意确认!'
declare s cursor -- 创建游标 S
static
for select a from dbo.fn_split(@receiver,',')
open s --打开游标
fetch next from s into @str --提取上次提取行的下一行
while(@@fetch_status=0) --循环手机号码,并各自插入到MSG_Outbox中
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
-- return -2
-- else
begin
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) VALUES(@str,0,@sentContent,'',getdate())
end
fetch next from s into @str
end
close s --关闭游标
deallocate s --删除游标,释放资源
fetch next from L into @LineNum
end
close L --关闭游标
deallocate L --删除游标,释放资源 if exists (select * from UV_MsgInbox t3 where t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate()) and t3.sender in (select 号码 from Result group by 号码))
begin
declare @rece varchar(500)
declare @MsgTitle varchar(200)
declare @mmsLoc varchar(500)
declare M cursor --创建游标 M
static
for select MsgTitle,MMSContentLocation from UV_MsgInbox t3 where t3.MsgType=1 and t3.MsgArrivedTime >= dateadd(minute,-5,getdate()) and t3.sender in (select 号码 from Result group by 号码)
open M --打开游标
fetch next from M into @MsgTitle,@mmsLoc
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
while(@@fetch_status=0)
begin
declare sm cursor
static
for select a from dbo.fn_split(@receiver,',')
open sm
fetch next from sm into @rece
while(@@fetch_status=0)
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent and MMSContentLocation=@mmsLoc
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
insert into dbo.MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) values(@rece,1,@MsgTitle,@mmsLoc,getDate())
fetch next from sm into @rece
end
close sm
deallocate sm
fetch next from M into @MsgTitle,@mmsLoc
set @mmsLoc=substring(@mmsLoc,0,charindex(',',@mmsLoc))
end
close M
deallocate M
end
end我现在是这样写的 但是没有对
前面那个游标双重循环都可以的 但是到后面那个双重游标循环那里就不怎么对了,有符合条件的数据的时候都没能成功插入到表里 。
我的QQ 490562477
begin
if not exists(select * from MSG_Sentbox where msgTitle=@sentContent
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))
-- return -2
-- else
begin
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) VALUES(@str,0,@sentContent,'',getdate())
fetch next from s into @str
end
end
看楼主上面这段SQL的意思是不是如果括号中的不存在就跳过,执行下一个游标的值,否则执行插入操作?
declare @name varchar(100)declare cursor_a cursor
for
select name
from sys.objects
open cursor_afetch next from cursor_a into @name
while @@fetch_status = 0
begin
if @name = 'spt_values'
begin
goto pass
end
select @name
pass:
fetch next from cursor_a into @name
end
close cursor_a
deallocate cursor_a你看看用这样的方法能不能实现?
INSERT INTO MSG_Outbox (Receiver,MsgType,MsgTitle,MMSContentLocation,SendTime) select @str,0,@sentContent,'',getdate() from MSG_Outbox
where
not exists(select * from MSG_Sentbox where msgTitle=@sentContent and MMSContentLocation=@mmsLoc
and Receiver= @str and ActualSendTime >=dateadd(minute,-10,getdate()))