这样的SQL语句(游标)为什么要被重复执行5次? (5 行受影响) (5 行受影响) (5 行受影响) (5 行受影响) (5
(5行受影响)(5行受影响)(5行受影响)(5行受影响)(5行受影响)DECLARE@SAINTDECLARE@SBINTDECLARE@QN1VARCHAR(11)DE...
(5 行受影响)
(5 行受影响)
(5 行受影响)
(5 行受影响)
(5 行受影响)
DECLARE @SA INT
DECLARE @SB INT
DECLARE @QN1 VARCHAR(11)
DECLARE @QN2 VARCHAR(11)
SET @QN1='2011061316%'
SET @QN2='2011061317%'
DECLARE @S1S3 TABLE(MONITOR_TIME DATETIME,S01 VARCHAR(10),S03 VARCHAR(10))
DECLARE CURSOR_ID CURSOR FOR SELECT CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
OPEN CURSOR_ID
FETCH FROM CURSOR_ID INTO @SA,@SB
SET @SA=@SA+8
SET @SB=@SB+8
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @S1S3
SELECT (SUBSTRING(CP,10,4)+'-'+SUBSTRING(CP,14,2)+'-'+SUBSTRING(CP,16,2)+' '+SUBSTRING(CP,18,2)+':'+SUBSTRING(CP,20,2)+':'+SUBSTRING(CP,22,2)),SUBSTRING(CP,@SB,5),SUBSTRING(CP,@SA,5)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
FETCH NEXT FROM CURSOR_ID INTO @SA,@SB
SET @SA=@SA+8
SET @SB=@SB+8
END
CLOSE CURSOR_ID
DEALLOCATE CURSOR_ID
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
错在这一句啊,where id=@id才好。谢谢hit_lubin 和sytdeedee 得帮助 展开
(5 行受影响)
(5 行受影响)
(5 行受影响)
(5 行受影响)
DECLARE @SA INT
DECLARE @SB INT
DECLARE @QN1 VARCHAR(11)
DECLARE @QN2 VARCHAR(11)
SET @QN1='2011061316%'
SET @QN2='2011061317%'
DECLARE @S1S3 TABLE(MONITOR_TIME DATETIME,S01 VARCHAR(10),S03 VARCHAR(10))
DECLARE CURSOR_ID CURSOR FOR SELECT CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
OPEN CURSOR_ID
FETCH FROM CURSOR_ID INTO @SA,@SB
SET @SA=@SA+8
SET @SB=@SB+8
WHILE(@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @S1S3
SELECT (SUBSTRING(CP,10,4)+'-'+SUBSTRING(CP,14,2)+'-'+SUBSTRING(CP,16,2)+' '+SUBSTRING(CP,18,2)+':'+SUBSTRING(CP,20,2)+':'+SUBSTRING(CP,22,2)),SUBSTRING(CP,@SB,5),SUBSTRING(CP,@SA,5)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
FETCH NEXT FROM CURSOR_ID INTO @SA,@SB
SET @SA=@SA+8
SET @SB=@SB+8
END
CLOSE CURSOR_ID
DEALLOCATE CURSOR_ID
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
错在这一句啊,where id=@id才好。谢谢hit_lubin 和sytdeedee 得帮助 展开
3个回答
展开全部
提示5行受影响,是以下查询得到的结果行数,提示了5次,说明查询执行了5次。查询执行5次,那么就是因为你的游标执行了5次。你单独运行一下游标的查询语句,看看游标是不是有5行记录?如果有5行,那么就对上了。
没执行一次游标,都会执行一下游标体里面的查询,每次查询都会有一个输出信息N行受影响。
SELECT (SUBSTRING(CP,10,4)+'-'+SUBSTRING(CP,14,2)+'-'+SUBSTRING(CP,16,2)+' '+SUBSTRING(CP,18,2)+':'+SUBSTRING(CP,20,2)+':'+SUBSTRING(CP,22,2)),SUBSTRING(CP,@SB,5),SUBSTRING(CP,@SA,5)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
看看SELECT CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
结果是不是5行,如果是,提示5次5行受影响,没问题。
没执行一次游标,都会执行一下游标体里面的查询,每次查询都会有一个输出信息N行受影响。
SELECT (SUBSTRING(CP,10,4)+'-'+SUBSTRING(CP,14,2)+'-'+SUBSTRING(CP,16,2)+' '+SUBSTRING(CP,18,2)+':'+SUBSTRING(CP,20,2)+':'+SUBSTRING(CP,22,2)),SUBSTRING(CP,@SB,5),SUBSTRING(CP,@SA,5)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
看看SELECT CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
结果是不是5行,如果是,提示5次5行受影响,没问题。
追问
DECLARE CURSOR_ID CURSOR FORWARD_ONLY FOR SELECT ID,CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
OPEN CURSOR_ID
FETCH FROM CURSOR_ID INTO @ID,@SA,@SB
谢谢您的提示
展开全部
SELECT (SUBSTRING(CP,10,4)+'-'+SUBSTRING(CP,14,2)+'-'+SUBSTRING(CP,16,2)+' '+SUBSTRING(CP,18,2)+':'+SUBSTRING(CP,20,2)+':'+SUBSTRING(CP,22,2)),SUBSTRING(CP,@SB,5),SUBSTRING(CP,@SA,5)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
这条查询的结果应该是5条,每次循环执行的插入都是由这条查询造成的,每次插入的结果都是5条。所以影响的就是5条。
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
这条查询的结果应该是5条,每次循环执行的插入都是由这条查询造成的,每次插入的结果都是5条。所以影响的就是5条。
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
因为游标循环了5次 每次插了5行
更多追问追答
追问
哪个地方,说具体点,谢谢
追答
DECLARE CURSOR_ID CURSOR FOR SELECT CHARINDEX('S01',CP),CHARINDEX('S03',CP)
FROM [DB_ENVIDATACENTER].[dbo].[T_transmittal]
WHERE MN='83028583206005' AND CN='2011' AND QN BETWEEN @QN1 AND @QN2
这是你定义游标的语句,如果这个select语句的结果有5条,那么就意味着下面的循环将进行5次,而循环体里面是一条insert语句,这条语句也将被执行5次,同时insert语句中的select语句查询出5条结果,即每次执行insert将插入5条记录,也就是影响5行,综上所述,这段程序5次影响了5行。
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询