weixin_40725706

这个屌丝很懒，什么也没留下！

热门标签

项目名称：银行ATM存取款机系统设计与实现（sql语句模拟操作）_sql 定义身份证号前位必须是数字,后位可以是数字或者x。 --定义联系方式,必须是固

作者：weixin_40725706 | 2024-04-03 14:16:49

踩

sql 定义身份证号前位必须是数字,后位可以是数字或者x。 --定义联系方式,必须是固

项目名称：银行ATM存取款机系统设计与实现

一、创建数据库............................................................................................................. 3

1.1创建数据库....................................................................................................... 3

1.2创建各个数据表及相关的约束....................................................................... 3

1.3添加外键约束和生成数据库关系图............................................................... 6

二、创建触发器和插入测试数据................................................................................. 6

2.1创建级联触发器.............................................................................................. 6

2.2插入数据表的测试数据.................................................................................. 9

三、模拟常规业务....................................................................................................... 12

3.1修改客户密码................................................................................................. 12

3.2办理银行卡挂失............................................................................................ 12

3.3统计银行资金流通余额和盈利结算............................................................ 13

3.4查询本周开户信息........................................................................................ 14

3.5查询本月单次交易金额最高的卡号和总交易金额最高的卡号................ 14

3.6查询挂失客户................................................................................................ 15

3.7催款提醒业务................................................................................................ 15

四、创建、使用视图................................................................................................... 16

4.1输出银行客户记录视图VW_userInfo......................................................... 16

4.2输出银行卡记录视图VW_CardInfo............................................................. 16

4.3输出银行卡交易记录视图VW_TransInfo................................................... 17

4.4根据客户登录名查询该客户账户信息VW_OneUserInfo........................... 18

五、存储过程实现业务处理....................................................................................... 18

5.1完成存款或取款业务.................................................................................... 18

5.2产生随机卡号................................................................................................ 21

5.3完成开户业务................................................................................................ 22

5.4分页显示查询交易数据................................................................................ 24

5.5打印客户对账单............................................................................................ 25

5.6统计未发生交易的账户................................................................................ 27

5.7统计银行卡交易量和交易额........................................................................ 30

六、利用事务实现转账............................................................................................... 34

心得体会：................................................................................................................... 39

源代码：....................................................................................................................... 40

创建数据库

-- 创建数据库BankDB

-- 数据库文件和日志均保存在 D:\20160107SQL项目练习\银行ATM机器系统\

-- 文件大小为5MB增长15% 日志大小为3MB增长5MB

use master

if exists(select * from sysdatabases where name='BankDB')

drop database BankDB

create database BankDB

(

name='BankDB_Data',

filename='D:\20160107SQL\项目练习\银行ATM机器系统\BankDB01_Data.mdf',

size=5mb,

filegrowth=15%

)

log on

(

name='BankDB_Log',

filename='D:\20160107SQL\项目练习\银行ATM机器系统\BankDB01_Log.ldf',

size=3mb,

filegrowth=5mb

)

创建各个数据表及相关的约束

--创建银行业务类型表BankBusinessType

--判断银行业务类型表是否存在,若存在则删除

--创建银行业务类型表，包含银行业务类型编号BBTId主键自动标识符，银行业务类型名称BBTName char(20) not null，银行业务描述BBTComment varchar(100)

use BankDB

if exists(select * from sysobjects where name='BankBusinessType')

drop table BankBusinessType

create table BankBusinessType

(

BBTId int identity(1,1) primary key,

BBTName char(20) not null,

BBTComment varchar(100)

)

--创建银行客户信息表BankCustomer

--判断银行卡客户是否存在,若存在则删除

--创建银行客户信息表，包含客户编号BCID 主键自动标识符，客户姓名BCName char(20) not null，客户身份证BCICNo，客户联系电话BCTel、

客户居住地址BCAddr varchar(100)

--定义身份证号前位必须是数字，后位可以是数字或者X。

--定义联系方式，必须是固定电话号码或者手机号，固定电话号码前位必须是区号，手机号码前面第一位必须是1,第二位必须是[3,5,8,9]

if exists(select * from sysobjects where name='BankCustomer')

drop table BankCustomer

create table BankCustomer

(

--客户编号BCID 主键自动标识符，

BCID int identity(1,1) primary key,

--客户姓名BCName char(20) not null，

BCName char(20) not null,

--客户身份证BCICNo，

BCICNo char(18) not null,

--客户联系电话BCTel

BCTel char(12) not null,

--客户居住地址BCAddr varchar(100)

BCAddr varchar(100)

)

--定义身份证号前位必须是数字，后位可以是数字或者X。

alter table BankCustomer add constraint CK_BCICNo check(BCICNo like

replicate('[0-9]',17)+replicate('[0-9xX]',1))

--定义联系方式，必须是固定电话号码或者手机号，固定电话号码前位必须是区号，

--手机号码前面第一位必须是1,第二位必须是[3,5,8,9]

alter table BankCustomer add constraint CK_BCTel check(BCTel like

'1'+replicate('[3589]',1)+replicate('[0-9]',9) or BCTel

like replicate('[0-9]',3)+'-'+replicate('[0-9]',8) or

BCTel like replicate('[0-9]',4)+'-'+replicate('[0-9]',7))

