模拟银行系统(数据库设计)

--1.创建数据库
--判断数据库db_bank是否存在,如果存在,先删除该数据库
if EXISTS(select * from sysdatabases where name='db_bank')
	DROP DATABASE db_bank
go

--创建数据库
CREATE DATABASE[db_bank]
ON PRIMARY
(
	NAME=N'db_bank',
	FILENAME=N'd:\Bank\Database\db_bank.mdf',
	size=10MB,
	MAXSIZE=100MB,
	FILEGROWTH=10%
)
LOG ON
(
	NAME=N'db_bank_log',
	FILENAME=N'd:\Bank\Database\db_bank_log.ldf',
	size=10MB,
	MAXSIZE=1024MB,
	FILEGROWTH=10%
)


 

--2.创建表
use db_bank
go

--创建用户表
CREATE TABLE UserInfo
(
	ID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY,      	 --编号 主键 自动编号
	UserName NVARCHAR(10) NOT NULL,					 	 --用户姓名
	PersonID VARCHAR(18)  NOT NULL UNIQUE,			 	 --身份证号,长度是15或者18,唯一
	Telephone VARCHAR(13) NOT NULL,					  	 --电话,格式为XXXX-XXXXXXXX或11位手机号
	[Address] NVARCHAR(50),							 	  --住址
	CHECK(LEN(PersonID)=15 OR LEN(PersonID)=18),
	CHECK(SUBSTRING(Telephone,4,1)='-' OR SUBSTRING(Telephone,5,1)='-' OR LEN(Telephone)=11)	
)

--创建银行卡信息表
CREATE TABLE CardInfo
(
	CardNumber VARCHAR(15) NOT NULL PRIMARY KEY,        				--卡号,主键,前4位为9876,后11位自动生成
	OpenDate   DATE NOT NULL DEFAULT(GETDATE()),		 				--开户日期,默认为当前日期
	OpenMoney  DECIMAL(18,2) NOT NULL,					  				--开户金额,大于或等于1
	Balance    DECIMAL(18,2) NOT NULL,					  				--余额,大于或者等于0
	[Password] VARCHAR(6) NOT NULL DEFAULT('888888'),   				--密码,6位数密码,默认为88888888
	IsLock	   BIT        NOT NULL DEFAULT(1),			  				--是否锁定,默认为1
	CustomerID INT        NOT NULL FOREIGN KEY REFERENCES UserInfo(ID),--客户端编号,用户信息表编号字段的外键,表示该卡对应的客户编号。一位客户允许办理多张卡
	CHECK(LEN(CardNumber)=15),
	CHECK(OpenMoney>=1),
	CHECK(Balance>=0))

--创建交易记录表
CREATE TABLE TransInfo
(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,									--编号,主键,自动编号
	TransDate DATETIME NOT NULL DEFAULT(GETDATE()),								--交易日期,默认为系统当前日期
	CardNumber VARCHAR(15) NOT NULL FOREIGN KEY REFERENCES CardInfo(CardNumber),--卡号,银行卡信息表外键,可重复索引
	TransType NVARCHAR(5) NOT NULL,												--交易类型,可选项为:存入,支取,转入,转出
	TransMoney DECIMAL(18,2) NOT NULL,											--交易金额,大于0
	Remark NVARCHAR(200),														--备注,可以保存转账对方帐号等附加信息	
	CHECK(TransType IN('存入','支出','转入','转出')),
	CHECK(TransMoney>0)
)

 


--3.存储过程编写

--生成银行卡号存储过程
----------------------------------------------------------------------------------------------
use db_bank
go

