《數(shù)據(jù)庫技術(shù)與應(yīng)用課程設(shè)計(jì)報(bào)告.doc》由會(huì)員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫技術(shù)與應(yīng)用課程設(shè)計(jì)報(bào)告.doc（41頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

《數(shù)據(jù)庫技術(shù)與應(yīng)用》 課程設(shè)計(jì)報(bào)告 學(xué) 號(hào)：1467159124 姓 名：張喜泉 專 業(yè)：軟件工程 指導(dǎo)教師：康懿 完成日期：2016-12-24 目錄 《銀行ATM存取款機(jī)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)》 3 一、項(xiàng)目背景 3 1、項(xiàng)目任務(wù) 3 2、項(xiàng)目技能目標(biāo) 3 3、需求概述 3 4、開發(fā)環(huán)境 3 5、 問題分析 3 6、 實(shí)訓(xùn)進(jìn)度安排 5 二、項(xiàng)目實(shí)訓(xùn)內(nèi)容 5 制定《數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范》 5 1、 實(shí)訓(xùn)一：創(chuàng)建數(shù)據(jù)庫 5 2、 實(shí)訓(xùn)二：創(chuàng)建觸發(fā)器和隨機(jī)卡號(hào)的存儲(chǔ)過程(2學(xué)時(shí)) 9 3、 實(shí)訓(xùn)三：生成各個(gè)表的測試數(shù)據(jù)(4學(xué)時(shí)) 11 4、實(shí)訓(xùn)四：模擬常規(guī)業(yè)務(wù) 16 5、實(shí)訓(xùn)五：查詢統(tǒng)計(jì) 22 6、實(shí)訓(xùn)六：創(chuàng)建、使用視圖 25 7、實(shí)訓(xùn)七：存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理 28 35 8、實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬 35 三：心得體會(huì) 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)建視圖 使用存儲(chǔ)過程實(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ù)和存儲(chǔ)過程封裝業(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ù)、視圖和存儲(chǔ)過程，按照銀行的業(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í)需提前通知銀行，約定支取日期和金額方能支取的存款 整存整取 選擇存款期限，整筆存入，到期提取本息的一種定期儲(chǔ)蓄。銀行提供的存款期限有1年、2年和3年 零存整取 一種事先原定金額，逐月按約定金額存入，到期支取本息的定期儲(chǔ)蓄。銀行提供的存款期限由1年、2年和3年 自助轉(zhuǎn)賬 在ATM存取款機(jī)上辦理同一幣種賬戶的銀行卡之間互相劃轉(zhuǎn) (2) 客戶信息 每個(gè)客戶憑個(gè)人身份證在銀行可以開設(shè)多個(gè)銀行卡賬戶，開設(shè)賬戶時(shí)，客戶需要提供的開戶數(shù)據(jù)如表2所示： 表2． 開設(shè)銀行卡賬戶的客戶信息 數(shù)據(jù) 描述 姓名 必須提供 身份證號(hào) 唯一確定客戶，是由17位數(shù)字和1位數(shù)字或者字符X構(gòu)成。 聯(lián)系電話 手機(jī)號(hào)碼：由11位數(shù)字構(gòu)成，且前2位必須是13或者15、18開頭。 居住地址 可以選擇 (3) 開戶網(wǎng)點(diǎn)信息 表3． 開戶網(wǎng)點(diǎn)信息 數(shù)據(jù) 描述 網(wǎng)點(diǎn)編號(hào) 編號(hào)由6位數(shù)字構(gòu)成。 網(wǎng)點(diǎn)名稱 開戶行中文名稱 網(wǎng)點(diǎn)地址 開戶行所在地址信息。 (4) 銀行卡賬戶信息 銀行為每個(gè)賬戶提供一個(gè)銀行卡，每個(gè)銀行卡可以存入一種幣種的存款，銀行保存賬戶如表3所示： 表4． 銀行卡賬戶信息 數(shù)據(jù) 描述 卡號(hào) 銀行的卡號(hào)由16位數(shù)字組成，其中：一般前8位代表特俗含義，如代表某總行某支行等，假定該行要求其營業(yè)廳的卡號(hào)格式為1010 3576 XXXX XXXX，后8位必須是隨機(jī)產(chǎn)生且唯一，每4位號(hào)碼后有空格。 密碼 由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)編號(hào) 客戶網(wǎng)點(diǎn)編號(hào) 客戶持銀行卡在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ù) 描述 卡號(hào) 銀行的卡號(hào)由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ī)范 4 1份數(shù)據(jù)庫設(shè)計(jì)與編程規(guī)范 實(shí)訓(xùn)二：創(chuàng)建數(shù)據(jù)庫 4 T-SQL源文件 實(shí)訓(xùn)三：創(chuàng)建觸發(fā)器和插入測試數(shù)據(jù) 4 T-SQL源文件 實(shí)訓(xùn)五：模擬常規(guī)業(yè)務(wù) 4 T-SQL源文件 實(shí)訓(xùn)六：創(chuàng)建和使用視圖 4 T-SQL源文件 實(shí)訓(xùn)七：存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理 6 T-SQL源文件 實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬業(yè)務(wù) 6 T-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ù)類型表 序號(hào) 列名 數(shù)據(jù)類型 長度 小數(shù)位 標(biāo)識(shí) 主鍵 外鍵 允許空 默認(rèn)值 說明 1 BBTId int 4 0 是 是 否 銀行業(yè)務(wù)類型編號(hào)，自動(dòng)增長列 2 BBTName char 20 0 否 銀行業(yè)務(wù)類型名稱 3 BBTComment varchar 100 0 是 銀行業(yè)務(wù)描述 表名：BankCard銀行卡 序號(hào) 列名 數(shù)據(jù)類型 長度 小數(shù)位 標(biāo)識(shí) 主鍵 外鍵 允許空 默認(rèn)值 說明 1 BCNo char 19 0 是 否 卡號(hào) 2 BCPwd char 6 0 否 888888 密碼 3 BCCurrency char 5 0 否 RMB 幣種 4 BCBBTId int 4 0 是 否 業(yè)務(wù)類型 5 BCOpenDate date 3 0 否 getdate 開戶日期 6 BCOpenAmount money 8 4 否 開戶金額 7 BCRegLoss char 2 0 是 否 是否掛失 8 BCBCId int 4 0 否 客戶編號(hào) 9 BCExistBalance money 8 4 否 賬戶余額 10 BCBDID char 6 0 是 是 開戶行編號(hào) 表名：BankCustomer客戶信息 序號(hào) 列名 數(shù)據(jù)類型 長度 小數(shù)位 標(biāo)識(shí) 主鍵 外鍵 允許空 默認(rèn)值 說明 1 BCId int 4 0 是 是 否 客戶編號(hào) 2 BCName char 20 0 否 客戶名稱 3 BCICNo char 18 0 否 客戶身份證號(hào) 4 BCTel varchar 20 0 否 客戶電話號(hào) 5 BCAddr varchar 100 0 是 客戶地址 表名：BankDealInfo交易信息 序號(hào) 列名 數(shù)據(jù)類型 長度 小數(shù)位 標(biāo)識(shí) 主鍵 外鍵 允許空 默認(rèn)值 說明 1 BDNo int 4 0 是 是 否 交易編號(hào) 2 BDBCNo char 19 0 是 否 銀行卡號(hào) 3 BDDealDate date 3 0 否 getdate 交易日期 4 BDDealAcount money 8 4 否 交易金額 5 BDDealType char 10 0 否 交易類型 6 BDDealComment varchar 100 0 是 描述 表名：BankDesposit開戶網(wǎng)點(diǎn)信息 序號(hào) 列名 數(shù)據(jù)類型 長度 小數(shù)位 標(biāo)識(shí) 主鍵 外鍵 允許空 默認(rèn)值 說明 1 BDID char 6 0 是 否 網(wǎng)點(diǎn)編號(hào) 2 BDName char 20 0 否 網(wǎng)點(diǎn)名稱 3 BDAddress char 50 0 是 網(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ī)卡號(hào)的存儲(chǔ)過程(2學(xué)時(shí)) 使用T-SQL語句完成觸發(fā)器和隨機(jī)卡號(hào)存儲(chǔ)過程的創(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ī)卡號(hào)的存儲(chǔ)過程Proc_randCardID 創(chuàng)建存儲(chǔ)過程產(chǎn)生8位隨機(jī)數(shù)字，與前8位固定數(shù)字“1010 3576”連接，生成一個(gè)由16位數(shù)字組成的銀行卡號(hào)，并輸出。 要求： 產(chǎn)生隨機(jī)卡號(hào)的存儲(chǔ)過程名為Proc_randCardID。 利用下面的代碼調(diào)用存儲(chǔ)過程進(jìn)行測試 declare @myCardId1 char(19) exec proc_randCardId @myCardId1 output print 產(chǎn)生隨機(jī)卡號(hào)為+@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ù)bankcustomer BankCard表的測試數(shù)據(jù) 使用T-SQL語句向BankCard表中插入15000條數(shù)據(jù)，卡號(hào)調(diào)用存儲(chǔ)過程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) 修改存款類型為活期的銀行卡密碼 將存款類型為活期的銀行卡密碼改為身份證號(hào)后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è)存儲(chǔ)過程proc_staticsBanlanceAndProfit，執(zhí)行該存儲(chǔ)過程運(yùn)行結(jié)果如下圖所示： --執(zhí)行統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算的存儲(chǔ)過程 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． 本季度各銀行卡的交易信息：卡號(hào)，交易總額 查詢本季度交易總金額最高的銀行卡信息。 圖1． 本季度銀行卡交易額最高的銀行卡信息：卡號(hào)、開戶日期、開戶金額 (6) 查詢掛失客戶信息 查詢掛失賬號(hào)的客戶信息，利用子查詢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_searchCustomerNoDeal 7、實(shí)訓(xùn)七：存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理 使用T-SQL語句創(chuàng)建如下視圖，并保存為item7.sql文件 (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ǔ)過程名為usp_takeMoney。 編寫一個(gè)存儲(chǔ)過程完成存款和取款業(yè)務(wù)，并調(diào)用存儲(chǔ)過程取錢或者存錢進(jìn)行測試。 若是存取款過程成功，則結(jié)果窗口顯示如圖19的信息，包括卡號(hào)信息，以及當(dāng)天的該卡號(hào)交易信息。若是存取款不成功，則給出錯(cuò)誤提示信息。 結(jié)果如下圖所示 下述兩圖為存取款正確時(shí)的結(jié)果窗口： 圖13． 執(zhí)行存儲(chǔ)過程的結(jié)果窗口（存取款） 圖14． 執(zhí)行存儲(chǔ)過程的輸出消息 提示： 鑒于存款時(shí)客戶不需要提供密碼，在編寫存儲(chǔ)過程中，為輸入?yún)?shù)“密碼”列設(shè)置默認(rèn)值為Null。 在存儲(chǔ)過程中使用事務(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è)變量存放指定卡號(hào)的存款余額 declare @existBanlance money --啟動(dòng)事務(wù)機(jī)制 begin tran select @existBanlance=BCExistBalance from BankCard where BCNo=@BCNo print(交易前，卡號(hào)+@bcno+,余額為:+convert(varchar(20),@existBanlance)) print(交易正進(jìn)行，請稍后...) --如果輸入?yún)?shù)@pwd為空，則為取款業(yè)務(wù)，否則為存款業(yè)務(wù) if(@pwd is not null) --辦理取款業(yè)務(wù) begin --判斷指定卡號(hào)和密碼是否存在，若存在，則可以取款，否則失敗 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,取款,通過存儲(chǔ)過程) 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 取款失敗,卡號(hào)或用戶名錯(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,存款,通過存儲(chǔ)過程) --判斷事物處理是否有異常，沒有則提交事務(wù)，否則回滾 if(@@ERROR<>0) begin print 交易失敗 rollback tran end else begin commit tran print 交易成功,交易金額為:+convert(varchar(10),@money) --判斷交易類型,顯示余額 print 卡號(hào)+@BCNo+,余額為：+convert(varchar(10),@existBanlance+@money) end end end go --存款 exec proc_TakeMoney1010 3576 0001 8539,100 --取款 exec proc_TakeMoney1010 3576 0001 8539,100,888888 --若用戶操作已掛失的卡 exec proc_TakeMoney1010 3576 0000 7359,100 go 2.完成開戶業(yè)務(wù) 描述： 利用存儲(chǔ)過程為客戶開設(shè)2個(gè)銀行卡賬戶，開戶時(shí)需要提供客戶的信息有：開戶名、身份證號(hào)、電話號(hào)碼、開戶金額、存款類型和地址、開戶網(wǎng)點(diǎn)?？蛻舻男畔⒁姳硭荆? 為成功開戶的客戶提供銀行卡，且銀行卡號(hào)唯一。 要求： 開戶的存儲(chǔ)過程名為usp_openAccount。 使用下面的數(shù)據(jù)執(zhí)行該存儲(chǔ)過程，進(jìn)行測試：調(diào)用此存儲(chǔ)過程開戶。 表3． 兩位客戶的開戶信息 姓名 身份證 聯(lián)系電話 開戶金額 存款類型 地址 開戶網(wǎng)點(diǎn) 周公旦 150203197510074339 13088448822 1200 定活兩便 內(nèi)蒙古包頭 包頭樂園支行 姬昌 150203197610174339 15834567809 1100 活期 內(nèi)蒙古包頭 包鋼三中支行 結(jié)果如下圖所示： 圖15． 執(zhí)行開戶存儲(chǔ)過程的結(jié)果 圖16． 測試開戶存儲(chǔ)過程的輸出信息 use BankDB1 go if exists(select * from sysobjects where name=usp_openAccount) drop proc usp_openAccount go --創(chuàng)建開戶存儲(chǔ)過程usp_openAccount， --輸入?yún)?shù)分別是開戶名、身份證號(hào)、電話號(hào)碼、開戶金額、存款類型和地址 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ī)卡號(hào)是+@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ù)。 要求： 存儲(chǔ)過程名稱是usp_PagingDisplay. 測試數(shù)據(jù)是輸出第2頁，每頁10行交易數(shù)據(jù)，結(jié)果如下圖所示： 圖17． 每頁10行方式輸出第2頁交易數(shù)據(jù) 8、實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬 使用存儲(chǔ)過程和事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬業(yè)務(wù)，操作步驟如下所示： (1) 從某一個(gè)賬戶支取一定金額的存款。 (2) 將支取金額存入到另一個(gè)指定的賬戶中。 (3) 分別打印此筆業(yè)務(wù)的轉(zhuǎn)出賬單和轉(zhuǎn)入賬單。 要求： (1) 存儲(chǔ)過程名稱是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)建存儲(chǔ)過程，傳遞轉(zhuǎn)出賬號(hào)、密碼，轉(zhuǎn)入賬號(hào)以及轉(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)出賬號(hào)的姓名，貨幣類型，存款類型，開戶日期 declare @outname varchar(8),@outCurr char(3),@outType varchar(30),@outOpenDate date --轉(zhuǎn)入賬號(hào)的姓名，貨幣類型，存款類型，開戶日期 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)出,通過存儲(chǔ)過程) --增加一條轉(zhuǎn)入交易記錄 insert into BankDealInfo values(@inzh,GETDATE(),@dealAcount,轉(zhuǎn)入,通過存儲(chǔ)過程) --取得轉(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卡號(hào):+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) +通過存儲(chǔ)過程 --打印轉(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卡號(hào):+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) +通過存儲(chǔ)過程 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 三：心得體會(huì) 通過這次數(shù)據(jù)庫課程設(shè)計(jì)對數(shù)據(jù)庫的基本知識(shí)有了更深的認(rèn)識(shí)，包括外鍵、check約束、觸發(fā)器、存儲(chǔ)過程、游標(biāo)、視圖等。尤其加深了對觸發(fā)器、存儲(chǔ)過程和游標(biāo)的理解。在本次課設(shè)中也遇到了一些問題，比如如何快速的插入20萬條交易數(shù)據(jù)，剛開始用的最笨的方法，就是通過while語句多次調(diào)用存儲(chǔ)過程已得到符合要求的卡號(hà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和卡號(hào)BNCo,使用insert into #bankcard(BDBCNo) select BCNo from BankCard語句將bankcard表中的卡號(hào)復(fù)制到臨時(shí)表中，這就保證了每一個(gè)卡號(hào)都有自己唯一的序號(hào)，所以交易表的卡號(hào)就直接使用序列號(hào)來從臨時(shí)表中隨機(jī)取，這就保證了卡號(hào)肯定存在于bankcard表中，并且導(dǎo)入速度迅速提高（因?yàn)椴恍枰褂醚h(huán)語句去判斷）。 最后，還是非常高興能通過本次課設(shè)提高了自己的知識(shí)理解程度，也讓我感覺到了數(shù)據(jù)庫知識(shí)的樂趣，今后會(huì)更加努力以提高自己的知識(shí)水平。