数据库技术与应用课程设计银行ATM存取款系统

《数据库技术与应用》

课程设计报告

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

姓 名： 专 业： 指导教师：

完成日期：2015年11月21日

内蒙古科技大学课程设计任务书

课程名称 设计题目 指导教师 数据库技术与开发 银行ATM存取款机系统设计与实现 时间 2015年11月20日 一、教学要求 1、培养学生运用所学课程《SQL Server 2012》的理论知识和技能以及对数据库组织、管理和使用能力。 2、掌握数据库应用系统的整个设计过程（包括需求分析、概念结构设计、逻辑结构设计与物理设计与实施）以及运用Transact-SQL编写代码 二、设计资料及参数 根据问题分析，明确银行ATM存取款系统的实体、实体属性及实体之间的关系： 在充分理解银行业务需求后，围绕银行的需求进行分析，确认与银行ATM存取款有紧密关系的实体，并得到每个实体的必要属性。 根据业务，分析多个实体之间的关系。实体之间的关系可以是一对一、一对多和多对多。 三、设计要求及成果 1.创建数据库、创建表、创建约束 2.使用触发器和插入测试数据 3.模拟常规业务、创建视图 4.使用存储过程实现业务处理 5.利用事务实现较复杂的数据更新 6.使用视图简化复杂的数据查询 7.使用游标技术实现结果集的行集操作 四、进度安排 用Power designer绘制CDM模型、生成PDM模型（1天） 用T-SQL进行编程（2天） 修改优化课程设计代码（2天） 编写课程设计说明书和验收（2天） 五、评分标准 1.实训文档（30%） 2.实训项目完成的质量（50%） 3.平日考勤（20%） 4.成绩采用五级分制评定 六、建议参考资料 1.《数据库设计规范》， 2.《数据库系统教程》，施伯乐，高等教育出版社 3.《数据库系统概论》，王珊，高等教育出版社 4. 《SQL Server 2012》，王英英，清华大学出版社

I

目录

内蒙古科技大学课程设计任务书 ............................................................................................ I 第一章

需求分析 ................................................................................................................ 1

1.1 项目任务 .......................................................................................................................... 1 1.2 项目技能目标 .................................................................................................................. 1 1.3 需求概述 .......................................................................................................................... 1 1.4 开发环境 .......................................................................................................................... 1 1.5 问题分析 .......................................................................................................................... 1

第二章 项目实训内容 ........................................................................................................ 4

2.1 实训一：制定《数据库设计与编程规范》 .................................................................. 4 2.2 实训二：数据库分析设计与建模 .................................................................................. 4 2.3 实训三：创建数据库 ...................................................................................................... 6 2.4 实训四：创建触发器和插入测试数据 ........................................................................ 10 2.4 实训五：模拟常规业务 ................................................................................................ 19 2.6 实训六：创建、使用视图 ............................................................................................ 24 2.7 实训七：存储过程实现业务处理 ................................................................................ 27 2.8 实训八：利用事务实现转账 ........................................................................................ 41

第三章 第四章

项目心得体会 ...................................................................................................... 46 对《我的租房网》进行优化 .............................................................................. 47

附页 ......................................................................................................................................... 57

II

第一章 需求分析

1.1 项目任务

创建数据库、创建表、创建约束 使用触发器和插入测试数据 模拟常规业务、创建视图 使用存储过程实现业务处理 利用事务实现较复杂的数据更新

1.2 项目技能目标

使用T-SQL语句创建数据库、表和各种约束。 使用T-SQL语句编程实现常见业务。 使用触发器实现多表之间的级联更新。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。

使用游标技术实现结果集的行集操作。

1.3 需求概述

某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。

要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。

1.4 开发环境

数据库：SQL SERVER 2008开发版

1.5 问题分析

该项目的ATM存取款机业务如下： (1) 银行存取款业务介绍

银行为客户提供了各种银行存取款业务。详见表1.1

1

表1.1银行存取款业务

业务 活期 定活两便 通知 整存整取 零存整取 自助转账 描述 无固定存期，可随时存取，存取金额不限的一种比较灵活的存款 事先不约定存期，一次性存入，一次性支取的存款 不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款 选择存款期限，整笔存入，到期提取本息的一种定期储蓄。银行提供的存款期限有1年、2年和3年 一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。银行提供的存款期限由1年、2年和3年 在ATM存取款机上办理同一币种账户的银行卡之间互相划转 (2) 客户信息

每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表1.2所示：

表1.2开设银行卡账户的客户信息 数据 姓名 身份证号 联系电话 必须提供 唯一确定客户，如果是二代身份证，则是由17位数字和1位数字或者字符X构成。如果是一代身份证，则身份证号全部是15位数字。 分为座机号码和手机号码：座机号码由数字和“-”构成，由以下两种格式：XXX-XXXXXXXX或者XXXX-XXXXXXX。手机号码由11位数字构成，且前2位必须是13或者15开头。 可以选择 描述 居住地址

(3) 银行卡账户信息

银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表1.3所示：

表1.3 银行卡账户信息 数据 卡号 描述 银行的卡号由16位数字组成，其中：一般前8位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为1010 3576 XXXX XXXX，后8位必须是随机产生且唯一，每4位号码后有空格。 由6位数字构成，开户时默认为“888888” 默认为RMB，目前该银行尚未开设其他币种存款业务。 必须选择 客户开设银行卡账户的日期，默认为当日 客户开设银行卡账户时存入的金额，规定不得小于1元。 默认为“否” 密码 币种 存款类型 开户日期 开户金额 是否挂失 客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。银行规定，每个账户当前的存款金额不得小于1元。

(4) 银行卡交易信息

银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表1.4所示：

2

表1.4银行卡交易信息

数据 卡号 交易日期 交易金额 交易类型 备注 银行的卡号由16位数字组成 默认为当日 必须大于0元 包括：存入和支取两种 对每笔交易做必要的说明 描述 (5) 银行卡手工账户和存取款单据信息

该银行要求这套软件实现银行客户的开户、存款、取款、转账和余额查询等业务，使得银行储蓄业务方便、快捷，同事保证银行业务数据的安全性。

为使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的样本数据，以供项目开发时参考，参加图1.1和图1.2。

图1.1银行卡手工账户样本数据

图1.2存取款单据样本数据

3

第二章 项目实训内容

2.1 实训一：制定《数据库设计与编程规范》

1.参考技术文档：《数据库设计规范 (1)》、《数据库设计规范(修订)》、《数据库设计及编写规范》、《编程规范(T-SQL)》、《Transact-SQL_数据库编程命名规范》、《SQL_Server数据库编程规范》等技术文档，

2.制定一份10-15页，不少于3000字的《数据库设计与SQL编程规范》，要求至少包含各个数据库对象的命名规范、编程规范及注释规范。

该实训项目的设计与编程要求遵循该实训制定的《数据库设计与编程规范》。 3.《数据库设计与SQL编程规范》见附页2

2.2 实训二：数据库分析设计与建模

(1)分析银行ATM存取款系统实体

根据问题分析，明确银行ATM存取款系统的实体、实体属性及实体之间的关系：

1.在充分理解银行业务需求后，围绕银行的需求进行分析，确认与银行ATM存取款有紧密关系的实体，并得到每个实体的必要属性。

2.根据业务，分析多个实体之间的关系。实体之间的关系可以是一对一、一对多和多对多。