--生成银行卡号存储过程
CREATE PROCEDURE Proc_CreateCardNumber
@CN VARCHAR(15) OUTPUT
AS
BEGIN
	DECLARE @r BIGINT
	/*生成卡号*/
	SET @r=RAND((DATEPART(ss,GETDATE())+DATEPART(ms,GETDATE())))*100000000000
	SET @CN=CONVERT(VARCHAR,@r)
	
	/*如果银行卡信息表中存在该卡号,重新生成卡号*/
	while(EXISTS(select * from CardInfo where CardNumber = @cn))
	BEGIN
			SET @r=RAND((DATEPART(ss,GETDATE())+DATEPART(ms,GETDATE())))*100000000000
			SET @CN=CONVERT(VARCHAR,@r)	
	END

END


--执行开户功能存储过程
----------------------------------------------------------------------------------------------


--执行开户功能存储过程
/*
该存储过程接收用户姓名、身份证号、联系电话、联系地址、开户金额、初始密码
如果UserInfo表中存在指定用户身份证号相同,则不再创建新用户,直接使用原用户信息。
*/

CREATE PROCEDURE Proc_CreateCard
@UserName  VARCHAR(10),				--开户姓名
@PersonID  VARCHAR(18),				--身份证号
@Telephone VARCHAR(13),				--联系电话
@Money     DECIMAL(18,2),			--开户金额
@Address   NVARCHAR(50)='',			--地址
@Password  VARCHAR(6)='88888888',	--密码
@CN		   VARCHAR(15)=''OUTPUT		--开户卡号,系统自动生成
AS
BEGIN
		DECLARE @UID INT
		--判断身份证是否开过账户
		IF (EXISTS(select * from UserInfo where [email protected]))
			select @UID= ID from UserInfo where [email protected]
		ELSE
			BEGIN
			--插入 姓名,身份证,电话,地址 
			INSERT INTO UserInfo VALUES(@UserName,@PersonID,@Telephone,@Address)
			SET @[email protected]@IDENTITY
			PRINT @UID 
			END
		
		--执行存储过程生成银行卡号	
		EXEC Proc_CreateCardNumber @CN OUTPUT
		SET @CN='9876'[email protected]
		
		--插入银行卡信息到银行卡表
		INSERT INTO CardInfo(CardNumber,OpenMoney,Balance,[Password],IsLock,CustomerID)
					VALUES(@CN,@Money,@Money,@Password,1,@UID)	
END


--存款存储过程
---------------------------------------------------------------------------------------------

--存款存储过程
CREATE PROCEDURE Proc_SaveMoney
@CN    VARCHAR(15),
@Money DECIMAL(18,2)
AS

BEGIN
	--对银行卡信息表的卡号更新余额操作
	UPDATE CardInfo
	SET [email protected]
	where [email protected]
	
	--插入交易记录表,银行卡号,交易金额
	INSERT INTO TransInfo(CardNumber,TransType,TransMoney)
				VALUES(@CN,'存入',@Money)

END



--取款存储过程
---------------------------------------------------------------------------------------------

--取款存储过程
CREATE PROCEDURE Proc_GetMoney
@CN    VARCHAR(15),
@Money DECIMAL(18,2)
AS

BEGIN
	--对银行卡信息表的卡号更新余额操作
	UPDATE CardInfo
	SET [email protected]
	where [email protected]
	
	IF(@@ERROR=0)
	BEGIN
			--插入交易记录表,银行卡号,交易金额
			INSERT INTO TransInfo(CardNumber,TransType,TransMoney)
					VALUES(@CN,'支出',@Money)
	
	END
END


--转账存储过程
-----------------------------------------------------------------------------------------------


--转账存储过程
CREATE PROCEDURE Proc_TransFerAccount
@OutNumber VARCHAR(15),		--源卡号
@InNumer   VARCHAR(15),		--目的卡号
@Money	   DECIMAL(18,2)    --转账金额

