SQL Server 游标的使用示例

简介

本文主要记录 MSSQL 中的游标使用示例，在有必要时方便借鉴查阅。游标一般定义在某段功能性的 SQL 语句中，或者存储过程中。之所以选择用它，是因为有时候无法使用简单的 SQL 语句满足我们需求，比如需要对结果集中的每一条数据，根据不同条件进行不同操作（CRUD），这时我们就可以使用游标来完成。

提示：来之 DBA 的杰作，哈哈~~

 

1.   示例1：

本示例，用于初始化某新表的数据。使用游标遍历查询结果集，根据遍历的数据再插入另外两张表，SQL 代码如下：

IF(OBJECT_ID(‘SP_Init_CustomerNewOpening‘, ‘P‘) IS NOT NULL)

    DROP PROCEDURE SP_Init_CustomerNewOpening;

GO

CREATE PROCEDURE SP_Init_CustomerNewOpening

AS

    INSERT INTO Sys_DbLog(LogTime, Type, Item, Message, Remark) VALUES(GETDATE(), ‘info‘, ‘SP_Init_CustomerNewOpening‘, ‘存储过程执行开始‘, NULL);

    DECLARE @PayTime datetime, @Address nvarchar(500), @CustomerId bigint, @EmplCityId int, @EmployeeId int, @RealTotal decimal(18,2);

    DECLARE cursor_CustomerNewOpening CURSOR GLOBAL SCROLL DYNAMIC SCROLL_LOCKS

    FOR SELECT PayTime, Address, CustomerId, EmplCityId, EmployeeId, SUM(RealTotal) AS RealTotal FROM

    (

        SELECT T1.PayTime, T1.Address, T1.CustomerId, T1.EmplCityId, T1.EmployeeId, T2.RealTotal FROM

        (

            SELECT T2.PayTime, T2.Address, T1.Id AS CustomerId, T3.CityId AS EmplCityId, T3.EmployeeId FROM Customer AS T1

            INNER JOIN Orders AS T2 ON(T1.BdBonus = T2.Id)

            INNER JOIN Sys_EmployeeProfile AS T3 ON(T2.SalesUserId = T3.EmployeeId)

            WHERE T1.BdBonus > 0

        ) AS T1

        INNER JOIN Orders AS T2 ON(T1.PayTime = T2.PayTime AND T1.Address = T2.Address)

        WHERE 1=1

    ) AS T WHERE 1=1

    GROUP BY PayTime, Address, CustomerId, EmplCityId, EmployeeId;

 

    BEGIN TRY

        BEGIN TRANSACTION;

 

        DECLARE @RewardsMoney decimal(18,2) = CAST(ISNULL((SELECT Value FROM Config WHERE Id=79), ‘0‘) AS decimal(18,2));

        DECLARE @NewOpenMoney decimal(18,2) = 0;

        DECLARE @CustNewOpenId int;

 

        OPEN cursor_CustomerNewOpening;

        FETCH FIRST FROM cursor_CustomerNewOpening INTO @PayTime, @Address, @CustomerId, @EmplCityId, @EmployeeId, @RealTotal;

        WHILE(@@FETCH_STATUS = 0)

        BEGIN

            --插入客户新开

            SET @NewOpenMoney = CASE WHEN @RealTotal > 999 THEN 999 WHEN @RealTotal > 699 THEN 699 ELSE @RealTotal END;

            INSERT INTO Crm_CustomerNewOpening(CustomerId, EmplCityId, EmployeeId, RewardsMoney, NewOpenMoney, RealTotal,

                RefundmentMoney, PayTime, NewOpenStatus, AuditorId, AuditorTime, AuditDesc, CreateTime, UpdateTime)

            VALUES(@CustomerId, @EmplCityId, @EmployeeId, @RewardsMoney, @NewOpenMoney, @RealTotal,

                0, @PayTime, 4/*历史有效*/, NULL, NULL, NULL, GETDATE(), NULL);

            SET @CustNewOpenId = SCOPE_IDENTITY();

           

            --插入订单新开

            INSERT INTO Crm_OrderNewOpening(CustNewOpenId, OrderId, OrderNum, RealTotal, RefundmentMoney, CreateTime, UpdateTime)

            SELECT 0, T1.Id, T1.OrderNum, T1.RealTotal, 0, GETDATE(), NULL FROM Orders AS T1

            WHERE 1=1

            AND T1.PayTime = @PayTime

            AND T1.Address = @Address;

 

            FETCH NEXT FROM cursor_CustomerNewOpening INTO @PayTime, @Address, @CustomerId, @EmplCityId, @EmployeeId, @RealTotal;

        END

        CLOSE cursor_CustomerNewOpening;

        DEALLOCATE cursor_CustomerNewOpening;

        COMMIT;

        INSERT INTO Sys_DbLog(LogTime, Type, Item, Message, Remark) VALUES(GETDATE(), ‘info‘, ‘SP_Init_CustomerNewOpening‘, ‘存储过程执行结束‘, NULL);

    END TRY

    BEGIN CATCH

        ROLLBACK;

        DECLARE @Message varchar(8000) = ‘错误：行号[‘ + CAST(ERROR_LINE() AS nvarchar(20)) + ‘]，‘ + ERROR_MESSAGE();

        INSERT INTO Sys_DbLog(LogTime, Type, Item, Message, Remark) VALUES(GETDATE(), ‘error‘, ‘SP_Init_CustomerNewOpening‘, @Message, ‘事物已回滚‘);

        CLOSE cursor_CustomerNewOpening;

        DEALLOCATE cursor_CustomerNewOpening;

    END CATCH

GO

 

2.   示例2：

本示例，创建了一个存储过程，使用游标完成更新遍历的记录，再使用遍历的数据完成对另外两张表的 Insert 操作。该存储过程用于定时任务执行，比在程序中去查询数据，然后再遍历写入数据效率上还是比较有优势的，而且便于维护。SQL 代码如下：

IF(OBJECT_ID(‘SP_Add_CustomerNewOpening‘, ‘P‘) IS NOT NULL)

    DROP PROCEDURE SP_Add_CustomerNewOpening;

GO

CREATE PROCEDURE SP_Add_CustomerNewOpening

(

    @StartTime datetime,                --开始时间

    @EndTime datetime                   --结束时间

)

AS

    INSERT INTO Sys_DbLog(LogTime,