MYSQL实现ATM所有业务（附源码）

作为一个完整版的ATM系统，模拟银行的注册、登入、存取款等业务：

具体包括：用户的注册、登录、修改密码、存取款、生成交易信息、挂失、修改电话号码

主要涉及知识：E-R图、关系模式、数据库和表的创建、子句定义约束、触发器、存储过程

具体要求：

（1）用户注册、登录、修改登录密码业务。

只有注册用户才可以登录ATM系统，使用ATM的相关业务功能。用户还可以修改密码。

（2）开卡业务。

用户登录后，可以完成开卡业务，每个用户在该银行的持卡数不能超过三张。客户开户需要提供以下数据：

数据	描述
银行卡号	由19位数字组成，其中前8位代表特殊含义，如代表某总行某支行，假设该银行要求其卡号格式为6236 1000,后八位由开卡时间（包括年月日）和3位1000以内的随机数组成，每4位后面有空格。
密码	由6位数字组成，默认密码为000000
开户日期	默认为开户当日
开户金额	客户开设银行卡账户时存入的金额，规定不得小于1元
账户类型	账户有两种类型：活期和定期，默认为活期
是否挂失	默认为“否”

（3）存款业务

客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款业务。

（4）取款业务。

客户持银行卡在ATM机上输入密码，经系统验证身份后办理取款业务。银行规定，每个账户当前的存款金额不得小于1元。

（5）转账业务。

客户持银行卡在ATM机上输入密码，经系统验证身份后办理转账业务。银行规定，每个账户当前的存款金额不得小于1元。

（6）银行交易信息

银行在为客户办理业务时，需要记录每一笔账目。每一笔账目都将以银行交易信息的方式进行记录。

数据	描述
卡号	交易主卡卡号
交易日期	默认为当日当时
交易金额	必须大于0
交易类型	必须为存入和支取两种
备注	对每笔交易做必要说明

E-R图

 

关系模型：

客户（客户编号，姓名，密码，电话号码，身份证号）；

银行卡（卡编号，银行卡号，种类，开户日期，余额，密码，挂失状态，客户编号）；

交易信息（交易流水号，本账户卡号，交易类型，交易金额，交易时间，备注，对方账号卡号）

源代码：

DROP TABLE customer;
-- 创建客户表；
CREATE TABLE IF NOT EXISTS customer(
cID int auto_increment not null primary key comment '客户编号',
customerName varchar(10) not null comment '姓名',
pID char(6) not null comment '密码',
telephone char(11) not null comment'电话号码',
IDCard char(18) not null comment '身份证号',
address varchar(20) not null comment'地址'
);

DROP TABLE card;
-- 创建卡表；
CREATE TABLE IF NOT EXISTS card(
id int auto_increment not null primary key comment '卡编号',
cardId char(24) not null comment '银行卡号',
cardType char(6) not null default '活期' comment '种类',
openDate date not null comment'开户日期',
balance float not null comment '余额',
pID char(6) not null comment '密码',
isLost char(2) not null default '否' comment '是否挂失',
cID int comment '客户编号'
);

DROP TABLE transferInfo;
-- 交易信息表
CREATE TABLE IF NOT EXISTS transferInfo(
transID int auto_increment not null primary key comment '交易流水号',
carID1 char(24) not null comment '本方账号信息',
transType char(4) not null comment '交易类型',
transMoneny decimal(10,2) not null comment '交易金额',
transDate date not null comment '交易时间',
remark varchar(50) not null comment '备注',
cardID2 char(24) not null comment'对方账号信息'
);


-- alter table card drop check ck_cID;
-- ALTER TABLE card DROP FOREIGN KEY ck_cID;

-- 添加外键
alter table card add constraint ck_cID foreign key(cID) REFERENCES customer(cID);

