在存储过程中 报 名为 'mycursor ' 的游标已存在

[alabucunzai]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[jzl_getFrequentChangeStatus]
@area int,
@mjno varchar(20)
as
declare @cnt int,@index int, @sta_id varchar(50),@sta_id1 varchar(50),@pos_id varchar(50),@pos_id1 varchar(10),@begin_time datetime
declare @taskno varchar(20)

IF EXISTS (SELECT * from [tempdb].dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].#mytmp') )
DROP Table #mytmp
CREATE TABLE #mytmp(

[Sta_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Begin_time] [datetime] NULL ,
[Pos_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Bcch_0] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[taskNo] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[sta_addr] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[pos_addr] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,


)
if @area=-1 and @mjno='all'
begin
--print '1'
declare mycursor scroll cursor for
select sta_id,pos_id,begin_time,taskno from test_bcchmsg order by rtrim(sta_id)+rtrim(pos_id),begin_time desc
select @cnt = count(*) from(select * from test_bcchmsg) a
end
else if @area>-1 and @mjno='all'
begin
declare mycursor scroll cursor for
select a.sta_id,a.pos_id,a.begin_time,a.taskno from test_bcchmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where b.area = @area
order by rtrim(a.sta_id)+rtrim(a.pos_id),a.begin_time desc
select @cnt = count(*) from
(select a.* from bcch_testmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where b.area = @area) a
end
else if @mjno<>'all'
begin
declare mycursor scroll cursor for
select a.sta_id,a.pos_id,a.begin_time, a.taskno from test_bcchmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where a.sta_id=@mjno
order by rtrim(a.sta_id)+rtrim(a.pos_id),a.begin_time desc
select @cnt = count(*) from
(select a.* from bcch_testmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where a.sta_id=@mjno) a
end

open mycursor
if ( @@CURSOR_ROWS > 0 )
begin
set @index = 1
set @sta_id1 = ''
set @pos_id1 = ''
FETCH first FROM mycursor into @sta_id,@pos_id,@begin_time,@taskno

WHILE @@FETCH_STATUS = 0
BEGIN
--if @sta_id1<>@sta_id or @pos_id1 <> @pos_id
begin
set @sta_id1 = @sta_id
set @pos_id1 = @pos_id

insert into #mytmp
select a.sta_id,a.begin_time,a.b_count,a.fretime,a.pos_id,a.bcch,
a.taskno,b.main_addr,f.addr

from test_bcchmsg a inner join share_setting b on(a.sta_id=b.sta_id)
inner join child_setting f on( a.sta_id=f.sta_id and a.pos_id=f.pos_id )

where a.sta_id=@sta_id and a.pos_id=@pos_id and begin_time=@begin_time and taskno=@taskno




set @index = @index+1
if @index >@cnt break
end
FETCH next from mycursor into @sta_id,@pos_id,@begin_time,@taskno

end
end
close mycursor
deallocate mycursor
select * from #mytmp
DROP Table #mytmp

[zhou968]

已存在那是你上次用完后没删除啊

[hery2002]

declare mycursor scroll cursor for
select sta_id,pos_id,begin_time,taskno from test_bcchmsg order by rtrim(sta_id)+rtrim(pos_id),begin_time desc
select @cnt = count(*) from(select * from test_bcchmsg) a
end
else if @area>-1 and @mjno='all'
begin
declare mycursor scroll cursor for
定义了两次!
区分一下

[toxxj]

放到查询分析器中去检查下,看是哪一行出问题了

[hery2002]

--print '1'
declare mycursor scroll cursor for
select sta_id,pos_id,begin_time,taskno from test_bcchmsg order by rtrim(sta_id)+rtrim(pos_id),begin_time desc
select @cnt = count(*) from(select * from test_bcchmsg) a
end
else if @area>-1 and @mjno='all'
begin
declare mycursor scroll cursor for
select a.sta_id,a.pos_id,a.begin_time,a.taskno from test_bcchmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where b.area = @area
order by rtrim(a.sta_id)+rtrim(a.pos_id),a.begin_time desc
select @cnt = count(*) from
(select a.* from bcch_testmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where b.area = @area) a
end
else if @mjno <>'all'
begin
declare mycursor scroll cursor for
select a.sta_id,a.pos_id,a.begin_time, a.taskno from test_bcchmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where a.sta_id=@mjno
order by rtrim(a.sta_id)+rtrim(a.pos_id),a.begin_time desc
select @cnt = count(*) from
(select a.* from bcch_testmsg a inner join share_setting b on(a.sta_id=b.sta_id)
where a.sta_id=@mjno) a
end
-----------------------
这三处的定义的游标变量不要定义成一个了,
区分开来.

[Limpire]

--> 改为本地游标，不然存储过程并发就会出现这个问题：

declare @mycursor cursor

set  @mycursor = cursor scroll for
select sta_id,pos_id,begin_time,taskno from test_bcchmsg order by rtrim(sta_id)+rtrim(pos_id),begin_time desc

--> 其他自己改改。

[Limpire]

他这个多处定义是有条件判断的，是允许的，不是这个问题，是全局游标存储过程并发的问题。

[ojuju10]

用动态的游标

[alabucunzai]

其他的存储过程也用这个游标,但没有错误
这个就没有错误


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[jzl_getTroubleChild]
@area int
as
declare @cnt int,@index int, @sta_id varchar(20),@pos_id varchar(10),@alarm_st varchar(10),@sta_id1 varchar(20),@pos_id1 varchar(10),@alarm_st1 varchar(10) ,@alarm_dt varchar(20)


