數(shù)據(jù)庫課程設(shè)計(jì)匯本~銀行ATM存取款機(jī)系統(tǒng)設(shè)計(jì)分析實(shí)現(xiàn)

...wd......wd......wd...給學(xué)弟學(xué)妹的福利,數(shù)據(jù)庫這個(gè)難度還算可以吧.也不是很大.大家多多思考一下.代碼僅供參考.最好不要復(fù)制.手敲一遍也是好的.《數(shù)據(jù)庫技術(shù)與開發(fā)》工程實(shí)訓(xùn)設(shè)計(jì)報(bào)告工程名稱：銀行ATM存取款機(jī)系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)姓名：譚海兵 專業(yè)：軟件工程指導(dǎo)教師：余金林完成日期：2015-11-22TOC\o"1-3"\h\u19123一、工程背景3305371、工程任務(wù)3263882、工程技能目標(biāo)343463、需求概述3229494、開發(fā)環(huán)境3220155、問題分析48586二、工程實(shí)訓(xùn)內(nèi)容6292011、實(shí)訓(xùn)一：制定《數(shù)據(jù)庫設(shè)計(jì)與編程標(biāo)準(zhǔn)》6184892、實(shí)訓(xùn)二：數(shù)據(jù)庫分析設(shè)計(jì)與建模1228113、實(shí)訓(xùn)三：創(chuàng)立數(shù)據(jù)庫14239894、實(shí)訓(xùn)四：創(chuàng)立觸發(fā)器和插入測試數(shù)據(jù)1952425、實(shí)訓(xùn)五：模擬常規(guī)業(yè)務(wù)2690916、實(shí)訓(xùn)六：創(chuàng)立、使用視圖30235017、實(shí)訓(xùn)七：存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理33175218、實(shí)訓(xùn)八：利用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬49207208、心得體會(huì)54一、工程背景1、工程任務(wù)使用PowerDesigner完成數(shù)據(jù)庫設(shè)計(jì)創(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、工程技能目標(biāo)使用PowerDesigner完成數(shù)據(jù)庫概念模型和數(shù)據(jù)庫物理模型設(shè)計(jì)。使用T-SQL語句創(chuàng)立數(shù)據(jù)庫、表和各種約束。使用T-SQL語句編程實(shí)現(xiàn)常見業(yè)務(wù)。使用觸發(fā)器實(shí)現(xiàn)多表之間的級(jí)聯(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)，對(duì)銀行日常的存取款業(yè)務(wù)進(jìn)展計(jì)算機(jī)管理，以便保證數(shù)據(jù)的安全性，提高工作效率。要求根據(jù)銀行存取款業(yè)務(wù)需求設(shè)計(jì)出符合第三范式的數(shù)據(jù)庫構(gòu)造，使用T-SQL語言創(chuàng)立數(shù)據(jù)庫和表，并添加表約束，進(jìn)展數(shù)據(jù)的增刪改查，運(yùn)用邏輯構(gòu)造語句、事務(wù)、視圖和存儲(chǔ)過程，按照銀行的業(yè)務(wù)需求，實(shí)現(xiàn)各項(xiàng)銀行日常存款、取款和轉(zhuǎn)賬業(yè)務(wù)。4、開發(fā)環(huán)境數(shù)據(jù)庫：SQLSERVER2008開發(fā)版數(shù)據(jù)庫建模工具：PowerDesigner15問題分析該工程的ATM存取款機(jī)業(yè)務(wù)如下：(1)銀行存取款業(yè)務(wù)介紹銀行為客戶提供了各種銀行存取款業(yè)務(wù)。詳見表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所示：開設(shè)銀行卡賬戶的客戶信息數(shù)據(jù)描述姓名必須提供身份證號(hào)唯一確定客戶，如果是二代身份證，則是由17位數(shù)字和1位數(shù)字或者字符X構(gòu)成。如果是一代身份證，則身份證號(hào)全部是15位數(shù)字。聯(lián)系分為座機(jī)號(hào)碼和手機(jī)號(hào)碼：座機(jī)號(hào)碼由數(shù)字和“-〞構(gòu)成，由以下兩種格式：XXX-XXXXXXXX或者XXXX-XXXXXXX。手機(jī)號(hào)碼由11位數(shù)字構(gòu)成，且前2位必須是13或者15開頭。居住地址可以選擇(3)銀行卡賬戶信息銀行為每個(gè)賬戶提供一個(gè)銀行卡，每個(gè)銀行卡可以存入一種幣種的存款，銀行保存賬戶如表3所示：銀行卡賬戶信息數(shù)據(jù)描述卡號(hào)銀行的卡號(hào)由16位數(shù)字組成，其中：一般前8位代表特俗含義，如代表某總行某支行等，假定該行要求其營業(yè)廳的卡號(hào)格式為10103576XXXXXXXX，后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)為“否〞客戶持銀行卡在ATM機(jī)上輸入密碼，經(jīng)系統(tǒng)驗(yàn)證身份后辦理存款、取款和轉(zhuǎn)賬等銀行業(yè)務(wù)。銀行規(guī)定，每個(gè)賬戶當(dāng)前的存款金額不得小于1元。(4)銀行卡交易信息銀行在為客戶辦理業(yè)務(wù)時(shí)，需要記錄每一筆賬目，賬目交易信息如表4所示：銀行卡交易信息數(shù)據(jù)描述卡號(hào)銀行的卡號(hào)由16位數(shù)字組成交易日期默認(rèn)為當(dāng)日交易金額必須大于0元交易類型包括：存入和支取兩種備注對(duì)每筆交易做必要的說明銀行卡手工賬戶和存取款單據(jù)信息該銀行要求這套軟件實(shí)現(xiàn)銀行客戶的開戶、存款、取款、轉(zhuǎn)賬和余額查詢等業(yè)務(wù)，使得銀行儲(chǔ)蓄業(yè)務(wù)方便、快捷，同事保證銀行業(yè)務(wù)數(shù)據(jù)的安全性。為使開發(fā)人員盡快了解銀行業(yè)務(wù)，該銀行提供了銀行卡手工賬戶和存取款單據(jù)的樣本數(shù)據(jù)，以供工程開發(fā)時(shí)參考，參加表5和表6。銀行卡手工賬戶樣本數(shù)據(jù)存取款單據(jù)樣本數(shù)據(jù)二、工程實(shí)訓(xùn)內(nèi)容實(shí)訓(xùn)一：制定《數(shù)據(jù)庫設(shè)計(jì)與編程標(biāo)準(zhǔn)》長度標(biāo)準(zhǔn)但凡需要命名的對(duì)象其標(biāo)識(shí)符均控制在30個(gè)字符以內(nèi)，也即：SQLServer中的表名、字段名、函數(shù)名、存儲(chǔ)過程、觸發(fā)器、視圖等名字長度要盡量不超過30個(gè)字符長度。構(gòu)成標(biāo)準(zhǔn)數(shù)據(jù)庫各種名稱必須以字母開頭，但嚴(yán)禁以系統(tǒng)關(guān)鍵字開頭，名稱只能含有字母、數(shù)字以及下劃線“_〞三類字符，“_〞用于間隔名稱中的各語義字段；不要使用系統(tǒng)保存字作表名。大小寫標(biāo)準(zhǔn)構(gòu)成SQLServer數(shù)據(jù)庫中的各種名稱(表名、字段名、過程名、視圖名等所有命名符的首字母需要使用大寫，也即每個(gè)命名單詞的首字母大寫，其它字符小寫。但對(duì)于簡寫或縮寫的短單詞，如ID、UI可以全為大寫。主鍵標(biāo)準(zhǔn)除臨時(shí)表、流水表以及日志表外，其它表都要建設(shè)主鍵。主鍵最好設(shè)計(jì)成單一主鍵，盡量不要用復(fù)合主鍵，盡量使用沒有業(yè)務(wù)語義的字段作為主鍵，如采用按順序自增的數(shù)值型字段為主鍵注釋標(biāo)準(zhǔn)每個(gè)表，每個(gè)字段都要有注釋，說明其含義，對(duì)于冗余字段還要特別說明其維護(hù)方法，外鍵字段要說明參照于那個(gè)表，另外對(duì)于存儲(chǔ)過程、視圖、觸發(fā)器、函數(shù)等代碼均要增加注釋，以保持代碼的可讀性以及后續(xù)的可維護(hù)性。行大小標(biāo)準(zhǔn)SQLServer的1頁的大小是8K，因此一行的數(shù)據(jù)要控制到8K之內(nèi)，如果超過8K要想方法將表進(jìn)展拆分成多個(gè)子表。數(shù)據(jù)保存策略大表由于數(shù)據(jù)量較大，往往是系統(tǒng)的性能瓶頸所在，因此對(duì)于大表的設(shè)計(jì)好考慮到今后的數(shù)據(jù)轉(zhuǎn)移、分片、Partition等，并且對(duì)大表以及其相關(guān)表的數(shù)據(jù)保存時(shí)間也要有一個(gè)提前規(guī)劃，以免今后出現(xiàn)性能問題束手無策。必備字段要求每個(gè)大表都應(yīng)該添加以下幾個(gè)有用的字段，分別為創(chuàng)立日期、修改日期、操作人以及版本標(biāo)記，創(chuàng)立這些字段的目的是為今后的數(shù)據(jù)轉(zhuǎn)移以及分片或分區(qū)做準(zhǔn)備，同時(shí)也有利于今后的數(shù)據(jù)審計(jì)等。本卷須知基于大表的任何操作都要慎重思考，通常情況下要制止在大表上創(chuàng)立觸發(fā)器，制止在大表上做頻繁的批量更新或刪除動(dòng)作，制止在生產(chǎn)時(shí)間對(duì)大表做DDL操作，制止在大表上做全表掃描(FullScan)等。臨時(shí)表功能SQLServer分為全局臨時(shí)表和局部臨時(shí)表，臨時(shí)表在很多場合下能帶來意想不到的效果，尤其是需要中轉(zhuǎn)的數(shù)據(jù)記錄集采用臨時(shí)表能提升系統(tǒng)性能。臨時(shí)表支持索引、約束、排序等實(shí)體表具有的功能。存儲(chǔ)特點(diǎn)臨時(shí)表的數(shù)據(jù)存儲(chǔ)在tempdb數(shù)據(jù)庫中，因此過于頻繁的創(chuàng)立臨時(shí)表會(huì)增加tempdb庫的負(fù)荷，尤其是數(shù)據(jù)量超過10W條記錄的臨時(shí)表更是會(huì)影響tempdb庫的性能，由此在某些情況下可用CTE替代臨時(shí)表的使用。本卷須知臨時(shí)表執(zhí)行完畢后，要及時(shí)的手工Drop掉，及時(shí)釋放資源，減輕系統(tǒng)的Loading，另外特別注意的是要盡量制止使用全局臨時(shí)表，全局適合多個(gè)session間的數(shù)據(jù)交互，但往往會(huì)引起數(shù)據(jù)的串值。命名標(biāo)準(zhǔn)盡量采用有意義的字段名，使描述盡可能清楚，如采用縮寫，盡量使用通用的縮寫語言，如addr代表address，防止出現(xiàn)只有自己理解的縮寫。日期字段時(shí)效性數(shù)據(jù)應(yīng)包括“創(chuàng)立時(shí)間/修改時(shí)間〞字段，時(shí)間標(biāo)記對(duì)查找數(shù)據(jù)、清理數(shù)據(jù)、排序合并特別有用，另外要根據(jù)具體業(yè)務(wù)考量時(shí)間字段的類型，如在Smalldatetime和Datetime類型進(jìn)展選擇。注意保存詞對(duì)于字段的命名，要確保字段名沒有和保存詞、數(shù)據(jù)庫系統(tǒng)或者常用方法沖突，比方master、CROSS、address、substring、len、sysobjects等詞就不適合用來做字段的獨(dú)立命名。數(shù)值標(biāo)準(zhǔn)數(shù)值型的主要有INT、BIGINT、TINYINT、SMALLINT、FLOAT、NUMERIC、MONEY等類型，要根據(jù)實(shí)際應(yīng)用選擇適宜的類型，如字段的數(shù)據(jù)為小于255的整形數(shù)字，那么就要選擇TINYINT；如字段數(shù)據(jù)小于32767的整形數(shù)字，那么就要選擇SMALLINT，以此類推。文本標(biāo)準(zhǔn)文本類型主要有CHAR、VARCHAR、TEXT等類型，要根據(jù)實(shí)際應(yīng)用選擇適宜的類型，如字段文本長度固定為8位，那么就要用CHAR(8)；如文本長度最大為100，并且大小是非定長的，那么就要設(shè)為VARCHAR(100)。并且以上文本假設(shè)為漢字，那么就要設(shè)為NVARCHAR和NCHAR。字段命名技巧字段命名要統(tǒng)一標(biāo)準(zhǔn)，同一個(gè)字段在不同的表中命名要一致，另外字段名一般都要帶上業(yè)務(wù)模塊的前綴，如訂單(Order)價(jià)格字段命名為Or_Price,部門(Department)編號(hào)為Dep_No。命名標(biāo)準(zhǔn)沒有那個(gè)是最合理的，只有定義好最適合自己的統(tǒng)一標(biāo)準(zhǔn)即可。外鍵建設(shè)索引外鍵不建設(shè)索引，將有可能導(dǎo)致兩個(gè)嚴(yán)重的問題。1.更新相關(guān)的表產(chǎn)生死鎖。2.兩表關(guān)聯(lián)查詢時(shí)性能低下。因此通常情況下都必須要求外鍵建設(shè)索引。聯(lián)合索引標(biāo)準(zhǔn)當(dāng)數(shù)據(jù)對(duì)某表經(jīng)常要多條件查詢時(shí)，可能就需要建設(shè)聯(lián)合索引，聯(lián)合索引的第一個(gè)引導(dǎo)列字段非常重要，引導(dǎo)列字段通常要能過濾掉大局部數(shù)據(jù)，這樣方能減少IO的讀寫，提高性能。非引導(dǎo)列字段在引導(dǎo)列的查詢數(shù)據(jù)根基上繼續(xù)過濾數(shù)據(jù)，以提高查詢速度。聯(lián)合索引對(duì)更新會(huì)產(chǎn)生一定的性能影響。禁用多余索引數(shù)據(jù)庫索引能提高查詢速度，但會(huì)增加寫操作的開銷，因此對(duì)一些幾月或者從沒有使用過的索引要?jiǎng)h除掉，以免增大數(shù)據(jù)庫的負(fù)荷。重復(fù)索引問題一般情況下，盡量防止重復(fù)索引的出現(xiàn)，重復(fù)索引很容易引起死鎖，減低數(shù)據(jù)庫的并發(fā)訪問。重復(fù)索引也會(huì)造成索引的維護(hù)困難。索引數(shù)量限制數(shù)據(jù)庫索引主要用來解決讀的性能瓶頸，但是會(huì)增加寫操作的負(fù)荷，因此過多的索引會(huì)造成更新速度變慢，甚至?xí)鸩灰乃梨i。一般情況下表中的索引不要超過5個(gè)。本卷須知建設(shè)索引前，要充分了解表的使用及數(shù)據(jù)特性，要了解表的查詢條件和查詢頻率，甚至隨著業(yè)務(wù)的變化而引起表數(shù)據(jù)使用狀況的變化，帶之而來的是索引也需要相應(yīng)調(diào)整。命名標(biāo)準(zhǔn)存儲(chǔ)過程命名遵守統(tǒng)一的標(biāo)準(zhǔn)，對(duì)于業(yè)務(wù)存儲(chǔ)過程要以p或proc開頭，接著加上〞_〞，然后再加上模塊名稱簡寫和具體的業(yè)務(wù)詞，最后加上執(zhí)行類型。數(shù)據(jù)庫的存儲(chǔ)過程名嚴(yán)禁以sp開頭，sp通常表示系統(tǒng)數(shù)據(jù)庫存儲(chǔ)過程名的前綴。觸發(fā)器以Tr開頭，接著加上〞_〞，然后加上表名。書寫標(biāo)準(zhǔn)關(guān)鍵字建議用大寫，同樣的代碼書寫格式保持一致，SQL腳本采用縮進(jìn)風(fēng)格，風(fēng)格一致，縮進(jìn)格式一致，使用空格。INSERT標(biāo)準(zhǔn)通常情況下，INSERT語句要給出具體的字段列表，防止采用“INSERTINTOTB_1VALUES(‘值1’,’值2’,’值3’)〞用法，此種用法往往會(huì)由于表構(gòu)造變遷而導(dǎo)致語句不可執(zhí)行防止隱式轉(zhuǎn)換書寫時(shí)，必須明確表構(gòu)造及表中各個(gè)字段的數(shù)據(jù)類型，特別是查詢條件中的字段，要防止由于類型的不同導(dǎo)致數(shù)據(jù)類型轉(zhuǎn)換的發(fā)生，從而減少因?yàn)閿?shù)據(jù)類型轉(zhuǎn)換產(chǎn)生的系統(tǒng)開銷。NULL陷阱NULL不要直接用來進(jìn)展運(yùn)算符的比擬，也不要和其它值進(jìn)展連接操作，判斷一個(gè)值是否為NULL值時(shí)，要采用ISNULL來進(jìn)展比擬。LIKE標(biāo)準(zhǔn)LIKE子句應(yīng)盡量前段匹配，要防止通配符在前段，以免導(dǎo)致全索引掃描的發(fā)生。參數(shù)化代碼SQL中常量的直接使用，會(huì)導(dǎo)致SQL語句頻繁的硬解析，進(jìn)而嚴(yán)重影響數(shù)據(jù)庫的性能，基于這些原因，代碼中要盡量采用參數(shù)綁定，以減少語句硬解析的次數(shù)，從而提高語句執(zhí)行性能。動(dòng)態(tài)SQL動(dòng)態(tài)SQL是在運(yùn)行時(shí)才進(jìn)展解析的，相當(dāng)于是硬解析，因此會(huì)損失一些系統(tǒng)性能，但是動(dòng)態(tài)SQL寫法靈活，因此在某些情況下需要以性能換靈活，但對(duì)于用靜態(tài)語句就能簡單實(shí)現(xiàn)的SQL，就不要用動(dòng)態(tài)SQL語句。嵌套層級(jí)限制嵌套查詢盡量少使用，尤其是對(duì)于超過3層的嵌套查詢更要慎用，對(duì)于復(fù)雜的嵌套語句要根據(jù)業(yè)務(wù)進(jìn)展拆分為多條SQL來實(shí)現(xiàn)，或者通過臨時(shí)表來取代一局部嵌套層級(jí)。排序標(biāo)準(zhǔn)SQL語句中要盡量減少排序，對(duì)查詢結(jié)果進(jìn)展的排序會(huì)大大降低系統(tǒng)的性能，并且會(huì)增加tempdb數(shù)據(jù)庫的負(fù)荷，因此在開發(fā)時(shí)間寬松情況下，要盡量將排序動(dòng)作放到應(yīng)用程序?qū)尤ネ瓿?。代碼注釋要求注釋是指程序中會(huì)被編譯器忽略掉的局部，目的是描述代碼的用途及更新時(shí)間，合理的添加注釋可以使得程序構(gòu)造清晰，可以使代碼更好理解，便于系統(tǒng)后續(xù)的維護(hù)。一般情況下，注釋要不少于代碼的十分之一。靜態(tài)SQLSQL語句要盡可能采用靜態(tài)SQL，靜態(tài)SQL第一次執(zhí)行時(shí)會(huì)將編譯器解析的結(jié)果存儲(chǔ)在緩存中，下次執(zhí)行該靜態(tài)SQL時(shí)會(huì)直接從緩存中獲取其執(zhí)行方案，相當(dāng)于是軟解析，因此采用靜態(tài)SQL可以減少語句的解析時(shí)間，提升了數(shù)據(jù)庫的性能。最小事務(wù)原則數(shù)據(jù)庫事務(wù)用來保持?jǐn)?shù)據(jù)的一致性，但是對(duì)于一個(gè)執(zhí)行時(shí)間較長的大事務(wù)，會(huì)造成數(shù)據(jù)庫鎖的增加，當(dāng)鎖越積越多的時(shí)候就會(huì)從行鎖升級(jí)到頁鎖，從業(yè)鎖升級(jí)到表鎖，從而嚴(yán)重影響數(shù)據(jù)庫的性能。因此，在能滿足數(shù)據(jù)一致性的前提下，要盡量將非一致性要求的語句代碼從事務(wù)中移除，以便提升數(shù)據(jù)庫的并發(fā)訪問。順序提交順序提交是一個(gè)好的代碼編寫習(xí)慣，順序提交可以減少死鎖的發(fā)生，并且還能增加代碼的可讀性及可維護(hù)性。2、實(shí)訓(xùn)二：數(shù)據(jù)庫分析設(shè)計(jì)與建模(1)分析銀行ATM存取款系統(tǒng)實(shí)體銀行存取款系統(tǒng)ER圖標(biāo)準(zhǔn)數(shù)據(jù)庫構(gòu)造設(shè)計(jì)第一范式〔1NF〕是指數(shù)據(jù)庫表的每一列都是不可分割的根本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值，即實(shí)體中的某個(gè)屬性不能有多個(gè)值或者不能有重復(fù)的屬性。如在銀行客戶表BankCustomer中，不能將客戶信息都放在一列中顯示，也不能將其中的兩列或多列在一列中顯示；客戶信息表的每一行只表示一個(gè)員工的信息，一個(gè)客戶的信息在表中只出現(xiàn)一次。簡而言之，第一范式就是無重復(fù)的列。第二范式〔2NF〕要求實(shí)體的屬性完全依賴于主關(guān)鍵字。如銀行交易信息表BankDealInfo中，不能把卡號(hào)設(shè)為主鍵因?yàn)橐粋€(gè)卡號(hào)可以發(fā)生多條交易記錄。要確定唯一的一條信息，必須重新定義一個(gè)和其它屬性無關(guān)的交易編號(hào)。這樣要查詢一條交易信息。就可以用交易編號(hào)。簡而言之，第二范式就是屬性完全依賴于主鍵。第三范式〔3NF〕要求一個(gè)數(shù)據(jù)庫表中不包含已在其它表中已包含的非主關(guān)鍵字信息。如銀行卡表BankCard中，有了用戶Id后。不能還添加用戶姓名等相關(guān)的用戶信息。否則就會(huì)有大量的數(shù)據(jù)冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。(3)繪制CDM模型、生成PDM模型銀行存取款系統(tǒng)數(shù)據(jù)概念模型CDM銀行存取款系統(tǒng)數(shù)據(jù)物理模型PDM3、實(shí)訓(xùn)三：創(chuàng)立數(shù)據(jù)庫(1)創(chuàng)立數(shù)據(jù)庫使用CreateDataBase語句創(chuàng)立“ATM存取款機(jī)系統(tǒng)〞數(shù)據(jù)庫BankDB，數(shù)據(jù)文件和日志文件保存在指定目錄下文件增長率為15%。--創(chuàng)立BankDB數(shù)據(jù)庫，數(shù)據(jù)庫文件和日志文件均保存在--文件夾d:\sql下--文件增長率均為%，數(shù)據(jù)文件起始大小為MB，日志文件起始大小為MBCREATEDATABASEBankDBONPRIMARY(NAME=N'BankDB',FILENAME=N'd:\sql\BankDB.mdf',SIZE=5MB,FILEGROWTH=15%)LOGON(NAME=N'BankDB_log',FILENAME=N'd:\sql\BankDB_log.ldf',SIZE=2MB,FILEGROWTH=15%)(2)創(chuàng)立各個(gè)數(shù)據(jù)表及相關(guān)的約束根據(jù)銀行業(yè)務(wù)，分析表中每個(gè)列相應(yīng)的約束要求，為每個(gè)表添加各種約束。要求創(chuàng)立表時(shí)要求檢測是否存在表構(gòu)造，如果存在，則先刪除再創(chuàng)立。USEBankDBGO--判斷銀行業(yè)務(wù)類型是否存在,假設(shè)存在則刪除--sysobjects系統(tǒng)對(duì)象表。保存當(dāng)前數(shù)據(jù)庫的對(duì)象。--OBJECT_ID()根據(jù)對(duì)象名稱返回該對(duì)象的idIFEXISTS(SELECT*FROMsysobjectsWHEREid=OBJECT_ID(N'BankBusinessType'))DROPTABLEBankBusinessType--創(chuàng)立銀行業(yè)務(wù)類型表 CREATETABLEBankBusinessType(--銀行業(yè)務(wù)類型編號(hào)BBTIdINTIDENTITY(1,1)PRIMARYKEY,--銀行業(yè)務(wù)類型名稱BBTNameCHAR(20)NOTNULL,--銀行業(yè)務(wù)描述BBTCommentVARCHAR(100))GO--判斷銀行卡客戶是否存在,假設(shè)存在則刪除IFEXISTS(SELECT*FROMsysobjectsWHEREid=OBJECT_ID(N'BankCustomer'))DROPTABLEBankCustomer--創(chuàng)立銀行客戶信息表CREATETABLEBankCustomer(--客戶編號(hào)BCIdINTIDENTITY(1,1)PRIMARYKEY,--客戶姓名BCNameCHAR(20)NOTNULL,--客戶身份證,定義身份證號(hào)前位必須是數(shù)字，后位可以是數(shù)字或者X。BCICNoCHAR(18)NOTNULLCHECK(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)系,必須是固定號(hào)碼或者手機(jī)號(hào)BCTelVARCHAR(20)NOTNULLCHECK(BCTellike'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'orBCTellike'[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--建設(shè)銀行卡信息CREATETABLEBankCard(--銀行卡號(hào)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'),--幣種BCCurrencyCHAR(5)NOTNULLDEFAULT('RMB'),--存款類型BCBBTIdINTNOTNULL,--開戶日期BCOpenDateDATENOTNULLDEFAULT(GETDATE()),--開戶金額BCOpenAmountMONEYNOTNULLCHECK(BCOpenAmount>=1),--是否掛失BCRegLossCHAR(2)DEFAULT('否'),--客戶編號(hào)BCBCIdINTNOTNULL,--余額BCExistBalanceMONEYNOTNULL)GO--判斷交易信息BankDealInfo是否存在,假設(shè)存在則刪除IFEXISTS(SELECT*FROMsysobjectsWHEREid=OBJECT_ID(N'BankDealInfo'))DROPTABLEBankDealInfo--創(chuàng)立交易信息表CREATETABLEBankDealInfo(--交易編號(hào)BDNoINTIDENTITY(1,1)PRIMARYKEY,--卡號(hào)BDBCNoCHAR(19)NOTNULL,--交易日期BDDealDateDATENOTNULL,--交易金額BDDealAcountMONEYNOTNULL,--交易類型BDDealTypeCHAR(10)NOTNULLCHECK(BDDealType='存入'ORBDDealType='支取'),--交易備注BDDealCommentvarchar(100))(3)添加外鍵約束和生成數(shù)據(jù)庫關(guān)系圖添加子表外鍵約束及生成數(shù)據(jù)庫關(guān)系圖ALTERTABLEBankCardADDCONSTRAINTFK_BC_BBTFOREIGNKEY(BCBBTId)REFERENCESBankBusinessType(BBTId)ALTERTABLEBankCardADDCONSTRAINTFK_BC_BCFOREIGNKEY(BCBCId)REFERENCESBankCustomer(BCId)ALTERTABLEBankDealInfoADDCONSTRAINTFK_BDI_BCFOREIGNKEY(BDBCNo)REFERENCESBankCard(BCNo)在SQLSERVER里自動(dòng)生成數(shù)據(jù)庫關(guān)系圖，如以下列圖所示：BankDb數(shù)據(jù)庫關(guān)系圖4、實(shí)訓(xùn)四：創(chuàng)立觸發(fā)器和插入測試數(shù)據(jù)(1)創(chuàng)立級(jí)聯(lián)觸發(fā)器創(chuàng)立Insert觸發(fā)器在交易信息表創(chuàng)立一個(gè)Insert觸發(fā)器，當(dāng)增加一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。--在交易信息表插入觸發(fā)器.使用游標(biāo)當(dāng)新增一個(gè)交易信息.修改銀行卡的存款余額--檢查觸發(fā)器是否存在.存在則刪除IF(OBJECT_ID('tr_InsertDealInfo','TR')ISNOTnull)DROPTRIGGERtr_InsertDealInfoGO--創(chuàng)立Insert觸發(fā)器CREATETRIGGERtr_InsertDealInfoONBankDealInfoFORINSERTASDECLARE@typeCHAR(10),@sumMONEY,@BDBCNoCHAR(19)--定義游標(biāo),指向inserted表DECLAREcursor_BankDealInfoCURSORFORSELECTBDDealType,BDDealAcount,BDBCNoFROMInserted--翻開游標(biāo)OPENcursor_BankDealInfo--讀取游標(biāo).賦值給相應(yīng)字段FETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNo--@@FETCH_STATUS=0FETCH語句成功WHILE@@FETCH_STATUS=0BEGIN--根據(jù)交易類型.更新銀行卡余額.RTRIM和LRAIM是去除首尾空格IF(RTRIM(LTRIM(@type))='存入')UPDATEBankCardSETBCExistBalance=BCExistBalance+@sumWHEREBCNo=@BDBCNoIF(RTRIM(LTRIM(@type))='支取')UPDATEBankCardSETBCExistBalance=BCExistBalance-@sumWHEREBCNo=@BDBCNoFETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoEND--關(guān)閉游標(biāo)CLOSEcursor_BankDealInfo--釋放游標(biāo)DEALLOCATEcursor_BankDealInfoGO創(chuàng)立Delete觸發(fā)器在交易信息表創(chuàng)立一個(gè)Delete觸發(fā)器，當(dāng)刪除一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。--刪除--觸發(fā)器存在則刪除IF(OBJECT_ID('tr_DelDealInfo','TR')ISNOTNULL)DROPTRIGGERtr_DelDealInfoGO--創(chuàng)立delete觸發(fā)器CREATETRIGGERtr_DelDealInfoONBankDealInfoFORDELETEASDECLARE@typeCHAR(10),@sumMONEY,@BDBCNoCHAR(19)--定義游標(biāo),指向deleted表DECLAREcursor_BankDealInfoCURSORFORSELECTBDDealType,BDDealAcount,BDBCNoFROMDeleted--翻開游標(biāo)OPENcursor_BankDealInfo--讀取游標(biāo),賦值FETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoWHILE@@FETCH_STATUS=0BEGINIF(RTRIM(LTRIM(@type))='存入')UPDATEBankCardSETBCExistBalance=BCExistBalance-@sumWHEREBCNo=@BDBCNoIF(RTRIM(LTRIM(@type))='支取')UPDATEBankCardSETBCExistBalance=BCExistBalance+@sumWHEREBCNo=@BDBCNoFETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoENDCLOSEcursor_BankDealInfoDEALLOCATEcursor_BankDealInfoGO創(chuàng)立Update觸發(fā)器在交易信息表創(chuàng)立一個(gè)Update觸發(fā)器，當(dāng)更新一條交易信息時(shí)，修改相應(yīng)銀行卡的存款余額。--更新--觸發(fā)器存在則刪除IF(OBJECT_ID('tr_UpdateDealInfo','TR')ISNOTNULL)DROPTRIGGERtr_UpdateDealInfoGOCREATETRIGGERtr_UpdateDealInfoONBankDealInfoFORUPDATEASDECLARE@typeCHAR(10),@sumMONEY,@BDBCNoCHAR(19)--定義游標(biāo),指向deleted表DECLAREcursor_BankDealInfoCURSORFORSELECTBDDealType,BDDealAcount,BDBCNoFROMDeleted--翻開游標(biāo)OPENcursor_BankDealInfo--讀取游標(biāo),賦值FETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoWHILE@@FETCH_STATUS=0BEGINIF(RTRIM(LTRIM(@type))='存入')UPDATEBankCardSETBCExistBalance=BCExistBalance-@sumWHEREBCNo=@BDBCNoIF(RTRIM(LTRIM(@type))='支取')UPDATEBankCardSETBCExistBalance=BCExistBalance+@sumWHEREBCNo=@BDBCNoFETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoENDCLOSEcursor_BankDealInfoDEALLOCATEcursor_BankDealInfo--定義游標(biāo),指向inserted表DECLAREcursor_BankDealInfoCURSORFORSELECTBDDealType,BDDealAcount,BDBCNoFROMInserted--翻開游標(biāo)OPENcursor_BankDealInfo--讀取游標(biāo).賦值給相應(yīng)字段FETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNo--@@FETCH_STATUS=0FETCH語句成功WHILE@@FETCH_STATUS=0BEGIN--根據(jù)交易類型.更新銀行卡余額.RTRIM和LRAIM是去除首尾空格IF(RTRIM(LTRIM(@type))='存入')UPDATEBankCardSETBCExistBalance=BCExistBalance+@sumWHEREBCNo=@BDBCNoIF(RTRIM(LTRIM(@type))='支取')UPDATEBankCardSETBCExistBalance=BCExistBalance-@sumWHEREBCNo=@BDBCNoFETCHNEXTFROMcursor_BankDealInfoINTO@type,@sum,@BDBCNoEND--關(guān)閉游標(biāo)CLOSEcursor_BankDealInfo--釋放游標(biāo)DEALLOCATEcursor_BankDealInfo插入數(shù)據(jù)表的測試數(shù)據(jù)使用T-SQL語句向每個(gè)表插入如下所示測試數(shù)據(jù)，要保證業(yè)務(wù)數(shù)據(jù)的一致性和完整性。BankBusinessType表的測試數(shù)據(jù)INSERTINTOBankBusinessType(BBTName,BBTComment)VALUES ('活期','無固定存期,可隨時(shí)存取,存取金額不限的一種比擬靈活的存款'),('定活兩便','事先不約定存定期,一次性存入,一次性支取的存款'),('通知','不約定存期,支取是需提前通知銀行,約定支取日期和金額方能支取的存款'),('整存整取年','整筆存入,到期提取本息'),('整存整取年','整筆存入,到期提取本息'),('整存整取年','整筆存入,到期提取本息'),('零存整取年','事先原定金額,逐月按約定金額存入,到期支付本息'),('零存整取年','事先原定金額,逐月按約定金額存入,到期支付本息'),('零存整取年','事先原定金額,逐月按約定金額存入,到期支付本息'),('自助轉(zhuǎn)賬','銀行ATM存取款機(jī)上辦理銀行卡之間互相劃轉(zhuǎn)')GOSELECT*FROMBankBusinessTypeBankBusinessType表測試數(shù)據(jù)BankCustomer表的測試數(shù)據(jù)INSERTINTOBankCustomer(BCName,BCICNo,BCTel,BCAddr)VALUES('葉春萌','152825198512548541',,'麗都新城'),('周明',,,'松石名第')GOSELECT*FROMBankCustomerBankCustomer表測試數(shù)據(jù)BankCard表的測試數(shù)據(jù)INSERTINTOBankCard(BCNo,BCPwd,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCId,BCExistBalance)VALUES('1010357612345678','197611','RMB',1,CAST(DATEADD(DAY,-(RAND()*30),GETDATE())ASdate),1000,'否',1,1000),('1010357612345688','197611','RMB',2,CAST(DATEADD(DAY,-(RAND()*30),GETDATE())ASdate),1000,'否',2,1500)GOSELECT*FROMBankCardBankCard表測試數(shù)據(jù)BankDealInfo表的測試數(shù)據(jù)INSERTINTOBankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)VALUES('1010357612345678',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),500,'存入','單位月工資'),('1010357612345678',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),1500,'存入','單位月工資'),('1010357612345678',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),300,'存入','支付寶付款'),('1010357612345678',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),400,'支取','刷卡消費(fèi)'),('1010357612345688',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),3500,'存入','單位月工資'),('1010357612345688',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),4500,'存入','單位月工資'),('1010357612345688',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),800,'存入','支付寶付款'),('1010357612345688',CAST(DATEADD(DAY,-(RAND()*15),GETDATE())ASdate),900,'支取','刷卡消費(fèi)')GOSELECT*FROMBankDealInfoBankDealInfo表測試數(shù)據(jù)5、實(shí)訓(xùn)五：模擬常規(guī)業(yè)務(wù)(1)修改客戶密碼根據(jù)卡號(hào)修改指定2個(gè)客戶的銀行密碼，其中第一個(gè)客戶1010357612345678密碼修改為123456，第二個(gè)客戶1010357612345688修改為123123。--(1)修改客戶密碼UPDATEBankCardSETBCPwd='123465'WHEREBCNo='1010357612345678'UPDATEBankCardSETBCPwd='123123'WHEREBCNo='1010357612345688'SELECTBCNo'銀行卡卡號(hào)',BCPwd'密碼',BCCurrency'貨幣類型',BCBBTId'儲(chǔ)蓄種類',BCOpenDate'開戶日期',BCOpenAmount'開戶金額',BCRegLoss'是否掛失',BCBCId'客戶編號(hào)',BCExistBalance'存款金額'FROMBankCard修改客戶密碼效果圖辦理銀行卡掛失卡號(hào)為1010357612345678的銀行卡喪失，申請(qǐng)掛失。--(2)辦理銀行卡掛失UPDATEBankCardSETBCRegLoss='是'WHEREBCNo='1010357612345678'SELECTBCNo'銀行卡卡號(hào)',BCPwd'密碼',BCCurrency'貨幣類型',BBTName'儲(chǔ)蓄類型',BCOpenDate'開戶日期',BCOpenAmount'開戶金額',BCRegLoss'是否掛失',BCName'客戶姓名',BCExistBalance'存款金額'FROMBankCardINNERJOINBankCustomerONBankCard.BCBCId=BankCustomer.BCIdINNERJOINBankBusinessTypeONBankCard.BCBBTId=BankBusinessType.BBTId辦理銀行卡掛失效果圖統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算存入代表資金流入，支取代表資金流出。計(jì)算公式：資金流通余額=總存入金額-總支取金額假定存款利率為千分之三，貸款利率為千分之八。計(jì)算公式：盈利結(jié)算=總支取金額*0.008-總存入金額*0.003。要求創(chuàng)立一個(gè)存儲(chǔ)過程proc_staticsBanlanceAndProfit。--(3)統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算IFEXISTS(SELECT*FROMsys.sysobjectsWHEREname='proc_staticsBanlanceAndProfit')DROPPROCEDUREproc_staticsBanlanceAndProfitGOCREATEPROCEDUREproc_staticsBanlanceAndProfitASDECLARE@InMoneyMONEYDECLARE@OutMoneyMONEYSELECT@InMoney=SUM(BDDealAcount)FROMBankDealInfoWHEREBDDealType='存入'SELECT@OutMoney=SUM(BDDealAcount)FROMBankDealInfoWHEREBDDealType='支取'PRINT'存入總金額:'+LTRIM(STR(@InMoney))+'RMB,支取總金額:'+LTRIM(STR(@OutMoney))+'RMB,銀行流通余額:'+LTRIM(STR(@InMoney-@OutMoney))+'RMB,盈利余額:'+LTRIM(STR(@OutMoney*0.008-@InMoney*0.003))+'RMB'GOEXECproc_staticsBanlanceAndProfit統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算查詢本周開戶信息查詢本周開戶的卡號(hào)，顯示該卡的相關(guān)信息。--(4)查詢本周開戶信息--默認(rèn)星期日作為一周的第一天.修改星期一為第一天SETDATEFIRST1SELECTBCNo'銀行卡卡號(hào)',BCName'姓名',BCCurrency'貨幣類型',BCOpenDate'開戶時(shí)間',BBTName'儲(chǔ)蓄類型',BCOpenAmount'開戶金額',BCExistBalance'存款金額',CASEBCRegLossWHEN'是'THEN'掛失賬戶'WHEN'否'THEN'正常賬戶'end'是否掛失'FROMBankCardINNERJOINBankBusinessTypeONBankBusinessType.BBTId=BankCard.BCBBTIdINNERJOINBankCustomerONBankCustomer.BCId=BankCard.BCBCIdWHEREDATEDIFF(DAY,BCOpenDate,GETDATE())<DATEPART(WEEKDAY,GETDATE())本周開戶信息查詢本月單次交易金額最高的卡號(hào)和總交易金額最高的卡號(hào)查詢本月存、取款中單次交易金額最高的卡號(hào)信息。--(5)查詢本月單次交易金額最高的卡號(hào)和總交易金額最高的卡號(hào)SELECTDISTINCTBCNo'銀行卡卡號(hào)',BCOpenDate'開戶日期',BCOpenAmount'開戶金額',BDDealAcount'單次最高金額'FROMBankCardINNERJOINBankDealInfoONBankDealInfo.BDBCNo=BankCard.BCNoWHEREBDDealAcount=(SELECTMAX(BDDealAcount)FROMBankDealInfoWHEREDATEDIFF(MONTH,BDDealDate,GETDATE())=0)SELECTTOP1BCNo'銀行卡卡號(hào)',BCOpenDate'開戶日期',BCOpenAmount'開戶金額',SUM(BDDealAcount)'總交易最高金額'FROMBankCardINNERJOINBankDealInfoONBankDealInfo.BDBCNo=BankCard.BCNoWHEREDATEDIFF(MONTH,BDDealDate,GETDATE())=0GROUPBYBCNo,BCOpenDate,BCOpenAmountORDERBYSUM(BDDealAcount)DESC本月交易金額最高的卡號(hào)查詢掛失客戶查詢掛失賬號(hào)的客戶信息，分別利用子查詢in的方式或者內(nèi)部連接innerjoin。--(6)查詢掛失客戶--子查詢SELECTBCName'客戶姓名',BCTel'聯(lián)系'FROMBankCustomerWHEREBCIdIN(SELECTBCBCIdFROMBankCardWHEREBCRegLoss='是')--內(nèi)連接SELECTBCName'客戶姓名',BCTel'聯(lián)系'FROMBankCustomerINNERJOINBankCardONBankCard.BCBCId=BankCustomer.BCIdWHEREBCRegLoss='是'查詢掛失客戶催款提醒業(yè)務(wù)根據(jù)某種業(yè)務(wù)〔如代繳費(fèi)、代繳手機(jī)費(fèi)或房貸等〕的需要，每個(gè)月末，查詢出客戶賬戶上余額少于10000元，由銀行統(tǒng)一致電催款。--(7)催款提醒業(yè)務(wù)SELECTBCName'客戶姓名',BCTel'聯(lián)系',BCExistBalance'存款金額'FROMBankCardINNERJOINBankCustomerONBankCustomer.BCId=BankCard.BCBCIdWHEREBCExistBalance<=10000催款提醒業(yè)務(wù)6、實(shí)訓(xùn)六：創(chuàng)立、使用視圖輸出銀行客戶記錄視圖VW_userInfo顯示的列名全為中文，要求先判斷該視圖是否存在，假設(shè)存在，則先刪除。--(1)輸出銀行客戶記錄視圖VW_userInfoIFOBJECT_ID('VW_UserInfo','V')ISNOTNULLDROPVIEWVW_UserInfoGOCREATEVIEWVW_UserInfoASSELECTBCId'客戶編號(hào)',BCName'開戶名',BCICNo'身份證號(hào)',BCTel'號(hào)碼',BCAddr'居住地址'FROMBankCustomerGOSELECT*FROMVW_UserInfo輸出銀行客戶記錄輸出銀行卡記錄視圖VW_CardInfo--(2)輸出銀行卡記錄視圖VW_CardInfoIFOBJECT_ID('VW_CardInfo','V')ISNOTNULLDROPVIEWVW_CardInfoGOCREATEVIEWVW_CardInfoASSELECTBCNo'銀行卡卡號(hào)',BCName'姓名',BCCurrency'貨幣類型',BBTName'儲(chǔ)蓄類型',BCOpenDate'開戶日期',BCExistBalance'存款金額',BCPwd'密碼',BCRegLoss'是否掛失'FROMBankCardINNERJOINBankCustomerONBankCustomer.BCId=BankCard.BCBCIdINNERJOINBankBusinessTypeONBankBusinessType.BBTId=BankCard.BCBBTIdGOSELECT*FROMVW_CardInfo輸出銀行卡記錄輸出銀行卡交易記錄視圖VW_TransInfo--(3)輸出銀行卡交易記錄視圖VW_TransInfo.IFOBJECT_ID('VW_TransInfo','V')ISNOTNULLDROPVIEWVW_TransInfoGOCREATEVIEWVW_TransInfoASSELECTBDDealDate'交易日期',BDDealType'交易類型',BDBCNo'銀行卡卡號(hào)',BDDealAcount'交易金額',BDDealComment'備注'FROMBankDealInfoGOSELECT*FROMVW_TransInfoORDERBY交易日期輸出銀行卡的交易記錄根據(jù)客戶登錄名查詢該客戶賬戶信息VW_OneUserInfo根據(jù)客戶登錄名〔采用實(shí)名制訪問銀行系統(tǒng)〕查詢該客戶賬戶信息的視圖，利用SQLSERVER系統(tǒng)函數(shù)system_user獲得數(shù)據(jù)庫用戶名。--(4)根據(jù)客戶登錄名查詢該客戶賬戶信息VW_OneUserInfoIFOBJECT_ID('VW_OneUserInfo','V')ISNOTNULLDROPVIEWVW_OneUserInfoGOCREATEVIEWVW_OneUserInfoASSELECTBCId'客戶編號(hào)',BCName'開戶名',BCICNo'身份證號(hào)',BCTel'號(hào)碼',BCAddr'居住地址'FROMBankCustomerWHEREBCName=SYSTEM_USERGOSELECT*FROMVW_OneUserInfo根據(jù)登錄名查詢賬戶信息7、實(shí)訓(xùn)七：存儲(chǔ)過程實(shí)現(xiàn)業(yè)務(wù)處理(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。--(1)完成存款或取款業(yè)務(wù)--判斷存儲(chǔ)過程是否存在.存在則刪除IFOBJECT_ID('proc_TakeMoney','P')ISNOTNULLDROPPROCEDUREproc_TakeMoneyGO--創(chuàng)立存取款業(yè)務(wù)的存儲(chǔ)過程CREATEPROCEDUREproc_TakeMoney@bcnoCHAR(19),@moneyMONEY,@pwdCHAR(6)=NULLAS--不返回受影響的行數(shù)SETNOCOUNTONDECLARE@existBanlanceMONEY--啟動(dòng)事務(wù)BEGINTRANSACTIONSELECT@existBanlance=BCExistBalanceFROMBankCardWHEREBCNo=@bcnoPRINT'交易前,卡號(hào)'+@bcnoPRINT'交易正進(jìn)展,請(qǐng)稍后...'--如果輸入?yún)?shù)@pwd為空,則為取款業(yè)務(wù).否則為存款業(yè)務(wù)IF(SELECTBCRegLossFROMBankCardWHEREBCNo=@bcno)='是'BEGINPRINT'本卡已掛失,不能交易!'ROLLBACKTRANSACTIONRETURNENDIF(@pwdISNOTNULL)BEGIN--取款I(lǐng)FEXISTS(SELECT*FROMBankCardWHEREBCNo=@bcnoANDBCPwd=@pwd)BEGINIF(@money<=@existBanlance-1)INSERTINTOBankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)VALUES(@bcno,GETDATE(),@money,'支取','通過存儲(chǔ)過程操作')ELSEPRINT'取款交易失敗.余額缺乏,請(qǐng)減少取款'ENDELSEBEGINPRINT'取款交易失敗,卡號(hào)或密碼有錯(cuò)誤'ROLLBACKTRANSACTIONRETURNENDENDELSEBEGIN--辦理存款業(yè)務(wù)IFEXISTS(SELECT*FROMBankCardWHEREBCNo=@bcno)INSERTINTOBankDealInfo(BDBCNo,BDDealDate,BDDealAcount,BDDealType,BDDealComment)VALUES(@bcno,GETDATE(),@money,'存入','通過存儲(chǔ)過程操作')ELSEBEGINPRINT'存款失敗,卡號(hào)不存在'ROLLBACKTRANSACTIONRETURNENDEND--判斷事務(wù)處理是否有異常.沒有提交.有則回滾IF(@@ERROR<>0)BEGINPRINT'交易失敗,發(fā)生未知錯(cuò)誤'ROLLBACKTRANSACTIONENDELSEBEGINCOMMITTRANSACTIONPRINT'交易成功,交易金額為:'+LTRIM(STR(@money))SELECT@existBanlance=BCExistBalanceFROMBankCardWHEREBCNo=@bcnoPRINT'卡號(hào):'+@bcno+',余額為:'+LTRIM(STR(@existBanlance))END--顯示銀行卡用戶詳情和交易詳情SELECT*FROMVW_CardInfoSELECT*FROMVW_TransInfoORDERBY交易日期GO--執(zhí)行存款存儲(chǔ)過程EXECproc_TakeMoney@bcno='1010357612345688',@money=2000--執(zhí)行取款存儲(chǔ)過程EXECproc_TakeMoney@bcno='1010357612345688',@money=1200,@pwd='123465'執(zhí)行存儲(chǔ)過程的結(jié)果〔存款〕執(zhí)行存儲(chǔ)過程的輸出消息〔存款〕執(zhí)行存儲(chǔ)過程結(jié)果〔取款〕執(zhí)行存儲(chǔ)過程輸出消息〔取款〕產(chǎn)生隨機(jī)卡號(hào)創(chuàng)立存儲(chǔ)過程產(chǎn)生8位隨機(jī)數(shù)字，與前8位固定數(shù)字“10103576〞連接，生成一個(gè)由16位數(shù)字組成的銀行卡號(hào)，并輸出。--(2)產(chǎn)生隨機(jī)卡號(hào)IFOBJECT_ID('proc_randCardID','P')ISNOTNULLDROPPROCEDUREproc_randCardIDGO--創(chuàng)立隨機(jī)卡號(hào)的存儲(chǔ)過程CREATEPROCEDUREproc_randCardID@randCardIdCHAR(19)OUTPUTASDECLARE@rNUMERIC(8,8),@tmpstrCHAR(10)--產(chǎn)生隨機(jī)種子=當(dāng)前的月份數(shù)*100000+當(dāng)前的秒數(shù)*1000+當(dāng)前的毫秒數(shù)SET@r=RAND(DATEPART(MONTH,GETDATE())*100000+DATEPART(SECOND,GETDATE())*1000+DATEPART(MILLISECOND,GETDATE()))SET@tmpstr=CONVERT(CHAR(10),@r)SET@randCardId='10103576'+SUBSTRING(@tmpstr,3,4)+''+SUBSTRING(@tmpstr,7,4)GODECLARE@myCardId1CHAR(19)EXECproc_randCardID@myCardId1OUTPUTPRINT'產(chǎn)生隨機(jī)卡號(hào)為'+@myCardId1測試產(chǎn)生隨機(jī)卡號(hào)完成開戶業(yè)務(wù)描述：利用存儲(chǔ)過程為客戶開設(shè)2個(gè)銀行卡賬戶，開戶時(shí)需要提供客戶的信息有：開戶名、身份證號(hào)、號(hào)碼、開戶金額、存款類型和地址?？蛻舻男畔⒁姳硭荆簽槌晒﹂_戶的客戶提供銀行卡，且銀行卡號(hào)唯一。--(3)完成開戶業(yè)務(wù)IFOBJECT_ID('proc_openAccount','P')ISNOTNULLDROPPROCEDUREproc_openAccountGO--創(chuàng)立存取款業(yè)務(wù)的存儲(chǔ)過程CREATEPROCEDUREproc_openAccount@BCNameCHAR(20),@BCICNoCHAR(18),@BCTelVARCHAR(20),@BCOpenAmountMONEY,@BBTNameVARCHAR(20),@BCAddrvarchar(100)AS--不返回受影響的行數(shù)SETNOCOUNTONDECLARE@BCBBTIdINT,@BCNoCHAR(19),@BCBCIdINT--判斷存款類型是否正確IFEXISTS(SELECT*FROMBankBusinessTypeWHEREBBTName=@BBTName)BEGINBEGINTRANSELECT@BCBBTId=BBTIdFROMBankBusinessTypeWHEREBBTName=@BBTNameEXECproc_randCardID@BCNoOUTPUT--選出不重復(fù)的卡號(hào)WHILE(EXISTS(SELECT*FROMBankCardWHEREBCNo=@BCNo))EXECproc_randCardID@BCNoOUTPUT--插入一條客戶信息記錄,身份證一樣不重復(fù)插入IFNOTEXISTS(SELECT*FROMBankCustomerWHEREBCICNo=@BCICNo)BEGININSERTBankCustomer(BCName,BCICNo,BCTel,BCAddr)VALUES(@BCName,@BCICNo,@BCTel,@BCAddr)SET@BCBCId=@@IDENTITYENDELSESELECT@BCBCId=BCIdFROMBankCustomerWHEREBCICNo=@BCICNoINSERTBankCard(BCNo,BCPwd,BCCurrency,BCBBTId,BCOpenDate,BCOpenAmount,BCRegLoss,BCBCId,BCExistBalance)VALUES(@BCNo,'888888','RMB',@BCBBTId,GETDATE(),@BCOpenAmount,'否',@BCBCId,@BCOpenAmount)IF(@@ERROR<>0)BEGINPRINT'尊敬的客戶，開戶不成功，所有操作均撤銷'ROLLBACKTRANENDELSEBEGINCOMMITTRANPRINT'尊敬的客戶，開戶成功，系統(tǒng)為你產(chǎn)生的隨機(jī)卡號(hào)是:'+@BCNo+',開戶日期:'+CONVERT(CHAR(10),GETDATE(),111)+',開戶金額:'+LTRIM(STR(@BCOpenAmount))END--顯示開戶的客戶表信息和銀行卡信息SELECT*FROMVW_UserInfoSELECT*FROMVW_CardInfoENDELSEPRINT'尊敬的客戶,未能成功開戶,存款類型不正確,請(qǐng)重新輸入!'GOEXECproc_openAccount@BCName='陳曦',@BCICNo=,@BCTel=,@BCOpenAmount=1200,@BBTName='定活兩便',@BCAddr='海天家園'EXECproc_openAccount@BCName='林念初',@BCICNo=,@BCTel=,@BCOpenAmount=1000,@BBTName='活期',@BCAddr='峻峰華庭'執(zhí)行開戶存儲(chǔ)過程的結(jié)果測試開戶存儲(chǔ)過程的輸出信息分頁顯示查詢交易數(shù)據(jù)根據(jù)指定的頁數(shù)和每頁的記錄數(shù)分頁顯示交易數(shù)據(jù)。--(4)分頁顯示查詢交易數(shù)據(jù)IFOBJECT_ID('proc_PagingDisplay','P')ISNOTNULLDROPPROCEDUREproc_PagingDisplayGOCREATEPROCEDUREproc_PagingDisplay@pageINT,@countINTASSELECTRowNumber'交易編號(hào)',BDDealDate'交易日期',BDDealType'交易類型',BDBCNo'銀行卡卡號(hào)',BDDealAcount'交易金額'FROM(SELECTROW_NUMBER()OVER(ORDERBYBDNo)RowNumber,BDDealDate,BDDealType,BDBCNo,BDDealAcountFROMBankDealInfo)cWHEREc.RowNumberBETWEEN(@1)*@count+1AND@page*@countGOEXECproc_PagingDisplay@page=2,@count=5每頁5行方式輸出第2頁交易數(shù)據(jù)打印客戶對(duì)賬單為某個(gè)特定的銀行卡號(hào)打印指定時(shí)間內(nèi)發(fā)生交易的對(duì)賬單。--(5)打印客戶對(duì)賬單IFOBJECT_ID('proc_CheckSheet','P')ISNOTNULLDROPPROCEDUREproc_CheckSheetGOCREATEPROCEDUREproc_CheckSheet@bcnoCHAR(19),@startDateDATETIME=NULL,@endDateDATETIME=NULLAS--聲明各個(gè)變量分別存放姓名、貨幣類型、存款類型、開戶日期DECLARE@bcNameCHAR(20),@bcCurrencyCHAR(5),@bbtNameCHAR(20),@bcOpenDateDATE;--聲明各個(gè)變量分別存放交易日期、交易類型、交易金額、備注DECLARE@bddealdateDATETIME,@bddealtypeCHAR(10),@bddealacountMONEY,@bddealcommentVARCHAR(100);SELECT@bcName=B.BCName,@bcCurrency=A.BCCurrency,@bbtName=C.BBTName,@bcOpenDate=A.BCOpenDateFROMBankCardAINNERJOINBankCustomerBONA.BCBCId=B.BCIdINNERJOINBankBusinessTypeCONA.BCBBTId=C.BBTIdWHEREA.BCNo=@bcno;PRINT'卡號(hào):'+@bcno;PRINT'姓名:'+@bcName;PRINT'貨幣:'+@bcCurrency;PRINT'存款類型:'+@bbtName;PRINT'開戶日期:'+CONVERT(CHAR(12),@bcOpenDate,111);--創(chuàng)立一個(gè)游標(biāo)，指向指定的交易記錄IF(@startDateISNULL)DECLAREcur_BankDealinfoCURSORFORSELECTBDDealDate,BDDealType,BDDealAcount,BDDealCommentFROMBankDealInfoWHEREBDBCNo=@bcno;ELSEBEGINIF(@endDateISNULL)DECLAREcur_BankDealinfoCURSORFORSELECTBDDealDate,BDDealType,BDDealAcount,BDDealCommentFROMBankDealInfoWHEREBDBCNo=@bcnoANDBDDealDate>@startDate;ELSEDECLAREcur_BankDealinfoCURSORFORSELECTBDDealDate,BDDealType,BDDealAcount,BDDealCommentFROMBankDealInfoWHEREBDBCNo=@bcnoANDBDDealDate>=@startDateANDBDDealDate<=@endDate;END--翻開游標(biāo)OPENcur_BankDealinfoPRINTCONVERT(CHAR(12),'交易日')+CONVERT(CHAR(10),'類型')+CONVERT(CHAR(10),'交易金額')+CONVERT(CHAR(100),'備注')--從游標(biāo)里取出相應(yīng)字段的值到各個(gè)變量里FETCHNEXTFROMcur_BankDealinfoINTO@bddealdate,@bddealtype,@bddealacount,@bddealcommentWHILE@@FETCH_STATUS=0BEGINPRINTCONVERT(CHAR(12),@bddealdate,111)+CONVERT(CHAR(10),@bddealtype)+CONVERT(CHAR(10),@bddealacount)+CONVERT(CHAR(100),@bddealcomment);FETCHNEXTFROMcur_BankDealinfoINTO@bddealdate,@bddealtype,@bddealacount,@bddealcomment;END;CLOSEcur_BankDealinfo;DEALLOCATEcur_BankDealinfo;GOEXECproc_CheckSheet@bcno='1010357612345678'EXECproc_CheckSheet@bcno='1010357612345678',@startDate='2015-11-03',@endDate='2015-11-17'按卡號(hào)打印對(duì)賬單統(tǒng)計(jì)未發(fā)生交易的賬戶查詢統(tǒng)計(jì)指定時(shí)間段內(nèi)沒有發(fā)生交易的賬戶信息。--(6)統(tǒng)計(jì)未發(fā)生交易的賬戶IFOBJECT_ID('proc_getWithoutTrade','P')ISNOTNULLDROPPROCEDUREproc_getWithoutTradeGOCREATEPROCEDUREproc_getWithoutTrade@startDateDATETIME=NULL,@endDateDATETIME=NULLASDECLARE@nameCHAR(16),@ICNoCHAR(18),@telCHAR(15),@addrCHAR(50),@moneySumMONEY=0,@customerSumINT=0,@moneyINTIF(@startDateISNULL)SET@startDate=CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1')IF(@endDateISNULL)SET@endDate=GETDATE()--有的客戶未交易.所以用右連接或者全連接 DECLAREcur_outTradeCURSORFORSELECTDISTINCTBCName,BCICNo,BCTel,BCAddr,BCExistBalanceFROMBankDealInfoRIGHTJOINBankCardONBankCard.BCNo=BankDealInfo.BDBCNoRIGHTJOINBankCustomerONBankCustomer.BCId=BankCard.BCBCIdWHEREBCNoNOTIN(SELECTBDBCNoFROMBankDealInfowhereBDDealDate>=@startDateANDBDDealDate<=@endDate)PRINTCONVERT(CHAR(16),'客戶姓名')+CONVERT(CHAR(20),'身份證號(hào)')+CONVERT(CHAR(16),'')+CONVERT(CHAR(20),'地址')OPENcur_outTradeFETCHNEXTFROMcur_outTradeINTO@name,@ICNo,@tel,@addr,@moneyWHILE@@FETCH_STATUS=0BEGINPRINTCONVERT(CHAR(16),@name)+CONVERT(CHAR(20),@ICNo)+CONVERT(CHAR(16),@tel)+CONVERT(CHAR(20),@addr)SET@moneySum=@moneySum+@moneySET@customerSum=@customerSum+1FETCHNEXTFROMcur_outTradeINTO@name,@ICNo,@tel,@addr,@moneyENDPRINT'統(tǒng)計(jì)未發(fā)生交易的客戶'PRINT'客戶人數(shù):'+LTRIM(STR(@customerSum))+',客戶總余額:'+LTRIM(STR(@moneySum))CLOSEcur_outTradeDEALLOCATEcur_outTradeGOEXECproc_getWithoutTrade@startDate='2015-8-19',@endDate='2015-9-19'EXECproc_getWithoutTrade統(tǒng)計(jì)未發(fā)生交易的賬戶統(tǒng)計(jì)銀行卡交易量和交易額統(tǒng)計(jì)指定時(shí)間段內(nèi)某地區(qū)客戶在銀行卡交易量和交易額，如果不指定地區(qū)，則查詢所有客戶的交易量和交易額。--(7)統(tǒng)計(jì)銀行卡交易量和交易額IFOBJECT_ID('proc_getTradeInfo','P')ISNOTNULLDROPPROCEDUREproc_getTradeInfoGOCREATEPROCEDUREproc_getTradeInfo@startDateDATETIME=NULL,@endDateDATETIME=NULL,@addrCHAR(20)=NULLASDECLARE@inNumINT=