3.要求使用Visio绘制出ATM存取款系统ER图。 4.参考资料：《ER设计》、《基本ER图要点》 创建如下图2.1所示ER模型图：

图2.1银行存取款系统数据ER图模型

4

(2)规范数据库结构设计

1.使用数据库设计范式对数据库表结构进行规范优化，规范数据库的表结构同时，要考虑软件运行性能。必要时，可以有悖于第三范式的要求，适当增加冗余数据、减少表间链接，以空间换取时间。

2.如所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

3.如在银行客户表BankCustomer中，不能将客户信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。

(3)安装并演示Power designer建模示例

参考《PowerDesigner系统分析与建模》，安装PowerDesigner及破解步骤、演示创建数据库概念模型CDM、数据库物理模型PDM和自动生成T-SQL语句等操作。

(4)绘制CDM模型、生成PDM模型和生成T-SQL语句

使用PowerDesigner，把设计数据库第一步结果（即分析得到的银行ATM存取款系统的实体、实体属性、以及实体之间的关系）用CDM表示，要求CDM要体现出各实体之间的关系以及各实体的实体属性。

创建如下图2.2所示的数据库概念模型：

银行卡#银行卡号Characters (19)*密码Characters (6)*货币类型Characters (5)*开户日期Date*开户金额Money*是否挂失Characters (10)o卡内余额Money...客户-银行卡银行卡-交易信息ooooo客户信息IntegerCharacters (20)Characters (18)Characters (20)Variable characters (100)交易编号交易日期交易金额交易类型交易备注...银行交易信息IntegerDateMoneyCharacters (10)Variable characters (100)业务类型#业务编号Integer*业务名称Characters (20)o业务描述Variable characters (100)业务类型-银行卡#oooo客户编号客户姓名身份证号客户电话客户住址...

图2.2银行存取款系统数据概念模型CDM

检查所创建CDM模型无误后，自动生成相应的CDM设计文档和银行存取款系统数据物理模型PDM。PDM模型如图2.3所示：

5

银行卡业务编号客户编号银行卡号密码货币类型开户日期开户金额是否挂失卡内余额...intintchar(19)char(6)char(5)datetimemoneychar(10)moneyFK_BANKCARD_BBT-BC_BANKBUSI业务类型业务编号int业务名称char(20)业务描述varchar(100)FK_BANKCARD_BC-BC_BANKCUSTFK_BANKDEAL_BC-BD_BANKCARD业务编号客户编号银行卡号交易编号交易日期交易金额交易类型交易备注...银行交易信息intintchar(19)intdatetimemoneychar(10)varchar(100)客户信息客户编号客户姓名身份证号客户电话客户住址...intchar(20)char(18)char(20)varchar(100) 图2.3银行存取款系统数据物理模型PDM

2.3 实训三：创建数据库

(1)创建数据库

1.使用Create DataBase语句创建“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下（建立一个文件夹，用于存放该实训项目的所有相关T-SQL源文件）， 文件增长率为15%。 2.创建数据库的代码如下：

--创建BankDB数据库，数据库文件和日志文件均保存在 --文件夹D:\\数据库设计与开发\\2015年工程案例项目\\银行ATM存取款机系统下 --文件增长率均为15%，数据文件起始大小为5MB，日志文件起始大小为2MB create database BankDB on primary ( name=N'BankDB', filename=N'D:\\数据库设计与开发\\2015年工程案例项目\\银行ATM存取款机系统\\BankDB.mdf', size=5MB, filegrowth=15% ) log on ( name=N'BankDB_log', filename=N'D:\\数据库设计与开发\\2015年工程案例项目\\银行ATM存取款机系统\\BankDB_log.ldf', size=2MB, filegrowth=15% 6

) go

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

创建银行业务类型表BankBusinessType 银行业务信息表如下：

--判断银行业务类型表BankBusinessType是否存在,若存在,则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankBusinessType')) drop table BankBusinessType --创建银行业务类型表BankBusinessType，包含银行业务类型编号BBTId，银行业务类型名称BBTName，银行业务描述BBTComment create table BankBusinessType ( --定义银行业务类型编号,主键,标识列从1开始递增 BBTId int identity(1,1) primary key, --定义银行业务类型名称,非空 BBTName char(20) not null, --定义银行业务描述 BBTComment varchar(100) ); go

创建银行卡客户表BankCustome 银行卡客户表如下：

--判断银行卡客户表BankCustomer是否存在,若存在,则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankCustomer')) drop table BankCustomer --创建银行客户信息表BankCustomer，包含客户编号BCID，客户姓名BCName，客户身份证BCICNo，客户联系电话BCTel、客户地址BCAddr create table BankCustomer ( --定义客户编号,主键,标识列从1开始递增 BCId int identity(1,1) primary key, --定义客户姓名,非空 BCName char(20) not null, --定义客户身份证号前17位必须是数字，后1位可以是数字或者X,非空 BCICNo char(18) not null check(left(BCICNo,17) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and (right(BCICNo,1) like '[0-9]' or right(BCICNo,1) like 'X')), --定义客户联系电话，必须是固定电话号码或者手机号，座机号码由数字和“-”构成，由以下两种格式：XXX-XXXXXXXX或者XXXX-XXXXXXX， --手机号码由11位数字构成，且前2位必须是13或者15或者18开头,非空 BCTel varchar(20) not null 7