IF EXISTS (SELECT * from [tempdb].dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].#mytmp') )
DROP Table #mytmp
CREATE TABLE #mytmp(
[Sta_id] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Pos_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[alarm_st] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[alert] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[lvl] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[color] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[alarm_dt] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[alarm_resently_dt] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addr] [varchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[area] [tinyint] NULL ,
[onview] [tinyint] NULL ,
[alert_num] [int] NULL

)
if @area=-1
begin
declare mycursor scroll cursor for
SELECT STA_ID,POS_ID,alarm_st,alarm_dt FROM TroubleChild WHERE ONVIEW=1
ORDER BY STA_ID, POS_ID ,ALERT, ALARM_DT ASC
select @cnt = count(*) from(select distinct sta_id,pos_id,alert from TroubleChild) a
end
else
begin
declare mycursor scroll cursor for
SELECT STA_ID,POS_ID,alarm_st,alarm_dt FROM TroubleChild WHERE ONVIEW=1 and area=@area
ORDER BY STA_ID, POS_ID ,ALERT, ALARM_DT ASC
select @cnt = count(*) from(select distinct sta_id,pos_id,alert from TroubleChild where area =@area ) a
end

--print 'cnt='+cast( @cnt as varchar(10) )

open mycursor
if ( @@CURSOR_ROWS > 0 )
begin
declare @num int
set @num = 0
set @index = 1
set @sta_id1 = ''
set @pos_id1 = ''
set @alarm_st1 = ''
FETCH first FROM mycursor into @sta_id,@pos_id,@alarm_st,@alarm_dt

WHILE @@FETCH_STATUS = 0
BEGIN
if @sta_id1<>@sta_id or @pos_id1 <> @pos_id or @alarm_st<>@alarm_st1
begin

--print @sta_id1+','+@pos_id1+','+@alarm_st1
update #mytmp set alert_num = @num
where sta_id=@sta_id1 and pos_id=@pos_id1 and alarm_st=@alarm_st1
set @sta_id1 = @sta_id
set @pos_id1 = @pos_id
set @alarm_st1 = @alarm_st

insert into #mytmp
select a.*,1
from TroubleChild a
where a.sta_id=@sta_id and a.pos_id=@pos_id and a.alarm_st=@alarm_st and a.alarm_dt=@alarm_dt
set @num = 0
--print @sta_id+','+@pos_id+','+@alarm_st+','+@alarm_dt

--set @index = @index+1
--if @index >@cnt break
end
set @num = @num + 1
FETCH next from mycursor into @sta_id,@pos_id,@alarm_st,@alarm_dt
end
--print @sta_id1+','+@pos_id1+','+@alarm_st1
update #mytmp set alert_num = @num
where sta_id=@sta_id1 and pos_id=@pos_id1 and alarm_st=@alarm_st1
end
close mycursor
deallocate mycursor

--select * from #mytmp order by alarm_dt,alert_num desc

select distinct z.* from
(select b.itemid,a.* from #mytmp a inner join workitems b on(b.gid=a.sta_id and b.csid=a.pos_id and a.alert=b.alert)-- and a.alarm_dt=convert(varchar(20),b.alerttime ,20) )
where b.status<4
union all
select ''as itemid, x.* from
#mytmp x
where not exists(
select * from
(select b.itemid,a.* from #mytmp a inner join workitems b on(b.gid=a.sta_id and b.csid=a.pos_id and a.alert=b.alert) -- and a.alarm_dt=convert(varchar(20),b.alerttime ,20) )
where b.status<4)y
where x.sta_id=y.sta_id and x.pos_id=y.pos_id and x.alarm_st=y.alarm_st) ) z
order by z.alert--alarm_dt,alert_num desc

DROP Table #mytmp

[hery2002]

[Quote=引用 6 楼 Limpire 的回复:]
他这个多处定义是有条件判断的，是允许的，不是这个问题，是全局游标存储过程并发的问题。
[/Quote]
o ,
还没有仔细看清楚,:)

[Limpire]

[Quote=引用 8 楼 alabucunzai 的回复:]

其他的存储过程也用这个游标,但没有错误
这个就没有错误

[/Quote]

对啊，你别的存储过程也是这个全局的游标名，同时并行，后运行的就会报错。

[alabucunzai]

有10多个存储过程,就这个错误,其他的都好的

这是调用这个存储过程的代码

String sta_id = info.getSta_id();
int quju = info.getQuju();
String pos_id = (info.getPos_id()==null)?"---":info.getPos_id();
DataSource datasource = null;
Connection con = null;
Statement stmt = null;
ResultSet result = null;
String sql = "{call jzl_getFrequentChangeStatus(?,?)}";
ArrayList frelist = new ArrayList();
CallableStatement sqlStmt = null;
try
{
datasource = DataSourceUtils.setupDataSource();
con = datasource.getConnection();
sqlStmt = con.prepareCall(sql);
sqlStmt.setString(1, new Integer(quju).toString());
sqlStmt.setString(2, sta_id);
// sqlStmt.setString(3, pos_id);
result = sqlStmt.executeQuery();
while(result.next())
{
String share_id = result.getString("sta_id");
String child_id = result.getString("pos_id");
String begin_time = result.getString("begin_time");
String address = result.getString("address");
String taskno = result.getString("taskno");
String memo = "<input type=\"button\" name=\"detail\" value=\"详细信息\" onclick =\"onDetail('"+taskno+"','"+share_id+"','"+child_id+"','"+begin_time+"')\">";
TestBcchView tbv = new TestBcchView(taskno,share_id,child_id,begin_time);
tbv.setAddress(address);
tbv.setMemo(memo);
frelist.add(tbv);
}

}catch(SQLException e)
{
log.error("sql error in the getFreList()",e);
throw new SQLException ("sql error in the getFreList()"+e);
}
finally
{
sqlStmt.close();
con.close();
}
return frelist;