--建立银行卡信息 BankCard

--判断银行卡是否存在，若存在，则删除银行卡BankCard

-- 卡号 BCNo char(19) 主键必须符合16位数字构成,前8位为1010 3576,后位是随机产生且唯一,每4位必须有一个空格

-- 密码 BCPwd char(6) not null ,开户默认为'888888'

-- 币种 BCCurrency char(5) not null, 默认为RMB类型

-- 存款类型 BCBBTId int not null

-- 开户日期 BCOpenDate datetime not null,默认当日

-- 开户金额 BCOpenAmount money not null ,不得小于1元

-- 是否挂失 BCRegLoss char(2),默认为否

-- 客户编号 BCBCId intnot null,

-- 当前余额 BCExistBalance money not null

if exists(select * from sysobjects where name='BankCard')

drop table BankCard

create table BankCard

(

-- 卡号 BCNo char(19) 主键

BCNo char(19) primary key,

-- 密码 BCPwd char(6) not null ,开户默认为'888888'

BCPwd char(6) not null default('888888'),

-- 币种 BCCurrency char(5) not null, 默认为RMB类型

BCCurrency char(5) not null default('RMB'),

-- 存款类型 BCBBTId int not null

BCBBTId int not null,

-- 开户日期 BCOpenDate datetime notnull,默认当日

BCOpenDate date not null default(convert(varchar(10),getdate(),120)),

-- 开户金额 BCOpenAmount money not null ,不得小于1元

BCOpenAmountmoney not null check(BCOpenAmount>=1),

-- 是否挂失 BCRegLoss char(2),默认为否

BCRegLoss char(2) default('否') check(BCRegLoss='是' or BCRegLoss='否'),

-- 客户编号 BCBCId int not null,

BCBCId int not null,

-- 当前余额 BCExistBalance moneynot null

BCExistBalancemoney not null

)

--卡号必须符合16位数字构成,前8位为1010 3576,

--后位是随机产生且唯一,每4位必须有一个空格

alter table BankCard add constraint CK_BCNo check(BCNo like'1010 3576 '+

replicate('[0-9]',4)+' '+replicate('[0-9]',4))

--创建交易信息表BankDealInfo

--判断交易信息BankDealInfo是否存在,若存在则删除

--交易编号BDNo 为自动增长列主键

--卡号 BDBCNo char(19) not null

--交易日期 BDDealDate Datenot null 默认为当前日期

--交易金额 BDDealAcount money not null

--交易类型，有种存入和支取 BDDealType Char(10) not null

--交易备注 BDDealComment varchar(100)

if exists(select * from sysobjects where name='BankDealInfo')

drop table BankDealInfo

create table BankDealInfo

(

--交易编号BDNo 为自动增长列主键

BDNo int identity(1,1) primary key,

--卡号 BDBCNo char(19) not null

BDBCNo char(19) not null,

--交易日期 BDDealDate Date notnull 默认为当前日期

BDDealDate Date not null default(convert(varchar(10),getdate(),120)),

--交易金额 BDDealAcount money notnull

BDDealAcountmoney not null,

--交易类型，有种存入和支取 BDDealTypeChar(10) not null

BDDealType Char(10) not null check(BDDealType like'存入'

or BDDealType like'支取'),

--交易备注 BDDealCommentvarchar(100)

BDDealCommentvarchar(100)

)

添加外键约束和生成数据库关系图

--建立表之间的外键约束关系

alter table BankCard add constraint FK_BCBBTIdforeign key(BCBBTId)

references BankBusinessType(BBTId)

alter table BankCard add constraint FK_BCBCId foreign key(BCBCId)

references BankCustomer(BCID)

alter table BankDealInfo add constraint FK_BDBCNo foreign key(BDBCNo)

references BankCard(BCNo)

创建级联触发器

2.1.1创建Insert触发器

--在交易信息表中插入一个触发器tr_InsertdealInfo，修改银行卡的存款余额

--判断交易记录里是存入还是支取，及时更新银行卡表的存款余额

if exists(select * from sysobjects where name='tr_InsertdealInfo')

drop trigger tr_InsertdealInfo

create trigger tr_InsertdealInfo on BankDealInfo

for insert

declare @BDDealType Char(10),@BDDealAcount money,@BDBCNo char(19)

select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount,@BDBCNo

=BDBCNo from inserted

if(@BDDealType='存入')

updateBankCard set BCExistBalance+=@BDDealAcount where BCNo=@BDBCNo

else

begin

if(convert(float,(select BCExistBalance from BankCard where

BCNo=@BDBCNo))<@BDDealAcount)

begin

raiserror('本卡的当前余额不足',18,100)

rollbacktran

end

else

updateBankCard set BCExistBalance-=@BDDealAcount where BCNo=@BDBCNo

end

2.1.2创建Delete触发器

--在交易信息表中插入一个触发器tr_DeldealInfo，当删除一个交易信息，修改银行卡的存款余额

if exists(select * from sysobjects where name='tr_DeldealInfo')

drop trigger tr_DeldealInfo

create trigger tr_DeldealInfo on BankDealInfo

instead of delete

declare @BDDealType Char(10),@BDDealAcount money,@BDBCNo char(19)

select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount,@BDBCNo

=BDBCNo from deleted

if(@BDDealType='存入')

