关于游标,在线等。。。
hzyzx 2004-03-31 11:26:55 我的代码如下,可能出现死循环,运行没结果,请大家看看,帮忙解决。。
CREATE PROCEDURE [dbo].[d]
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NEWHL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NEWHL]
SELECT *
into dbo.NEWHL
FROM (SELECT GP_SG, gp_cz,GP_DBPF, GP_GYPF, GP_ZTC, GP_ZBP, 'Z' AS gp_zuo,SPACE(40) AS GP_ZLJ1, SPACE(40) AS GP_ZLJ2,SPACE(40) AS GP_YLJ1,SPACE(40) AS GP_YLJ2
FROM dbo.HLZ
WHERE (GP_ZTC > '')
UNION
SELECT gp_sg,gp_cz, gp_dbpf, gp_gypf, gp_ytc, gp_ybp, 'Y' AS gp_zuo,SPACE(40) AS GP_ZLJ1, SPACE(40) AS GP_ZLJ2,SPACE(40) AS GP_YLJ1,SPACE(40) AS GP_YLJ2
FROM hlz
WHERE gp_ytc > '') DERIVEDTBL
--ER TABLE [DBO].NEWHL ADD GP_ZLJ1 CHAR(40) --COLLATE Chinese_PRC_CI_AS NULL
--ALTER TABLE [DBO].NEWHL ADD GP_ZLJ2 CHAR(40) --COLLATE Chinese_PRC_CI_AS NULL
--LTER TABLE [DBO].NEWHL ADD GP_YLJ1 CHAR(40) --COLLATE Chinese_PRC_CI_AS NULL
--LTER TABLE [DBO].NEWHL ADD GP_YLJ2 CHAR(40) --COLLATE Chinese_PRC_CI_AS NULL
alter table dbo.newhl WITH NOCHECK ADD CONSTRAINT pk_newHL
PRIMARY KEY CLUSTERED (gp_cz,gp_dbpf,gp_SG,gp_gypf,gp_zuo)
update newhl set GP_ZLJ1=gp_zbp where gp_zuo='Z' and (len(rtrim(gp_sg))=5)
update newhl set GP_YLJ1=gp_zbp where gp_zuo='Y' and (len(rtrim(gp_sg))=5)
begin
DECLARE @CZ CHAR(30)
DECLARE @DBPF CHAR(40)
DECLARE @GYPF CHAR(50)
DECLARE @ZBP CHAR(20)
declare @ZTC varchar(2)
declare @zuo varchar(2)
DECLARE @SG VARCHAR(6)
DECLARE @LJMX CHAR(40)
declare AA CURSOR FOR
SELECT DISTINCT GP_CZ,GP_DBPF,gp_gypf,GP_ZTC,GP_ZBP FROM NEWHL
open aa
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP
while @@fetch_status=0
begin
declare bb cursor for select GP_SG from newhl
WHERE GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
SET @LJMX='与'
OPEN BB
FETCH NEXT FROM BB INTO @SG
WHILE @@FETCH_STATUS=0
BEGIN
SET @LJMX=RTRIM(@LJMX)+RTRIM(@SG)
END
FETCH NEXT FROM BB INTO @SG
SET @LJMX=RTRIM(@LJMX) +'同穿'
if @@fetch_status<>0
begin
close bb
deallocate bb
continue
end
close bb
deallocate bb
---------------------------------------------------------------------------
declare bb cursor for select gp_sg,gp_zuo from newhl
where GP_CZ=@CZ AND GP_DBPF=@DBPF AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP
open bb
fetch next from bb into @sg, @zuo
while @@fetch_status =0
begin
if len(rtrim(@sg)) =3
begin
if @zuo='Z'
begin
update newhl set gp_zlj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
update newhl set gp_ylj1=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
else
begin
if @zuo='Z'
update newhl set gp_zlj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
else
update newhl set gp_ylj2=@ljmx where GP_CZ=@CZ AND GP_DBPF=@DBPF
AND GP_ZTC=@ZTC AND GP_ZBP=@ZBP and gp_sg=@sg and gp_zuo =@zuo
end
end
fetch next from bb into @sg, @zuo
if @@fetch_status<>0
begin
close bb
deallocate bb
continue
end
close bb
deallocate bb
------------------------------------------------------------------------------
end
fetch next from aa into @CZ,@DBPF,@GYPF,@ZTC,@ZBP
close aa
deallocate aa
end
GO