数据库期末作业之银行ATM存取款机系统

--一、建库、建表、建约束
--1、使用SQL创建表
--客户信息表userinfo
--字段名称	说明	备注
--customerID	顾客编号	自动编号（标识列），从1开始，主键	
--用序列sequence实现，用其属性：nextval
--customerName	开户名	必填	
--PID	身份证号	必填，智能是18位或15位，唯一约束	check约束len()函数
--telephone	***话	必填，11位手机号	check约束，’[0-9]’
--address	居住地址		
create table userinfo
(
customerID int identity(1,1),
customerName char(10),
PID char(18) ,
telephone char(11),
address char(30)
)
--银行卡信息表cardinfo
--字段名称	说明
--cardID	卡号	必填，主键，
--银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某综合某支行等，
--假定该行要求其营业厅的卡号格式为10103576**** ***开始，
--每4位号码后有空格，卡号一般是随机产生。
--curType	货币种类	必填，默认为RMB
--savingTate	存款类型	活期/定活两便/定期
--openDate	开户日期	必填，默认为系统当前日期
--openMoney	开户金额	必填，不低于1元
--balance	余额	必填，不低于1元，否则将销户
--pass	密码	必填，6位数字，开户时默认为6个“6”
--IsReportloss	是否挂失	必填，是/否值，默认为“否”
--customerID	顾客编号	外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号
create table cardinfo
(
cardID char(19) not null,
curType char(10),
savingTate char(10) ,
openDate datetime,
openMoney money,
balance money,
pass char(6),
IsReportloss char(2) ,
customerID int
)
--交易信息表transinfo
--字段名称	说明
--transDate	交易日期	必填，默认为系统当前日期
--cardID	卡号	必填，外键
--transType	交易类型	必填，只能是存入/支取
--transMoney	交易金额	必填，大于0
--remark	备注	可选，其他说明
create table transinfo
(
transDate datetime,
cardID char(19),
transType char(4),
transMoney money,
remark varchar(100)
)
--2、使用SQL语言在每个表上添加约束
--主键约束、外键约束、CHECK约束、默认约束、非空约束
--①
--客户信息表userinfo

--customerID	顾客编号	自动编号（标识列），从1开始，主键	
alter table userinfo
add constraint PK_userinfor primary key(customerID)

--customerName	开户名	必填	
alter table userinfo
add constraint CK_cn check(customerName is not null)

--PID	身份证号	必填，智能是18位或15位，唯一约束	check约束len()函数

alter table userinfo
add constraint CK_PID check(len(PID)=18 or len(PID)=15 )
alter table userinfo
add constraint CK_pn check(PID is not null)
alter table userinfo
add constraint UK_pid unique(pid)

--telephone	***话	必填，11位手机号	check约束，’[0-9]’