check(BCTel like '[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or BCTel like '[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or BCTel like '1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --定义客户地址 BCAddr varchar(100) ); go

创建银行卡账户信息表BankCard 银行卡账户信息表如下：

--判断银行卡账户信息表BankCard是否存在，若存在，则删除 if exists(select * from sysobjects where id=object_id(N'BankCard')) drop table BankCard /*创建银行卡账户信息表BankCard,包含银行卡卡号BCNo,银行卡密码BCPwd,币种BCCurrency,存款类型BCBBTId, 开户日期BCOpenDate,开户金额BCOpenAmount,是否挂失BCRegLoss,客户编号BCBCId，余额BankCardBCExistBalance*/ create table BankCard ( --定义银行卡卡号,必须符合位数字构成,前位为1010 3576,后位是随机产生且唯一,每4位必须有一个空格,主键 BCNo char(19) primary key check(BCNo like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), --定义银行卡密码,开户默认为“888888”，非空 BCPwd char(6) not null default('888888'), --定义币种,默认为RMB类型，非空 BCCurrency char(5) not null default('RMB'), --定义存款类型，非空 BCBBTId int not null, --定义开户日期,默认当日，非空 BCOpenDate date not null default(getdate()), --定义开户金额,不得小于1元，非空 BCOpenAmount money not null check(BCOpenAmount>=1), --定义是否挂失,默认为“否” BCRegLoss char(2) default(N'否'), --定义客户编号，非空 BCBCId int not null, --定义银行卡账户余额，非空 BCExistBalance money not null ); go

创建银行卡交易信息表BankDealInfo 银行卡交易信息表如下：

--判断交易信息BankDealInfo是否存在,若存在则删除 if exists(select * from sysobjects where id=OBJECT_ID(N'BankDealInfo')) drop table BankDealInfo --创建银行卡交易信息表BankDealInfo，包含交易编号BDNo，银行卡卡号8

BDBCNo，交易日期BDDealDate，交易金额BDDealAcount，交易类型BDDealType，交易备注BDDealComment create table BankDealInfo ( --定义交易编号，标识列从1开始增长，主键 BDNo int identity(1,1) primary key, --定义银行卡卡号，非空 BDBCNo char(19) not null, --定义交易日期,默认为当日，非空 BDDealDate Date not null default(getdate()), --定义交易金额,必须大于0元，非空 BDDealAcount money not null check(BDDealAcount>0), --定义交易类型，有两种:存入和支取，非空 BDDealType Char(10) not null check(BDDealType='存入' or BDDealType='支取'), --定义交易备注 BDDealComment varchar(100) ); go

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

添加子表外键约束及生成数据库关系图

1.在银行卡账户信息表BankCard中BCBBTId字段上添加外键约束fk_BC_BBT 添加约束的代码如下：

--在表BankCard中的BCBBTId字段上添加外键约束fk_BC_BBT alter table BankCard add constraint fk_BC_BBT foreign key(BCBBTId) references bankbusinesstype(BBTId);

2.在银行卡账户信息表BankCard中BCBCId字段上添加外键约束fk_BC_BC 添加约束的代码如下：

--在表BankCard中的BCBCId字段上添加外键约束fk_BC_BC alter table BankCard add constraint fk_BC_BC foreign key(BCBCId) references BankCustomer(BCId);

3.在银行卡交易信息表BankDealInfo中BDBCNo字段上添加外键约束fk_BDI_BC 添加约束的代码如下：

--在表BankDealInfo中的BDBCNo字段上添加外键约束fk_BDI_B alter table BankDealInfo add constraint fk_BDI_BC foreign key(BDBCNo) references BankCard(BCNo);

在SQL SERVER里自动生成数据库关系图，如图2.4所示：

9

图2.4 BankDB数据库关系图

2.4 实训四：创建触发器和插入测试数据

(1)创建级联触发器

1.创建tr_InsertDealInfo触发器

在交易信息表BankDealInfo中创建一个tr_InsertDealInfo触发器，当增加一条交易信息时，修改相应银行卡的存款余额。使用游标，实现批量增加的级联更新。 创建触发器的代码如下：

--判断触发器tr_InsertdealInfo是否存在，若存在，则删除 if (object_id('tr_InsertdealInfo','tr') is not null) drop trigger tr_InsertdealInfo go --在表bankdealinfo上创建一个触发器tr_InsertdealInfo create trigger tr_InsertdealInfo on bankdealinfo for insert as declare @type char(10),@sum money,@BDBCNo char(19); --创建一个游标cursor_BankDealinfo，指向inserted表 declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted --打开游标 open cursor_BankDealinfo --取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin --判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') update bankcard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; 10

if(rtrim(ltrim(@type))='支取') update bankcard set BCExistBalance=BCExistBalance-@sum where BCNo=@BDBCNo; fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo end --关闭游标 close cursor_BankDealinfo --释放游标 deallocate cursor_BankDealinfo go

2.创建tr_DeldealInfo触发器

在交易信息表创建一个tr_DeldealInfo触发器，当删除一条交易信息时，修改相应银行卡的存款余额。使用游标，实现批量删除的级联更新。 创建触发器的代码如下：

--判断触发器tr_DeldealInfo是否存在，若存在，则删除 if (object_id('tr_DeldealInfo','tr') is not null) drop trigger tr_DeldealInfo go --在表bankdealinfo上创建一个触发器tr_InsertdealInfo create trigger tr_DeldealInfo on bankdealinfo for delete as declare @type char(10),@sum money,@BDBCNo char(19); --创建一个游标cursor_BankDealinfo，指向deleted表 declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted --打开游标 open cursor_BankDealinfo --取游标中各个字段的值复制给各个变量 fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin --判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') update bankcard set BCExistBalance=BCExistBalance-@sum where BCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') update bankcard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; fetch next from cursor_BankDealinfo into @type,@sum,@BDBCNo end --关闭游标 close cursor_BankDealinfo --释放游标 deallocate cursor_BankDealinfo go

11

3.创建tr_UpdatedealInfo触发器

在交易信息表创建一个tr_UpdatedealInfo触发器，当更新一条交易信息时，修改相应银行卡的存款余额。使用游标，实现批量删除的级联更新。 创建触发器的代码如下：

--在交易信息表中插入一个触发器，使用游标,当更新一个交易信息，修改银行卡的存款余额 if (object_id('tr_UpdatedealInfo','tr') is not null) drop trigger tr_UpdatedealInfo go create trigger tr_UpdatedealInfo on BankDealInfo for update as declare @type char(10),@sum money,@BDBCNo char(19); --先进行删除操作 --创建一个游标，指向deleted表 declare cursor_delete_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted --打开游标 open cursor_delete_BankDealinfo fetch next from cursor_delete_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin --判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') update BankCard set BCExistBalance=BCExistBalance-@sum where BCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') update BankCard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; fetch next from cursor_del_BankDealinfo into @type,@sum,@BDBCNo end close cursor_delete_BankDealinfo deallocate cursor_delete_BankDealinfo --再进行增加操作 --创建一个游标，指向inserted表 declare cursor_insert_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted --打开游标 open cursor_insert_BankDealinfo fetch next from cursor_insert_BankDealinfo into @type,@sum,@BDBCNo while @@fetch_status=0 begin --判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') update BankCard set BCExistBalance=BCExistBalance+@sum where BCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') update BankCard set BCExistBalance=BCExistBalance-@sum 12

('1010 3576 1234 5678','2015-11-01','600.00','支取','支付宝付款'), ('1010 3576 1234 5678','2015-11-08','700.00','支取','刷卡消费'), ('1010 3576 1234 5678','2015-11-09','3000.00','存入','单位1月工资'), ('1010 3576 1234 5678','2015-11-11','2800.00','存入','单位2月工资'), ('1010 3576 1234 5678','2015-11-25','1600.00','支取','支付宝付款'), ('1010 3576 1234 5678','2015-11-23','900.00','支取','刷卡消费'), ('1010 3576 1234 5688','2015-11-03','500.00','存入','单位1月工资'), ('1010 3576 1234 5688','2015-11-04','1500.00','存入','单位2月工资'), ('1010 3576 1234 5688','2015-11-01','600.00','支取','支付宝付款'), ('1010 3576 1234 5688','2015-11-08','700.00','支取','刷卡消费'), ('1010 3576 1234 5688','2015-11-09','3000.00','存入','单位1月工资'), ('1010 3576 1234 5688','2015-11-11','2800.00','存入','单位2月工资'), ('1010 3576 1234 5688','2015-11-25','1600.00','支取','支付宝付款'), ('1010 3576 1234 5688','2015-11-23','900.00','支取','刷卡消费'), ('1010 3576 1234 5689','2015-11-03','500.00','存入','单位1月工资'), ('1010 3576 1234 5689','2015-11-04','1500.00','存入','单位2月工资'), ('1010 3576 1234 5689','2015-11-01','600.00','支取','支付宝付款'), ('1010 3576 1234 5689','2015-11-08','700.00','支取','刷卡消费'), ('1010 3576 1234 5689','2015-11-09','3000.00','存入','单位1月工资'), ('1010 3576 1234 5689','2015-11-11','2800.00','存入','单位2月工资'), ('1010 3576 1234 5689','2015-11-25','1600.00','支取','支付宝付款'), ('1010 3576 1234 5689','2015-11-23','900.00','支取','刷卡消费') select * from BankDealInfo 测试数据如下图2.8

18

图2.8

2.4 实训五：模拟常规业务

(1)修改客户密码

