课程设计：基于SQL Server的银行ATM 存取款机系统设计与实现

加油加油

前言

        本文章主要是在学了SQL Server后做的一个课程设计，全文有三万多字，接近一千行，光简简单单写这篇博客就花了不少时间，若有出现错误之处，请指出，定当感激不尽，一起学习，一起进步。

---

一、项目背景

1、项目任务

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

2、项目技能目标

• 使用PowerDesigner 完成数据库概念模型和数据库物理模型设计。
• 使用 T-SQL 语句创建数据库、表和各种约束。
• 使用 T-SQL 语句编程实现常见业务。
• 使用触发器实现多表之间的级联更新。
• 使用事务和存储过程封装业务逻辑。
• 使用视图简化复杂的数据查询。
• 使用游标技术实现结果集的行集操作。

3、需求概述

       某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套 ATM 存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安 全性，提高工作效率。
       要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用 T-SQL 语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事 务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业 务。

4、开发环境

• 数据库：SQL SERVER 2008 开发版
• 数据库建模工具：PowerDesigner15

5 、问题分析

该项目的 ATM 存取款机业务如下：

(1) 银行存取款业务介绍

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

(2) 客户信息

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

(3) 银行卡账户信息

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

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

(4) 银行卡交易信息

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

(5) 银行卡手工账户和存取款单据信息

       该银行要求这套软件实现银行客户的开户、存款、取款、转账和余额查询等业务，使得银行储蓄业务方便、快捷，同事保证银行业务数据的安全性。
       为使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的 样本数据，以供项目开发时参考，参加表 5 和表 6 。

二、项目实训内容

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

• 长度规范：
         凡是需要命名的对象其标识符均控制在 30 个字符以内，也即：SQL Server 中的 表名、字段名、函数名、存储过程、触发器、视图等名字长度要尽量不超过 30 个字 符长度。
• 构成规范：
         数据库各种名称必须以字母开头，但严禁以系统关键字开头，名称只能含有字母、数 字以及下划线“_ ”三类字符， “_ ”用于间隔名称中的各语义字段；不要使用系统保留字作表名。
