事务+存储过程转账功能

笔记说明：按“功能概述→代码拆分→核心知识点→注意事项→测试分析”结构整理，保留原代码核心逻辑，补充详细注释和考点解析，适配期末复习和实操调试。

一、功能概述

本案例通过**存储过程+事务**实现银行卡转账功能，核心目标是保证转账操作的原子性（转出和转入要么全成功，要么全失败），避免因部分操作失败导致数据不一致（如钱转出成功但转入失败，出现资金流失）。

涉及表：CardNew（银行卡表，包含StdudentId、StudentName、CurrentMoney字段，其中CurrentMoney有check约束：余额>1）。

二、代码拆分详解

模块1：存储过程删除（避免重复创建）

— 判断存储过程Test1是否存在，若存在则删除
if exists(select * from sysobjects where name = 'Test1')
drop proc Test1;
go

核心说明：

• sysobjects：系统表，存储数据库中所有对象（存储过程、表、视图等）的信息。

• exists：判断存储过程是否存在，避免重复创建导致“已存在同名对象”报错。

• go：批处理结束标记，将前后代码分隔为独立的执行批次。

模块2：创建转账存储过程（核心代码）

— 创建存储过程Test1，实现转账功能
create proc Test1
@inAccount int, — 入账账号（接收转账的银行卡ID）
@outAccout int, — 出账账号（转出金额的银行卡ID，注意原代码拼写：outAccout）
@jine int — 转账金额
as
begin
— 关闭计数消息返回，提升存储过程执行效率（可选，推荐添加）
SET NOCOUNT ON;

— 定义变量：统计事务中SQL执行的错误个数（0=无错误，非0=有错误）
declare @errorNum int;
set @errorNum = 0;

— 开启事务：标记事务的开始，后续操作纳入事务管理
begin transaction;

begin
— 1. 转出逻辑：从出账账号扣除指定金额
update CardNew
set CurrentMoney = CurrentMoney – @jine
where StdudentId = @outAccout;

— 记录转出操作的错误码：@@ERROR是全局变量，返回上一条SQL的错误码
set @errorNum = @errorNum + @@ERROR;

— 2. 转入逻辑：向入账账号增加指定金额
update CardNew
set CurrentMoney = CurrentMoney + @jine
where StdudentId = @inAccount;

— 记录转入操作的错误码，累计错误个数
set @errorNum = @errorNum + @@ERROR;

— 3. 判断事务执行状态，决定提交或回滚
if(@errorNum > 0) — 存在错误（如账号不存在、余额不足违反约束）
begin
rollback transaction; — 回滚事务：撤销所有未提交的操作
print '转账失败，事务已回滚（错误累计数：' + CAST(@errorNum as varchar) + '）';
end
else — 无错误，所有操作执行成功
begin
commit transaction; — 提交事务：将操作结果永久保存到数据库
print '转账成功，事务已提交';
end
end

— 开启计数消息返回，恢复默认设置
SET NOCOUNT OFF;
end
go

核心说明：

• create proc 存储过程名：创建存储过程，括号内为输入参数，需指定参数类型和注释。

• @@ERROR：全局变量，核心考点！返回上一条SQL语句的错误码，0表示无错误，非0表示有错误（如违反check约束、语法错误）。

• begin transaction：开启事务，后续的update操作作为一个整体，要么全执行，要么全撤销。

• rollback transaction：回滚事务，当事务中任意一步出错，撤销所有操作，恢复到事务开启前的状态。

• commit transaction：提交事务，当所有操作无错误时，将修改永久保存，事务结束。

模块3：测试存储过程（转账成功场景）

— 测试场景1：转账成功（前提：CardNew表中存在1000、1003账号，且1003余额≥1000+1）
print '========== 测试场景1：正常转账 ==========';
exec Test1 1000, 1003, 1000; — 调用存储过程：入账1000，出账1003，转账1000元
select * from CardNew; — 查询转账后数据，验证余额变化
print '==========================================';

测试前提：CardNew表中StdudentId=1003的账号初始余额需大于1000（如1400），扣除1000后余额为400，满足check约束（余额>1），操作无错误，事务提交。

模块4：测试存储过程（转账失败场景）

— 测试场景2：转账失败（前提：经过场景1转账后，1003账号余额为400，再次转账1000元会导致余额不足）
print '========== 测试场景2：转账失败（余额不足） ==========';
exec Test1 1000, 1003, 1000; — 再次调用存储过程，转账1000元
select * from CardNew; — 查询数据，验证事务回滚后余额未变化
print '==========================================';

