主要是实现一个银行自动提款机系统,..不是很难.需要的看看!S2学习的所有SQL技术基本都用上了.
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='bank')
DROP DATABASE bank
GO
CREATE DATABASE bank
GO
USE bank
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='userInfo')
DROP TABLE userInfo
GO
CREATE TABLE userInfo
(
customerID INT IDENTITY(1,1) NOT NULL, --顾客标号
customerName VARCHAR(10) NOT NULL, --顾客姓名
PID VARCHAR(18) NOT NULL, --身份证号
telephone VARCHAR(13) NOT NULL, --电话
address VARCHAR(50) NULL --地址
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='cardInfo')
DROP TABLE cardInfo
GO
CREATE TABLE cardInfo
(
cardID VARCHAR(19) NOT NULL, --卡号格式 1010 3576 **** ****
curType VARCHAR(10) NOT NULL, --货币种类
savingType VARCHAR(4) NULL, --存款类型
openDate DATETIME NOT NULL, --开户日期
openMoney MONEY NOT NULL, --开户金额
balance MONEY NOT NULL, --余额
pass VARCHAR(20) NOT NULL, --密码
IsReportLoss BIT NOT NULL, --是否挂失
customerID INT NOT NULL --顾客编号,外键
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='transInfo')
DROP TABLE transInfo
GO
CREATE TABLE transInfo
(
transDate DATETIME NULL, --交易时间必须 默认当前
cardID VARCHAR(19) NOT NULL, --卡号 外间 可重复索引
transType VARCHAR(4) NOT NULL, --交易类型 存入 之取
transMoney MONEY NOT NULL, --交易金额 大于0
remark VARCHAR(100) NULL, --备注
)
GO
/*建立个表约束*/
ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY (customerID) --住键约束
ALTER TABLE userInfo ADD CONSTRAINT CK_PID CHECK (LEN(PID)=15 OR LEN(PID)=18) --身份证为15到18人
ALTER TABLE userInfo ADD CONSTRAINT UP_PID UNIQUE (PID) --身份证唯一
ALTER TABLE userInfo ADD CONSTRAINT CK_telephone CHECK (LEN(telephone)=11 OR telephone LIKE '____-________') --11
GO
ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY (cardID)
ALTER TABLE cardInfo ADD CONSTRAINT CK_cardID CHECK (cardID LIKE '1010 3576 ____ ____' AND LEN(cardID)=19)
ALTER TABLE cardInfo ADD CONSTRAINT DE_curType DEFAULT 'RMB' FOR curType
ALTER TABLE cardInfo ADD CONSTRAINT DF_openDate DEFAULT getdate() FOR openDate
ALTER TABLE cardInfo ADD CONSTRAINT CK_openMoney CHECK (openMoney >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_balance CHECK (balance >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_pass CHECK (LEN(pass)>=6)
ALTER TABLE cardInfo ADD CONSTRAINT DF_pass DEFAULT 666666 FOR pass
ALTER TABLE cardInfo ADD CONSTRAINT DF_IsReportLoss DEFAULT 0 FOR IsReportLoss
ALTER TABLE cardInfo ADD CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES userInfo(customerID)
GO
ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT getdate() FOR transDate
ALTER TABLE transInfo ADD CONSTRAINT FK_cardID FOREIGN KEY (cardID) REFERENCES cardInfo(cardID)
ALTER TABLE transInfo ADD CONSTRAINT CK_transType CHECK (transType IN ('存入' ,'支取'))
ALTER TABLE transInfo ADD CONSTRAINT CK_transMoney CHECK (transMoney > 0)
GO
/*插入数据*/
INSERT INTO userInfo (customerName,PID,telephone,address) VALUES ('张三','123456789012345','0010-67898978','北京海淀')
INSERT INTO userInfo (customerName,PID,telephone) VALUES ('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1234 5678','活期',1000,1000,1)
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1212 1134','定期',1,1,2)
GO
/*张三取900,李四存5000*/
DECLARE @cidz VARCHAR(19),@cidl VARCHAR(19)
/*获取张三和李四的卡号*/
SELECT @cidz=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
SELECT @cidl=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
/*分别支取和存入*/
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidz,'支取',900)
UPDATE cardInfo SET balance=balance-900 WHERE cardID=@cidz
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidl,'存入',5000)
UPDATE cardInfo SET balance=balance+5000 WHERE cardID=@cidl
GO
/*===============常规业务模拟,修改密码===============*/
/*张三修改密码123456 李四修改密码123456*/
UPDATE cardInfo SET pass='123456' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
UPDATE cardInfo SET pass='123123' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*李四卡挂失*/
UPDATE cardInfo SET IsReportLoss=1 WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*统计银行的资金流通余额和盈利*/
DECLARE @sumin INT,@sumput INT
SELECT @sumin=SUM(transMoney) FROM transInfo WHERE transType='存入'
SELECT @sumput=SUM(transMoney) FROM transInfo WHERE transType='支取'
PRINT '银行流通余额总计为:'+CONVERT(VARCHAR(20),@sumin-@sumput)
PRINT '盈利结算为:'+CONVERT(VARCHAR(20),@sumput*0.008-@sumin*0.003)
GO
/*求本周内开户的银行卡号*/
SELECT cardID FROM cardInfo WHERE openDate > (getdate()-datepart(dw,getdate())+2)
/*查询本月交易金额最高的卡号*/
SELECT cardID,SUM(transMoney) FROM transInfo WHERE transType='支出' GROUP BY cardID
/*查询挂失帐号的客户信息*/
GO
SELECT * FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)
/*催款提醒业务*/
GO
SELECT 客户姓名=customerName,联系电话=telephone,帐上余额=balance FROM userInfo INNER JOIN cardInfo
ON userInfo.customerID=cardInfo.customerID
WHERE balance<200
GO
/*7创建所以和试图*/
/*给交易表的卡号cardID字段创建重复索引,以便加速查询,充填因为为70%*/
IF EXISTS (SELECT * FROM sysindexes WHERE name='index_cardID')
DROP INDEX transInfo.index_cardID
GO
CREATE NONCLUSTERED INDEX index_cardID
ON transInfo(cardID)
GO
/*按指定索引查询 张三 (卡号 10103576 1212 1134)的交易记录*/
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1212 1134'
/*创建试图方便客户查看*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_userInfo')
DROP VIEW view_userInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_cardInfo')
DROP VIEW view_cardInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_transInfo')
DROP VIEW view_transInfo
GO
CREATE VIEW view_userInfo
AS
SELECT 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address FROM userInfo
GO
CREATE VIEW view_cardInfo
AS
SELECT 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss FROM cardInfo
GO
CREATE VIEW view_transInfo
AS
SELECT 交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark FROM transInfo
GO
SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
/*============创建触发器trig_trans============*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='trig_trans')
DROP TRIGGER trig_trans
GO
CREATE TRIGGER trig_trans
ON transInfo
FOR INSERT
AS
DECLARE @cardid VARCHAR(19),@money MONEY,@type VARCHAR(4)
SELECT @cardid=cardID,@money=transMoney,@type=transType FROM inserted
IF @type='存入'
BEGIN
UPDATE cardInfo SET balance=balance+@money WHERE cardID=@cardid
PRINT '交易成功帐号余额'
SELECT balance FROM cardInfo WHERE cardID=@cardid
END
ELSE
BEGIN
IF ((SELECT balance FROM cardInfo WHERE cardID=@cardid)-@money)>=1
BEGIN
UPDATE cardInfo SET balance=balance-@money WHERE cardID=@cardid
PRINT '交易成功帐号余额'
SELECT balance FROM cardInfo WHERE cardID=@cardid
END
ELSE
BEGIN
RAISERROR ('账户余额不足,交易失败',16,1)
ROLLBACK TRAN
END
END
GO
/*===========测试触发器===============*/
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1234 5678','支取',1000,DEFAULT)
GO
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1212 1134','存入',200,DEFAULT)
GO
/*========创建存储过程,实现取钱和存钱============*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_takeMoney')
DROP PROC proc_takeMoney
GO
CREATE PROC proc_takeMoney
@card VARCHAR(19), --卡号
@money MONEY, --交易金额
@type VARCHAR(4), --交易类型
@pass VARCHAR(20) = NULL --密码,默认空
AS
IF @type='支取'
BEGIN
IF ((SELECT pass FROM cardInfo WHERE cardID=@card)=@pass)
INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
ELSE
PRINT '密码输入错误,交易失败'
END
IF @type='存入'
INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
GO
DECLARE @acardid VARCHAR(19),@bcardid VARCHAR(19)
SELECT @acardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_takeMoney @acardid,300,'支取','123356'
GO
DECLARE @bcardid VARCHAR(19)
SELECT @bcardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
EXEC proc_takeMoney @bcardid,500,'存入'
GO
/*产生随机卡号proc_randCardID*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_randCardID')
DROP PROC proc_randCardID
GO
CREATE PROC proc_randCardID
@randCardID VARCHAR(19) OUTPUT
AS
DECLARE @r numeric(15,8),@rd INT
SELECT @r=RAND((DATEPART(mm,GETDATE())*10000+DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))
SELECT @rd=@r*100000000
SELECT @randCardID='1010 3576 '+ LEFT(@rd,4)+' '+RIGHT(@rd,4)
GO
DECLARE @randcardID VARCHAR(19)
EXEC proc_randCardID @randcardID OUTPUT
PRINT '随机卡号为:'+convert(varchar(19),@randcardID)
GO
/*开户存储过程proc_openAccount*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_openAccount')
DROP PROC proc_openAccount
GO
CREATE PROC proc_openAccount
@uname VARCHAR(10), --开户姓名
@upid VARCHAR(18), --身份证
@telephone VARCHAR(13), --电话
@address VARCHAR(50) = NULL, --地址
@money MONEY, --开户金额
@type VARCHAR(4) --开户类型
AS
DECLARE @ucid INT,@randcardid VARCHAR(19),@error INT
SET @error=0
IF EXISTS (SELECT * FROM userInfo WHERE PID=@upid)
BEGIN
RAISERROR ('此账户已经开户,请不要重复开户',16,1)
RETURN
END
INSERT INTO userInfo VALUES (@uname,@upid,@telephone,@address)
SET @error=@error+@@error
SELECT @ucid=customerID FROM userInfo WHERE PID=@upid
WHILE (1=1)
BEGIN
EXEC proc_randcardID @randcardid OUTPUT
SET @error=@error+@@error
IF NOT EXISTS (SELECT * FROM cardInfo WHERE cardID=@randcardid)
BREAK
END
INSERT INTO cardInfo VALUES (@randcardid,DEFAULT,@type,DEFAULT,@money,@money,DEFAULT,DEFAULT,@ucid)
SET @error=@error+@@error
IF @error>0
BEGIN
PRINT '发生错误,开户失败'
ROLLBACK TRANSACTION
END
PRINT '尊敬的客户,开户成功!系统为您产生的随机卡号为'+convert(varchar(19),@randcardid)
GO
/*===测试开户存储过程==========*/
EXEC proc_openAccount '王五','334456889012678','2222-63598978','河南新乡',1000,'活期'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666','河南新乡',1,'定期'
/*====================创建转账================*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_transfer')
DROP PROC proc_transfer
GO
CREATE PROC proc_transfer
@cardout VARCHAR(19), --转出卡号
@cardin VARCHAR(19), --转入卡号
@money MONEY --金额
AS
BEGIN TRAN
DECLARE @sumerror INT
SET @sumerror=0
INSERT INTO transInfo VALUES (DEFAULT,@cardout,'支取',@money,DEFAULT)
SET @sumerror=@sumerror+@@error
INSERT INTO transInfo VALUES (DEFAULT,@cardin,'存入',@money,DEFAULT)
SET @sumerror=@sumerror+@@error
IF @sumerror>0
BEGIN
PRINT '发生错误,转账失败,事务回滚'
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
PRINT '提交成功'
END
SELECT @money=balance FROM cardInfo WHERE cardID=@cardout
PRINT '卡号:'+convert(varchar(19),@cardout)+' 余额:'+convert(varchar(19),@money)
SELECT @money=balance FROM cardInfo WHERE cardID=@cardin
PRINT '卡号:'+convert(varchar(19),@cardin)+' 余额:'+convert(varchar(19),@money)
GO
/*测试转账事务*/
DECLARE @cardout VARCHAR(19),@cardin VARCHAR(19)
SELECT @cardout=cardID FROM cardInfo
WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
SELECT @cardin=cardID FROM cardInfo
WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_transfer @cardout,@cardin,2000
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
/*创建登录帐号*/
EXEC sp_addlogin 'sysAdmin','1234'
EXEC sp_grantdbaccess 'sysAdmin','sysAdminDBUser'
GRANT SELECT,INSERT,UPDATE,DELETE ON transInfo TO sysAdminDBUser
SET NOCOUNT ON
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='bank')
DROP DATABASE bank
GO
CREATE DATABASE bank
GO
USE bank
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='userInfo')
DROP TABLE userInfo
GO
CREATE TABLE userInfo
(
customerID INT IDENTITY(1,1) NOT NULL, --顾客标号
customerName VARCHAR(10) NOT NULL, --顾客姓名
PID VARCHAR(18) NOT NULL, --身份证号
telephone VARCHAR(13) NOT NULL, --电话
address VARCHAR(50) NULL --地址
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='cardInfo')
DROP TABLE cardInfo
GO
CREATE TABLE cardInfo
(
cardID VARCHAR(19) NOT NULL, --卡号格式 1010 3576 **** ****
curType VARCHAR(10) NOT NULL, --货币种类
savingType VARCHAR(4) NULL, --存款类型
openDate DATETIME NOT NULL, --开户日期
openMoney MONEY NOT NULL, --开户金额
balance MONEY NOT NULL, --余额
pass VARCHAR(20) NOT NULL, --密码
IsReportLoss BIT NOT NULL, --是否挂失
customerID INT NOT NULL --顾客编号,外键
)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='transInfo')
DROP TABLE transInfo
GO
CREATE TABLE transInfo
(
transDate DATETIME NULL, --交易时间必须 默认当前
cardID VARCHAR(19) NOT NULL, --卡号 外间 可重复索引
transType VARCHAR(4) NOT NULL, --交易类型 存入 之取
transMoney MONEY NOT NULL, --交易金额 大于0
remark VARCHAR(100) NULL, --备注
)
GO
/*建立个表约束*/
ALTER TABLE userInfo ADD CONSTRAINT PK_customerID PRIMARY KEY (customerID) --住键约束
ALTER TABLE userInfo ADD CONSTRAINT CK_PID CHECK (LEN(PID)=15 OR LEN(PID)=18) --身份证为15到18人
ALTER TABLE userInfo ADD CONSTRAINT UP_PID UNIQUE (PID) --身份证唯一
ALTER TABLE userInfo ADD CONSTRAINT CK_telephone CHECK (LEN(telephone)=11 OR telephone LIKE '____-________') --11
GO
ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIMARY KEY (cardID)
ALTER TABLE cardInfo ADD CONSTRAINT CK_cardID CHECK (cardID LIKE '1010 3576 ____ ____' AND LEN(cardID)=19)
ALTER TABLE cardInfo ADD CONSTRAINT DE_curType DEFAULT 'RMB' FOR curType
ALTER TABLE cardInfo ADD CONSTRAINT DF_openDate DEFAULT getdate() FOR openDate
ALTER TABLE cardInfo ADD CONSTRAINT CK_openMoney CHECK (openMoney >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_balance CHECK (balance >= 1)
ALTER TABLE cardInfo ADD CONSTRAINT CK_pass CHECK (LEN(pass)>=6)
ALTER TABLE cardInfo ADD CONSTRAINT DF_pass DEFAULT 666666 FOR pass
ALTER TABLE cardInfo ADD CONSTRAINT DF_IsReportLoss DEFAULT 0 FOR IsReportLoss
ALTER TABLE cardInfo ADD CONSTRAINT FK_customerID FOREIGN KEY (customerID) REFERENCES userInfo(customerID)
GO
ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT getdate() FOR transDate
ALTER TABLE transInfo ADD CONSTRAINT FK_cardID FOREIGN KEY (cardID) REFERENCES cardInfo(cardID)
ALTER TABLE transInfo ADD CONSTRAINT CK_transType CHECK (transType IN ('存入' ,'支取'))
ALTER TABLE transInfo ADD CONSTRAINT CK_transMoney CHECK (transMoney > 0)
GO
/*插入数据*/
INSERT INTO userInfo (customerName,PID,telephone,address) VALUES ('张三','123456789012345','0010-67898978','北京海淀')
INSERT INTO userInfo (customerName,PID,telephone) VALUES ('李四','321245678912345678','0478-44443333')
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1234 5678','活期',1000,1000,1)
INSERT INTO cardInfo (cardID,savingType,openMoney,balance,customerID) VALUES ('1010 3576 1212 1134','定期',1,1,2)
GO
/*张三取900,李四存5000*/
DECLARE @cidz VARCHAR(19),@cidl VARCHAR(19)
/*获取张三和李四的卡号*/
SELECT @cidz=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
SELECT @cidl=cardID FROM cardInfo WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
/*分别支取和存入*/
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidz,'支取',900)
UPDATE cardInfo SET balance=balance-900 WHERE cardID=@cidz
INSERT INTO transInfo (cardID,transType,transMoney) VALUES (@cidl,'存入',5000)
UPDATE cardInfo SET balance=balance+5000 WHERE cardID=@cidl
GO
/*===============常规业务模拟,修改密码===============*/
/*张三修改密码123456 李四修改密码123456*/
UPDATE cardInfo SET pass='123456' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='张三')
UPDATE cardInfo SET pass='123123' WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*李四卡挂失*/
UPDATE cardInfo SET IsReportLoss=1 WHERE customerID= (SELECT customerID FROM userInfo WHERE customerName='李四')
GO
/*统计银行的资金流通余额和盈利*/
DECLARE @sumin INT,@sumput INT
SELECT @sumin=SUM(transMoney) FROM transInfo WHERE transType='存入'
SELECT @sumput=SUM(transMoney) FROM transInfo WHERE transType='支取'
PRINT '银行流通余额总计为:'+CONVERT(VARCHAR(20),@sumin-@sumput)
PRINT '盈利结算为:'+CONVERT(VARCHAR(20),@sumput*0.008-@sumin*0.003)
GO
/*求本周内开户的银行卡号*/
SELECT cardID FROM cardInfo WHERE openDate > (getdate()-datepart(dw,getdate())+2)
/*查询本月交易金额最高的卡号*/
SELECT cardID,SUM(transMoney) FROM transInfo WHERE transType='支出' GROUP BY cardID
/*查询挂失帐号的客户信息*/
GO
SELECT * FROM userInfo WHERE customerID IN (SELECT customerID FROM cardInfo WHERE IsReportLoss=1)
/*催款提醒业务*/
GO
SELECT 客户姓名=customerName,联系电话=telephone,帐上余额=balance FROM userInfo INNER JOIN cardInfo
ON userInfo.customerID=cardInfo.customerID
WHERE balance<200
GO
/*7创建所以和试图*/
/*给交易表的卡号cardID字段创建重复索引,以便加速查询,充填因为为70%*/
IF EXISTS (SELECT * FROM sysindexes WHERE name='index_cardID')
DROP INDEX transInfo.index_cardID
GO
CREATE NONCLUSTERED INDEX index_cardID
ON transInfo(cardID)
GO
/*按指定索引查询 张三 (卡号 10103576 1212 1134)的交易记录*/
SELECT * FROM transInfo (INDEX=index_cardID) WHERE cardID='1010 3576 1212 1134'
/*创建试图方便客户查看*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_userInfo')
DROP VIEW view_userInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_cardInfo')
DROP VIEW view_cardInfo
GO
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='view_transInfo')
DROP VIEW view_transInfo
GO
CREATE VIEW view_userInfo
AS
SELECT 客户编号=customerID,开户名=customerName,身份证号=PID,电话号码=telephone,居住地址=address FROM userInfo
GO
CREATE VIEW view_cardInfo
AS
SELECT 卡号=cardID,货币种类=curType,存款类型=savingType,开户日期=openMoney,余额=balance,密码=pass,是否挂失=IsReportLoss FROM cardInfo
GO
CREATE VIEW view_transInfo
AS
SELECT 交易日期=transDate,交易类型=transType,卡号=cardID,交易金额=transMoney,备注=remark FROM transInfo
GO
SELECT * FROM view_userInfo
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
/*============创建触发器trig_trans============*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='trig_trans')
DROP TRIGGER trig_trans
GO
CREATE TRIGGER trig_trans
ON transInfo
FOR INSERT
AS
DECLARE @cardid VARCHAR(19),@money MONEY,@type VARCHAR(4)
SELECT @cardid=cardID,@money=transMoney,@type=transType FROM inserted
IF @type='存入'
BEGIN
UPDATE cardInfo SET balance=balance+@money WHERE cardID=@cardid
PRINT '交易成功帐号余额'
SELECT balance FROM cardInfo WHERE cardID=@cardid
END
ELSE
BEGIN
IF ((SELECT balance FROM cardInfo WHERE cardID=@cardid)-@money)>=1
BEGIN
UPDATE cardInfo SET balance=balance-@money WHERE cardID=@cardid
PRINT '交易成功帐号余额'
SELECT balance FROM cardInfo WHERE cardID=@cardid
END
ELSE
BEGIN
RAISERROR ('账户余额不足,交易失败',16,1)
ROLLBACK TRAN
END
END
GO
/*===========测试触发器===============*/
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1234 5678','支取',1000,DEFAULT)
GO
INSERT INTO transInfo VALUES (DEFAULT,'1010 3576 1212 1134','存入',200,DEFAULT)
GO
/*========创建存储过程,实现取钱和存钱============*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_takeMoney')
DROP PROC proc_takeMoney
GO
CREATE PROC proc_takeMoney
@card VARCHAR(19), --卡号
@money MONEY, --交易金额
@type VARCHAR(4), --交易类型
@pass VARCHAR(20) = NULL --密码,默认空
AS
IF @type='支取'
BEGIN
IF ((SELECT pass FROM cardInfo WHERE cardID=@card)=@pass)
INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
ELSE
PRINT '密码输入错误,交易失败'
END
IF @type='存入'
INSERT INTO transInfo VALUES (DEFAULT,@card,@type,@money,DEFAULT)
GO
DECLARE @acardid VARCHAR(19),@bcardid VARCHAR(19)
SELECT @acardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_takeMoney @acardid,300,'支取','123356'
GO
DECLARE @bcardid VARCHAR(19)
SELECT @bcardid=cardID FROM cardInfo WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
EXEC proc_takeMoney @bcardid,500,'存入'
GO
/*产生随机卡号proc_randCardID*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_randCardID')
DROP PROC proc_randCardID
GO
CREATE PROC proc_randCardID
@randCardID VARCHAR(19) OUTPUT
AS
DECLARE @r numeric(15,8),@rd INT
SELECT @r=RAND((DATEPART(mm,GETDATE())*10000+DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))
SELECT @rd=@r*100000000
SELECT @randCardID='1010 3576 '+ LEFT(@rd,4)+' '+RIGHT(@rd,4)
GO
DECLARE @randcardID VARCHAR(19)
EXEC proc_randCardID @randcardID OUTPUT
PRINT '随机卡号为:'+convert(varchar(19),@randcardID)
GO
/*开户存储过程proc_openAccount*/
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_openAccount')
DROP PROC proc_openAccount
GO
CREATE PROC proc_openAccount
@uname VARCHAR(10), --开户姓名
@upid VARCHAR(18), --身份证
@telephone VARCHAR(13), --电话
@address VARCHAR(50) = NULL, --地址
@money MONEY, --开户金额
@type VARCHAR(4) --开户类型
AS
DECLARE @ucid INT,@randcardid VARCHAR(19),@error INT
SET @error=0
IF EXISTS (SELECT * FROM userInfo WHERE PID=@upid)
BEGIN
RAISERROR ('此账户已经开户,请不要重复开户',16,1)
RETURN
END
INSERT INTO userInfo VALUES (@uname,@upid,@telephone,@address)
SET @error=@error+@@error
SELECT @ucid=customerID FROM userInfo WHERE PID=@upid
WHILE (1=1)
BEGIN
EXEC proc_randcardID @randcardid OUTPUT
SET @error=@error+@@error
IF NOT EXISTS (SELECT * FROM cardInfo WHERE cardID=@randcardid)
BREAK
END
INSERT INTO cardInfo VALUES (@randcardid,DEFAULT,@type,DEFAULT,@money,@money,DEFAULT,DEFAULT,@ucid)
SET @error=@error+@@error
IF @error>0
BEGIN
PRINT '发生错误,开户失败'
ROLLBACK TRANSACTION
END
PRINT '尊敬的客户,开户成功!系统为您产生的随机卡号为'+convert(varchar(19),@randcardid)
GO
/*===测试开户存储过程==========*/
EXEC proc_openAccount '王五','334456889012678','2222-63598978','河南新乡',1000,'活期'
EXEC proc_openAccount '赵二','213445678912342222','0760-44446666','河南新乡',1,'定期'
/*====================创建转账================*/
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='proc_transfer')
DROP PROC proc_transfer
GO
CREATE PROC proc_transfer
@cardout VARCHAR(19), --转出卡号
@cardin VARCHAR(19), --转入卡号
@money MONEY --金额
AS
BEGIN TRAN
DECLARE @sumerror INT
SET @sumerror=0
INSERT INTO transInfo VALUES (DEFAULT,@cardout,'支取',@money,DEFAULT)
SET @sumerror=@sumerror+@@error
INSERT INTO transInfo VALUES (DEFAULT,@cardin,'存入',@money,DEFAULT)
SET @sumerror=@sumerror+@@error
IF @sumerror>0
BEGIN
PRINT '发生错误,转账失败,事务回滚'
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
PRINT '提交成功'
END
SELECT @money=balance FROM cardInfo WHERE cardID=@cardout
PRINT '卡号:'+convert(varchar(19),@cardout)+' 余额:'+convert(varchar(19),@money)
SELECT @money=balance FROM cardInfo WHERE cardID=@cardin
PRINT '卡号:'+convert(varchar(19),@cardin)+' 余额:'+convert(varchar(19),@money)
GO
/*测试转账事务*/
DECLARE @cardout VARCHAR(19),@cardin VARCHAR(19)
SELECT @cardout=cardID FROM cardInfo
WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='李四')
SELECT @cardin=cardID FROM cardInfo
WHERE customerID=(SELECT customerID FROM userInfo WHERE customerName='张三')
EXEC proc_transfer @cardout,@cardin,2000
SELECT * FROM view_cardInfo
SELECT * FROM view_transInfo
/*创建登录帐号*/
EXEC sp_addlogin 'sysAdmin','1234'
EXEC sp_grantdbaccess 'sysAdmin','sysAdminDBUser'
GRANT SELECT,INSERT,UPDATE,DELETE ON transInfo TO sysAdminDBUser