-- 删除check约束
-- ALTER TABLE `表名` DROP CHECK 约束名;
alter table customer drop check ck_pID;
alter table card drop check ck_pID1;
alter table transferinfo drop check ck_pID;
-- check约束
-- customer的check约束
-- 密码长度为3
alter table customer add constraint ck_pID check(length(pID)=3);
-- 银行卡号长度为3
alter table customer add constraint ck_IDCard check(length(IDCard)=3);
-- 电话号码长度为3
alter table customer add constraint ck_telephone check(length(telephone)=3);

-- card的check约束

-- 账户有两种类型：活期和定期
alter table card add constraint ck_cardType check(cardType in('活期','定期'));
alter table card add constraint ck_PID1 check(length(pID)=3);
alter table card add constraint ck_balance check(length(balance)>=1);
-- 挂失状态有两种：是 和 否
alter table card add constraint ck_isLost check(isLost in('是','否'));
-- transferInfo的check约束
alter table transferInfo add constraint ck_transMoney check(transMoneny>0);

-- 添加唯一约束
-- customer添加唯一约束
alter table customer drop check uk_telephone;
alter table customer add constraint uk_telephone UNIQUE(telephone);
alter table customer add constraint uk_IDCard UNIQUE(IDCard);

-- card添加默认值
-- 账号类型默认为活期
alter table card change column cardType cardType char(6) default '活期';
-- 密码默认为000000
alter table card change column pID pID  char(6) default '000000';
-- 挂失状态默认为否
alter table card change column isLost isLost char(2) default '否';


-- transferInfo添加默认值
alter table transferinfo change column cardID2 cardID2 char(24) default 'ATM';
-- 开户日期	默认为开户当日
alter table transferinfo change column transDate transDate date default NOW();

-- 升序Asc及降序Desc
DESC card;
DESC transferinfo;
-- 清空表
truncate table customer;
truncate table card;
truncate table transferinfo;

-- 给客户表插入数据
insert into customer values
('1','李其','121','121','121','湖南');
insert into customer(customerName,pID,telephone,IDCard,address)
values ('胡强','120','120','120','云南');
insert into customer(customerName,pID,telephone,IDCard,address)
values ('黄胜','128','128','128','四川');
insert into customer(customerName,pID,telephone,IDCard,address)
values ('张三','123','123','127','湖南');
select * from customer;

-- 插入卡表生成信息
insert into card (cardId,cardType,openDate,balance,pID,isLost,cID)
values ((SELECT CEILING(RAND()*900+100)),'活期',now(),'100','123','否','5');

-- 随机生成19为数卡号
drop procedure procard;
create procedure procard(out v_str varchar(24))
begin
declare v_year int(4);
declare v_month char(4);
declare v_day char(4);
declare b int(4);
declare c int(4);
set v_year=(select year(now()));
set v_month=(Select Right(100 + month(now()), 2));
set v_day=(Select Right(100 + day(now()), 2));
set b=(SELECT CEILING(RAND()*900+100));
set v_str=concat('6236 1000 ',v_year,' ',v_month,v_day,' ',b);
set v_month=concat('0',c);
select v_month;
select v_str;
end;

call procard(@str);

-- 随机生成一个三位数的卡号
drop procedure procard1;
create procedure procard1(out v_str varchar(3))
begin
set v_str=(SELECT CEILING(RAND()*900+100));
select v_str;
end;
call procard1(@str);

-- 存储过程：随机生成一个19位数的卡号
drop procedure procard2;
create procedure procard2(out v_str varchar(24))
begin
set v_str=concat('6236 1000 ',(select year(now())),' ',(Select Right(100 + month(now()), 2)),(Select Right(100 + day(now()), 2)),' ',(SELECT CEILING(RAND()*900+100)));
select v_str;
end;

call procard2(@str);


-- 随机生成一个19位数的卡号
select(concat('6236 1000 ',(select year(now())),' ',(Select Right(100 + month(now()), 2)),(Select Right(100 + day(now()), 2)),' ',(SELECT CEILING(RAND()*900+100))));



-- 清空表中的数据
truncate table customer;
truncate table card;
truncate table transferinfo；

-- 显示存储过程的信息
truncate procedure staus;

-- 5-1用户注册
drop procedure register_u;