begin

if(convert(float,(select BCExistBalance from BankCard where

BCNo=@BDBCNo))<@BDDealAcount)

begin

raiserror('本卡的当前余额不足',18,100)

rollbacktran

end

else

updateBankCard set BCExistBalance-=@BDDealAcount where BCNo=@BDBCNo

end

else

updateBankCard set BCExistBalance+=@BDDealAcount where BCNo=@BDBCNo

2.1.3创建Update触发器

--在交易信息表中插入一个触发器，修改银行卡的存款余额

--判断交易记录里是存入还是支取，及时更新银行卡表的存款余额

if exists(select * from sysobjects where name='tr_updatedealInfo')

drop trigger tr_updatedealInfo

create trigger tr_updatedealInfo on BankDealInfo

for update

declare @oldDealType Char(10),@newDealType Char(10),@oldDealAcount

money,@newDealAcount money,@BDBCNo char(19)

select @BDBCNo=BDBCNo,@oldDealType=BDDealType,@oldDealAcount=

BDDealAcountfrom deleted

select @newDealType=BDDealType,@newDealAcount=BDDealAcount from inserted

if(@oldDealType=@newDealAcount)

begin

if(@oldDealType='存入')

updateBankCard set BCExistBalance+=@newDealAcount-@oldDealAcount

whereBCNo=@BDBCNo

else

begin

if(convert(float,(select BCExistBalance from BankCard where

BCNo=@BDBCNo))<@newDealAcount-@oldDealAcount)

begin

raiserror('本卡的当前余额不足',18,100)

rollbacktran

end

else

updateBankCard set BCExistBalance-=@newDealAcount-@oldDealAcount

whereBCNo=@BDBCNo

end

else

begin

if(@oldDealType='存入')

begin

if(convert(float,(select BCExistBalance from BankCard where

BCNo=@BDBCNo))<@newDealAcount+@oldDealAcount)

begin

raiserror('本卡的当前余额不足',18,100)

rollbacktran

end

else

updateBankCard set BCExistBalance-=@newDealAcount+@oldDealAcount

whereBCNo=@BDBCNo

end

else

begin

updateBankCard set BCExistBalance+=@newDealAcount-@oldDealAcount

whereBCNo=@BDBCNo

end

插入数据表的测试数据

--插入表的测试数据

insert into BankBusinessType

values('活期','无固定存储期,可随时存款,存款金额不限的一种比较灵活的存款'),

('定活俩便','事先不约定存期,一次性存入,一次性取出的存款'),

('通知','不约定存期,支取时需要提前通知银行,约定支取时间和金额方能支取的存款'),

('整存整取1年','整笔存入,到期提取本息'),('整存整取2年','整笔存入,到期提取本息'),

('整存整取3年','整笔存入,到期提取本息'),('零存整取1年',

'事先约定金额,逐月按约定金额存入,到期支付本息'),('零存整取2年',

'事先约定金额,逐月按约定金额存入,到期支付本息'),('零存整取3年',

'事先约定金额,逐月按约定金额存入,到期支付本息'),('自助转账',

'银行ATM存取款机上办理银行卡之间的相互转账')

select * from BankBusinessType

insert into BankCustomer values('张飞','150203197611154224','13088447030',

'包头市昆区宝钢五中'),('关羽','15020319761005123X','0472-2315490',

'包头昆区阿尔丁大街')

select * from BankCustomer

insert into BankCard values('1010 3576 1234 5678','197611',default,1,

'2018-05-28',1000,default,1,1000),('1010 3576 1234 5688','197711',

default,2,'2018-05-28',1500,default,2,1500)

select * from BankCard

insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',500,

'存入','单位1月工资')

insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',1500,'存入',

'单位2月工资')

insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',600,'支取',

'支付宝付款')

insert into BankDealInfo values('1010 3576 1234 5678','2018-05-29',700,'支取',

'刷卡消费')

insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',3000,'存入',

'单位1月工资')

insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',2800,'存入',

'单位2月工资')

insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',1600,'支取',

'支付宝付款')

insert into BankDealInfo values('1010 3576 1234 5688','2018-05-29',900,'支取',

'刷卡消费')

select * from BankDealInfo

select * from BankCard

修改客户密码

--修改客户密码

--根据卡号修改指定2个客户的银行密码，其中第一个客户1010 3576 1234 5678密码修改为123456，第二个客户1010 3576 1234 5688修改为123123.

update BankCardset BCPwd='123456' where BCNo='1010 3576 1234 5678'

update BankCardset BCPwd='123123' where BCNo='1010 3576 1234 5688'

-- 按照以下字段进行显示 '卡号', '密码', '货币类型', '存储类型','开户日期', '开户金额','是否挂失', '客户编号','存款金额'

select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BCBBTId as

存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss

as 是否挂失,BCBCId as 客户编号,BCExistBalance as 存款金额 from BankCard

办理银行卡挂失

--办理银行卡挂失

--卡号为1010 3576 1234 5678的银行卡丢失，申请挂失，要求使用innerjoin语句显示如下图运行结果：

update BankCardset BCRegLoss='是' where BCNo='1010 3576 1234 5688'

select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BBTName as

存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCRegLoss

as 是否挂失,BCBCId as 客户编号,BCExistBalance as 存款金额 from BankCard

