使用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
 

 

現實生活中的金融業務是相當複雜的，上面的幾個方案只是類比了最基本的情況，譬如，跨行之間的轉賬，同行不同區的轉賬這些都是具體的業務需求。要把這些業務都使用預存程序來實現的話，對我們的資料庫開發人員來說，要求就更高了。