根据卡号修改指定2个客户的银行密码，其中第一个客户1010 3576 1234 5678密码修改为123456，第二个客户1010 3576 1234 5688修改为123123，并使用Select查询语句显示效果图：

修改数据代码如下：

--修改客户密码 update BankCard set BCPwd='123456' where BCNo='1010 3576 1234 5678'; select * from BankCard where BCNo='1010 3576 1234 5678' update BankCard set BCPwd='123123' where BCNo='1010 3576 1234 5688'; select * from BankCard where BCNo='1010 3576 1234 5688' 测试数据如下图2.9

图2.9

(2)办理银行卡挂失

卡号为1010 3576 1234 5678的银行卡丢失，申请挂失，使用inner join语句显示运行结果：

19

修改数据代码如下：

--办理银行卡挂失 update BankCard set BCRegLoss='是' where BCNo='1010 3576 1234 5678'; select BCNo as '卡号', BCPwd as '密码', BCCurrency as '货币类型', BBTName as '储蓄类型', BCOpenDate as '开户日期', BCOpenAmount as '开户金额', BCRegloss as '是否挂失', BCName as '客户姓名', BCExistBalance as '存款余额' from ((BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId) inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId) 测试数据如下图2.10

图2.10

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

存入代表资金流入，支取代表资金流出。

计算公式：资金流通余额=总存入金额-总支取金额 假定存款利率为千分之三，贷款利率为千分之八。

计算公式：盈利结算=(总存入金额-总支取金额)*0.008-总存入金额*0.003。 创建一个存储过程proc_staticsBanlanceAndProfit，执行该存储过程运行结果 创建存储过程的代码如下：

--统计银行资金流通余额和盈利结算 if exists (select * from sysobjects where name='proc_staticsBanlanceAndProfit') drop procedure proc_staticsBanlanceAndProfit go create procedure proc_staticsBanlanceAndProfit as declare @InMoney money,@OutMoney money --获取总存入金额和总支取金额 select @InMoney=sum(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType))='存入' select @OutMoney=sum(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType))='支取'; print '存入总金额：'+rtrim(ltrim(str(@InMoney)))+' RMB，支取总金额：'+rtrim(ltrim(str(@OutMoney)))+' RMB，银行流通余额：20

'+rtrim(ltrim(str(@InMoney-@OutMoney)))+' RMB' print '盈利余额为：'+rtrim(ltrim(str((@InMoney-@OutMoney)*0.008-@InMoney*0.003)))+' RMB' go exec proc_staticsBanlanceAndProfit 测试数据如下图2.11

图2.11

(4)查询本周开户信息

查询本周开户的卡号，显示该卡的相关信息。 相关代码如下：

