DECLARE 游标名 CURSOR
FOR 游标要实现的功能程序
[
    FOR {
        READ ONLY   -- 仅查询 
        | UPDATE    -- 可更新 
        [OF 列名列表] -- 如果有这个, 表示只能针对指定列更新
    }
]

OPEN 游标名

FETCH 游标名 INTO @变量名

-- 关闭游标
CLOSE 游标名

-- 释放游标
DEALLOCATE 游标名

-- 删除游标集中的当前行
DELETE FROM 表名
WHERE CURRENT OF 游标名

-- 修改游标集中的当前行
UPDATE 表名
SET 列名1 = 值1, ...
WHERE CURRENT OF 游标名

DECLARE @变量1, ...
DECLARE 游标名 CURSOR FOR
    SELECT ....

OPEN 游标名
FETCH 游标名 INTO @变量名1, ...

WHILE (@@FETCH_STATUS=0)
BEGIN
    一些操作
    FETCH 游标名 INTO @变量名1, ...
END

CLOSE 游标名
DEALLOCATE 游标名

-- 创建示例表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    Status VARCHAR(20) DEFAULT 'Pending',
    TotalAmount DECIMAL(10, 2) DEFAULT 0
);

CREATE TABLE OrderItems (
    ItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

CREATE TABLE OrderProcessingLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    ProcessDate DATETIME,
    Action VARCHAR(100),
    OldStatus VARCHAR(20),
    NewStatus VARCHAR(20)
);

-- 插入示例数据
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(1, 101, '2023-01-01'),
(2, 102, '2023-01-02'),
(3, 103, '2023-01-03');

INSERT INTO OrderItems VALUES
(1, 1, 1001, 2, 25.50),
(2, 1, 1002, 1, 120.00),
(3, 2, 1003, 3, 15.75),
(4, 2, 1004, 1, 200.00),
(5, 3, 1005, 5, 10.25),
(6, 3, 1006, 2, 45.00);

-- 声明变量
DECLARE @OrderID INT;
DECLARE @CustomerID INT;
DECLARE @OrderDate DATETIME;
DECLARE @OldStatus VARCHAR(20);
DECLARE @TotalAmount DECIMAL(10, 2);
DECLARE @ProcessedCount INT = 0;
DECLARE @ErrorCount INT = 0;

-- 声明游标
DECLARE OrderCursor CURSOR FOR
    SELECT OrderID, CustomerID, OrderDate, Status
    FROM Orders
    WHERE Status = 'Pending'
    ORDER BY OrderDate;

-- 打开游标
OPEN OrderCursor;

-- 开始事务
BEGIN TRANSACTION;

-- 获取第一行数据
FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID, @OrderDate, @OldStatus;

-- 循环处理每一行
WHILE @@FETCH_STATUS = 0
BEGIN
	    BEGIN TRY
        -- 计算订单总金额
        SELECT @TotalAmount = SUM(Quantity * UnitPrice)
        FROM OrderItems
        WHERE OrderID = @OrderID;
        
        -- 更新订单状态和总金额
        UPDATE Orders
        SET Status = 'Processed',
            TotalAmount = @TotalAmount
        WHERE OrderID = @OrderID;
        
        -- 记录处理日志
        INSERT INTO OrderProcessingLog (OrderID, ProcessDate, Action, OldStatus, NewStatus)
        VALUES (@OrderID, GETDATE(), 'Order processed', @OldStatus, 'Processed');
        
        -- 增加处理计数
        SET @ProcessedCount = @ProcessedCount + 1;
        
        -- 模拟复杂逻辑：VIP客户特殊处理
        IF @CustomerID % 10 = 0 -- 假设VIP客户的CustomerID能被10整除
        BEGIN
            -- 记录VIP处理日志
            INSERT INTO OrderProcessingLog (OrderID, ProcessDate, Action, OldStatus, NewStatus)
            VALUES (@OrderID, GETDATE(), 'VIP customer processed', 'Processed', 'Processed');
            
            -- 这里可以添加VIP客户的特殊处理逻辑
            PRINT 'VIP customer order processed: OrderID = ' + CAST(@OrderID AS VARCHAR);
        END
        
        PRINT 'Order processed successfully: OrderID = ' + CAST(@OrderID AS VARCHAR);
    END TRY
    BEGIN CATCH
        -- 错误处理
        PRINT 'Error processing order: ' + CAST(@OrderID AS VARCHAR) + '. Error: ' + ERROR_MESSAGE();
        
        -- 记录错误日志
        INSERT INTO OrderProcessingLog (OrderID, ProcessDate, Action, OldStatus, NewStatus)
        VALUES (@OrderID, GETDATE(), 'Error: ' + ERROR_MESSAGE(), @OldStatus, 'Error');
        
        -- 更新订单状态为错误
        UPDATE Orders
        SET Status = 'Error'
        WHERE OrderID = @OrderID;
        
        -- 增加错误计数
        SET @ErrorCount = @ErrorCount + 1;
    END CATCH
    
    -- 获取下一行数据
    FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID, @OrderDate, @OldStatus;
END

-- 提交事务
COMMIT TRANSACTION;

-- 关闭游标
CLOSE OrderCursor;

-- 释放游标
DEALLOCATE OrderCursor;

-- 输出处理结果
PRINT 'Processing completed.';
PRINT 'Orders processed successfully: ' + CAST(@ProcessedCount AS VARCHAR);
PRINT 'Orders with errors: ' + CAST(@ErrorCount AS VARCHAR);

-- 查询处理后的结果
SELECT * FROM Orders;
SELECT * FROM OrderProcessingLog ORDER BY ProcessDate DESC;