--創建數據庫
use master
go
if exists(select * from sysdatabases where name = 'BankDB')
drop database BankDB
go
exec xp_cmdshell 'md E:\朱礦龍',NO_OUTPUT
create database BankDB
go
use BankDB
--創建帳戶信息表AccountInfo
if exists(select * from sysobjects where name = 'AccountInfo')
drop table AccountInfo
go
create table AccountInfo
(
CustID int identity(1,1) primary key,
CustName varchar(20) not null,
IDCard varchar(18) check(len(IDCard) = 15 or len(IDCard) = 18),
TelePhone varchar(13) check(len(TelePhone)=11 or len(TelePhone) like'[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or len(TelePhone) like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') ,
Address varchar(50) default '地址不詳'
)
go
--創建信用卡信息表CardInfo
if exists(select * from sysobjects where name = 'CardInfo')
drop table CardInfo
go
create table CardInfo
(
CardID varchar(19) check(len(CardID)=19) primary key,
CardPassWord varchar(6) default'888888',
CustID int references AccountInfo(CustID),
SaveType varchar(10) not null check(SaveType in('活期','定期')),
OpenDate datetime not null default(getdate()),
OpenMoney money not null check(OpenMoney >= 1),
LeftMoney money not null check(LeftMoney >= 1),
IsLoss varchar(2) not null check(IsLoss in('是','否')) default '否'
)
go
--創建交易信息表TransInfo
if exists(select * from sysobjects where name = 'TransInfo')
drop table TransInfo
go
create table TransInfo
(
CardID varchar(19) not null,
TransType varchar(4) not null check(TransType in('存入','支取')),
TransMoney money not null check(TransMoney > 0),
TransDate datetime default(getdate())
)
go

-------------------------------插入測試數據-------------------------------------
---為AccountInfo表插入測試數據
insert into AccountInfo values('孫悟空','422322123902140019','027-8888988','花果山')
insert into AccountInfo values('唐僧','422322001902140019','027-8536896','大唐')
insert into AccountInfo values('沙和尚','410334020157144719','13295654665','通天河')
---為CardInfo表插入測試數據
insert into CardInfo values('1027 3726 1536 1135',default,1,'定期',default,500.00,500.00,default)
insert into CardInfo values('1029 3526 1235 1235',default,2,'活期',default,1500.00,1500.00,default)
insert into CardInfo values('1324 7532 1536 1935',default,3,'活期',default,4500.00,4500.00,default)

---表的查看
select * from AccountInfo

select * from CardInfo

select * from TransInfo

-----------------T-SQL----------------------------
--孫悟空修改密碼
update CardInfo set CardPassWord = 611234 where CustID = 1

--孫悟空取錢
--事務開始
begin transaction tran_Qu
--定義一個用于記錄錯誤的變量
declare @tran_error int
set @tran_error = 0;
--將孫悟空交易進行記錄
insert into TransInfo values('1027 3726 1536 1135','支取',200.00,getdate())
set @tran_error = @tran_error + @@error
--從孫悟空的帳戶中減去200.00
update CardInfo set LeftMoney = LeftMoney - 200
where CardID = '1027 3726 1536 1135'
set @tran_error = @tran_error + @@error
if @tran_error <> 0
   begin
      --執行錯誤，回滾事務
      rollback transaction
      print '支取失敗，交易已取消'
   end
else
  begin
    --沒有發現錯誤，提交事務
    commit transaction
    print'交易成功，已保存新數據'
  end
go
select * from CardInfo where CustID = 1

--沙和尚存錢
begin transaction tran_bring
declare @tran_error int
set @tran_error = 0;
insert into TransInfo values('1324 7532 1536 1935','存入',1200.00,getdate())
set @tran_error = @tran_error + @@error
update CardInfo set LeftMoney = LeftMoney + 1200
where CardID = '1324 7532 1536 1935'
set @tran_error = @tran_error + @@error
if @tran_error <> 0
   begin
      rollback transaction
      print '存入失敗，交易已取消'
   end
else
  begin
    commit transaction
    print'交易成功，已保存新數據'
  end
go
select * from CardInfo where CustID = 3

--唐僧卡丟失
update CardInfo set IsLoss='是' where CustID = 2

--查詢最近10開戶的銀行卡信息
select * from CardInfo where datediff(dd,OpenDate,getdate()) <= 10

