R
reallycamera
Unregistered / Unconfirmed
GUEST, unregistred user!
ALTER PROCEDURE 省际边界分区水资源质量状况评价表
@年 INT=2003,
@月 INT=2
AS
CREATE TABLE #TAB3 (水质类别 VARCHAR(16), 苏沪边界断面数 INT,苏沪断面百分比 decimal(10,3),苏浙边界断面数 INT,苏浙断面百分比 decimal(4,3))
--苏沪边界的
DECLARE
@STCD AS VARCHAR(20),--断面编码
@STCDCOUNT AS decimal(10,1)--断面个数
SELECT @STCDCOUNT = COUNT(distinct(STCD)) FROM GIS_WQ_STATION_B
where schemeno='省际边界水质评价'and caltype='常规水质评价'and regionnm='苏沪边界'
insert into #tab3 (水质类别) values('I')
insert into #tab3 (水质类别) values('II')
insert into #tab3 (水质类别) values('III')
insert into #tab3 (水质类别) values('IV')
insert into #tab3 (水质类别) values('V')
insert into #tab3 (水质类别) values('劣于V')
EXECUTE ('DECLARE STCD_CURSOR CURSOR FOR SELECT STCD FROM GIS_WQ_STATION_B
where schemeno=''省际边界水质评价''and caltype=''常规水质评价''and regionnm=''苏沪边界''')
declare @ONECOUNT AS decimal(10,1)--一类水断面个数
declare @TWOCOUNT AS decimal(10,1)
declare @THREECOUNT AS decimal(10,1)
declare @FOURCOUNT AS decimal(10,1)
declare @FIVECOUNT AS decimal(10,1)
declare @SIXCOUNT AS decimal(10,1)
declare @TMP AS INT
SET @ONECOUNT = 0
SET @TWOCOUNT = 0
SET @THREECOUNT = 0
SET @FOURCOUNT = 0
SET @FIVECOUNT = 0
SET @SIXCOUNT = 0
OPEN STCD_CURSOR
while (@@FETCH_STATUS = 0)
BEGIN
SELECT @TMP =Station实际类别 FROM GIS_WQ_NM_MC WHERE STCD=@STCD AND Year=@年 AND Month=@月
if @TMP = 1 set @ONECOUNT = @ONECOUNT+1
if @TMP = 2 set @twoCOUNT = @twoCOUNT+1
if @TMP = 3 set @threeCOUNT = @threeCOUNT+1
if @TMP = 4 set @fourCOUNT = @fourCOUNT+1
if @TMP = 5 set @fiveCOUNT = @fiveCOUNT+1
if @TMP = 6 set @sixCOUNT = @sixCOUNT+1
FETCH NEXT FROM STCD_CURSOR INTO @STCD
IF (@@fetch_status<>0) break
END
close STCD_CURSOR
Deallocate STCD_CURSOR
update #tab3 set 苏沪边界断面数 = @ONECOUNT where 水质类别 = 'I'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'I'
else
if @STCDCOUNT<>0 --by clg
update #tab3 set 苏沪断面百分比= @ONECOUNT/@STCDCOUNT where 水质类别 = 'I'
update #tab3 set 苏沪边界断面数 = @twoCOUNT where 水质类别 = 'II'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'II'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @twoCOUNT/@STCDCOUNT where 水质类别 = 'II'
update #tab3 set 苏沪边界断面数 = @threeCOUNT where 水质类别 = 'III'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'III'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @threeCOUNT/@STCDCOUNT where 水质类别 = 'III'
update #tab3 set 苏沪边界断面数 = @fourCOUNT where 水质类别 = 'IV'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'IV'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @fourCOUNT/@STCDCOUNT where 水质类别 = 'IV'
update #tab3 set 苏沪边界断面数 = @fiveCOUNT where 水质类别 = 'V'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'V'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @fiveCOUNT/@STCDCOUNT where 水质类别 = 'V'
update #tab3 set 苏沪边界断面数 = @sixCOUNT where 水质类别 = '劣于V'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = '劣于V'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @sixCOUNT/@STCDCOUNT where 水质类别 = '劣于V'
--苏浙边界的
DECLARE
@STCD2 AS VARCHAR(20),
@STCDCOUNT2 AS decimal(10,1)
SELECT @STCDCOUNT2 = COUNT(distinct(STCD)) FROM GIS_WQ_STATION_B
where schemeno='省际边界水质评价'and caltype='常规水质评价'and regionnm='苏浙边界'
EXECUTE ('DECLARE STCD_CURSOR2 CURSOR FOR SELECT STCD FROM GIS_WQ_STATION_B
where schemeno=''省际边界水质评价''and caltype=''常规水质评价''and regionnm=''苏浙边界''')
declare @ONECOUNT2 AS decimal(10,1)
declare @TWOCOUNT2 AS decimal(10,1)
declare @THREECOUNT2 AS decimal(10,1)
declare @FOURCOUNT2 AS decimal(10,1)
declare @FIVECOUNT2 AS decimal(10,1)
declare @SIXCOUNT2 AS decimal(10,1)
declare @TMP2 AS INT
SET @ONECOUNT2 = 0
SET @TWOCOUNT2 = 0
SET @THREECOUNT2 = 0
SET @FOURCOUNT2 = 0
SET @FIVECOUNT2 = 0
SET @SIXCOUNT2 = 0
OPEN STCD_CURSOR2
while @@FETCH_STATUS = 0
BEGIN
SELECT @TMP2 =Station实际类别 FROM GIS_WQ_NM_MC WHERE STCD=@STCD2 AND Year=@年 AND Month=@月
if @TMP2 = 1 set @ONECOUNT2 = @ONECOUNT2+1
if @TMP2 = 2 set @twoCOUNT2 = @twoCOUNT2+1
if @TMP2 = 3 set @threeCOUNT2 = @threeCOUNT2+1
if @TMP2 = 4 set @fourCOUNT2 = @fourCOUNT2+1
if @TMP2 = 5 set @fiveCOUNT2 = @fiveCOUNT2+1
if @TMP2 = 6 set @sixCOUNT2 = @sixCOUNT2+1
FETCH NEXT FROM STCD_CURSOR2 INTO @STCD2
IF (@@fetch_status<>0) break
END
close STCD_CURSOR2
Deallocate STCD_CURSOR2
update #tab3 set 苏浙边界断面数 = @ONECOUNT2 where 水质类别 = 'I'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'I'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @ONECOUNT2/@STCDCOUNT2 where 水质类别 = 'I'
update #tab3 set 苏浙边界断面数 = @twoCOUNT2 where 水质类别 = 'II'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'II'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @twoCOUNT2/@STCDCOUNT2 where 水质类别 = 'II'
update #tab3 set 苏浙边界断面数 = @threeCOUNT2 where 水质类别 = 'III'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'III'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @threeCOUNT2/@STCDCOUNT2 where 水质类别 = 'III'
update #tab3 set 苏浙边界断面数 = @fourCOUNT2 where 水质类别 = 'IV'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'IV'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @fourCOUNT2/@STCDCOUNT2 where 水质类别 = 'IV'
update #tab3 set 苏浙边界断面数 = @fiveCOUNT2 where 水质类别 = 'V'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'V'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @fiveCOUNT2/@STCDCOUNT2 where 水质类别 = 'V'
update #tab3 set 苏浙边界断面数 = @sixCOUNT2 where 水质类别 = '劣于V'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = '劣于V'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @sixCOUNT2/@STCDCOUNT2 where 水质类别 = '劣于V'
select * from #tab3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
运行上述的存储过程，得到的结果只有苏沪边界的数据没有苏浙边界的，是不是第二个游标不能使用？请高手指点迷津！
@年 INT=2003,
@月 INT=2
AS
CREATE TABLE #TAB3 (水质类别 VARCHAR(16), 苏沪边界断面数 INT,苏沪断面百分比 decimal(10,3),苏浙边界断面数 INT,苏浙断面百分比 decimal(4,3))
--苏沪边界的
DECLARE
@STCD AS VARCHAR(20),--断面编码
@STCDCOUNT AS decimal(10,1)--断面个数
SELECT @STCDCOUNT = COUNT(distinct(STCD)) FROM GIS_WQ_STATION_B
where schemeno='省际边界水质评价'and caltype='常规水质评价'and regionnm='苏沪边界'
insert into #tab3 (水质类别) values('I')
insert into #tab3 (水质类别) values('II')
insert into #tab3 (水质类别) values('III')
insert into #tab3 (水质类别) values('IV')
insert into #tab3 (水质类别) values('V')
insert into #tab3 (水质类别) values('劣于V')
EXECUTE ('DECLARE STCD_CURSOR CURSOR FOR SELECT STCD FROM GIS_WQ_STATION_B
where schemeno=''省际边界水质评价''and caltype=''常规水质评价''and regionnm=''苏沪边界''')
declare @ONECOUNT AS decimal(10,1)--一类水断面个数
declare @TWOCOUNT AS decimal(10,1)
declare @THREECOUNT AS decimal(10,1)
declare @FOURCOUNT AS decimal(10,1)
declare @FIVECOUNT AS decimal(10,1)
declare @SIXCOUNT AS decimal(10,1)
declare @TMP AS INT
SET @ONECOUNT = 0
SET @TWOCOUNT = 0
SET @THREECOUNT = 0
SET @FOURCOUNT = 0
SET @FIVECOUNT = 0
SET @SIXCOUNT = 0
OPEN STCD_CURSOR
while (@@FETCH_STATUS = 0)
BEGIN
SELECT @TMP =Station实际类别 FROM GIS_WQ_NM_MC WHERE STCD=@STCD AND Year=@年 AND Month=@月
if @TMP = 1 set @ONECOUNT = @ONECOUNT+1
if @TMP = 2 set @twoCOUNT = @twoCOUNT+1
if @TMP = 3 set @threeCOUNT = @threeCOUNT+1
if @TMP = 4 set @fourCOUNT = @fourCOUNT+1
if @TMP = 5 set @fiveCOUNT = @fiveCOUNT+1
if @TMP = 6 set @sixCOUNT = @sixCOUNT+1
FETCH NEXT FROM STCD_CURSOR INTO @STCD
IF (@@fetch_status<>0) break
END
close STCD_CURSOR
Deallocate STCD_CURSOR
update #tab3 set 苏沪边界断面数 = @ONECOUNT where 水质类别 = 'I'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'I'
else
if @STCDCOUNT<>0 --by clg
update #tab3 set 苏沪断面百分比= @ONECOUNT/@STCDCOUNT where 水质类别 = 'I'
update #tab3 set 苏沪边界断面数 = @twoCOUNT where 水质类别 = 'II'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'II'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @twoCOUNT/@STCDCOUNT where 水质类别 = 'II'
update #tab3 set 苏沪边界断面数 = @threeCOUNT where 水质类别 = 'III'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'III'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @threeCOUNT/@STCDCOUNT where 水质类别 = 'III'
update #tab3 set 苏沪边界断面数 = @fourCOUNT where 水质类别 = 'IV'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'IV'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @fourCOUNT/@STCDCOUNT where 水质类别 = 'IV'
update #tab3 set 苏沪边界断面数 = @fiveCOUNT where 水质类别 = 'V'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = 'V'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @fiveCOUNT/@STCDCOUNT where 水质类别 = 'V'
update #tab3 set 苏沪边界断面数 = @sixCOUNT where 水质类别 = '劣于V'
if @STCDCOUNT=0 update #tab3 set 苏沪断面百分比= 0 where 水质类别 = '劣于V'
else
if @STCDCOUNT<>0--by clg
update #tab3 set 苏沪断面百分比= @sixCOUNT/@STCDCOUNT where 水质类别 = '劣于V'
--苏浙边界的
DECLARE
@STCD2 AS VARCHAR(20),
@STCDCOUNT2 AS decimal(10,1)
SELECT @STCDCOUNT2 = COUNT(distinct(STCD)) FROM GIS_WQ_STATION_B
where schemeno='省际边界水质评价'and caltype='常规水质评价'and regionnm='苏浙边界'
EXECUTE ('DECLARE STCD_CURSOR2 CURSOR FOR SELECT STCD FROM GIS_WQ_STATION_B
where schemeno=''省际边界水质评价''and caltype=''常规水质评价''and regionnm=''苏浙边界''')
declare @ONECOUNT2 AS decimal(10,1)
declare @TWOCOUNT2 AS decimal(10,1)
declare @THREECOUNT2 AS decimal(10,1)
declare @FOURCOUNT2 AS decimal(10,1)
declare @FIVECOUNT2 AS decimal(10,1)
declare @SIXCOUNT2 AS decimal(10,1)
declare @TMP2 AS INT
SET @ONECOUNT2 = 0
SET @TWOCOUNT2 = 0
SET @THREECOUNT2 = 0
SET @FOURCOUNT2 = 0
SET @FIVECOUNT2 = 0
SET @SIXCOUNT2 = 0
OPEN STCD_CURSOR2
while @@FETCH_STATUS = 0
BEGIN
SELECT @TMP2 =Station实际类别 FROM GIS_WQ_NM_MC WHERE STCD=@STCD2 AND Year=@年 AND Month=@月
if @TMP2 = 1 set @ONECOUNT2 = @ONECOUNT2+1
if @TMP2 = 2 set @twoCOUNT2 = @twoCOUNT2+1
if @TMP2 = 3 set @threeCOUNT2 = @threeCOUNT2+1
if @TMP2 = 4 set @fourCOUNT2 = @fourCOUNT2+1
if @TMP2 = 5 set @fiveCOUNT2 = @fiveCOUNT2+1
if @TMP2 = 6 set @sixCOUNT2 = @sixCOUNT2+1
FETCH NEXT FROM STCD_CURSOR2 INTO @STCD2
IF (@@fetch_status<>0) break
END
close STCD_CURSOR2
Deallocate STCD_CURSOR2
update #tab3 set 苏浙边界断面数 = @ONECOUNT2 where 水质类别 = 'I'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'I'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @ONECOUNT2/@STCDCOUNT2 where 水质类别 = 'I'
update #tab3 set 苏浙边界断面数 = @twoCOUNT2 where 水质类别 = 'II'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'II'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @twoCOUNT2/@STCDCOUNT2 where 水质类别 = 'II'
update #tab3 set 苏浙边界断面数 = @threeCOUNT2 where 水质类别 = 'III'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'III'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @threeCOUNT2/@STCDCOUNT2 where 水质类别 = 'III'
update #tab3 set 苏浙边界断面数 = @fourCOUNT2 where 水质类别 = 'IV'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'IV'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @fourCOUNT2/@STCDCOUNT2 where 水质类别 = 'IV'
update #tab3 set 苏浙边界断面数 = @fiveCOUNT2 where 水质类别 = 'V'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = 'V'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @fiveCOUNT2/@STCDCOUNT2 where 水质类别 = 'V'
update #tab3 set 苏浙边界断面数 = @sixCOUNT2 where 水质类别 = '劣于V'
if @STCDCOUNT2=0 update #tab3 set 苏浙断面百分比= 0 where 水质类别 = '劣于V'else
if @STCDCOUNT2<>0--by clg
update #tab3 set 苏浙断面百分比= @sixCOUNT2/@STCDCOUNT2 where 水质类别 = '劣于V'
select * from #tab3
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
运行上述的存储过程，得到的结果只有苏沪边界的数据没有苏浙边界的，是不是第二个游标不能使用？请高手指点迷津！