晚上睡不着觉，看了看数据库，用sql写的ATM系统，流程很简单，本人能力有限，各位了解数据库的大虾看一下，欢迎补充。
USE master
go
IF EXISTS(SELECT * FROM sysdatabases WHERE name='MyBanks')
DROP DATABASE MyBanks
CREATE DATABASE MyBanks
go
USE MyBanks
go
CREATE TABLE BankAccount
(
bankID char(5) CHECK(len(bankID)=5) PRIMARY KEY,
bankName nchar(4) NOT NULL,
bankMoney money CHECK(bankMoney>10000) NOT null
)
go
CREATE TABLE UserAccount
(
accountID char(8) CHECK(len(accountID)=8) PRIMARY KEY,
accountName nchar(4) NOT NULL,
accountPwd int CHECK(len(accountPwd)=6) NOT NULL,
accountMoney money check(accountMoney>=10) NOT NULL,
bankID char(5) FOREIGN KEY REFERENCES BankAccount(bankID)
)
go
INSERT INTO BankAccount
SELECT '10010','工商银行',50000 union
SELECT '10011','建设银行',56000 union
SELECT '10012','交通银行',80000 union
SELECT '10013','中国银行',20000
go
INSERT INTO UserAccount
SELECT '61000101','张三',123123,5000,'10010' UNION
SELECT '61000102','李斯',123123,1000,'10011' UNION
SELECT '61000103','王五',123123,2000,'10012' UNION
SELECT '61000104','赵六',123123,53000,'10010' UNION
SELECT '61000105','小水',123123,4000,'10012' UNION
SELECT '61000106','小蛇',123123,5700,'10013'
go
---------------------------------------事务实现ATM机的存款、取款、转账功能--------------------------------------------------
--1、准备账户信息，以备转账过程中使用
--2、判断当前账户的用户名和密码是否匹配
--3、判断用户具体要进行的操作（存款、取款、转账功能）
--4、通过判断目标账户来进行具体操作的判断
--5、如果转账判断账户是哪个银行的，如果目标账户和自己账户相同则为同行转账，否则为异行转账
--6、同行转账没有手续费，异行转账收取4%的手续费
--7、输出操作结果
--当前账户
declare @userAccount char(8)
--当前账户密码
declare @userPwd int
--目标账户
declare @targetAccount char(8)
--操作金额
declare @operateMoney money
--定义账户余额
declare @money money
--用户银行
declare @userBank char(5)
--目标银行
declare @targetBank char(5)
set @userAccount = '61000101'
set @userPwd = 123123
set @operateMoney = 100
set @targetAccount = '61000106'
select * from UserAccount where accountID=@userAccount and accountPwd = @userPwd
if (@@rowcount>=1)
begin
print '您的账户登录成功!!!'
--同行操作
if(@userAccount=@targetAccount)
BEGIN
--存款或者取款
BEGIN TRANSACTION
BEGIN try
UPDATE userAccount SET accountMoney = accountMoney+@operateMoney WHERE accountID = @userAccount
COMMIT TRANSACTION
select @money = accountMoney FROM userAccount WHERE accountID = @userAccount
PRINT '交易成功,您的账户余额为'+convert(varchar,@money)
END try
BEGIN catch
PRINT '您当前的账务操作有误，请确认后重试！！！'
ROLLBACK TRANSACTION
END catch
END
--不同账户操作(转账操作)
ELSE
BEGIN
--比较不同的账户是不是同一个银行
select @userBank = bankID from UserAccount where accountID = @userAccount
select @targetBank = bankID from UserAccount where accountID = @targetAccount
--同行转账
if (@userBank=@targetBank)
begin
begin transaction
begin try
--从个人账户转账到银行账户
update UserAccount set accountMoney = accountMoney - @operateMoney where accountID = @userAccount
update BankAccount set bankMoney = bankMoney + @operateMoney where bankID = @userBank
--从银行账户转账到目标账户
update BankAccount set bankMoney = bankMoney - @operateMoney where bankID = @userBank
update UserAccount set accountMoney = accountMoney + @operateMoney where accountID = @targetAccount
commit transaction
select @money = accountMoney FROM userAccount WHERE accountID = @userAccount
PRINT '转账成功,您的账户余额为'+convert(varchar,@money)
end try
begin catch
PRINT '转战失败，请确认后重试！！！'
rollback transaction
end catch
end
--异行转账
else
BEGIN
BEGIN transaction
BEGIN try
--从个人账户转账到银行账户
update UserAccount set accountMoney = accountMoney - @operateMoney where accountID = @userAccount
update BankAccount set bankMoney = bankMoney + @operateMoney where bankID = @userBank
--从自己的银行转入目标账户的银行
UPDATE BankAccount SET bankMoney = bankMoney - @operateMoney where bankID = @userBank
UPDATE BankAccount SET bankMoney = bankMoney + @operateMoney WHERE bankID = @targetBank
--从目标银行账户转账到目标账户
UPDATE BankAccount SET bankMoney = bankMoney - @operateMoney WHERE bankID = @targetBank
update UserAccount set accountMoney = accountMoney + @operateMoney where accountID = @targetAccount
COMMIT TRANSACTION
select @money = accountMoney FROM userAccount WHERE accountID = @userAccount
PRINT '异行转账成功,您的账户余额为'+convert(varchar,@money)
END try
BEGIN catch
PRINT '异行转帐失败，请确认后重试！！！'
rollback transaction
END catch
END
END
end
else
begin
print '您的密码或者用户名有误，请重新再试!!!'
end
|
收藏
淘帖
踩
