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);
??? 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
轉載于:https://www.cnblogs.com/abeam/p/9929203.html
總結
以上是生活随笔為你收集整理的SQL Server 游标的使用示例的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Echarts之悬浮框中的数据排序
- 下一篇： 【*2000】【2018-2019 IC