数据库课程设计-银行ATM存取款机系统设计与实现.doc

.给学弟学妹的福利,数据库这个难度还算可以吧.也不是很大.大家多多思考一下.代码仅供参考.最好不要复制.手敲一遍也是好的.数据库技术与开发项目实训设计报告项目名称：银行ATM存取款机系统设计与实现姓 名：谭海兵专 业：软件工程指导教师：余金林完成日期：2015-11-22一、项目背景31、项目任务32、项目技能目标33、需求概述34、开发环境35、问题分析4二、项目实训内容61、实训一：制定数据库设计与编程规范62、实训二：数据库分析设计与建模123、实训三：创建数据库144、实训四：创建触发器和插入测试数据195、实训五：模拟常规业务266、实训六：创建、使用视图307、实训七：存储过程实现业务处理338、实训八：利用事务实现转账498、心得体会54一、项目背景1、项目任务 使用PowerDesigner完成数据库设计 创建数据库、创建表、创建约束 使用触发器和插入测试数据 模拟常规业务、创建视图 使用存储过程实现业务处理 利用事务实现较复杂的数据更新2、项目技能目标 使用PowerDesigner完成数据库概念模型和数据库物理模型设计。 使用T-SQL语句创建数据库、表和各种约束。 使用T-SQL语句编程实现常见业务。 使用触发器实现多表之间的级联更新。 使用事务和存储过程封装业务逻辑。 使用视图简化复杂的数据查询。 使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的安全性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创建数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境 数据库：SQL SERVER 2008开发版 数据库建模工具：PowerDesigner155、 问题分析该项目的ATM存取款机业务如下：(1) 银行存取款业务介绍 银行为客户提供了各种银行存取款业务。详见表1表1 银行存取款业务业务描述活期无固定存期，可随时存取，存取金额不限的一种比较灵活的存款定活两便事先不约定存期，一次性存入，一次性支取的存款通知不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款整存整取选择存款期限，整笔存入，到期提取本息的一种定期储蓄。银行提供的存款期限有1年、2年和3年零存整取一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。银行提供的存款期限由1年、2年和3年自助转账在ATM存取款机上办理同一币种账户的银行卡之间互相划转(2) 客户信息每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表2所示：表2 开设银行卡账户的客户信息数据描述姓名必须提供身份证号唯一确定客户，如果是二代身份证，则是由17位数字和1位数字或者字符X构成。如果是一代身份证，则身份证号全部是15位数字。联系电话分为座机号码和手机号码：座机号码由数字和“-”构成，由以下两种格式：XXX-XXXXXXXX或者XXXX-XXXXXXX。手机号码由11位数字构成，且前2位必须是13或者15开头。居住地址可以选择(3) 银行卡账户信息 银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表3所示：表3 银行卡账户信息数据描述卡号银行的卡号由16位数字组成，其中：一般前8位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为1010 3576 XXXX XXXX，后8位必须是随机产生且唯一，每4位号码后有空格。密码由6位数字构成，开户时默认为“888888”币种默认为RMB，目前该银行尚未开设其他币种存款业务。存款类型必须选择开户日期客户开设银行卡账户的日期，默认为当日开户金额客户开设银行卡账户时存入的金额，规定不得小于1元。是否挂失默认为“否” 客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。银行规定，每个账户当前的存款金额不得小于1元。(4) 银行卡交易信息 银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表4所示：表4 银行卡交易信息数据描述卡号银行的卡号由16位数字组成交易日期默认为当日交易金额必须大于0元交易类型包括：存入和支取两种备注对每笔交易做必要的说明(5) 银行卡手工账户和存取款单据信息该银行要求这套软件实现银行客户的开户、存款、取款、转账和余额查询等业务，使得银行储蓄业务方便、快捷，同事保证银行业务数据的安全性。为使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的样本数据，以供项目开发时参考，参加表5和表6。表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数据库的负荷，因此在开发时间宽松情况下，要尽量将排序动作放到应用程序层去完成。代码注释要求 注释是指程序中会被编译器忽略掉的部分，目的是描述代码的用途及更新时间，合理的添加注释可以使得程序结构清晰，可以使代码更好理解，便于系统后续的维护。一般情况下，注释要不少于代码的十分之一。静态 SQLSQL语句要尽可能采用静态SQL，静态SQL第一次执行时会将编译器解析的结果存储在缓存中，下次执行该静态SQL时会直接从缓存中获取其执行计划，相当于是软解析，因此采用静态SQL可以减少语句的解析时间，提升了数据库的性能。最小事务原则数据库事务用来保持数据的一致性，但是对于一个执行时间较长的大事务，会造成数据库锁的增加，当锁越积越多的时候就会从行锁升级到页锁，从业锁升级到表锁，从而严重影响数据库的性能。因此，在能满足数据一致性的前提下，要尽量将非一致性要求的语句代码从事务中移除，以便提升数据库的并发访问。顺序提交 顺序提交是一个好的代码编写习惯，顺序提交可以减少死锁的发生，并且还能增加代码的可读性及可维护性。2、实训二：数据库分析设计与建模(1) 分析银行ATM存取款系统实体图1 银行存取款系统ER图(2) 规范数据库结构设计第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如在银行客户表BankCustomer中，不能将客户信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个客户的信息在表中只出现一次。简而言之，第一范式就是无重复的列。第二范式（2NF）要求实体的属性完全依赖于主关键字。如银行交易信息表BankDealInfo中，不能把卡号设为主键因为一个卡号可以发生多条交易记录。要确定唯一的一条信息，必须重新定义一个和其它属性无关的交易编号。这样要查询一条交易信息。就可以用交易编号。简而言之，第二范式就是属性完全依赖于主键。 第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。如银行卡表BankCard中，有了用户Id后。不能还添加用户姓名等相关的用户信息。否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。 (3) 绘制CDM模型、生成PDM模型图2 银行存取款系统数据概念模型CDM图3 银行存取款系统数据物理模型PDM3、实训三：创建数据库(1) 创建数据库使用Create DataBase语句创建“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下文件增长率为15%。-创建BankDB数据库，数据库文件和日志文件均保存在-文件夹d:sql下-文件增长率均为%，数据文件起始大小为MB，日志文件起始大小为MBCREATE DATABASE BankDBON PRIMARY(NAME=NBankDB,FILENAME=Nd:sqlBankDB.mdf,SIZE=5MB,FILEGROWTH=15%)LOG ON (NAME=NBankDB_log,FILENAME=Nd:sqlBankDB_log.ldf,SIZE=2MB,FILEGROWTH=15%)(2) 创建各个数据表及相关的约束根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。USE BankDBGO-判断银行业务类型是否存在,若存在则删除-sysobjects系统对象表。保存当前数据库的对象。-OBJECT_ID()根据对象名称返回该对象的idIF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(NBankBusinessType)DROP TABLE BankBusinessType-创建银行业务类型表CREATE TABLE BankBusinessType(-银行业务类型编号BBTId INT IDENTITY(1,1) PRIMARY KEY,-银行业务类型名称BBTName CHAR(20) NOT NULL,-银行业务描述BBTComment VARCHAR(100) )GO-判断银行卡客户是否存在,若存在则删除IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID(NBankCustomer)DROP TABLE BankCustomer-创建银行客户信息表CREATE TABLE BankCustomer(-客户编号BCId INT IDENTITY(1,1) PRIMARY KEY,-客户姓名BCName CHAR(20) NOT NULL,-客户身份证,定义身份证号前位必须是数字，后位可以是数字或者X。BCICNo CHAR(18) NOT NULL CHECK(LEFT(BCICNo,17) LIKE 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9 AND (RIGHT(BCICNo,1) LIKE 0-9 OR RIGHT(BCICNo,1) LIKE X),-客户联系电话,必须是固定电话号码或者手机号BCTel VARCHAR(20) NOT NULL CHECK(BCTel like 0-90-90-90-9_0-90-90-90-90-90-90-9 orBCTel like 0-90-90-9_0-90-90-90-90-90-90-90-9 or BCTel like 13580-90-90-90-90-90-90-90-90-9),-客户居住地址BCAddr varchar(100)GO-判断银行卡是否存在，若存在。则删除BankCardIF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(NBankCard)DROP TABLE BankCard-建立银行卡信息CREATE TABLE BankCard(-银行卡号BCNo CHAR(19) PRIMARY KEY CHECK(BCNo LIKE 1010 3576 0-90-90-90-9 0-90-90-90-9),-密码BCPwd CHAR(6) NOT NULL DEFAULT(888888),-币种BCCurrency CHAR(5) NOT NULL DEFAULT(RMB),-存款类型BCBBTId INT NOT NULL,-开户日期BCOpenDate DATE NOT NULL DEFAULT(GETDATE(),-开户金额BCOpenAmount MONEY NOT NULL CHECK(BCOpenAmount=1),-是否挂失BCRegLoss CHAR(2) DEFAULT(否),-客户编号BCBCId INT NOT NULL,-余额BCExistBalance MONEY NOT NULL)GO-判断交易信息BankDealInfo是否存在,若存在则删除IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(NBankDealInfo)DROP TABLE BankDealInfo-创建交易信息表CREATE TABLE BankDealInfo(-交易编号BDNo INT IDENTITY(1,1) PRIMARY KEY,-卡号BDBCNo CHAR(19) NOT NULL,-交易日期BDDealDate DATE NOT NULL,-交易金额BDDealAcount MONEY NOT NULL,-交易类型BDDealType CHAR(10) NOT NULL CHECK(BDDealType=存入 OR BDDealType=支取),-交易备注BDDealComment varchar(100)(3) 添加外键约束和生成数据库关系图添加子表外键约束及生成数据库关系图ALTER TABLE BankCardADD CONSTRAINT FK_BC_BBT FOREIGN KEY(BCBBTId) REFERENCES BankBusinessType(BBTId)ALTER TABLE BankCardADD CONSTRAINT FK_BC_BC FOREIGN KEY(BCBCId) REFERENCES BankCustomer(BCId)ALTER TABLE BankDealInfoADD CONSTRAINT FK_BDI_BC FOREIGN KEY(BDBCNo) REFERENCES BankCard(BCNo)在SQL SERVER里自动生成数据库关系图，如下图所示：图4 BankDb数据库关系图4、实训四：创建触发器和插入测试数据(1) 创建级联触发器 创建Insert触发器 在交易信息表创建一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。-在交易信息表插入触发器.使用游标当新增一个交易信息.修改银行卡的存款余额-检查触发器是否存在.存在则删除IF(OBJECT_ID(tr_InsertDealInfo,TR) IS NOT null)DROP TRIGGER tr_InsertDealInfoGO-创建Insert触发器CREATE TRIGGER tr_InsertDealInfo ON BankDealInfo FOR INSERTASDECLARE type CHAR(10),sum MONEY,BDBCNo CHAR(19)-定义游标,指向inserted表DECLARE cursor_BankDealInfo CURSOR FOR SELECT BDDealType,BDDealAcount,BDBCNo FROM Inserted-打开游标OPEN cursor_BankDealInfo-读取游标.赋值给相应字段FETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNo-FETCH_STATUS=0 FETCH 语句成功WHILE FETCH_STATUS=0BEGIN-根据交易类型.更新银行卡余额.RTRIM和LRAIM是去除首尾空格IF(RTRIM(LTRIM(type)=存入)UPDATE BankCard SET BCExistBalance=BCExistBalance+sum WHERE BCNo=BDBCNoIF(RTRIM(LTRIM(type)=支取)UPDATE BankCard SET BCExistBalance=BCExistBalance-sum WHERE BCNo=BDBCNoFETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoEND-关闭游标CLOSE cursor_BankDealInfo-释放游标DEALLOCATE cursor_BankDealInfoGO 创建Delete触发器在交易信息表创建一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。-删除-触发器存在则删除IF(OBJECT_ID(tr_DelDealInfo,TR) IS NOT NULL)DROP TRIGGER tr_DelDealInfoGO-创建delete触发器CREATE TRIGGER tr_DelDealInfo ON BankDealInfo FOR DELETEASDECLARE type CHAR(10),sum MONEY,BDBCNo CHAR(19)-定义游标,指向deleted表DECLARE cursor_BankDealInfo CURSOR FOR SELECT BDDealType,BDDealAcount,BDBCNo FROM Deleted-打开游标OPEN cursor_BankDealInfo-读取游标,赋值FETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoWHILE FETCH_STATUS=0BEGINIF(RTRIM(LTRIM(type)=存入)UPDATE BankCard SET BCExistBalance=BCExistBalance-sum WHERE BCNo=BDBCNoIF(RTRIM(LTRIM(type)=支取)UPDATE BankCard SET BCExistBalance=BCExistBalance+sum WHERE BCNo=BDBCNoFETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoENDCLOSE cursor_BankDealInfoDEALLOCATE cursor_BankDealInfoGO 创建Update触发器在交易信息表创建一个Update触发器，当更新一条交易信息时，修改相应银行卡的存款余额。-更新-触发器存在则删除IF(OBJECT_ID(tr_UpdateDealInfo,TR) IS NOT NULL)DROP TRIGGER tr_UpdateDealInfoGOCREATE TRIGGER tr_UpdateDealInfo ON BankDealInfo FOR UPDATEASDECLARE type CHAR(10),sum MONEY,BDBCNo CHAR(19)-定义游标,指向deleted表DECLARE cursor_BankDealInfo CURSOR FOR SELECT BDDealType,BDDealAcount,BDBCNo FROM Deleted-打开游标OPEN cursor_BankDealInfo-读取游标,赋值FETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoWHILE FETCH_STATUS=0BEGINIF(RTRIM(LTRIM(type)=存入)UPDATE BankCard SET BCExistBalance=BCExistBalance-sum WHERE BCNo=BDBCNoIF(RTRIM(LTRIM(type)=支取)UPDATE BankCard SET BCExistBalance=BCExistBalance+sum WHERE BCNo=BDBCNoFETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoENDCLOSE cursor_BankDealInfoDEALLOCATE cursor_BankDealInfo-定义游标,指向inserted表DECLARE cursor_BankDealInfo CURSOR FOR SELECT BDDealType,BDDealAcount,BDBCNo FROM Inserted-打开游标OPEN cursor_BankDealInfo-读取游标.赋值给相应字段FETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNo-FETCH_STATUS=0 FETCH 语句成功WHILE FETCH_STATUS=0BEGIN-根据交易类型.更新银行卡余额.RTRIM和LRAIM是去除首尾空格IF(RTRIM(LTRIM(type)=存入)UPDATE BankCard SET BCExistBalance=BCExistBalance+sum WHERE BCNo=BDBCNoIF(RTRIM(LTRIM(type)=支取)UPDATE BankCard SET BCExistBalance=BCExistBalance-sum WHERE BCNo=BDBCNoFETCH NEXT FROM cursor_BankDealInfo INTO type,sum,BDBCNoEND-关闭游标CLOSE cursor_BankDealInfo-释放游标DEALLOCATE cursor_BankDealInfo(2) 插入数据表的测试数据使用T-SQL语句向每个表插入如下所示测试数据，要保证业务数据的一致性和完整性。 BankBusinessType表的测试数据INSERT INTO BankBusinessType(BBTName,BBTComment)VALUES(活期,无固定存期,可随时存取,存取金额不限的一种比较灵活的存款),(定活两便,事先不约定存定期,一次性存入,一次性支取的存款),(通知,不约定存期,支取是需提前通知银行,约定支取日期和金额方能支取的存款),(整存整取年,整笔存入,到期提取本息),(整存整取年,整笔存入,到期提取本息),(整存整取年,整笔存入,到期提取本息),(零存整取年,事先原定金额,逐月按约定金额存入,到期支付本息),(零存整取年,事先原定金额,逐月按约定金额存入,到期支付本息),(零存整取年,事先原定金额,逐月按约定金额存入,到期支付本息),(自助转账,银行ATM存取款机上办理银行卡之间互相划转)GOSELECT * FROM BankBusinessType图5 BankBusinessType表测试数据 BankCustomer表的测试数据INSERT INTO BankCustomer (BCName,BCICNo,BCTel,BCAddr)VALUES (叶春萌,152825198512548541丽都新城), (周松石名第)GOSELECT * FROM BankCustomer图6 BankCustomer表测试数据 BankCard表的测试数据INSERT INTO BankCard (BCNo,BCPwd,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCId,BCExistBalance)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)GOSELECT * FROM BankCard图7 BankCard表测试数据 BankDealInfo表的测试数据INSERT INTO BankDealInfo (BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)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,支取,刷卡消费)GOSELECT * FROM BankDealInfo图8 BankDealInfo表测试数据5、实训五：模拟常规业务(1) 修改客户密码根据卡号修改指定2个客户的银行密码，其中第一个客户1010 3576 1234 5678密码修改为123456，第二个客户1010 3576 1234 5688修改为123123。-(1)修改客户密码UPDATE BankCard SET BCPwd=123465 WHERE BCNo=1010 3576 1234 5678UPDATE BankCard SET BCPwd=123123 WHERE BCNo=1010 3576 1234 5688SELECT BCNo 银行卡卡号,BCPwd 密码,BCCurrency 货币类型,BCBBTId 储蓄种类,BCOpenDate 开户日期,BCOpenAmount 开户金额, BCRegLoss 是否挂失,BCBCId客户编号,BCExistBalance 存款金额 FROM BankCard图9 修改客户密码效果图(2) 办理银行卡挂失卡号为1010 3576 1234 5678的银行卡丢失，申请挂失。-(2)办理银行卡挂失UPDATE BankCard SET BCRegLoss=是 WHERE BCNo=1010 3576 1234 5678SELECT BCNo 银行卡卡号,BCPwd 密码,BCCurrency 货币类型,BBTName 储蓄类型,BCOpenDate 开户日期,BCOpenAmount 开户金额, BCRegLoss 是否挂失,BCName客户姓名,BCExistBalance 存款金额 FROM BankCard INNER JOIN BankCustomer ON BankCard.BCBCId=BankCustomer.BCIdINNER JOIN BankBusinessType ON BankCard.BCBBTId=BankBusinessType.BBTId图10 办理银行卡挂失效果图(3) 统计银行资金流通余额和盈利结算存入代表资金流入，支取代表资金流出。计算公式：资金流通余额=总存入金额-总支取金额假定存款利率为千分之三，贷款利率为千分之八。计算公式：盈利结算=总支取金额*0.008-总存入金额*0.003。要求创建一个存储过程proc_staticsBanlanceAndProfit。-(3)统计银行资金流通余额和盈利结算IF EXISTS(SELECT * FROM sys.sysobjects WHERE name=proc_staticsBanlanceAndProfit)DROP PROCEDURE proc_staticsBanlanceAndProfitGOCREATE PROCEDURE proc_staticsBanlanceAndProfitASDECLARE InMoney MONEYDECLARE OutMoney MONEYSELECT InMoney=SUM(BDDealAcount) FROM BankDealInfo WHERE BDDealType=存入SELECT OutMoney=SUM(BDDealAcount) FROM BankDealInfo WHERE BDDealType=支取PRINT 存入总金额:+LTRIM(STR(InMoney)+RMB,支取总金额:+LTRIM(STR(OutMoney)+RMB,银行流通余额:+LTRIM(STR(InMoney-OutMoney)+RMB,盈利余额:+LTRIM(STR(OutMoney*0.008-InMoney*0.003)+RMBGOEXEC proc_staticsBanlanceAndProfit图11 统计银行资金流通余额和盈利结算(4) 查询本周开户信息查询本周开户的卡号，显示该卡的相关信息。-(4)查询本周开户信息-默认星期日作为一周的第一天.修改星期一为第一天SET DATEFIRST 1SELECT BCNo 银行卡卡号,BCName 姓名, BCCurrency 货币类型,BCOpenDate 开户时间,BBTName 储蓄类型,BCOpenAmount 开户金额,BCExistBalance 存款金额, CASE BCRegLossWHEN 是 THEN 挂失账户WHEN 否 THEN 正常账户end 是否挂失 FROM BankCardINNER JOIN BankBusinessType ON BankBusinessType.BBTId = BankCard.BCBBTIdINNER JOIN BankCustomer ON BankCustomer.BCId = BankCard.BCBCIdWHERE DATEDIFF(DAY,BCOpenDate,GETDATE()DATEPART(WEEKDAY,GETDATE()图12 本周开户信息(5) 查询本月单次交易金额最高的卡号和总交易金额最高的卡号查询本月存、取款中单次交易金额最高的卡号信息。-(5)查询本月单次交