测试分析：1003账号余额400，扣除1000后余额为-600，违反CardNew表的check(CurrentMoney>1)约束，转出操作报错，@@ERROR返回非0值，@errorNum>0，触发事务回滚，两个update操作均被撤销，余额恢复到转账前的状态。

三、核心考点总结（必背）

1. 事务的核心作用

解决多步DML操作（insert、update、delete）的数据一致性问题，确保操作的原子性（要么全成功，要么全失败），典型场景：转账、批量修改、订单支付。

2. 事务的核心语法（3句）

• 开启事务：begin transaction

• 提交事务：commit transaction（无错误时执行，数据永久保存）

• 回滚事务：rollback transaction（有错误时执行，撤销所有操作）

3. @@ERROR全局变量的特点

• 仅返回“上一条SQL语句”的错误码，需每次操作后立即累加，否则会被后续操作覆盖。

• 0表示无错误，非0表示有错误（不同错误对应不同错误码）。

• 可用于判断事务中是否出现异常，是事务回滚的核心依据。

4. 本案例中转账失败的常见原因

• 出账账号/入账账号不存在（StdudentId无匹配记录），update操作无影响，但@@ERROR仍为0（需额外添加账号存在性判断）。

• 出账账号余额不足，扣除金额后余额≤1，违反check约束，触发报错，@@ERROR返回非0值。

• 转账金额≤0，导致余额计算异常（可添加金额合法性判断优化）。

四、易错点与优化建议

1. 原代码易错点

• 参数拼写错误：@outAccout（正确应为@outAccount），虽不影响执行，但不符合命名规范，建议修正。

• 无错误提示：原代码未添加print语句，无法直观判断转账成功/失败，建议补充。

• 无前置判断：未判断账号是否存在、金额是否合法，建议在事务开启前添加业务逻辑判断，减少无效事务。

2. 优化建议（可选，提升健壮性）

— 优化：添加金额合法性和账号存在性判断（放在事务开启前）
if(@jine <= 0)
begin
raiserror('转账金额必须大于0',16,1);
return;
end

if not exists(select 1 from CardNew where StdudentId = @outAccout)
begin
raiserror('出账账号不存在',16,1);
return;
end

if not exists(select 1 from CardNew where StdudentId = @inAccount)
begin
raiserror('入账账号不存在',16,1);
return;
end

五、测试结果分析

场景1（成功）

执行结果：出账账号1003余额减少1000，入账账号1000余额增加1000，事务提交，打印“转账成功，事务已提交”。

场景2（失败）

执行结果：出账账号1003余额未变化，入账账号1000余额未变化，事务回滚，打印“转账失败，事务已回滚”，查询结果与转账前一致。

TRY-CATCH+双重错误捕获（转账案例)

笔记说明：按“基础语法示例→核心案例拆分→考点解析→易错点总结”结构整理，保留原代码核心逻辑，补充详细注释和考试重点，适配期末复习和实操调试。

一、TRY-CATCH基础语法（入门示例）

TRY-CATCH是SQL Server的异常捕获机制，用于捕获并处理执行过程中的错误，替代传统的@@ERROR逐行判断，更高效、更全面。

— 示例1：基础TRY-CATCH语法（字符转数字）
— TRY块：存放可能出现错误的代码
begin try
declare @i int
set @i = cast('12' as int) — 正常转换，无错误
print @i — 输出：12
end try

— CATCH块：存放错误处理代码，仅当TRY块出现错误时执行
begin catch
— raiserror：抛出自定义错误（16为普通错误等级，不会中断数据库服务）
raiserror('字符不能转成数字',16,1)
print '转换失败' — 仅当转换出错时才会执行
end catch

核心说明：

• begin try…end try：包裹可能出现错误的代码，若代码执行无错，跳过CATCH块；若出错，立即跳转到CATCH块执行。

• begin catch…end catch：包裹错误处理代码，是异常捕获的“处理区”。

• raiserror：抛出自定义错误，参数依次为「错误信息、错误等级（16为常用）、状态码」，区别于 C# 的 throw——仅抛出提示，不自动中断程序，需配合 return 退出。

• 区别于C#的throw：SQL中raiserror仅抛出错误提示，不会自动中断程序，需配合return退出存储过程.

— 示例2：事务结合TRY-CATCH（删除操作）
begin tran — 开启事务
begin try
— 执行可能出错的删除操作（StudentId=1000000大概率不存在）
delete from StudentInfo where StudentId = 1000000
— 若无错误，提交事务
commit tran
print '删除成功，事务已提交'
end try
begin catch
— 若有错误，回滚事务
print '执行任意一个错误就回滚'
rollback tran
print '事务已回滚'
end catch