--查询本周开户信息 select BCNo as '卡号', BCName as '姓名', BCCurrency as '货币', BBTName as '存款类型', BCOpenDate as '开户日期', BCOpenAmount as '开户金额', BCExistBalance as '存款余额', case BCRegloss when '是' then '挂失账户' when '否' then '正常账户' else '无' end as '账户状态' from ((BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId) inner join BankBusinessType on BankBusinessType.BBTId=BankCard.BCBBTId) where (abs(datediff(day,getdate(),BCOpenDate))

图2.11

(5)查询本周开户信息

查询本月单次交易金额最高的卡号和总交易金额最高的卡号 1.本月单次交易金额最高卡号的相关代码如下：

21

--查询本月单次交易金额最高的卡号和总交易金额最高的卡号 --申明变量@max用于存放本月单笔最大交易额度 declare @max money --将本月单笔最大交易额度值赋值给@max set @max=(select max(BDDealAcount) from BankDealInfo where abs(datediff(month,getdate(),BDDealDate))<1) --筛选出本月单次交易额度最大值并数据卡号，开户日期，开户金额 select distinct BCNo as '卡号', BCOpenDate as '开户日期', BCOpenAmount as '开户金额' from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo where BDDealAcount>=@max and abs(datediff(month,getdate(),BDDealDate))<1; 测试数据如下图2.12

图2.12

2.本月总交易金额最高卡号的相关代码如下：

--建立临时表#sum，用于存放本月的交易记录 create table #sum ( BDBCNo char(19), BDDealDate date, BDDealAcount money ) --将本月交易记录插入临时表#sum insert into #sum(BDBCNo,BDDealDate,BDDealAcount) (select BDBCNo,BDDealDate,BDDealAcount from BankDealInfo where abs(datediff(month,getdate(),BDDealDate))<1) declare @id char(19) --从临时表中筛选出交易总金额最大的卡号 set @id=(select top(1) BDBCNo from #sum group by BDBCNo order by sum(BDDealAcount) desc) --输出本月交易总金额最大的卡号，开户日期，开户金额 select distinct BCNo as '卡号', BCOpenDate as '开户日期', BCOpenAmount as '开户金额' from BankCard where BCNo=@id 测试数据如下图2.13

22

图2.13

(5)查询挂失客户

查询挂失账号的客户信息，分别利用子查询in的方式和内部连接inner join的方式进行查询

1.利用子查询in的方式 查询代码如下：

--查询挂失账号的客户信息，利用子查询in的方式 select BCName 客户姓名 ,BCTel 客户联系电话 from BankCustomer where BCId in (select BCBCId from BankCard where BCRegLoss='是') 查询结果如图2.14所示：

图2.14

2.利用内部连接inner join的方式

查询代码如下：

--查询挂失客户，利用内部连接inner join的方式 select BCName as '客户姓名',BCTel as '联系电话' from BankCustomer inner join BankCard on BankCustomer.BCId=BankCard.BCBCId where BCRegLoss='是' 查询结果如图2.15

图2.15

(6)催款提醒业务

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

查询代码如下：

--催款提醒业务 select BCName as '客户姓名',BCTel as '联系电话',BCExistBalance as '存款余额' 23

from BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId where BCExistBalance<2000 查询结果如图2.16

图2.16

2.6 实训六：创建、使用视图

(1)输出银行客户记录视图VW_userInfo

创建银行客户记录视图VW_userInfo，显示的列名全为中文，先判断该视图是否存在，若存在，则先删除，并显示视图中的数据。

创建视图的代码如下：

--输出银行客户记录视图VW_userInfo if exists (select * from sys.views where name='VW_userInfo') drop view VW_userInfo go create view VW_userInfo as select BCId as '客户编号', BCName as '开户名', BCICNo as '身份证', BCTel as '电话号码', BCAddr as '居住地址' from BankCustomer go select * from VW_userInfo 查询结果如图2.17

图2.17

24

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

创建银行卡记录视图VW_CardInfo，显示的列名全为中文，先判断该视图是否存在，若存在，则先删除，并显示视图中的数据。

创建视图的代码如下：

--输出银行卡记录视图VW_CardInfo if exists (select * from sys.views where name='VW_CardInfo') drop view VW_CardInfo go create view VW_CardInfo as select BCNo as '卡号', BCName as '开户名', BCCurrency as '货币类型', BBTName as '存款类型', BCOpenDate as '开户日期', BCExistBalance as '存款余额', BCPwd as '密码', BCRegloss as '是否挂失' from ((BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId) inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId) go select * from VW_CardInfo 查询结果如图2.18

图2.18

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

创建银行卡交易记录视图VW_TransInfo，显示的列名全为中文，先判断该视图是否存在，若存在，则先删除，并显示视图中的数据。

创建视图的代码如下：

--输出银行卡交易记录视图VW_TransInfo if exists (select * from sys.views where name='VW_TransInfo') drop view VW_TransInfo 25

go create view VW_TransInfo as select BDDealDate as '交易日期', BDDealType as '交易类型', BDBCNo as '卡号', BDDealAcount as '交易金额', BDDealComment as '备注' from BankDealInfo go select * from VW_TransInfo 查询结果如图2.19

图2.19

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

创建客户登录名查询该客户账户信息视图VW_OneUserInfo，显示的列名全为中文，先判断该视图是否存在，若存在，则先删除，并显示视图中的数据。

创建视图的代码如下：

--判断客户登录名的客户账户信息视图VW_OneUserInfo是否存在，若存在，则删除 if exists(select * from sysobjects where name='VW_OneUserInfo') drop view VW_OneUserInfo go --创建客户登录名的客户账户信息视图VW_OneUserInfo create view VW_OneUserInfo as select 客户编号=BCId,客户姓名=BCName,身份证=BCICNo,联系电话=BCTel,居住地址=BCAddr from BankCustomer where BCName=system_user go --显示视图中的数据 select * from VW_OneUserInfo 查询结果如图2.20

26

图2.20

2.7 实训七：存储过程实现业务处理

(1) 完成存款或取款业务 描述：

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

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

3.如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。 4.检查客户输入的密码是否正确。

5.账户取款金额是否大于当前存款额加1。 要求：

1.取款或存款存储过程名为proc_TakeMoney。

2.编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试。 创建存储过程的代码如下：

--完成存款或取款业务 --判断该存储过程是否存在，若存在，则删除 if exists(select * from sysobjects where name='proc_TakeMoney') drop procedure proc_TakeMoney; go --创建存取款业务的存储过程 create procedure proc_TakeMoney @bcno char(19),@money money,@pwd char(6)=null as --不返回受影响的行数 set nocount on --声明一变量存放指定卡号的存款余额 declare @existBanlance money,@bcregloss char(2) --启动事务机制 begin tran --如果该卡已挂失则不能执行转账操作 select @existBanlance=BCExistBalance,@bcregloss=BCRegLoss from BankCard where BCNo=@bcno if (@bcregloss='是') begin print '该卡已挂失，操作执行不成功！' --select @@ERROR=1 end else begin print '交易前，卡号'+@bcno+'，余额为：'+ltrim(str(@existBanlance)) 27

print '交易正进行，请稍后...' --如果输入参数@pwd为空，则为取款业务，否则为存款业务 if (@pwd is not null) --办理取款业务 begin --判断指定卡号和密码是否存在，若存在，则可以取款，否则不能办理取款业务 if exists(select * from BankCard where BCNo=@bcno and BCPwd=@pwd) begin --判断取款金额是否小于等于存款余额-1，若条件成立，则可以取款，否则不能取款 if(@money<=@ExistBanlance-1) begin insert into BankDealInfo values(@bcno,getdate(),@money,'支取','') print '交易成功，交易金额为：'+convert(char(10),@money) end else print '取款交易失败，余额不足，请减少取款' end else if not exists(select * from BankCard where BCNo=@bcno) begin print '不存在该银行卡！' end else --begin --if not exists(select * from BankCard where BCPwd=@pwd) print '取款交易失败，密码有错误！' --return --end --print '取款交易失败，密码有错误' end else begin --办理存款业务 if exists(select * from BankCard where BCNo=@bcno) begin insert into BankDealInfo values(@bcno,getdate(),@money,'存入','') print '交易成功，交易金额为：'+ltrim(str(@money)) end else print '不存在该卡号，交易失败！' end 28

end --判断事务处理里是否有异常，若没有异常，则提交，若有异常，则回滚 if (@@ERROR<>0) begin print '交易失败' rollback tran end else begin commit tran if (@bcregloss='是') begin print '交易失败！' end else begin --print '交易成功，交易金额为：'+ltrim(str(@money)) --判断该交易为何种类型业务，若是存款，则现有余额等于原有余额加上存款金额 if (@pwd is not null) begin select @existBanlance=BCExistBalance from BankCard where BCNo=@bcno --print '交易成功，交易金额为：'+ltrim(str(@money)) print '卡号'+@bcno+'，余额为'+ltrim(str(@existBanlance)) end else begin select @existBanlance=BCExistBalance from BankCard where BCNo=@bcno --print '交易成功，交易金额为：'+ltrim(str(@money)) print '卡号'+@bcno+'，余额为'+ltrim(str(@existBanlance)) end end end go --执行存款存储过程 exec proc_TakeMoney '1010 3576 1234 5688',100 exec proc_TakeMoney '1010 3576 1234 5678',100 exec proc_TakeMoney '1010 3576 1234 5608',100 --执行取款存储过程 exec proc_TakeMoney '1010 3576 1234 5688',100,'123123' exec proc_TakeMoney '1010 3576 1234 5678',100,'123123' exec proc_TakeMoney '1010 3576 1234 5688',100,'123129' 测试结果如下图：

29

1.执行存款过程如图2.21

图2.21

2执行存款过程且卡号已挂失如图2.22.

图2.22

3.执行存款过程且卡号不存在如图2.23

图2.23

4.执行取款过程如图2.24

图2.24

5.执行取款过程且卡号已挂失如图2.25

图2.25

6.执行取款过程且密码错误如图2.26

图2.26

(2) 完成存款或取款业务

30

创建存储过程产生8位随机数字，与前8位固定数字“1010 3576”连接，生成一个由16位数字组成的银行卡号，并输出。

要求：

1.产生随机卡号的存储过程名为proc_randCardId。 2.使用随机函数生成银行卡后8位的数字，随机函数的用法：rand(随机种子)将产生0~1的随机数，要求每次的随机种子不一样。

3.为了保证随机种子每次都不相同，一般采用的算法是：随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数，产生了0~1的随机数后，取小数点后8位，即：0.XXXXXXXX。 创建存储过程的代码如下：

--产生随机卡号 --判断该存储过程是否存在，若存在，则删除 if exists(select * from sysobjects where name='proc_randCardId') drop procedure proc_randCardId; go create procedure proc_randCardId @randCardId char(19) output as declare @r numeric(15,8),@tmpstr char(10) --产生随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数 select @r=RAND(DATEPART(mm,getdate())*100000+DATEPART(SS,getdate())*1000+DATEPART(ms,getdate())) set @tmpstr=convert(char(10),@r) set @randCardId='1010 3576 '+SUBSTRING(@tmpstr,3,4)+' '+SUBSTRING(@tmpstr,7,4) --print '产生随机卡号为'+@randCardId go --执行产生随机卡号过程 declare @randCardId char(19) exec proc_randCardId @randCardId output print '产生随机卡号为'+@randCardId 执行结果如图2.27

图2.27

(3)完成开户业务

描述：

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

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

开户的存储过程名为usp_openAccount。

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

31

创建存储过程的代码如下：

--完成开户业务 if exists(select * from sysobjects where name='usp_openAccount') drop procedure usp_openAccount; go --创建开户存储过程usp_openAccount，输入参数分别是开户名、身份证号、电话号码、开户金额、存款类型和地址 create proc usp_openAccount @BCName char(20),@BCICNo char(18),@BCTel varchar(20),@BCOpendAmount money,@BBTName char(20),@BCAddr varchar(100) as declare @BCBBTId int,@BCNo char(19),@BCId int --先判断存款类型是否正确 if exists(select * from BankBusinessType where BBTName=@BBTName) begin begin tran select @BCBBTId=BBTId from BankBusinessType where BBTName=@BBTName exec proc_randCardId @BCNo output --判断是否卡号重复，若重复，则继续循环，否则退出循环 while(exists(select * from BankCard where BCNo=@BCNo)) exec proc_randCardId @BCNo output --插入一条客户信息记录 insert into BankCustomer values(@BCName,@BCICNo,@BCTel,@BCAddr) --得到刚插入的客户信息的编号 set @BCId=(select BCId from BankCustomer where BCICNo=@BCICNo) --插入一条新开银行卡记录 insert into BankCard values(@BCNo,convert(char(6),substring(@BCICNo,9,6)),'RMB',@BCBBTId,getdate(),@BCOpendAmount,'否',@BCId,@BCOpendAmount) --判断上述事务操作是否有异常--substring(@BCICNo,9,6)) if(@@ERROR<>0) Begin print '尊敬的客户，开户不成功，所有操作均撤销' rollback tran end else begin commit tran print '尊敬的客户，开户成功，系统为你产生的随机卡号是：'+convert(char(19),@BCNo) end --显示开户的客户信息和银行卡信息 select * from BankCustomer select * from BankCard 32