inner join BankBusinessType on BCBBTId=BBTId

统计银行资金流通余额和盈利结算

-- 使用存储过程 proc_BanlanceAndProfit,

-- 统计银行资金流通余额和盈利结算

-- 1.获取总存入金额和总支取金额

盈利余额=总存入金额*0.008-总支取金额*0.003

if exists(select * from sysobjects where name='proc_BanlanceAndProfit')

drop procedure proc_BanlanceAndProfit

create procedure proc_BanlanceAndProfit

declare @inMoney money=0,@outMoney money=0,@i int=1,@BDDealType Char(10),

@BDDealAcountmoney

while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

begin

select @BDDealType=BDDealType,@BDDealAcount=BDDealAcount from

BankDealInfowhere BDNo=@i

if(@BDDealType='存入')

set @inMoney+=@BDDealAcount

else if(@BDDealType='支取')

set @outMoney+=@BDDealAcount

set @i+=1

end

print '存入总金额：'+ltrim(str(@inMoney))+'RMB,支取总金额：'+

ltrim(str(@outMoney))+'RMB'

print '盈利余额为：'+ltrim(str(@inMoney*0.008-@outMoney*0.003))

exec proc_BanlanceAndProfit

查询本周开户信息

--查询本周开户信息

select BCNo as 卡号,BCPwd as 密码,BCCurrency as 货币类型,BBTName as

存储类型,BCOpenDate as 开户日期,BCOpenAmount as 开户金额,BCName as

客户姓名,BCExistBalance as 存款金额,BCRegLoss as 是否挂失,(case when

BCRegLoss='否' then '正常账户' else '挂失账户' end) as 账户状态 from

BankCard,BankCustomer,BankBusinessType where BCBBTId=BBTId and BCID=BCBCId

and datediff(ww,BCOpenDate,getdate())=0

查询本月单次交易金额最高的卡号和总交易金额最高的卡号

代码实现：

--查询

--1.本月单次交易金额最高的卡号

select top 1 BDBCNo as 卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额

from BankCard,BankDealInfo,BankCustomer where BDBCNo=BCNo and BCID=BCBCId

and datediff(mm,BCOpenDate,getdate())=0 order by BDDealAcount desc

--2. 总交易金额最高的卡号

select top 1 BDBCNo as 卡号,BCOpenDate as 开户日期,BCOpenAmount as 开户金额

from BankCard,BankDealInfo,BankCustomer where BDBCNo=BCNo and BCID=BCBCId

group by BDBCNo,BCOpenDate,BCOpenAmount order by sum(BDDealAcount) desc

查询挂失客户

--查询挂失客户

--查询挂失账号的客户信息，分别利用子查询in的方式或者内部连接inner join，查询结果如下图所示：

select BCName as 客户姓名,BCTel as 客户电话 from BankCustomer where

BCID in(select BCBCId from BankCard where BCRegLoss='是')

催款提醒业务

--催款提醒业务

--根据某种业务（如代缴电话费、代缴手机费或房贷等）的需要，每个月末，查询出客户账户上余额少于2000元，由银行统一致电催款。

select BCName as 客户姓名,BCTel as 客户电话,BCExistBalance as 客户余额

from BankCustomer inner join BankCard on BCID=BCBCId where BCExistBalance<2000

输出银行客户记录视图VW_userInfo

为向客户提供友好的用户界面，使用T-SQL语句创建下面几个视图，并使用这些视图输出各表信息。

--输出银行客户记录视图VW_userInfo

--显示的列名全为中文，要求先判断该视图是否存在，若存在，则先删除。结果如下图所示：

if exists(select * from sysobjects where name='VW_userInfo')

drop view VW_userInfo

create view VW_userInfo

select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

as 电话号码,BCAddr as 居住地址 from BankCustomer

select * from VW_userInfo

输出银行卡记录视图VW_CardInfo

--(2)输出银行卡记录视图VW_CardInfo

if exists(select * from sysobjects where name='VW_CardInfo')

drop view VW_CardInfo

create view VW_CardInfo

select BCNo as 卡号,BCName as 开户名,BCCurrency as 货币类型,BBTName as

存储类型,BCOpenDate as 开户日期,BCExistBalance as 存款金额,BCPwd as 密码

,BCRegLoss as 是否挂失 from BankCard,BankBusinessType,BankCustomer

where BBTId=BCBBTId and BCID=BCBCId

select * from VW_CardInfo

输出银行卡交易记录视图VW_TransInfo

--输出银行卡交易记录视图VW_TransInfo

--查询该视图，结果如下图所示：

if exists(select * from sysobjects where name='VW_TransInfo')

drop view VW_TransInfo

create view VW_TransInfo

select BDDealDate as 交易日期,BDDealType as 交易类型,BDBCNo as 交易卡号,

BDDealAcountas 交易金额,BDDealComment as 备注 from BankDealInfo

select * from VW_TransInfo

根据客户登录名查询该客户账户信息VW_OneUserInfo

--根据客户登录名查询该客户账户信息VW_OneUserInfo

if exists(select * from sysobjects where name='VW_OneUserInfo')

drop view VW_OneUserInfo

create view VW_OneUserInfo

select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

as 电话号码,BCAddr as 居住地址 from BankCustomer where BCName=system_user

