课程设计：基于SQL Server的银行ATM 存取款机系统设计与实现
生活随笔
收集整理的這篇文章主要介紹了
课程设计：基于SQL Server的银行ATM 存取款机系统设计与实现
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
目錄
- 前言
- 一、項目背景
- 1、項目任務
- 2、項目技能目標
- 3、需求概述
- 4、開發環境
- 5 、問題分析
- (1) 銀行存取款業務介紹
- (2) 客戶信息
- (3) 銀行卡賬戶信息
- (4) 銀行卡交易信息
- (5) 銀行卡手工賬戶和存取款單據信息
- 二、項目實訓內容
- 1、實訓一：制定《數據庫設計與編程規范》
- 2、實訓二：數據庫分析設計與建模
- (1) 分析銀行 ATM 存取款系統實體
- (2) 規范數據庫結構設計
- (3) 繪制 CDM 模型、生成 PDM 模型
- 3、實訓三：創建數據庫
- (1) 創建數據庫
- (2) 創建各個數據表及相關的約束
- (3) 添加外鍵約束和生成數據庫關系圖
- 4、實訓四：創建觸發器和插入測試數據
- (1) 創建級聯觸發器
- (2) 插入數據表的測試數據
- 5、實訓五：模擬常規業務
- (1) 修改客戶密碼
- (2) 辦理銀行卡掛失
- (3) 統計銀行資金流通余額和盈利結算
- (4) 查詢本周開戶信息
- (5) 查詢本月單次交易金額最高的卡號和總交易金額最高的卡號
- (6) 查詢掛失客戶
- (7) 催款提醒業務
- 6、實訓六：創建、使用視圖
- (1) 輸出銀行客戶記錄視圖VW_userInfo
- (2) 輸出銀行卡記錄視圖VW_CardInfo
- (3) 輸出銀行卡交易記錄視圖VW_TransInfo
- (4) 根據客戶登錄名查詢該客戶賬戶信息VW_OneUserInfo
- 7、實訓七：存儲過程實現業務處理
- (1) 完成存款或取款業務
- (2) 產生隨機卡號
- (3) 完成開戶業務
- (4) 分頁顯示查詢交易數據
- (5) 統計未發生交易的賬戶
- 8、實訓八：利用事務實現轉賬
| 加油加油 |
前言
? ? ? ? 本文章主要是在學了SQL Server后做的一個課程設計，全文有三萬多字，接近一千行，光簡簡單單寫這篇博客就花了不少時間，若有出現錯誤之處，請指出，定當感激不盡，一起學習，一起進步。
一、項目背景
1、項目任務
- 使用PowerDesigner 完成數據庫設計
- 創建數據庫、創建表、創建約束
- 使用觸發器和插入測試數據
- 模擬常規業務、創建視圖
- 使用存儲過程實現業務處理
- 利用事務實現較復雜的數據更新
2、項目技能目標
- 使用PowerDesigner 完成數據庫概念模型和數據庫物理模型設計。
- 使用 T-SQL 語句創建數據庫、表和各種約束。
- 使用 T-SQL 語句編程實現常見業務。
- 使用觸發器實現多表之間的級聯更新。
- 使用事務和存儲過程封裝業務邏輯。
- 使用視圖簡化復雜的數據查詢。
- 使用游標技術實現結果集的行集操作。
3、需求概述
? ? ? ?某銀行是一家民辦的小型銀行企業，現有十多萬客戶，公司將為該銀行開發一套 ATM 存取款機系統，對銀行日常的存取款業務進行計算機管理，以便保證數據的安 全性，提高工作效率。
? ? ? ?要求根據銀行存取款業務需求設計出符合第三范式的數據庫結構，使用 T-SQL 語言創建數據庫和表，并添加表約束，進行數據的增刪改查，運用邏輯結構語句、事 務、視圖和存儲過程，按照銀行的業務需求，實現各項銀行日常存款、取款和轉賬業 務。
4、開發環境
- 數據庫：SQL SERVER 2008 開發版
- 數據庫建模工具：PowerDesigner15
5 、問題分析
該項目的 ATM 存取款機業務如下：
(1) 銀行存取款業務介紹
? ? ? ?銀行為客戶提供了各種銀行存取款業務。詳見表 1
(2) 客戶信息
? ? ? ?每個客戶憑個人身份證在銀行可以開設多個銀行卡賬戶，開設賬戶時，客戶需要 提供的開戶數據如表 2 所示：
(3) 銀行卡賬戶信息
? ? ? ?銀行為每個賬戶提供一個銀行卡，每個銀行卡可以存入一種幣種的存款，銀行保 存賬戶如表 3 所示：
客戶持銀行卡在 ATM 機上輸入密碼，經系統驗證身份后辦理存款、取款和轉賬 等銀行業務。銀行規定，每個賬戶當前的存款金額不得小于 1 元。
(4) 銀行卡交易信息
? ? ? ?銀行在為客戶辦理業務時，需要記錄每一筆賬目，賬目交易信息如表 4 所示：
(5) 銀行卡手工賬戶和存取款單據信息
? ? ? ?該銀行要求這套軟件實現銀行客戶的開戶、存款、取款、轉賬和余額查詢等業務，使得銀行儲蓄業務方便、快捷，同事保證銀行業務數據的安全性。
? ? ? ?為使開發人員盡快了解銀行業務，該銀行提供了銀行卡手工賬戶和存取款單據的 樣本數據，以供項目開發時參考，參加表 5 和表 6 。
二、項目實訓內容
1、實訓一：制定《數據庫設計與編程規范》
- 長度規范：
? ? ? ?凡是需要命名的對象其標識符均控制在 30 個字符以內，也即：SQL Server 中的 表名、字段名、函數名、存儲過程、觸發器、視圖等名字長度要盡量不超過 30 個字 符長度。 - 構成規范：
? ? ? ?數據庫各種名稱必須以字母開頭，但嚴禁以系統關鍵字開頭，名稱只能含有字母、數 字以及下劃線“_ ”三類字符， “_ ”用于間隔名稱中的各語義字段；不要使用系統保留字作表名。 - 大小寫規范：
? ? ? ?構成 SQL Server 數據庫中的各種名稱(表名、字段名、過程名、視圖名等所有命 名符的首字母需要使用大寫，也即每個命名單詞的首字母大寫，其它字符小寫。但對 于簡寫或縮寫的短單詞，如 ID 、UI 可以全為大寫。 - 主鍵規范：
? ? ? ?除臨時表、流水表以及日志表外，其它表都要建立主鍵。主鍵最好設計成單一主 鍵，盡量不要用復合主鍵，盡量使用沒有業務語義的字段作為主鍵，
如采用按順序自增的數值型字段為主鍵 - 注釋規范：
? ? ? ?每個表，每個字段都要有注釋，說明其含義，對于冗余字段還要特別說明其維護方法，外鍵字段要說明參照于那個表，另外對于存儲過程、視圖、觸發器、函數等代碼均要增加注釋，以保持代碼的可讀性以及后續的可維護性。 - 行大小規范：
? ? ? ?SQL Server 的 1 頁的大小是 8K，因此一行的數據要控制到 8K 之內，如果超過 8K 要想辦法將表進行拆分成多個子表。 - 數據保留策略：
? ? ? ?大表由于數據量較大，往往是系統的性能瓶頸所在，因此對于大表的設計好考慮到今后的數據轉移、分片、Partition 等，并且對大表以及其相關表的數據保留時間也要有一個提前規劃，以免今后出現性能問題束手無策。 - 必備字段要求：
? ? ? ?每個大表都應該添加以下幾個有用的字段，分別為創建日期、修改日期、操作人以及版本標記，創建這些字段的目的是為今后的數據轉移以及分片或分區做準備，同時也有利于今后的數據審計等。
注意事項：
? ? ? ?基于大表的任何操作都要慎重思考，通常情況下要禁止在大表上創建觸發器，禁止在大表上做頻繁的批量更新或刪除動作，禁止在生產時間對大表做 DDL 操作，禁止在大表上做全表掃描(Full Scan)等。 - 臨時表功能：
? ? ? ?SQL Server 分為全局臨時表和局部臨時表，臨時表在很多場合下能帶來意想不到 的效果，尤其是需要中轉的數據記錄集采用臨時表能提升系統性能。臨時表支持索引、 約束、排序等實體表具有的功能。 - 存儲特點：
? ? ? ?臨時表的數據存儲在 tempdb 數據庫中，因此過于頻繁的創建臨時表會增加 tempdb 庫的負荷，尤其是數據量超過 10W 條記錄的臨時表更是會影響 tempdb 庫的 性能，由此在某些情況下可用CTE 替代臨時表的使用。
注意事項：
? ? ? ?臨時表執行完畢后，要及時的手工 Drop 掉，及時釋放資源，減輕系統的 Loading ， 另外特別注意的是要盡量禁止使用全局臨時表，全局適合多個 session 間的數據交互， 但往往會引起數據的串值。 - 命名規范：
? ? ? ?盡量采用有意義的字段名，使描述盡可能清楚，如采用縮寫，盡量使用通用的縮 寫語言，如 addr 代表 address，避免出現只有自己理解的縮寫。 - 日期字段：
? ? ? ?時效性數據應包括“創建時間/修改時間”字段，時間標記對查找數據、清理數據、 排序合并特別有用，另外要根據具體業務考量時間字段的類型，如在 Smalldatetime和 Datetime 類型進行選擇。 - 注意保留詞：
? ? ? ?對于字段的命名，要確保字段名沒有和保留詞、數據庫系統或者常用方法沖突， 比如 master 、CROSS 、address 、substring 、len 、sysobjects 等詞就不適合用來做字段 的獨立命名。 - 數值規范：
? ? ? ?數值型的主要有 INT 、BIGINT 、TINYINT 、SMALLINT 、FLOAT 、NUMERIC 、 MONEY 等類型，要根據實際應用選擇合適的類型，如字段的數據為小于 255 的整形 數字，那么就要選擇 TINYINT；如字段數據小于 32767 的整形數字，那么就要選擇SMALLINT，以此類推。 - 文本規范：
? ? ? ?文本類型主要有 CHAR 、VARCHAR 、TEXT 等類型，要根據實際應用選擇合適 的類型，如字段文本長度固定為 8 位，那么就要用CHAR(8)；如文本長度最大為 100 ， 并且大小是非定長的，那么就要設為 VARCHAR(100)。并且以上文本若為漢字，那 么就要設為 NVARCHAR 和 NCHAR 。 - 字段命名技巧：
? ? ? ?字段命名要統一規范，同一個字段在不同的表中命名要一致，另外字段名一般都 要帶上業務模塊的前綴，如訂單(Order)價格字段命名為 Or_Price,部門(Department)編 號為 Dep_No。命名規范沒有那個是最合理的，只有定義好最適合自己的統一規范即 可。
? ? ? ?外鍵建立索引：
外鍵不建立索引，將有可能導致兩個嚴重的問題。1.更新相關的表產生死鎖。2. 兩表關聯查詢時性能低下。因此通常情況下都必須要求外鍵建立索引。 - 聯合索引規范：
? ? ? ?當數據對某表經常要多條件查詢時，可能就需要建立聯合索引，聯合索引的第一 個引導列字段非常重要，引導列字段通常要能過濾掉大部分數據，這樣方能減少 IO 的讀寫，提高性能。非引導列字段在引導列的查詢數據基礎上繼續過濾數據，以提高 查詢速度。聯合索引對更新會產生一定的性能影響。
禁用多余索引：
? ? ? ?數據庫索引能提高查詢速度，但會增加寫操作的開銷，因此對一些幾月或者從沒 有使用過的索引要刪除掉，以免增大數據庫的負荷。
重復索引問題：
? ? ? ?一般情況下，盡量避免重復索引的出現，重復索引很容易引起死鎖，減低數據庫
的并發訪問。重復索引也會造成索引的維護困難。
索引數量限制：
? ? ? ?數據庫索引主要用來解決讀的性能瓶頸，但是會增加寫操作的負荷，因此過多的 索引會造成更新速度變慢，甚至會引起不要的死鎖。一般情況下表中的索引不要超過5 個。
注意事項：
? ? ? ?建立索引前，要充分了解表的使用及數據特性，要了解表的查詢條件和查詢頻率， 甚至隨著業務的變化而引起表數據使用狀況的變化，帶之而來的是索引也需要相應調
整。 - 命名規范：
? ? ? ?存儲過程命名遵守統一的規范，對于業務存儲過程要以 p 或 proc開頭，接著加上” _ ”，然后再加上模塊名稱簡寫和具體的業務詞，最后加上執行類型。
數據庫的存儲過程名嚴禁以 sp 開頭，sp 通常表示系統數據庫存儲過程名的前綴。觸 發器以 Tr 開頭，接著加上”_ ”，然后加上表名。 - 書寫規范：
? ? ? ?關鍵字建議用大寫，同樣的代碼書寫格式保持一致，SQL 腳本采用縮進風格，風 格一致，縮進格式一致，使用空格。
INSERT 規范：
? ? ? ?通常情況下，INSERT 語句要給出具體的字段列表，避免采用“INSERT INTO TB_1 VALUES( ‘值 1’, ’值 2’, ’值 3’)”用法，此種用法往往會由于表結構變遷 而導致語句不可執行
避免隱式轉換：
? ? ? ?書寫時，必須明確表結構及表中各個字段的數據類型，特別是查詢條件中的字段，要避免由于類型的不同導致數據類型轉換的發生，從而減少因為
數據類型轉換產生的系統開銷。 - NULL 陷阱：
? ? ? ?NULL 不要直接用來進行運算符的比較，也不要和其它值進行連接操作，判斷一 個值是否為 NULL 值時，要采用IS NULL 來進行比較。 - LIKE 規范：
? ? ? ?LIKE 子句應盡量前段匹配，要避免通配符在前段，以免導致全索引掃描的發生。 - 參數化代碼：
? ? ? ?SQL 中常量的直接使用，會導致 SQL 語句頻繁的硬解析，進而嚴重影響數據庫 的性能，基于這些原因，代碼中要盡量采用參數綁定，以減少語句硬解析的次數，從 而提高語句執行性能。 - 動態 SQL：
? ? ? ?動態 SQL 是在運行時才進行解析的，相當于是硬解析，因此會損失一些系統性 能，但是動態 SQL 寫法靈活，因此在某些情況下需要以性能換靈活，但對于用靜態 語句就能簡單實現的 SQL，就不要用動態SQL 語句。 - 嵌套層級限制：
? ? ? ?嵌套查詢盡量少使用，尤其是對于超過 3 層的嵌套查詢更要慎用，對于復雜的嵌 套語句要根據業務進行拆分為多條 SQL 來實現，或者通過臨時表來取代一部分嵌套 層級。 - 排序規范：
? ? ? ?SQL 語句中要盡量減少排序，對查詢結果進行的排序會大大降低系統的性能，并 且會增加 tempdb 數據庫的負荷，因此在開發時間寬松情況下，要盡量將排序動作放 到應用程序層去完成。 - 代碼注釋要求：
? ? ? ?注釋是指程序中會被編譯器忽略掉的部分，目的是描述代碼的用途及更新時間， 合理的添加注釋可以使得程序結構清晰，可以使代碼更好理解，便于系統后續的維護。 一般情況下，注釋要不少于代碼的十分之一。 - 靜態 SQL：
? ? ? ?SQL 語句要盡可能采用靜態 SQL，靜態 SQL 第一次執行時會將編譯器解析的結 果存儲在緩存中，下次執行該靜態 SQL 時會直接從緩存中獲取其執行計劃，相當于 是軟解析，因此采用靜態 SQL 可以減少語句的解析時間，提升了數據庫的性能。 - 最小事務原則：
? ? ? ?數據庫事務用來保持數據的一致性，但是對于一個執行時間較長的大事務，會造 成數據庫鎖的增加，當鎖越積越多的時候就會從行鎖升級到頁鎖，從業鎖升級到表鎖， 從而嚴重影響數據庫的性能。因此，在能滿足數據一致性的前提下，要盡量將非一致
性要求的語句代碼從事務中移除，以便提升數據庫的并發訪問。 - 順序提交：
? ? ? ?順序提交是一個好的代碼編寫習慣，順序提交可以減少死鎖的發生，并且還能增 加代碼的可讀性及可維護性。
2、實訓二：數據庫分析設計與建模
(1) 分析銀行 ATM 存取款系統實體
(2) 規范數據庫結構設計
- 第一范式（1NF）是指數據庫表的每一列都是不可分割的基本數據項，同一列中 不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。
? ? ? ?如在銀行客戶表 BankCustomer 中，不能將客戶信息都放在一列中顯示，也不能 將其中的兩列或多列在一列中顯示；客戶信息表的每一行只表示一個員工的信息，一個客戶的信息在表中只出現一次。簡而言之，第一范式就是無重復的列。 - 第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。
? ? ? ?如銀行交易信息表 BankDealInfo 中，不能把卡號設為主鍵因為一個卡號可以發生 多條交易記錄。要確定唯一的一條信息，必須重新定義一個和其它屬性無關的交易編 號。這樣要查詢一條交易信息。就可以用交易編號。簡而言之，第二范式就是屬性完全依賴于主鍵。 - 第三范式（3NF）要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字 信息。
? ? ? ? 如銀行卡表 BankCard 中，有了用戶Id 后。不能還添加用戶姓名等相關的用戶信 息。否則就會有大量的數據冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。
(3) 繪制 CDM 模型、生成 PDM 模型
3、實訓三：創建數據庫
(1) 創建數據庫
? ? ? ?使用 Create DataBase 語句創建“ATM 存取款機系統”數據庫BankDB，數據文件和日志文件保存在指定目錄下文件增長率為 15% 。
--創建BankDB數據庫，數據庫文件和日志文件均保存在文件夾D:\data下 --文件增長率均為%，數據文件起始大小為MB，日志文件起始大小為MB create database BankDB on primary ( name=N'BankDB', filename=N'C:\data\BankDB.mdf', size=5mb, filegrowth=15% ) log on ( name=N'BankDB_log', filename=N'C:\data\BankDB_log.ldf', size=2mb, filegrowth=15% )(2) 創建各個數據表及相關的約束
? ? ? ?根據銀行業務，分析表中每個列相應的約束要求，為每個表添加各種約束。要求創建表時要求檢測是否存在表結構，如果存在，則先刪除再創建。
use BankDB go--判斷銀行客戶信息表是否存在,若存在則刪除 --sysobjects 系統對象表。保存當前數據庫的對象。 --OBJECT_ID()根據對象名稱返回該對象的id IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankCustomerInfo')) DROP TABLE BankCustomerInfo--創建BankCustomerInfo表 create table BankCustomerInfo( CustNum int identity(1,1) primary key not null, --客戶編號 CustName char(20) not null, --客戶姓名 CustID char(18) not null, --身份證號 CustTelephone char(20) not null, --客戶電話 CustAddress varchar(100) not null --客戶住址 )--判斷銀行卡表是否存在,若存在則刪除 IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankCardInfo')) DROP TABLE BankCardInfo--創建BankCardInfo表 create table BankCardInfo( CardID char(19) primary key not null, --銀行卡號 Password char(6) not null, --密碼 MoneyType char(5) not null, --貨幣類型 DepositType int not null, --存款類型 OpenDate date not null, --開戶日期 OpenMoney money not null, --開戶金額 CardLoss char(10) not null, --是否掛失 CustNum int not null, --客戶編號 CardMoney numeric(8,2) not null --卡內余額 )--判斷銀行交易信息表是否存在,若存在則刪除 IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankDealInfo')) DROP TABLE BankDealInfo--創建BankDealInfo表 create table BankDealInfo( DealNum int identity(1,1) primary key not null, --交易編號 CardID char(19) not null, --銀行卡號 DealDate date not null, --交易日期 DealMoney decimal(8,2) not null, --交易金額 DealType char(256) not null, --交易類型 DealNote varchar(1024) null --交易備注 )--判斷業務類型表是否存在,若存在則刪除 IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'BankBusinessType')) DROP TABLE BankBusinessType--創建BankBusinessType表 create table BankBusinessType( BusNum int identity(1,1) primary key not null, --業務編號 BusName char(20) not null, --業務名稱 BusDescribe varchar(100) null --業務描述 )--為BankCustomerInfo表添加約束 --添加check約束，身份證號前位必須是數字，后位可以是數字或者X alter table BankCustomerInfo add constraint ck_CustID check(left(CustID ,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(CustID ,1) like'[0-9]' or right(CustID,1) like 'X')) --客戶電話必須是固定電話號碼或者手機號 alter table BankCustomerInfo add constraint ck_CustTelephone check( CustTelephone like'[0-9][0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or CustTelephone like '[0-9][0-9][0-9]_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or CustTelephone like '1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') --銀行卡號前位必須為，后位為-9任意數字--為BankCardInfo表添加約束 alter table BankCardInfo add constraint ck_CardID check(CardID LIKE '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]') --密碼默認為 alter table BankCardInfo add constraint df_Password default('888888') for Password --貨幣類型默認為RMB alter table BankCardInfo add constraint df_MoneyType default('RMB') for MoneyType --開戶日期默認為當前時間 alter table BankCardInfo add constraint df_OpenDate default(getdate()) for OpenDate --開戶金額至少為元 alter table BankCardInfo add constraint ck_OpenMoney check(OpenMoney>=1) --是否掛失默認為’否‘ alter table BankCardInfo add constraint df_CardLoss default('否') for CardLoss(3) 添加外鍵約束和生成數據庫關系圖
添加子表外鍵約束
--添加外鍵約束 alter table BankCardInfo add constraint fk_DepositType foreign key(DepositType) references BankBusinessType(BusNum) alter table BankCardInfo add constraint fk_CustNum foreign key(CustNum) references BankCustomerInfo(CustNum) alter table BankDealInfo add constraint fk_CardID foreign key(CardID) references BankCardInfo(CardID)在 SQL SERVER 里自動生成數據庫關系圖，如下圖所示：
4、實訓四：創建觸發器和插入測試數據
(1) 創建級聯觸發器
創建 Insert 觸發器：
? ? ? ? 在交易信息表創建一個 insert 觸發器，當增加一條交易信息時，修改相應銀行卡
的存款余額。
創建Delete觸發器：
? ? ? ? 在交易信息表創建一個 Delete 觸發器，當刪除一條交易信息時，修改相應銀行卡
的存款余額。
創建 Update 觸發器
? ? ? ? 在交易信息表創建一個 Update 觸發器，當更新一條交易信息時，修改相應銀行 卡的存款余額。
(2) 插入數據表的測試數據
? ? ? ? 使用 T-SQL 語句向每個表插入如下所示測試數據，要保證業務數據的一致性和 完整性。
BankBusinessType 表的測試數據：
BankCustomerInfo 表的測試數據：
BankCardInfo表的測試數據：
insert into BankCardInfo (CardID,Password,MoneyType,DepositType,OpenDate,OpenMoney,CardLoss,CustNum,CardMoney) values ('1010 3576 1234 5678','197611','RMB',1,cast(dateadd(day,-(rand()*30),getdate()) as date),1000,'否',1,1000), ('1010 3576 1234 5688','197611','RMB',2,cast(dateadd(day,-(rand()*30),getdate()) as date),1000,'否',2,1500) go select * from BankCardInfo
BankDealInfo 表的測試數據：
5、實訓五：模擬常規業務
(1) 修改客戶密碼
? ? ? ?根據卡號修改指定 2 個客戶的銀行密碼，其中第一個客戶 1010 3576 1234 5678 密碼修改為 123456，第二個客戶 1010 3576 1234 5688 修改為 123123。
update BankCardInfo set Password='123465' where CardID='1010 3576 1234 5678' update BankCardInfo set Password='123123' where CardID='1010 3576 1234 5688' select CardID '銀行卡卡號',Password '密碼',MoneyType '貨幣類型',DepositType '儲蓄種類',OpenDate ' 開戶日期',OpenMoney '開戶金額', CardLoss '是否掛失',CustNum'客戶編號',CardMoney '存款金額' from BankCardInfo(2) 辦理銀行卡掛失
? ? ? ?卡號為 1010 3576 1234 5678 的銀行卡丟失，申請掛失。
update BankCardInfo set CardLoss='是' where CardID='1010 3576 1234 5678' select CardID '銀行卡卡號',Password'密碼',MoneyType '貨幣類型',BusName '儲蓄類型',OpenDate ' 開戶日期',OpenMoney '開戶金額', CardLoss '是否掛失',CustName'客戶姓名',CardMoney '存款 金額' from BankCardInfo inner join BankCustomerInfo on BankCardInfo.CustNum=BankCustomerInfo.CustNum inner join BankBusinessType on BankCardInfo.DepositType=BankBusinessType.BusNum(3) 統計銀行資金流通余額和盈利結算
存入代表資金流入，支取代表資金流出。
計算公式：資金流通余額=總存入金額-總支取金額
假定存款利率為千分之三，貸款利率為千分之八。
計算公式：盈利結算=總支取金額0.008-總存入金額0.003 。
要求創建一個存儲過程 proc_staticsBanlanceAndProfit 。
(4) 查詢本周開戶信息
查詢本周開戶的卡號，顯示該卡的相關信息。
--默認星期日作為一周的第一天.修改星期一為第一天 set datefirst 1 select CardID '銀行卡卡號',CustName '姓名', MoneyType '貨幣類型',OpenDate '開戶時間 ',BusName '儲蓄類型',OpenMoney '開戶金額',CardMoney '存款金額', case CardLoss when '是' then '掛失賬戶' when '否' then '正常賬戶' end '是否掛失' from BankCardInfo inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum where datediff(day,OpenDate,getdate())<datepart(weekday,getdate())(5) 查詢本月單次交易金額最高的卡號和總交易金額最高的卡號
查詢本月存、取款中單次交易金額最高的卡號信息。
select distinct BankDealInfo.CardID '銀行卡卡號',OpenDate '開戶日期',OpenMoney '開戶金額 ',DealMoney '單次最高金額' from BankCardInfo inner join BankDealInfo on BankDealInfo.CardID=BankCardInfo.CardID where DealMoney =(select max(DealMoney ) from BankDealInfo where datediff(month,DealDate,getdate())=0) select top 1 BankCardInfo.CardID '銀行卡卡號',OpenDate '開戶日期',OpenMoney '開戶金額 ',sum(DealMoney ) '總交易最高金額' from BankCardInfo inner join BankDealInfo on BankDealInfo.CardID=BankCardInfo.CardID where datediff(month,DealDate,getdate())=0 group by BankCardInfo.CardID,OpenDate,OpenMoney order by sum(DealMoney ) desc(6) 查詢掛失客戶
--子查詢 select CustName '客戶姓名',CustTelephone '聯系電話' from BankCustomerInfo where CustNum in (select CustNum from BankCardInfo where CardLoss='是') --內連接 select CustName '客戶姓名',CustTelephone '聯系電話' from BankCustomerInfo inner join BankCardInfo on BankCardInfo.CustNum = BankCustomerInfo.CustNum where CardLoss='是'(7) 催款提醒業務
? ? ? ?根據某種業務（如代繳電話費、代繳手機費或房貸等）的需要，每個月末，查詢出客戶賬戶上余額少于 10000 元，由銀行統一致電催款。
select CustName '客戶姓名',CustTelephone '聯系電話',CardMoney '存款金額' from BankCardInfo inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum where CardMoney<=100006、實訓六：創建、使用視圖
(1) 輸出銀行客戶記錄視圖VW_userInfo
? ? ? ?顯示的列名全為中文，要求先判斷該視圖是否存在，若存在，則先刪除。
if object_id('vw_userinfo','v') is not null drop view vw_userinfo go create view vw_userinfo as select CustNum '客戶編號',CustName '開戶名',CustID '身份證號',CustTelephone '電話號碼',CustAddress '居住地址' from BankCustomerInfo go select * from vw_userinfo(2) 輸出銀行卡記錄視圖VW_CardInfo
if object_id('VW_CardInfo','v') is not null drop view VW_CardInfo go create view VW_CardInfo as select CardID '銀行卡卡號',CustName '姓名', MoneyType '貨幣類型',BusName '儲蓄類型 ',OpenDate '開戶日期',CardMoney '存款金額',Password'密碼', CardLoss '是否掛失' from BankCardInfo inner join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum inner join BankBusinessType on BankBusinessType.BusNum = BankCardInfo.DepositType go select * from VW_CardInfo(3) 輸出銀行卡交易記錄視圖VW_TransInfo
if object_id('VW_TransInfo','v') is not null drop view VW_TransInfo go create view VW_TransInfo as select DealDate '交易日期',DealType '交易類型',CardID '銀行卡卡號',DealMoney'交易 金額',DealNote '備注' from BankDealInfo go select * from VW_TransInfo order by 交易日期(4) 根據客戶登錄名查詢該客戶賬戶信息VW_OneUserInfo
? ? ? ?根據客戶登錄名（采用實名制訪問銀行系統）查詢該客戶賬戶信息的視圖，利用SQL SERVER 系統函數 system_user 獲得數據庫用戶名。
if object_id('VW_OneUserInfo','v') is not null drop view VW_OneUserInfo go create view VW_OneUserInfo as select CustNum '客戶編號',CustName '開戶名',CustID '身份證號',CustTelephone '電話號碼',CustAddress '居住地址' from BankCustomerInfo where CustName=system_user go select * from VW_OneUserInfo7、實訓七：存儲過程實現業務處理
(1) 完成存款或取款業務
描述：
? ? ? ? 根據銀行卡號和交易金額實現銀行卡的存款和取款業務。
? ? ? ? 每一筆存款，取款業務都要計入銀行交易賬，并同時更新客戶的存款余額。
? ? ? ? 如果是取款業務，在記賬之前，要完成下面兩項數據的檢查驗證工作，如果檢查 不合格，那么中斷取款業務，給出提示信息后退出。
? ? ? ? 檢查客戶輸入的密碼是否正確。
? ? ? ? 賬戶取款金額是否大于當前存款額加 1 。
(2) 產生隨機卡號
? ? ? ? 創建存儲過程產生 8 位隨機數字，與前 8 位固定數字“1010 3576”連接，生成一個由 16 位數字組成的銀行卡號，并輸出。
if object_id('proc_RandCardID','p') is not null drop procedure proc_RandCardID go --創建隨機卡號的存儲過程 create procedure proc_RandCardID @randcardid char(19) output as declare @r numeric(8,8),@tmpstr char(10) --產生隨機種子=當前的月份數*100000+當前的秒數*1000+當前的毫秒數 set @r=rand(datepart(month,getdate())*100000+datepart(second,getdate())*1000+datepart(millisecond,getdate())) set @tmpstr=convert(char(10),@r) set @randcardid='1010 3576 '+substring(@tmpstr,3,4)+' '+substring(@tmpstr,7,4) go declare @mycardid1 char(19) exec proc_RandCardID @mycardid1 output print '產生隨機卡號為'+@mycardid1(3) 完成開戶業務
描述：
? ? ? ? 利用存儲過程為客戶開設 2 個銀行卡賬戶，開戶時需要提供客戶的信息有：開戶 名、身份證號、電話號碼、開戶金額、存款類型和地址。客戶的信息見表所示：
? ? ? ? 為成功開戶的客戶提供銀行卡，且銀行卡號唯一。
(4) 分頁顯示查詢交易數據
? ? ? ? 根據指定的頁數和每頁的記錄數分頁顯示交易數據。
if object_id('proc_PagingDisplay','p') is not null drop procedure proc_PagingDisplay go create procedure proc_PagingDisplay @page int,@count int as select rownumber '交易編號',DealDate '交易日期',DealType '交易類型',CardID '銀行卡卡號 ',DealMoney'交易金額' from (select row_number() over(order by DealNum) rownumber,DealDate,DealType,CardID,DealMoney from BankDealInfo) c where c.rownumber between (@page-1)*@count+1 and @page*@count go exec proc_PagingDisplay @page = 2,@count = 5(5) 統計未發生交易的賬戶
? ? ? ? 查詢統計指定時間段內沒有發生交易的賬戶信息
if object_id('proc_getwithouttrade','p') is not null drop procedure proc_getwithouttrade go create procedure proc_getwithouttrade @startdate datetime=null,@enddate datetime=null as declare @name char(16),@icno char(18),@tel char(15),@addr char(50),@moneysum money=0,@customersum int=0,@money int if (@startdate is null) set @startdate=convert(datetime,convert(char(8),getdate(),120)+'1') if (@enddate is null) set @enddate=getdate() --有的客戶未交易.所以用右連接或者全連接 declare cur_outtrade cursor for select distinct CustName,CustID,CustTelephone,CustAddress,CardMoney from BankDealInfo right join BankCardInfo on BankCardInfo.CardID=BankDealInfo.CardID right join BankCustomerInfo on BankCustomerInfo.CustNum = BankCardInfo.CustNum where BankDealInfo.CardID not in(select BankDealInfo.CardID from BankDealInfo where DealDate>=@startdate and DealDate<=@enddate) print convert(char(16),'客戶姓名')+convert(char(20),'身份證號')+convert(char(16),'電 話')+convert(char(20),'地址') open cur_outtrade fetch next from cur_outtrade into @name,@icno,@tel,@addr,@money while @@fetch_status=0 begin print convert(char(16),@name)+convert(char(20),@icno)+convert(char(16),@tel)+convert(char(20),@addr) set @moneysum=@moneysum+@money set @customersum=@customersum+1 fetch next from cur_outtrade into @name,@icno,@tel,@addr,@money end print '統計未發生交易的客戶' print '客戶人數:'+ltrim(str(@customersum))+',客戶總余額:'+ltrim(str(@moneysum)) close cur_outtrade deallocate cur_outtrade go exec proc_getwithouttrade @startdate='2015-8-19',@enddate='2015-9-19' exec proc_getwithouttrade8、實訓八：利用事務實現轉賬
? ? ? ? 使用存儲過程和事務實現轉賬業務，操作步驟如下所示：
? ? ? ? (1)從某一個賬戶支取一定金額的存款。
? ? ? ? (2)將支取金額存入到另一個指定的賬戶中。
? ? ? ? (3)分別打印此筆業務的轉出賬單和轉入賬單
總結
以上是生活随笔為你收集整理的课程设计：基于SQL Server的银行ATM 存取款机系统设计与实现的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 什么是大数据，大数据到底应该如何学？
- 下一篇： 【OpenCV 例程200篇】33. 图