end else print '尊敬的客户，未能成功开户，存款类型不正确，请重新输入' go --开户名、身份证号、电话号码、开户金额、存款类型和地址 exec usp_openAccount '周公旦','150203197510074339','0472_2457890','1200.00','定活两便','内蒙古科技大学' exec usp_openAccount '机舱','150203197610074339','0472_2457890','1100.00','活期','内蒙古科技大学' 查询结果如图2.28、图2.29

图2.28

图2.29

(4)分页显示查询交易数据

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

存储过程名称是usp_PagingDisplay

测试数据是输出第2页，每页5行交易数据 创建存储过程的代码如下：

--分页显示查询交易数据 if exists(select * from sysobjects where name='usp_PagingDisplay') drop procedure usp_PagingDisplay; go 33

create procedure usp_PagingDisplay as select BDNo,BDDealDate,BDDealType,BDBCNo,BDDealAcount from (select BDNo,BDDealDate,BDDealType,BDBCNo,BDDealAcount, ROW_NUMBER() over(order by BDNo) as RowNumber from BankDealInfo ) C where (C.RowNumber between 6 and 10) go exec usp_PagingDisplay 查询结果如图2.30

图2.30

(5)打印客户对账单

为某个特定的银行卡号打印指定时间内发生交易的对账单。要求如下： 1.存储过程名称是usp_CheckSheet。

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

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

如果指定交易时间段，那么打印指定卡号在指定时间内发生的所有交易记录，如测试命令：exec usp_CheckSheet '1010 3576 1234 5678','2014-1-1','2014-1-15' 创建存储过程的代码如下：

--打印客户对账单 use BankDB go --判断客户对账单存储过程是否存在，若是存在，则删除 if exists(select * from sysobjects where name='usp_CheckSheet') drop procedure usp_CheckSheet go --创建客户对账单的存储过程 create procedure usp_CheckSheet @bcno char(19),@startDate datetime=null,@endDate datetime=null as --声明各个变量分别存放姓名、货币类型、存款类型、开户日期 declare @bcName char(20),@bcCurrency char(5),@bbtName char(20),@bcOpenDate date --声明各个变量分别存放交易日期、交易类型、交易金额、备注 declare @bddealdate datetime,@bddealtype char(10),@bddealacount money, 34

@bddealcomment varchar(100) select @bcName=B.BCName,@bcCurrency=A.BCCurrency, @bbtName=C.BBTName,@bcOpenDate=A.BCOpenDate from BankCard A inner join BankCustomer B on A.BCBCId=B.BCId inner join BankBusinessType C on A.BCBBTId=C.BBTId where A.BCNo=@bcno print '卡号:'+@bcno print '姓名:'+@bcName print '货币:'+@bcCurrency print '存款类型:'+@bbtName print '开户日期:'+convert(char(12),@bcOpenDate,111) --创建一个游标，指向指定的交易记录 if(@startDate is null) declare cur_BankDealinfo cursor for select bddealdate,bddealtype,bddealacount,bddealcomment from BankDealInfo where bdbcno=@bcno else begin if(@endDate is null) declare cur_BankDealinfo cursor for select bddealdate,bddealtype,bddealacount,bddealcomment from BankDealInfo where bdbcno=@bcno and bddealdate>@startdate else declare cur_BankDealinfo cursor for select bddealdate,bddealtype,bddealacount,bddealcomment from BankDealInfo where bdbcno=@bcno and bddealdate>@startdate and bddealdate<@enddate end --打开游标 open cur_BankDealinfo print convert(char(12),'交易日')+convert(char(10),'类型')+ convert(char(10),'交易金额')+convert(char(100),'备注') --从游标里取出相应字段的值到各个变量里 fetch next from cur_BankDealinfo into @bddealdate,@bddealtype,@bddealacount,@bddealcomment while(@@FETCH_STATUS=0) begin print convert(char(12),@bddealdate,111)+convert(char(10),@bddealtype)+ convert(char(10),@bddealacount)+convert(char(100),@bddealcomment) fetch next from cur_BankDealinfo into @bddealdate,@bddealtype,@bddealacount,@bddealcomment 35

end close cur_BankDealinfo deallocate cur_BankDealinfo go exec usp_CheckSheet '1010 3576 1234 5688' exec usp_CheckSheet '1010 3576 1234 5678','2015-1-1','2015-11-15' 查询结果如图2.31、图2.32

图2.31

图2.32

(6)统计未发生交易的账户

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

1.存储过程名称是usp_getWithoutTrade。 2.指定时间段

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

3.采用游标技术打印未发生交易的客户信息。 创建存储过程的代码如下：

--统计未发生交易的账户 if exists(select * from sysobjects where name='usp_getWithoutTrade') 36