AS
BEGIN
		--事物开始
		BEGIN TRANSACTION
		
		--源卡号金额扣除
		UPDATE CardInfo
		SET [email protected]
		where [email protected]
		
		--目的卡号金额增加
		UPDATE CardInfo
		SET [email protected]
		where [email protected]
		
		--目的卡号转入记录
		INSERT INTO TransInfo(CardNumber,TransType,TransMoney,Remark)
					VALUES(@InNumer,'转入',@Money,@OutNumber)
		
		--源卡号转出记录			
		INSERT INTO TransInfo(CardNumber,TransType,TransMoney,Remark)
					VALUES(@OutNumber,'转出',@Money,@InNumer)
					
		IF(@@ERROR=0)
			COMMIT TRANSACTION			--事物成功
		ELSE
			ROLLBACK TRANSACTION		--事物失败,回滚操作

							
END
--------------------------------------------------------------------------------------------------


 

--3.存储过程编写

--生成银行卡号存储过程
----------------------------------------------------------------------------------------------
use db_bank
go

--生成银行卡号存储过程
CREATE PROCEDURE Proc_CreateCardNumber
@CN VARCHAR(15) OUTPUT
AS
BEGIN
	DECLARE @r BIGINT
	/*生成卡号*/
	SET @r=RAND((DATEPART(ss,GETDATE())+DATEPART(ms,GETDATE())))*100000000000
	SET @CN=CONVERT(VARCHAR,@r)
	
	/*如果银行卡信息表中存在该卡号,重新生成卡号*/
	while(EXISTS(select * from CardInfo where CardNumber = @cn))
	BEGIN
			SET @r=RAND((DATEPART(ss,GETDATE())+DATEPART(ms,GETDATE())))*100000000000
			SET @CN=CONVERT(VARCHAR,@r)	
	END

END


--执行开户功能存储过程
----------------------------------------------------------------------------------------------


--执行开户功能存储过程
/*
该存储过程接收用户姓名、身份证号、联系电话、联系地址、开户金额、初始密码
如果UserInfo表中存在指定用户身份证号相同,则不再创建新用户,直接使用原用户信息。
*/

CREATE PROCEDURE Proc_CreateCard
@UserName  VARCHAR(10),				--开户姓名
@PersonID  VARCHAR(18),				--身份证号
@Telephone VARCHAR(13),				--联系电话
@Money     DECIMAL(18,2),			--开户金额
@Address   NVARCHAR(50)='',			--地址
@Password  VARCHAR(6)='888888',	--密码
@CN		   VARCHAR(15)=''OUTPUT		--开户卡号,系统自动生成
AS
BEGIN
		DECLARE @UID INT
		--判断身份证是否开过账户
		IF (EXISTS(select * from UserInfo where [email protected]))
			select @UID= ID from UserInfo where [email protected]
		ELSE
			BEGIN
			--插入 姓名,身份证,电话,地址 
			INSERT INTO UserInfo VALUES(@UserName,@PersonID,@Telephone,@Address)
			SET @[email protected]@IDENTITY
			PRINT @UID 
			END
		
		--执行存储过程生成银行卡号	
		EXEC Proc_CreateCardNumber @CN OUTPUT
		SET @CN='9876'[email protected]
		
		--插入银行卡信息到银行卡表
		INSERT INTO CardInfo(CardNumber,OpenMoney,Balance,[Password],IsLock,CustomerID)
					VALUES(@CN,@Money,@Money,@Password,1,@UID)	
END


--存款存储过程
---------------------------------------------------------------------------------------------

--存款存储过程
CREATE PROCEDURE Proc_SaveMoney
@CN    VARCHAR(15),
@Money DECIMAL(18,2)
AS

BEGIN
	--对银行卡信息表的卡号更新余额操作
	UPDATE CardInfo
	SET [email protected]
	where [email protected]
	
	--插入交易记录表,银行卡号,交易金额
	INSERT INTO TransInfo(CardNumber,TransType,TransMoney)
				VALUES(@CN,'存入',@Money)

END



--取款存储过程
---------------------------------------------------------------------------------------------

--取款存储过程
CREATE PROCEDURE Proc_GetMoney
@CN    VARCHAR(15),
@Money DECIMAL(18,2)
AS

