SQL模板.doc

数据库技术与应用课程设计报告数据库技术与应用课程设计报告项目名称：银行ATM存取款机系统设计与实现姓 名：侯龙超学 号：1267159229专 业：软件12-2班指导教师： 刘立新完成日期：2014-11-20目录：一、创建数据库1(1) 创建数据库1(2)创建各个数据表及相关的约束1(3) 添加外键约束和生成数据库关系图3二、创建触发器和插入数据4(1) 创建级联触发器4(2) 插入数据表的测试数据6三、 模拟常规业务9(1) 修改客户密码9(2) 办理银行卡挂失10(3) 统计银行资金流通余额和盈利结算10(4) 查询本周开户信息11(5) 查询本月单次交易金额最高的卡号和总交易金额最高的卡号12(6) 查询挂失客户13(7) 催款提醒业务13四、 创建、使用视图14(1) 输出银行客户记录视图VW_userInfo14(2) 输出银行卡记录视图VW_CardInfo14(3) 输出银行卡交易记录视图VW_TransInfo15(4) 根据客户登录名查询该客户账户信息16五、 存储过程实现业务处理16(1) 完成存款或取款业务16(2) 产生随机卡号19(3) 完成开户业务20(4) 分页显示查询交易数据21(5) 打印客户对账单22(6) 统计未发生交易的账户24(7) 统计银行卡交易量和交易额26六、 利用事务实现转账29七、 总结33一、创建数据库(1) 创建数据库使用Create DataBase语句创建“ATM存取款机系统”数据库BankDB，数据文件和日志文件保存在指定目录下，文件增长率为15%。代码如下所示：-（1）创建数据库create database BankDBon Primary(name = NBankDB_data,fileName = ND:SQLBankDBBankDB_data.mdf,size = 5mb,filegrowth = 15%)log on(name = NBankDB_log,filename = ND:SQLBankDBBankDB_log.ldf,size = 2mb,filegrowth = 15%)(2)创建各个数据表及相关的约束根据银行业务，分析表中每个列相应的约束要求，为每个表添加各种约束。要求创建表时要求检测是否存在表结构，如果存在，则先删除再创建。代码如下所示：-（2）创建各个数据表及相关的约束use BankDBgo-判断银行业务类型表是否存在，若存在则删除if 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-创建银行卡客户表BankCustomercreate table BankCustomer(-客户编号BCId int identity(1,1) primary key,-客户姓名BCName char(10) not null,-客户身份证BCICNo char(18) not nullcheck(left(BCICNo,17) like 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9and (right(BCICNo,1) like 0-9 or right(BCICNo,1) like X) ),-客户电话号码BCTel char(20) not nullcheck (BCTel like 0-90-90-90-9-0-90-90-90-90-90-90-9 or BCTel like 0-90-90-9-0-90-90-90-90-90-90-90-9 orBCTel like 13580-90-90-90-90-90-90-90-90-9),-客户地址BCAddr varchar(100) );go-判断银行卡是否存在，若存在则删除if 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(),-开户金额，不小于1元BCOpenAmount money not null check(BCOpenAmount = 1),-是否挂失,默认为否BCRegLoss char(2) default(否),-客户编号BCBCId int not null,-卡内余额BCExistBalance money not null)go-判断交易信息表是否存在，若存在则删除if exists(select * from sysobjects where id=OBJECT_ID(NBankDealInfo)drop table BankDealInfo-创建交易信息表BankDealInfocreate table BankDealInfo(-交易编号BDNo int identity(1,1) primary key,-卡号BDBCNo char(19) not null,-交易日期BDDealDate date not null default(getdate(),-交易金额BDDealAcount money not null,-交易类型，有存入和支取BDDealType char(10) not null check(BDDealType=存入 or BDDealType=支取),-交易备注BDDealComment varchar(100)go(3) 添加外键约束和生成数据库关系图添加子表外键约束及生成数据库关系图代码如下所示：-（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里自动生成数据库关系图，如下图(图1.1)所示：图 1.1二、创建触发器和插入数据(1) 创建级联触发器 创建Insert触发器 在交易信息表BankDealInfo中创建一个Insert触发器，当增加一条交易信息时，修改相应银行卡的存款余额。代码如下所示：-创建Insert触发器-在交易信息表中插入一个触发器，当有数据插入时更新银行卡余额if(OBJECT_ID(tr_InsertBankDealInfo,tr) is not null)drop trigger tr_InsertbankDealInfogocreate trigger tr_InsertBankDealInfoon BankDealInfofor insertasdeclare type char(10),sum money,BDBCNo char(19)declare cursor_BankDealInfo cursor forselect BDDealType,BDDealAcount,BDBCNo from insertedopen cursor_BankDealInfofetch 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 创建Delete触发器在交易信息表创建一个Delete触发器，当删除一条交易信息时，修改相应银行卡的存款余额。代码如下所示：-创建Delete触发器-当删除一条交易信息时，修改响应银行卡的存款金额if(OBJECT_ID(tr_DelBankDealInfo,tr) is not null)drop trigger tr_DelBankDealInfogocreate trigger tr_DelBankDealInfoon BankDealInfofor deleteasdeclare type char(10),sum money,BDBCNo char(19)declare cursor_BankDealInfo cursor forselect BDDealType,BDDealAcount,BDBCNo from deletedopen cursor_BankDealInfofetch 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_UpdateBankDealInfo,tr) is not null)drop trigger tr_UpdateBankDealInfogocreate trigger tr_UpdateBankDealInfoon BankDealInfofor updateasdeclare newType char(10),oldType char(10),newSum money,oldSum money,BDBCNo char(19)declare cursor_newBankDealInfo cursor forselect BDDealType,BDDealAcount,BDBCNo from inserteddeclare cursor_oldBankDealInfo cursor forselect BDDealType,BDDealAcount,BDBCNo from deletedopen cursor_newBankDealInfoopen cursor_oldBankDealInfofetch next from cursor_newBankDealInfo into newType,newSum,BDBCNofetch next from cursor_oldBankDealInfo into oldType,oldSum,BDBCNowhile FETCH_STATUS=0beginif(RTRIM(LTRIM(newType)=RTRIM(LTRIM(oldType)beginif(RTRIM(LTRIM(newType)=存入)update BankCard set BCExistBalance=BCExistBalance-oldSum+newSumif(RTRIM(LTRIM(newType)=支取)update BankCard set BCExistBalance=BCExistBalance+oldSum-newSumendelsebeginif(RTRIM(LTRIM(newType)=存入)update BankCard set BCExistBalance=BCExistBalance+oldSum+newSumif(RTRIM(LTRIM(newType)=支取)update BankCard set BCExistBalance=BCExistBalance-oldSum-newSumendfetch next from cursor_newBankDealInfo into newType,newSum,BDBCNofetch next from cursor_oldBankDealInfo into oldType,oldSum,BDBCNoendclose cursor_newBankDealInfoclose cursor_oldBankDealInfodeallocate cursor_newBankDealInfodeallocate cursor_oldBankDealInfogo(2) 插入数据表的测试数据使用T-SQL语句向每个表插入如下所示测试数据，要保证业务数据的一致性和完整性。 使用T-SQL向已经创建的BankBusinessType表插入数据代码如下所示：-向BankBusinessType表插入数据insert into BankBusinessType(BBTName,BBTComment)values(活期,无固定存期，可随时存取，存取金额不限的一种比较灵活的存款),(定活两便,事先不约定存期，一次性存入，一次性支取的存款),(通知,不约定存期，支取时需提前通知银行，约定支取日期和金额方能支取的存款),(整存整取1年,整笔存入，到期提取本息),(整存整取2年,整笔存入，到期提取本息),(整存整取3年,整笔存入，到期提取本息),(零存整取1年,实现原定金额，逐月按约定金额存入，到期支付本息),(零存整取2年,实现原定金额，逐月按约定金额存入，到期支付本息),(零存整取3年,实现原定金额，逐月按约定金额存入，到期支付本息),(自助转账,银行ATM存取款机上办理银行卡之间互相划转)select *from BankBusinessTypego执行结果如下(图 2.1)所示：图 2.1 使用T-SQL语句向BankCustomer表中插入数据代码如下所示：-向BankCustomer表中插入数据insert into BankCustomer(BCName,BCICNo,BCTel,BCAddr)values(张包头市昆区包钢五中),(关包头市昆区阿尔丁大街)select * from BankCustomergo执行结果如下(图 2.2)所示：图 2.2 使用T-SQL语句向BankCard表中插入数据，要求开户日期设置为当前日期近一个月的随机某一天（使用相应的日期函数和随机函数完成）。代码如下所示：-向BankCard表中插入数据insert into BankCard(BCNo,BCPwd,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCId,BCExistBalance)values(1010 3576 1234 5678,197611,RMB,1,CAST(DATEADD(day,-cast(rand()*30 as int),GETDATE() as date),1000,否,1,1000),(1010 3576 1234 5688,197711,RMB,2,CAST(DATEADD(day,-cast(rand()*30 as int),GETDATE() as date),1500,否,2,1500)select * from BankCardgo-为表BankDealInfo创建触发器tr_InsertBankDealInfoDate是插入的交易日期必须晚于开户日期if(OBJECT_ID(tr_InsertBankDealInfoDate,tr) is not null)drop trigger tr_InsertBankDealInfoDategocreate trigger tr_InsertBankDealInfoDate on BankDealInfofor insertasdeclare date date,BCNo char(19),BDNo intdeclare cursor_BankDealInfo cursor for select BDNo,BDBCNo,BDDealDate from insertedopen cursor_BankDealInfofetch next from cursor_BankDealInfo into BDNo,BCNo,datewhile FETCH_STATUS=0begin while date开户日期 between DATEAdd(DAY,-DatePart(DD,GETDATE()+1,GETDATE() and GETDATE() -DW(WeekDay)一周中第几天,Dd(Day)一月中第几天,Dy(dayofyear)一年中第几天go查询结果如下(图 3.4)所示：图 3.4(5) 查询本月单次交易金额最高的卡号和总交易金额最高的卡号查询本月存、取款中单次交易金额最高的卡号信息。代码如下所示：-(5)查询本月单词交易金额最高的卡号和总交易金额最高的卡号-要求使用子查询，查询单次交易金额最高select BC.BCNo as 卡号,BC.BCOpenDate as 开户日期,BC.BCOpenAmount as 开户金额from BankCard BCwhere Bc.BCNo in (select distinct BDI.BDBCNofrom BankDealInfo BDIwhere DATEDIFF(DD,GETDATE(),BDI.BDDealDate)DATEPART(DD,getdate() and BDI.BDDealAcount=(select MAX(BDDealAcount) from BankDealInfo)-查询总交易金额最高select BC.BCNo as 卡号,BC.BCOpenDate as 开户日期,BC.BCOpenAmount as 开户金额from BankCard BCwhere Bc.BCNo in (select top 1 A.BDBCNofrom BankDealInfo BDIinner join (select BDBCNo,Sum(BDDealAcount) as sum from BankDealInfo group by BDBCNo)A on A.BDBCNo=BDI.BDBCNowhere DATEDIFF(DD,BDI.BDDealDate,GETDATE()DATEPART(DD,getdate()order by sum desc)执行结果如下(图 3.5)所示：图 3.5(6) 查询挂失客户查询挂失账号的客户信息。代码如下所示：-(6)查询挂失客户-利用子查询in查询select BCR.BCName as 客户姓名,BCR.BCTel as 联系电话from BankCustomer BCRwhere BCR.BCId in (select BC.BCBCIdfrom BankCard BCwhere rtrim(ltrim(BC.BCRegLoss)=是)-使用内连接inner join查询select BCR.BCName as 客户姓名,BCR.BCTel as 联系电话from BankCustomer BCRinner join BankCard BC on BC.BCBCId=BCR.BCIdwhere rtrim(ltrim(BC.BCRegLoss)=是执行结果如下(图 3.6)所示：图 3.6(7) 催款提醒业务根据某种业务（如代缴电话费、代缴手机费或房贷等）的需要，每个月末，查询出客户账户上余额少于2000元，由银行统一致电催款。代码如下所示：-(7)催款提醒业务select BCR.BCName as 客户姓名,BCR.BCTel as 联系电话,BC.BCExistBalance as 存款余额from BankCustomer BCRinner join BankCard BC on BC.BCBCId=BCR.BCIdwhere BC.BCExistBalance2000执行结果如下(图 3.7)所示：图 3.74、 创建、使用视图使用T-SQL语句创建视图。为向客户提供友好的用户界面，使用T-SQL语句创建下面几个视图，并使用这些视图输出各表信息。(1) 输出银行客户记录视图VW_userInfo显示的列名全为中文，要求先判断该视图是否存在，若存在，则先删除。代码如下所示：- (1)输出银行客户记录视图VW_userInfoif exists(select table_name from INFORMATION_SCHEMA.VIEWS where table_name=VW_userInfo)drop view VW_userInfogocreate view VW_userInfoasselect BCR.BCId as 客户编号,BCR.BCName as 开户名,BCR.BCICNo as 身份证号,BCR.BCTel as 电话号码,BCR.BCAddr as 居住地址from BankCustomer BCRgoselect * from VW_userInfo执行结果如下(图 4.1)所示：图 4.1(2) 输出银行卡记录视图VW_CardInfo代码如下所示：-(2)输出银行卡记录视图VW_CardInfoif exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME=VW_CardInfo)drop view VW_CardInfogocreate view VW_CardInfoasselect BC.BCNo as 卡号,BCR.BCName as 开户名,bc.BCCurrency as 货币种类,BBT.BBTName as 存款类型,bc.BCOpenDate as 开户日期,bc.BCExistBalance as 存款余额,bc.BCPwd as 密码,bc.BCRegLoss as 是否挂失from BankCard BCinner join BankBusinessType BBT on BBT.BBTId=BC.BCBBTIdinner join BankCustomer BCR on BCR.BCId = BC.BCBCIdgoselect * from VW_CardInfo执行结果如下(图 4.2)所示：图 4.2(3) 输出银行卡交易记录视图VW_TransInfo代码如下所示：-(3)输出银行卡交易记录视图VW_TransInfoif exists(select table_name from INFORMATION_SCHEMA.VIEWS where TABLE_NAME=VW_TransInfo)drop view VW_TransInfogocreate view VW_TransInfoasselect BDI.BDDealDate as 交易日期,BDI.BDDealType as 交易类型,BDI.BDBCNo as 卡号,BDI.BDDealAcount as 交易金额,BDI.BDDealComment as 备注from BankDealInfo BDIgoselect * from VW_TransInfo执行结果如下(图 4.3)所示：图 4.3(4) 根据客户登录名查询该客户账户信息 根据客户登录名（采用实名制访问银行系统）查询该客户账户信息的视图，利用SQL SERVER系统函数system_user获得数据库用户名。代码如下所示：-(4)根据客户登录名查询该客户账户信息VW_OneUserInfoif exists(select table_name from INFORMATION_SCHEMA.VIEWS where TABLE_NAME=VW_OneUserInfo)drop view VW_OneUserInfogocreate view VW_OneUserInfo asselect * from VW_userInfo where 开户名=SYSTEM_USERgoselect * from VW_OneUserInfo执行结果如下(图 4.4)所示：图 4.45、 存储过程实现业务处理(1) 完成存款或取款业务 描述： 根据银行卡号和交易金额实现银行卡的存款和取款业务。 每一笔存款，取款业务都要计入银行交易账，并同时更新客户的存款余额。 如果是取款业务，在记账之前，要完成下面两项数据的检查验证工作，如果检查不合格，那么中断取款业务，给出提示信息后退出。 检查客户输入的密码是否正确。 账户取款金额是否大于当前存款额加1。要求： 取款或存款存储过程名为usp_takeMoney。 编写一个存储过程完成存款和取款业务，并调用存储过程取钱或者存钱进行测试，测试数据是张飞的卡号支取100元（密码123456），关羽的卡号存入2100元。代码如下所示：-(1)完成存款或取款业务if exists(select * from sysobjects where name=usp_takeMoney)drop procedure usp_takeMoneygocreate procedure usp_takeMoney bcno char(19),money money,pwd char(6)=nullasset nocount ondeclare existBalance moneybegin transelect existBalance=BCExistBalance from BankCard where BCNo=bcnoprint 交易前的卡号是：+bcno+,余额为：+ltrim(str(existBalance)print 交易正在进行 ，请稍后.-判断是否有密码，若有密码则为取款，无密码为存款if(pwd is not null)begin-/取款-查询账户和密码是否存在if exists(select * from BankCard where BCNo=bcno and BCPwd=pwd)begin-/存在账户和密码-余额够不够if (moneyexistBalance)begin-/余额够了,那么取钱insert into BankDealInfo(BDBCNo,BDDealAcount,BDDealType,BDDealComment)values(bcno,money,支取,ATM取出)end -余额够了endelsebegin-/余额不够print 取款失败，余额不足，请少取点end-余额不够endend-存在账户 endelsebegin