Ø 简介
本文主要记录 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);
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;
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
--,Orders AS t2
--WHERE t1.PayTime = t2.PayTime AND t1.Address = t2.Address
) AS T WHERE 1=1
GROUP BY PayTime, Address, CustomerId, EmplCityId, EmployeeId;
OPEN cursor_CustomerNewOpening;
FETCH FIRST FROM cursor_CustomerNewOpening INTO @PayTime, @Address, @CustomerId, @EmplCityId, @EmployeeId, @RealTotal;
DECLARE @dt datetime = '2018-06-01 00:00:00';
WHILE(@@FETCH_STATUS = 0)
BEGIN
IF((@PayTime >= @dt AND @RealTotal >= 999) OR (@PayTime < @dt AND @RealTotal >= 699))
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 @CustNewOpenId, t1.Id, t1.OrderNum, t1.RealTotal, 0, GETDATE(), NULL FROM Orders AS t1
WHERE 1=1
AND t1.PayTime = @PayTime
AND t1.Address = @Address;
END
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, '事物已回滚');
END CATCH
GO
2. 示例2：
1) 本示例，创建了一个存储过程，创建了两个游标。
2) 第一个游标，使用游标完成更新遍历的记录，再使用遍历的数据完成对另外两张表的 Insert 操作。
3) 第二个游标，完成对3张表的更新操作，在示例中使用了当前游标记录作为更新条件，例如：WHERE CURRENT OF cursor_Crm_Refundment。
4) 该存储过程用于定时任务执行，比在程序中去查询数据，然后再遍历写入数据效率上还是比较有优势的，而且便于维护。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
DECLARE @Remark varchar(8000) = NULL;
INSERT INTO Sys_DbLog(LogTime, Type, Item, Message, Remark) VALUES(GETDATE(), 'info', 'SP_Add_CustomerNewOpening', '存储过程执行开始', NULL);
DECLARE @RewardsMoney decimal(18,2) = CAST(ISNULL((SELECT Value FROM Config WHERE Id=79), '0') AS decimal(18,2));
DECLARE @NewOpenMoney decimal(18,2) = CAST(ISNULL((SELECT Value FROM Config WHERE Id=80), '0') AS decimal(18,2));
DECLARE @AddCustNewOpenCount int = 0, @AddOrderNewOpenCount int = 0, @UpdateCustNewOpenCount int = 0,
@UpdateOrderNewOpenCount int = 0, @UpdateRefundmentCount int = 0;
BEGIN TRY
BEGIN TRANSACTION;
--1. 新增新开客户
DECLARE @OrderNewOpenId int, @CustNewOpenId int;
DECLARE @UserId bigint, @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 UserId, PayTime, Address, CustomerId, EmplCityId, EmployeeId, SUM(RealTotal) AS RealTotal FROM
(
SELECT t1.UserId, t1.PayTime, t1.Address, t2.CustomerId, t4.CityId AS EmplCityId, t4.EmployeeId, t1.RealTotal FROM Orders AS t1
INNER JOIN UserInfo AS t2 ON(t1.UserId = t2.Id)
INNER JOIN Customer AS t3 ON(t2.CustomerId = t3.Id)
INNER JOIN Sys_EmployeeProfile AS t4 ON(t1.SalesUserId = t4.EmployeeId)
WHERE 1=1
AND t1.UserId > 0
AND t2.CustomerId > 0
AND t1.SalesUserId > 0
AND (t1.OrderStatusId > 1 AND t1.OrderStatusId < 10)
AND (t1.PayStatusId = 2 AND t1.PayTime >= @StartTime AND t1.PayTime <= @EndTime)
AND NOT EXISTS(SELECT TOP 1 1 FROM Crm_CustomerNewOpening AS T WHERE T.CustomerId = t2.CustomerId AND (T.NewOpenStatus = 1 OR T.NewOpenStatus = 4))
AND NOT EXISTS(SELECT TOP 1 1 FROM Crm_OrderNewOpening AS T WHERE T.OrderId = t1.Id)
) AS T WHERE 1=1
AND RealTotal >= @NewOpenMoney
GROUP BY UserId, PayTime, Address, CustomerId, EmplCityId, EmployeeId;
OPEN cursor_CustomerNewOpening;
FETCH FIRST FROM cursor_CustomerNewOpening INTO @UserId, @PayTime, @Address, @CustomerId, @EmplCityId, @EmployeeId, @RealTotal;
WHILE(@@FETCH_STATUS = 0)
BEGIN
--防止本次执行多条符合的情况
IF(NOT EXISTS(SELECT TOP 1 1 FROM Crm_CustomerNewOpening AS T WHERE T.CustomerId = @CustomerId AND (T.NewOpenStatus = 1 OR T.NewOpenStatus = 4)))
BEGIN
--插入客户新开
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, 1/*当前有效*/, NULL, NULL, NULL, GETDATE(), NULL);
SELECT @CustNewOpenId = SCOPE_IDENTITY(), @AddCustNewOpenCount = (@AddCustNewOpenCount + @@ROWCOUNT);
--插入订单新开
INSERT INTO Crm_OrderNewOpening(CustNewOpenId, OrderId, OrderNum, RealTotal, RefundmentMoney, CreateTime, UpdateTime)
SELECT @CustNewOpenId, t1.Id, t1.OrderNum, t1.RealTotal, 0, GETDATE(), NULL FROM Orders AS t1
WHERE 1=1
AND t1.UserId = @UserId
AND t1.PayTime = @PayTime
AND t1.Address = @Address;
SELECT @AddOrderNewOpenCount = (@AddOrderNewOpenCount + @@ROWCOUNT);
END
FETCH NEXT FROM cursor_CustomerNewOpening INTO @UserId, @PayTime, @Address, @CustomerId, @EmplCityId, @EmployeeId, @RealTotal;
END
CLOSE cursor_CustomerNewOpening;
DEALLOCATE cursor_CustomerNewOpening;
--2. 更新客户新开（退款失效）
DECLARE @RefundmentId int, @OrderId bigint, @RefundmentMoney decimal(18,2);
DECLARE cursor_Crm_Refundment CURSOR GLOBAL SCROLL DYNAMIC SCROLL_LOCKS
FOR SELECT Id, OrderId, RefundmentMoney FROM Crm_Refundment AS t1 WHERE t1.OrderNewOpenId IS NULL
AND t1.CreateTime >= @StartTime AND t1.CreateTime <= @EndTime
FOR UPDATE OF OrderNewOpenId, UpdateTime;
OPEN cursor_Crm_Refundment;
FETCH FIRST FROM cursor_Crm_Refundment INTO @RefundmentId, @OrderId, @RefundmentMoney;
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @OrderNewOpenId = NULL, @CustNewOpenId = NULL;
SELECT @OrderNewOpenId = Id, @CustNewOpenId = CustNewOpenId FROM Crm_OrderNewOpening WHERE OrderId = @OrderId;
IF(@OrderNewOpenId IS NOT NULL AND @CustNewOpenId IS NOT NULL)
BEGIN
DECLARE @RowCount1 int = 0, @RowCount2 int = 0;
--1. 更新订单新开
UPDATE Crm_OrderNewOpening SET RefundmentMoney = (RefundmentMoney + @RefundmentMoney), UpdateTime = GETDATE()
WHERE Id = @OrderNewOpenId;
SELECT @RowCount1 = @@ROWCOUNT, @UpdateOrderNewOpenCount = (@UpdateOrderNewOpenCount + @@ROWCOUNT);
--2. 更新客户新开
UPDATE Crm_CustomerNewOpening SET RefundmentMoney = (RefundmentMoney + @RefundmentMoney),
NewOpenStatus = (CASE WHEN NewOpenStatus = 1 AND (RealTotal - (RefundmentMoney + @RefundmentMoney)) < @NewOpenMoney
THEN 2 ELSE NewOpenStatus END)/*只有【当前有效】状态才更新状态*/,
RefundmentId = (CASE WHEN NewOpenStatus = 1 AND (RealTotal - (RefundmentMoney + @RefundmentMoney)) < @NewOpenMoney
--AND RefundmentId IS NULL/*逻辑上可以省略该条件，因为。。。*/
THEN @RefundmentId ELSE RefundmentId END)/*记录导致退款失效的退款Id*/,
UpdateTime = GETDATE()
WHERE Id = @CustNewOpenId;
SELECT @RowCount2 = @@ROWCOUNT, @UpdateCustNewOpenCount = (@UpdateCustNewOpenCount + @@ROWCOUNT);
IF(@RowCount1 = 1 AND @RowCount2 = 1)
BEGIN
UPDATE Crm_Refundment SET OrderNewOpenId = @OrderNewOpenId, UpdateTime = GETDATE() WHERE CURRENT OF cursor_Crm_Refundment;
SELECT @UpdateRefundmentCount = (@UpdateRefundmentCount + @@ROWCOUNT);
END
END
FETCH NEXT FROM cursor_Crm_Refundment INTO @RefundmentId, @OrderId, @RefundmentMoney;
END
CLOSE cursor_Crm_Refundment;
DEALLOCATE cursor_Crm_Refundment;
COMMIT;
SET @Remark = '新开客户数：' + CAST(@AddCustNewOpenCount AS varchar(20))
+ '，新开订单数：' + CAST(@AddOrderNewOpenCount AS varchar(20))
+ '，更新新开客户数：' + CAST(@UpdateCustNewOpenCount AS varchar(20))
+ '，更新新开订单数：' + CAST(@UpdateOrderNewOpenCount AS varchar(20))
+ '，更新退款数：' + CAST(@UpdateRefundmentCount AS varchar(20));
INSERT INTO Sys_DbLog(LogTime, Type, Item, Message, Remark) VALUES(GETDATE(), 'info', 'SP_Add_CustomerNewOpening', '存储过程执行结束', @Remark);
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_Add_CustomerNewOpening', @Message, '事物已回滚');
END CATCH
GO