數(shù)據(jù)庫技術(shù)與應用課程設(shè)計指導書

《數(shù)據(jù)庫技術(shù)與開發(fā)》課程設(shè)計指導書內(nèi)科大信息學院計算機系2014年2月15日實訓工程：《銀行ATM存取款機系統(tǒng)設(shè)計與實現(xiàn)》一、工程背景1、工程任務使用PowerDesigner完成數(shù)據(jù)庫設(shè)計創(chuàng)立數(shù)據(jù)庫、創(chuàng)立表、創(chuàng)立約束使用觸發(fā)器和插入測試數(shù)據(jù)模擬常規(guī)業(yè)務、創(chuàng)立視圖使用存儲過程實現(xiàn)業(yè)務處理利用事務實現(xiàn)較復雜的數(shù)據(jù)更新2、工程技能目標使用PowerDesigner完成數(shù)據(jù)庫概念模型和數(shù)據(jù)庫物理模型設(shè)計。使用T-SQL語句創(chuàng)立數(shù)據(jù)庫、表和各種約束。使用T-SQL語句編程實現(xiàn)常見業(yè)務。使用觸發(fā)器實現(xiàn)多表之間的級聯(lián)更新。使用事務和存儲過程封裝業(yè)務邏輯。使用視圖簡化復雜的數(shù)據(jù)查詢。使用游標技術(shù)實現(xiàn)結(jié)果集的行集操作。3、需求概述某銀行是一家民辦的小型銀行企業(yè)，現(xiàn)有十多萬客戶，公司將為該銀行開發(fā)一套ATM存取款機系統(tǒng)，對銀行日常的存取款業(yè)務進行計算機管理，以便保證數(shù)據(jù)的平安性，提高工作效率。要求根據(jù)銀行存取款業(yè)務需求設(shè)計出符合第三范式的數(shù)據(jù)庫結(jié)構(gòu)，使用T-SQL語言創(chuàng)立數(shù)據(jù)庫和表，并添加表約束，進行數(shù)據(jù)的增刪改查，運用邏輯結(jié)構(gòu)語句、事務、視圖和存儲過程，按照銀行的業(yè)務需求，實現(xiàn)各項銀行日常存款、取款和轉(zhuǎn)賬業(yè)務。4、開發(fā)環(huán)境數(shù)據(jù)庫：SQLSERVER2008開發(fā)版數(shù)據(jù)庫建模工具：PowerDesigner15問題分析該工程的ATM存取款機業(yè)務如下：(1)銀行存取款業(yè)務介紹銀行為客戶提供了各種銀行存取款業(yè)務。詳見表1銀行存取款業(yè)務業(yè)務描述活期無固定存期，可隨時存取，存取金額不限的一種比擬靈活的存款定活兩便事先不約定存期，一次性存入，一次性支取的存款通知不約定存期，支取時需提前通知銀行，約定支取日期和金額方能支取的存款整存整取選擇存款期限，整筆存入，到期提取本息的一種定期儲蓄。銀行提供的存款期限有1年、2年和3年零存整取一種事先原定金額，逐月按約定金額存入，到期支取本息的定期儲蓄。銀行提供的存款期限由1年、2年和3年自助轉(zhuǎn)賬在ATM存取款機上辦理同一幣種賬戶的銀行卡之間互相劃轉(zhuǎn)(2)客戶信息每個客戶憑個人身份證在銀行可以開設(shè)多個銀行卡賬戶，開設(shè)賬戶時，客戶需要提供的開戶數(shù)據(jù)如表2所示：開設(shè)銀行卡賬戶的客戶信息數(shù)據(jù)描述姓名必須提供身份證號唯一確定客戶，如果是二代身份證，那么是由17位數(shù)字和1位數(shù)字或者字符X構(gòu)成。如果是一代身份證，那么身份證號全部是15位數(shù)字。聯(lián)系分為座機號碼和號碼：座機號碼由數(shù)字和“-”構(gòu)成，由以下兩種格式：XXX-XXXXXXXX或者XXXX-XXXXXXX。號碼由11位數(shù)字構(gòu)成，且前2位必須是13或者15開頭。居住地址可以選擇(3)銀行卡賬戶信息銀行為每個賬戶提供一個銀行卡，每個銀行卡可以存入一種幣種的存款，銀行保存賬戶如表3所示：銀行卡賬戶信息數(shù)據(jù)描述卡號銀行的卡號由16位數(shù)字組成，其中：一般前8位代表特俗含義，如代表某總行某支行等，假定該行要求其營業(yè)廳的卡號格式為10103576XXXXXXXX，后8位必須是隨機產(chǎn)生且唯一，每4位號碼后有空格。密碼由6位數(shù)字構(gòu)成，開戶時默認為“888888”幣種默認為RMB，目前該銀行尚未開設(shè)其他幣種存款業(yè)務。存款類型必須選擇開戶日期客戶開設(shè)銀行卡賬戶的日期，默認為當日開戶金額客戶開設(shè)銀行卡賬戶時存入的金額，規(guī)定不得小于1元。是否掛失默認為“否”客戶持銀行卡在ATM機上輸入密碼，經(jīng)系統(tǒng)驗證身份后辦理存款、取款和轉(zhuǎn)賬等銀行業(yè)務。銀行規(guī)定，每個賬戶當前的存款金額不得小于1元。(4)銀行卡交易信息銀行在為客戶辦理業(yè)務時，需要記錄每一筆賬目，賬目交易信息如表4所示：銀行卡交易信息數(shù)據(jù)描述卡號銀行的卡號由16位數(shù)字組成交易日期默認為當日交易金額必須大于0元交易類型包括：存入和支取兩種備注對每筆交易做必要的說明銀行卡手工賬戶和存取款單據(jù)信息該銀行要求這套軟件實現(xiàn)銀行客戶的開戶、存款、取款、轉(zhuǎn)賬和余額查詢等業(yè)務，使得銀行儲蓄業(yè)務方便、快捷，同事保證銀行業(yè)務數(shù)據(jù)的平安性。為使開發(fā)人員盡快了解銀行業(yè)務，該銀行提供了銀行卡手工賬戶和存取款單據(jù)的樣本數(shù)據(jù)，以供工程開發(fā)時參考，參加表5和表6。銀行卡手工賬戶樣本數(shù)據(jù)存取款單據(jù)樣本數(shù)據(jù)實訓進度安排實訓進度安排如下表所示：實訓進度安排實訓內(nèi)容所需學時提交文檔實訓一：制定數(shù)據(jù)庫設(shè)計與編程標準41份數(shù)據(jù)庫設(shè)計與編程標準實訓二：數(shù)據(jù)庫分析設(shè)計與建模8數(shù)據(jù)庫分析設(shè)計文檔及數(shù)據(jù)庫概念模型、物理模型。實訓三：創(chuàng)立數(shù)據(jù)庫4T-SQL源文件實訓四：創(chuàng)立觸發(fā)器和插入測試數(shù)據(jù)4T-SQL源文件實訓五：模擬常規(guī)業(yè)務4T-SQL源文件實訓六：創(chuàng)立和使用視圖4T-SQL源文件實訓七：存儲過程實現(xiàn)業(yè)務處理6T-SQL源文件實訓八：利用事務實現(xiàn)轉(zhuǎn)賬業(yè)務6T-SQL源文件7、成績考核實訓工程最終成績由3局部構(gòu)成：平日考勤20%+實訓文檔30%+實訓工程完成質(zhì)量50%，但凡報優(yōu)的同學需參加公開辯論，講解辯論10-15分鐘。凡實訓工程雷同，或者考勤不合格直接視同不及格。二、工程實訓內(nèi)容實訓內(nèi)容由8個實訓工程構(gòu)成，建議在參考代碼和實現(xiàn)步驟根底上進行改良，每個實訓子工程的T-SQL語句寫成1個T-SQL源文件，如item1.sql。實訓一：制定《數(shù)據(jù)庫設(shè)計與編程標準》參考技術(shù)文檔：《數(shù)據(jù)庫設(shè)計標準(1)》、《數(shù)據(jù)庫設(shè)計標準(修訂)》、《數(shù)據(jù)庫設(shè)計及編寫標準》、《編程標準(T-SQL)》、《Transact-SQL_數(shù)據(jù)庫編程命名標準》、《SQL_Server數(shù)據(jù)庫編程標準》等技術(shù)文檔，制定一份10-15頁，不少于3000字的《數(shù)據(jù)庫設(shè)計與SQL編程標準》，要求至少包含各個數(shù)據(jù)庫對象的命名標準、編程標準及注釋標準。該實訓工程的設(shè)計與編程要求遵循該實訓制定的《數(shù)據(jù)庫設(shè)計與編程標準》。2、實訓二：數(shù)據(jù)庫分析設(shè)計與建模閱讀工程背景相關(guān)內(nèi)容，在8學時內(nèi)按下述推薦步驟完成下述實訓內(nèi)容：(1)分析銀行ATM存取款系統(tǒng)實體(2學時)根據(jù)問題分析，明確銀行ATM存取款系統(tǒng)的實體、實體屬性及實體之間的關(guān)系：在充分理解銀行業(yè)務需求后，圍繞銀行的需求進行分析，確認與銀行ATM存取款有緊密關(guān)系的實體，并得到每個實體的必要屬性。根據(jù)業(yè)務，分析多個實體之間的關(guān)系。實體之間的關(guān)系可以是一對一、一對多和多對多。要求使用Visio繪制出ATM存取款系統(tǒng)ER圖。參考資料：《ER設(shè)計》、《根本ER圖要點》(2)標準數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(2學時)使用數(shù)據(jù)庫設(shè)計范式對數(shù)據(jù)庫表結(jié)構(gòu)進行標準優(yōu)化，標準數(shù)據(jù)庫的表結(jié)構(gòu)同時，要考慮軟件運行性能。必要時，可以有悖于第三范式的要求，適當增加冗余數(shù)據(jù)、減少表間鏈接，以空間換取時間。要求以上述數(shù)據(jù)庫設(shè)計舉例說明3種數(shù)據(jù)庫設(shè)計范式的具體應用實例，每個設(shè)計范式至少舉例說明其中1個實例。如所謂第一范式〔1NF〕是指數(shù)據(jù)庫表的每一列都是不可分割的根本數(shù)據(jù)項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。如在銀行客戶表BankCustomer中，不能將客戶信息都放在一列中顯示，也不能將其中的兩列或多列在一列中顯示；客戶信息表的每一行只表示一個員工的信息，一個員工的信息在表中只出現(xiàn)一次。簡而言之，第一范式就是無重復的列。(3)安裝并演示PowerDesigner建模例如〔2學時〕參考《PowerDesigner系統(tǒng)分析與建?！罚惭bPowerDesigner及破解步驟、演示創(chuàng)立數(shù)據(jù)庫概念模型CDM、數(shù)據(jù)庫物理模型PDM和自動生成T-SQL語句等操作。(4)繪制CDM模型、生成PDM模型和生成T-SQL語句〔2學時〕使用PowerDesigner，把設(shè)計數(shù)據(jù)庫第一步結(jié)果〔即分析得到的銀行ATM存取款系統(tǒng)的實體、實體屬性、以及實體之間的關(guān)系〕用CDM表示，要求CDM要表達出各實體之間的關(guān)系以及各實體的實體屬性。創(chuàng)立如下列圖1所示的數(shù)據(jù)庫概念模型：銀行存取款系統(tǒng)數(shù)據(jù)概念模型CDM檢查所創(chuàng)立CDM模型無誤后，自動生成相應的CDM設(shè)計文檔和銀行存取款系統(tǒng)數(shù)據(jù)物理模型PDM。參考PDM模型如圖2所示：銀行存取款系統(tǒng)數(shù)據(jù)物理模型PDM3、實訓三：創(chuàng)立數(shù)據(jù)庫使用T-SQL語句完成數(shù)據(jù)庫、數(shù)據(jù)表和各種約束及觸發(fā)器的創(chuàng)立，并保存為item2.sql文件。按下述推薦步驟，在4學時內(nèi)完成下述實訓內(nèi)容：(1)創(chuàng)立數(shù)據(jù)庫〔1學時〕使用CreateDataBase語句創(chuàng)立“ATM存取款機系統(tǒng)”數(shù)據(jù)庫BankDB，數(shù)據(jù)文件和日志文件保存在指定目錄下〔建議建立一個文件夾，用于存放該實訓工程的所有相關(guān)T-SQL源文件〕，文件增長率為15%。參考代碼如下所示：--創(chuàng)立BankDB數(shù)據(jù)庫，數(shù)據(jù)庫文件和日志文件均保存在--文件夾G:\2014年工程案例工程\銀行ATM存取款機系統(tǒng)下--文件增長率均為%，數(shù)據(jù)文件起始大小為MB，日志文件起始大小為MBcreatedatabaseBankDBonprimary( name=N'BankDB', filename=N'G:\2014年工程案例工程\銀行ATM存取款機系統(tǒng)\BankDB.mdf',size=5MB,filegrowth=15%)logon( name=N'BankDB_log', filename=N'G:\2014年工程案例工程\銀行ATM存取款機系統(tǒng)\BankDB_log.ldf',size=2MB,filegrowth=15%)(2)創(chuàng)立各個數(shù)據(jù)表及相關(guān)的約束〔2學時〕根據(jù)實訓1設(shè)計出的“銀行ATM存取款機系統(tǒng)”PDM模型的數(shù)據(jù)表結(jié)構(gòu)，使用CreateTable語句創(chuàng)立表結(jié)構(gòu)。根據(jù)銀行業(yè)務，分析表中每個列相應的約束要求，為每個表添加各種約束。要求創(chuàng)立表時要求檢測是否存在表結(jié)構(gòu)，如果存在，那么先刪除再創(chuàng)立。建議選擇2張表的T-SQL語句進行修改，先創(chuàng)立表結(jié)構(gòu)里的各個字段，再用AlterTable語句為每個表添加各種約束。參考代碼如下所示：useBankDB;go--判斷銀行業(yè)務類型表是否存在,假設(shè)存在那么刪除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankBusinessType'))droptableBankBusinessType--創(chuàng)立銀行業(yè)務類型表，包含銀行業(yè)務類型編號BBTId，銀行業(yè)務類型名稱BBTName，銀行業(yè)務描述BBTCommentcreatetableBankBusinessType( BBTIdintidentity(1,1)primarykey, BBTNamechar(20)notnull, BBTCommentvarchar(100));go--判斷銀行卡客戶是否存在,假設(shè)存在那么刪除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankCustomer'))droptableBankCustomer--創(chuàng)立銀行客戶信息表，包含客戶編號BCID，客戶姓名BCName，客戶身份證BCICNo，客戶聯(lián)系BCTel、客戶居住地址BCAddrcreatetableBankCustomer( BCIdintidentity(1,1)primarykey, BCNamechar(20)notnull, --定義身份證號前位必須是數(shù)字，后位可以是數(shù)字或者X。 BCICNochar(18)notnull check(left(BCICNo,17)like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' and(right(BCICNo,1)like'[0-9]'orright(BCICNo,1)like'X')), --定義聯(lián)系方式，必須是固定號碼或者號，且前位必須是，第位必須是或或 BCTelvarchar(20)notnull check(BCTellike'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or BCTellike'[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' orBCTellike'1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --定義客戶地址 BCAddrvarchar(100));go--判斷銀行卡是否存在，假設(shè)存在，那么刪除銀行卡BankCardifexists(select*fromsysobjectswhereid=object_id(N'BankCard'))droptableBankCard--建立銀行卡信息createtableBankCard( --卡號必須符合位數(shù)字構(gòu)成,前位為3576,后位是隨機產(chǎn)生且唯一,每位必須有一個空格 BCNochar(19)primarykeycheck(BCNolike'10103576[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), --密碼,開戶默認為 BCPwdchar(6)notnulldefault('888888'), --幣種,為RMB類型 BCCurrencychar(5)notnulldefault('RMB'), --存款類型 BCBBTIdintnotnull, --開戶日期,默認當日 BCOpenDatedatenotnulldefault(getdate()), --開戶金額,不得小于元 BCOpenAmountmoneynotnullcheck(BCOpenAmount>=1), --是否掛失,默認為否 BCRegLosschar(2)default('否'), --客戶編號 BCBCIdintnotnull, BCExistBalancemoneynotnull);--判斷交易信息BankDealInfo是否存在,假設(shè)存在那么刪除ifexists(select*fromsysobjectswhereid=OBJECT_ID(N'BankDealInfo'))droptableBankDealInfo--創(chuàng)立交易信息表createtableBankDealInfo( --交易編號為自動增長列 BDNointidentity(1,1)primarykey, --卡號 BDBCNochar(19)notnull, --交易日期 BDDealDateDatenotnulldefault(getdate()), --交易金額 BDDealAcountmoneynotnull, --交易類型，有種存入和支取 BDDealTypeChar(10)notnullcheck(BDDealType='存入'orBDDealType='支取'), --交易備注 BDDealCommentvarchar(100));(3)添加外鍵約束和生成數(shù)據(jù)庫關(guān)系圖〔1學時〕添加子表外鍵約束及生成數(shù)據(jù)庫關(guān)系圖添加外鍵約束參考代碼如下所示：--建立表之間的外鍵約束關(guān)系altertablebankcardaddconstraintfk_BC_BBTforeignkey(BCBBTId)referencesbankbusinesstype(bbtid);altertablebankcardaddconstraintfk_BC_BCforeignkey(BCBCId)referencesBankCustomer(BCId);altertablebankDealInfoaddconstraintfk_BDI_BCforeignkey(BDBCNo)referencesBankCard(BCNo);在SQLSERVER里自動生成數(shù)據(jù)庫關(guān)系圖，如下列圖所示：BankDb數(shù)據(jù)庫關(guān)系圖4、實訓四：創(chuàng)立觸發(fā)器和插入測試數(shù)據(jù)(1)創(chuàng)立級聯(lián)觸發(fā)器〔2學時〕創(chuàng)立Insert觸發(fā)器在交易信息表BankDealInfo中創(chuàng)立一個Insert觸發(fā)器，當增加一條交易信息時，修改相應銀行卡的存款余額。建議使用游標，實現(xiàn)批量增加的級聯(lián)更新。--在交易信息表中插入一個觸發(fā)器，使用游標當新增一個交易信息，修改銀行卡的存款余額if(object_id('tr_InsertdealInfo','tr')isnotnull)droptriggertr_InsertdealInfogocreatetriggertr_InsertdealInfoonbankdealinfoforinsertasdeclare@typechar(10),@summoney,@BDBCNochar(19);--創(chuàng)立一個游標，指向inserted表declarecursor_BankDealinfocursorforselectBDDealType,BDDealAcount,BDBCNofrominserted--翻開游標opencursor_BankDealinfo--取游標中各個字段的值復制給各個變量fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNowhile@@fetch_status=0begin --判斷交易記錄里是存入還是支取，及時更新銀行卡表的存款余額 if(rtrim(ltrim(@type))='存入') updatebankcardsetBCExistBalance=BCExistBalance+@sumwhereBCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') updatebankcardsetBCExistBalance=BCExistBalance-@sumwhereBCNo=@BDBCNo; fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNoendclosecursor_BankDealinfodeallocatecursor_BankDealinfogo創(chuàng)立Delete觸發(fā)器在交易信息表創(chuàng)立一個Delete觸發(fā)器，當刪除一條交易信息時，修改相應銀行卡的存款余額。建議使用游標，實現(xiàn)批量刪除的級聯(lián)更新。--在交易信息表中插入一個觸發(fā)器，使用游標,當刪除一個交易信息，修改銀行卡的存款余額if(object_id('tr_DeldealInfo','tr')isnotnull)droptriggertr_DeldealInfogocreatetriggertr_DeldealInfoonbankdealinfofordeleteasdeclare@typechar(10),@summoney,@BDBCNochar(19);--創(chuàng)立一個游標，指向deleted表declarecursor_BankDealinfocursorforselectBDDealType,BDDealAcount,BDBCNofromdeleted--翻開游標opencursor_BankDealinfo--取游標中各個字段的值復制給各個變量fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNowhile@@fetch_status=0begin if(rtrim(ltrim(@type))='存入') updatebankcardsetBCExistBalance=BCExistBalance-@sumwhereBCNo=@BDBCNo; if(rtrim(ltrim(@type))='支取') updatebankcardsetBCExistBalance=BCExistBalance+@sumwhereBCNo=@BDBCNo; fetchnextfromcursor_BankDealinfointo@type,@sum,@BDBCNoendclosecursor_BankDealinfodeallocatecursor_BankDealinfogo創(chuàng)立Update觸發(fā)器參照上述代碼，創(chuàng)立一個Update觸發(fā)器，當更新交易信息表的記錄時，更新銀行卡表的相應卡號的余額。(2)插入數(shù)據(jù)表的測試數(shù)據(jù)〔2學時〕使用T-SQL語句向每個表插入如下所示測試數(shù)據(jù)，要保證業(yè)務數(shù)據(jù)的一致性和完整性。BankBusinessType表的測試數(shù)據(jù)使用T-SQL向已經(jīng)創(chuàng)立的BankBusinessType表插入表7數(shù)據(jù)：BankBusinessType表測試數(shù)據(jù)BankCustomer表的測試數(shù)據(jù)使用T-SQL語句向BankCustomer表中插入表8的數(shù)據(jù)：BankCustomer表測試數(shù)據(jù)BankCard表的測試數(shù)據(jù)使用T-SQL語句向BankCard表中插入表9的數(shù)據(jù)，要求開戶日期設(shè)置為當前日期近一個月的隨機某一天〔使用相應的日期函數(shù)和隨機函數(shù)完成〕。擴展題：如何保證某卡號對應的交易日期必須晚于該卡的開戶日期，建議增加相應的級聯(lián)觸發(fā)器。BankCard表測試數(shù)據(jù)BankDealInfo表的測試數(shù)據(jù)使用T-SQL語句向BankDealInfo表中插入表10的數(shù)據(jù)，要求交易日期設(shè)置為當前日期近半個月的隨機某一天〔使用相應的日期函數(shù)和隨機函數(shù)完成〕。通過檢查相應賬戶余額，仔細檢查上述建立的2個級聯(lián)觸發(fā)器是否發(fā)揮作用？BankDealInfo表測試數(shù)據(jù)5、實訓五：模擬常規(guī)業(yè)務使用T-SQL語句實現(xiàn)銀行的日常業(yè)務，并保存為item4.sql文件。按下述推薦步驟，在4學時內(nèi)完成下述實訓內(nèi)容：(1)修改客戶密碼根據(jù)卡號修改指定2個客戶的銀行密碼，其中第一個客戶1010357612345678密碼修改為123456，第二個客戶1010357612345688修改為123123，并使用Select查詢語句顯示如下效果圖：修改客戶密碼效果圖辦理銀行卡掛失卡號為1010357612345678的銀行卡喪失，申請掛失，要求使用innerjoin語句顯示如下列圖運行結(jié)果：辦理銀行卡掛失效果圖統(tǒng)計銀行資金流通余額和盈利結(jié)算存入代表資金流入，支取代表資金流出。計算公式：資金流通余額=總存入金額-總支取金額假定存款利率為千分之三，貸款利率為千分之八。計算公式：盈利結(jié)算=總支取金額*0.008-總存入金額*0.003。要求創(chuàng)立一個存儲過程proc_staticsBanlanceAndProfit，執(zhí)行該存儲過程運行結(jié)果如下列圖所示：統(tǒng)計銀行資金流通余額和盈利結(jié)算參考代碼如下所示：ifexists(select*fromsysobjectswherename='proc_staticsBanlanceAndProfit')......;gocreateprocedureproc_staticsBanlanceAndProfitasdeclare@InMoneymoney......--獲取總存入金額和總支取金額select@InMoney=SUM(BDDealAcount)fromBankDealInfowherertrim(ltrim(BDDealType))='存入'......print'存入總金額:'+rtrim(ltrim(str(@InMoney)))+'RMB,支取總金額:'......go查詢本周開戶信息查詢本周開戶的卡號，顯示該卡的相關(guān)信息。結(jié)果如下列圖所示：本周開戶信息提示：求時間差使用日期函數(shù)DateDiff()，求星期幾使用日期函數(shù)DatePart()，賬戶狀態(tài)列使用Case語句轉(zhuǎn)換。查詢本月單次交易金額最高的卡號和總交易金額最高的卡號查詢本月存、取款中單次交易金額最高的卡號信息。結(jié)果如下列圖所示：本月交易金額最高的卡號提示：要求使用子查詢和distinct去掉重復的卡號,使用DateDiff()和DataPart()函數(shù)得到本月內(nèi)的交易信息。查詢掛失客戶查詢掛失賬號的客戶信息，分別利用子查詢in的方式或者內(nèi)部連接innerjoin，查詢結(jié)果如下列圖所示：查詢掛失客戶催款提醒業(yè)務根據(jù)某種業(yè)務〔如代繳費、代繳費或房貸等〕的需要，每個月末，查詢出客戶賬戶上余額少于2000元，由銀行統(tǒng)一致電催款。查詢結(jié)果如下列圖所示：催款提醒業(yè)務利用子查詢或者內(nèi)部連接innerjoin均可，參考代碼如下所示：select......fromBankCardAinnerjoinBankCustomerBWhere......6、實訓六：創(chuàng)立、使用視圖使用T-SQL語句創(chuàng)立如下視圖，并保存為item5.sql文件。按下述推薦步驟，在4學時內(nèi)完成下述實訓內(nèi)容：為向客戶提供友好的用戶界面，使用T-SQL語句創(chuàng)立下面幾個視圖，并使用這些視圖輸出各表信息。輸出銀行客戶記錄視圖VW_userInfo顯示的列名全為中文，要求先判斷該視圖是否存在，假設(shè)存在，那么先刪除。結(jié)果如下列圖所示：輸出銀行客戶記錄輸出銀行卡記錄視圖VW_CardInfo建議使用內(nèi)部連接InnerJoin語句，結(jié)果如下列圖所示：輸出銀行卡記錄輸出銀行卡交易記錄視圖VW_TransInfo查詢該視圖，結(jié)果如下列圖所示：輸出銀行卡的交易記錄根據(jù)客戶登錄名查詢該客戶賬戶信息VW_OneUserInfo根據(jù)客戶登錄名〔采用實名制訪問銀行系統(tǒng)〕查詢該客戶賬戶信息的視圖，利用SQLSERVER系統(tǒng)函數(shù)system_user獲得數(shù)據(jù)庫用戶名。根據(jù)登錄名查詢賬戶信息7、實訓七：存儲過程實現(xiàn)業(yè)務處理(1)完成存款或取款業(yè)務描述：根據(jù)銀行卡號和交易金額實現(xiàn)銀行卡的存款和取款業(yè)務。每一筆存款，取款業(yè)務都要計入銀行交易賬，并同時更新客戶的存款余額。如果是取款業(yè)務，在記賬之前，要完成下面兩項數(shù)據(jù)的檢查驗證工作，如果檢查不合格，那么中斷取款業(yè)務，給出提示信息后退出。檢查客戶輸入的密碼是否正確。賬戶取款金額是否大于當前存款額加1。要求：取款或存款存儲過程名為usp_takeMoney。編寫一個存儲過程完成存款和取款業(yè)務，并調(diào)用存儲過程取錢或者存錢進行測試，測試數(shù)據(jù)是張飛的卡號支取100元〔密碼123456〕，關(guān)羽的卡號存入2100元。結(jié)果如下列圖所示下述兩圖為張飛卡號存入2100元的運行結(jié)果：執(zhí)行存儲過程的結(jié)果〔存款〕執(zhí)行存儲過程的輸出消息〔存款〕下述兩圖為關(guān)羽卡號取款100元的執(zhí)行結(jié)果：執(zhí)行存儲過程結(jié)果〔取款〕執(zhí)行存儲過程輸出消息〔取款〕提示：鑒于存款時客戶不需要提供密碼，在編寫存儲過程中，為輸入?yún)?shù)“密碼”列設(shè)置默認值為Null。在存儲過程中使用事務，以保證數(shù)據(jù)操作的一致性。測試時，可以根據(jù)客戶姓名查出張三和李四的卡號。存儲過程參考代碼如下所示：--判斷該存儲過程是否存在，假設(shè)存在，那么刪除ifexists(select*fromsysobjectswherename='proc_TakeMoney') ......;go--創(chuàng)立存取款業(yè)務的存儲過程createprocedureproc_TakeMoney @bcnochar(19),@moneymoney,@pwdchar(6)=nullas --不返回受影響的行數(shù) setnocounton --聲明一變量存放指定卡號的存款余額 declare@existBanlancemoney --啟動事務機制 begintran select@existBanlance=BCExistBalancefromBankCardwhereBCNo=@bcno print'交易前，卡號'+@bcno+'，余額為：'+ltrim(str(@existBanlance)) print'交易正進行，請稍后...' --如果輸入?yún)?shù)@pwd為空，那么為取款業(yè)務，否那么為存款業(yè)務 if(@pwdisnotnull) --辦理取款業(yè)務 begin --判斷指定卡號和密碼是否存在，假設(shè)存在，那么可以取款，否那么不能辦理取款業(yè)務 ifexists(......) begin --判斷取款金額是否小于等于存款余額-1，假設(shè)條件成立，那么可以取款，否那么不能取款 if(@money<=@ExistBanlance-1) insertintoBankDealInfo...... else print'取款交易失敗，余額缺乏，請減少取款' end else print'取款交易失敗，密碼有錯誤' end else --辦理存款業(yè)務 insertintoBankDealInfo...... --判斷事務處理里是否有異常，假設(shè)沒有異常，那么提交，假設(shè)有異常，那么回滾 if(@@ERROR<>0) begin print'交易失敗' rollbacktran end else begin committran print'交易成功，交易金額為：'+ltrim(str(@money)) --判斷該交易為何種類型業(yè)務，假設(shè)是存款，那么現(xiàn)有余額等于原有余額加上存款金額 ...... end --顯示銀行卡用戶詳情和交易詳情 ......go測試代碼如下所示：--執(zhí)行存款存儲過程execproc_TakeMoney'1010357612345678',2100--執(zhí)行取款存儲過程execproc_TakeMoney'1010357612345678',100,'123456'產(chǎn)生隨機卡號創(chuàng)立存儲過程產(chǎn)生8位隨機數(shù)字，與前8位固定數(shù)字“10103576”連接，生成一個由16位數(shù)字組成的銀行卡號，并輸出。要求：產(chǎn)生隨機卡號的存儲過程名為Proc_randCardID。利用下面的代碼調(diào)用存儲過程進行測試declare@myCardId1char(19)execproc_randCardId@myCardId1outputprint'產(chǎn)生隨機卡號為'+@myCardId1結(jié)果如下圖：測試產(chǎn)生隨機卡號提示：使用隨機函數(shù)生成銀行卡后8位的數(shù)字，隨機函數(shù)的用法：rand(隨機種子)將產(chǎn)生0~1的隨機數(shù)，要求每次的隨機種子不一樣。為了保證隨機種子每次都不相同，一般采用的算法是：隨機種子=當前的月份數(shù)*100000+當前的秒數(shù)*1000+當前的毫秒數(shù)產(chǎn)生了0~1的隨機數(shù)后，取小數(shù)點后8位，即：0.XXXXXXXX。關(guān)鍵代碼如下所示：declare@rnumeric(15,8),@randCardIdchar(19),@tmpstrchar(10)--產(chǎn)生隨機種子=當前的月份數(shù)*100000+當前的秒數(shù)*1000+當前的毫秒數(shù)select@r=RAND(DATEPART(mm,getdate())*100000+DATEPART(SS,getdate())*1000+DATEPART(ms,getdate()))set@tmpstr=convert(char(10),@r)set@randCardId=......+SUBSTRING(@tmpstr,3,4)+......完成開戶業(yè)務描述：利用存儲過程為客戶開設(shè)2個銀行卡賬戶，開戶時需要提供客戶的信息有：開戶名、身份證號、號碼、開戶金額、存款類型和地址。客戶的信息見表所示：為成功開戶的客戶提供銀行卡，且銀行卡號唯一。要求：開戶的存儲過程名為usp_openAccount。使用下面的數(shù)據(jù)執(zhí)行該存儲過程，進行測試：調(diào)用此存儲過程開戶。兩位客戶的開戶信息姓名身份證聯(lián)系開戶金額存款類型地址周公旦0472_24578901200定活兩便內(nèi)蒙古包頭姬昌0472_24578901100活期內(nèi)蒙古包頭結(jié)果如下列圖所示：執(zhí)行開戶存儲過程的結(jié)果測試開戶存儲過程的輸出信息提示：調(diào)用上述產(chǎn)生隨機卡號的存儲過程獲得生成的隨機卡號，檢查改隨機卡號在現(xiàn)有的銀行卡中是否存在。如果不存在，那么往相關(guān)表中插入開戶信息；否那么將調(diào)用上述隨機卡號的存儲過程，重新生成隨機卡號，直至產(chǎn)生一個不存在的銀行卡號為止。參考代碼如下useBankDBgoifexists(select*fromsysobjectswherename='usp_openAccount') dropprocusp_openAccountgo--創(chuàng)立開戶存儲過程usp_openAccount，輸入?yún)?shù)分別是開戶名、身份證號、號碼、開戶金額、存款類型和地址createprocusp_openAccount......asdeclare@BCBBTIdint,@BCNochar(19),@BCIdint --先判斷存款類型是否正確 ifexists(......) begin begintran select@BCBBTId=BBTIdfromBankBusinessTypewhereBBTName=@BBTName execproc_randCardId@BCNooutput --判斷是否卡號重復，假設(shè)重復，那么繼續(xù)循環(huán)，否那么退出循環(huán) while(exists(......)) execproc_randCardId@BCNooutput --插入一條客戶信息記錄 insert...... --得到剛插入的客戶信息的編號 ...... --插入一條新開銀行卡記錄 insertintoBankCard...... --判斷上述事務操作是否有異常 if(@@ERROR<>0) Begin print'尊敬的客戶，開戶不成功，所有操作均撤銷' rollbacktran end else begin committran print'尊敬的客戶，開戶成功，系統(tǒng)為你產(chǎn)生的隨機卡號是'+...... end --顯示開戶的客戶信息和銀行卡信息 ...... end else print'尊敬的客戶，未能成功開戶，存款類型不正確，請重新輸入'go分頁顯示查詢交易數(shù)據(jù)根據(jù)指定的頁數(shù)和每頁的記錄數(shù)分頁顯示交易數(shù)據(jù)。要求：存儲過程名稱是usp_PagingDisplay.測試數(shù)據(jù)是輸出第2頁，每頁5行交易數(shù)據(jù)，結(jié)果如下列圖所示：每頁5行方式輸出第2頁交易數(shù)據(jù)擴展要求創(chuàng)立一個usp_PagingDisplay1，調(diào)用系統(tǒng)存儲過程SP_EXECUTESQL，執(zhí)行動態(tài)生成的T-SQL語句。代碼提示：createprocedureusp_PagingDisplay ......as select...... from (selectROW_NUMBER()over(orderbyBDNo)asRowNumber,......)C where(C.RowNumberbetween.......)打印客戶對賬單為某個特定的銀行卡號打印指定時間內(nèi)發(fā)生交易的對賬單。要求如下：存儲過程名稱是usp_CheckSheet。分別采用以下兩種方式執(zhí)行存儲過程，結(jié)果如下列圖所示。如果不指定交易時間段，那么打印指定卡號的所有交易記錄，如測試命令：execusp_CheckSheet'1010357612345688'如果指定交易時間段，那么打印指定卡號在指定時間內(nèi)發(fā)生的所有交易記錄，如測試命令：execusp_CheckSheet'1010357612345678','2014-1-1','2014-1-15'按卡號打印對賬單代碼提示：useBankDBgo--判斷客戶對賬單存儲過程是否存在，假設(shè)是存在，那么刪除ifexists(select*fromsysobjectswherename='usp_CheckSheet') dropprocedureusp_CheckSheetgo--創(chuàng)立客戶對賬單的存儲過程createprocedureusp_CheckSheet@bcnochar(19),@startDatedatetime=null,@endDatedatetime=nullas--聲明各個變量分別存放姓名、貨幣類型、存款類型、開戶日期declare@bcNamechar(20),@bcCurrencychar(5),@bbtNamechar(20),@bcOpenDatedate--聲明各個變量分別存放交易日期、交易類型、交易金額、備注declare@bddealdatedatetime,@bddealtypechar(10),@bddealacountmoney, @bddealcommentvarchar(100)select@bcName=B.BCName,@bcCurrency=A.BCCurrency, @bbtName=C.BBTName,@bcOpenDate=A.BCOpenDate fromBankCardAinnerjoinBankCustomerB onA.BCBCId=B.BCId innerjoinBankBusinessTypeC onA.BCBBTId=C.BBTId whereA.BCNo=@bcnoprint'卡號:'+@bcnoprint'姓名:'+@bcNameprint'貨幣:'+@bcCurrencyprint'存款類型:'+@bbtNameprint'開戶日期:'+convert(char(12),@bcOpenDate,111)--創(chuàng)立一個游標，指向指定的交易記錄if(@startDateisnull) declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoelse begin if(@endDateisnull) declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoandbddealdate>@startdate else declarecur_BankDealinfocursor forselectbddealdate,bddealtype,bddealacount,bddealcomment fromBankDealInfo wherebdbcno=@bcnoandbddealdate>@startdateandbddealdate<@enddate end--翻開游標opencur_BankDealinfoprintconvert(char(12),'交易日')+convert(char(10),'類型')+ convert(char(10),'交易金額')+convert(char(100),'備注')--從游標里取出相應字段的值到各個變量里fetchnextfromcur_BankDealinfointo@bddealdate,@bddealtype,@bddealacount,@bddealcommentwhile(@@FETCH_STATUS=0) begin printconvert(char(12),@bddealdate,111)+convert(char(10),@bddealtype)+ convert(char(10),@bddealacount)+convert(char(100),@bddealcomment) fetchnextfromcur_BankDealinfointo@bddealdate,@bddealtype,@bddealacount,@bddealcomment endclosecur_BankDealinfodeallocatecur_BankDealinfogo統(tǒng)計未發(fā)生交易的賬戶查詢統(tǒng)計指定時間段內(nèi)沒有發(fā)生交易的賬戶信息。要求：存儲過程名稱是usp_getWithoutTrade。指定時間段如果沒有指定起始日期，那么自本月1日開始進行統(tǒng)計，如果沒有指定終止日期，那么截止到當日為止。要求采用游標技術(shù)打印未發(fā)生交易的客戶信息，參考客戶對賬單的代碼。該存儲過程的執(zhí)行結(jié)果如下列圖所示統(tǒng)計未發(fā)生交易的賬戶統(tǒng)計銀行卡交易量和交易額統(tǒng)計指定時間段內(nèi)某地區(qū)客戶在銀行卡交易量和交易額，如果不指定地區(qū)，那么查詢所有客戶的交易量和交易額。要求：存儲過程名稱是usp_getTradeInfo。指定時間段和客戶所在區(qū)域如果沒有指定起始日期，那么自當年1月1日開始統(tǒng)計，如果沒有指定終止日期，那么以當日作為截止日。如果沒有指定地點〔根據(jù)客戶所在地址查詢〕，如北京，那么統(tǒng)計全部客戶的交易量和交易額。執(zhí)行下述存儲過程語句：execusp_getTradeInfoexecusp_getTradeInfo'2014-1-18','2014-2-20'execusp_getTradeInfo'2014-1-18','2014-1-20','包鋼'結(jié)果如下列圖所示：統(tǒng)計銀行卡交易量和交易額8、實訓八：利用事務實現(xiàn)轉(zhuǎn)賬使用存儲過程和事務實現(xiàn)轉(zhuǎn)賬業(yè)務，操作步驟如下所示：從某一個賬戶支取一定金額的存款。將支取金額存入到另一個指定的賬戶中。分別打印此筆業(yè)務的轉(zhuǎn)出賬單和轉(zhuǎn)入賬單。要求：存儲過程名稱是usp_transfer。要求使用事務機制實現(xiàn)轉(zhuǎn)賬業(yè)務。結(jié)果如下圖：實現(xiàn)轉(zhuǎn)賬業(yè)務關(guān)鍵代碼如下所示：useBankDBgo--判斷該存儲過程是否存在，假設(shè)存在，那么刪除ifexists(select*fromsysobjectswherename='usp_transfer') dropprocedureusp_transfergo--創(chuàng)立轉(zhuǎn)賬存儲過程，需要傳遞兩個賬戶號碼及轉(zhuǎn)賬金額createprocedureusp_transfer@outbcnochar(19),@inbcnochar(19),@dealAcountmoneyas--不返回受影響的行數(shù)setnocounton--聲明個變量分別存放轉(zhuǎn)出賬戶和轉(zhuǎn)入賬戶的轉(zhuǎn)賬之后的余額declare@outexistbalancemoney,@inexistbalancemoney--聲明變量存放轉(zhuǎn)出賬戶的姓名、貨幣類型、存款類型和開戶日期declare@outbcnamechar(20),@outBcCurrencychar(5),@outBBTNamechar(20),@outBcOpenDatedate--聲明變量存放轉(zhuǎn)入賬戶的姓名、貨幣類型、存款類型和開戶日期declare@inbcnamechar(20),@inBcCurrencychar(5),@inBBTNamechar(20),@inBcOpenDatedate print'開始轉(zhuǎn)賬，請稍后...' --判斷轉(zhuǎn)出賬戶及余額是否大于轉(zhuǎn)出金額+1 ifexists(select*fromBankCardwhereBCNo=@outbcnoan