数据库技术与应用课程设计指导书

《数据库技术与开发》课程设计指导书内科大信息学院计算机系2014年2月15日实训工程：《银行ATM存取款机系统设计与实现》一、工程背景1、工程任务使用PowerDesigner完成数据库设计创立数据库、创立表、创立约束使用触发器和插入测试数据模拟常规业务、创立视图使用存储过程实现业务处理利用事务实现较复杂的数据更新2、工程技能目标使用PowerDesigner完成数据库概念模型和数据库物理模型设计。使用T-SQL语句创立数据库、表和各种约束。使用T-SQL语句编程实现常见业务。使用触发器实现多表之间的级联更新。使用事务和存储过程封装业务逻辑。使用视图简化复杂的数据查询。使用游标技术实现结果集的行集操作。3、需求概述某银行是一家民办的小型银行企业，现有十多万客户，公司将为该银行开发一套ATM存取款机系统，对银行日常的存取款业务进行计算机管理，以便保证数据的平安性，提高工作效率。要求根据银行存取款业务需求设计出符合第三范式的数据库结构，使用T-SQL语言创立数据库和表，并添加表约束，进行数据的增删改查，运用逻辑结构语句、事务、视图和存储过程，按照银行的业务需求，实现各项银行日常存款、取款和转账业务。4、开发环境数据库：SQLSERVER2008开发版数据库建模工具：PowerDesigner15问题分析该工程的ATM存取款机业务如下：(1)银行存取款业务介绍银行为客户提供了各种银行存取款业务。详见表1银行存取款业务业务描述活期无固定存期，可随时存取，存取金额不限的一种比拟灵活的存款定活两便事先不约定存期，一次性存入，一次性支取的存款通知不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款整存整取选择存款期限，整笔存入，到期提取本息的一种定期储蓄。银行提供的存款期限有1年、2年和3年零存整取一种事先原定金额，逐月按约定金额存入，到期支取本息的定期储蓄。银行提供的存款期限由1年、2年和3年自助转账在ATM存取款机上办理同一币种账户的银行卡之间互相划转(2)客户信息每个客户凭个人身份证在银行可以开设多个银行卡账户，开设账户时，客户需要提供的开户数据如表2所示：开设银行卡账户的客户信息数据描述姓名必须提供身份证号唯一确定客户，如果是二代身份证，那么是由17位数字和1位数字或者字符X构成。如果是一代身份证，那么身份证号全部是15位数字。联系分为座机号码和号码：座机号码由数字和“-”构成，由以下两种格式：XXX-XXXXXXXX或者XXXX-XXXXXXX。号码由11位数字构成，且前2位必须是13或者15开头。居住地址可以选择(3)银行卡账户信息银行为每个账户提供一个银行卡，每个银行卡可以存入一种币种的存款，银行保存账户如表3所示：银行卡账户信息数据描述卡号银行的卡号由16位数字组成，其中：一般前8位代表特俗含义，如代表某总行某支行等，假定该行要求其营业厅的卡号格式为10103576XXXXXXXX，后8位必须是随机产生且唯一，每4位号码后有空格。密码由6位数字构成，开户时默认为“888888”币种默认为RMB，目前该银行尚未开设其他币种存款业务。存款类型必须选择开户日期客户开设银行卡账户的日期，默认为当日开户金额客户开设银行卡账户时存入的金额，规定不得小于1元。是否挂失默认为“否”客户持银行卡在ATM机上输入密码，经系统验证身份后办理存款、取款和转账等银行业务。银行规定，每个账户当前的存款金额不得小于1元。(4)银行卡交易信息银行在为客户办理业务时，需要记录每一笔账目，账目交易信息如表4所示：银行卡交易信息数据描述卡号银行的卡号由16位数字组成交易日期默认为当日交易金额必须大于0元交易类型包括：存入和支取两种备注对每笔交易做必要的说明银行卡手工账户和存取款单据信息该银行要求这套软件实现银行客户的开户、存款、取款、转账和余额查询等业务，使得银行储蓄业务方便、快捷，同事保证银行业务数据的平安性。为使开发人员尽快了解银行业务，该银行提供了银行卡手工账户和存取款单据的样本数据，以供工程开发时参考，参加表5和表6。银行卡手工账户样本数据存取款单据样本数据实训进度安排实训进度安排如下表所示：实训进度安排实训内容所需学时提交文档实训一：制定数据库设计与编程标准41份数据库设计与编程标准实训二：数据库分析设计与建模8数据库分析设计文档及数据库概念模型、物理模型。实训三：创立数据库4T-SQL源文件实训四：创立触发器和插入测试数据4T-SQL源文件实训五：模拟常规业务4T-SQL源文件实训六：创立和使用视图4T-SQL源文件实训七：存储过程实现业务处理6T-SQL源文件实训八：利用事务实现转账业务6T-SQL源文件7、成绩考核实训工程最终成绩由3局部构成：平日考勤20%+实训文档30%+实训工程完成质量50%，但凡报优的同学需参加公开辩论，讲解辩论10-15分钟。凡实训工程雷同，或者考勤不合格直接视同不及格。二、工程实训内容实训内容由8个实训工程构成，建议在参考代码和实现步骤根底上进行改良，每个实训子工程的T-SQL语句写成1个T-SQL源文件，如item1.sql。实训一：制定《数据库设计与编程标准》参考技术文档：《数据库设计标准(1)》、《数据库设计标准(修订)》、《数据库设计及编写标准》、《编程标准(T-SQL)》、《Transact-SQL_数据库编程命名标准》、《SQL_Server数据库编程标准》等技术文档，制定一份10-15页，不少于3000字的《数据库设计与SQL编程标准》，要求至少包含各个数据库对象的命名标准、编程标准及注释标准。该实训工程的设计与编程要求遵循该实训制定的《数据库设计与编程标准》。2、实训二：数据库分析设计与建模阅读工程背景相关内容，在8学时内按下述推荐步骤完成下述实训内容：(1)分析银行ATM存取款系统实体(2学时)根据问题分析，明确银行ATM存取款系统的实体、实体属性及实体之间的关系：在充分理解银行业务需求后，围绕银行的需求进行分析，确认与银行ATM存取款有紧密关系的实体，并得到每个实体的必要属性。根据业务，分析多个实体之间的关系。实体之间的关系可以是一对一、一对多和多对多。要求使用Visio绘制出ATM存取款系统ER图。参考资料：《ER设计》、《根本ER图要点》(2)标准数据库结构设计(2学时)使用数据库设计范式对数据库表结构进行标准优化，标准数据库的表结构同时，要考虑软件运行性能。必要时，可以有悖于第三范式的要求，适当增加冗余数据、减少表间链接，以空间换取时间。要求以上述数据库设计举例说明3种数据库设计范式的具体应用实例，每个设计范式至少举例说明其中1个实例。如所谓第一范式〔1NF〕是指数据库表的每一列都是不可分割的根本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如在银行客户表BankCustomer中，不能将客户信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；客户信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。(3)安装并演示PowerDesigner建模例如〔2学时〕参考《PowerDesigner系统分析与建模》，安装PowerDesigner及破解步骤、演示创立数据库概念模型CDM、数据库物理模型PDM和自动生成T-SQL语句等操作。(4)绘制CDM模型、生成PDM模型和生成T-SQL语句〔2学时〕使用PowerDesigner，把设计数据库第一步结果〔即分析得到的银行ATM存取款系统的实体、实体属性、以及实体之间的关系〕用CDM表示，要求CDM要表达出各实体之间的关系以及各实体的实体属性。创立如下列图1所示的数据库概念模型：银行存取款系统数据概念模型CDM检查所创立CDM模型无误后，自动生成相应的CDM设计文档和银行存取款系统数据物理模型PDM。参考PDM模型如图2所示：银行存取款系统数据物理模型PDM3、实训三：创立数据库使用T-SQL语句完成数据库、数据表和各种约束及触发器的创立，并保存为item2.sql文件。按下述推荐步骤，在4学时内完成下述实训内容：(1)创立数据库〔1学时〕使用CreateDataBase语句创立“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下〔建议建立一个文件夹，用于存放该实训工程的所有相关T-SQL源文件〕，文件增长率为15%。参考代码如下所示：--创立BankDB数据库，数据库文件和日志文件均保存在--文件夹G:\2014年工程案例工程\银行ATM存取款机系统下--文件增长率均为%，数据文件起始大小为MB，日志文件起始大小为MBcreatedatabaseBankDBonprimary( name=N'BankDB', filename=N'G:\2014年工程案例工程\银行ATM存取款机系统\BankDB.mdf',size=5MB,filegrowth=15%)logon( name=N'BankDB_log', filename=N'G:\2014年工程案例工程\银行ATM存取款机系统\BankDB_log.ldf',size=2MB,filegrowth=15%)(2)创立各个数据表及相关的约束〔2学时〕根据实训1设计出的“银行ATM存取款机系统”PDM模型的数据表结构，使用CreateTable语句创立表结构。根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创立表时要求检测是否存在表结构，如果存在，那么先删除再创立。建议选择2张表的T-SQL语句进行修改，先创立表结构里的各个字段，再用AlterTable语句为每个表添加各种约束。参考代码如下所示：useBankDB;go--判断银行业务类型表是否存在,假设存在那么删除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankBusinessType'))droptableBankBusinessType--创立银行业务类型表，包含银行业务类型编号BBTId，银行业务类型名称BBTName，银行业务描述BBTCommentcreatetableBankBusinessType( BBTIdintidentity(1,1)primarykey, BBTNamechar(20)notnull, BBTCommentvarchar(100));go--判断银行卡客户是否存在,假设存在那么删除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankCustomer'))droptableBankCustomer--创立银行客户信息表，包含客户编号BCID，客户姓名BCName，客户身份证BCICNo，客户联系BCTel、客户居住地址BCAddrcreatetableBankCustomer( BCIdintidentity(1,1)primarykey, BCNamechar(20)notnull, --定义身份证号前位必须是数字，后位可以是数字或者X。 BCICNochar(18)notnull check(left(BCICNo,17)like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and(right(BCICNo,1)like'[0-9]'orright(BCICNo,1)like'X')), --定义联系方式，必须是固定号码或者号，且前位必须是，第位必须是或或 BCTelvarchar(20)notnull check(BCTellike'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or BCTellike'[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' orBCTellike'1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --定义客户地址 BCAddrvarchar(100));go--判断银行卡是否存在，假设存在，那么删除银行卡BankCardifexists(select*fromsysobjectswhereid=object_id(N'BankCard'))droptableBankCard--建立银行卡信息createtableBankCard( --卡号必须符合位数字构成,前位为3576,后位是随机产生且唯一,每位必须有一个空格 BCNochar(19)primarykeycheck(BCNolike'10103576[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --密码,开户默认为 BCPwdchar(6)notnulldefault('888888'), --币种,为RMB类型 BCCurrencychar(5)notnulldefault('RMB'), --存款类型 BCBBTIdintnotnull, --开户日期,默认当日 BCOpenDatedatenotnulldefault(getdate()), --开户金额,不得小于元 BCOpenAmountmoneynotnullcheck(BCOpenAmount>=1), --是否挂失,默认为否 BCRegLosschar(2)default('否'), --客户编号 BCBCIdintnotnull, BCExistBalancemoneynotnull);--判断交易信息BankDealInfo是否存在,假设存在那么删除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankDealInfo'))droptableBankDealInfo--创立交易信息表createtableBankDealInfo( --交易编号为自动增长列 BDNointidentity(1,1)primarykey, --卡号 BDBCNochar(19)notnull, --交易日期 BDDealDateDatenotnulldefault(getdate()), --交易金额 BDDealAcountmoneynotnull, --交易类型，有种存入和支取 BDDealTypeChar(10)notnullcheck(BDDealType='存入'orBDDealType='支取'), --交易备注 BDDealCommentvarchar(100));(3)添加外键约束和生成数据库关系图〔1学时〕添加子表外键约束及生成数据库关系图添加外键约束参考代码如下所示：--建立表之间的外键约束关系altertablebankcardaddconstraintfk_BC_BBTforeignkey(BCBBTId)referencesbankbusinesstype(bbtid);altertablebankcardaddconstraintfk_BC_BCforeignkey(BCBCId)referencesBankCustomer(BCId);altertablebankDealInfoaddconstraintfk_BDI_BCforeignkey(BDBCNo)referencesBankCard(BCNo);在SQLSERVER里自动生成数据库关系图，如下列图所示：BankDb数据库关系图4、实训四：创立触发器和插入测试数据(1)创立级联触发器〔2学时〕创立Insert触发器在交易信息表BankDealInfo中创立一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。建议使用游标，实现批量增加的级联更新。--在交易信息表中插入一个触发器，使用游标当新增一个交易信息，修改银行卡的存款余额if(object_id('tr_InsertdealInfo','tr')isnotnull)droptriggertr_InsertdealInfogocreatetriggertr_InsertdealInfoonbankdealinfoforinsertasdeclare@typechar(10),@summoney,@BDBCNochar(19);--创立一个游标，指向inserted表declarecursor_BankDealinfocursorforselectBDDealType,BDDealAcount,BDBCNofrominserted--翻开游标opencursor_BankDealinfo--取游标中各个字段的值复制给各个变量fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNowhile@@fetch_status=0begin --判断交易记录里是存入还是支取，及时更新银行卡表的存款余额 if(rtrim(ltrim(@type))='存入') updatebankcardsetBCExistBalance=BCExistBalance+@sumwhereBCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') updatebankcardsetBCExistBalance=BCExistBalance-@sumwhereBCNo=@BDBCNo; fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNoendclosecursor_BankDealinfodeallocatecursor_BankDealinfogo创立Delete触发器在交易信息表创立一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。建议使用游标，实现批量删除的级联更新。--在交易信息表中插入一个触发器，使用游标,当删除一个交易信息，修改银行卡的存款余额if(object_id('tr_DeldealInfo','tr')isnotnull)droptriggertr_DeldealInfogocreatetriggertr_DeldealInfoonbankdealinfofordeleteasdeclare@typechar(10),@summoney,@BDBCNochar(19);--创立一个游标，指向deleted表declarecursor_BankDealinfocursorforselectBDDealType,BDDealAcount,BDBCNofromdeleted--翻开游标opencursor_BankDealinfo--取游标中各个字段的值复制给各个变量fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNowhile@@fetch_status=0begin if(rtrim(ltrim(@type))='存入') updatebankcardsetBCExistBalance=BCExistBalance-@sumwhereBCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') updatebankcardsetBCExistBalance=BCExistBalance+@sumwhereBCNo=@BDBCNo; fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNoendclosecursor_BankDealinfodeallocatecursor_BankDealinfogo创立Update触发器参照上述代码，创立一个Update触发器，当更新交易信息表的记录时，更新银行卡表的相应卡号的余额。(2)插入数据表的测试数据〔2学时〕使用T-SQL语句向每个表插入如下所示测试数据，要保证业务数据的一致性和完整性。BankBusinessType表的测试数据使用T-SQL向已经创立的BankBusinessType表插入表7数据：BankBusinessType表测试数据BankCustomer表的测试数据使用T-SQL语句向BankCustomer表中插入表8的数据：BankCustomer表测试数据BankCard表的测试数据使用T-SQL语句向BankCard表中插入表9的数据，要求开户日期设置为当前日期近一个月的随机某一天〔使用相应的日期函数和随机函数完成〕。扩展题：如何保证某卡号对应的交易日期必须晚于该卡的开户日期，建议增加相应的级联触发器。BankCard表测试数据BankDealInfo表的测试数据使用T-SQL语句向BankDealInfo表中插入表10的数据，要求交易日期设置为当前日期近半个月的随机某一天〔使用相应的日期函数和随机函数完成〕。通过检查相应账户余额，仔细检查上述建立的2个级联触发器是否发挥作用？BankDealInfo表测试数据5、实训五：模拟常规业务使用T-SQL语句实现银行的日常业务，并保存为item4.sql文件。按下述推荐步骤，在4学时内完成下述实训内容：(1)修改客户密码根据卡号修改指定2个客户的银行密码，其中第一个客户1010357612345678密码修改为123456，第二个客户1010357612345688修改为123123，并使用Select查询语句显示如下效果图：修改客户密码效果图办理银行卡挂失卡号为1010357612345678的银行卡丧失，申请挂失，要求使用innerjoin语句显示如下列图运行结果：办理银行卡挂失效果图统计银行资金流通余额和盈利结算存入代表资金流入，支取代表资金流出。计算公式：资金流通余额=总存入金额-总支取金额假定存款利率为千分之三，贷款利率为千分之八。计算公式：盈利结算=总支取金额*0.008-总存入金额*0.003。要求创立一个存储过程proc_staticsBanlanceAndProfit，执行该存储过程运行结果如下列图所示：统计银行资金流通余额和盈利结算参考代码如下所示：ifexists(select*fromsysobjectswherename='proc_staticsBanlanceAndProfit')......;gocreateprocedureproc_staticsBanlanceAndProfitasdeclare@InMoneymoney......--获取总存入金额和总支取金额select@InMoney=SUM(BDDealAcount)fromBankDealInfowherertrim(ltrim(BDDealType))='存入'......print'存入总金额:'+rtrim(ltrim(str(@InMoney)))+'RMB,支取总金额:'......go查询本周开户信息查询本周开户的卡号，显示该卡的相关信息。结果如下列图所示：本周开户信息提示：求时间差使用日期函数DateDiff()，求星期几使用日期函数DatePart()，账户状态列使用Case语句转换。查询本月单次交易金额最高的卡号和总交易金额最高的卡号查询本月存、取款中单次交易金额最高的卡号信息。结果如下列图所示：本月交易金额最高的卡号提示：要求使用子查询和distinct去掉重复的卡号,使用DateDiff()和DataPart()函数得到本月内的交易信息。查询挂失客户查询挂失账号的客户信息，分别利用子查询in的方式或者内部连接innerjoin，查询结果如下列图所示：查询挂失客户催款提醒业务根据某种业务〔如代缴费、代缴费或房贷等〕的需要，每个月末，查询出客户账户上余额少于2000元，由银行统一致电催款。查询结果如下列图所示：催款提醒业务利用子查询或者内部连接innerjoin均可，参考代码如下所示：select......fromBankCardAinnerjoinBankCustomerBWhere......6、实训六：创立、使用视图使用T-SQL语句创立如下视图，并保存为item5.sql文件。按下述推荐步骤，在4学时内完成下述实训内容：为向客户提供友好的用户界面，使用T-SQL语句创立下面几个视图，并使用这些视图输出各表信息。输出银行客户记录视图VW_userInfo显示的列名全为中文，要求先判断该视图是否存在，假设存在，那么先删除。结果如下列图所示：输出银行客户记录输出银行卡记录视图VW_CardInfo建议使用内部连接InnerJoin语句，结果如下列图所示：输出银行卡记录输出银行卡交易记录视图VW_TransInfo查询该视图，结果如下列图所示：输出银行卡的交易记录根据客户登录名查询该客户账户信息VW_OneUserInfo根据客户登录名〔采用实名制访问银行系统〕查询该客户账户信息的视图，利用SQLSERVER系统函数system_user获得数据库用户名。根据登录名查询账户信息7、实训七：存储过程实现业务处理(1)完成存款或取款业务描述：根据银行卡号和交易金额实现银行卡的存款和取款业务。每一笔存款，取款业务都要计入银行交易账，并同时更新客户的存款余额。如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。检查客户输入的密码是否正确。账户取款金额是否大于当前存款额加1。要求：取款或存款存储过程名为usp_takeMoney。编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试，测试数据是张飞的卡号支取100元〔密码123456〕，关羽的卡号存入2100元。结果如下列图所示下述两图为张飞卡号存入2100元的运行结果：执行存储过程的结果〔存款〕执行存储过程的输出消息〔存款〕下述两图为关羽卡号取款100元的执行结果：执行存储过程结果〔取款〕执行存储过程输出消息〔取款〕提示：鉴于存款时客户不需要提供密码，在编写存储过程中，为输入参数“密码”列设置默认值为Null。在存储过程中使用事务，以保证数据操作的一致性。测试时，可以根据客户姓名查出张三和李四的卡号。存储过程参考代码如下所示：--判断该存储过程是否存在，假设存在，那么删除ifexists(select*fromsysobjectswherename='proc_TakeMoney') ......;go--创立存取款业务的存储过程createprocedureproc_TakeMoney @bcnochar(19),@moneymoney,@pwdchar(6)=nullas --不返回受影响的行数 setnocounton --声明一变量存放指定卡号的存款余额 declare@existBanlancemoney --启动事务机制 begintran select@existBanlance=BCExistBalancefromBankCardwhereBCNo=@bcno print'交易前，卡号'+@bcno+'，余额为：'+ltrim(str(@existBanlance)) print'交易正进行，请稍后...' --如果输入参数@pwd为空，那么为取款业务，否那么为存款业务 if(@pwdisnotnull) --办理取款业务 begin --判断指定卡号和密码是否存在，假设存在，那么可以取款，否那么不能办理取款业务 ifexists(......) begin --判断取款金额是否小于等于存款余额-1，假设条件成立，那么可以取款，否那么不能取款 if(@money<=@ExistBanlance-1) insertintoBankDealInfo...... else print'取款交易失败，余额缺乏，请减少取款' end else print'取款交易失败，密码有错误' end else --办理存款业务 insertintoBankDealInfo...... --判断事务处理里是否有异常，假设没有异常，那么提交，假设有异常，那么回滚 if(@@ERROR<>0) begin print'交易失败' rollbacktran end else begin committran print'交易成功，交易金额为：'+ltrim(str(@money)) --判断该交易为何种类型业务，假设是存款，那么现有余额等于原有余额加上存款金额 ...... end --显示银行卡用户详情和交易详情 ......go测试代码如下所示：--执行存款存储过程execproc_TakeMoney'1010357612345678',2100--执行取款存储过程execproc_TakeMoney'1010357612345678',100,'123456'产生随机卡号创立存储过程产生8位随机数字，与前8位固定数字“10103576”连接，生成一个由16位数字组成的银行卡号，并输出。要求：产生随机卡号的存储过程名为Proc_randCardID。利用下面的代码调用存储过程进行测试declare@myCardId1char(19)execproc_randCardId@myCardId1outputprint'产生随机卡号为'+@myCardId1结果如下图：测试产生随机卡号提示：使用随机函数生成银行卡后8位的数字，随机函数的用法：rand(随机种子)将产生0~1的随机数，要求每次的随机种子不一样。为了保证随机种子每次都不相同，一般采用的算法是：随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数产生了0~1的随机数后，取小数点后8位，即：0.XXXXXXXX。关键代码如下所示：declare@rnumeric(15,8),@randCardIdchar(19),@tmpstrchar(10)--产生随机种子=当前的月份数*100000+当前的秒数*1000+当前的毫秒数select@r=RAND(DATEPART(mm,getdate())*100000+DATEPART(SS,getdate())*1000+DATEPART(ms,getdate()))set@tmpstr=convert(char(10),@r)set@randCardId=......+SUBSTRING(@tmpstr,3,4)+......完成开户业务描述：利用存储过程为客户开设2个银行卡账户，开户时需要提供客户的信息有：开户名、身份证号、号码、开户金额、存款类型和地址。客户的信息见表所示：为成功开户的客户提供银行卡，且银行卡号唯一。要求：开户的存储过程名为usp_openAccount。使用下面的数据执行该存储过程，进行测试：调用此存储过程开户。两位客户的开户信息姓名身份证联系开户金额存款类型地址周公旦0472_24578901200定活两便内蒙古包头姬昌0472_24578901100活期内蒙古包头结果如下列图所示：执行开户存储过程的结果测试开户存储过程的输出信息提示：调用上述产生随机卡号的存储过程获得生成的随机卡号，检查改随机卡号在现有的银行卡中是否存在。如果不存在，那么往相关表中插入开户信息；否那么将调用上述随机卡号的存储过程，重新生成随机卡号，直至产生一个不存在的银行卡号为止。参考代码如下useBankDBgoifexists(select*fromsysobjectswherename='usp_openAccount') dropprocusp_openAccountgo--创立开户存储过程usp_openAccount，输入参数分别是开户名、身份证号、号码、开户金额、存款类型和地址createprocusp_openAccount......asdeclare@BCBBTIdint,@BCNochar(19),@BCIdint --先判断存款类型是否正确 ifexists(......) begin begintran select@BCBBTId=BBTIdfromBankBusinessTypewhereBBTName=@BBTName execproc_randCardId@BCNooutput --判断是否卡号重复，假设重复，那么继续循环，否那么退出循环 while(exists(......)) execproc_randCardId@BCNooutput --插入一条客户信息记录 insert...... --得到刚插入的客户信息的编号 ...... --插入一条新开银行卡记录 insertintoBankCard...... --判断上述事务操作是否有异常 if(@@ERROR<>0) Begin print'尊敬的客户，开户不成功，所有操作均撤销' rollbacktran end else begin committran print'尊敬的客户，开户成功，系统为你产生的随机卡号是'+...... end --显示开户的客户信息和银行卡信息 ...... end else print'尊敬的客户，未能成功开户，存款类型不正确，请重新输入'go分页显示查询交易数据根据指定的页数和每页的记录数分页显示交易数据。要求：存储过程名称是usp_PagingDisplay.测试数据是输出第2页，每页5行交易数据，结果如下列图所示：每页5行方式输出第2页交易数据扩展要求创立一个usp_PagingDisplay1，调用系统存储过程SP_EXECUTESQL，执行动态生成的T-SQL语句。代码提示：createprocedureusp_PagingDisplay ......as select...... from (selectROW_NUMBER()over(orderbyBDNo)asRowNumber,......)C where(C.RowNumberbetween.......)打印客户对账单为某个特定的银行卡号打印指定时间内发生交易的对账单。要求如下：存储过程名称是usp_CheckSheet。分别采用以下两种方式执行存储过程，结果如下列图所示。如果不指定交易时间段，那么打印指定卡号的所有交易记录，如测试命令：execusp_CheckSheet'1010357612345688'如果指定交易时间段，那么打印指定卡号在指定时间内发生的所有交易记录，如测试命令：execusp_CheckSheet'1010357612345678','2014-1-1','2014-1-15'按卡号打印对账单代码提示：useBankDBgo--判断客户对账单存储过程是否存在，假设是存在，那么删除ifexists(select*fromsysobjectswherename='usp_CheckSheet') dropprocedureusp_CheckSheetgo--创立客户对账单的存储过程createprocedureusp_CheckSheet@bcnochar(19),@startDatedatetime=null,@endDatedatetime=nullas--声明各个变量分别存放姓名、货币类型、存款类型、开户日期declare@bcNamechar(20),@bcCurrencychar(5),@bbtNamechar(20),@bcOpenDatedate--声明各个变量分别存放交易日期、交易类型、交易金额、备注declare@bddealdatedatetime,@bddealtypechar(10),@bddealacountmoney, @bddealcommentvarchar(100)select@bcName=B.BCName,@bcCurrency=A.BCCurrency, @bbtName=C.BBTName,@bcOpenDate=A.BCOpenDate fromBankCardAinnerjoinBankCustomerB onA.BCBCId=B.BCId innerjoinBankBusinessTypeC onA.BCBBTId=C.BBTId whereA.BCNo=@bcnoprint'卡号:'+@bcnoprint'姓名:'+@bcNameprint'货币:'+@bcCurrencyprint'存款类型:'+@bbtNameprint'开户日期:'+convert(char(12),@bcOpenDate,111)--创立一个游标，指向指定的交易记录if(@startDateisnull) declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoelse begin if(@endDateisnull) declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoandbddealdate>@startdate else declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoandbddealdate>@startdateandbddealdate<@enddate end--翻开游标opencur_BankDealinfoprintconvert(char(12),'交易日')+convert(char(10),'类型')+ convert(char(10),'交易金额')+convert(char(100),'备注')--从游标里取出相应字段的值到各个变量里fetchnextfromcur_BankDealinfointo@bddealdate,@bddealtype,@bddealacount,@bddealcommentwhile(@@FETCH_STATUS=0) begin printconvert(char(12),@bddealdate,111)+convert(char(10),@bddealtype)+ convert(char(10),@bddealacount)+convert(char(100),@bddealcomment) fetchnextfromcur_BankDealinfointo@bddealdate,@bddealtype,@bddealacount,@bddealcomment endclosecur_BankDealinfodeallocatecur_BankDealinfogo统计未发生交易的账户查询统计指定时间段内没有发生交易的账户信息。要求：存储过程名称是usp_getWithoutTrade。指定时间段如果没有指定起始日期，那么自本月1日开始进行统计，如果没有指定终止日期，那么截止到当日为止。要求采用游标技术打印未发生交易的客户信息，参考客户对账单的代码。该存储过程的执行结果如下列图所示统计未发生交易的账户统计银行卡交易量和交易额统计指定时间段内某地区客户在银行卡交易量和交易额，如果不指定地区，那么查询所有客户的交易量和交易额。要求：存储过程名称是usp_getTradeInfo。指定时间段和客户所在区域如果没有指定起始日期，那么自当年1月1日开始统计，如果没有指定终止日期，那么以当日作为截止日。如果没有指定地点〔根据客户所在地址查询〕，如北京，那么统计全部客户的交易量和交易额。执行下述存储过程语句：execusp_getTradeInfoexecusp_getTradeInfo'2014-1-18','2014-2-20'execusp_getTradeInfo'2014-1-18','2014-1-20','包钢'结果如下列图所示：统计银行卡交易量和交易额8、实训八：利用事务实现转账使用存储过程和事务实现转账业务，操作步骤如下所示：从某一个账户支取一定金额的存款。将支取金额存入到另一个指定的账户中。分别打印此笔业务的转出账单和转入账单。要求：存储过程名称是usp_transfer。要求使用事务机制实现转账业务。结果如下图：实现转账业务关键代码如下所示：useBankDBgo--判断该存储过程是否存在，假设存在，那么删除ifexists(select*fromsysobjectswherename='usp_transfer') dropprocedureusp_transfergo--创立转账存储过程，需要传递两个账户号码及转账金额createprocedureusp_transfer@outbcnochar(19),@inbcnochar(19),@dealAcountmoneyas--不返回受影响的行数setnocounton--声明个变量分别存放转出账户和转入账户的转账之后的余额declare@outexistbalancemoney,@inexistbalancemoney--声明变量存放转出账户的姓名、货币类型、存款类型和开户日期declare@outbcnamechar(20),@outBcCurrencychar(5),@outBBTNamechar(20),@outBcOpenDatedate--声明变量存放转入账户的姓名、货币类型、存款类型和开户日期declare@inbcnamechar(20),@inBcCurrencychar(5),@inBBTNamechar(20),@inBcOpenDatedate print'开始转账，请稍后...' --判断转出账户及余额是否大于转出金额+1 ifexists(select*fromBankCardwhereBCNo=@outbcnoan