• 大小写规范：
         构成 SQL Server 数据库中的各种名称(表名、字段名、过程名、视图名等所有命 名符的首字母需要使用大写，也即每个命名单词的首字母大写，其它字符小写。但对 于简写或缩写的短单词，如 ID 、UI 可以全为大写。
• 主键规范：
         除临时表、流水表以及日志表外，其它表都要建立主键。主键最好设计成单一主 键，尽量不要用复合主键，尽量使用没有业务语义的字段作为主键，
  如采用按顺序自增的数值型字段为主键
• 注释规范：
         每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段要说明参照于那个表，另外对于存储过程、视图、触发器、函数等代码均要增加注释，以保持代码的可读性以及后续的可维护性。
• 行大小规范：
         SQL Server 的 1 页的大小是 8K，因此一行的数据要控制到 8K 之内，如果超过 8K 要想办法将表进行拆分成多个子表。
• 数据保留策略：
         大表由于数据量较大，往往是系统的性能瓶颈所在，因此对于大表的设计好考虑到今后的数据转移、分片、Partition 等，并且对大表以及其相关表的数据保留时间也要有一个提前规划，以免今后出现性能问题束手无策。
• 必备字段要求：
         每个大表都应该添加以下几个有用的字段，分别为创建日期、修改日期、操作人以及版本标记，创建这些字段的目的是为今后的数据转移以及分片或分区做准备，同时也有利于今后的数据审计等。
  注意事项：
         基于大表的任何操作都要慎重思考，通常情况下要禁止在大表上创建触发器，禁止在大表上做频繁的批量更新或删除动作，禁止在生产时间对大表做 DDL 操作，禁止在大表上做全表扫描(Full Scan)等。
• 临时表功能：
         SQL Server 分为全局临时表和局部临时表，临时表在很多场合下能带来意想不到 的效果，尤其是需要中转的数据记录集采用临时表能提升系统性能。临时表支持索引、 约束、排序等实体表具有的功能。
• 存储特点：
         临时表的数据存储在 tempdb 数据库中，因此过于频繁的创建临时表会增加 tempdb 库的负荷，尤其是数据量超过 10W 条记录的临时表更是会影响 tempdb 库的 性能，由此在某些情况下可用CTE 替代临时表的使用。
  注意事项：
         临时表执行完毕后，要及时的手工 Drop 掉，及时释放资源，减轻系统的 Loading ， 另外特别注意的是要尽量禁止使用全局临时表，全局适合多个 session 间的数据交互， 但往往会引起数据的串值。
• 命名规范：
         尽量采用有意义的字段名，使描述尽可能清楚，如采用缩写，尽量使用通用的缩 写语言，如 addr 代表 address，避免出现只有自己理解的缩写。
• 日期字段：
         时效性数据应包括“创建时间/修改时间”字段，时间标记对查找数据、清理数据、 排序合并特别有用，另外要根据具体业务考量时间字段的类型，如在 Smalldatetime和 Datetime 类型进行选择。
• 注意保留词：
         对于字段的命名，要确保字段名没有和保留词、数据库系统或者常用方法冲突， 比如 master 、CROSS 、address 、substring 、len 、sysobjects 等词就不适合用来做字段 的独立命名。
• 数值规范：
         数值型的主要有 INT 、BIGINT 、TINYINT 、SMALLINT 、FLOAT 、NUMERIC 、 MONEY 等类型，要根据实际应用选择合适的类型，如字段的数据为小于 255 的整形 数字，那么就要选择 TINYINT；如字段数据小于 32767 的整形数字，那么就要选择SMALLINT，以此类推。
• 文本规范：
         文本类型主要有 CHAR 、VARCHAR 、TEXT 等类型，要根据实际应用选择合适 的类型，如字段文本长度固定为 8 位，那么就要用CHAR(8)；如文本长度最大为 100 ， 并且大小是非定长的，那么就要设为 VARCHAR(100)。并且以上文本若为汉字，那 么就要设为 NVARCHAR 和 NCHAR 。
• 字段命名技巧：
         字段命名要统一规范，同一个字段在不同的表中命名要一致，另外字段名一般都 要带上业务模块的前缀，如订单(Order)价格字段命名为 Or_Price,部门(Department)编 号为 Dep_No。命名规范没有那个是最合理的，只有定义好最适合自己的统一规范即 可。
         外键建立索引：
  外键不建立索引，将有可能导致两个严重的问题。1.更新相关的表产生死锁。2. 两表关联查询时性能低下。因此通常情况下都必须要求外键建立索引。
• 联合索引规范：
         当数据对某表经常要多条件查询时，可能就需要建立联合索引，联合索引的第一 个引导列字段非常重要，引导列字段通常要能过滤掉大部分数据，这样方能减少 IO 的读写，提高性能。非引导列字段在引导列的查询数据基础上继续过滤数据，以提高 查询速度。联合索引对更新会产生一定的性能影响。
  禁用多余索引：
         数据库索引能提高查询速度，但会增加写操作的开销，因此对一些几月或者从没 有使用过的索引要删除掉，以免增大数据库的负荷。
  重复索引问题：
         一般情况下，尽量避免重复索引的出现，重复索引很容易引起死锁，减低数据库
  的并发访问。重复索引也会造成索引的维护困难。
  索引数量限制：
         数据库索引主要用来解决读的性能瓶颈，但是会增加写操作的负荷，因此过多的 索引会造成更新速度变慢，甚至会引起不要的死锁。一般情况下表中的索引不要超过5 个。
  注意事项：
         建立索引前，要充分了解表的使用及数据特性，要了解表的查询条件和查询频率， 甚至随着业务的变化而引起表数据使用状况的变化，带之而来的是索引也需要相应调
  整。
• 命名规范：
         存储过程命名遵守统一的规范，对于业务存储过程要以 p 或 proc开头，接着加上” _ ”，然后再加上模块名称简写和具体的业务词，最后加上执行类型。
  数据库的存储过程名严禁以 sp 开头，sp 通常表示系统数据库存储过程名的前缀。触 发器以 Tr 开头，接着加上”_ ”，然后加上表名。
• 书写规范：
         关键字建议用大写，同样的代码书写格式保持一致，SQL 脚本采用缩进风格，风 格一致，缩进格式一致，使用空格。
  INSERT 规范：
         通常情况下，INSERT 语句要给出具体的字段列表，避免采用“INSERT INTO TB_1 VALUES( ‘值 1’, ’值 2’, ’值 3’)”用法，此种用法往往会由于表结构变迁 而导致语句不可执行
  避免隐式转换：
         书写时，必须明确表结构及表中各个字段的数据类型，特别是查询条件中的字段，要避免由于类型的不同导致数据类型转换的发生，从而减少因为
  数据类型转换产生的系统开销。
• NULL 陷阱：
         NULL 不要直接用来进行运算符的比较，也不要和其它值进行连接操作，判断一 个值是否为 NULL 值时，要采用IS NULL 来进行比较。
• LIKE 规范：
         LIKE 子句应尽量前段匹配，要避免通配符在前段，以免导致全索引扫描的发生。
• 参数化代码：
         SQL 中常量的直接使用，会导致 SQL 语句频繁的硬解析，进而严重影响数据库 的性能，基于这些原因，代码中要尽量采用参数绑定，以减少语句硬解析的次数，从 而提高语句执行性能。
• 动态 SQL：
         动态 SQL 是在运行时才进行解析的，相当于是硬解析，因此会损失一些系统性 能，但是动态 SQL 写法灵活，因此在某些情况下需要以性能换灵活，但对于用静态 语句就能简单实现的 SQL，就不要用动态SQL 语句。
• 嵌套层级限制：
         嵌套查询尽量少使用，尤其是对于超过 3 层的嵌套查询更要慎用，对于复杂的嵌 套语句要根据业务进行拆分为多条 SQL 来实现，或者通过临时表来取代一部分嵌套 层级。
• 排序规范：
         SQL 语句中要尽量减少排序，对查询结果进行的排序会大大降低系统的性能，并 且会增加 tempdb 数据库的负荷，因此在开发时间宽松情况下，要尽量将排序动作放 到应用程序层去完成。
• 代码注释要求：
         注释是指程序中会被编译器忽略掉的部分，目的是描述代码的用途及更新时间， 合理的添加注释可以使得程序结构清晰，可以使代码更好理解，便于系统后续的维护。 一般情况下，注释要不少于代码的十分之一。
• 静态 SQL：
         SQL 语句要尽可能采用静态 SQL，静态 SQL 第一次执行时会将编译器解析的结 果存储在缓存中，下次执行该静态 SQL 时会直接从缓存中获取其执行计划，相当于 是软解析，因此采用静态 SQL 可以减少语句的解析时间，提升了数据库的性能。
• 最小事务原则：
         数据库事务用来保持数据的一致性，但是对于一个执行时间较长的大事务，会造 成数据库锁的增加，当锁越积越多的时候就会从行锁升级到页锁，从业锁升级到表锁， 从而严重影响数据库的性能。因此，在能满足数据一致性的前提下，要尽量将非一致
  性要求的语句代码从事务中移除，以便提升数据库的并发访问。
• 顺序提交：
         顺序提交是一个好的代码编写习惯，顺序提交可以减少死锁的发生，并且还能增 加代码的可读性及可维护性。

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

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

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

• 第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中 不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
         如在银行客户表 BankCustomer 中，不能将客户信息都放在一列中显示，也不能 将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个客户的信息在表中只出现一次。简而言之，第一范式就是无重复的列。
• 第二范式（2NF）要求实体的属性完全依赖于主关键字。
         如银行交易信息表 BankDealInfo 中，不能把卡号设为主键因为一个卡号可以发生 多条交易记录。要确定唯一的一条信息，必须重新定义一个和其它属性无关的交易编 号。这样要查询一条交易信息。就可以用交易编号。简而言之，第二范式就是属性完全依赖于主键。
• 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字 信息。
          如银行卡表 BankCard 中，有了用户Id 后。不能还添加用户姓名等相关的用户信 息。否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

(3) 绘制 CDM 模型、生成 PDM 模型

3、实训三：创建数据库

(1) 创建数据库

       使用 Create DataBase 语句创建“ATM 存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下文件增长率为 15% 。

--创建BankDB数据库，数据库文件和日志文件均保存在文件夹D:\data下
--文件增长率均为%，数据文件起始大小为MB，日志文件起始大小为MB
create database BankDB
on primary
(
name=N'BankDB',
filename=N'C:\data\BankDB.mdf',
size=5mb,
filegrowth=15%
)
log on
(
name=N'BankDB_log',
filename=N'C:\data\BankDB_log.ldf',
size=2mb,
filegrowth=15%
)

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

       根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。

use BankDB
go

--判断银行客户信息表是否存在,若存在则删除
--sysobjects 系统对象表。保存当前数据库的对象。
--OBJECT_ID()根据对象名称返回该对象的id
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankCustomerInfo'))
DROP TABLE BankCustomerInfo

--创建BankCustomerInfo表
create table BankCustomerInfo(
CustNum int identity(1,1) primary key not null, --客户编号
CustName char(20) not null,                     --客户姓名
CustID char(18) not null,                       --身份证号
CustTelephone char(20) not null,                --客户电话
CustAddress varchar(100) not null               --客户住址
)

--判断银行卡表是否存在,若存在则删除
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankCardInfo'))
DROP TABLE BankCardInfo

--创建BankCardInfo表
create table BankCardInfo(
CardID char(19) primary key not null,            --银行卡号
Password char(6) not null,                       --密码
MoneyType char(5) not null,                      --货币类型
DepositType int not null,                         --存款类型
OpenDate date not null,                          --开户日期
OpenMoney money not null,                         --开户金额
CardLoss char(10) not null,                      --是否挂失
CustNum int not null,                            --客户编号
CardMoney numeric(8,2) not null                  --卡内余额
)

--判断银行交易信息表是否存在,若存在则删除
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankDealInfo'))
DROP TABLE BankDealInfo

--创建BankDealInfo表
create table BankDealInfo(
DealNum int identity(1,1) primary key not null,  --交易编号
CardID char(19) not null,                        --银行卡号
DealDate date not null,                          --交易日期
DealMoney decimal(8,2) not null,                 --交易金额
DealType char(256) not null,                     --交易类型
DealNote varchar(1024) null                      --交易备注
)

--判断业务类型表是否存在,若存在则删除
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankBusinessType'))
DROP TABLE BankBusinessType

--创建BankBusinessType表
create table BankBusinessType(
BusNum int identity(1,1) primary key not null,  --业务编号
BusName char(20) not null,                      --业务名称
BusDescribe varchar(100) null                   --业务描述
)

--为BankCustomerInfo表添加约束
--添加check约束，身份证号前位必须是数字，后位可以是数字或者X
alter table BankCustomerInfo add constraint ck_CustID check(left(CustID ,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(CustID ,1) like'[0-9]' or right(CustID,1) like 'X'))
--客户电话必须是固定电话号码或者手机号
alter table BankCustomerInfo add constraint ck_CustTelephone check(
CustTelephone  like'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
CustTelephone  like '[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
CustTelephone  like '1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
--银行卡号前位必须为，后位为-9任意数字

--为BankCardInfo表添加约束
alter table BankCardInfo add constraint ck_CardID check(CardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
--密码默认为
alter table BankCardInfo add constraint df_Password default('888888') for Password
--货币类型默认为RMB
alter table BankCardInfo add constraint df_MoneyType default('RMB') for MoneyType
--开户日期默认为当前时间
alter table BankCardInfo add constraint df_OpenDate default(getdate()) for OpenDate
--开户金额至少为元
alter table BankCardInfo add constraint ck_OpenMoney check(OpenMoney>=1)
--是否挂失默认为’否‘
alter table BankCardInfo add constraint df_CardLoss default('否') for CardLoss 

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

添加子表外键约束

--添加外键约束
alter table BankCardInfo add constraint fk_DepositType foreign key(DepositType) references BankBusinessType(BusNum)
alter table BankCardInfo add constraint fk_CustNum foreign key(CustNum) references BankCustomerInfo(CustNum)
alter table BankDealInfo add constraint fk_CardID foreign key(CardID) references BankCardInfo(CardID)

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

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

(1) 创建级联触发器

创建 Insert 触发器：
        在交易信息表创建一个 insert 触发器，当增加一条交易信息时，修改相应银行卡
的存款余额。

--在交易信息表插入触发器.使用游标当新增一个交易信息.修改银行卡的存款余额
--检查触发器是否存在.存在则删除
if(object_id('tr_insertDealInfo','tr') is not null) 
drop trigger tr_insertDealInfo  
go 
--创建insert 触发器
create trigger tr_insertDealInfo  on BankDealInfo for insert 
as 
declare @type char(10),@sum money,@BDCardID char(19) 
--定义游标,指向inserted 表
declare cursor_BankDealInfo cursor for 
select DealType,DealMoney,CardID from inserted 
--打开游标、
open cursor_BankDealInfo 
--读取游标.赋值给相应字段
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
--@@fetch_status=0 fetch 语句成功
while @@fetch_status=0 
begin 
--根据交易类型.更新银行卡余额.rtrim 和lraim 是去除首尾空格
if(rtrim(ltrim(@type))='存入') 
update BankCardInfo set CardMoney=CardMoney+@sum where CardID=@BDCardID 
if(rtrim(ltrim(@type))='支取') 
update BankCardInfo set CardMoney=CardMoney-@sum where CardID=@BDCardID 
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
end 
--关闭游标
close cursor_BankDealInfo 
--释放游标
deallocate cursor_BankDealInfo
go

创建Delete触发器：
        在交易信息表创建一个 Delete 触发器，当删除一条交易信息时，修改相应银行卡
的存款余额。

--触发器存在则删除
if(object_id('tr_delDealInfo','tr') is not null) 
drop trigger tr_delDealInfo 
go 
--创建delete 触发器
create trigger tr_delDealInfo on BankDealInfo for delete 
as 
declare @type char(10),@sum money,@BDCardID char(19) 
--定义游标,指向deleted 表
declare cursor_BankDealInfo cursor for 
select DealType,DealMoney,CardID from deleted 
--打开游标
open cursor_BankDealInfo 
--读取游标,赋值
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
while @@fetch_status=0 
begin 
if(rtrim(ltrim(@type))='存入') 
update BankCardInfo set CardMoney=CardMoney-@sum where CardID=@BDCardID 
if(rtrim(ltrim(@type))='支取') 
update BankCardInfo set CardMoney=CardMoney+@sum where CardID=@BDCardID 
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
end 
close cursor_BankDealInfo 
deallocate cursor_BankDealInfo 
go

创建 Update 触发器
        在交易信息表创建一个 Update 触发器，当更新一条交易信息时，修改相应银行 卡的存款余额。

--触发器存在则删除
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,@BDCardID char(19) 
--定义游标,指向deleted 表
declare cursor_BankDealInfo cursor for 
select DealType,DealMoney,CardID from deleted 
--打开游标
open cursor_BankDealInfo 
--读取游标,赋值
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
while @@fetch_status=0 
begin 
if(rtrim(ltrim(@type))='存入') 
update BankCardInfo set CardMoney=CardMoney-@sum where CardID=@BDCardID 
if(rtrim(ltrim(@type))='支取') 
update BankCardInfo set CardMoney=CardMoney+@sum where CardID=@BDCardID 
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
end 
close cursor_BankDealInfo 
deallocate cursor_BankDealInfo 
--定义游标,指向inserted 表
declare cursor_BankDealInfo cursor for 
select DealType,DealMoney,CardID from inserted 
--打开游标
open cursor_BankDealInfo 
--读取游标.赋值给相应字段
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
--@@fetch_status=0 fetch 语句成功
while @@fetch_status=0 
begin 
--根据交易类型.更新银行卡余额.rtrim 和lraim 是去除首尾空格
if(rtrim(ltrim(@type))='存入') 
update BankCardInfo set CardMoney=CardMoney+@sum where CardID=@BDCardID 
if(rtrim(ltrim(@type))='支取') 
update BankCardInfo set CardMoney=CardMoney-@sum where CardID=@BDCardID 
fetch next from cursor_BankDealInfo into @type,@sum,@BDCardID 
end 
--关闭游标
close cursor_BankDealInfo 
--释放游标
deallocate cursor_BankDealInfo

(2) 插入数据表的测试数据

        使用 T-SQL 语句向每个表插入如下所示测试数据，要保证业务数据的一致性和 完整性。
BankBusinessType 表的测试数据：

insert into BankBusinessType 
(BusName,BusDescribe) 
values ('活期','无固定存期,可随时存取,存取金额不限的一种比较灵活的存款'), 
('定活两便','事先不约定存定期,一次性存入,一次性支取的存款'), 
('通知','不约定存期,支取是需提前通知银行,约定支取日期和金额方能支取的存款'), 
('整存整取年','整笔存入,到期提取本息'), 
('整存整取年','整笔存入,到期提取本息'), 
('整存整取年','整笔存入,到期提取本息'), 
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'), 
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'), 
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'), 
('自助转账','银行atm 存取款机上办理银行卡之间互相划转') 
go 
select * from BankBusinessType

BankCustomerInfo 表的测试数据：

insert into BankCustomerInfo(CustName,CustID,CustTelephone,CustAddress) 
values ('叶春萌','152825198512548541','13585941287','丽都新城'), 
('周明','152821198401248574','0147-7418527','松石名第') 
go 
select * from BankCustomerInfo

BankCardInfo表的测试数据：

insert into BankCardInfo 
(CardID,Password,MoneyType,DepositType,OpenDate,OpenMoney,CardLoss,CustNum,CardMoney) 
values ('1010 3576 1234 5678','197611','RMB',1,cast(dateadd(day,-(rand()*30),getdate()) 
as date),1000,'否',1,1000), 
('1010 3576 1234 5688','197611','RMB',2,cast(dateadd(day,-(rand()*30),getdate()) 
as date),1000,'否',2,1500) 
go 
select * from BankCardInfo

BankDealInfo 表的测试数据：

insert into BankDealInfo 
(CardID,DealDate,DealMoney,DealType,DealNote) 
values ('1010 3576 1234 5678',cast(dateadd(day,-(rand()*15),getdate()) as date),500,'存入','单位月工资'), 
('1010 3576 1234 5678',cast(dateadd(day,-(rand()*15),getdate()) as date),1500,'存入','单位月工资'), 
('1010 3576 1234 5678',cast(dateadd(day,-(rand()*15),getdate()) as date),300,'存入','支付宝付款'), 
('1010 3576 1234 5678',cast(dateadd(day,-(rand()*15),getdate()) as date),400,'支取','刷卡消费'), 
('1010 3576 1234 5688',cast(dateadd(day,-(rand()*15),getdate()) as date),3500,'存入','单位月工资'), 
('1010 3576 1234 5688',cast(dateadd(day,-(rand()*15),getdate()) as date),4500,'存入','单位月工资'), 
('1010 3576 1234 5688',cast(dateadd(day,-(rand()*15),getdate()) as date),800,'存入','支付宝付款'), 
('1010 3576 1234 5688',cast(dateadd(day,-(rand()*15),getdate()) as date),900,'支取','刷卡消费') 
go 
select * from BankDealInfo

5、实训五：模拟常规业务

(1) 修改客户密码

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

update BankCardInfo set Password='123465' where CardID='1010 3576 1234 5678' 
update BankCardInfo set Password='123123' where CardID='1010 3576 1234 5688' 
select CardID '银行卡卡号',Password '密码',MoneyType '货币类型',DepositType '储蓄种类',OpenDate ' 
开户日期',OpenMoney '开户金额', CardLoss '是否挂失',CustNum'客户编号',CardMoney '存款金额' from BankCardInfo

(2) 办理银行卡挂失

       卡号为 1010 3576 1234 5678 的银行卡丢失，申请挂失。

update BankCardInfo set CardLoss='是' where CardID='1010 3576 1234 5678'
select CardID '银行卡卡号',Password'密码',MoneyType '货币类型',BusName '储蓄类型',OpenDate ' 
开户日期',OpenMoney '开户金额', CardLoss '是否挂失',CustName'客户姓名',CardMoney '存款
金额' from BankCardInfo 
inner join BankCustomerInfo on BankCardInfo.CustNum=BankCustomerInfo.CustNum 
inner join BankBusinessType on BankCardInfo.DepositType=BankBusinessType.BusNum

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

存入代表资金流入，支取代表资金流出。
计算公式：资金流通余额=总存入金额-总支取金额
假定存款利率为千分之三，贷款利率为千分之八。
计算公式：盈利结算=总支取金额0.008-总存入金额0.003 。
要求创建一个存储过程 proc_staticsBanlanceAndProfit 。

if exists(select * from sys.sysobjects where name='proc_staticsBanlanceAndProfit') 
drop procedure proc_staticsBanlanceAndProfit
go 
create procedure proc_staticsBanlanceAndProfit
as 
declare @inmoney money 
declare @outmoney money 
select @inmoney=sum(DealMoney) from BankDealInfo where DealType='存入' 
select @outmoney=sum(DealMoney) from BankDealInfo where DealType='支取' 
print '存入总金额:'+ltrim(str(@inmoney))+'rmb,支取总金
额:'+ltrim(str(@outmoney))+'rmb,银行流通余额:'+ltrim(str(@inmoney-@outmoney))+'rmb, 
盈利余额:'+ 
ltrim(str(@outmoney*0.008-@inmoney*0.003))+'rmb' 
go 
exec proc_staticsBanlanceAndProfit

(4) 查询本周开户信息

查询本周开户的卡号，显示该卡的相关信息。

--默认星期日作为一周的第一天.修改星期一为第一天
set datefirst 1 
select CardID '银行卡卡号',CustName '姓名', MoneyType '货币类型',OpenDate '开户时间
',BusName '储蓄类型',OpenMoney '开户金额',CardMoney '存款金额', case CardLoss 
when '是' then '挂失账户' 
when '否' then '正常账户' 
end '是否挂失' from BankCardInfo 
inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType 
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
where datediff(day,OpenDate,getdate())<datepart(weekday,getdate())

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

查询本月存、取款中单次交易金额最高的卡号信息。

select distinct BankDealInfo.CardID '银行卡卡号',OpenDate '开户日期',OpenMoney '开户金额
',DealMoney '单次最高金额' from BankCardInfo
inner join BankDealInfo on BankDealInfo.CardID=BankCardInfo.CardID 
where DealMoney =(select max(DealMoney ) from BankDealInfo 
where datediff(month,DealDate,getdate())=0) 
select top 1 BankCardInfo.CardID '银行卡卡号',OpenDate '开户日期',OpenMoney '开户金额
',sum(DealMoney ) '总交易最高金额' from BankCardInfo 
inner join BankDealInfo on BankDealInfo.CardID=BankCardInfo.CardID 
where datediff(month,DealDate,getdate())=0 
group by BankCardInfo.CardID,OpenDate,OpenMoney 
order by sum(DealMoney ) desc

(6) 查询挂失客户

--子查询
select CustName '客户姓名',CustTelephone '联系电话' from BankCustomerInfo 
where CustNum in (select CustNum from BankCardInfo where CardLoss='是') 
--内连接
select CustName '客户姓名',CustTelephone '联系电话' from BankCustomerInfo 
inner join BankCardInfo on BankCardInfo.CustNum = BankCustomerInfo.CustNum 
where CardLoss='是'

(7) 催款提醒业务

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

select CustName '客户姓名',CustTelephone '联系电话',CardMoney '存款金额' from BankCardInfo 
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
where CardMoney<=10000

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

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

       显示的列名全为中文，要求先判断该视图是否存在，若存在，则先删除。

if object_id('vw_userinfo','v') is not null 
drop view vw_userinfo 
go 
create view vw_userinfo 
as 
select CustNum '客户编号',CustName '开户名',CustID '身份证号',CustTelephone '电话号码',CustAddress '居住地址' 
from BankCustomerInfo 
go
select * from vw_userinfo

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

if object_id('VW_CardInfo','v') is not null 
drop view VW_CardInfo 
go 
create view VW_CardInfo 
as 
select CardID '银行卡卡号',CustName '姓名', MoneyType '货币类型',BusName '储蓄类型
',OpenDate '开户日期',CardMoney '存款金额',Password'密码', CardLoss '是否挂失' from 
BankCardInfo 
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType 
go 
select * from VW_CardInfo

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

if object_id('VW_TransInfo','v') is not null 
drop view VW_TransInfo 
go 
create view VW_TransInfo
as 
select DealDate '交易日期',DealType '交易类型',CardID '银行卡卡号',DealMoney'交易
金额',DealNote '备注' from BankDealInfo 
go 
         select * from VW_TransInfo order by 交易日期

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

       根据客户登录名（采用实名制访问银行系统）查询该客户账户信息的视图，利用SQL SERVER 系统函数 system_user 获得数据库用户名。

if object_id('VW_OneUserInfo','v') is not null 
drop view VW_OneUserInfo
go 
create view VW_OneUserInfo
as 
select CustNum '客户编号',CustName '开户名',CustID '身份证号',CustTelephone '电话号码',CustAddress '居住地址' 
from BankCustomerInfo 
where CustName=system_user 
go 
select * from VW_OneUserInfo

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

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

描述：
        根据银行卡号和交易金额实现银行卡的存款和取款业务。
        每一笔存款，取款业务都要计入银行交易账，并同时更新客户的存款余额。
        如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查 不合格，那么中断取款业务，给出提示信息后退出。
        检查客户输入的密码是否正确。
        账户取款金额是否大于当前存款额加 1 。

  --判断存储过程是否存在.存在则删除
if object_id('proc_TakeMoney','p') is not null 
drop procedure proc_TakeMoney
go 
--创建存取款业务的存储过程
create procedure proc_TakeMoney 
@CardID char(19),@money money,@pwd char(6)=null 
as 
--不返回受影响的行数
set nocount on 
declare @existbanlance money 
--启动事务
begin transaction
select @existbanlance=CardMoney from BankCardInfo where CardID=@CardID 
print '交易前,卡号'+@CardID 
print '交易正进行,请稍后...' 
--如果输入参数@pwd 为空,则为取款业务.否则为存款业务
if (select CardLoss from BankCardInfo where CardID=@CardID)='是' 
begin 
print '本卡已挂失,不能交易!' 
rollback transaction 
return 
end 
if(@pwd is not null) 
begin 
--取款
if exists(select * from BankCardInfo where CardID=@CardID and Password=@pwd) 
begin 
if(@money<=@existbanlance-1) 
insert into BankDealInfo 
(CardID,DealDate,DealMoney,DealType,DealNote) 
values (@CardID,getdate(),@money,'支取','通过存储过程操作') 
else 
print '取款交易失败.余额不足,请减少取款' 
end 
else 
begin 
print '取款交易失败,卡号或密码有错误' 
rollback transaction 
return 
end
end 
else 
begin 
--办理存款业务
if exists(select * from BankCardInfo where CardID=@CardID) 
insert into BankDealInfo 
(CardID,DealDate,DealMoney,DealType,DealNote) 
values (@CardID,getdate(),@money,'存入','通过存储过程操作') 
else 
begin 
print '存款失败,卡号不存在' 
rollback transaction 
return 
end 
end 
--判断事务处理是否有异常.没有提交.有则回滚
if(@@error<>0) 
begin 
print '交易失败,发生未知错误' 
rollback transaction 
end 
else 
begin 
commit transaction 
print '交易成功,交易金额为:'+ltrim(str(@money)) 
select @existbanlance=CardMoney from BankCardInfo where CardID=@CardID 
print '卡号:'+@CardID+',余额为:'+ltrim(str(@existbanlance)) 
end
--显示银行卡用户详情和交易详情
select * from VW_CardInfo 
select * from VW_TransInfo order by 交易日期 
go 
--执行存款存储过程
exec proc_TakeMoney @CardID='1010 3576 1234 5688',@money=2000 
--执行取款存储过程
exec proc_TakeMoney @CardID='1010 3576 1234 5688',@money=1200,@pwd='123465'

(2) 产生随机卡号

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

if object_id('proc_RandCardID','p') is not null 
drop procedure proc_RandCardID
go 
--创建随机卡号的存储过程
create procedure proc_RandCardID
@randcardid char(19) output 
as 
declare @r numeric(8,8),@tmpstr char(10) 
--产生随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数
set 
@r=rand(datepart(month,getdate())*100000+datepart(second,getdate())*1000+datepart(millisecond,getdate())) 
set @tmpstr=convert(char(10),@r) 
set @randcardid='1010 3576 '+substring(@tmpstr,3,4)+' '+substring(@tmpstr,7,4) 
go 
declare @mycardid1 char(19) 
exec proc_RandCardID @mycardid1 output 
print '产生随机卡号为'+@mycardid1

(3) 完成开户业务

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

if object_id('proc_OpenAccount','p') is not null 
drop procedure proc_OpenAccount
go 
--创建存取款业务的存储过程
create procedure proc_OpenAccount
@CustName char(20),@CustID char(18),@CustTelephone varchar(20),@OpenMoney 
money,@BusName varchar(20),@CustAddress varchar(100) 
as 
--不返回受影响的行数
set nocount on 
declare @DepositType int,@CardID char(19),@CustNum int 
--判断存款类型是否正确
if exists(select * from BankBusinessType where BusName=@BusName) 
begin 
begin tran 
select @DepositType=BusNum from BankBusinessType where BusName=@BusName 
exec proc_randcardid @CardID output 
--选出不重复的卡号
while(exists(select * from BankCardInfo where CardID=@CardID)) 
exec proc_RandCardID @CardID output 
--插入一条客户信息记录,身份证一样不重复插入
if not exists(select * from BankCustomerInfo where CustID=@CustID ) 
begin 
insert BankCustomerInfo 
(CustName,CustID,CustTelephone,CustAddress) 
values (@CustName,@CustID,@CustTelephone,@CustAddress) 
set @CustNum=@@identity 
end 
else 
select @CustNum=CustNum from BankCustomerInfo where CustID=@CustID 
insert BankCardInfo 
(CardID,Password,MoneyType,DepositType,OpenDate,OpenMoney,CardLoss,CustNum,CardMoney)
values (@CardID,'888888','rmb',@DepositType,getdate(),@OpenMoney,'否
',@CustNum,@OpenMoney) 
if(@@error<>0) 
begin 
print '尊敬的客户，开户不成功，所有操作均撤销' 
rollback tran 
end 
else 
begin 
commit tran 
print '尊敬的客户，开户成功，系统为你产生的随机卡号是:'+@CardID+',开户日
期:'+convert(char(10),getdate(),111)+',开户金额:'+ltrim(str(@OpenMoney)) 
end 
--显示开户的客户表信息和银行卡信息
select * from vw_userinfo 
select * from VW_CardInfo 
end 
else 
print '尊敬的客户,未能成功开户,存款类型不正确,请重新输入!' 
go 
exec proc_OpenAccount @CustName = '陈曦',@CustID = '152825198909120124',@CustTelephone = 
'13574129856',@OpenMoney = 1200,@BusName = '定活两便',@CustAddress = '海天家园' 
exec proc_OpenAccount @CustName = '林念初',@CustID = '152825197905149652',@CustTelephone = 
'15852147869',@OpenMoney = 1000,@BusName = '活期',@CustAddress = '峻峰华庭'

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

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

if object_id('proc_PagingDisplay','p') is not null 
drop procedure proc_PagingDisplay
go 
create procedure proc_PagingDisplay
@page int,@count int 
as 
select rownumber '交易编号',DealDate '交易日期',DealType '交易类型',CardID '银行卡卡号
',DealMoney'交易金额' from (select row_number() over(order by DealNum) 
rownumber,DealDate,DealType,CardID,DealMoney from BankDealInfo) c
where c.rownumber between (@page-1)*@count+1 and @page*@count 
go 
exec proc_PagingDisplay @page = 2,@count = 5

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

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

if object_id('proc_getwithouttrade','p') is not null 
drop procedure proc_getwithouttrade 
go 
create procedure proc_getwithouttrade 
@startdate datetime=null,@enddate datetime=null 
as 
declare @name char(16),@icno char(18),@tel char(15),@addr char(50),@moneysum 
money=0,@customersum int=0,@money int 
if (@startdate is null) 
set @startdate=convert(datetime,convert(char(8),getdate(),120)+'1') 
if (@enddate is null) 
set @enddate=getdate() 
--有的客户未交易.所以用右连接或者全连接
declare cur_outtrade cursor 
for select distinct CustName,CustID,CustTelephone,CustAddress,CardMoney from BankDealInfo 
right join BankCardInfo on BankCardInfo.CardID=BankDealInfo.CardID 
right join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
where BankDealInfo.CardID not in(select BankDealInfo.CardID from BankDealInfo where 
DealDate>=@startdate and DealDate<=@enddate) 
print convert(char(16),'客户姓名')+convert(char(20),'身份证号')+convert(char(16),'电
话')+convert(char(20),'地址') 
open cur_outtrade 
fetch next from cur_outtrade into @name,@icno,@tel,@addr,@money 
while @@fetch_status=0 
begin
print 
convert(char(16),@name)+convert(char(20),@icno)+convert(char(16),@tel)+convert(char(20),@addr) 
set @moneysum=@moneysum+@money 
set @customersum=@customersum+1 
fetch next from cur_outtrade into @name,@icno,@tel,@addr,@money 
end 
print '统计未发生交易的客户' 
print '客户人数:'+ltrim(str(@customersum))+',客户总余额:'+ltrim(str(@moneysum)) 
close cur_outtrade 
deallocate cur_outtrade 
go 
exec proc_getwithouttrade @startdate='2015-8-19',@enddate='2015-9-19' 
exec proc_getwithouttrade

8、实训八：利用事务实现转账

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

--判断该存储过程是否存在，若存在，则删除
if object_id('usp_transfer','p') is not null 
drop procedure usp_transfer 
go 
--创建转账存储过程，需要传递两个账户号码及转账金额
create procedure usp_transfer 
@outCardID char(19),@inCardID char(19),@dealacount money 
as 
--不返回受影响的行数
set nocount on 
--声明个变量分别存放转出账户和转入账户的转账之后的余额
declare @outexistbalance money,@inexistbalance money 
--声明变量存放转出账户的姓名、货币类型、存款类型和开户日期
declare @outCustName char(20),@outMoneyType char(5),@outBusName 
char(20),@outOpenDate date 
--声明变量存放转入账户的姓名、货币类型、存款类型和开户日期
declare @inCustName char(20),@inMoneyType char(5),@inBusName char(20),@inOpenDate 
date 
--声明交易日.类型.交易金额.备注
declare @DealDate date,@DealType char(20),@DealMoney money,@DealNote char(100) 
print '开始转账，请稍后...' 
--判断转出账户及余额是否大于转出金额+1 
if exists(select * from BankCardInfo where CardID=@outCardID and 
CardMoney>=@dealacount+1) 
begin 
--判断转入账户是否存在
if not exists(select * from BankCardInfo where CardID=@inCardID) 
begin 
print '转入账户不存在,转账交易失败' 
return 
end 
--判断转出账户是否存在
if not exists(select * from BankCardInfo where CardID=@outCardID) 
begin 
print '转出账户不存在,转账交易失败' 
return 
end 
--启动事务机制
begin tran
print '交易正进行，请稍后...' 
--首先增加一条转出账户的支出交易记录
insert into BankDealInfo (CardID,DealDate,DealMoney,DealType,DealNote) 
values (@outCardID,getdate(),@dealacount,'支取','转出') 
-- 给交易日.类型.交易金额.备注赋值
select 
@DealDate=DealDate,@DealType=DealType,@DealMoney=DealMoney,@DealNote=DealNote from BankDealInfo where DealNum=@@identity 
--增加一条转入账户的存取交易记录
insert into BankDealInfo (CardID,DealDate,DealMoney,DealType,DealNote) 
values (@inCardID,getdate(),@dealacount,'存入','转入') 
--取得转账后两个账户的余额
select @outexistbalance=(select CardMoney from BankCardInfo where CardID=@outCardID) 
select @inexistbalance=(select CardMoney from BankCardInfo where CardID=@inCardID) 
--判断事务处理里是否有异常，若没有异常，则提交，若有异常，则回滚
if @@error<>0 
begin 
print '转账交易失败' 
rollback tran 
end 
else 
begin 
commit tran 
print '交易成功,交易金额:'+convert(varchar(10),@dealacount) 
print '卡号'+@outCardID+' 余额:'+ltrim(str(@outexistbalance)) 
print '卡号'+@inCardID+' 余额:'+ltrim(str(@inexistbalance)) 
print '转账成功!' 
end
end 
--分别打印转出账户对账单和转入账户对账单
else
begin 
print '转出账户余额不足，此次转账交易失败' 
return 
end
print '打印转出账户对账单' 
--获取转出账户的相关信息
select 
@outCustName=CustName,@outMoneyType=MoneyType,@outBusName=BusName,@outOpenDate=OpenDate from BankCardInfo 
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType 
where CardID=@outCardID 
print '---------------------------------------------------' 
print '卡号:'+@outCardID 
print '姓名:'+@outCustName 
print '存款类型:'+@outMoneyType 
print '开户日期:'+convert(char(14),@outOpenDate) 
print convert(char(15),'交易日')+convert(char(15),'类型')+convert(char(15),'交易金额
')+convert(char(15),'备注') 
print '---------------------------------------------------' 
print 
convert(char(15),@DealDate)+convert(char(15),@DealType)+convert(char(15),ltrim(@DealMoney))+convert(char(15),@DealNote) 
print '打印转入账户对账单' 
--获取转入账户的相关信息
select 
@inCustName=CustName,@inMoneyType=MoneyType,@inBusName=BusName,@inOpenDate=OpenDate from BankCardInfo 
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum 
inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType 
where CardID=@inCardID 
print '---------------------------------------------------' 
print '卡号:'+@inCardID 
print '姓名:'+@inCustName 
print '存款类型:'+@inMoneyType 
print '开户日期:'+convert(char(14),@inOpenDate) 
-- 给交易日.类型.交易金额.备注赋值
select 
@DealDate=DealDate,@DealType=DealType,@DealMoney=DealMoney,@DealNote=DealNote from BankDealInfo where DealNum=@@identity 
print convert(char(15),'交易日')+convert(char(15),'类型')+convert(char(15),'交易金额
')+convert(char(15),'备注') 
print '---------------------------------------------------' 
print 
convert(char(15),@DealDate)+convert(char(15),@DealType)+convert(char(15),ltrim(@DealMoney))+convert(char(15),@DealNote) 
Go
exec usp_transfer @outCardID='1010 3576 1234 5678',@inCardID='1010 3576 1234 5688',@dealacount=50