核心知识点：事务与 TRY-CATCH 结合，实现“操作成功则提交，操作失败则回滚”，避免事务残留和数据不一致，是转账、订单支付等场景的核心用法。

二、核心案例：转账存储过程（双重错误捕获）

本案例通过「业务逻辑前置判断 + @@ERROR + TRY-CATCH」实现双重错误捕获，是考试和实际开发的高频考点，核心目标是保证转账操作的原子性（转出、转入要么全成功，要么全失败）。

模块1：删除原有存储过程（避免重复创建）

— 判断存储过程test01是否存在，若存在则删除
if exists (select * from sysobjects where name = 'test01')
drop proc test01;
go

说明：

• sysobjects：SQL Server 系统表，存储数据库中所有对象（存储过程、表、视图等）的信息。

• go：批处理结束标记，将前后代码分隔为独立的执行批次，避免语法报错。

模块2：创建存储过程（核心代码，拆分详解）

— 创建转账存储过程test01
create proc test01
@inAccount int, — 入账账号（接收方）
@outAccount int, — 出账账号（转出方）
@jine int — 转账金额
as
begin
SET NOCOUNT ON; — 关闭计数消息，提升执行效率

— 定义变量：统计SQL执行错误个数（0=无错，非0=有错）
declare @errorNum int;
set @errorNum = 0;

— ==================== 第一层：业务逻辑前置判断（提前拦截无效请求） ====================
— 1. 判断转账金额是否合法（金额必须大于0）
if(@jine <= 0)
begin
raiserror('转账的金额不合法',16,1); — 抛出业务错误
return; — 退出存储过程，不执行后续操作
end

— 2. 判断转出账户是否存在（exists+select 1：高效判断记录是否存在）
if not exists (select 1 from CardNew where StdudentId = @outAccount)
begin
raiserror('转出账户不存在',16,1);
return;
end

— 3. 判断转入账户是否存在
if not exists (select 1 from CardNew where StdudentId = @inAccount)
begin
raiserror('转入账户不存在',16,1);
return;
end

— 4. 判断转出账户余额是否充足（可选，原代码注释，建议开启）
declare @outMoney money; — 存储转出账户当前余额
select @outMoney = CurrentMoney from CardNew where StdudentId = @outAccount;
if(@outMoney – @jine <= 1) — 扣除后需满足CardNew表的check约束（余额>1）
begin
raiserror('余额不足',16,1);
return;
end

— ==================== 第二层：事务+双重错误捕获（执行转账操作） ====================
begin tran; — 开启事务，后续操作纳入事务管理

begin try
— 1. 转出逻辑：从出账账号扣除指定金额
update CardNew
set CurrentMoney = CurrentMoney – @jine
where StdudentId = @outAccount;

— 记录转出操作的错误码（@@ERROR：上一条SQL的错误码）
set @errorNum = @errorNum + @@ERROR;

— 2. 转入逻辑：向入账账号增加指定金额
update CardNew
set CurrentMoney = CurrentMoney + @jine
where StdudentId = @inAccount;

— 记录转入操作的错误码，累计错误个数
set @errorNum = @errorNum + @@ERROR;

— 3. 第一层捕获（@@ERROR）：判断SQL执行是否出错
if(@errorNum > 0)
begin
print '检测到SQL执行错误，触发回滚';
raiserror('sql执行出错，事务已经回滚',16,1);
rollback transaction; — 回滚事务
return;
end
else
begin
commit transaction; — 无错误，提交事务
print '转账成功，事务已提交';
end
end try

begin catch
— 第二层捕获（TRY-CATCH）：捕获TRY块中未被@@ERROR拦截的严重错误
— @@TRANCOUNT：全局变量，返回当前会话未提交的事务数量，避免重复回滚报错
if(@@TRANCOUNT > 0)
begin
rollback tran; — 回滚所有未提交的操作
print '可提交的事务 回滚';
end
return; — 退出存储过程
end catch

SET NOCOUNT OFF; — 恢复计数消息
end
go

模块3：测试存储过程

— 测试场景：正常转账（前提：1003、1002账号存在，且1002余额充足）
exec test01 1003, 1002, 1000; — 入账1003，出账1002，转账1000元
select * from CardNew; — 查询转账后数据，验证余额变化

三、核心考点解析（必背，适配简答/填空）

1. 双重错误捕获的核心逻辑（简答题重点）

