SQL Server 事务、异常与游标核心机制及实战

事务处理

事务是将多条 T-SQL 指令视为一个不可分割的逻辑单元，执行时要么全部成功提交，要么在发生错误时全部取消回滚。这种机制对于保障金融转账等关键业务的数据一致性至关重要。

第1步：理解事务ACID属性

在编写业务逻辑前，必须明确事务的四个核心特性，这是设计正确数据更新流程的基础：

• 原子性 (Atomicity)：事务内的所有操作“全有或全无”，不会出现只执行了一半的情况。
• 一致性 (Consistency)：事务执行前后，数据库必须满足所有预定义的约束和规则。
• 隔离性 (Isolation)：并发事务之间互不干扰，一个事务的状态变化只能以已提交或未提交的形式被其他事务看到，中间状态不可见。
• 持久性 (Durability)：一旦事务提交，其修改永久生效，即使随后系统断电或崩溃，数据也不会丢失。

第2步：选择事务模式

SQL Server 支持多种事务管理方式，根据场景选择合适的模式：

• 显示事务：开发者显式使用 BEGIN TRANSACTION 开始，并根据逻辑判断决定 COMMIT TRANSACTION（提交）或 ROLLBACK TRANSACTION（回滚）。
• 自动提交事务：默认模式，每条独立的 T-SQL 语句都是一个单独的事务。例如删除操作若违反外键约束，将自动回滚。
• 隐式事务：通过 SET IMPLICIT_TRANSACTIONS ON 开启后，每个语句会自动启动新事务，无需显式 BEGIN，但仍需手动 COMMIT 或 ROLLBACK。

第3步：实施事务控制与验证

在实际操作中，通常结合全局变量 @@TRANCOUNT 监控事务层级，配合条件判断实现稳健的流程控制。

1. 开始事务：使用 BEGIN TRAN 开启，系统内部计数器 @@TRANCOUNT 加 1。
2. 检查执行状态：通过 @@ERROR 跟踪每一步的返回值，或结合异常处理块判断逻辑是否通顺。
3. 提交或回滚：若无错误则执行 COMMIT，使变更永久化；若有错误则执行 ROLLBACK，将数据恢复到事务开始前的状态。

第4步：构建安全的事务示例

以下是一个典型的银行转账场景，利用 TRY...CATCH 包装事务，确保金额扣减与增加要么同时成功，要么同时失败。

begin transaction tran_bank;
declare @tran_error int;
    set @tran_error = 0;
    begin try
        -- Jack 账户扣款
        update bank set totalMoney = totalMoney - 10000 where userName = 'jack';        
        set @tran_error = @tran_error + @@error;
        -- Jason 账户收款
        update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
        set @tran_error = @tran_error + @@error;
    end try
    begin catch        
        print '出现异常，错误编号：' + convert(varchar, error_number()) + '， 错误消息：' + error_message(); 
        set @tran_error = @tran_error + 1;
    end catch
if (@tran_error > 0)
    begin
        -- 只要任一环节出错，整体回滚
        rollback tran;
        print '转账失败，交易已取消';
    end
else
    begin
        commit tran;
        print '转账成功';
    end
go

异常处理机制

当 T-SQL 执行过程中遇到错误或异常情况时，直接使用 TRY...CATCH 构造可以拦截错误并执行自定义的错误处理逻辑，避免批处理立即终止。

第1步：掌握TRY...CATCH结构

TRY 块包含可能出错的 SQL 语句，CATCH 块紧跟其后用于处理错误。如果 TRY 中检测到错误，控制权直接转移至 CATCH 块。需要注意的是，TRY...CATCH 无法捕获编译级别的语法错误（如关键字拼写错误）或对象名解析错误的情况（除非这些错误发生在存储过程内部且被嵌套调用）。

第2步：使用系统函数诊断错误

在 CATCH 块中，可以使用特定的内置函数获取详细的错误上下文信息，以便记录日志或返回给用户：

• ERROR_NUMBER()：返回具体的错误编号。
• ERROR_MESSAGE()：返回完整的错误描述文本。
• ERROR_SEVERITY()：获取严重性级别，数字越大越严重。
• ERROR_LINE()：指出发生错误的具体代码行号。
• ERROR_PROCEDURE()：返回触发错误的存储过程或触发器名称。

第3步：创建错误日志与记录

为了追踪生产环境的问题，建议建立错误日志表，并在异常发生时自动写入详细信息。

首先创建存储日志信息的表和用于记录的存储过程：