--查詢最大交易的卡信息
declare @maxMoney money
select @maxMoney = max(TransMoney) from TransInfo
select * from CardInfo where CardID in(select CardID from TransInfo where TransMoney = @maxMoney)

--查詢交易信息表中總的交易
declare @allMoney money
declare @QuMoney money
declare @CunMoney money
select @allMoney = sum(TransMoney) from TransInfo
select @QuMoney = sum(TransMoney) from TransInfo where TransType = '支取'
select @CunMoney = sum(TransMoney) from TransInfo where TransType = '存入'
print '總交易金額：' + convert(varchar(10),@allMoney)
print '支取交易金額：' + convert(varchar(10),@QuMoney)
print '存入交易金額：' + convert(varchar(10),@CunMoney)

--給交易信息表加上非聚集索引，并利用非聚集索引查詢數據
if exists(select * from sysobjects where name = 'IX_CardID')
drop index BankDB.IX_CardID
go
create nonclustered index IX_CardID
on TransInfo(CardID)
with fillfactor = 30
go
select * from TransInfo with(index = IX_CardID) where CardID = '1324 7532 1536 1935'

--查詢掛失的賬戶信息
select * from AccountInfo where CustID in(select CustID from CardInfo where IsLoss = '是')

--賬戶信息視圖
if exists(select * from sysobjects where name = 'view_AccountCardInfo')
drop view view_AccountCardInfo
go
create view view_AccountCardInfo
as
select AccountInfo.CustID '帳戶編號',CustName '帳戶姓名',IDCard'身份證號碼',TelePhone'客戶電話',Address'客戶地址',
        CardID'信用卡編號',SaveType'儲蓄類型',OpenDate'開戶日期',OpenMoney'開戶金額',IsLoss'是否掛失'
from AccountInfo join CardInfo
on AccountInfo.CustID = CardInfo.CustID
go
select * from view_AccountCardInfo

--交易信息視圖
if exists(select * from sysobjects where name = 'view_TransInfo')
drop view view_TransInfo
go
create view view_TransInfo
as
select CardID '卡號',TransType '交易類型',TransMoney '交易金額',TransDate '交易時間'
from TransInfo
go
go
select * from view_TransInfo

---------------第三階段----------------
--------T-SQL-------------
if exists(select name from sysobjects where name = 'Tri_TransInfo_Insert')
drop trigger Tri_TransInfo_Insert
go
create trigger Tri_TransInfo_Insert on TransInfo for insert
as
 declare @TempTransType varchar(10)     --定義臨時的變量存放交易類型
 declare @TempTransMoney money          --定義臨時的變量存放交易金額
 declare @TempCardID varchar(19)        --定義臨時的變量存放卡號
 declare @TempLeftMoney money           --定義臨時的變量存放客戶的余額
 
--從inserted臨時表中取出數據賦值
select @TempTransType = TransType,@TempTransMoney = TransMoney,@TempCardID = CardID
from inserted
select @TempLeftMoney = LeftMoney from CardInfo where CardID = @TempCardID

if(@TempTransType = '支取')      
  begin
    if(@TempLeftMoney - @TempTransMoney >=1)  
       begin
          update CardInfo set LeftMoney = @TempLeftMoney - @TempTransMoney where CardID = @TempCardID
          print '交易成功！'
       end
     else
       begin
          rollback transaction
          print '余額不足，交易失??！'
       end
  end
else
  begin
    update CardInfo set LeftMoney = @TempLeftMoney + @TempTransMoney where CardID = @TempCardID
    print '交易成功！'
  end
select @TempLeftMoney = LeftMoney from CardInfo where CardID = @TempCardID  
print '卡號：' + convert(varchar(19),@TempCardID) + '  余額：' + convert(varchar(10),@TempLeftMoney)
go
set nocount on   --不顯示語句影響記錄行數
--測試觸發器,沙和尚支取
insert into TransInfo(CardID,TransType,TransMoney) values('9645 9087 9371 4492','支取',500)
go

select * from TransInfo
select * from accountinfo
select * from CardInfo

--利用存儲過程實現備份交易信息的業務
if exists(select *from sysobjects where name = 'Proc_Backup_TransInfo')
    drop procedure Proc_Backup_TransInfo