本案例通过“三层防护”保证转账操作的安全性和数据一致性，核心逻辑如下：

• 第一层：业务逻辑前置判断：在开启事务前，通过 if+exists+raiserror+return 拦截无效请求（金额非法、账号不存在、余额不足），从源头避免无效操作，减少事务开启次数，提升执行效率。

• 第二层：@@ERROR 捕获：执行转账 SQL 后，通过 @@ERROR 统计错误个数，捕获 SQL 执行层面的普通错误（如约束冲突、语法错误），一旦检测到错误，立即回滚事务。

• 第三层：TRY-CATCH 捕获：捕获 TRY 块中未被 @@ERROR 拦截的严重错误（如死锁、数据库连接异常、权限不足），确保无论出现何种错误，都能回滚事务，避免事务残留和数据不一致。

2. 关键全局变量（选择题/填空题重点）

• @@ERROR：返回上一条 SQL 语句的错误码，0 表示无错误，非 0 表示有错误；易错点：仅返回“上一条”SQL 的错误码，需每次操作后立即累加，否则会被后续操作覆盖。

• @@TRANCOUNT：返回当前会话中未提交的事务数量，用于判断是否需要回滚；核心作用：避免“无事务可回滚”报错，是事务异常处理的必备判断。

3. 事务与 TRY-CATCH 结合的核心原则

事务的核心是“原子性”，结合 TRY-CATCH 时需遵循以下原则，否则会导致数据不一致：

• 事务在 TRY 块外开启，确保 TRY-CATCH 能覆盖整个事务流程（若在 TRY 块内开启，严重错误可能导致事务未被回滚）。

• TRY 块内执行核心业务操作，无错则提交事务；有错则通过 @@ERROR 触发回滚。

• CATCH 块内必须判断事务状态（@@TRANCOUNT>0），确保回滚所有未提交操作，避免事务残留。

四、易错点总结（考试避坑重点）

1. 原代码易错点

• 余额判断被注释：原代码中未开启余额判断，若出账账号余额不足，会触发 CardNew 表的 check 约束报错，建议开启以提前拦截，提升用户体验。

• 缺少错误详细信息：CATCH 块中未输出错误编号和描述，不利于调试，建议添加错误相关函数（见下文补充优化）。

• 提示信息不清晰：原代码中“ddddddddddddddddddd”无实际意义，建议替换为明确的错误提示，便于测试和调试。

2. 开发/考试高频易错场景

• 忘记添加 return：raiserror 仅抛出错误提示，不会自动中断程序，若缺少 return，会继续执行后续代码，导致无效事务开启。

• @@ERROR 使用不当：未在每个 SQL 操作后立即累加，导致错误码被后续操作覆盖，无法正确统计错误个数，进而导致事务未回滚。

• 重复回滚事务：未通过 @@TRANCOUNT 判断事务状态，若事务已提交，执行 rollback 会触发“无事务可回滚”报错，是考试和开发的高频易错点。

五、补充优化（提升健壮性，考试加分项）

在 CATCH 块中添加错误详细信息输出，便于调试和错误排查，是考试加分项，也是实际开发的必备操作：

begin catch
if(@@TRANCOUNT > 0)
begin
rollback tran;
print '可提交的事务 回滚';
end
— 输出错误详细信息（考试加分项）
print '错误编号：' + CAST(ERROR_NUMBER() as varchar);
print '错误描述：' + ERROR_MESSAGE();
print '错误严重等级：' + CAST(ERROR_SEVERITY() as varchar);
return;
end catch

补充说明：ERROR_NUMBER()（错误编号）、ERROR_MESSAGE()（错误描述）、ERROR_SEVERITY()（错误等级）是 CATCH 块专用函数，仅在 CATCH 块内有效。

六、测试结果分析

场景1：正常转账（账号存在、金额合法、余额充足）

执行结果：出账账号1002余额减少1000元，入账账号1003余额增加1000元，事务提交，打印“转账成功，事务已提交”，查询 CardNew 表，数据与预期一致。

场景2：异常场景（如余额不足）

执行结果：触发业务逻辑判断，抛出“余额不足”错误，执行 return 退出存储过程，事务未开启，CardNew 表数据无变化；若未开启余额判断，会触发 check 约束报错，@@ERROR 捕获错误，触发事务回滚，数据仍无变化，保证了数据一致性。

场景3：严重错误（如死锁）

执行结果：TRY 块中的代码被中断，直接跳转到 CATCH 块，判断 @@TRANCOUNT>0 后回滚事务，打印“可提交的事务 回滚”，数据无变化，避免事务残留。