SQL Server游标的使用案例

SELECT * INTO backup_db.dbo.EgSys_ActualFreightDetail_202208251630 FROM EgSys_ActualFreightDetail (NOLOCK);

BEGIN

DECLARE @DetailID INT;
DECLARE @PackageFee VARCHAR(50);

DECLARE MyCursor CURSOR FOR (
    SELECT DetailID,PackageFee FROM EgSys_ActualFreightDetail (NOLOCK) WHERE PackageFee LIKE '%:0,%'
);

OPEN MyCursor;

FETCH NEXT FROM MyCursor INTO @DetailID, @PackageFee;

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @DetailID AS DetailID,CONVERT(VARCHAR, SUBSTRING(@PackageFee, CHARINDEX('0,', @PackageFee) + 2, 50)) AS PackageFee INTO #Temp;

    UPDATE d SET d.PackageFee = #Temp.PackageFee FROM EgSys_ActualFreightDetail (NOLOCK) d INNER JOIN #Temp ON #Temp.DetailID = d.detailID;

    DROP TABLE #Temp;

    FETCH NEXT FROM MyCursor INTO @DetailID, @PackageFee;
END;

CLOSE MyCursor;

DEALLOCATE MyCursor;

END;