Use SQL server stored procedures to implement bank transfers

In the banking and financial system, we often need to implement business operations such as bank transfers, and such financial systems have high concurrency, so we need to consider how to improve performance and ensure security. Using Stored Procedures for bank transfers is a good choice.

Advantages and disadvantages of the stored procedure in the SQL Server database compared with the transact-SQL language in the application:

Advantages:

1. The stored procedure has been registered on the server, pre-compiled, and pre-compiled in the database, reducing the time spent on compiling statements.

2. stored procedures can be used to reduce network communication traffic. Stored Procedure Code is directly stored in the database. During execution, only the application needs to pass parameters, instead of passing the entire segment of T-SQL code to the database.

3. High reusability. A stored procedure can be called in different places in the application.

4. high maintainability: encapsulates the operations of the entire business, which facilitates code replacement in the future, and the application will be continuously maintained in the future, the trancsql Process Code will become more and more complex. At the same time, updating stored procedures is usually much simpler than updating applications. Updates should be compiled and deployed.

5. Use the cache to improve performance. The compiled stored procedure directly enters the SQL Server cache so that it can be called immediately at the next execution. In SQL Server 2005, the execution plan has been cached for all T-SQL batches, with the same efficiency as the stored procedure.

6. enforce the security authentication mechanism in the database to improve the security of applications:

A) Authorization to specific users for stored procedures, and access to specific data.

B) enhanced code security. Passing parameters can effectively prevent SQL injection.

Disadvantages:

1. Poor portability

A) Because stored procedures bind applications to SQL Server, encapsulating business logic Using Stored Procedures limits the portability of applications.

B) if the portability of applications is very important in your environment, it may be a better choice to encapsulate the business logic in an RDBMS-specific middle layer.

2. Increase the load on the database server

3. The programming SQL statement function of the old version of SQL Server is poor, and SQL Server 2005 is improved.

4. the debugging process is not as convenient as the application

 

Next, let's take a look at how to use the SQL Server 2005 stored procedure to implement business operations such as bank transfers:

 

1. Preparation: first create an account bank account table. The script is as follows:

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. Implementation solution 1:

Transfers between two accounts without considering any conditions.

Parameters:

@ Out_cardno: transfer out account

@ In_cardno: transfer into account

@ Money: transfer amount

 

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 'transfer successful .'

End

 

-- Execute the above Stored Procedure

Exec sp_transfer_money1 '01', '02', 1200.0
 

 

Problem:

The transfer-out account "01" has only 1000, but can successfully transfer 1200, which is incredible.

 

 

3. Implementation solution 2:

Add judgment on the balance of the outgoing account:

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 'transfer successful .'

End

Else

Begin

Print 'balance is insufficient .'

End

End

 

Exec sp_transfer_money2 '01 ', '02', 1000.0
 

 

Problem:

1. stored procedure parameters come and go from external sources, but the validity of the parameters cannot be determined. Once a parameter has a problem, an error may occur when executing the stored procedure, resulting in the failure of executing some business code, data inconsistency occurs.

2. Lack of management of transaction control

 

4. Implementation solution 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 'transfer successful .'

If @ remain> @ money

Begin

Rollback transaction

End

Commit transaction T1

End

Else

Begin

Print 'balance is insufficient .'

End

End

 

Exec sp_transfer_money3 '01', '02', 100.0
 

 

In real life, the financial business is quite complex. The above solutions only simulate the most basic situations, such as inter-bank transfers, transfers between different zones in the same industry are specific business needs. If we want to use stored procedures to implement these services, our database developers have higher requirements.