create procedure register_u(v_customerName varchar(10),v_pID char(6),v_telephone char(11),v_IDCard char(18),v_address varchar(20),out v_flag char(4))
begin
if exists(Select * from customer where telephone=v_telephone and )
then set v_flag='注册失败';
else
insert into customer(customerName,pID,telephone,IDCard,address)
values(v_customerName,v_pID,v_telephone,v_IDCard,v_address);
set v_flag='注册成功';
end if;
end;


-- 引入参数分别为姓名、卡密码、电话号码、身份证号、地址
call register_u('王五','205','200','200','湖南',@str8);
select @str8 as 注册状态;
select *  from customer;

select count(*) from card group by cID;
select count(*) from card where cID=5;


insert into card (cardId,cardType,openDate,balance,pID,isLost,cID)
values ((SELECT CEILING(RAND()*900+100)),'活期',now(),'100','123','否','5');


drop procedure addCard;

create procedure addCard(v_cardId int(3),v_cardType char(4),v_openDate date,v_balance float (4),v_pID char(3),v_isLost char(2),v_cID int(20),out v_str varchar(11))
begin
declare v_num int(2);
set v_num=(select count(*) from card where cID=v_cID);
if (v_num<3)
then
set v_str='开卡成功';
insert into card (cardId,cardType,openDate,balance,pID,isLost,cID)
values (v_cardId,v_cardType,v_openDate,v_balance,v_pID,v_isLost,v_cID);
else
set v_str='卡数已满3张，开卡失败';
end if;
select v_str as 开卡状态;
end;

-- 测试
call addCard((SELECT CEILING(RAND()*900+100)),'活期',now(),'100','123','否','5',@str);
select * from card;

-- 5-1用户登入
drop procedure userLogin;
create procedure userLogin(v_IDCard varchar(11),v_pID char(6),out v_str varchar(11))
begin
if(not exists(select * from customer where IDCard=v_IDCard and pID=v_pid))
  then set v_str='登入失败';
else 
set v_str='成功登入';
select *
from customer
where IDCard=v_IDCard;
end if;
select v_str as 登入状态;
end;

-- 输入参数为身份证号码、密码
call userLogin('126','122',@str);
select * from customer;

-- （5-2）修改密码
-- 修改客户登入密码
drop procedure changeUserPassword;

create procedure changeUserPassword(v_IDCard varchar(3),v_password varchar(3),v_password1 varchar(3),v_password2 varchar(3),out v_str varchar(11))
begin
if(exists(select * from customer where IDCard=v_IDCard and pID=v_password)and v_password1=v_password2)
 then set v_str='修改成功';
 update customer
 set pID=v_password1
 where IDCard=v_IDCard and pID=v_password;
else 
set v_str='修改失败';
end if;
end;

-- -- 输入身份证号、原来密码、新密码、确认密码
call changeUserPassword('126','122','120','120',@str);
select @str as 修改状态;
select *  from customer;



5-3- 修改电话号码
drop procedure changeTel;

create procedure changeTel(v_IDCard varchar(3),v_tel varchar(3),out v_str varchar(11))
begin
if(exists(select * from customer where IDCard=v_IDCard ))
 then set v_str='修改成功';
 update customer
 set telephone=v_tel
 where IDCard=v_IDCard;
else 
set v_str='修改失败';
end if;
end;

-- 输入身份证号、新电话号码
call changeTel('126','129',@str);
select @str as 修改状态;
select *  from customer;



-- 5-4- 删除客户
-- 建立触发器
create trigger cus_delete after delete on customer for each row
begin
delete from card where cID=old.cID;
select * from customer;
select * from card;
end;

-- 验证：
delete  from customer where cID='1';


-- 5-5挂失
drop procedure getLost;
create procedure getLost(v_cardId char (24),v_password varchar(3),out v_str varchar(11))
begin
if(exists(select * from card where cardId=v_cardId and pID=v_password))
then set v_str='挂失成功';
 update card
 set isLost='是'
 where cardId=v_cardId and pID=v_password;
else 
set v_str='挂失失败';
end if;
end;