drop procedure usp_getWithoutTrade; go create procedure usp_getWithoutTrade @startDate datetime=null,@endDate datetime=null as --声明各个变量分别存放姓名、身份证号、电话、地址 declare @bcName char(20),@bcICNo char(18),@bcTel varchar(20),@bcAddr varchar(100) --声明各个变量分别存放客户人数、客户总余额 declare @money money select distinct @bcName=BCName,@bcICNo=BCICNo,@bcTel=BCTel,@bcAddr=BCAddr,@money=BCExistBalance from (BankCard inner join BankCustomer on BankCustomer.BCId=BankCard.BCBCId) --inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) --创建一个游标，指向指定的交易记录 if(@startDate is null) declare cur_BankCustomer cursor for select distinct BCName,BCICNo,BCTel,BCAddr,BCExistBalance from (BankCard inner join BankCustomer on BankCustomer.BCId=BankCard.BCBCId) --inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) where BCNo not in(select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo where abs(datediff(month,getdate(),BDDealDate))<1) or BCNo not in (select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) else begin if(@endDate is null) declare cur_BankCustomer cursor for select distinct BCName,BCICNo,BCTel,BCAddr,BCExistBalance from (BankCard inner join BankCustomer on BankCustomer.BCId=BankCard.BCBCId) --inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) where BCNo not in(select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo where BDDealDate between @startDate and getdate()) or BCNo not in (select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) else declare cur_BankCustomer cursor for select distinct BCName,BCICNo,BCTel,BCAddr,BCExistBalance from (BankCard inner join BankCustomer on 37

BankCustomer.BCId=BankCard.BCBCId) --inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) where BCNo not in(select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo where BDDealDate between @startDate and @endDate) or BCNo not in (select BCNo from BankCard inner join BankDealInfo on BankCard.BCNo=BankDealInfo.BDBCNo) end --打开游标 open cur_BankCustomer declare @num int,@sum money set @num=0 set @sum=0 print convert(char(20),'客户姓名')+convert(char(18),'身份证号')+' '+convert(varchar(20),'电话')+' '+convert(varchar(100),'地址') --从游标里取出相应字段的值到各个变量里 fetch next from cur_BankCustomer into @bcName,@bcICNo,@bcTel,@bcAddr,@money while(@@FETCH_STATUS=0) begin select @num=@num+1 select @sum=@sum+@money print convert(char(20),@bcName)+convert(char(18),@bcICNo)+' '+convert(varchar(20),@bcTel)+' '+convert(varchar(100),@bcAddr) fetch next from cur_BankCustomer into @bcName,@bcICNo,@bcTel,@bcAddr,@money end close cur_BankCustomer deallocate cur_BankCustomer print '统计未发生交易的客户' print '客户人数：'+convert(varchar(5),@num)+'，客户总余额为：'+convert(char(10),@sum)+'元' go exec usp_getWithoutTrade '2015-10-17' 执行结果如图2.33

图2.33

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

38

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

要求：

存储过程名称是usp_getTradeInfo。 指定时间段和客户所在区域

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

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

创建存储过程的代码如下：

--统计银行卡交易和交易额 if exists(select * from sysobjects where name='usp_getTradeInfo') drop procedure usp_getTradeInfo; go create procedure usp_getTradeInfo @startDate datetime=null,@endDate datetime=null,@address varchar(100)=null as --声明各个变量分别存放交易日期、交易金额 declare @bdDealDate date,@bdDealAcount money,@bdDealType char(10) --声明各个变量分别存放客户人数、客户总余额 --declare @number int,@money money select distinct @bdDealDate=BDDealDate,@bdDealAcount=BDDealAcount,@bdDealType=BDDealType from BankDealInfo --where --abs(datediff(day,@startDate,@endDate))>=abs(datediff(day,@startDate,BDDealDate)) and --abs(datediff(day,@startDate,@endDate))>=abs(datediff(day,@endDate,BDDealDate)) --创建一个游标，指向指定的交易记录 if(@startDate is null) declare cur_BankTradeInfo cursor for select BDDealDate,BDDealAcount,BDDealType from BankDealInfo where abs(datediff(year,getdate(),BDDealDate))<1 else begin if(@endDate is null) declare cur_BankTradeInfo cursor for select BDDealDate,BDDealAcount,BDDealType from BankDealInfo where BDDealDate between @startDate and getdate() else declare cur_BankTradeInfo cursor 39

for select BDDealDate,BDDealAcount,BDDealType from BankDealInfo where BDDealDate between @startDate and @endDate end --打开游标 open cur_BankTradeInfo declare @cnum int,@qnum int,@csum money,@qsum money set @cnum=0 set @qnum=0 set @csum=0 set @qsum=0 print '统计银行卡交易量和交易额' print '起始日期 '+convert(varchar(20),@startDate,111)+' '+'截止日期 '+convert(varchar(20),@endDate,111) --从游标里取出相应字段的值到各个变量里 fetch next from cur_BankTradeInfo into @bdDealDate,@bdDealAcount,@bdDealType while(@@FETCH_STATUS=0) begin if @bdDealType='存入' select @cnum=@cnum+1, @csum=@csum+@bdDealAcount if @bdDealType='支取' select @qnum=@qnum+1, @qsum=@qsum+@bdDealAcount fetch next from cur_BankTradeInfo into @bdDealDate,@bdDealAcount,@bdDealType end close cur_BankTradeInfo deallocate cur_BankTradeInfo print '存入笔数：'+convert(varchar(5),@cnum)+'，存入金额：'+convert(char(10),@csum)+'元' print '支取笔数：'+convert(varchar(5),@qnum)+'，支取金额：'+convert(char(10),@qsum)+'元' print '交易总笔数：'+convert(varchar(5),(@cnum+@qnum))+'，结余金额：'+convert(char(10),(@csum+@qsum))+'元' go exec usp_getTradeInfo '2015-11-01','2015-11-18' 执行结果如图2.34

图2.34

40

2.8 实训八：利用事务实现转账

使用存储过程和事务实现转账业务，操作步骤如下所示： 1.从某一个账户支取一定金额的存款。 2.将支取金额存入到另一个指定的账户中。 3.分别打印此笔业务的转出账单和转入账单。 要求：

1.存储过程名称是usp_transfer。 2.使用事务机制实现转账业务。

创建存储过程和事务机制的代码如下：

--利用事务实现转账 use BankDB go --判断该存储过程是否存在，若存在，则删除 if exists(select * from sysobjects where name='usp_transfer') drop procedure usp_transfer go --创建转账存储过程，需要传递两个账户号码及转账金额 create procedure usp_transfer @outbcno char(19),@inbcno char(19),@dealAcount money as --不返回受影响的行数 set nocount on --声明个变量分别存放转出账户和转入账户的转账之后的余额 declare @outexistbalance money,@inexistbalance money,@bcregloss1 char(2),@bcregloss2 char(2) --声明变量存放转出账户的姓名、货币类型、存款类型和开户日期 declare @outbcname char(20),@outBcCurrency char(5),@outBBTName char(20),@outBcOpenDate date --声明变量存放转入账户的姓名、货币类型、存款类型和开户日期 declare @inbcname char(20),@inBcCurrency char(5),@inBBTName char(20),@inBcOpenDate date select @bcregloss1=BCRegLoss from BankCard where BCNo=@outbcno select @bcregloss2=BCRegLoss from BankCard where BCNo=@inbcno if (@bcregloss1='是' or @bcregloss2='是' ) begin if (@bcregloss1='是') begin print '挂失卡号：'+convert(char(19),@outbcno) end if(@bcregloss2='是') begin print '挂失卡号：'+convert(char(19),@inbcno) end print '转账不成功！' end else 41

begin print '开始转账，请稍后...' --判断转出账户及余额是否大于转出金额+1 if exists(select * from BankCard where BCNo=@outbcno and BCExistBalance>=@dealAcount+1) begin --判断转入账户是否存在 if not exists(select * from BankCard where BCNo=@inbcno) begin print '转入账户不存在,转账交易失败' return end --启动事务机制 begin tran print '交易正进行，请稍后...' --取得转账后两个账户的余额 select @outexistbalance=BCExistBalance-@dealAcount from BankCard where BCNo=@outbcno select @inexistbalance=BCExistBalance+@dealAcount from BankCard where BCNo=@inbcno --首先增加一条转出账户的支出交易记录 insert into BankDealInfo values(@outbcno,getdate(),@dealAcount,'支取','银行转账业务') --增加一条转入账户的存取交易记录 insert into BankDealInfo values(@inbcno,getdate(),@dealAcount,'存入','通过存储过程存款') --取得转账后两个账户的余额 --select @outexistbalance=BCExistBalance-@dealAcount from BankCard where BCNo=@outbcno --select @inexistbalance=BCExistBalance+@dealAcount from BankCard where BCNo=@inbcno --判断事务处理里是否有异常，若没有异常，则提交，若有异常，则回滚 if (@@ERROR<>0) begin print '转账交易失败' rollback tran end else begin commit tran print '交易成功,交易金额:'+convert(varchar(10),@dealAcount) print '卡号'+convert(char(19),@outbcno) +' 余额'+convert(char(10),@outexistbalance) print '交易成功,交易金额:'+convert(varchar(10),@dealAcount) print '卡号'+convert(char(19),@inbcno) +' 余额'+convert(char(10),@inexistbalance) print '转账成功！' end 42

