數(shù)據(jù)庫技術(shù)與應(yīng)用課程設(shè)計(jì)報(bào)告.doc

數(shù)據(jù)庫技術(shù)與應(yīng)用課程設(shè)計(jì)報(bào)告學(xué) 號：1467159124姓 名：張喜泉專 業(yè)：軟件工程指導(dǎo)教師：康懿完成日期：2016-12-24目錄銀行ATM存取款機(jī)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)3一、項(xiàng)目背景31、項(xiàng)目任務(wù)32、項(xiàng)目技能目標(biāo)33、需求概述34、開發(fā)環(huán)境35、 問題分析36、 實(shí)訓(xùn)進(jìn)度安排5二、項(xiàng)目實(shí)訓(xùn)內(nèi)容5制定數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范51、 實(shí)訓(xùn)一：創(chuàng)建數(shù)據(jù)庫52、 實(shí)訓(xùn)二：創(chuàng)建觸發(fā)器和隨機(jī)卡號的存儲過程(2學(xué)時(shí))93、 實(shí)訓(xùn)三：生成各個(gè)表的測試數(shù)據(jù)(4學(xué)時(shí))114、實(shí)訓(xùn)四：模擬常規(guī)業(yè)務(wù)165、實(shí)訓(xùn)五：查詢統(tǒng)計(jì)226、實(shí)訓(xùn)六：創(chuàng)建、使用視圖257、實(shí)訓(xùn)七：存儲過程實(shí)現(xiàn)業(yè)務(wù)處理28358、實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬35三：心得體會41銀行ATM存取款機(jī)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)一、項(xiàng)目背景1、項(xiàng)目任務(wù) 創(chuàng)建數(shù)據(jù)庫、創(chuàng)建表、創(chuàng)建約束 使用觸發(fā)器和插入測試數(shù)據(jù) 模擬常規(guī)業(yè)務(wù)、創(chuàng)建視圖 使用存儲過程實(shí)現(xiàn)業(yè)務(wù)處理 利用事務(wù)實(shí)現(xiàn)較復(fù)雜的數(shù)據(jù)更新2、項(xiàng)目技能目標(biāo) 使用T-SQL語句創(chuàng)建數(shù)據(jù)庫、表和各種約束。 使用T-SQL語句編程實(shí)現(xiàn)常見業(yè)務(wù)。 使用觸發(fā)器實(shí)現(xiàn)多表之間的級聯(lián)更新。 使用事務(wù)和存儲過程封裝業(yè)務(wù)邏輯。 使用視圖簡化復(fù)雜的數(shù)據(jù)查詢。 使用游標(biāo)技術(shù)實(shí)現(xiàn)結(jié)果集的行集操作。3、需求概述某銀行是一家民辦的小型銀行企業(yè)，現(xiàn)有十多萬客戶，公司將為該銀行開發(fā)一套ATM存取款機(jī)系統(tǒng)，對銀行日常的存取款業(yè)務(wù)進(jìn)行計(jì)算機(jī)管理，以便保證數(shù)據(jù)的安全性，提高工作效率。要求根據(jù)銀行存取款業(yè)務(wù)需求設(shè)計(jì)出符合第三范式的數(shù)據(jù)庫結(jié)構(gòu)，使用T-SQL語言創(chuàng)建數(shù)據(jù)庫和表，并添加表約束，進(jìn)行數(shù)據(jù)的增刪改查，運(yùn)用邏輯結(jié)構(gòu)語句、事務(wù)、視圖和存儲過程，按照銀行的業(yè)務(wù)需求，實(shí)現(xiàn)各項(xiàng)銀行日常存款、取款和轉(zhuǎn)賬業(yè)務(wù)。4、開發(fā)環(huán)境 數(shù)據(jù)庫：SQL SERVER 2008開發(fā)版5、 問題分析該項(xiàng)目的ATM存取款機(jī)業(yè)務(wù)如下：(1) 銀行存取款業(yè)務(wù)介紹 銀行為客戶提供了各種銀行存取款業(yè)務(wù)。詳見表1表1 銀行存取款業(yè)務(wù)業(yè)務(wù)描述活期無固定存期，可隨時(shí)存取，存取金額不限的一種比較靈活的存款定活兩便事先不約定存期，一次性存入，一次性支取的存款通知不約定存期，支取時(shí)需提前通知銀行，約定支取日期和金額方能支取的存款整存整取選擇存款期限，整筆存入，到期提取本息的一種定期儲蓄。銀行提供的存款期限有1年、2年和3年零存整取一種事先原定金額，逐月按約定金額存入，到期支取本息的定期儲蓄。銀行提供的存款期限由1年、2年和3年自助轉(zhuǎn)賬在ATM存取款機(jī)上辦理同一幣種賬戶的銀行卡之間互相劃轉(zhuǎn)(2) 客戶信息 每個(gè)客戶憑個(gè)人身份證在銀行可以開設(shè)多個(gè)銀行卡賬戶，開設(shè)賬戶時(shí)，客戶需要提供的開戶數(shù)據(jù)如表2所示：表2 開設(shè)銀行卡賬戶的客戶信息數(shù)據(jù)描述姓名必須提供身份證號唯一確定客戶，是由17位數(shù)字和1位數(shù)字或者字符X構(gòu)成。聯(lián)系電話手機(jī)號碼：由11位數(shù)字構(gòu)成，且前2位必須是13或者15、18開頭。居住地址可以選擇(3) 開戶網(wǎng)點(diǎn)信息表3 開戶網(wǎng)點(diǎn)信息數(shù)據(jù)描述網(wǎng)點(diǎn)編號編號由6位數(shù)字構(gòu)成。網(wǎng)點(diǎn)名稱開戶行中文名稱網(wǎng)點(diǎn)地址開戶行所在地址信息。(4) 銀行卡賬戶信息 銀行為每個(gè)賬戶提供一個(gè)銀行卡，每個(gè)銀行卡可以存入一種幣種的存款，銀行保存賬戶如表3所示：表4 銀行卡賬戶信息數(shù)據(jù)描述卡號銀行的卡號由16位數(shù)字組成，其中：一般前8位代表特俗含義，如代表某總行某支行等，假定該行要求其營業(yè)廳的卡號格式為1010 3576 XXXX XXXX，后8位必須是隨機(jī)產(chǎn)生且唯一，每4位號碼后有空格。密碼由6位數(shù)字構(gòu)成，開戶時(shí)默認(rèn)為“888888”幣種默認(rèn)為RMB，目前該銀行尚未開設(shè)其他幣種存款業(yè)務(wù)。存款類型必須選擇開戶日期客戶開設(shè)銀行卡賬戶的日期，默認(rèn)為當(dāng)日開戶金額客戶開設(shè)銀行卡賬戶時(shí)存入的金額，規(guī)定不得小于1元。是否掛失默認(rèn)為“否”網(wǎng)點(diǎn)編號客戶網(wǎng)點(diǎn)編號 客戶持銀行卡在ATM機(jī)上輸入密碼，經(jīng)系統(tǒng)驗(yàn)證身份后辦理存款、取款和轉(zhuǎn)賬等銀行業(yè)務(wù)。銀行規(guī)定，每個(gè)賬戶當(dāng)前的存款金額不得小于1元。(5) 銀行卡交易信息 銀行在為客戶辦理業(yè)務(wù)時(shí)，需要記錄每一筆賬目，賬目交易信息如表4所示：表1 銀行卡交易信息數(shù)據(jù)描述卡號銀行的卡號由16位數(shù)字組成交易日期默認(rèn)為當(dāng)日交易金額必須大于0元金額,必須為100元的整數(shù)倍交易類型包括：存款、取款、轉(zhuǎn)入或者轉(zhuǎn)出4種備注對每筆交易做必要的說明6、 實(shí)訓(xùn)進(jìn)度安排實(shí)訓(xùn)進(jìn)度安排如下表所示：表2 實(shí)訓(xùn)進(jìn)度安排實(shí)訓(xùn)內(nèi)容所需學(xué)時(shí)提交文檔實(shí)訓(xùn)一：制定數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范41份數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范實(shí)訓(xùn)二：創(chuàng)建數(shù)據(jù)庫4T-SQL源文件實(shí)訓(xùn)三：創(chuàng)建觸發(fā)器和插入測試數(shù)據(jù)4T-SQL源文件實(shí)訓(xùn)五：模擬常規(guī)業(yè)務(wù)4T-SQL源文件實(shí)訓(xùn)六：創(chuàng)建和使用視圖4T-SQL源文件實(shí)訓(xùn)七：存儲過程實(shí)現(xiàn)業(yè)務(wù)處理6T-SQL源文件實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬業(yè)務(wù)6T-SQL源文件二、項(xiàng)目實(shí)訓(xùn)內(nèi)容實(shí)訓(xùn)內(nèi)容由5個(gè)實(shí)訓(xùn)項(xiàng)目構(gòu)成，建議在參考代碼和實(shí)現(xiàn)步驟基礎(chǔ)上進(jìn)行改進(jìn)，每個(gè)實(shí)訓(xùn)子項(xiàng)目的T-SQL語句寫成1個(gè)T-SQL源文件，如item1.sql。 制定數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范 參考技術(shù)文檔：數(shù)據(jù)庫設(shè)計(jì)規(guī)范 (1)、數(shù)據(jù)庫設(shè)計(jì)規(guī)范(修訂)、數(shù)據(jù)庫設(shè)計(jì)及編寫規(guī)范、編程規(guī)范(T-SQL)、Transact-SQL_數(shù)據(jù)庫編程命名規(guī)范、SQL_Server數(shù)據(jù)庫編程規(guī)范等技術(shù)文檔， 制定一份3-5頁，不少于1500字的數(shù)據(jù)庫設(shè)計(jì)與SQL編程規(guī)范，要求至少包含各個(gè)數(shù)據(jù)庫對象的命名規(guī)范、編程規(guī)范及注釋規(guī)范。 該實(shí)訓(xùn)項(xiàng)目的設(shè)計(jì)與編程要求遵循該實(shí)訓(xùn)制定的數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范。1、 實(shí)訓(xùn)一：創(chuàng)建數(shù)據(jù)庫 閱讀數(shù)據(jù)庫結(jié)構(gòu)相關(guān)描述表名：BankBusinessType銀行業(yè)務(wù)類型表序號列名數(shù)據(jù)類型長度小數(shù)位標(biāo)識主鍵外鍵允許空默認(rèn)值說明1BBTIdint40是是否銀行業(yè)務(wù)類型編號，自動(dòng)增長列2BBTNamechar200否銀行業(yè)務(wù)類型名稱3BBTCommentvarchar1000是銀行業(yè)務(wù)描述表名：BankCard銀行卡序號列名數(shù)據(jù)類型長度小數(shù)位標(biāo)識主鍵外鍵允許空默認(rèn)值說明1BCNochar190是否卡號2BCPwdchar60否888888密碼3BCCurrencychar50否RMB幣種4BCBBTIdint40是否業(yè)務(wù)類型5BCOpenDatedate30否getdate開戶日期6BCOpenAmountmoney84否開戶金額7BCRegLosschar20是否是否掛失8BCBCIdint40否客戶編號9BCExistBalancemoney84否賬戶余額10BCBDIDchar60是是開戶行編號表名：BankCustomer客戶信息序號列名數(shù)據(jù)類型長度小數(shù)位標(biāo)識主鍵外鍵允許空默認(rèn)值說明1BCIdint40是是否客戶編號2BCNamechar200否客戶名稱3BCICNochar180否客戶身份證號4BCTelvarchar200否客戶電話號5BCAddrvarchar1000是客戶地址表名：BankDealInfo交易信息序號列名數(shù)據(jù)類型長度小數(shù)位標(biāo)識主鍵外鍵允許空默認(rèn)值說明1BDNoint40是是否交易編號2BDBCNochar190是否銀行卡號3BDDealDatedate30否getdate交易日期4BDDealAcountmoney84否交易金額5BDDealTypechar100否交易類型6BDDealCommentvarchar1000是描述表名：BankDesposit開戶網(wǎng)點(diǎn)信息序號列名數(shù)據(jù)類型長度小數(shù)位標(biāo)識主鍵外鍵允許空默認(rèn)值說明1BDIDchar60是否網(wǎng)點(diǎn)編號2BDNamechar200否網(wǎng)點(diǎn)名稱3BDAddresschar500是網(wǎng)點(diǎn)地址使用T-SQL語句完成數(shù)據(jù)庫、數(shù)據(jù)表和各種約束的創(chuàng)建，并保存為item1.sql文件。按下述推薦步驟，在4學(xué)時(shí)內(nèi)完成下述實(shí)訓(xùn)內(nèi)容：(1) 創(chuàng)建數(shù)據(jù)庫（1學(xué)時(shí)）使用Create DataBase語句創(chuàng)建“ATM存取款機(jī)系統(tǒng)”數(shù)據(jù)庫BankDB，數(shù)據(jù)文件和日志文件保存在指定目錄下（建議建立一個(gè)文件夾，用于存放該實(shí)訓(xùn)項(xiàng)目的所有相關(guān)T-SQL源文件），文件增長率為15%。(2) 創(chuàng)建各個(gè)數(shù)據(jù)表及相關(guān)的約束（2學(xué)時(shí)）根據(jù)數(shù)據(jù)表結(jié)構(gòu)，使用Create Table語句創(chuàng)建表結(jié)構(gòu)。根據(jù)銀行業(yè)務(wù)，分析表中每個(gè)列相應(yīng)的約束要求，為每個(gè)表添加各種約束。要求創(chuàng)建表時(shí)要求檢測是否存在表結(jié)構(gòu)，如果存在，則先刪除再創(chuàng)建。2、 實(shí)訓(xùn)二：創(chuàng)建觸發(fā)器和隨機(jī)卡號的存儲過程(2學(xué)時(shí))使用T-SQL語句完成觸發(fā)器和隨機(jī)卡號存儲過程的創(chuàng)建，并保存為item2.sql文件。(1) 創(chuàng)建級聯(lián)觸發(fā)器 創(chuàng)建Insert觸發(fā)器 在交易信息表BankDealInfo中創(chuàng)建一個(gè)Insert觸發(fā)器，當(dāng)增加一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。建議使用游標(biāo)，實(shí)現(xiàn)批量增加的級聯(lián)更新。 創(chuàng)建Delete觸發(fā)器在交易信息表創(chuàng)建一個(gè)Delete觸發(fā)器，當(dāng)刪除一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。(2) 創(chuàng)建產(chǎn)生隨機(jī)卡號的存儲過程Proc_randCardID創(chuàng)建存儲過程產(chǎn)生8位隨機(jī)數(shù)字，與前8位固定數(shù)字“1010 3576”連接，生成一個(gè)由16位數(shù)字組成的銀行卡號，并輸出。要求： 產(chǎn)生隨機(jī)卡號的存儲過程名為Proc_randCardID。 利用下面的代碼調(diào)用存儲過程進(jìn)行測試declare myCardId1 char(19)exec proc_randCardId myCardId1 outputprint 產(chǎn)生隨機(jī)卡號為+myCardId1 結(jié)果如圖所示：3、 實(shí)訓(xùn)三：生成各個(gè)表的測試數(shù)據(jù)(4學(xué)時(shí))使用T-SQL語句向每個(gè)表插入如下所示測試數(shù)據(jù)，要保證業(yè)務(wù)數(shù)據(jù)的一致性和完整性，保存為item3.sql文件(1) BankBusinessType表的測試數(shù)據(jù) 使用T-SQL向已經(jīng)創(chuàng)建的BankBusinessType表插入如下數(shù)據(jù)：由于該表的數(shù)據(jù)已經(jīng)給出，故直接使用向?qū)?dǎo)入。2) BankDesposit表的測試數(shù)據(jù) 使用T-SQL語句插入不少于10條開戶網(wǎng)點(diǎn)信息，參考數(shù)據(jù)如下所示：BankCustomer表的測試數(shù)據(jù)BankCustomer表，產(chǎn)生10000條記錄，如下圖所示：導(dǎo)入數(shù)據(jù)bankcustomerBankCard表的測試數(shù)據(jù)使用T-SQL語句向BankCard表中插入15000條數(shù)據(jù)，卡號調(diào)用存儲過程Proc_randCardID隨機(jī)產(chǎn)生，開戶日期設(shè)置為當(dāng)前日期近3年的隨機(jī)某一天（使用相應(yīng)的日期函數(shù)和隨機(jī)函數(shù)完成），開戶金額為100-2,000之間的正數(shù)（100的倍數(shù)）。BankDealInfo表的測試數(shù)據(jù)使用T-SQL語句向BankDealInfo表中插入20萬條數(shù)據(jù)，要求交易日期設(shè)置為當(dāng)前日期近3年的隨機(jī)某一天（使用相應(yīng)的日期函數(shù)和隨機(jī)函數(shù)完成），要求交易日期晚于該卡的開戶日期，交易金額為100-2,000之間的正數(shù)（100的倍數(shù)）。 實(shí)訓(xùn)四：模擬常規(guī)業(yè)務(wù)使用T-SQL語句實(shí)現(xiàn)銀行的日常業(yè)務(wù)，并保存為item4.sql文件。按下述推薦步驟，在4學(xué)時(shí)內(nèi)完成下述實(shí)訓(xùn)內(nèi)容：(1) 修改存款類型為活期的銀行卡密碼將存款類型為活期的銀行卡密碼改為身份證號后6位，并查詢本周開戶的銀行卡信息，如圖所示。寫出更新密碼和查詢修改密碼效果圖的T-SQL語句。修改客戶密碼效果圖（只顯示本周開戶的銀行卡）(2) 辦理銀行卡掛失 將賬戶余額小于0的銀行卡辦理為掛失狀態(tài)，并顯示如下查詢結(jié)果（按銀行卡開戶日期排序）：由于設(shè)計(jì)表數(shù)據(jù)時(shí)就限制了余額不能小于0，所以查詢?yōu)榭?3) 統(tǒng)計(jì)某個(gè)指定客戶的資金流通余額和盈利結(jié)算存入代表資金流入，支取代表資金流出（掛失的銀行卡不參與計(jì)算）。計(jì)算公式：資金流通余額=總存入金額-總支取金額假定存款利率為千分之三，貸款利率為千分之八。計(jì)算公式：盈利結(jié)算=總支取金額*0.008-總存入金額*0.003。要求創(chuàng)建一個(gè)存儲過程proc_staticsBanlanceAndProfit，執(zhí)行該存儲過程運(yùn)行結(jié)果如下圖所示：-執(zhí)行統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算的存儲過程exec proc_staticsBanlanceAndProfit 張建軍;圖1 統(tǒng)計(jì)某客戶的資金流通余額和盈利結(jié)算(4) 查詢某個(gè)開戶網(wǎng)點(diǎn)本季度開戶信息查詢本季度某個(gè)開戶網(wǎng)點(diǎn)的銀行卡開戶相關(guān)信息，如查詢大連新型支行的本季度開戶信息。結(jié)果如下圖所示，按開戶日期排序：圖2 本季度某個(gè)開戶網(wǎng)點(diǎn)的開戶信息 (5) 查詢本周開戶且本周單次交易金額最高的交易信息查詢本周開戶的銀行卡中單次交易金額最高的信息。結(jié)果如下圖所示： 查詢本季度各銀行卡交易總額的信息，結(jié)果如下圖所示：圖1 本季度各銀行卡的交易信息：卡號，交易總額 查詢本季度交易總金額最高的銀行卡信息。圖1 本季度銀行卡交易額最高的銀行卡信息：卡號、開戶日期、開戶金額(6) 查詢掛失客戶信息查詢掛失賬號的客戶信息，利用子查詢in的方式，查詢結(jié)果如下圖所示：圖3 查詢掛失客戶由于所有卡余額均大于等于0，所以并沒有掛失賬戶，此查詢?yōu)榭諏?shí)訓(xùn)五：查詢統(tǒng)計(jì)使用T-SQL語句實(shí)現(xiàn)銀行的日常業(yè)務(wù)，并保存為item5.sql文件。按下述推薦步驟，在4學(xué)時(shí)內(nèi)完成下述實(shí)訓(xùn)內(nèi)容：(1) 催款提醒業(yè)務(wù)根據(jù)某種業(yè)務(wù)（如代繳電話費(fèi)、代繳手機(jī)費(fèi)或房貸等）的需要，每個(gè)月末，查詢出各個(gè)開戶網(wǎng)點(diǎn)中客戶賬戶上余額少于200元的客戶總數(shù)，由銀行統(tǒng)一致電催款。查詢結(jié)果如下圖所示：圖4 催款提醒業(yè)務(wù) 查詢沒有開戶的客戶信息查詢沒有開戶的客戶信息，查詢結(jié)果如下圖所示：圖5 查詢沒有開戶的客戶信息由于在設(shè)計(jì)表數(shù)據(jù)時(shí)為每一位客戶都至少開了一張卡，故不存在未開戶客戶(3) 統(tǒng)計(jì)各個(gè)開戶網(wǎng)點(diǎn)營業(yè)情況 顯示各開戶網(wǎng)點(diǎn)的本周開戶數(shù)（只統(tǒng)計(jì)賬戶余額在5000元以上的銀行卡），交易總金額，交易總筆數(shù)。查詢結(jié)果如下圖所示：圖6 查詢本周各開戶網(wǎng)點(diǎn)的營業(yè)情況(4) 查詢客戶開卡數(shù)量 顯示開卡數(shù)量等于或者超過5張的客戶信息。查詢結(jié)果如下圖所示：圖7 查詢開卡數(shù)量5張及以上的客戶信息(5) 統(tǒng)計(jì)本月開戶的銀行卡支取情況 顯示本月開戶的銀行卡支取情況，查看各個(gè)銀行卡的開戶金額+收入總額-支出總額是否和賬戶余額相符。查詢結(jié)果如下圖所示：圖8 查詢本月開戶的銀行卡支取情況6、實(shí)訓(xùn)六：創(chuàng)建、使用視圖使用T-SQL語句創(chuàng)建如下視圖，并保存為item6.sql文件。按下述推薦步驟，在2學(xué)時(shí)內(nèi)完成下述實(shí)訓(xùn)內(nèi)容：為向客戶提供友好的用戶界面，使用T-SQL語句創(chuàng)建下面幾個(gè)視圖，并使用這些視圖輸出各表信息。(1) 輸出銀行客戶記錄視圖VW_userInfo 顯示的列名全為中文，顯示銀行卡沒有掛失的客戶記錄視圖，要求先判斷該視圖是否存在，若存在，則先刪除。結(jié)果如下圖所示： 圖9 輸出銀行客戶記錄（其對應(yīng)的銀行卡狀態(tài)為未掛失）(2) 輸出銀行卡記錄視圖VW_CardInfo建議使用內(nèi)部連接Inner Join語句，結(jié)果如下圖所示：圖10 輸出銀行卡記錄(3) 輸出銀行卡交易記錄視圖VW_TransInfo查詢該視圖，視圖按交易日期排序，結(jié)果如下圖所示：由于要在視圖中使用order by 因此使用top 圖11 輸出銀行卡的交易記錄(4) 查詢本季度沒有交易記錄的客戶信息 創(chuàng)建視圖vw_searchCustomerNoDeal，查詢本季度沒有交易記錄的客戶信息。圖12 本季度沒有交易記錄的客戶信息vw_searchCustomerNoDeal7、實(shí)訓(xùn)七：存儲過程實(shí)現(xiàn)業(yè)務(wù)處理 使用T-SQL語句創(chuàng)建如下視圖，并保存為item7.sql文件(1) 完成存款或取款業(yè)務(wù) 描述： 根據(jù)銀行卡號和交易金額實(shí)現(xiàn)銀行卡的存款和取款業(yè)務(wù)。 每一筆存款，取款業(yè)務(wù)都要計(jì)入銀行交易賬，并同時(shí)更新客戶的存款余額。 如果是取款業(yè)務(wù)，在記賬之前，要完成下面兩項(xiàng)數(shù)據(jù)的檢查驗(yàn)證工作，如果檢查不合格，那么中斷取款業(yè)務(wù)，給出提示信息后退出。 檢查客戶輸入的密碼是否正確。 賬戶取款金額是否大于當(dāng)前存款額加1。要求： 取款或存款存儲過程名為usp_takeMoney。 編寫一個(gè)存儲過程完成存款和取款業(yè)務(wù)，并調(diào)用存儲過程取錢或者存錢進(jìn)行測試。 若是存取款過程成功，則結(jié)果窗口顯示如圖19的信息，包括卡號信息，以及當(dāng)天的該卡號交易信息。若是存取款不成功，則給出錯(cuò)誤提示信息。 結(jié)果如下圖所示 下述兩圖為存取款正確時(shí)的結(jié)果窗口： 圖13 執(zhí)行存儲過程的結(jié)果窗口（存取款）圖14 執(zhí)行存儲過程的輸出消息 提示： 鑒于存款時(shí)客戶不需要提供密碼，在編寫存儲過程中，為輸入?yún)?shù)“密碼”列設(shè)置默認(rèn)值為Null。 在存儲過程中使用事務(wù)，以保證數(shù)據(jù)操作的一致性。 use BankDB1 go -7-1 if exists(select * from sysobjects where id=OBJECT_ID(Nproc_TakeMoney) drop proc proc_TakeMoney go create proc proc_TakeMoney BCNo varchar(20),money money,pwd varchar(10)=null as -不返回受影響的行數(shù) set nocount on -聲明一個(gè)變量存放指定卡號的存款余額 declare existBanlance money -啟動(dòng)事務(wù)機(jī)制 begin tran select existBanlance=BCExistBalance from BankCard where BCNo=BCNo print(交易前，卡號+bcno+,余額為:+convert(varchar(20),existBanlance) print(交易正進(jìn)行，請稍后.) -如果輸入?yún)?shù)pwd為空，則為取款業(yè)務(wù)，否則為存款業(yè)務(wù) if(pwd is not null) -辦理取款業(yè)務(wù) begin -判斷指定卡號和密碼是否存在，若存在，則可以取款，否則失敗 if exists(select BCNo,BCPwd from BankCard where (BCNo=BCNo and BCPwd=pwd) begin if(select BCRegLoss from BankCard where BCNo=BCNo)=是) begin print 該卡已掛失,無法辦理相應(yīng)業(yè)務(wù) rollback end else begin -判斷取款金額是否小于等于余額，若條件成立，則可以取款，否則失敗 if(money<=existBanlance) begin insert into BankDealInfo values(BCNo,GETDATE(),money,取款,通過存儲過程) if(ERROR<>0) begin print 交易失敗 rollback tran end else begin commit tran print 交易成功,交易金額為:+convert(varchar(10),money)+,余額為：+convert(varchar(10),(existBanlance-money) end end else begin print 余額不足,取款失敗 rollback tran end end end else begin print 取款失敗,卡號或用戶名錯(cuò)誤 rollback tran end end else begin if(select BCRegLoss from BankCard where BCNo=BCNo)=是) begin print 該卡已掛失,無法辦理相應(yīng)業(yè)務(wù) rollback end else begin -辦理存款業(yè)務(wù) insert into BankDealInfo values(BCNo,GETDATE(),money,存款,通過存儲過程) -判斷事物處理是否有異常，沒有則提交事務(wù)，否則回滾 if(ERROR<>0) begin print 交易失敗 rollback tran end else begin commit tran print 交易成功,交易金額為:+convert(varchar(10),money) -判斷交易類型,顯示余額 print 卡號+BCNo+,余額為：+convert(varchar(10),existBanlance+money) end endendgo-存款exec proc_TakeMoney1010 3576 0001 8539,100-取款exec proc_TakeMoney1010 3576 0001 8539,100,888888-若用戶操作已掛失的卡exec proc_TakeMoney1010 3576 0000 7359,100go2.完成開戶業(yè)務(wù)描述： 利用存儲過程為客戶開設(shè)2個(gè)銀行卡賬戶，開戶時(shí)需要提供客戶的信息有：開戶名、身份證號、電話號碼、開戶金額、存款類型和地址、開戶網(wǎng)點(diǎn)?？蛻舻男畔⒁姳硭荆?為成功開戶的客戶提供銀行卡，且銀行卡號唯一。要求： 開戶的存儲過程名為usp_openAccount。 使用下面的數(shù)據(jù)執(zhí)行該存儲過程，進(jìn)行測試：調(diào)用此存儲過程開戶。表3 兩位客戶的開戶信息姓名身份證聯(lián)系電話開戶金額存款類型地址開戶網(wǎng)點(diǎn)周公旦150203197510074339130884488221200定活兩便內(nèi)蒙古包頭包頭樂園支行姬昌150203197610174339158345678091100活期內(nèi)蒙古包頭包鋼三中支行 結(jié)果如下圖所示：圖15 執(zhí)行開戶存儲過程的結(jié)果圖16 測試開戶存儲過程的輸出信息 use BankDB1 go if exists(select * from sysobjects where name=usp_openAccount) drop proc usp_openAccount go -創(chuàng)建開戶存儲過程usp_openAccount， -輸入?yún)?shù)分別是開戶名、身份證號、電話號碼、開戶金額、存款類型和地址 create proc usp_openAccount BCName varchar(12),BDBCNo varchar(20),BDTel varchar(12), OpenAccount money,BCtype varchar(6),address varchar(100) as declare BCBBTId int,BCNO varchar(19),BCId int,BCOpenDate date if (exists(select * from BankBusinessType WHERE BBTName=BCtype) begin set nocount on begin tran set BCId=(select count(*) from BankCustomer)+1 select BCBBTId=BBTId from BankBusinessType where BBTName=BCtype exec randCardId BCNo output while(exists(select * from BankCard where BCNo=BCNO) exec randCardId BCNo output insert into BankCustomer values(BCName,BDBCNo,BDTel,address) insert into BankCard values(BCNO,BCBBTId,GETDATE(),OpenAccount,否,BCId,OpenAccount,100001) -判斷事物操作是否有異常 if(ERROR<>0) begin print 尊敬的用戶,開戶失敗，所有操作均撤銷 rollback end else begin commit tran set BCOpenDate=(select BCOpenDate from BankCard where BCNo=BCNO) print 尊敬的客戶,開戶成功,系統(tǒng)為你產(chǎn)生的隨機(jī)卡號是+BCNo print 開戶日期:+convert(varchar(12),BCOpenDate)+開戶金額:+convert(varchar(10),OpenAccount) select * from BankCustomer where BCId=BCId select * from BankCard where BCNo=BCNO end end else print 尊敬的客戶，未能成功開戶，存款類型不正確，請重新輸入 go exec usp_openAccount李鑫,150202196210030491,13904721843,1000,活期,內(nèi)蒙古科技大學(xué)支行 測試失敗情況： exec usp_openAccount李鑫,150202196210030491,13904721843,1000,123,內(nèi)蒙古科技大學(xué)支行(4) 分頁顯示查詢交易數(shù)據(jù)根據(jù)指定的頁數(shù)和每頁的記錄數(shù)分頁顯示交易數(shù)據(jù)。要求： 存儲過程名稱是usp_PagingDisplay. 測試數(shù)據(jù)是輸出第2頁，每頁10行交易數(shù)據(jù)，結(jié)果如下圖所示：圖17 每頁10行方式輸出第2頁交易數(shù)據(jù) 8、實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬使用存儲過程和事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬業(yè)務(wù)，操作步驟如下所示：(1) 從某一個(gè)賬戶支取一定金額的存款。(2) 將支取金額存入到另一個(gè)指定的賬戶中。(3) 分別打印此筆業(yè)務(wù)的轉(zhuǎn)出賬單和轉(zhuǎn)入賬單。 要求：(1) 存儲過程名稱是usp_transfer。(2) 要求使用事務(wù)機(jī)制實(shí)現(xiàn)轉(zhuǎn)賬業(yè)務(wù)。(3) 結(jié)果如圖所示： use BankDB1 go if exists(select * from sysobjects where name=usp_transfer) drop proc usp_transfer go -創(chuàng)建存儲過程，傳遞轉(zhuǎn)出賬號、密碼，轉(zhuǎn)入賬號以及轉(zhuǎn)賬金額 create proc usp_transfer outzh varchar(20),inzh varchar(20),pwd varchar(8),dealAcount money as -不返回受影響的行數(shù) set nocount on -轉(zhuǎn)賬之后賬戶余額 declare outbalance money,inbalance money -轉(zhuǎn)出賬號的姓名，貨幣類型，存款類型，開戶日期 declare outname varchar(8),outCurr char(3),outType varchar(30),outOpenDate date -轉(zhuǎn)入賬號的姓名，貨幣類型，存款類型，開戶日期 declare inname varchar(8),inCurr char(3),inType varchar(30),inOpenDate date -判斷轉(zhuǎn)出賬戶是否存在 if(not exists(select BCNo from BankCard where BCNo=outzh) or (pwd<>(select BCPwd from BankCard where BCNo=outzh) begin print轉(zhuǎn)出賬戶不存在或者密碼錯(cuò)誤，轉(zhuǎn)賬失敗 return end else begin print開始轉(zhuǎn)賬，請稍后. -判斷轉(zhuǎn)出賬戶余額是否大于等于轉(zhuǎn)賬金額 if(exists(select * from BankCard where BCNo=outzh and BCExistBalance>=dealAcount) begin -判斷轉(zhuǎn)入賬戶是否存在 if(not exists(select BCNo from BankCard where BCNo=inzh) begin print轉(zhuǎn)入賬戶不存在，轉(zhuǎn)賬失敗 return end else begin begin tran print交易正在進(jìn)行，請稍后. -增加一條轉(zhuǎn)出交易記錄 insert into BankDealInfo values(outzh,GETDATE(),dealAcount,轉(zhuǎn)出,通過存儲過程) -增加一條轉(zhuǎn)入交易記錄 insert into BankDealInfo values(inzh,GETDATE(),dealAcount,轉(zhuǎn)入,通過存儲過程) -取得轉(zhuǎn)賬后兩個(gè)賬戶的余額 select outbalance=(select BCExistBalance from BankCard where BCNo=outzh) select inbalance=(select BCExistBalance from BankCard where BCNo=inzh) -判斷事務(wù)處理是否正常，有異常則回滾，無異常則提交 if(ERROR<>0) begin print轉(zhuǎn)賬失敗,正在進(jìn)行恢復(fù) rollback tran end else begin commit tran print交易成功,交易金額:+convert(varchar(10),dealAcount) end end end else begin print轉(zhuǎn)出賬戶余額不足，交易失敗 return end print打印轉(zhuǎn)出賬戶的相關(guān)信息 print- -獲取轉(zhuǎn)出賬戶的相關(guān)信息 select outname=BCName,outCurr=BCCurrency,outType=BBTName,outOpenDate=BCOpenDate from BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId where BCNo=outzh print卡號:+convert(varchar(20),outzh) print姓名:+outname print貨幣:+outCurr print存款類型:+outType print開戶日期:+convert(varchar(10),outOpenDate) print- print交易日 類型 交易金額 備注 print- print convert(varchar(15),getdate()+ +轉(zhuǎn)出+ +convert(varchar(10),dealAcount) +通過存儲過程 -打印轉(zhuǎn)入賬戶對賬單 print print打印轉(zhuǎn)入賬戶對賬單 print- -獲取轉(zhuǎn)入賬戶信息 select inname=BCName,inCurr=BCCurrency,inType=BBTName,inOpenDate=BCOpenDate from BankCard inner join BankCustomer on BankCard.BCBCId=BankCustomer.BCId inner join BankBusinessType on BankCard.BCBBTId=BankBusinessType.BBTId where BCNo=inzh print卡號:+convert(varchar(20),inzh) print姓名:+inname print貨幣:+inCurr print存款類型:+inType print開戶日期:+convert(varchar(10),inOpenDate) print- print交易日 類型 交易金額 備注 print- print convert(varchar(15),getdate()+ +轉(zhuǎn)入+ +convert(varchar(10),dealAcount) +通過存儲過程 end go 其余情況測試： 情況一：轉(zhuǎn)出賬戶不存在 exec usp_transfer0001 2949,1010 3576 0000 7359,888888,100 情況二：密碼錯(cuò)誤 exec usp_transfer1010 3576 0001 2949,1010 3576 0000 7359,788888,100 情況三：轉(zhuǎn)出賬戶余額不足 exec usp_transfer1010 3576 0001 2949,1010 3576 0000 7359,888888,10000 情況四：轉(zhuǎn)入賬戶不存在 exec usp_transfer1010 3576 0001 2949,11010 3576 0000 7359,888888,100三：心得體會通過這次數(shù)據(jù)庫課程設(shè)計(jì)對數(shù)據(jù)庫的基本知識有了更深的認(rèn)識，包括外鍵、check約束、觸發(fā)器、存儲過程、游標(biāo)、視圖等。尤其加深了對觸發(fā)器、存儲過程和游標(biāo)的理解。在本次課設(shè)中也遇到了一些問題，比如如何快速的插入20萬條交易數(shù)據(jù)，剛開始用的最笨的方法，就是通過while語句多次調(diào)用存儲過程已得到符合要求的卡號（保證其存在于bankcard表），所以速度很慢，大約接近1小時(shí)才能導(dǎo)入20W數(shù)據(jù)，后來想到了改進(jìn)的辦法，1分50S左右就可以導(dǎo)入20W條交易數(shù)據(jù) ，改進(jìn)方法是：創(chuàng)建bankcard臨時(shí)表，該表有兩個(gè)字段，自增長列ID和卡號BNCo,使用insert into #bankcard(BDBCNo) select BCNo from BankCard語句將bankcard表中的卡號復(fù)制到臨時(shí)表中，這就保證了每一個(gè)卡號都有自己唯一的序號，所以交易表的卡號就直接使用序列號來從臨時(shí)表中隨機(jī)取，這就保證了卡號肯定存在于bankcard表中，并且導(dǎo)入速度迅速提高（因?yàn)椴恍枰褂醚h(huán)語句去判斷）。最后，還是非常高興能通過本次課設(shè)提高了自己的知識理解程度，也讓我感覺到了數(shù)據(jù)庫知識的樂趣，今后會更加努力以提高自己的知識水平。