select * from VW_OneUserInfo

完成存款或取款业务

描述：

Ø 根据银行卡号和交易金额实现银行卡的存款和取款业务。

Ø 每一笔存款，取款业务都要计入银行交易账户，并同时更新客户的存款余额。

Ø 如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。

² 检查客户输入的密码是否正确。

² 账户取款金额是否大于当前存款额加1。

要求：

Ø 取款或存款存储过程名为usp_takeMoney。

Ø 编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试，测试数据是张飞的卡号支取100元（密码123456），关羽的卡号存入2100元。

--创建存取款业务的存储过程proc_TakeMoney

--完成存款或取款业务

if exists(select * from sysobjects where name='proc_TakeMoney')

drop procedure proc_TakeMoney

create procedure proc_TakeMoney @bcno char(19),@saleMoney money,@pwd char(6)=null

declare @existBanlance money,@errorCount int =0

begin tran

select @existBanlance=BCExistBalance from BankCard where BCNo=@bcno

--不返回受影响的行数

set nocount on

print '交易前，卡号'+@bcno+'，余额为：'+ltrim(str(@existBanlance))

print '交易正进行，请稍后...'

--如果输入参数@pwd为空，则为存款业务，否则为取款业务

if (@pwd is not null)

begin

--1. 办理取款业务

--判断指定卡号和密码是否存在，若存在，则可以取款，否则不能办理取款业务

--判断取款金额是否小于等于存款余额-1，若条件成立，则可以取款，否则不能取款

if exists(select * from BankCard where BCNo=@bcno and BCPwd=@pwd)

begin

if(convert(money,(select BCExistBalance from BankCard where

BCNo=@bcno and BCPwd=@pwd))-1<=@saleMoney)

raiserror('存款余额不足，不能办理取款业务！！',15,1)

else

begin

set@existBanlance-=@saleMoney

insertinto BankDealInfovalues(@bcno,default,@saleMoney,'支取','取款')

set@errorCount+=@@error

end

else

raiserror('不能办理取款业务！！',15,1)

end

else

begin

--2. 办理存款业务

--判断事务处理里是否有异常，若没有异常，则提交，若有异常，则回滚

--判断该交易为何种类型业务，若是存款，则现有余额等于原有余额加上存款金额

set @existBanlance+=@saleMoney

insert into BankDealInfo values(@bcno,default,@saleMoney,'存入','存款')

set @errorCount+=@@error

end

if @errorCount<>0

begin

rollback tran

raiserror('支取失败！！',18,2)

end

else

commit tran

print '交易成功，交易金额为：'+ltrim(str(@saleMoney))

print '卡号'+@bcno+'，余额为：'+ltrim(str(@existBanlance))

--执行存款存储过程

exec proc_TakeMoney'1010 3576 1234 5678',2100

--执行取款存储过程

exec proc_TakeMoney'1010 3576 1234 5678',100,'123456'

select * from BankCard

select * from BankDealInfo

产生随机卡号

--使用存储过程 Proc_randCardID 产生随机卡号

--随机生成后8位卡号 [rand() 随机数 ]

-- 再把前面的8位补上 '1010 3576'

-- 最后判断卡号是否存在,如果存在则重新生成卡号的过程

if exists(select * from sysobjects where name='proc_randCardId')

drop procedure proc_randCardId

create procedure proc_randCardId @myCardId1varchar(19) output

while(1=1)

begin

declare @num1 char(4),@num2 char(4)

set @num1=convert(int,rand()*10000)

set @num2=convert(int,rand()*10000)

set @num1=replicate(0,4-len(@num1))+@num1

set @num2=replicate(0,4-len(@num2))+@num2

set @myCardId1='1010 3576 '+@num1+' '+@num2

if(@myCardId1 not in(select BCNo from BankCard))

break

end

declare @myCardId1 varchar(19)

exec proc_randCardId@myCardId1 output

print '产生随机卡号为:'+@myCardId1

完成开户业务

描述：

Ø 利用存储过程为客户开设2个银行卡账户，开户时需要提供客户的信息有：开户名、身份证号、电话号码、开户金额、存款类型和地址。客户的信息见表所示：

Ø 为成功开户的客户提供银行卡，且银行卡号唯一。

要求：

Ø 开户的存储过程名为usp_openAccount。

Ø 使用下面的数据执行该存储过程，进行测试：调用此存储过程开户。

--完成开户业务

--创建开户存储过程usp_openAccount，输入参数分别是开户名、身份证号、

--电话号码、开户金额、存款类型和地址

if exists(select * from sysobjects where name='usp_openAccount')

drop procedure usp_openAccount

create procedure usp_openAccount @BCNamechar(20),@BCICNo char(18),

@BCTel char(12),@BCOpenAmount money,@BBTName char(20),@BCAddr varchar(100)

--先判断存款类型是否正确

if exists(select BBTName from BankBusinessType whereBBTName=@BBTName)

begin

declare @errorCount int =0

begin tran

--插入一条客户信息记录

insert into BankCustomer values(@BCName,@BCICNo,@BCTel,@BCAddr)

set @errorCount+=@@error

--得到刚插入的客户信息的编号

declare @BCID int,@BCNo char(19)

select @BCID=max(BCID) from BankCustomer

