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

朱雀 2022-10-12 01:03 239阅读 0赞

### 目录 ###

*  前言
 *  一、项目背景
 *   *  1、项目任务
     *  2、项目技能目标
     *  3、需求概述
     *  4、开发环境
     *  5 、问题分析
     *   *  (1) 银行存取款业务介绍
         *  (2) 客户信息
         *  (3) 银行卡账户信息
         *  (4) 银行卡交易信息
         *  (5) 银行卡手工账户和存取款单据信息
 *  二、项目实训内容
 *   *  1、实训一：制定《数据库设计与编程规范》
     *  2、实训二：数据库分析设计与建模
     *   *  (1) 分析银行 ATM 存取款系统实体
         *  (2) 规范数据库结构设计
         *  (3) 绘制 CDM 模型、生成 PDM 模型
     *  3、实训三：创建数据库
     *   *  (1) 创建数据库
         *  (2) 创建各个数据表及相关的约束
         *  (3) 添加外键约束和生成数据库关系图
     *  4、实训四：创建触发器和插入测试数据
     *   *  (1) 创建级联触发器
         *  (2) 插入数据表的测试数据
     *  5、实训五：模拟常规业务
     *   *  (1) 修改客户密码
         *  (2) 办理银行卡挂失
         *  (3) 统计银行资金流通余额和盈利结算
         *  (4) 查询本周开户信息
         *  (5) 查询本月单次交易金额最高的卡号和总交易金额最高的卡号
         *  (6) 查询挂失客户
         *  (7) 催款提醒业务
     *  6、实训六：创建、使用视图
     *   *  (1) 输出银行客户记录视图VW\_userInfo
         *  (2) 输出银行卡记录视图VW\_CardInfo
         *  (3) 输出银行卡交易记录视图VW\_TransInfo
         *  (4) 根据客户登录名查询该客户账户信息VW\_OneUserInfo
     *  7、实训七：存储过程实现业务处理
     *   *  (1) 完成存款或取款业务
         *  (2) 产生随机卡号
         *  (3) 完成开户业务
         *  (4) 分页显示查询交易数据
         *  (5) 统计未发生交易的账户
     *  8、实训八：利用事务实现转账

# 前言 #

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

--------------------

# 一、项目背景 #

## 1、项目任务 ##

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

## 2、项目技能目标 ##

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

## 3、需求概述 ##

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

## 4、开发环境 ##

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

## 5 、问题分析 ##

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

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

银行为客户提供了各种银行存取款业务。详见表 1  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70]

### (2) 客户信息 ###

每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要 提供的开户数据如表 2 所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 1]

### (3) 银行卡账户信息 ###

银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保 存账户如表 3 所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 2]  
客户持银行卡在 ATM 机上输入密码，经系统验证身份后办理存款、取款和转账 等银行业务。银行规定，每个账户当前的存款金额不得小于 1 元。

### (4) 银行卡交易信息 ###

银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表 4 所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 3]

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

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

# 二、项目实训内容 #

## 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 存取款系统实体 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 6]

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 8]

## 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 里自动生成数据库关系图，如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 9]

## 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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 10]  
BankCustomerInfo 表的测试数据：

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

![在这里插入图片描述][20210704131321514.png]

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

![在这里插入图片描述][20210704131353628.png]  
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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 11]

## 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

![在这里插入图片描述][20210704131602111.png]

### (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

![在这里插入图片描述][20210704131654994.png]

### (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

![在这里插入图片描述][20210704131830957.png]

### (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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 12]

### (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='是'

![在这里插入图片描述][20210704132036379.png]

### (7) 催款提醒业务 ###

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

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

![在这里插入图片描述][20210704132135430.png]

## 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

![在这里插入图片描述][2021070413223141.png]

### (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

![在这里插入图片描述][20210704132306456.png]

### (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 交易日期

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 13]

### (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

![在这里插入图片描述][20210704132437525.png]

## 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'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 14]

### (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

![在这里插入图片描述][20210704132754778.png]

### (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 = '峻峰华庭'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 15]

### (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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 16]

### (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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 17]

## 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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70]: /images/20221005/3f562d1b3900458ca601cf6d37d34d3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 1]: /images/20221005/4ec61cfeb4a04e638a3f603fd44a6548.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 2]: /images/20221005/d8b739e8a9764ba6b6bedf3d4e948699.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 3]: /images/20221005/334d2db6376b490c9339d0ff681a1dba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 4]: /images/20221005/56b3059a47464042874f75e91ce35455.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 5]: /images/20221005/9f5aff6acb3c4e6d8087e568fad39999.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 6]: /images/20221005/e450a4bccb584218b7e1ec75445d5b08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 7]: /images/20221005/337c394304ed4faba79dc044e0a5f202.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 8]: /images/20221005/e39da6ae785448c0b151b96238f2fe27.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 9]: /images/20221005/2f14a32293584318ad824dd045a64a6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 10]: /images/20221005/089d0463199049828948ff4f0c79d38a.png
[20210704131321514.png]: /images/20221005/9b7275cf642847709728b0ea12aeffe2.png
[20210704131353628.png]: /images/20221005/0dd198e6f0b743f395d38d615f9013e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 11]: /images/20221005/be92c9eb5a364bc696d61744ce9fa550.png
[20210704131602111.png]: /images/20221005/a0f75ac0bdb6411cb5726e67b0b85eee.png
[20210704131654994.png]: /images/20221005/f50ea739c7ca49dc8254824c8e05738b.png
[20210704131830957.png]: /images/20221005/a37dd2b5e6c74542b264177bbc2cf20d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 12]: /images/20221005/741241e536304cf19af660295023b2f6.png
[20210704132036379.png]: /images/20221005/19889ad2eb2f44f4800dddd701f2e5b8.png
[20210704132135430.png]: /images/20221005/afed133a0f06401ca953f73f2ec72f75.png
[2021070413223141.png]: /images/20221005/cdc6b4baa9834ec785d1c8ce5ff7a155.png
[20210704132306456.png]: /images/20221005/9bd91de5ad464e34bc7575906957fe8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 13]: /images/20221005/2df841d0cf3d43c8941b83bfe39609be.png
[20210704132437525.png]: /images/20221005/0018bf2bf2fb482d91a25ae3ab345436.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 14]: /images/20221005/45bba236decf4c0481836d9e0ca9482c.png
[20210704132754778.png]: /images/20221005/245029127fa34895878d7b517f4198f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 15]: /images/20221005/0abec2d3219e40f6984e0096c93010cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 16]: /images/20221005/c81b0e5366a541aeb647b55858a2a321.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L015X2RhaWx5X2xpZmU_size_16_color_FFFFFF_t_70 17]: /images/20221005/f5cda4526c5044739f72e9de9aead30f.png