热门标签
热门文章
- 1NLP在医疗领域的应用：病历分析与疾病预测
- 2GAT-图注意力网络_gat图注意力网络
- 3没事学着玩，大白学 aardio 第一段代码_aardio退出键
- 4图像的传统特征和深度特征介绍及其提取方法
- 5OpenHarmony 关闭息屏方式总结_openharmony 设置不息屏
- 6tensorflow_serving的部署_tensorflow serving 部署
- 7【AI大模型应用开发】【RAG评估】0. 综述：一文了解RAG评估方法、工具与指标
- 8python自然语言处理——NLTK——词性标签（pos_tag）_nltk.pos_tag
- 9【云原生】Dockerfile文件详解_前端dockerfile文件
- 10【经验总结】Ubuntu 22.04 安装 ROS_ubuntu22.04安装ros1
当前位置: article > 正文
课程设计：基于SQL Server的银行ATM 存取款机系统设计与实现_某银行是一家民办的小型银行企业,现有十多万客户。为提高工作效率,该银行将请公司
作者：你好赵伟 | 2024-04-03 14:14:57
赞
踩
某银行是一家民办的小型银行企业,现有十多万客户。为提高工作效率,该银行将请公司
目录
| 加油加油 |
前言
本文章主要是在学了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% )
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
(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)
- 1
- 2
- 3
- 4
在 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
创建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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
创建 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
(2) 插入数据表的测试数据
使用 T-SQL 语句向每个表插入如下所示测试数据，要保证业务数据的一致性和 完整性。
BankBusinessType 表的测试数据：
insert into BankBusinessType
(BusName,BusDescribe)
values ('活期','无固定存期,可随时存取,存取金额不限的一种比较灵活的存款'),
('定活两便','事先不约定存定期,一次性存入,一次性支取的存款'),
('通知','不约定存期,支取是需提前通知银行,约定支取日期和金额方能支取的存款'),
('整存整取年','整笔存入,到期提取本息'),
('整存整取年','整笔存入,到期提取本息'),
('整存整取年','整笔存入,到期提取本息'),
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'),
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'),
('零存整取年','事先原定金额,逐月按约定金额存入,到期支付本息'),
('自助转账','银行atm 存取款机上办理银行卡之间互相划转')
go
select * from BankBusinessType
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
BankCustomerInfo 表的测试数据：
insert into BankCustomerInfo(CustName,CustID,CustTelephone,CustAddress)
values ('叶春萌','152825198512548541','13585941287','丽都新城'),
('周明','152821198401248574','0147-7418527','松石名第')
go
select * from BankCustomerInfo
- 1
- 2
- 3
- 4
- 5
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
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
- 1
- 2
- 3
- 4
(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
- 1
- 2
- 3
- 4
- 5
- 6
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
(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())
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
(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='是'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
(7) 催款提醒业务
根据某种业务（如代缴电话费、代缴手机费或房贷等）的需要，每个月末，查询出客户账户上余额少于 10000 元，由银行统一致电催款。
select CustName '客户姓名',CustTelephone '联系电话',CardMoney '存款金额' from BankCardInfo
inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum
where CardMoney<=10000
- 1
- 2
- 3
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
(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 交易日期
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
(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 = '峻峰华庭'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
(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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/355837
推荐阅读
相关标签