end --分别打印转出账户对账单和转入账户对账单 else if not exists(select * from BankCard where BCNo=@outbcno) begin print '转出账户不存在！交易失败！' return end else begin print '转出账户余额不足，此次转账交易失败' return end print '打印转出账户对账单' --获取转出账户的相关信息 select @outbcname=BCName,@outBcCurrency=BCCurrency,@outBBTName=BBTName,@outBcOpenDate=BCOpenDate from ((BankCard inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId) inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId) where BCNo=@outbcno print '------------------------------' --print '------------------------------' print '卡号：'+convert(char(19),@outbcno) print '姓名：'+convert(char(20),@outbcname) print '货币类型：'+convert(char(15),@outBcCurrency) print '存款类型：'+convert(char(20),@outBBTName) print '开户日期：'+convert(char(20),@outBcOpenDate,111) declare @bddealdate date,@bddealtype char(20),@bDDealComment char(100) --通过插入日期降序得到最近插入的一条记录 select top 1 @bddealdate=BDDealDate,@bddealtype=BDDealType,@bDDealComment=BDDealComment from BankDealInfo where BDBCNo=@outbcno order by BDDealDate desc print convert(char(15),'交易日')+convert(char(15),'类型')+convert(char(15),'交易金额')+convert(char(15),'备注') print '---------------------------------------------------------------------' print convert(char(15),@bddealdate,111)+convert(char(10),@bddealtype)+convert(char(15),@dealAcount)+convert(char(100),@bDDealComment) print '打印转入账户对账单' --获取转入账户的相关信息 select @inbcname=BCName,@inBcCurrency=BCCurrency,@inBBTName=BBTName,@inBcOpenDate=BCOpenDate from ((BankCard inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId) 43

inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId) where BCNo=@inbcno print '------------------------------' print '卡号：'+convert(char(19),@inbcno) print '姓名：'+convert(char(20),@inbcname) print '货币类型：'+convert(char(15),@inBcCurrency) print '存款类型：'+convert(char(20),@inBBTName) print '开户日期：'+convert(char(20),@inBcOpenDate,111) --通过插入日期降序得到最近插入的一条记录 select top 1 @bddealdate=BDDealDate,@bddealtype=BDDealType,@bDDealComment=BDDealComment from BankDealInfo where BDBCNo=@outbcno order by BDDealDate desc print convert(char(15),'交易日')+convert(char(15),'类型')+convert(char(15),'交易金额')+convert(char(15),'备注') print '---------------------------------------------------------------------' print convert(char(15),@bddealdate,111)+convert(char(10),@bddealtype)+convert(char(15),@dealAcount)+convert(char(100),@bDDealComment) end go exec usp_transfer '1010 3576 1234 5688','1010 3576 1234 5689',100 exec usp_transfer '1010 3576 1234 5678','1010 3576 1234 5689',100 exec usp_transfer '1010 3576 1234 5678','1010 3576 1234 5678',100 exec usp_transfer '1010 3576 1234 5608','1010 3576 1234 5688',100 执行结果如下： 1.执行转账如图2.35

图2.35

44

2.执行转账且转出账户挂失如图2.36

图2.36

3.执行转账且转出转入账户都挂失如图2.37

如图2.37

4.执行转账且转账的账户不存在如图2.38

图2.38

45

第三章 项目心得体会

做这个课程设计有许多感想，首先这是我第一次投入大量的精力去做一个课程设计并且参加答优。以前也想过去答优但是条件不允许，因为是大二才同过转专业转到软件工程专业的，所以大一的许多原专业没有学的课都要去重新跟着下一届的学生一起学习，结果每次课程设计都会有冲突，一周既要做下一届的课设又要做本届的课设，因此根本就没有时间和精力去答优。这次大一的课终于补完了，有充分的时间去答优。

一开始拿到题目的是后，就通篇的进行略读一番，初步的感觉认为不是很难。当开始做的时候发现并不是那么的简单，同时也发现银行系统中存在许多的不足：

1. 挂失的账户还能进行交易，不符合真实的情况。

2. 银行存储钱时，当账户不存在会出现意想不到的错误。 3. 银行取钱的时，当账户不存在或是密码错误时会出现错误。 4. 银行账户进行转账是时，当账户不存在会出错误。

5. 因为BankCard表和BankDealInfo表是通过银行卡号连接的，当查询在一定时间内

未交易的用户时，提示代码只是从BankDealInfo表中进行查询符合条件的值，而已经开户的账户却一次都没有交易的用户却容易被忽略掉。 6. 提示代码中对银行的利润算法也有问题，给的算法为：

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

而当一个人的工资存在银行里，然后取钱或消费购物时，银行对该用户还收取了0.008的贷款利率，这与现实生活中的情况不符。 在完成了老师所给的问题后，自己对以上出现的问题都进行了一定的修改，让运行的更加符合日常生活中银行的规范。

在修改优化这些代码中，觉得银行账户存取款和银行账户进行转账的代码编写的时候遇到了很多问题，有些问题是进行百度搜索解决方法的，有些问题在百度上没有解决方法时，通过和同学讨论或是自己进行不断思考得出答案的。

最后在这里感谢一下教授我们课程的余金林老师，有一些同学认为该门课不好学，有一些晦涩难懂。在前期我也有类似的感觉，但经过余老师教会了我们最重要的就是自学和自我钻研的方法，通过自己课下阅读一些相关书籍，发现其实这门课挺有趣的。对进行深入的学习更有感觉更有想法。

谢谢余老师的辛勤教学。

46

第四章 对《我的租房网》进行优化

(1)给各表缴入非聚集索引

创建非聚集索引代码如下：

--给客户信息表中的UserName创建非聚集索引 create unique nonclustered index Idx_userName on sys_user(UserName) with fillfactor=10; --给区县信息表中的DName创建非聚集索引 create unique nonclustered index Idx_dName on hos_district(DName) with fillfactor=10; --给街道信息表中的SName创建非聚集索引 create unique nonclustered index Idx_sName on hos_street(SName) with fillfactor=10; --给房屋信息表中的HTName创建非聚集索引 create unique nonclustered index Idx_htName on hos_type(HTName) with fillfactor=10;

(2)给随机插入十万条代码增加事务优化

1.没有添加事务的插入十万条代码如下

declare @begin datetime,@end datetime set @begin =getdate() --定义局部变量 declare @topic varchar(50) declare @contents varchar(50) declare @copy varchar(50) declare @userid int declare @streetid int declare @htid int declare @price decimal(6,2) declare @htime datetime --向hos_house表中插入10000条数据 --使用事物 declare @i int set @i=0 while @i<100000 begin --对局部变量进行赋值 set @topic=(select top 1* from ##topic order by newid()) set @contents=(select top 1* from ##contents order by newid()) 47

本文来源：https://www.bwwdw.com/article/r0zw.html