-- 输入参数为开号、密码
call getLost('588','123',@str5);
select @str5 as 挂失状态;
select * from card;

-- 5-6 取消挂失
drop procedure disgetLost;
create procedure disgetLost(v_cardId char (24),v_password varchar(3),out v_str varchar(11))
begin
if(exists(select * from card where cardId=v_cardId and pID=v_password))
then set v_str='取消挂失成功';
 update card
 set isLost='否'
 where cardId=v_cardId and pID=v_password;
else 
set v_str='取消挂失失败';
end if;
end;


-- 输入参数为开号、密码
call disgetLost('264','123',@str6);
select @str6 as 挂失状态;
select * from card;


-- （5-7）存款业务
drop procedure inputMoney;
create procedure inputMoney(v_cardId char (24),v_password varchar(3),v_inputMoney float,out v_str varchar(11))
begin
if(exists(select * from card where cardId=v_cardId and pID=v_password))
then set v_str='存款成功';
 update card
 set balance=balance+v_inputMoney
 where cardId=v_cardId and pID=v_password;
else 
set v_str='存款失败';
end if;
end;

-- 输入参数为开号、密码、存款金额
call inputMoney('708','123','100',@str3);
select @str3 as 存款状态;
select * from card;


-- （5-8）取款业务
drop procedure outputMoney;
create procedure outputMoney(v_cardId char (24),v_password varchar(3),v_outputMoney float,out v_str varchar(11))
begin
declare v_cardmoney char(24);
if(exists(select * from card where cardId=v_cardId and pID=v_password))
then 
  set v_cardmoney=(select balance from card where cardId=v_cardId);
  if(v_cardmoney>v_outputMoney)
  then 
	set v_str='取款成功';
  update card
  set balance=balance-v_outputMoney
  where cardId=v_cardId and pID=v_password;
	else 
	set v_str='余额不足';
	end if;
else 
set v_str='账号或密码输入错误';
end if;
end;

-- 输入参数为开号、密码、取款金额
call outputMoney('708','123','50',@str4);
select @str4 as 取款状态;
select * from card;

-- （5-9）转账业务
drop procedure transfer;
create procedure transfer(v_cardId1 char(24),v_cardId2 char(24),v_transMoney float,out v_str varchar(11))
begin
declare v_card1 char(24);
declare v_card2 char(24);
set v_card1=(select balance from card where cardId=v_cardId1);
set v_card2=(select balance from card where cardId=v_cardId2);
if(v_card1>=v_transMoney)
then set v_str='转账成功';
 update card
 set balance=v_card1-v_transMoney
 where cardId=v_cardId1;
  update card
 set balance=v_card2+v_transMoney
 where cardId=v_cardId2;
else 
set v_str='余额不足';
end if;
end;

-- 输入参数本卡号、转入卡号、转账金额
call transfer('127','264','100',@str6);
select @str6 as 转账状态;
select * from card;

-- （5-10）交易信息生成

drop procedure transfer1;
create procedure transfer1(v_cardId1 char(24),v_care char(24),v_cardId2 char(24),v_transMoney float,v_care1 char(24),out v_str varchar(11))
begin
declare v_card1 char(24);
declare v_card2 char(24);
set v_card1=(select balance from card where cardId=v_cardId1);
set v_card2=(select balance from card where cardId=v_cardId2);
if(v_card1>=v_transMoney)
then
	 set v_str='转账成功';
   update card
   set balance=v_card1-v_transMoney
   where cardId=v_cardId1;
   update card
   set balance=v_card2+v_transMoney
   where cardId=v_cardId2;
   insert into transferinfo(carID1,transType,transMoneny,transDate,remark,cardID2) values(v_cardId1,v_care,v_transMoney,now(),v_care1   ,v_cardId2);
else 
set v_str='余额不足';
end if;
select * from transferinfo;
select * from card;
end;


-- 输入参数为本开号、转账类型、对方卡号、转账金额、转账日期、备注、
call transfer1('708','转账','458','100','直接入账',@str6);
select @str6 as 转账状态;