USE [master]
GO
/****** Object: Database [BankSystem] Script Date: 11/20/2013 16:12:45 ******/
CREATE DATABASE [BankSystem] ON PRIMARY
( NAME = N'BankSystem', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BankSystem.mdf'
, SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'BankSystem_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
\BankSystem_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [BankSystem] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [BankSystem].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
EXEC sys.sp_db_vardecimal_storage_format N'BankSystem', N'ON'
GO
USE [BankSystem]
GO
/****** Object: Table [dbo].[CardInfo] Script Date: 11/20/2013 16:12:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CardInfo](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[CardNum] [nvarchar](19) NOT NULL,
[Balance] [money] NULL,
PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Transfer_Event] Script Date: 11/20/2013 16:12:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transfer_Event](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[FromCardID] [nvarchar](19) NOT NULL,
[ToCardID] [nvarchar](19) NOT NULL,
[TransMoney] [money] NULL,
[OccurTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[TranserMoney] Script Date: 11/20/2013 16:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--实现转账过程的存储过程实现如下(0代表成功,1代表转账源账户不存在,2代表转账目标账户不存在,3代表金额不足,4代表失败)
CREATE procedure [dbo].[TranserMoney]
(
@fromCardNum varchar(19), --转账源卡号
@toCardNum varchar(19), --转账目标卡号
@tansMoney Money, --转账金额
@statue int output --0代表成功,1代表转账源账户不存在,2代表转账目标账户不存在,3代表金额不足,4代表失败
)
as
declare @cardNum varchar(19)
declare @balance Money=(select balance from CardInfo where CardNum=@fromCardNum)
--开始事务
if @balance is not null
begin
declare @exist int=(select COUNT(*) from CardInfo where CardNum=@toCardNum)
if @exist !=0
begin
if @balance>=@tansMoney
begin
begin transaction
declare @level int=0
update CardInfo set Balance=Balance-@tansMoney where CardNum=@fromCardNum
set @level=@level+@@ERROR
update CardInfo set Balance=Balance+@tansMoney where CardNum=@toCardNum
set @level=@level+@@ERROR
insert into Transfer_Event(FromCardID,ToCardID,TransMoney,OccurTime)values
(@fromCardNum,@toCardNum,@tansMoney,GETDATE())
set @level=@level+@@ERROR
if @level=0
begin
set @statue=0
commit transaction --提交事务
end
else
begin
set @statue=4
rollback transaction --回滚事务
end
end
else
begin
set @statue=3
end
end
else
begin
set @statue=2
end
end
else
begin
set @statue=1
end
GO
/****** Object: Default [DF__CardInfo__Balanc__014935CB] Script Date: 11/20/2013 16:12:45 ******/
ALTER TABLE [dbo].[CardInfo] ADD DEFAULT ((0)) FOR [Balance]
GO
下面的测试代码
--0代表成功,1代表转账源账户不存在,2代表转账目标账户不存在,3代表金额不足,4代表失败
declare @statue int
exec TranserMoney '101','103',100,@statue output
select @statue
select * from cardinfo
select * from Transfer_Event