悲剧,游标速度很慢,执行了7秒,还是使用子表比较好
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[tradezone_review_plan]
AS
BEGIN
DECLARE db CURSOR FOR
SELECT
A.ROW_ID,
A.BRAND
FROM T_NWP_TRADEZONE_INFO A
OPEN db
DECLARE @row_id NVARCHAR(32)
DECLARE @brand NVARCHAR(5)
FETCH NEXT FROM db
INTO
@row_id,
@brand
WHILE @@fetch_status = 0
BEGIN
DECLARE @tempRowId NVARCHAR(32)
DECLARE @reviewTime NVARCHAR(10)
DECLARE @reviewYear NVARCHAR(4)
DECLARE @reviewMonth NVARCHAR(2)
DECLARE @tempReviewTime NVARCHAR(10)
SET @tempRowId = REPLACE(NEWID(),'-','')
BEGIN
IF EXISTS(SELECT TOP 1 REVIEW_DATE FROM T_NWP_TRADEZONE_REVIEW WHERE DELETE_FLAG ='1' AND FK_ROW_ID = @row_id ORDER BY REVIEW_DATE DESC)
BEGIN
SET @tempReviewTime = (SELECT TOP 1 REVIEW_DATE FROM T_NWP_TRADEZONE_REVIEW WHERE DELETE_FLAG ='1' AND FK_ROW_ID = @row_id ORDER BY REVIEW_DATE DESC)
IF (SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) - CAST(SUBSTRING(@tempReviewTime,1,4) AS INT) > 0)
BEGIN
SET @reviewTime = CAST((SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) + 1) AS NVARCHAR(10)) + '-01-01'
SET @reviewYear = SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) + 1
SET @reviewMonth = '01'
END
ELSE
BEGIN
SET @reviewTime = REPLACE(@tempReviewTime,SUBSTRING(@tempReviewTime,1,4),SUBSTRING(@tempReviewTime,1,4)+1)
SET @reviewYear = SUBSTRING(@reviewTime,1,4)
SET @reviewMonth = SUBSTRING(@reviewTime,6,7)
END
INSERT INTO T_NWP_TRADEZONE_REVIEW_TEST_PLAN
(
ROW_ID,
TRADEZONE_ID,
GROUP_ID,
SORT_ID,
PLAN_TYPE,
BRAND,
YEAR,
MONTH,
TEST_PLAN_DATE,
YEAR_PLAN_FLAG,
MONTH_PLAN_FLAG,
NO_REVIEW_FLAG,
NO_REVIEW_REASON,
AUDIT_FLAG,
CHANGE_FLAG,
CREATOR_ID,
CREATE_TIME,
UPDATOR_ID,
UPDATE_TIME,
DELETE_FLAG,
TEST_PLAN_BAK_DATE
)VALUES(
@tempRowId, @row_id, @tempRowId,'0','M1121',@brand,@reviewYear,@reviewMonth,@reviewTime,'M0009','M0009','M0817','M1170','','','admin',GETDATE(),'admin',GETDATE(),'1',@reviewTime
)
END
ELSE
IF EXISTS(SELECT TOP 1 SUBSTRING(CONVERT(NVARCHAR(10), CREATE_TIME,20),1,10) FROM H_NWP_TRADE_PLAN_REPORT WHERE FK_ROW_ID = @row_id ORDER BY CREATE_TIME DESC)
BEGIN
SET @tempReviewTime = (SELECT TOP 1 SUBSTRING(CONVERT(NVARCHAR(10), CREATE_TIME,20),1,10) FROM H_NWP_TRADE_PLAN_REPORT WHERE FK_ROW_ID = @row_id ORDER BY CREATE_TIME DESC)
IF (SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) - CAST(SUBSTRING(@tempReviewTime,1,4) AS INT) > 0)
BEGIN
SET @reviewTime = CAST((SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) + 1) AS NVARCHAR(10)) + '-01-01'
SET @reviewYear = SUBSTRING(CONVERT(NVARCHAR(10), GETDATE(),20),1,4) + 1
SET @reviewMonth = '01'
END
ELSE
BEGIN
SET @reviewTime = REPLACE(@tempReviewTime,SUBSTRING(@tempReviewTime,1,4),SUBSTRING(@tempReviewTime,1,4)+1)
SET @reviewYear = SUBSTRING(@reviewTime,1,4)
SET @reviewMonth = SUBSTRING(@reviewTime,6,7)
END
INSERT INTO T_NWP_TRADEZONE_REVIEW_TEST_PLAN
(
ROW_ID,
TRADEZONE_ID,
GROUP_ID,
SORT_ID,
PLAN_TYPE,
BRAND,
YEAR,
MONTH,
TEST_PLAN_DATE,
YEAR_PLAN_FLAG,
MONTH_PLAN_FLAG,
NO_REVIEW_FLAG,
NO_REVIEW_REASON,
AUDIT_FLAG,
CHANGE_FLAG,
CREATOR_ID,
CREATE_TIME,
UPDATOR_ID,
UPDATE_TIME,
DELETE_FLAG,
TEST_PLAN_BAK_DATE
)VALUES(
@tempRowId, @row_id, @tempRowId,'0','M1121',@brand,@reviewYear,@reviewMonth,@reviewTime,'M0009','M0009','M0817','M1170','','','admin',GETDATE(),'admin',GETDATE(),'1',@reviewTime
)
END
END
FETCH NEXT FROM db
INTO
@row_id,
@brand
END
CLOSE db
DEALLOCATE db
END