--插入一条新开银行卡记录

exec proc_randCardId@BCNo output

insert into BankCard values(@BCNo,default,default,(select BBTId from

BankBusinessTypewhere BBTName=@BBTName),default,@BCOpenAmount,default,

@BCID,@BCOpenAmount)

set @errorCount+=@@error

--判断上述事务操作是否有异常

--1.如果错误 '尊敬的客户，开户不成功，所有操作均撤销'

if @errorCount<>0

begin

rollbacktran

raiserror('尊敬的客户，开户不成功，所有操作均撤销！！',18,2)

end

-- 2. 如果成功 '尊敬的客户，开户成功，系统为你产生的

--随机卡号是'+@BCNo

else

begin

print'尊敬的客户，开户成功，系统为你产生的'

print'随机卡号是'+@BCNo

committran

end

else

--错误显示 '尊敬的客户，未能成功开户，存款类型不正确，请重新输入'

raiserror('尊敬的客户，未能成功开户，存款类型不正确，请重新输入',15,1)

--执行开户过程

exec dbo.usp_openAccount'周公旦','150203197510074339','0472-2457890','1200.00',

'定活俩便','内蒙古包头'

exec dbo.usp_openAccount'姬昌','150203197610174339','0472-2457890','1300.00',

'活期','内蒙古包头'

exec dbo.usp_openAccount'白天磊','150207199301252316','18347261609','10000000',

'通知','内蒙古包头'

exec dbo.usp_openAccount'白天帅','150203197510074339','15102274286','1200.00',

'定活俩便','天津'

--显示开户的客户信息和银行卡信息

--'客户编号', '开户名', '身份证号', '电话号码', '居住地址',

--'卡号', '客户', '货币种类', '存款类型', '余额', '密码', '是否挂失'

select BCID as 客户编号,BCName as 开户名,BCICNo as 身份证号,BCTel

as 电话号码,BCAddr as 居住地址 from BankCustomer

select BCNo as 卡号,BCName as 客户,BCCurrency as 货币类型,BBTName as

存款类型,BCExistBalance as 余额,BCPwd as 密码,BCRegLoss as 是否挂失

from BankCard,BankBusinessType,BankCustomer where BBTId=BCBBTId

and BCID=BCBCId

分页显示查询交易数据

--分页显示查询交易数据

--根据指定的页数和每页的记录数分页显示交易数据。要求：

-- 显示第2页的5条信息

if exists(select * from sysobjects where name='usp_PagingDisplay')

drop procedure usp_PagingDisplay

create procedure usp_PagingDisplay @pageint,@info int

select top(@page*@info) * from BankDealInfo where BDNo not in(select

top((@page-1)*@info) BDNo from BankDealInfo)

exec usp_PagingDisplay2,5

打印客户对账单

为某个特定的银行卡号打印指定时间内发生交易的对账单。要求如下：

Ø 存储过程名称是usp_CheckSheet。

Ø 分别采用以下两种方式执行存储过程，结果如下图所示。

² 如果不指定交易时间段，那么打印指定卡号的所有交易记录，如测试命令：exec usp_CheckSheet '1010 3576 1234 5688'

² 如果指定交易时间段，那么打印指定卡号在指定时间内发生的所有交易记录，如测试命令：

--（5）打印客户对账单

--创建客户对账单的存储过程usp_CheckSheet

--判断客户对账单存储过程是否存在，若是存在，则删除

if exists(select * from sysobjects where name='usp_CheckSheet')

drop procedure usp_CheckSheet

create procedure usp_CheckSheet @BCNo char(19),@startTime date=null,

@endTime date=null

declare @BCName char(20),@BCCurrency char(5),@BBTName char(20),

@BCOpenDate date,@i int=0

select @BCName=BCName,@BCCurrency=BCCurrency,@BBTName=BBTName,

@BCOpenDate=BCOpenDate from BankCustomer,BankBusinessType,BankCard

where BCID=BCBCId and BBTId=BCBBTId and BCNo=@BCNo

print '卡号：'+@BCNo

print '姓名：'+@BCName

print '货币：'+@BCCurrency

print '存款类型：'+@BBTName

print '开户日期：'+convert(char,@BCOpenDate)

print '交易日 '+'类型 '+'交易金额 '+'备注'

--打印客户对账单

while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

begin

declare @BDDealDate date,@BDDealType char(10),@BDDealAcount money,

@BDDealCommentvarchar(100)

if(@startTime is null)

set @startTime=dateadd(dd,-convert(int,datename(dd,getdate()))+1,

getdate())

if(@endTime is null)

set @endTime=getdate()

select @BDDealDate=BDDealDate,@BDDealType=BDDealType,@BDDealAcount

=BDDealAcount,@BDDealComment=BDDealComment from BankDealInfo where

BDBCNo=@BCNo and datediff(dd,@startTime,BDDealDate)>=0 and datediff

(dd,@endTime,BDDealDate)<=0 and BDNo=@i

if(@BDDealDate is not null and @BDDealType is not null and @BDDealAcount

is not null and @BDDealComment is not null)

printrtrim(convert(char,@BDDealDate))+' '+rtrim(ltrim(@BDDealType))

+' '+ltrim(convert(char,@BDDealAcount))+' '+@BDDealComment

