- -----------创建数据库----------------
- user master
- go
- if exists (select * from sysdatabases where name= 'bankSystem' )
- drop database bankSystem
- go
- create database bankSystem
- on primary
- (
- name='bank_data' ,
- filename='D:\bank\bank_data.mdf' ,
- size=5,
- filegrowth=15%
- )
- log on
- (
- name='bank_log' ,
- filename='D:\bank\bank_log.ldf' ,
- size=5,
- filegrowth=15%
- )
- go
- ----------------建表并添加约束----------------
- use bankSystem
- go
- ---------------------表userInfo--------------
- if exists (select * from sysObjects where name= 'userInfo' )
- drop table userInfo
- go
- create table userInfo
- (
- customerID int identity(1,1) not null ,
- customerName varchar(30) not null ,
- PID varchar(18) not null ,
- telephone varchar(13) not null ,
- address varchar(50)
- )
- go
- alter table userInfo
- add constraint PK_customID primary key (customerID)
- alter table userInfo
- add constraint UQ_pid unique (PID)
- alter table userInfo
- add constraint CK_pid check (pid like '4206[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]' or
- pid like '4206[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
- alter table userInfo
- add constraint CK_telephone check (telephone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
- telephone like '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' )
- ------------表cardInfo---------------
- if exists (select * from sysObjects where name= 'cardInfo' )
- drop table cardInfo
- go
- create table cardInfo
- (
- cardID varchar(18) not null ,
- curType varchar(10) not null ,
- savingType varchar(10) not null ,
- openDate datetime not null ,
- openMoney money not null ,
- balance money not null ,
- pass varchar(6) not null ,
- IsReportLoss bit not null ,
- customerID int not null
- )
- go
- alter table cardInfo
- add constraint PK_cardID primary key (cardID)
- alter table cardInfo
- add constraint CK_cardID check (cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9]' )
- alter table cardInfo
- add constraint DF_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 (pass like '[0-9][0-9][0-9][0-9][0-9][0-9]' )
- alter table cardInfo
- add constraint DF_pass default (888888) 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)
- alter table cardInfo
- add constraint CK_savingType check (savingType like '活期' or savingType like '定活两期' or savingType like '定期' )
- ---------------表transInfo-------------
- if exists (select * from sysObjects where name= 'transInfo' )
- drop table transInfo
- go
- create table transInfo
- (
- transDate datetime not null ,
- cardID varchar(18) not null ,
- transType varchar(4) not null ,
- transMoney money not null ,
- remark text
- )
- 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 like '存入' or transType like '支取' )
- alter table transInfo
- add constraint CK_transMoney check (transMoney >0)
- go
- --------------插入数据----------------
- insert into userInfo values ('张三' , '420656789012345' , '010-67898978' , '北京海淀' )
- insert into userInfo values ('李四' , '420645678912345678' , '0478-44443333' , default )
- insert into cardInfo values ('1010 3576 1212 113' , default , '定期' , '2007-10-10 11:54:36.812' ,1,1, default , default ,2)
- insert into cardInfo values ('1010 3576 1234 567' , default , '活期' , '2007-10-10 11:58:45.352' ,1000,1000, default , default ,1)
- insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:36.812' , '支取' , '1010 3576 1234 567' ,900)
- update cardInfo set balance=balance-900 where cardID= '1010 3576 1234 567'
- insert into transInfo (transDate,transType,cardID,transMoney) values ('2007-10-24 11:56:54.245' , '存入' , '1010 3576 1212 113' ,5000)
- update cardInfo set balance=balance+5000 where cardID= '1010 3576 1212 113'
- ---------------修改密码--------------
- update cardInfo set pass= '123456' where cardID= '1010 3576 1234 567'
- update cardInfo set pass= '123123' where cardID= '1010 3576 1212 113'
- -------------是否挂失------------
- update cardInfo set IsReportLoss=1 where cardID= '1010 3576 1212 113'
- ---------------统计银行资金流通余额和盈利结算--------------
- declare @inMoney money,@outMoney money,@sumMoney money,@rateEnd money
- select @inMoney=sum(transMoney) from transInfo where transType='存入'
- select @outMoney=sum(transMoney) from transInfo where transType='支取'
- set @sumMoney=@inMoney-@outMoney
- set @rateEnd=@outMoney*0.008-@inMoney*0.003
- print '银行流余额总计为:' +convert(varchar(20),@sumMoney)+ 'RMB'
- print '盈利结算结果为:' +convert(varchar(20),@rateEnd)+ 'RMB'
- ---------------查询本周开户卡号----------------
- select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())
- ----------------查询本月交易金额最高卡号------------
- select * from userInfo where customerID in
- (select customerID from cardInfo where cardID in
- (select cardID from cardInfo where datepart(wk,openDate)=datepart(wk,getDate())))
- ----------------本月交易金额最大的卡号为-----------------
- select cardID from transInfo where transMoney=(
- select max(transMoney) from transInfo where datepart(mm,transDate)=datepart(mm,getDate()))
- ---------------------挂失帐号客户信息----------------
- select customerName as 客户姓名 telephone as 电话 from userInfo where customerID in
- (select customerID from cardInfo where isreportloss = 1)
- -------------------------催款提醒业务-------------
- select customerName as 客户姓名,telephone as 电话,balance as 卡上余额 from userInfo inner join cardInfo
- on userInfo.customerID=cardInfo.customerID where userInfo.customerID in
- (select customerID from cardInfo where balance < 200)
- ------------创建索引--------------
- if exists (select * from sysindexes where name= 'IX_cardid' )
- drop index transInfo.IX_cardid
- go
- create nonclustered index IX_cardid
- on transInfo (cardID)
- with fillfactor=70
- go
- select * from transInfo with(index=IX_cardid) where cardID ='1010 3576 1212 113'
- ------------创建视图-------------
- if exists (select * from sysobjects where name= 'view_userInfo' )
- drop view view_userInfo
- go
- create view view_userInfo
- as
- select 客户编号=customerID,开户姓名=customerName,身份证号=PID,电话=telephone,地址=address from userinfo
- go
- if exists (select * from sysobjects where name= 'view_cardInfo' )
- drop view view_cardInfo
- go
- create view view_cardInfo
- as
- select 卡号=cardID,货币=curType,存款类型=savingType,开户日期=openDate,
- 余额=balance,密码=pass,是否挂失=isreportloss,客户编号=customerID from cardInfo
- go
- if exists (select * from sysobjects where name= 'view_transInfo' )
- drop view view_transInfo
- 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
- go
- ---------------创建存储过程----------------
- if exists (select * from sysobjects where name= 'proc_getAndoutMoney' )
- drop proc proc_getAndoutMoney
- go
- create proc proc_getAndoutMoney
- @cardID varchar(19),
- @money money,
- @type char (4),
- @inputpass char (6)
- as
- if (@type= '支取' )
- begin
- begin transaction
- declare @error int
- set @error = 0
- if (@inputpass=(select pass from cardInfo where cardID=@cardID))
- begin
- update cardInfo set balance=balance-@money where cardID=@cardID
- set @error=@error+@@error
- insert into transInfo values (getDate(),@cardID,@type,@money,default )
- set @error=@error+@@error
- end
- else
- print '密码错误'
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- end
- else
- begin
- update cardInfo set balance=balance+@money where cardID=@cardID
- insert into transInfo values (getDate(),@cardID,@type,@money,default )
- end
- go
- exec proc_getAndoutMoney '1010 3576 1212 113' ,500, '存入' , '000000'
- exec proc_getAndoutMoney '1010 3576 1234 567' ,300, '支取' , '123446'
- select * from cardInfo where cardID='1010 3576 1212 113'
- -----------随机产生卡号----------
- use bankSystem
- go
- if exists (select * from sysobjects where name= 'proc_randCardID' )
- drop proc proc_randCardID
- go
- create proc proc_randCardID
- @randCardID varchar(18) output
- as
- declare @rand numeric(15,8)
- declare @tempStr varchar(16)
- select @rand=rand((datepart(mm,getDate())*100000+datepart(ss,getDate())*1000+datepart(ms,getDate())))
- set @tempStr=convert(varchar(16),@rand)
- set @randCardID= '1010 3576 ' +subString(@tempStr,3,4)+ ' ' +subString(@tempStr,7,3)
- go
- declare @mycardID varchar(18)
- exec proc_randCardID @mycardID output
- print '随机产生卡号为:' +@mycardID
- -------------开户存储过程--------------
- set nocount on
- if exists (select * from sysobjects where name= 'proc_openAccount' )
- drop proc proc_openAccount
- go
- create proc proc_openAccount
- @customerName varchar(8),
- @PID varchar(18),
- @telephone char (13),
- @openMoney money,
- @savingType char (8),
- @address varchar(50)=' '
- as
- declare @cardID varchar(18)
- declare @customerID int
- while (1=1)
- begin
- exec proc_randCardID @cardID output
- if not exists (select * from cardInfo where cardID=@cardID)
- break
- else
- continue
- end
- if (@openMoney<1)
- return
- else
- begin
- begin transaction
- declare @error int
- set @error=0
- insert into userInfo values (@customerName,@PID,@telephone,@address)
- set @error=@error+@@error
- select @customerID=customerID from userInfo where customerName=@customerName
- set @error=@error+@@error
- insert into cardInfo (cardID,savingType,openMoney,balance,customerID)
- values (@cardID,@savingType,@openMoney,@openMoney,@customerID)
- set @error=@error+@@error
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- end
- print '尊敬的客户,开户成功!系统为您产生的随机卡号为:' +@cardID+ ' 开户日期 ' +
- convert(varchar(30),getDate(),111)+' 开户金额 ' +convert(varchar(10),@openMoney)
- go
- exec proc_openAccount '王五' , '420656889012678' , '2222-63598978' ,1000, '活期' , '河南新乡'
- -----------------转账事务------------------
- if exists (select * from sysobjects where name= 'proc_transfer' )
- drop proc proc_transfer
- go
- create proc proc_transfer
- @card1 char (18),
- @card2 char (18),
- @outMoney money
- as
- begin transaction
- declare @error int
- set @error=0
- update cardInfo set balance=balance-@outMoney where cardID=@card1
- set @error=@error+@@error
- insert into transInfo values (getDate(),@card1,'支取' ,@outMoney, default )
- set @error=@error+@@error
- update cardInfo set balance=balance+@outMoney where cardID=@card2
- set @error=@error+@@error
- insert into transInfo values (getDate(),@card2,'存入' ,@outMoney, default )
- set @error=@error+@@error
- if (@error<>0)
- rollback transaction
- else
- commit transaction
- go
- exec proc_transfer '1010 3576 1212 113' , '1010 3576 1234 567' ,2000
- select * from transInfo where cardID='1010 3576 1212 113'
- select * from transInfo where cardID='1010 3576 1234 567'
- -------------------创建登录帐号和数据库用户---------------------
- exec sp_addlogin 'sysAdmin' , '1234'
- exec sp_grantdbaccess 'sysAdmin' , 'sysAdminBankUser'
- grant insert,update,delete,select on userInfo,cardInfo,transInfo to sysAdminBankUser
补上触发器:
- --update触发器
- set nocount on
- if exists (select * from sysobjects where name = 'trig_ppr_Update' )
- drop trigger trig_ppr_Update
- go
- create trigger trig_ppr_Update
- on PlanPropertyRelation
- --with encryption --加密
- for update
- as
- declare @Plans_Id int ,@currentId int ,@childId int ,@childNewLevel int
- declare @oldFatherId int ,@newFatherId int
- select @oldFatherId=PPR_ProExtend_ID from deleted
- select @newFatherId=PPR_ProExtend_ID from inserted
- if (@oldFatherId=@newFatherId)
- return ;
- else
- begin
- select @Plans_Id=Plans_ID,@childId=Property_ID,@currentId=PlanPropertyRelation_ID from inserted
- delete from PropertyValuesRelation where PlanPropertyRelation_ID=@currentId
- end
- go
- --删除触发器,删除前触发
- set nocount on
- if exists (select * from sysobjects where name = 'trig_ppr_BeforeDelete' )
- drop trigger trig_ppr_BeforeDelete
- go
- create trigger trig_ppr_BeforeDelete
- on PlanPropertyRelation
- --with encryption --加密
- INSTEAD OF delete
- as
- declare @oldId int ,@error int
- select @oldId=PlanPropertyRelation_ID from deleted
- delete from PropertyValuesRelation where PlanPropertyRelation_ID=@oldId
- delete from PlanPropertyRelation where PlanPropertyRelation_ID=@oldId
- go
-