使用SQL SERVER存储过程实现银行转账业务

在银行金融系统中，我们常常都要实现银行转账这样的业务操作，而这种金融系统并发性相当高，需要考虑的如何提高性能和保证安全性等相关的问题。使用存储过程来实现银行转账是一个很好的选择。

SQL SERVER数据库中的存储过程相对于应用程序中来操作Transact-SQL语言的优缺点：

优点：

1.              存储过程已在服务器注册，预编译，存储过程预先编译好放在数据库内，减少编译语句所花的时间

2.              存储过程可以用于减少网络通信流量，存储过程代码直接存储于数据库中，执行的时候只需要应用程序传递参数即可，而不需要整段的T-SQL代码传递到数据库中。

3.              重用性高，一段存储过程，可以在应用程序中不同的位置来调用。

4.              可维护性高，把整个业务的操作封装在一起，有利于以后做代码的替换操作，而应用程序会在日后不断的维护中，会导致TrancSQL过程代码会变得越来越复杂，同时，更新存储过程通常比更新应用程序简单的多，更新应该程序需要编译，部署。

5.              使用缓存改善性能，编译好的存储过程直接进入SQL SERVER的缓存中，使得下次执行的时候，能马上调用。而在SQL SERVER 2005中，执行计划已针对所有 T-SQL 批处理进行了缓存，效率和存储过程差不多了。

6.              强制使用数据库中的安全认证机制，使得应用程序的安全性提高：

a)         对存储过程向特定用户授权，也可以提供对特定数据的访问。

b)        增强代码安全，通过传递参数的方式，能有效防止 SQL注入。

缺点：

1.         可移植性差

a)         由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

b)        如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

2.         增加数据库服务器的负载

3.         旧版本的SQL SERVER的编程SQL语句功能较差，SQL SERVER 2005有所改善

4.         调试过程不如应用程序方便

 

下面我们来看看如何来使用SQL SERVER 2005的存储过程实现银行转账这样的业务操作：

 

1.       准备工作：先创建一个account银行账户表，脚本如下：

create table account(

       id int identity(1,1) primary key,

       cardno char(20),

       money numeric(18,2)

)

 

insert into account values('01',1000.0)

insert into account values('02',1000.0)

 

 

 

2.       实现方案1：

不考虑任何的条件，简单实现两个账户之间的转账操作。

参数：

@out_cardno:转出账户

@in_cardno: 转入账户

@money:   转账金额

 

CREATE PROCEDURE sp_transfer_money1

       @out_cardno char(20),

       @in_cardno char(20),

       @money numeric(18,2)

AS

BEGIN

       update account set money = money-@money where cardno=@out_cardno

       update account set money = money+@money where cardno=@in_cardno      

       PRINT '转账成功.'

END

 

-- 执行上面的存储过程

EXEC sp_transfer_money1 '01','02',1200.0

 

出现问题：

       转出账户”01”的只有1000块，但是却能够成功的转1200块，这样的做法是不可思议的。

 

 

3.       实现方案2：

加入对转出账户的余额判断：

CREATE PROCEDURE sp_transfer_money2

       @out_cardno char(20),

       @in_cardno char(20),

       @money numeric(18,2)

AS

BEGIN

       DECLARE @remain numeric(18,2)

       select @remain=money from account where cardno=@out_cardno

       IF @remain>=@money

       BEGIN

              update account set money = money-@money where cardno=@out_cardno

              update account set money = money+@money where cardno=@in_cardno      

              PRINT '转账成功.'

       END

       ELSE

       BEGIN

              PRINT '余额不足.'

       END

END

 

EXEC sp_transfer_money2 '01','02',1000.0

 

出现问题：

1.       存储过程的参数从外部出入，但是不能确定参数的合法性，一旦参数出现问题，执行存储过程就可能发生错误，导致部分业务代码执行不成功，发生数据不一致的问题。

2.       缺少事务控制的管理

 

4.       实现方案3：

 

CREATE PROCEDURE sp_transfer_money3

       @out_cardno char(20),

       @in_cardno char(20),

       @money numeric(18,2)

as

BEGIN

       DECLARE @remain numeric(18,2)

       select @remain=money from account where cardno=@out_cardno

       if @remain>@money

       BEGIN

              BEGIN TRANSACTION T1

                     update account set money = money-@money where cardno=@out_cardno

                     update account set money = money+@money where cardno=@in_cardno                                PRINT '转账成功.'

                     if @remain>@money

                     begin

                            rollback transaction

                     end

              COMMIT TRANSACTION T1

       END

       ELSE

       BEGIN

              PRINT '余额不足.'

       END

END

 

EXEC sp_transfer_money3 '01','02',100.0

 

现实生活中的金融业务是相当复杂的，上面的几个方案只是模拟了最基本的情况，譬如，跨行之间的转账，同行不同区的转账这些都是具体的业务需求。要把这些业务都使用存储过程来实现的话，对我们的数据库开发人员来说，要求就更高了。