go
create procedure Proc_Backup_TransInfo
@BackupDate datetime
as
declare @MyError int
set @MyError = 0
print '開始備份......'
if exists(select * from sysobjects where name ='Back_TransInfo')
begin
 begin tran 
 insert into Back_TransInfo select * from TransInfo where datediff(dd,TransDate,getdate()) >=0
set @MyError = @MyError + @@error
if @MyError != 0
   begin
       rollback transaction
      print '備份失敗'
   end
else
  begin
      commit transaction
       print'備份成功'
   end
  end
else
  begin
    begin tran 
    select * into Back_TransInfo from TransInfo where datediff(dd,TransDate,getdate()) >=0
    set @MyError = @MyError + @@error
if @MyError != 0
   begin
      rollback transaction
      print '備份失敗!'
   end
else
   begin
      commit transaction
      print '備份成功!'
   end
end
go
insert into TransInfo values('1324 7532 1536 1935','支取',500.00,getdate()) 
insert into TransInfo values('1324 7532 1536 1935','支取',500.00,getdate())
insert into TransInfo values('1324 7532 1536 1935','支取',500.00,getdate())
declare @BackDate datetime
set @BackDate = getDate()
exec Proc_Backup_TransInfo '2008-10-31'
go
select * from Back_TransInfo

--實現銀行卡的存儲過程
if exists(select * from sysobjects where name = 'Proc_GetCardID')
    drop procedure Proc_GetCardID
go
create procedure Proc_GetCardID
@CardID varchar(19) output
as
declare @TempRand numeric(18,16)
declare @TempStr varchar(18)
set @TempRand = rand(datepart(ms,getdate())*10000)
set @TempStr = convert(varchar(18),@TempRand)
set @CardID = substring(@TempStr,3,4)+' '+substring(@TempStr,7,4)+' '+substring(@TempStr,11,4)+' '+substring(@TempStr,15,4)
go
declare @MyCardID varchar(19)
exec Proc_GetCardID @MyCardID output
print '產生的隨即卡號是：' + @MyCardID
go

--實現開戶的存儲過程
if exists(select * from sysobjects where name = 'Proc_OpenAcount')
    drop procedure Proc_OpenAcount
go
create procedure Proc_OpenAcount
@CustName varchar(20),
@IDCard varchar(18),
@Telephone varchar(13),
@OpenMoney money = 1,
@SaveType varchar(10) = '活期',
@Address varchar(50)= '地址不詳'
as
 declare @MyCardID varchar(19)
 declare @MyCustID int
 exec Proc_GetCardID @MyCardID output
while(1=1)
 begin
 if exists(select * from CardInfo where CardID = @MyCardID)
 exec Proc_GetCardID @MyCardID output
else
 break
 end
insert into AccountInfo values(@CustName,@IDCard,@TelePhone,@Address)  
select @MyCustID = CustID from AccountInfo where IDCard = @IDCard      
insert into CardInfo values(@MyCardID,default,@MyCustID,@SaveType,default,@OpenMoney,@OpenMoney,default)
print '尊敬的客戶：' +@CustName +'開戶成功，卡號為：'+@MyCardId

print '產生的隨機卡號為：' + @MyCardID
exec Proc_OpenAcount '白骨精','245687556977812345','12478964568'
exec Proc_OpenAcount '嫦娥公主','745687476912812335','14796653141',@Address = '月亮'

----銷戶
if exists(select * from sysobjects where name = 'Proc_DropAcount')
    drop procedure Proc_DropAcount
go
create procedure Proc_DropAcount
 --@CardID varchar(19)
@IDCard varchar(18) --身份證號
as
declare @TempCustID int
declare @TempLeftMoney money
--select @TempCustID = CustID from CardInfo where CardID = @CardID 
--select @TempLeftMoney = LeftMoney from CardInfo where CardID = @CardID
print '該帳戶余額：' +convert(varchar(10),@TempLeftMoney)+'正在取出。。。'
delete from CardInfo where custid in(select custid from accountinfo where IDCard=@IDCard)
delete from  AccountInfo where IDCard=@IDCard
go
declare @CardID varchar(19)
select @CardID = CardID from CardInfo where CustID in(select CustID from AccountInfo where CustName = '唐僧')
exec Proc_DropAcount '422322001902140019'--根據身份證號刪除
go
---表的查看
select * from AccountInfo
select * from CardInfo