IF OBJECT_ID ( 'PR_ZKC_ADDRKD2', 'P' ) IS NOT NULL
DROP PROCEDURE PR_ZKC_ADDRKD2;
GO
CREATE PROCEDURE PR_ZKC_ADDRKD2
@vLsbh varchar(20)
AS
DECLARE @vWlbh varchar(30)
DECLARE @vFlbh varchar(20)
DECLARE @vJH varchar(20)
DECLARE @iNumber INT
DECLARE zkc_cursor CURSOR
FOR SELECT RKD1_WLBH,RKD1_FLBH,CONVERT(INT,RKD1_SSSL) FROM RKD1,WLZD WHERE RKD1_WLBH = WLZD_WLBH
AND WLZD_SFDJ = '1' AND RKD1_LSBH = @vLsbh
OPEN zkc_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @vWlbh=''
SELECT @vFlbh=''
SELECT @iNumber=0
FETCH NEXT FROM zkc_cursor INTO @vWlbh,@vFlbh,@iNumber
SELECT @vJH = ''
SELECT @vJH = MAX(RKD2_JH) FROM RKD2
WHERE EXISTS (SELECT 1 FROM RKD1 WHERE RKD1_WLBH = @vWlbh AND RKD1_LSBH = RKD2_LSBH AND RKD1_FLBH = RKD2_FLBH)
IF @vJH = '' OR @vJH IS NULL
BEGIN
SELECT @vJH='100525000'
END
WHILE @iNumber > 0
BEGIN
SELECT @vJH = CONVERT(varchar,CONVERT(INT,@vJH)+1)
INSERT INTO RKD2(RKD2_LSBH,RKD2_FLBH,RKD2_JH,RKD2_SL,RKD2_FSL1,RKD2_FSL2)
VALUES(@vLsbh,@vFlbh,@vJH,1,1,1)
select @iNumber = @iNumber - 1
END
END
CLOSE zkc_cursorDEALLOCATE zkc_cursor
执行第一次可以,执行第二次就没反应了,
SQLSERVER 2005关了,在打开一次还能执行,
不知道怎么回事
DROP PROCEDURE PR_ZKC_ADDRKD2;
GO
CREATE PROCEDURE PR_ZKC_ADDRKD2
@vLsbh varchar(20)
AS
DECLARE @vWlbh varchar(30)
DECLARE @vFlbh varchar(20)
DECLARE @vJH varchar(20)
DECLARE @iNumber INT
DECLARE zkc_cursor CURSOR
FOR SELECT RKD1_WLBH,RKD1_FLBH,CONVERT(INT,RKD1_SSSL) FROM RKD1,WLZD WHERE RKD1_WLBH = WLZD_WLBH
AND WLZD_SFDJ = '1' AND RKD1_LSBH = @vLsbh
OPEN zkc_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @vWlbh=''
SELECT @vFlbh=''
SELECT @iNumber=0
FETCH NEXT FROM zkc_cursor INTO @vWlbh,@vFlbh,@iNumber
SELECT @vJH = ''
SELECT @vJH = MAX(RKD2_JH) FROM RKD2
WHERE EXISTS (SELECT 1 FROM RKD1 WHERE RKD1_WLBH = @vWlbh AND RKD1_LSBH = RKD2_LSBH AND RKD1_FLBH = RKD2_FLBH)
IF @vJH = '' OR @vJH IS NULL
BEGIN
SELECT @vJH='100525000'
END
WHILE @iNumber > 0
BEGIN
SELECT @vJH = CONVERT(varchar,CONVERT(INT,@vJH)+1)
INSERT INTO RKD2(RKD2_LSBH,RKD2_FLBH,RKD2_JH,RKD2_SL,RKD2_FSL1,RKD2_FSL2)
VALUES(@vLsbh,@vFlbh,@vJH,1,1,1)
select @iNumber = @iNumber - 1
END
END
CLOSE zkc_cursorDEALLOCATE zkc_cursor
执行第一次可以,执行第二次就没反应了,
SQLSERVER 2005关了,在打开一次还能执行,
不知道怎么回事
游标的基本写法declare @id int,@name varchar(20);
declare cur cursor fast_forward for
select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
--做你要做的事
fetch next from cur into @id,@name;
end
close cur;
deallocate cur;
DROP PROCEDURE PR_ZKC_ADDRKD2;
GO
CREATE PROCEDURE PR_ZKC_ADDRKD2
@vLsbh varchar(20)
AS
DECLARE @vWlbh varchar(30)
DECLARE @vFlbh varchar(20)
DECLARE @vJH varchar(20)
DECLARE @iNumber INTDECLARE zkc_cursor CURSOR
FOR SELECT RKD1_WLBH,RKD1_FLBH,CONVERT(INT,RKD1_SSSL) FROM RKD1,WLZD WHERE RKD1_WLBH = WLZD_WLBH
AND WLZD_SFDJ = '1' AND RKD1_LSBH = @vLsbh
OPEN zkc_cursor
FETCH NEXT FROM zkc_cursor INTO @vWlbh,@vFlbh,@iNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @vJH = ''
SELECT @vJH = MAX(RKD2_JH) FROM RKD2
WHERE EXISTS (SELECT 1 FROM RKD1 WHERE RKD1_WLBH = @vWlbh AND RKD1_LSBH = RKD2_LSBH AND RKD1_FLBH = RKD2_FLBH)
IF @vJH = '' OR @vJH IS NULL
BEGIN
SELECT @vJH='100525000'
END
WHILE @iNumber > 0
BEGIN
SELECT @vJH = CONVERT(varchar,CONVERT(INT,@vJH)+1)
INSERT INTO RKD2(RKD2_LSBH,RKD2_FLBH,RKD2_JH,RKD2_SL,RKD2_FSL1,RKD2_FSL2)
VALUES(@vLsbh,@vFlbh,@vJH,1,1,1)
select @iNumber = @iNumber - 1
END
SELECT @vWlbh=''
SELECT @vFlbh=''
SELECT @iNumber=0
FETCH NEXT FROM zkc_cursor INTO @vWlbh,@vFlbh,@iNumberEND
CLOSE zkc_cursorDEALLOCATE zkc_cursor