BEGIN
	--对银行卡信息表的卡号更新余额操作
	UPDATE CardInfo
	SET [email protected]
	where [email protected]
	
	IF(@@ERROR=0)
	BEGIN
			--插入交易记录表,银行卡号,交易金额
			INSERT INTO TransInfo(CardNumber,TransType,TransMoney)
					VALUES(@CN,'支出',@Money)
	
	END
END


--转账存储过程
-----------------------------------------------------------------------------------------------


--转账存储过程
CREATE PROCEDURE Proc_TransFerAccount
@OutNumber VARCHAR(15),		--源卡号
@InNumer   VARCHAR(15),		--目的卡号
@Money	   DECIMAL(18,2)    --转账金额

AS
BEGIN
		--事物开始
		BEGIN TRANSACTION
		
		--源卡号金额扣除
		UPDATE CardInfo
		SET [email protected]
		where [email protected]
		
		--目的卡号金额增加
		UPDATE CardInfo
		SET [email protected]
		where [email protected]
		
		--目的卡号转入记录
		INSERT INTO TransInfo(CardNumber,TransType,TransMoney,Remark)
					VALUES(@InNumer,'转入',@Money,@OutNumber)
		
		--源卡号转出记录			
		INSERT INTO TransInfo(CardNumber,TransType,TransMoney,Remark)
					VALUES(@OutNumber,'转出',@Money,@InNumer)
					
		IF(@@ERROR=0)
			COMMIT TRANSACTION			--事物成功
		ELSE
			ROLLBACK TRANSACTION		--事物失败,回滚操作

							
END
--------------------------------------------------------------------------------------------------


 

--插入数据
use db_bank
go

DECLARE @cardNumber VARCHAR(15)
EXEC Proc_CreateCard 
@UserName='张月关',
@PersonID='410156190012056548',
@Telephone='010-32586458',
@Address='北京海定',
@money=100,
@[email protected] OUTPUT
PRINT '卡号为:'[email protected]

go

DECLARE @cardNumber VARCHAR(15)
EXEC Proc_CreateCard 
@UserName='李四平',
@personID='120325201206023254',
@Telephone='12222222222',
@Address='河南郑州',
@money=1,
@[email protected] OUTPUT
PRINT '卡号为:'[email protected]

go
DECLARE @cardNumber VARCHAR(15)
EXEC Proc_CreateCard
@UserName='祝涛',
@PersonID='325156198201253288',
@Telephone='0371-25863214',
@Address='河南郑州',
@money=20000,
@[email protected] OUTPUT 
PRINT '卡号为:'[email protected]

go

DECLARE @cardNumber VARCHAR(15)
EXEC Proc_CreateCard
@UserName='刘明',
@PersonID='658254195511153265',
@Telephone='0521-32651478',
@Address='河南郑州',
@money=1000,
@[email protected] OUTPUT
PRINT '卡号为:'[email protected]

go


 

 

 

 

--存款操作
EXEC Proc_SaveMoney '987672650464229',5000
EXEC Proc_SaveMoney '987672669097200',1200
EXEC Proc_SaveMoney '987672682140280',1000
EXEC Proc_SaveMoney '987672700773251',1500


 

 

 

 

--取款操作
EXEC Proc_GetMoney '987672650464229',100
EXEC Proc_GetMoney '987672669097200',200
EXEC Proc_GetMoney '987672682140280',300
EXEC Proc_GetMoney '987672700773251',400


 

--转账操作
EXEC Proc_TransferAccount '987672650464229','987672669097200',100


 

--查询交易记录视图
CREATE VIEW View_TransInfo
AS
select u.UserName,T.* from TransInfo T,UserInfo U,CardInfo C
where T.CardNumber=C.CardNumber AND C.CustomerID=U.ID

--执行查询交易记录
select * from View_TransInfo where CardNumber='987672650464229'


阅读更多

更多精彩内容