和炭化室数量相关的储存过程.doc

资源描述

修改名称 SP_GetMplcData 修改位置第15行原代码 Select * from T_MPLCTestData WHERE LEAK = '泄漏' OR ID > 200 Order By (Case When (id=201 or id = 202) Then 1 Else 0 End),testtime 修改后代码 Select * from T_MPLCTestData WHERE LEAK = '泄漏' OR ID > @CharRoomCount*2 Order By (Case When (id=@CharRoomCount*2+1 or id = @CharRoomCount*2+2) Then 1 Else 0 End),testtime 测试结果用代码exec SP_GetMplcData调用此存储过程，得到与预期结果相符的数据，测试成功。修改名称 SP_GetOutCharRoom 修改位置第23行原代码 IF @ID > 100 SET @ID = @ID -100 修改后代码 IF @ID > @CharRoomCount SET @ID = @ID - @CharRoomCount 测试结果定义输出变量@i，用代码exec SP_GetOutCharRoom @i output, ' '调用此存储过程，测试第1个与最后一个炭化室号，再随机抽取一个，得到与预期结果相符的数据，测试成功。修改名称 SP_GetPreOutNo 修改位置第19行原代码 IF @ID = 0 SET @ID = 100 修改后代码 IF @ID = 0 SET @ID = @CharRoomCount 测试结果定义输出变量@i，用代码exec SP_GetPreOutNo ' ', @i output调用此存储过程，测试第1个与最后一个炭化室号，再随机抽取一个，得到与预期结果相符的数据，测试成功。修改名称 SP_GetStandardCharRoom 修改位置第19行原代码 IF @ID > 100 SET @ID = @ID - 100 修改后代码 IF @ID > @CharRoomCount SET @ID = @ID - @CharRoomCount 测试结果定义输出变量@i，用代码exec SP_GetStandardCharRoom ' ', @i output调用此存储过程，测试第1个与最后一个炭化室号，再随机抽取一个，得到与预期结果相符的数据，测试成功。修改名称 SP_GetStoveLidData 修改位置第13行原代码 SELECT * FROM T_StoveLidTestData WHERE LEAK = '泄漏' OR ID > 400 Order By (Case When (id=401 or id = 402) Then 1 Else 0 End),testtime 修改后代码 SELECT * FROM T_StoveLidTestData WHERE LEAK = '泄漏' OR ID > @CharRoomCount*4 Order By (Case When (id=@CharRoomCount * 4 + 1 or id = @CharRoomCount * 4 + 2) Then 1 Else 0 End),testtime 测试结果用代码exec SP_GetStoveLidData调用此存储过程，得到与预期结果相符的数据，测试成功。修改名称 SP_GetTestCharRoom 修改位置第19行原代码 IF @ID <= 0 SET @ID = @ID +100 修改后代码 IF @ID <= 0 SET @ID = @ID + @CharRoomCount 测试结果定义输出变量@i，用代码exec SP_GetTestCharRoom ' ', @i output调用此存储过程，测试第1个与最后一个炭化室号，再随机抽取一个，得到与预期结果相符的数据，测试成功。修改名称 SP_LostDataMakeupCPLC 修改位置1 第131行原代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE PCAppraise = '达标' AND ID < 101 修改后代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE PCAppraise = '达标' AND ID <= @CharRoomCount 修改位置2 第134行原代码 SELECT @PCCOUNT = COUNT(*)-3 FROM T_CPLCTestData 修改后代码 SELECT @PCCOUNT = @CharRoomCount 修改位置3 第136行原代码 SELECT @SO2MUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE SO2Appraise = '达标' AND ID < 101 修改后代码 SELECT @SO2MUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE SO2Appraise = '达标' AND ID <= @CharRoomCount 修改位置5 第143行原代码 UPDATE T_CPLCTestData SET PcAppraise = @PCMU, SO2Appraise = @SO2MU WHERE ID = 103 修改后代码 UPDATE T_CPLCTestData SET PcAppraise = @PCMU, SO2Appraise = @SO2MU WHERE ID = @CharRoomCount+3 修改位置6 第148行原代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_CPLCTestData WHERE ID <= 100 修改后代码 UPDATE T_CPLCTestData SET PcAppraise = @PCMU, SO2Appraise = @SO2MU WHERE ID = @CharRoomCount+3 修改位置7 第151行原代码 UPDATE T_CPLCTestData SET PCValue = @MAXVALUE WHERE ID = 101 UPDATE T_CPLCTestData SET PCValue = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_CPLCTestData SET PCValue = @MAXVALUE WHERE ID = @CharRoomCount+1 UPDATE T_CPLCTestData SET PCValue = @AVGVALUE WHERE ID = @CharRoomCount+2 修改位置8 第 156行原代码 SELECT @MAXVALUE = MAX(SO2Value), @AVGVALUE = AVG(SO2Value) FROM T_CPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(SO2Value), @AVGVALUE = AVG(SO2Value) FROM T_CPLCTestData WHERE ID <= @CharRoomCount 修改位置9 第159行原代码 UPDATE T_CPLCTestData SET SO2Value = @MAXVALUE WHERE ID = 101 UPDATE T_CPLCTestData SET SO2Value = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_CPLCTestData SET SO2Value = @MAXVALUE WHERE ID = @CharRoomCount+1 UPDATE T_CPLCTestData SET SO2Value = @AVGVALUE WHERE ID = @CharRoomCount+2 测试结果修改名称 SP_LostDataMakeupHPLC 修改位置1 第192行原代码 IF @ChrRmNo = 3 OR @ChrRmNo = 27 OR @ChrRmNo = 53 OR @ChrRmNo = 58 OR @ChrRmNo = 83 OR @ChrRmNo = 108 修改后代码 IF @ChrRmNo = 3 OR @ChrRmNo = dbo.F_OrderToPhy(@CharRoomCount/4) OR @ChrRmNo = dbo.F_OrderToPhy(@CharRoomCount/2-2) OR @ChrRmNo = dbo.F_OrderToPhy(@CharRoomCount/2+2) OR @ChrRmNo = dbo.F_OrderToPhy(@CharRoomCount*3/4) OR @ChrRmNo = dbo.F_OrderToPhy(@CharRoomCount-2) 修改位置2 第285行原代码 IF (@PLCCODE = 'SB03' AND @TestCharRoomNo <= 55 AND NOT EXISTS(SELECT * FROM T_StoveLidTestDataHistory WHERE CHARROOMNO = @TestCharRoomNo AND ABS(DATEDIFF(MINUTE, TESTTIME, @CurTestTime))<=5)) OR (@PLCCODE = 'SB05' AND @TestCharRoomNo >= 56 AND NOT EXISTS(SELECT * FROM T_StoveLidTestDataHistory WHERE CHARROOMNO = @TestCharRoomNo AND ABS(DATEDIFF(MINUTE, TESTTIME, @CurTestTime))<=5)) 修改后代码 IF (@PLCCODE = 'SB03' AND @TestCharRoomNo <= DBO.F_OrderToPhy(@CharRoomCount/2) AND NOT EXISTS(SELECT * FROM T_StoveLidTestDataHistory WHERE CHARROOMNO = @TestCharRoomNo AND ABS(DATEDIFF(MINUTE, TESTTIME, @CurTestTime))<=5)) OR (@PLCCODE = 'SB05' AND @TestCharRoomNo > DBO.F_OrderToPhy(@CharRoomCount/2) AND NOT EXISTS(SELECT * FROM T_StoveLidTestDataHistory WHERE CHARROOMNO = @TestCharRoomNo AND ABS(DATEDIFF(MINUTE, TESTTIME, @CurTestTime))<=5)) 修改位置3 第430行原代码 IF (@PLCCODE = 'SB02') AND (@TestCharRoomNo = 3 OR @TestCharRoomNo = 27 OR @TestCharRoomNo = 53 OR (@TestCharRoomNo = @XFX1 AND @XFX1 <= 55 AND @JJC1 = '机侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 <= 55 AND @JJC2 = '机侧')) 修改后代码 IF (@PLCCODE = 'SB02') AND (@TestCharRoomNo = 3 OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/4) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/2-2) OR (@TestCharRoomNo = @XFX1 AND @XFX1 <= DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC1 = '机侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 <= DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC2 = '机侧')) 修改位置4 第475行原代码 IF (@PLCCODE = 'SB03') AND (@TestCharRoomNo = 3 OR @TestCharRoomNo = 27 OR @TestCharRoomNo = 53 OR (@TestCharRoomNo = @XFX1 AND @XFX1 <= 55 AND @JJC1 = '焦侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 <= 55 AND @JJC2 = '焦侧')) 修改后代码 IF (@PLCCODE = 'SB03') AND (@TestCharRoomNo = 3 OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/4) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/2-2) OR (@TestCharRoomNo = @XFX1 AND @XFX1 <= DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC1 = '焦侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 <= DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC2 = '焦侧')) 修改位置5 第519行原代码 IF (@PLCCODE = 'SB04') AND (@TestCharRoomNo = 58 OR @TestCharRoomNo = 83 OR @TestCharRoomNo = 108 OR (@TestCharRoomNo = @XFX1 AND @XFX1 >= 56 AND @JJC1 = '机侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 >= 56 AND @JJC2 = '机侧')) 修改后代码 IF (@PLCCODE = 'SB04') AND (@TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/2+2) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount*3/4) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount-2) OR (@TestCharRoomNo = @XFX1 AND @XFX1 > DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC1 = '焦侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 > DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC2 = '焦侧')) 修改位置6 第564行原代码 IF (@PLCCODE = 'SB04') AND (@TestCharRoomNo = 58 OR @TestCharRoomNo = 83 OR @TestCharRoomNo = 108 OR (@TestCharRoomNo = @XFX1 AND @XFX1 >= 56 AND @JJC1 = '机侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 >= 56 AND @JJC2 = '机侧')) 修改后代码 IF (@PLCCODE = 'SB04') AND (@TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount/2+2) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount*3/4) OR @TestCharRoomNo = dbo.F_OrderToPhy(@CharRoomCount-2) OR (@TestCharRoomNo = @XFX1 AND @XFX1 > DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC1 = '焦侧') OR (@TestCharRoomNo = @XFX2 AND @XFX2 > DBO.F_OrderToPhy(@CharRoomCount/2) AND @JJC2 = '焦侧')) 测试结果修改名称 SP_LostDataMakeupMPLC 修改位置第193行原代码 UPDATE T_MPLCTestData SET LEAK = CAST(@JCLEAK AS VARCHAR(10)) + '%' WHERE ID = 201 UPDATE T_MPLCTestData SET LEAK = CAST(@CCLEAK AS VARCHAR(10)) + '%' WHERE ID = 202 修改后代码 UPDATE T_MPLCTestData SET LEAK = CAST(@JCLEAK AS VARCHAR(10)) + '%' WHERE ID = @CharRoomCount + 1 UPDATE T_MPLCTestData SET LEAK = CAST(@CCLEAK AS VARCHAR(10)) + '%' WHERE ID = @CharRoomCount + 2 测试结果修改名称 SP_LostDataMakeupZPLC 修改位置1 第103行原代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE PCAppraise = '达标' AND ID < 101 修改后代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE PCAppraise = '达标' AND ID < @CharRoomCount + 1 修改位置2 第107行原代码 SELECT @PCCOUNT = COUNT(*)-3 FROM T_ZPLCTestData 修改后代码 SELECT @PCCOUNT = @CharRoomCount 修改位置3 第109行原代码 SELECT @H2SMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE H2SAppraise = '达标' AND ID < 101 修改后代码 SELECT @H2SMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE H2SAppraise = '达标' AND ID < @CharRoomCount + 1 修改位置4 第117行原代码 UPDATE T_ZPLCTestData SET PCAppraise = @PCMU, H2SAppraise = @H2SMU WHERE ID = 103 修改后代码 UPDATE T_ZPLCTestData SET PCAppraise = @PCMU, H2SAppraise = @H2SMU WHERE ID = @CharRoomCount + 3 修改位置5 第123行原代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_ZPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_ZPLCTestData WHERE ID <= @CharRoomCount 修改位置5 第126行原代码 UPDATE T_ZPLCTestData SET PCValue = @MAXVALUE WHERE ID = 101 UPDATE T_ZPLCTestData SET PCValue = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_ZPLCTestData SET PCValue = @MAXVALUE WHERE ID = @CharRoomCount + 1 UPDATE T_ZPLCTestData SET PCValue = @AVGVALUE WHERE ID = @CharRoomCount + 2 修改位置6 第131行原代码 SELECT @MAXVALUE = MAX(H2SValue), @AVGVALUE = AVG(H2SValue) FROM T_ZPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(H2SValue), @AVGVALUE = AVG(H2SValue) FROM T_ZPLCTestData WHERE ID <= @CharRoomCount 修改位置7 第134行原代码 UPDATE T_ZPLCTestData SET H2SValue = @MAXVALUE WHERE ID = 101 UPDATE T_ZPLCTestData SET H2SValue = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_ZPLCTestData SET H2SValue = @MAXVALUE WHERE ID = @CharRoomCount + 1 UPDATE T_ZPLCTestData SET H2SValue = @AVGVALUE WHERE ID = @CharRoomCount + 2 测试结果修改名称 SP_HplcUPDATA 修改位置第61行原代码 SELECT @PCCOUNT = COUNT(*)-3 FROM T_HPLCTestData 修改后代码 SELECT @PCCOUNT = @charRoomCount 测试结果修改名称 SP_MPLCUPDATE 修改位置第30行原代码 UPDATE T_MPLCTestData SET LEAK = CAST(@JCLEAK AS VARCHAR(10)) + '%' WHERE ID = 201 UPDATE T_MPLCTestData SET LEAK = CAST(@CCLEAK AS VARCHAR(10)) + '%' WHERE ID = 202 修改后代码 UPDATE T_MPLCTestData SET LEAK = CAST(@JCLEAK AS VARCHAR(10)) + '%' WHERE ID = @CharRoomCount * 2 + 1 UPDATE T_MPLCTestData SET LEAK = CAST(@CCLEAK AS VARCHAR(10)) + '%' WHERE ID = @CharRoomCount * 2 + 2 测试结果修改名称 SP_NewCplcData 修改位置1 第112行原代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE PCAppraise = '达标' AND ID < 101 修改后代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE PCAppraise = '达标' AND ID <= @CharRoomCount 修改位置2 第115行原代码 SELECT @PCCOUNT = COUNT(*)-3 FROM T_CPLCTestData 修改后代码 SELECT @PCCOUNT = @CharRoomCount 修改位置3 第117行原代码 SELECT @SO2MUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE SO2Appraise = '达标' AND ID < 101 修改后代码 SELECT @SO2MUCOUNT = COUNT(*) FROM T_CPLCTestData WHERE SO2Appraise = '达标' AND ID <= @CharRoomCount 修改位置4 第126行原代码 UPDATE T_CPLCTestData SET PcAppraise = @PCMU, SO2Appraise = @SO2MU WHERE ID = 103 修改后代码 UPDATE T_CPLCTestData SET PcAppraise = @PCMU, SO2Appraise = @SO2MU WHERE ID = @CharRoomCount + 3 修改位置5 第131行原代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_CPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_CPLCTestData WHERE ID <= @CharRoomCount 修改位置6 第134行原代码 UPDATE T_CPLCTestData SET PCValue = @MAXVALUE WHERE ID = 101 UPDATE T_CPLCTestData SET PCValue = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_CPLCTestData SET PCValue = @MAXVALUE WHERE ID = @CharRoomCount + 1 UPDATE T_CPLCTestData SET PCValue = @AVGVALUE WHERE ID = @CharRoomCount + 2 修改位置7 第139行原代码 SELECT @MAXVALUE = MAX(SO2Value), @AVGVALUE = AVG(SO2Value) FROM T_CPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(SO2Value), @AVGVALUE = AVG(SO2Value) FROM T_CPLCTestData WHERE ID <= @CharRoomCount 修改位置8 第142行原代码 UPDATE T_CPLCTestData SET SO2Value = @MAXVALUE WHERE ID = 101 UPDATE T_CPLCTestData SET SO2Value = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_CPLCTestData SET SO2Value = @MAXVALUE WHERE ID = @CharRoomCount + 1 UPDATE T_CPLCTestData SET SO2Value = @AVGVALUE WHERE ID = @CharRoomCount + 2 测试结果修改名称 SP_NewZplcData 修改位置1 第117行原代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE PCAppraise = '达标' AND ID < 101 修改后代码 SELECT @PCMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE PCAppraise = '达标' AND ID <= @CharRoomCount 修改位置2 第121行原代码 SELECT @PCCOUNT = COUNT(*)-3 FROM T_ZPLCTestData 修改后代码 SELECT @PCCOUNT = @CharRoomCount 修改位置3 第123行原代码 SELECT @H2SMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE H2SAppraise = '达标' AND ID < 101 修改后代码 SELECT @H2SMUCOUNT = COUNT(*) FROM T_ZPLCTestData WHERE H2SAppraise = '达标' AND ID <= @CharRoomCount 修改位置4 第133行原代码 UPDATE T_ZPLCTestData SET PCAppraise = @PCMU, H2SAppraise = @H2SMU WHERE ID = 103 修改后代码 UPDATE T_ZPLCTestData SET PCAppraise = @PCMU, H2SAppraise = @H2SMU WHERE ID = @CharRoomCount + 3 修改位置5 第139行原代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_ZPLCTestData WHERE ID <= 100 修改后代码 SELECT @MAXVALUE = MAX(PCValue), @AVGVALUE = AVG(PCValue) FROM T_ZPLCTestData WHERE ID <= @CharRoomCount 修改位置6 第142行原代码 UPDATE T_ZPLCTestData SET PCValue = @MAXVALUE WHERE ID = 101 UPDATE T_ZPLCTestData SET PCValue = @AVGVALUE WHERE ID = 102 修改后代码 UPDATE T_ZPLCTestData SET PCValue = @MAXVALUE WHERE ID = @CharRoomCount + 1 UPDATE T_ZPLC

展开阅读全文