alter table userinfo
add constraint CK_tele check(telephone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table userinfo
add constraint CK_tn check(telephone is not null)

--②
--银行卡信息表cardinfo

--cardID	卡号	必填，主键，
--卡号格式为10103576**** ***开始
alter table cardinfo
ADD	constraint CK_Nca check(cardID is not null)
alter table cardinfo
ADD	constraint PK_ca primary key(cardID),
	constraint CK_car check(cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
--curType	货币种类	必填，默认为RMB

alter table cardinfo
add constraint DF_ca default 'RMB' for curType,
	constraint CK_Ncur check(curType is not null)
	
--savingTate	存款类型	活期/定活两便/定期

alter table cardinfo
add constraint CK_st check(savingTate in ('活期','定活两便','定期'))

--openDate	开户日期	必填，默认为系统当前日期

alter table cardinfo
add constraint DF_openDate default getdate() for openDate,
	constraint cK_nod check(openDate is not null)
	
--openMoney	开户金额	必填，不低于1元

alter table cardinfo
add constraint CK_nopenM check(openMoney is not null),
	constraint CK_openM check(openMoney>=1)
	
--balance	余额	必填，不低于1元，否则将销户

alter table cardinfo
add constraint CK_nbalance check(balance is not null),
	constraint CK_balance check(balance>=1)
	
--pass	密码	必填，6位数字，开户时默认为6个“6”

alter table cardinfo
add constraint CK_npass check(pass is not null),
	constraint DF_PASS default '666666' for pass,
	constraint CK_PASS check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')
--IsReportloss	是否挂失	必填，是/否值，默认为“否”

alter table cardinfo
add constraint CK_nIsReportloss check(IsReportloss is not null),
	constraint DF_IsReportloss default '否' for IsReportloss,
	constraint CK_IsReportloss check(IsReportloss in('是','否'))
	
--customerID	顾客编号	外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号

alter table cardinfo
add constraint FK_customerID foreign key(customerID) references userinfo(customerID),
	constraint CK_ncustomerID check(customerID is not null)
	
--③
--交易信息表transinfo

--transDate	交易日期	必填，默认为系统当前日期
--cardID	卡号	必填，外键
--transType	交易类型	必填，只能是存入/支取
--transMoney	交易金额	必填，大于0
--remark	备注	可选，其他说明
alter table transinfo
add constraint CK_Nrd check(transDate is not null),
	constraint DF_td default getdate() for transDate,
	
	constraint FK_cardID foreign key (cardID) references cardinfo(cardID),
	constraint CK_NcardID check(cardID is not null),
	
	constraint CK_NtransType check(transType is not null),
	constraint DF_transType CHECK(transType in('存入','支取')),
	
	constraint CK_NtransMoney check(transMoney is not null),
	constraint DF_transMoney CHECK(transMoney>0)	
--二、插入测试数据
--使用SQL语言向每个表中插入至少3条记录

--1.向userinfo表中插入3条记录
insert into userinfo(customerName,PID,telephone,address) values
			('李白','370404066601021111','15000000000','山东济南'),
			('李大白','370404066601022222','15000000111','山东济宁'),
			('李小白','370404066601023333','15000000222','山东泰安')
--2.向cardinfo表中插入3条记录
insert into cardinfo(cardID,savingTate,openMoney,balance,pass,customerID) values
			('1010 3576 0000 0000','活期',10,2,'000000',1),
			('1010 3576 0000 0001','活期',100,20,'000001',2),
			('1010 3576 0000 0010','活期',1000,200,'000010',3)
--3.向transinfo表中插入3条记录
insert into transinfo(cardID,transType,transMoney) values
('1010 3576 0000 0000','存入',100),
('1010 3576 0000 0001','存入',100),
('1010 3576 0000 0010','支取',100)
--三、模拟常规业务
--1)修改客户密码
--修改卡号为‘1010 3576 0000 0000’的密码为‘000011’
update cardinfo set pass='000011' where cardID='1010 3576 0000 0000'
--2)办理银行卡挂失
--挂失卡号为‘1010 3576 0000 0000’的卡
update cardinfo set IsReportloss='是' where cardID='1010 3576 0000 0000'
--3)统计银行资金流通余额和盈利结算
--银行资金流通余额=总存入金额-总支取金额
--通过调用存储过程计算流通余额
create proc pro_sum
as
begin
declare @v_sum money,@v_inmoney money,@v_omoney money
select @v_inmoney=(select SUM(transMoney) from transinfo where transType='存入')
select @v_omoney=(select SUM(transMoney) from transinfo where transType='支取')
select @v_sum=@v_inmoney-@v_omoney
select '流通余额'=@v_sum
end
go

exec pro_sum
--盈利结算=总支取金额 * 0.008 – 总存入金额 * 0.003
--通过调用存储过程计算盈利结算
create proc profit
as
begin
declare @v_pro money,@v_inmoney money,@v_omoney money
select @v_inmoney=(select SUM(transMoney) from transinfo where transType='存入')
select @v_omoney=(select SUM(transMoney) from transinfo where transType='支取')
select @v_pro=@v_omoney*0.008 -@v_inmoney * 0.003
select '盈利结算'=@v_pro
end
go
exec profit
--4)查询本周开户的卡号，显示该卡相关信息
select * from cardinfo where (DATEDIFF(DAY,GETDATE(),openDate)<DATEPART(WEEKDAY,openDate))
--5)查询本月交易金额最高的卡号
select distinct cardID from transinfo where transMoney=(select MAX(transMoney) from transinfo)
--6)查询挂失账号的客户信息
select * from userinfo where customerID in(select customerID from cardinfo where IsReportloss='是')
--四、利用视图实现数据查询
--1)为客户提供以下3个视图供其查询该客户数据
--2)提供友好界面，要求各列名称为中文描述
--3)调用创建的视图获得查询结果
--客户基本信息：vw_userInfo
create view vw_userInfo 
as select customerID 客户号,customerName 客户名,PID 身份证号,telephone 电话号码,address 地址 from userinfo

