如何将基于CURSOR的查询转换为基于SET [英] How to convert CURSOR based query in to SET based
问题描述
我不是SQL方面的专家，我正在研究巨大的SQL代码，但不幸的是它有一个CURSOR，它可以在其中处理另外两个嵌套的CURSORS(存储过程中总共有三个游标)，可以处理数百万个数据进行DELETE，UPDATE和INSERT操作.
I'm not a expert on SQL and I'm working on a huge SQL code and unfortunately it has a CURSOR which handles another two nested CURSORS within it (totally three cursors inside a stored procedure), which handles millions of data to be DELETE,UPDATE and INSERT.
由于执行逐行操作，这需要花费大量时间(超过13个小时，最终会出现错误光标名称数据集已经存在")，我正在尝试将其修改为基于SET的方法，我陷入了如何转换为基于SET的方法时如何执行INSERT，DELETE和UPDATE的问题.换句话说，我无法通过在JOINS中替换它们来完成所有的INSERT，DELETE和UPDATE.
This takes a whole lot of time (more than 13 hrs and eventually gets an error 'cursor names datasets already exists') because of row by row execution and I'm trying to modify this in to SET based approach and I'm stuck how to do the INSERT,DELETE and UPDATE while converting into SET based approach.Or in other words I couldn't do all the INSERT,DELETE and UPDATE by replacing them in JOINS.
我知道这是一个很大的代码，可能不是在这里发布的正确尝试，但是我已经以一种简单的方式在另一篇文章中发表了，人们并没有理解我的问题，所以我将其放在此处实际代码.
I know this is a large code and might not be right attempt to post here but I already to put this in a simple manner in another post and people doesn't get the idea of my question so I'm putting here the actual code.
这是我正在处理的代码:
This is the code I'm working on :
ALTER PROCEDURE [dbo].[proc_Upload] WITH RECOMPILE
as
set NoCount on
DECLARE
@StartTime datetime,
@EndTime datetime,
@DataID uniqueidentifier,
@CollectionDate datetime,
@Status int,
@PeriodID int,
@EndDate datetime,
@GroupID uniqueidentifier,
@ProjectID INT,
@FAID uniqueidentifier,
@UploadID int ,
@Createdate datetime
declare @projects table(
[ProjectID] [INT],
[Title] [varchar](255) ,
[currency] [int] ,
[Cost_Until_Today] [float] ,
[StartDate] [datetime] ,
[EndDate] [datetime] ,
[MisID] [uniqueidentifier] ,
[SystemStatus] [int] ,
[FacilitatingAgency] [uniqueidentifier] ,
[SyncMode] [int] )
INSERT @projects
SELECT ID, PROJECTNAME, PROJECTCURRENCY_ID, Cost_Until_Today, PROJECTESTABLISHEDDATE, EndDate, MisID, 4, FacilitatingAgency, SyncMode
FROM [dbo].[T_PROJECTS] /*thsi is the source table where every projectIDs need to be processed*/
IF exists ( select *
from dbo.sysobjects
where id = object_id(N'[dbo].[datasets]') and objectproperty(id, N'IsTable') = 1 )
DROP Table [dbo].datasets
SELECT mgd.Gd_ID, mg.Grp_ID, mgd.Gd_CollectionDate, mgd.Gd_IsDeleted, mgd.Gd_CreateDate, mg.Grp_Project, mg.Grp_IsDeleted , mg.Grp_Legacy_ID, p.LegacyProjectId
INTO datasets
FROM Savix_Service_Group..Group_Data mgd
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_ID = mgc.Gc_GroupID
inner join SG_Dynamic_Forms..v_projects p ON p.ProjectID = mg.Grp_Project
DECLARE projects1 CURSOR LOCAL FOR Select distinct ProjectID, isnull([FacilitatingAgency], '00000000-0000-0000-0000-000000000000') from @projects P WHERE P.SystemStatus = 4 AND P.SyncMode = 1
/*First cursor - fetch the cursor from ProjectaTable*/
OPEN projects1
FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRAN
DELETE FROM T_PROJECTGROUPSDATA
WHERE T_PROJECTGROUPSDATA.UPLOAD_ID IN (SELECT ID FROM T_UPLOADS WHERE project_savix_ID = @ProjectID AND UPLOADFILENAME = 'Automatic upload from web MIS')
DECLARE datasets CURSOR LOCAL FAST_FORWARD FOR SELECT Gd_ID, Grp_ID, Gd_CollectionDate, Gd_IsDeleted, Gd_CreateDate
FROM datasets
WHERE LegacyProjectId = @ProjectID AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1
/*Second cursor - this will get the 'collectionDate'field from datasetsTable for every project fetched in above cursor and also get @dataID which is used to insert value in to other table-T_PROJECTGROUPSDATA*/
OPEN datasets
FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE period CURSOR LOCAL FAST_FORWARD FOR SELECT ID, dbo.fn_GetEndOfPeriod(ID) FROM T_PERIODS
/* dbo.fn_GetEndOfPeriod(ID) - this function will give the end of the date of that specifc quarter for any given date*/
WHERE DATEDIFF(dd,@CollectionDate,dbo.fn_GetEndOfPeriod(ID)) >= 0
ORDER BY [YEAR],[Quarter]
/*Third Cursor - this will process the records from another table called period with above fetched @collectionDate*/
OPEN period
FETCH NEXT FROM period INTO @PeriodID, @EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM Savix_Service_Group..Group_Data mgd
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
WHERE mgc.Gc_GroupID = @GroupID
AND DATEDIFF(dd,mgd.Gd_CollectionDate,@EndDate) >= 0
AND (mgd.Gd_CollectionDate > @CollectionDate )
AND mgd.Gd_IsDeleted != 1)
BEGIN
BREAK
END
IF EXISTS (SELECT ID FROM T_UPLOADS u
WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3 AND UPLOADFILENAME != 'Automatic upload from web MIS')
BEGIN
FETCH NEXT FROM period INTO @PeriodID, @EndDate
CONTINUE
END
SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
/*If T_uploads doesn't have appropirate period ID from cursor fetch then create a new entry in T_uploads with current projectID*/
IF @UploadID IS NULL
BEGIN
declare @Project_ID_Legacy int = ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID)
INSERT INTO T_UPLOADSFIRSTSTEP
(PROJECT_ID
,UPLOADDATE
,UPLOADFILENAME
,UPLOADUSER_ID
,CURRENTSTEP
,STATUS
,Project_ID_MIS)
SELECT @Project_ID_Legacy , --mm 06/12
GETDATE(),
'Automatic upload from web MIS',
2,
2,
0,
@ProjectID
INSERT INTO T_UPLOADS
(ID, periodID, projectID,UPLOADDATE,UPLOADFILENAME,UPLOADUSER_ID )
SELECT uf.ID,
@PeriodID,
uf.PROJECT_ID,
uf.UPLOADDATE,
uf.UPLOADFILENAME,
uf.UPLOADUSER_ID
FROM T_UPLOADSFIRSTSTEP uf
INNER JOIN @projects mp ON uf.Project_ID_MIS = mp.ProjectID
WHERE uf.Project_ID_MIS = @ProjectID AND uf.STATUS = 0
AND NOT EXISTS (SELECT * FROM T_UPLOADS u WHERE u.PROJECT_ID = uf.PROJECT_ID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
DELETE FROM T_UPLOADSFIRSTSTEP WHERE STATUS = 0 AND PROJECT_ID = @Project_ID_Legacy
SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3)
END ELSE
UPDATE T_UPLOADS SET
TOTALEXPENDITURES = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(@PeriodID))/DATEDIFF(d,mp.StartDate,mp.EndDate)/dbo.fn_RateAtDate(mp.Currency,dbo.fn_GetEndOfPeriod(@PeriodID))
ELSE 0 END,
TotalExpendituresNative = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(@PeriodID))/DATEDIFF(d,mp.StartDate,mp.EndDate)
ELSE 0 END
FROM @projects mp
WHERE T_UPLOADS.ID = @UploadID AND mp.ProjectID = @ProjectID
INSERT INTO T_PROJECTGROUPSDATA
(uploadID, fieldA,fieldB,......fieldN )
SELECT @UploadID,p.fieldA,mg.fieldB,......mgc.fieldN
FROM @projects mp
inner join SG_Dynamic_Forms..v_projects p ON p.LegacyProjectId = mp.projectID
inner join Savix_Service_Group..Groups mg ON mg.Grp_Project = p.ProjectID
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_GroupID = mg.Grp_ID
INNER JOIN Savix_Service_Group..Group_Data mgd ON mgd.Gd_CycleID = mgc.Gc_ID
LEFT JOIN Savix_Service_Trainers..Trainers me ON me.Tr_ID = mgc.Gc_MonitoredBy
LEFT JOIN Savix_Service_Dictionaries..Dictionary mgt ON mgt.Dny_ID = me.Tr_Type
--left join v1_Report_UDF_Data_UploadToSavix udf on udf.DataID = mgd.Gd_ID
WHERE mgd.Gd_ID = @DataID
FETCH NEXT FROM period INTO @PeriodID, @EndDate
END
CLOSE period
DEALLOCATE period
FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
END
CLOSE datasets
DEALLOCATE datasets
COMMIT
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID
IF CURSOR_STATUS('global' , 'period') >= 0
BEGIN
CLOSE period
DEALLOCATE uploadID
END
IF CURSOR_STATUS('global' , 'datasets') >= 0
BEGIN
CLOSE datasets
DEALLOCATE datasets
END
IF @@TRANCOUNT > 0
ROLLBACK
INSERT INTO error_catch_UploadtoSavix
SELECT cast(ERROR_NUMBER() as nvarchar), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID, getdate()
END CATCH
FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
END
CLOSE projects1
DEALLOCATE projects1
SELECT 1 as success
这是我试图做的，卡在中间和不完整的地方:
Here is what I've tried to do and stuck in middle and incomplete:
select s.ID,u.*
from T_PROJECTS_TEMP pt
INNER JOIN
(SELECT mgd.Gd_ID, mg.Grp_ID, mgd.Gd_CollectionDate, mgd.Gd_IsDeleted, mgd.Gd_CreateDate, mg.Grp_Project, mg.Grp_IsDeleted , mg.Grp_Legacy_ID, p.LegacyProjectId
FROM Savix_Service_Group..Group_Data mgd
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_ID = mgc.Gc_GroupID
inner join SG_Dynamic_Forms..v_projects p ON p.ProjectID = mg.Grp_Project
WHERE LegacyProjectId = 5047 AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1 ) dataset on pt.ID = dataset.LegacyProjectId
INNER JOIN T_PERIODS s ON DATEDIFF(DAY,dataset.Gd_CollectionDate,dbo.fn_GetEndOfPeriod(s.ID)) >= 0
LEFT JOIN T_UPLOADS u ON u.project_savix_ID = pt.ID AND u.PERIOD_ID = s.ID AND u.STATUS = 3 --AND u.UPLOADFILENAME != 'Automatic upload from web MIS'
WHERE NOT EXISTS (SELECT * FROM Savix_Service_Group..Group_Data mgd
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
WHERE mgc.Gc_GroupID = Grp_ID
AND DATEDIFF(dd,mgd.Gd_CollectionDate,(select dbo.fn_GetEndOfPeriod(s.ID))) >= 0
AND (mgd.Gd_CollectionDate > Gd_CollectionDate) AND mgd.Gd_IsDeleted != 1 )
AND NOT EXISTS (SELECT ID FROM T_UPLOADS u
WHERE u.project_savix_ID = pt.ID AND u.PERIOD_ID = s.ID AND u.STATUS = 3 AND UPLOADFILENAME != 'Automatic upload from web MIS')
ORDER BY s.ID
样本数据
T_Project和数据集的表和数据- http://www.sqlfiddle.com/#！18/00205/2
T_Periods的表和数据- http://www.sqlfiddle.com/# ！18/3b6b1/2
Tables and data for T_Periods - http://www.sqlfiddle.com/#!18/3b6b1/2
用于T_upload的表和数据(在游标运行之前)- http://www .sqlfiddle.com/#！18/7d12f3/1
Tables and data for T_uploads (before the cursor running)- http://www.sqlfiddle.com/#!18/7d12f3/1
其他表格数据1- http://www.sqlfiddle.com/#!18/6e499
其他表格数据2- http://www.sqlfiddle.com/#！ 18/30e3b/3
函数fn_getEndOFperiod- http://www.sqlfiddle.com/#!18/54d66
function fn_getEndOFperiod - http://www.sqlfiddle.com/#!18/54d66
请注意-上面任何一张表中都有多于一行，但是我给出的示例只有一条记录.
PLease note - there will be more than one row in any of the above tables, however the example I had given will have just one record.
T_uploads表- http://sqlfiddle.com/#!18/027cc/1
T_uploads table - http://sqlfiddle.com/#!18/027cc/1
T_ProjectsGroupData还将总共有15条记录，即每个T_uploads.ID(与T_ProjectsGroupData .UploadID字段相关)在T_ProjectsGroupData中都有一个条目
T_ProjectsGroupData will have total of 15 records aswell i.e for every T_uploads.ID (which is relation to T_ProjectsGroupData .UploadID field) there will be a entry in T_ProjectsGroupData
除了将基于CURSOR的代码转换为基于SET的简单方法外，任何改进此代码的帮助和建议都将是可观的.
I except this CURSOR based code to be converted in to a simple SET based approach, any help and suggestion to improve this code will be much much appreciable.
推荐答案
INSERT INTO T_PROJECTGROUPSDATA (uploadID, fieldA,fieldB,......fieldN )
SELECT t.ID,p.fieldA,mg.fieldB,......mgc.fieldN
FROM T_Projects mp
INNER JOIN T_UPLOADS t mp.ID = t.project_savix_ID
INNER JOIN SG_Dynamic_Forms..v_projects p ON p.LegacyProjectId = mp.ID
INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_Project = p.ProjectID
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_GroupID = mg.Grp_ID
INNER JOIN Savix_Service_Group..Group_Data mgd ON mgd.Gd_CycleID = mgc.Gc_ID
LEFT JOIN Savix_Service_Trainers..Trainers me ON me.Tr_ID = mgc.Gc_MonitoredBy
LEFT JOIN Savix_Service_Dictionaries..Dictionary mgt ON mgt.Dny_ID = me.Tr_Type
WHERE mp.SyncMode = 1
AND t.[STATUS] = 3 AND t.UPLOADFILENAME = 'Automatic upload from web MIS'
AND mg.Grp_IsDeleted = 0 AND mgd.Gd_IsDeleted != 1
UPDATE t SET
TOTALEXPENDITURES = CASE WHEN DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,mp.EndDate) != 0
THEN p.Cost_Until_Today*DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,dbo.fn_GetEndOfPeriod(t.Period_ID))/DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,mp.EndDate)/dbo.fn_RateAtDate(p.PROJECTCURRENCY_ID,dbo.fn_GetEndOfPeriod(t.Period_ID))
ELSE 0
END,
TotalExpendituresNative = CASE WHEN DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,mp.EndDate) != 0
THEN p.Cost_Until_Today*DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,dbo.fn_GetEndOfPeriod(t.Period_ID))/DATEDIFF(d,mp.PROJECTESTABLISHEDDATE,mp.EndDate)
ELSE 0
END
FROM T_UPLOADS t
JOIN T_Projects mp ON mp.ID = t.project_savix_ID AND mp.SyncMode = 1
WHERE t.[STATUS] = 3 AND t.UPLOADFILENAME = 'Automatic upload from web MIS'
为解释我是如何提出的，这是下面代码的修改版本.我不能保证我得到了一切，我必须做一些假设.缺少示例数据，所有的savix表都丢失了.给定信息，这是我能提供的最佳答案，并将我的时间限制在少于4个小时.我可以做更多的事情，但除了互联网成名之外，您还必须给我更多.
To explain how I came up with this, here is a modified version of the code below. I cannot guarantee I got everything, I had to make some assumptions. There was a lacking of sample data and all the savix tables are missing. This is the best answer I can come up with given the information and limiting my time to less than 4 hours invested. I could do much more but you would have to give me more than internet fame.
--ALTER PROCEDURE [dbo].[proc_Upload] WITH RECOMPILE
--as
set NoCount on
DECLARE
@StartTime datetime,
@EndTime datetime,
@DataID uniqueidentifier,
@CollectionDate datetime,
@Status int,
@PeriodID int,
@EndDate datetime,
@GroupID uniqueidentifier,
@ProjectID INT,
@FAID uniqueidentifier,
@UploadID int ,
@Createdate datetime,
@MINIDprojects INT,
@MAXIDprojects INT,
@MINIDdatasets INT,
@MAXIDdatasets INT,
@MINIDperiods INT,
@MAXIDperiods INT
工作和测试游标很困难，因此我用包含身份的临时表替换了它们(项目，projects1，数据集和期间)，并遍历了它们.
It is difficult to work and test cursors, so I replaced them ( projects, projects1, datasets and period ) with temp tables containing an identity and looped through them.
IF OBJECT_ID('tempdb..#projects') IS NOT NULL DROP TABLE #projects
IF OBJECT_ID('tempdb..#projects1') IS NOT NULL DROP TABLE #projects1
IF OBJECT_ID('tempdb..#datasets') IS NOT NULL DROP TABLE #datasets
IF OBJECT_ID('tempdb..#period') IS NOT NULL DROP TABLE #period
CREATE TABLE #projects
(
[ProjectID] [INT],
[Title] [varchar](255) ,
[currency] [int] ,
[Cost_Until_Today] [float] ,
[StartDate] [datetime] ,
[EndDate] [datetime] ,
[MisID] [uniqueidentifier] ,
[SystemStatus] [int] ,
[FacilitatingAgency] [uniqueidentifier] ,
[SyncMode] [int]
)
CREATE TABLE #projects1
(
ID INT IDENTITY(1,1),
[ProjectID] [INT],
[FacilitatingAgency] [uniqueidentifier]
)
CREATE TABLE #datasets
(
ID INT IDENTITY(1,1),
Gd_ID [uniqueidentifier],
Grp_ID [uniqueidentifier],
Gd_CollectionDate DATETIME,
Gd_IsDeleted BIT,
Gd_CreateDate DATETIME
)
CREATE TABLE #period
(
ID INT IDENTITY(1,1),
IDPeriod INT,
EndDate DATETIME
)
INSERT #projects ( [ProjectID], [Title], {currency], [Cost_Until_Today], [StartDate], [EndDate], [MisID], [SystemStatus], [FacilitatingAgency], [SyncMode] )
SELECT ID, PROJECTNAME, PROJECTCURRENCY_ID, Cost_Until_Today, PROJECTESTABLISHEDDATE, EndDate, MisID, 4, FacilitatingAgency, SyncMode
FROM [dbo].[T_PROJECTS] /*thsi is the source table where every projectIDs need to be processed*/
Projects包含来自T_Projects的所有ID(projectID)，此表中使用的唯一字段是ID，projectcurrency，projectbuilteddate(startdate)，enddate和syncmode(必须为= 1，我们稍后会看到).我们可以忽略systemstatus，因为它是一个常量.
Projects contains all ID's ( projectID ) from T_Projects, the only fields that are used from this table are the ID, projectcurrency, projectestablisheddate ( startdate ), enddate and syncmode ( must be = 1, we will see that later ). We can ignore systemstatus as it is a constant.
表dastasets放在这里并加载.我们只关心Savix_Service_Group..Group_Data表中的gd_id，但仍需要联接其他表，以防它们过滤掉不需要的值.插入记录时，将在下面再次使用此逻辑.
The table dastasets is dropped and loaded here. We only care about the gd_id from the Savix_Service_Group..Group_Data table but still need to join to the other tables in case that they filter out values we don't want. This logic is used again down below when inserting records.
IF exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].datasets]') and objectproperty(id, N'IsTable') = 1 ) DROP Table [dbo].datasets
SELECT mgd.Gd_ID, mg.Grp_ID, mgd.Gd_CollectionDate, mgd.Gd_IsDeleted, mgd.Gd_CreateDate, mg.Grp_Project, mg.Grp_IsDeleted , mg.Grp_Legacy_ID, p.LegacyProjectId
INTO datasets
FROM Savix_Service_Group..Group_Data mgd
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
INNER JOIN Savix_Service_Group..Groups mg ON mg.Grp_ID = mgc.Gc_GroupID
inner join SG_Dynamic_Forms..v_projects p ON p.ProjectID = mg.Grp_Project
--DECLARE projects1 CURSOR LOCAL FOR Select distinct ProjectID, isnull([FacilitatingAgency], '00000000-0000-0000-0000-000000000000') from @projects P WHERE P.SystemStatus = 4 AND P.SyncMode = 1
--/*First cursor - fetch the cursor from ProjectaTable*/
--OPEN projects1
--FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
--WHILE @@FETCH_STATUS = 0
在这里，我们要在project1中插入一个不同的项目ID值.如果没有更多数据，我的假设是该ID在T_projects表中是唯一的，因此除了通过SyncMode = 1进行过滤外，此步骤在某种程度上是不必要的.请记住，SystemStatus是一个常量，我们在其中填充了值'4'，条件可以删除而没有效果.
Here we are inserting into projects1 a distinct value of project id's. Without more data, my assumption is that the ID is distinct in the T_projects table and therefore this step is somewhat unnecessary aside from filtering out by SyncMode = 1. Remember that SystemStatus is a constant and we populated it with the value '4', this criteria could be removed with no effect.
INSERT INTO #projects1 ( ProjectID, FacilitatingAgency )
SELECT DISTINCT ProjectID, isnull([FacilitatingAgency], '00000000-0000-0000-0000-000000000000')
FROM #projects p
WHERE SystemStatus = 4 AND SyncMode = 1
SELECT @MINIDprojects = MIN(ID), @MAXIDprojects = MAX(ID) FROM #projects1
WHILE @MINIDprojects < @MAXIDprojects + 1
BEGIN
-- BEGIN TRY
-- BEGIN TRAN
SELECT @ProjectID = ProjectID, @FAID = FacilitatingAgency FROM #projects1 WHERE ID = @MINIDprojects
--DELETE FROM T_PROJECTGROUPSDATA WHERE T_PROJECTGROUPSDATA.UPLOAD_ID IN (SELECT ID FROM T_UPLOADS WHERE project_savix_ID = @ProjectID AND UPLOADFILENAME = 'Automatic upload from web MIS')
--DECLARE datasets CURSOR LOCAL FAST_FORWARD FOR SELECT Gd_ID, Grp_ID, Gd_CollectionDate, Gd_IsDeleted, Gd_CreateDate
-- FROM datasets
-- WHERE LegacyProjectId = @ProjectID AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1
-- /*Second cursor - this will get the 'collectionDate'field from datasetsTable for every project fetched in above cursor and also get @dataID which is used to insert value in to other table-T_PROJECTGROUPSDATA*/
--OPEN datasets
--FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
--WHILE @@FETCH_STATUS = 0
在这里，我们通过T_Project.ID和Savix_Service_Group..Groups.Grp_IsDeleted = 0 AND Savix_Service_Group..Group_Data.Gd_IsDeleted！= 1过滤插入#datasets
Here we insert into #datasets filtering by T_Project.ID AND Savix_Service_Group..Groups.Grp_IsDeleted = 0 AND Savix_Service_Group..Group_Data.Gd_IsDeleted != 1
INSERT INTO #datasets ( Gd_ID, Grp_ID, Gd_CollectionDate, Gd_IsDeleted, Gd_CreateDate )
SELECT Gd_ID, Grp_ID, Gd_CollectionDate, Gd_IsDeleted, Gd_CreateDate FROM datasets WHERE LegacyProjectId = @ProjectID AND Grp_IsDeleted = 0 AND Gd_IsDeleted != 1
SELECT @MINIDdatasets = MIN(ID), @MAXIDdatasets = MAX(ID) FROM #datasets
WHILE @MINIDdatasets < @MAXIDdatasets + 1
BEGIN
SELECT @DataID = Gd_ID, @GroupID = Grp_ID, @CollectionDate = Gd_CollectionDate, @Status = Gd_IsDeleted, @Createdate = Gd_CreateDate FROM #datasets WHERE ID = @MINIDdatasets
--DECLARE period CURSOR LOCAL FAST_FORWARD FOR SELECT ID, dbo.fn_GetEndOfPeriod(ID) FROM T_PERIODS
--/* dbo.fn_GetEndOfPeriod(ID) - this function will give the end of the date of that specifc quarter for any given date*/
-- WHERE DATEDIFF(dd,@CollectionDate,dbo.fn_GetEndOfPeriod(ID)) >= 0
-- ORDER BY [YEAR],[Quarter]
-- /*Third Cursor - this will process the records from another table called period with above fetched @collectionDate*/
--OPEN period
--FETCH NEXT FROM period INTO @PeriodID, @EndDate
--WHILE @@FETCH_STATUS = 0
这可能是这里最差的游标用法.我们加载所有期间并在其中循环.最后，我们只关心T_UPLOADS表中的时间段.
This is probably the worst use of a cursor here. We load all the periods and cycle through them. In the end we only care about periods that are in the T_UPLOADS table.
INSERT INTO #period ( IDPeriod, EndDate ) SELECT ID, dbo.fn_GetEndOfPeriod(ID) FROM T_PERIODS WHERE DATEDIFF(dd,@CollectionDate,dbo.fn_GetEndOfPeriod(ID)) >= 0
SELECT @MINIDperiods = MIN(ID), @MAXIDperiods = MAX(ID) FROM #period
WHILE @MINIDperiods < @MAXIDperiods + 1
BEGIN
--IF EXISTS (SELECT * FROM Savix_Service_Group..Group_Data mgd
-- INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_ID = mgd.Gd_CycleID
-- WHERE mgc.Gc_GroupID = @GroupID
-- AND DATEDIFF(dd,mgd.Gd_CollectionDate,@EndDate) >= 0
-- AND (mgd.Gd_CollectionDate > @CollectionDate )
-- AND mgd.Gd_IsDeleted != 1)
--BEGIN
-- BREAK
--END
在这里，我们跳过UPLOADFILENAME！='从Web MIS自动上传'的所有时间段
Here we skip any periods where UPLOADFILENAME != 'Automatic upload from web MIS'
--IF EXISTS (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.STATUS = 3 AND UPLOADFILENAME != 'Automatic upload from web MIS')
--BEGIN
-- FETCH NEXT FROM period INTO @PeriodID, @EndDate
-- CONTINUE
--END
请记住，我们只关心status = 3和UPLOADFILENAME ='从Web MIS自动上传'的地方
With that in mind we only care where status = 3 and UPLOADFILENAME = 'Automatic upload from web MIS'
SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.[STATUS] = 3 AND UPLOADFILENAME = 'Automatic upload from web MIS')
/*If T_uploads doesn't have appropirate period ID from cursor fetch then create a new entry in T_uploads with current projectID*/
IF @UploadID IS NOT NULL
BEGIN
--declare @Project_ID_Legacy int = ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID)
为什么他们使用第二个表(T_UPLOADFIRSTSTEP)来生成一条记录，在该记录中，他们仅使用id，然后在创建T_Uploads记录时使用该ID删除记录，这超出了我的理解，在我看来是可怕的编码.
Why they use a second table ( T_UPLOADFIRSTSTEP ) to generate a record where they only use the id and then delete the record using that ID in the creation of the T_Uploads record is beyond my understanding and appears to me to be terrible coding.
-- INSERT INTO T_UPLOADSFIRSTSTEP
-- (PROJECT_ID
-- --,UPLOADDATE
-- --,UPLOADFILENAME
-- --,UPLOADUSER_ID
-- --,CURRENTSTEP
-- ,[STATUS]
-- ,Project_ID_MIS)
-- SELECT ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID),
-- --GETDATE(),
-- --'Automatic upload from web MIS',
-- --2,
-- --2,
-- 0,
-- @ProjectID
插入T_UPLOADS总是会失败，因为有些字段不能为null，并且不在插入列表中，也没有为其分配默认值.
The insert into T_UPLOADS will always fail as there are fields that can not be null that are not in the insert list and do not have a default value assigned to them.
-- INSERT INTO T_UPLOADS ( ID, periodID, projectID,UPLOADDATE,UPLOADFILENAME,UPLOADUSER_ID )
-- SELECT uf.ID,
-- @PeriodID,
-- ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID),
-- GETDATE(),
-- 'Automatic upload from web MIS',
-- 2
-- FROM T_UPLOADSFIRSTSTEP uf
-- INNER JOIN #projects mp ON uf.Project_ID_MIS = mp.ProjectID
-- WHERE uf.Project_ID_MIS = @ProjectID AND uf.[STATUS] = 0
-- AND NOT EXISTS (SELECT * FROM T_UPLOADS u WHERE u.PROJECT_ID = uf.PROJECT_ID AND u.PERIOD_ID = @PeriodID AND u.[STATUS] = 3)
-- DELETE FROM T_UPLOADSFIRSTSTEP WHERE STATUS = 0 AND PROJECT_ID = ISNULL((select distinct PROJECT_ID from T_UPLOADS where project_savix_ID = @ProjectID),@ProjectID)
-- --SET @UploadID = (SELECT ID FROM T_UPLOADS u WHERE u.project_savix_ID = @ProjectID AND u.PERIOD_ID = @PeriodID AND u.[STATUS] = 3)
-- END
--ELSE
因此，这是在这3个游标内完成的真实逻辑.知道我们不在乎周期，因此逻辑会遍历所有周期.我们只关心我们必须过滤某些条件(状态= 3和UPLOADFILENAME ='从Web MIS自动上传')，并且它们具有匹配的T_Project.ID表，其中条件T_Projects.SyncMode = 1
So here is the real logic that is done inside those 3 cursors. Understanding that we don't care about periods, the logic loops through them all. We only care that we have to filter certain criteria ( status = 3 and UPLOADFILENAME = 'Automatic upload from web MIS' ) and that they have a matching T_Project.ID table with the criteria T_Projects.SyncMode = 1
UPDATE t SET
TOTALEXPENDITURES = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(t.Period_ID))/DATEDIFF(d,mp.StartDate,mp.EndDate)/dbo.fn_RateAtDate(mp.Currency,dbo.fn_GetEndOfPeriod(t.Period_ID))
ELSE 0
END,
TotalExpendituresNative = CASE WHEN DATEDIFF(d,mp.StartDate,mp.EndDate) != 0
THEN mp.Cost_Until_Today*DATEDIFF(d,mp.StartDate,dbo.fn_GetEndOfPeriod(t.Period_ID))/DATEDIFF(d,mp.StartDate,mp.EndDate)
ELSE 0
END
FROM T_UPLOADS t
JOIN #projects mp ON mp.ProjectID = t.project_savix_ID
WHERE 1=1 -- t.ID = @UploadID
--AND t.project_savix_ID = @ProjectID AND t.PERIOD_ID = @PeriodID
AND t.[STATUS] = 3 AND t.UPLOADFILENAME = 'Automatic upload from web MIS'
插入T_PROJECTGROUPDATA已复制了用于创建从#datasets派生的@dataid变量的逻辑.这是从我们在上面删除并创建的数据集表派生的.
The insert into T_PROJECTGROUPDATA has duplicated the logic used to create the @dataid variable derived from #datasets. Which is derived from the datasets table we dropped and created above.
INSERT INTO T_PROJECTGROUPSDATA (uploadID, fieldA,fieldB,......fieldN )
SELECT @UploadID,p.fieldA,mg.fieldB,......mgc.fieldN
FROM #projects mp
inner join SG_Dynamic_Forms..v_projects p ON p.LegacyProjectId = mp.projectID
inner join Savix_Service_Group..Groups mg ON mg.Grp_Project = p.ProjectID
INNER JOIN Savix_Service_Group..Group_Cycle mgc ON mgc.Gc_GroupID = mg.Grp_ID
INNER JOIN Savix_Service_Group..Group_Data mgd ON mgd.Gd_CycleID = mgc.Gc_ID
LEFT JOIN Savix_Service_Trainers..Trainers me ON me.Tr_ID = mgc.Gc_MonitoredBy
LEFT JOIN Savix_Service_Dictionaries..Dictionary mgt ON mgt.Dny_ID = me.Tr_Type
--left join v1_Report_UDF_Data_UploadToSavix udf on udf.DataID = mgd.Gd_ID
WHERE mgd.Gd_ID = @DataID
--FETCH NEXT FROM period INTO @PeriodID, @EndDate
SET @MINIDperiods = @MINIDperiods + 1
END
--CLOSE period
--DEALLOCATE period
--FETCH NEXT FROM datasets INTO @DataID, @GroupID, @CollectionDate, @Status, @Createdate
SET @MINIDdatasets = @MINIDdatasets + 1
END
--CLOSE datasets
--DEALLOCATE datasets
--COMMIT
--END TRY
--BEGIN CATCH
--SELECT ERROR_NUMBER(), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID
--IF CURSOR_STATUS('global' , 'period') >= 0
--BEGIN
-- CLOSE period
-- DEALLOCATE uploadID
--END
--IF CURSOR_STATUS('global' , 'datasets') >= 0
--BEGIN
-- CLOSE datasets
-- DEALLOCATE datasets
--END
--IF @@TRANCOUNT > 0
-- ROLLBACK
--INSERT INTO error_catch_UploadtoSavix
--SELECT cast(ERROR_NUMBER() as nvarchar), ERROR_MESSAGE(),@PeriodID, @ProjectID, @UploadID,@DataID, getdate()
--END CATCH
SET @MINIDprojects = @MINIDprojects + 1
--FETCH NEXT FROM projects1 INTO @ProjectID, @FAID
END
--CLOSE projects1
--DEALLOCATE projects1
--SELECT 1 as success
那里有.将近300行和3个光标压缩到30行且没有光标.
There you have it. Condensing nearly 300 lines and 3 cursors down to 30 lines and no cursors.
这篇关于如何将基于CURSOR的查询转换为基于SET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