-- 定义日志表结构
if (object_id('errorLog', 'U') is not null)
    drop table errorLog
go
create table errorLog(
    errorLogID int primary key identity(100, 1),
    errorTime datetime default getDate(),
    userName sysname default current_user,
    errorNumber int,
    errorSeverity int,
    errorState int,
    errorProcedure nvarchar(126),
    errorLine int,
    errorMessage nvarchar(4000)
)
go

-- 封装写入日志的存储过程
if (object_id('proc_add_exception_log', 'p') is not null)
    drop proc proc_add_exception_log
go
create proc proc_add_exception_log(@logId int = 0 output)
as
begin
    set nocount on;
    set @logId = 0;
    begin try
        if (error_number() is null) return;
        
        -- 插入错误详情
        insert into errorLog values(getDate(), current_user, 
            error_number(), error_severity(), error_state(), 
            error_procedure(), error_line(), error_message());
        select @logId = @@identity;
    end try
    begin catch
        exec proc_error_info;
        return -1;
    end catch
end
go

第4步：在业务中集成异常处理

在实际执行存在风险的操作（如级联删除）时，结合 xact_state() 判断事务当前的有效性，灵活决定是回滚还是提交：

declare @id int;
begin try
    begin tran;
    delete classes where id = 1;
    commit tran;
end try
begin catch
    -- 1. 输出详细报错信息供调试
    exec proc_error_info;
    
    -- 2. 处理事务状态
    if (xact_state() <> 0)
    begin
        -- xact_state() 为 -1 时表示事务处于“不可提交”状态，必须完全回滚
        rollback tran;
    end
    -- 3. 记录本次异常到日志表
    exec proc_add_exception_log @id output;
end catch
select * from errorLog where errorLogID = @id;
go

游标的运用

游标是一种允许开发者从查询结果集中逐行提取数据的机制。虽然 SQL Server 提倡基于集合的操作以提升性能，但在需要逐条比对、生成复杂文本报告或对单行数据进行特殊转换时，游标依然不可替代。

第1步：定义游标及其参数

使用 DECLARE CURSOR 声明游标，关键参数决定了游标的行为模式：

• 滚动范围：LOCAL（局部有效）或 GLOBAL（全局连接内有效）；FORWARD ONLY（仅向下）或 SCROLL（可向上、向下、定位访问）。
• 并发选项：READ_ONLY（只读）；SCROLL_LOCKS（锁定读取的行以保证后续更新）；OPTIMISTIC（乐观锁，仅在更新时检查原始数据是否被改过）。

第2步：执行标准游标生命周期

游标的完整操作遵循严格的顺序：

1. 打开 (Open)：执行 OPEN cursor_name 填充结果集。可通过 @@CURSOR_ROWS 查看结果行数。
2. 读取 (Fetch)：使用 FETCH 命令配合变量接收数据。NEXT 是最常用的取值方式，也可用 FIRST、PRIOR、LAST 或绝对/相对偏移量。
3. 关闭 (Close)：执行 CLOSE cursor_name 释放当前结果集资源，此时游标仍定义存在，可再次 OPEN。
4. 删除 (Deallocate)：执行 DEALLOCATE cursor_name 彻底销毁游标定义。

第3步：实现游标循环读取示例

以下代码演示了如何使用 Scroll 游标读取 student 表中的数据，并将其格式化打印：

-- 1. 声明游标，允许随意滚动
declare cursor_stu cursor scroll for
    select id, name, age from student;

-- 2. 打开游标
open cursor_stu;

-- 3. 定义变量存储每列值
declare @id int, @name nvarchar(20), @age varchar(20);

-- 4. 读取第一条记录
fetch first from cursor_stu into @id, @name, @age;

print '读取的数据如下：';
-- 5. 循环遍历，@@FETCH_STATUS=0表示取到了有效数据
while (@@fetch_status = 0)
begin
    print '编号：' + convert(char(5), @id) + ', 名称：' + @name + ', 类型：' + @age;
    -- 继续读取下一条
    fetch next from cursor_stu into @id, @name, @age;
end

-- 6. 清理资源
close cursor_stu;
deallocate cursor_stu;

总结

事务处理确保了数据的完整性与一致性，异常处理增强了代码的鲁棒性与可维护性，而游标则为处理复杂的逐行逻辑提供了必要的手段。熟练组合运用这三者，能够编写出既高效又安全的 SQL Server 应用程序。以上就是 SQL Server 事务、异常与游标核心机制及实战的详细内容，更多关于数据库开发技巧的资料请关注本站其它相关文章！