set @BDDealDate=null

set @BDDealType=null

set @BDDealAcount=null

set @BDDealComment=null

set @i+=1

end

--执行打印客户对账单

exec usp_CheckSheet'1010 3576 1234 5688','2018-05-15','2018-06-15'

exec usp_CheckSheet'1010 3576 1234 5678'

统计未发生交易的账户

查询统计指定时间段内没有发生交易的账户信息。

要求：

存储过程名称是usp_getWithoutTrade。

指定时间段

如果没有指定起始日期，那么自本月1日开始进行统计，如果没有指定终止日期，那么截止到当日为止。

要求采用游标技术打印未发生交易的客户信息，参考客户对账单的代码。

该存储过程的执行结果如下图所示

--统计未发生交易的账户

if exists(select * from sysobjects where name='usp_getWithoutTrade')

drop procedure usp_getWithoutTrade

create procedure usp_getWithoutTrade @startTimedate=null,@endTime date=null

begin

if(@startTime is null)

set @startTime=dateadd(dd,-convert(int,datename(dd,getdate()))+1,

getdate())

if(@endTime is null)

set @endTime=getdate()

declare cur_BankCustomer cursorforward_only forselect distinct

BCID,BCName,BCICNo,BCTel,BCAddr from BankCustomer inner join

BankCard on BCID=BCBCId where BCNo not in(select BDBCNo

from BankDealInfo where datediff(dd,@endTime,BDDealDate)<=0

and datediff(dd,@startTime,BDDealDate)>=0)

open cur_BankCustomer

declare @i int=0,@BCExistBalance money,@sumMoney money=0

print '客户编号 '+'客户姓名 '+'身份证号 '+' 电话号码 '+' 地址'

while(1=1)

begin

declare @BCID int,@BCName char(20),@BCICNo char(18),@BCTel char(12),

@BCAddr varchar(20)

fetch next from cur_BankCustomer into@BCID,@BCName,@BCICNo,@BCTel,@BCAddr

if @@fetch_status<>0

break

print convert(char(11),@BCID)+rtrim(@BCName)+' '+@BCICNo+' '+@BCTel+

' '+@BCAddr

set @i+=1

select @BCExistBalance=BCExistBalance from BankCard where BCBCId=@BCID

set @sumMoney+=@BCExistBalance

end

close cur_BankCustomer

deallocate cur_BankCustomer

print '统计为发生交易的人数：'+ltrim(str(@i))+'人,客户总余额为：'+

ltrim(str(@sumMoney))+'元'

end

exec usp_getWithoutTrade'2018-05-28','2018-05-28'

统计银行卡交易量和交易额

统计指定时间段内某地区客户在银行卡交易量和交易额，如果不指定地区，则查询所有客户的交易量和交易额。

要求：

Ø 存储过程名称是usp_getTradeInfo。

Ø 指定时间段和客户所在区域

如果没有指定起始日期，那么自当年1月1日开始统计，如果没有指定终止日期，那么以当日作为截止日。

如果没有指定地点（根据客户所在地址查询），如北京，那么统计全部客户的交易量和交易额。

--(7)统计银行卡交易量和交易额

--统计指定时间段内某地区客户在银行卡交易量和交易额，如果不指定地区，则查询所有客户的交易量和交易额。

if exists(select * from sysobjects where name='usp_getTradeInfo')

drop procedure usp_getTradeInfo

create procedure usp_getTradeInfo @startTimedate=null,@endTime date=null,

@addr varchar(100)=null

declare @inCount int=0,@outCount int=0,@inMoney money=0,@outMoney money=0,

@sumInMoney money=0,@sumOutMoney money=0,@i int=1

while(@i<=convert(int,(select max(BDNo) from BankDealInfo)))

begin

if(@startTime is null)

set @startTime=dateadd(mm,-convert(int,datename(mm,getdate()))

+1,dateadd(dd,-convert(int,datename(dd,getdate()))+1,getdate()))

if(@endTime is null)

set @endTime=getdate()

if(@addr is null)

begin

select@inCount=count(*) from BankDealInfo where BDDealType='存入'

and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

BDDealDate)<=0

select@inMoney=BDDealAcount from BankDealInfo where BDDealType

='存入' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

and datediff(dd,@endTime,BDDealDate)<=0

set @sumInMoney+=@inMoney

select@outCount=count(*) from BankDealInfo where BDDealType='支取'

and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

BDDealDate)<=0

select@outMoney=BDDealAcount from BankDealInfo where BDDealType

='支取' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

and datediff(dd,@endTime,BDDealDate)<=0

set @sumOutMoney+=@outMoney

end

else

begin

select@inCount=count(*) from BankDealInfo where BDDealType='存入'

and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

BDDealDate)<=0 and BDBCNo in(select BCNo from BankCard where

BCBCIdin(select BCID from BankCustomer where BCAddr=@addr))

select@inMoney=BDDealAcount from BankDealInfo where BDDealType

='存入' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

and datediff(dd,@endTime,BDDealDate)<=0 and BDBCNo in(select BCNo

from BankCard where BCBCId in(select BCID from BankCustomer

whereBCAddr=@addr))

set @sumInMoney+=@inMoney

select@outCount=count(*) from BankDealInfo where BDDealType='支取'

