數(shù)據(jù)庫(kù)課程設(shè)計(jì)-ATM存取款機(jī)系統(tǒng).doc

數(shù)據(jù)庫(kù)技術(shù)與開發(fā)項(xiàng)目實(shí)訓(xùn)設(shè)計(jì)報(bào)告項(xiàng)目名稱：ATM存取款機(jī)系統(tǒng)姓 名：*學(xué) 號(hào)：*專 業(yè)：軟件2012-1指導(dǎo)教師： 于金林完成日期：2014-11-19目錄一、創(chuàng)建數(shù)據(jù)庫(kù)11.1創(chuàng)建數(shù)據(jù)庫(kù)11.2 創(chuàng)建各個(gè)數(shù)據(jù)表及相關(guān)的約束21.3添加外鍵約束和生成數(shù)據(jù)庫(kù)關(guān)系圖5二、創(chuàng)建觸發(fā)器和插入測(cè)試數(shù)據(jù)72.1 創(chuàng)建級(jí)聯(lián)觸發(fā)器72.2 插入數(shù)據(jù)表的測(cè)試數(shù)據(jù)12三、模擬常規(guī)業(yè)務(wù)183.1 修改客戶密碼183.2 辦理銀行卡掛失183.3 統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算193.4 查詢本周開戶信息213.5 查詢本月單次交易金額最高的卡號(hào)和總交易金額最高的卡號(hào)213.6 查詢掛失客戶223.7 催款提醒業(yè)務(wù)23四、創(chuàng)建、使用視圖244.1 輸出銀行客戶記錄視圖VW_userInfo244.2 輸出銀行卡記錄視圖VW_CardInfo254.3 輸出銀行卡交易記錄視圖VW_TransInfo264.4根據(jù)客戶登錄名查詢?cè)摽蛻糍~戶信息VW_OneUserInfo27五、存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理285.1完成存款或取款業(yè)務(wù)285.2 產(chǎn)生隨機(jī)卡號(hào)325.3 完成開戶業(yè)務(wù)345.4 分頁(yè)顯示查詢交易數(shù)據(jù)375.5 打印客戶對(duì)賬單395.6 統(tǒng)計(jì)未發(fā)生交易的賬戶435.7 統(tǒng)計(jì)銀行卡交易量和交易額46六、利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬52七、心得體會(huì)59一、創(chuàng)建數(shù)據(jù)庫(kù)1.1創(chuàng)建數(shù)據(jù)庫(kù) 使用Create DataBase語句創(chuàng)建“ATM存取款機(jī)系統(tǒng)”數(shù)據(jù)庫(kù)BankDB代碼如下：-創(chuàng)建BankDB數(shù)據(jù)庫(kù)create database BankDB on primary(name=NBankDB,filename=NC:2014年工程案例項(xiàng)目銀行ATM存取款機(jī)系統(tǒng)BankDB.mdf, size=5MB, filegrowth=15%)log on(name=NBankDB_log,filename=NC:2014年工程案例項(xiàng)目銀行ATM存取款機(jī)系統(tǒng)BankDB_log.ldf, size=2MB, filegrowth=15%)GO1.2 創(chuàng)建各個(gè)數(shù)據(jù)表及相關(guān)的約束使用Create Table語句創(chuàng)建表結(jié)構(gòu)。根據(jù)銀行業(yè)務(wù)，分析表中每個(gè)列相應(yīng)的約束要求，為每個(gè)表添加各種約束。要求創(chuàng)建表時(shí)要求檢測(cè)是否存在表結(jié)構(gòu)，如果存在，則先刪除再創(chuàng)建。 代碼如下use BankDB;go-判斷銀行業(yè)務(wù)類型表是否存在,若存在則刪除if exists(select * from sysobjects where id=OBJECT_ID(NBankBusinessType) drop table BankBusinessType-創(chuàng)建銀行業(yè)務(wù)類型表create table BankBusinessType(BBTId int identity(1,1) primary key,-銀行業(yè)務(wù)類型編號(hào)BBTName char(20) not null,-銀行業(yè)務(wù)類型名稱BBTComment varchar(100)-銀行業(yè)務(wù)類型描述);go-判斷銀行卡客戶是否存在,若存在則刪除if exists(select * from sysobjects where id=OBJECT_ID(NBankCustomer) drop table BankCustomer-創(chuàng)建銀行客戶信息表create table BankCustomer(BCuId int identity(1,1) primary key,-銀行客戶編號(hào)BCuName char(20) not null,-銀行客戶名字BCuICNo char(18) not null -銀行客戶身份證號(hào)check(left(BCuICNo,17) like 0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9 and (right(BCuICNo,1) like 0-9 or right(BCuICNo,1) like X),-定義身份證號(hào)前位必須是數(shù)字，后位可以是數(shù)字或者X。BCuTel varchar(20) not null -銀行客戶聯(lián)系方式check(BCuTel like 0-90-90-90-9_0-90-90-90-90-90-90-9 orBCuTel like 0-90-90-9_0-90-90-90-90-90-90-90-9 or BCuTel like 13580-90-90-90-90-90-90-90-90-9),-定義聯(lián)系方式，必須是固定電話號(hào)碼或者手機(jī)號(hào)，手機(jī)前2位必須是13或15BCuAddress varchar(100)-銀行客戶地址);go-判斷銀行卡是否存在，若存在，則刪除銀行卡BankCardif exists(select * from sysobjects where id=object_id(NBankCard) drop table BankCard-建立銀行卡信息create table BankCard(-卡號(hào)必須符合16位數(shù)字構(gòu)成,前8位為1010 3576,后位是隨機(jī)產(chǎn)生且唯一,每4位必須有一個(gè)空格BCNo char(19) primary key check(BCNo like 1010 3576 0-90-90-90-9 0-90-90-90-9),BCPassword char(6) not null default(888888),-密碼,開戶默認(rèn)為888888BCCurrency char(5) not null default(RMB),-幣種,為RMB類型BCBBTId int not null,-存款類型BCOpenDate date not null default(getdate(),-開戶日期,默認(rèn)當(dāng)日BCOpenAmount money not null check(BCOpenAmount=1),-開戶金額,不得小于元BCRegLoss char(2) default(否),-是否掛失,默認(rèn)為否BCBCuId int not null,-客戶編號(hào)BCExistBalance money not null-余額);-判斷交易信息BankDealInfo是否存在,若存在則刪除if exists(select * from sysobjects where id=OBJECT_ID(NBankDealInfo) drop table BankDealInfo-創(chuàng)建交易信息表create table BankDealInfo(BDNo int identity(1,1) primary key,-交易編號(hào)為自動(dòng)增長(zhǎng)列BDBCNo char(19) not null,-卡號(hào)BDDealDate Date not null default(getdate(),-交易日期BDDealAcount money not null,-交易金額BDDealType Char(10) not null check(BDDealType=存入 or BDDealType=支取),-交易類型，有種存入和支取BDDealComment varchar(100)-交易描述);1.3添加外鍵約束和生成數(shù)據(jù)庫(kù)關(guān)系圖添加子表外鍵約束及生成數(shù)據(jù)庫(kù)關(guān)系圖代碼如下：-建立表之間的外鍵約束關(guān)系alter table bankcard add constraint fk_BC_BBT foreign key(BCBBTId) references bankbusinesstype(bbtid);-BC表與BTT表alter table bankcard add constraint fk_BC_BCu foreign key(BCBCuId) references BankCustomer(BCuId);-BC表與BCu表alter table bankDealInfoadd constraint fk_BD_BC foreign key(BDBCNo) references BankCard(BCNo);-BD表與BC表在SQL SERVER里自動(dòng)生成數(shù)據(jù)庫(kù)關(guān)系圖，如下所示：二、創(chuàng)建觸發(fā)器和插入測(cè)試數(shù)據(jù)2.1 創(chuàng)建級(jí)聯(lián)觸發(fā)器 2.1.1創(chuàng)建Insert觸發(fā)器 在交易信息表BankDealInfo中創(chuàng)建一個(gè)Insert觸發(fā)器，當(dāng)增加一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。 建議使用游標(biāo)，實(shí)現(xiàn)批量增加的級(jí)聯(lián)更新。-創(chuàng)建insert觸發(fā)器-在交易信息表中插入一個(gè)觸發(fā)器，使用游標(biāo)當(dāng)新增一個(gè)交易信息，修改銀行卡的存款余額if (object_id(tr_InsertdealInfo,tr) is not null) drop trigger tr_InsertdealInfogocreate trigger tr_InsertdealInfoon bankdealinfofor insertas declare type char(10),sum money,BDBCNo char(19); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted-創(chuàng)建一個(gè)游標(biāo)，指向inserted表 open cursor_BankDealinfo-打開游標(biāo) fetch next from cursor_BankDealinfo into type,sum,BDBCNo-取游標(biāo)中各個(gè)字段的值復(fù)制給各個(gè)變量 while fetch_status=0-fetch語句成功-=-1是語句失敗或此行不在結(jié)果集中；=-2是被提取的行不存在 begin -判斷交易記錄里是存入還是支取，及時(shí)更新銀行卡表的存款余額 if(rtrim(ltrim(type)=存入) -去除多余空格 update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo-關(guān)閉游標(biāo) deallocate cursor_BankDealinfo-釋放游標(biāo)Go2.1.2 創(chuàng)建Delete觸發(fā)器在交易信息表創(chuàng)建一個(gè)Delete觸發(fā)器，當(dāng)刪除一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。建議使用游標(biāo)，實(shí)現(xiàn)批量刪除的級(jí)聯(lián)更新。-創(chuàng)建delete觸發(fā)器-在交易信息表中插入一個(gè)觸發(fā)器，使用游標(biāo),當(dāng)刪除一個(gè)交易信息，修改銀行卡的存款余額if (object_id(tr_DeldealInfo,tr) is not null) drop trigger tr_DeldealInfogocreate trigger tr_DeldealInfoon bankdealinfofor deleteas declare type char(10),sum money,BDBCNo char(19); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted-創(chuàng)建一個(gè)游標(biāo)，指向deleted表 open cursor_BankDealinfo-打開游標(biāo) fetch next from cursor_BankDealinfo into type,sum,BDBCNo-取游標(biāo)中各個(gè)字段的值復(fù)制給各個(gè)變量 while fetch_status=0 begin if(rtrim(ltrim(type)=存入) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealinfo go2.1.3創(chuàng)建Update觸發(fā)器 創(chuàng)建一個(gè)Update觸發(fā)器，當(dāng)更新交易信息表的記錄時(shí)，更新銀行卡表的相應(yīng)卡號(hào)的余額。-創(chuàng)建update觸發(fā)器 -在交易信息表中插入一個(gè)觸發(fā)器，使用游標(biāo),當(dāng)刪除一個(gè)交易信息，修改銀行卡的存款余額if (object_id(tr_updatedealInfo,tr) is not null) drop trigger tr_updatedealInfogocreate trigger tr_updatedealInfoon bankdealinfofor updateas declare type char(10),sum money,BDBCNo char(19); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from inserted -創(chuàng)建一個(gè)游標(biāo)，指向inserted表 open cursor_BankDealinfo fetch next from cursor_BankDealinfo into type,sum,BDBCNo while fetch_status=0 begin if(rtrim(ltrim(type)=存入) update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealInfo; print(插入記錄); declare cursor_BankDealinfo cursor for select BDDealType,BDDealAcount,BDBCNo from deleted -創(chuàng)建一個(gè)游標(biāo)，指向deleted表 open cursor_BankDealinfo fetch next from cursor_BankDealinfo into type,sum,BDBCNo while fetch_status=0 begin if(rtrim(ltrim(type)=存入) update bankcard set BCExistBalance=BCExistBalance-sum where BCNo=BDBCNo; if(rtrim(ltrim(type)=支取) update bankcard set BCExistBalance=BCExistBalance+sum where BCNo=BDBCNo; fetch next from cursor_BankDealinfo into type,sum,BDBCNo end close cursor_BankDealinfo deallocate cursor_BankDealInfo; print(刪除記錄);2.2 插入數(shù)據(jù)表的測(cè)試數(shù)據(jù)使用T-SQL語句向每個(gè)表插入如測(cè)試數(shù)據(jù)，要保證業(yè)務(wù)數(shù)據(jù)的一致性和完整性。2.2.1 BankBusinessType表的測(cè)試數(shù)據(jù)代碼如下：-向已經(jīng)創(chuàng)建的BankBusinessType表插入測(cè)試數(shù)據(jù) insert into bankbusinessType(BBTName,BBTComment) values(活期,無固定存期,可隨時(shí)存取,存取金額不限的一種比較靈活的存款),-加上逗號(hào) (定活兩便,事先不約定存期，一次性存入，一次性支取的存款), (通知,不約定存期，支取時(shí)應(yīng)提前通知銀行，約定支取日期和金額方能支取的存款), (整存整取1年,整筆存入，到期提取本息), (整存整取2年,整筆存入，到期提取本息), (整存整取3年,整筆存入，到期提取本息),(零存整取1年,事先原定金額，逐月按約定金額存入，到期支付本息),(零存整取2年,事先原定金額，逐月按約定金額存入，到期支付本息),(零存整取3年,事先原定金額，逐月按約定金額存入，到期支付本息),(自助轉(zhuǎn)賬,銀行ATM存款機(jī)上辦理銀行卡之間互相劃轉(zhuǎn))-不加逗號(hào)select * from bankbusinesstype 運(yùn)行結(jié)果如下：2.2.2 BankCustomer表的測(cè)試數(shù)據(jù) 代碼如下：-向已經(jīng)創(chuàng)建的BankCustomer表中插入測(cè)試數(shù)據(jù)insert into bankcustomer (BCuName,BCuICNo,BCuTel,BCuAddress) values(張包頭市昆區(qū)包鋼五中), (關(guān)羽0472_2315490,包頭市昆區(qū)阿爾丁大街), (林俊杰,140203197616547894沈陽(yáng)市連山區(qū)), (張翰,21564876630154788X,0456_6561423,大連市幸福街)select * from BankCustomer 運(yùn)行結(jié)果如下：2.2.3 BankCard表的測(cè)試數(shù)據(jù)使用T-SQL語句向BankCard表中插入數(shù)據(jù)，要求開戶日期設(shè)置為當(dāng)前日期近一個(gè)月的隨機(jī)某一天（使用相應(yīng)的日期函數(shù)和隨機(jī)函數(shù)完成）。 代碼如下：insert into BankCard(BCNo,BCPassword,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCuId,BCExistBalance)values(1010 3576 1234 5678,000000,RMB,1,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),2000,否,1,2000), (1010 3576 1234 5688,000000,RMB,2,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),1500,否,2,1500), (1010 3576 1234 5698,000000,RMB,1,cast(dateadd(day,-cast(rand()*30 as int),getdate() as date),1000,否,1,1000);goselect * from BankCard;go運(yùn)行結(jié)果如下：2.2.4 BankDealInfo表的測(cè)試數(shù)據(jù)使用T-SQL語句向BankDealInfo表中插入數(shù)據(jù)，要求交易日期設(shè)置為當(dāng)前日期近半個(gè)月的隨機(jī)某一天（使用相應(yīng)的日期函數(shù)和隨機(jī)函數(shù)完成）。代碼如下：insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),400,存入,單位1月工資), (1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),500,存入,單位2月工資), (1010 3576 1234 5678,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),600,支取,支付寶付款), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),700,支取,刷卡消費(fèi)), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),800,存入,單位1月工資), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),900,存入,單位2月工資), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),1000,支取,支付寶付款), (1010 3576 1234 5688,cast(dateadd(day,-cast(rand()*15 as int),getdate() as date),900.,支取,刷卡消費(fèi));select * from BankDealInfo;Go運(yùn)行結(jié)果如下：三、模擬常規(guī)業(yè)務(wù)3.1 修改客戶密碼根據(jù)卡號(hào)修改指定2個(gè)客戶的銀行密碼，其中第一個(gè)客戶1010 3576 1234 5678密碼修改為123456，第二個(gè)客戶1010 3576 1234 5688修改為123123代碼如下：-修改客戶密碼update BankCard set BCPassword=123456 where BCNo=1010 3576 1234 5678;update BankCard set BCPassword=123123 where BCNo=1010 3576 1234 5688;goselect BCNo as 銀行卡卡號(hào),BCPassword as 密碼,BCCurrency as 幣種,BCBBTId as 銀行業(yè)務(wù)類型號(hào),BCOpenDate as 開戶日期,BCOpenAmount as 開戶金額,BCRegLoss as 是否掛失,BCBCuId as 客戶編號(hào),BCExistBalance as 余額from BankCard;go運(yùn)行結(jié)果如下：3.2 辦理銀行卡掛失卡號(hào)為1010 3576 1234 5678的銀行卡丟失，申請(qǐng)掛失代碼如下：-辦理銀行卡掛失update BankCard set BCRegLoss=是 where BCNo=1010 3576 1234 5688;goselect BCNo as 銀行卡卡號(hào),BCPassword as 密碼,BCCurrency as 幣種,BBTName as 銀行業(yè)務(wù)類型名,BCOpenDate as 開戶日期,BCOpenAmount as 開戶金額,BCRegLoss as 是否掛失,BCuName as 銀行客戶姓名,BCExistBalance as 余額from BankCard inner join BankCustomer on BankCard.BCBCuid=BankCustomer.BCuId-銀行卡和客戶進(jìn)行內(nèi)連接inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTIdgo運(yùn)行結(jié)果如下：3.3 統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算存入代表資金流入，支取代表資金流出。計(jì)算公式：資金流通余額=總存入金額-總支取金額假定存款利率為千分之三，貸款利率為千分之八。計(jì)算公式：盈利結(jié)算=總支取金額*0.008-總存入金額*0.003。要求創(chuàng)建一個(gè)存儲(chǔ)過程pro_staticsBanlanceAndProfit代碼如下：-統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算if exists(select * from sysobjects where name=proc_staticsBanlanceAndProfit ) drop procedure proc_staticsBanlanceAndProfit; go -創(chuàng)建存儲(chǔ)過程create procedure proc_staticsBanlanceAndProfitasdeclare InMoney money,OutMoney money;select InMoney=SUM(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType)=存入;select OutMoney=SUM(BDDealAcount) from BankDealInfo where rtrim(ltrim(BDDealType)=支取;print 存入總金額:+rtrim(ltrim(str(InMoney)+RMB,支取總金額:+rtrim(ltrim(str(OutMoney)+RMB,銀行流通余額:+rtrim(ltrim(str(InMoney-OutMoney)+RMB;-資金流通余額=總存入金額-總支取金額print 盈利余額為:+rtrim(ltrim(str(OutMoney*0.008-InMoney*0.003)+RMB-盈利結(jié)算=總支取金額*0.008-總存入金額*0.003exec proc_staticsBanlanceAndProfit;運(yùn)行結(jié)果如下：3.4 查詢本周開戶信息查詢本周開戶的卡號(hào)，顯示該卡的相關(guān)信息， 提示：求時(shí)間差使用日期函數(shù)DateDiff()，求星期幾使用日期函數(shù)DatePart()，賬戶狀態(tài)列使用Case語句轉(zhuǎn)換。代碼如下：-查詢本周開戶信息select BCNo as 銀行卡卡號(hào),BCOpenDate as 開戶日期,BCOpenAmount as 開戶金額 from BankCard whereDATEDIFF(day,BCOpenDate,GETDATE()DATEPART(WEEKDAY,GETDATE();go運(yùn)行結(jié)果如下：3.5 查詢本月單次交易金額最高的卡號(hào)和總交易金額最高的卡號(hào)查詢本月存、取款中單次交易金額最高的卡號(hào)信息，提示：要求使用子查詢和distinct去掉重復(fù)的卡號(hào),使用DateDiff()和DataPart()函數(shù)得到本月內(nèi)的交易信息。代碼如下：-查詢本月存、取款中單次交易金額最高的卡號(hào)信息select distinct BDBCNo as 銀行卡卡號(hào),BCOpenDate as 開戶日期,BCOpenAmount as 開戶金額 from BankDealInfo,BankCard where BankDealInfo.BDBCNo=BankCard.BCNo and BDDealAcount=(select Max(BDDealAcount) from BankDealInfo) and datediff(day,BCOpenDate,getdate()datepart(month,getdate(); 運(yùn)行結(jié)果如下：3.6 查詢掛失客戶查詢掛失賬號(hào)的客戶信息，分別利用子查詢in的方式或者內(nèi)部連接inner join。代碼如下：-查詢掛失客戶select BCuName as 客戶姓名,bcuicno as 客戶身份證號(hào),BCuTel as 聯(lián)系電話,bcuaddress as 客戶地址 from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuId whereBCRegLoss=是;Go運(yùn)行結(jié)果如下:3.7 催款提醒業(yè)務(wù)根據(jù)某種業(yè)務(wù)（如代繳電話費(fèi)、代繳手機(jī)費(fèi)或房貸等）的需要，每個(gè)月末，查詢出客戶賬戶上余額少于2000元，由銀行統(tǒng)一致電催款。代碼如下：-催款提醒業(yè)務(wù)select BCuName as 客戶姓名,bcuicno as 客戶身份證號(hào), BCuTel as 聯(lián)系電話,BCExistBalance as 賬上余額 from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuId whereBankCard.BCExistBalance2000;go運(yùn)行結(jié)果如下：四、創(chuàng)建、使用視圖4.1 輸出銀行客戶記錄視圖VW_userInfo顯示的列名全為中文，要求先判斷該視圖是否存在，若存在，則先刪除代碼如下：-輸出銀行客戶記錄視圖VW_userInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_userInfo)drop view VW_userInfo;gocreate view VW_userInfo as-指定視圖要執(zhí)行的操作select BCuId as 客戶號(hào),BCuName as 客戶姓名,BCuICNo as 客戶身份證號(hào),BCuTel as 客戶電話,BCuAddress as 客戶地址 from BankCustomer;gouse bankdb select * from VW_userInfo;運(yùn)行結(jié)果如下：4.2 輸出銀行卡記錄視圖VW_CardInfo建議使用內(nèi)部連接Inner Join語句代碼如下：-輸出銀行卡記錄視圖VW_CardInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_CardInfo)drop view VW_CardInfo;gocreate view VW_CardInfo asselect BCNo as 卡號(hào),BCPassword as 密碼,BCCurrency as 幣種,BBTName as 存儲(chǔ)類型,BCOpenDate as 開戶日期,BCExistBalance as 余額,BCRegLoss as 是否掛失from BankCard inner join BankCustomer on BankCard.BCBCuId=BankCustomer.BCuIdinner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId;gouse bankdbselect * from VW_CardInfo;運(yùn)行結(jié)果如下：4.3 輸出銀行卡交易記錄視圖VW_TransInfo 代碼如下：-輸出銀行卡交易記錄視圖VW_TransInfoIF EXISTS (SELECT * FROM sys.views WHERE name =VW_TransInfo)drop view VW_TransInfo;gocreate view VW_TransInfo asselect BDBCNo as 卡號(hào), BDDealDate as 交易日期,BDDealType as 交易類型,BDDealAcount as 交易金額,BDDealComment as 交易描述 from BankDealInfo;gouse bankdb select * from VW_TransInfo;運(yùn)行結(jié)果如下：4.4根據(jù)客戶登錄名查詢?cè)摽蛻糍~戶信息VW_OneUserInfo 根據(jù)客戶登錄名（采用實(shí)名制訪問銀行系統(tǒng)）查詢?cè)摽蛻糍~戶信息的視圖，利用SQL SERVER系統(tǒng)函數(shù)system_user獲得數(shù)據(jù)庫(kù)用戶名。代碼如下：-根據(jù)客戶登錄名查詢?cè)摽蛻糍~戶信息VW_OneUserInfoIF EXISTS(SELECT 1 FROM sys.views WHERE name=VW_OneUserInfo) DROP VIEW VW_OneUserInfo go CREATE VIEW VW_OneUserInfo as SELECT BCuId AS 客戶編號(hào),BCuName AS 開戶名,BCuICNo AS 身份證號(hào),BCuTel AS 電話號(hào)碼,BCuAddress AS 居住地址 from BankCustomerWHERE BCuName=system_userSELECT * FROM VW_OneUserInfo運(yùn)行結(jié)果如下：五、存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理5.1完成存款或取款業(yè)務(wù) 描述： 根據(jù)銀行卡號(hào)和交易金額實(shí)現(xiàn)銀行卡的存款和取款業(yè)務(wù)。 每一筆存款，取款業(yè)務(wù)都要計(jì)入銀行交易賬，并同時(shí)更新客戶的存款余額。 如果是取款業(yè)務(wù)，在記賬之前，要完成下面兩項(xiàng)數(shù)據(jù)的檢查驗(yàn)證工作，如果檢查不合格，那么中斷取款業(yè)務(wù)，給出提示信息后退出。 檢查客戶輸入的密碼是否正確。 賬戶取款金額是否大于當(dāng)前存款額加1。要求： 取款或存款存儲(chǔ)過程名為pro_takeMoney。 編寫一個(gè)存儲(chǔ)過程完成存款和取款業(yè)務(wù)，并調(diào)用存儲(chǔ)過程取錢或者存錢進(jìn)行測(cè)試，測(cè)試數(shù)據(jù)是張飛的卡號(hào)支取100元（密碼123456），關(guān)羽的卡號(hào)存入2100元。代碼如下：- 完成存款或取款業(yè)務(wù)if exists(select * from sysobjects where name=pro_TakeMoney)drop procedure pro_TakeMoneygo-創(chuàng)建存取款業(yè)務(wù)的存儲(chǔ)過程create procedure pro_TakeMoneybcno char(19),money money,password char(6)=nullasset nocount on;declare bcexistBalance money;begin transactionselect bcexistBalance=BCexistBalance from BankCard where BCNo=bcno;print 交易之前，卡號(hào)+bcno+，余額為：+ltrim(str(bcexistBalance);print 交易正在進(jìn)行，請(qǐng)耐心等候.;if (password is not null)-如果密碼不空，則為取款beginif exists(select * from BankCard where BCNo=bcno and BCPassword=password)beginif(money=bcExistBalance-1)insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(bcno,getdate(),money,支取,通過ATM取款);elseprint 取款交易失敗，余額不足，請(qǐng)減少取款;endelseprint 取款交易失敗，密碼有錯(cuò)誤;endelse-如果密碼為空，則為存款insert into BankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)values(bcno,getdate(),money,存入,通過ATM存款);-辦理存款業(yè)務(wù) if (ERROR0)-事務(wù)處理時(shí)異常，則回滾beginprint 交易失敗;rollback transaction;endelse-沒有異常，則提交commit transaction;print 交易成功，交易金額為：+ltrim(str(money);select bcexistBalance=BCExistBalance from BankCard where BCNo=bcno;print 卡號(hào)+bcno+，余額為：+cast(bcexistBalance as char(20);select * from bankcard;select * from bankdealinfo;Go exec pro_TakeMoney 1010 3576 1234 5678,2100; go exec pro_TakeMoney 1010 3576 1234 5678,100,123456; go張飛卡號(hào)存入2100元的運(yùn)行結(jié)果：關(guān)羽卡號(hào)取款100元的運(yùn)行結(jié)果：5.2 產(chǎn)生隨機(jī)卡號(hào)創(chuàng)建存儲(chǔ)過程產(chǎn)生8位隨機(jī)數(shù)字，與前8位固定數(shù)字“1010 3576”連接，生成一個(gè)由16位數(shù)字組成的銀行卡號(hào)，并輸出。要求： 產(chǎn)生隨機(jī)卡號(hào)的存儲(chǔ)過程名為Pro_randCardID。 使用隨機(jī)函數(shù)生成銀行卡后8位的數(shù)字，隨機(jī)函數(shù)的用法：rand(隨機(jī)種子)將產(chǎn)生01的隨機(jī)數(shù)，要求每次的隨機(jī)種子不一樣。 為了保證隨機(jī)種子每次都不相同，一般采用的算法是：隨機(jī)種子=當(dāng)前的月份數(shù)*100000+當(dāng)前的秒數(shù)*1000+當(dāng)前的毫秒數(shù)產(chǎn)生了01的隨機(jī)數(shù)后，取小數(shù)點(diǎn)后8位，即：0.XX