select *from vw_userInfo
--银行卡信息：vw_cardInfo
create view vw_cardInfo
as select cardid 银行卡号,curType 货币类型,savingTate 存款类型,openDate 开户日期,openMoney 开户金额,
balance 余额,pass 密码,IsReportloss 是否挂失,customerID 客户号 from cardinfo

select *from vw_cardInfo
--银行卡交易信息：vw_transInfo
create view vw_transInfo
as select transDate 交易日期,cardID 银行卡号,transType 交易类型,transMoney 交易金额,remark 备注 from transinfo

select *from vw_transInfo

--五、用存储过程实现业务处理
--1)完成开户业务

--创建存储过程通过随机数产生卡号
create proc randomcardid
@cardid charoutput
as
select @cardid='1010 3576 '+substring(convert(char(10),RAND()*10000),0,5)+' '+substring(convert(char(10),RAND()*10000),0,5)
go

--创建开户存储过程
create proc openaccount
@curtype char,
@savingTate char ,
@openMoney money,
@customerID int
as
begin
begin tran
if(@openMoney<1)
	begin
		print'开户金额小于一元！'
		rollback tran
	end
else
	begin
		declare @cardid char 
		exec  randomcardid @cardid output
		insert into cardinfo values(@cardid,@curtype,@savingTate,default,@openMoney,@openMoney,default,DEFAULT,@customerID )
		print'开户成功！'
		select *from cardinfo where cardID=@cardid
		commit tran
	end
end
go

--test：开户金额>=1
exec openaccount 'DOLLAR','活期',2,'1'

--test：开户金额<1
exec openaccount 'DOLLAR','活期',0.1,'1'
--开户金额小于一元！

--2)完成取款或存款业务
alter proc sav_get
@v_cardnumber char,
@v_transtype char,
@v_money money,
@v_pass char,
@remark char
as
begin tran
if exists(select *from cardinfo where cardID=@v_cardnumber)
	begin
		if(@v_pass!=(select pass from cardinfo where cardID=@v_cardnumber))
		begin
			raiserror('密码输入错误！',16,1)
			rollback tran
		end

		if(@v_transtype='存入')
		begin
			update cardinfo set balance=balance+@v_money where cardID=@v_cardnumber
			print'存入成功！'
		end
		else if(@v_transtype='支取' )
		begin
			update cardinfo set balance=balance-@v_money where cardID=@v_cardnumber
			if((select balance from cardinfo where cardID=@v_cardnumber)<0)
			begin
				print'余额不足！'
				rollback tran
			end
		end
		insert into transinfo values(GETDATE(),@v_cardnumber,@v_transtype,@v_money,@remark)
	end
commit tran
go
select *from cardinfo
exec sav_get'1010 3576 0000 0000','存入',10,'000011',null
--3)根据卡号打印对账单
create procedure show
@cardnumber char
as
begin
if exists(select*from transinfo where cardID=@cardnumber)
	select *from transinfo where cardID=@cardnumber
else
	print'卡号不存在！'
end
go

exec show '1010 3576 0000 0000'
--4)查询、统计指定时间段内没有发生交易的账户信息
create proc noexchange
@starttime datetime,
@endtime datetime
as
begin
if (@starttime<@endtime)
	select *from cardinfo 
	where cardID not in(select cardID from transinfo where transDate>=@starttime and transDate<=@endtime)
else 
	print'开始时间应早于结束时间！'
end 
go
exec noexchange'2018-12-18','2018-12-19'