and datediff(dd,@startTime,BDDealDate)>=0 and datediff(dd,@endTime,

BDDealDate)<=0 and BDBCNo in(select BCNo from BankCard where

BCBCIdin(select BCID from BankCustomer where BCAddr=@addr))

select@outMoney=BDDealAcount from BankDealInfo where BDDealType

='支取' and BDNo=@i and datediff(dd,@startTime,BDDealDate)>=0

and datediff(dd,@endTime,BDDealDate)<=0 and BDBCNo in(select BCNo

from BankCard where BCBCId in(select BCID from BankCustomer

whereBCAddr=@addr))

set @sumOutMoney+=@outMoney

end

set @i+=1

end

print '---------------------------------------------------------------'

print '统计银行卡交易量和交易额'

print '起始日期：'+convert(char(21),@startTime)+'截止日期：'+convert(char,@endTime)

print '存入笔数：'+ltrim(str(@inCount))+' 存入金额：'

+ltrim(str(@sumInMoney))

print '支出笔数：'+ltrim(str(@outCount))+' 支出金额：'

+ltrim(str(@sumOutMoney))

print '交易总笔数：'+ltrim(str(@inCount+@outCount))

print '结余金额：'+ltrim(str(@sumInMoney-@sumOutMoney))

print '---------------------------------------------------------------'

exec usp_getTradeInfo

exec usp_getTradeInfo@addr='包头市昆区宝钢五中'

exec usp_getTradeInfo'2018-05-1','2018-05-28'

exec usp_getTradeInfo'2018-05-1','2018-12-30','包头市昆区宝钢五中'

exec usp_getTradeInfo@addr='内蒙古包头'

利用事务实现转账

使用存储过程和事务实现转账业务，操作步骤如下所示：

(1) 从某一个账户支取一定金额的存款。

(2) 将支取金额存入到另一个指定的账户中。

(3) 分别打印此笔业务的转出账单和转入账单。

要求：

(1) 存储过程名称是usp_transfer。

(2) 要求使用事务机制实现转账业务。

if exists(select * from sysobjects where name='usp_transfer')

drop procedure usp_transfer

create procedure usp_transfer @outBCNochar(19),@inBCNo char(19),@saleMoney money

declare @errorCount int =0,@outBCExistBalance money,@inBCExistBalance money

begin tran

--不返回受影响的行数

set nocount on

insert into BankDealInfo values(@outBCNo,default,@saleMoney,'支取','通过存储')

set @errorCount+=@@error

insert into BankDealInfo values(@inBCNo,default,@saleMoney,'存入','通过存储')

set @errorCount+=@@error

if @errorCount<>0

begin

rollback tran

raiserror('转账失败！！',18,2)

end

else

begin

commit tran

print '开始转账，请稍后...'

print '交易正进行，请稍后...'

print '交易成功，交易金额：'+ltrim(str(@saleMoney))

select @outBCExistBalance=BCExistBalance from BankCard where

BCNo=@outBCNo

print '卡号：'+@outBCNo+' 余额：'+ltrim(str(@outBCExistBalance))

select @inBCExistBalance=BCExistBalance from BankCard where

BCNo=@inBCNo

print '卡号：'+@inBCNo+' 余额：'+ltrim(str(@inBCExistBalance))

declare @outBCName char(20),@outBCCurrency char(5),@outBBTName

char(20),@outBCOpenDate date

select @outBCName=BCName from BankCustomer where BCID=(select BCBCId from

BankCard where BCNo=@outBCNo)

select @outBCCurrency=BCCurrency,@outBCOpenDate=BCOpenDate from

BankCard where BCNo=@outBCNo

select @outBBTName=BBTName from BankBusinessType whereBBTId=(select

BCBBTId from BankCard where BCNo=@outBCNo)

print '打印出转出对账单'

print '---------------------------------------------------------------'

print '卡号：'+@outBCNo

print '货币类型：'+@outBCCurrency

print '存款类型：'+@outBBTName

print '开户日期：'+convert(char,@outBCOpenDate)

print '交易日 '+' 类型 '+'交易金额 '+'备注'

print convert(char(10),getdate(),120)+' 支取 '+ltrim(str(@saleMoney))+

' 通过存储'

declare @inBCName char(20),@inBCCurrency char(5),@inBBTName

char(20),@inBCOpenDate date

select @inBCName=BCName from BankCustomer where BCID=(select BCBCId from

BankCard where BCNo=@inBCNo)

select @inBCCurrency=BCCurrency,@inBCOpenDate=BCOpenDate from

BankCard where BCNo=@inBCNo

select @inBBTName=BBTName from BankBusinessType whereBBTId=(select

BCBBTId from BankCard where BCNo=@inBCNo)

print '打印出转入对账单'

print '---------------------------------------------------------------'

print '卡号：'+@inBCNo

print '货币类型：'+@inBCCurrency

print '存款类型：'+@inBBTName

print '开户日期：'+convert(char,@inBCOpenDate)

print '交易日 '+' 类型 '+'交易金额 '+'备注'

print convert(char(10),getdate(),120)+' 存入 '+ltrim(str(@saleMoney))+

' 通过存储'

print '---------------------------------------------------------------'

end

exec usp_transfer'1010 3576 1234 5678','1010 3576 1234 5688',100

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/355845