《數(shù)據庫技術與應用課程設計報告.doc》由會員分享，可在線閱讀，更多相關《數(shù)據庫技術與應用課程設計報告.doc（41頁珍藏版）》請在裝配圖網上搜索。

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