SQL之ATM機業務預存程序

#1  SQL之ATM機業務預存程序

 

/*建立帳戶表*/

/*ATM機業務！*/
create table bank
(
bid int primary key identity(1000,1),
--流水號
bcid numeric(15,0) default rand()*1000000000000000 unique check(bcid>=100000000000000),
--銀行卡號，僅限15位元字,應為系統自動分配
--此處以rand函數取隨機值，*1後15個零後可以保證其最大值不會超過15位，check約束為14個零
--確保其最小值為15位，加unique唯一約束防止隨機數取到重複值
bname varchar(10),
--戶主名
bpassword numeric(6,0),
--密碼，僅限6位元字
bmoney money
--帳戶餘額
)
--select * from bank

--drop table bank
insert into bank (bname,bpassword,bmoney) values ('李仕翰',111111,1000000)
insert into bank (bname,bpassword,bmoney) values ('杜剛',222222,1)
insert into bank (bname,bpassword,bmoney) values ('張俊',333333,7551)
insert into bank (bname,bpassword,bmoney) values ('王黃',444444,80000)
insert into bank (bname,bpassword,bmoney) values ('李軍',555555,756)
insert into bank (bname,bpassword,bmoney) values ('周鯤鵬',666666,890)
--此處插入有可能被check約束報錯，主要是bcid取隨機數有可能取值不夠15位，重複執行即可


/*開戶*/
create proc padd @name varchar(10),@pass numeric(6,0),@money money
--@name,@pass,@money參數1帳戶名稱，參數2開戶密碼，參數3開戶存款
as
begin
if len(ltrim(rtrim(@name)))=0
begin
print '你有名字沒啊？'
return
end
--防止使用者名稱為空白或空格
if @pass<100000 or @pass>999999

begin
print '請輸入6位密碼，就是你左手手指頭數再加一個，謝謝'
return
end
if(@money<10)

begin
print '大哥，有10塊錢沒？開戶最少要10塊錢'
return
end

insert into bank select rand()*1000000000000000,@name,@pass,@money
--插入新記錄，即開戶
if @@rowcount<>1

begin
print '出了點點小錯誤，請重新操作，不好意思啦！'
return
--此處如果報錯，仍然是隨機數取不夠位，被check約束阻止的原因
end
else

begin
print '開戶成功啦！'
end
end

exec padd '張偉',777777,10
--參數1帳戶名稱，參數2開戶密碼，參數3開戶存款
--drop proc padd

/*存款*/

create proc plus @cid numeric(15,0),@pass numeric(6,0),@money money
--參數1卡號，ATM機讀卡時應該是讀取其卡號，參數2密碼，參數3為存款額
as
begin
if convert(int,@money)%100<>0 or @money=0
begin
print '本自動存款機只接受面額為100元大團結！'
return
end
--對100模數，保證存款額可被100整除，需要convert強制轉換為int型
update bank set bmoney=bmoney+@money where bcid=@cid and bpassword=@pass

if @@rowcount<>1
--如影響行數不是1行，則可能出錯，包括密碼有誤的情況
begin
print '您的密碼有誤！或者您的銀行卡存在問題，請重新輸入或者聯絡發卡銀行'
return
end
else
begin
print '存款成功！您剛剛存入了'+convert(varchar,@money)+'，謝謝送錢啦！'
--@money為money型，如連接字串則需要convert轉換為varchar
end
end

declare @t numeric(15,0)
set @t=(select bcid from bank where bid=1001)
--以上了兩個語句可以不如此執行，但需要查出該使用者的bcid隨機值，
--弟兄們各自建表時，此值應該各不一樣，所以加上以上兩句便於調試，主要是將隨機值先賦值給變數，以下題目相同
exec plus @t,222222,0
--drop proc plus

/*查詢餘額*/
create proc find @cid numeric(15,0),@pass numeric(6,0)
as
begin
declare @temp int
set @temp=(select bmoney from bank where (bcid=@cid) and (bpassword=@pass))
--取帳戶的的餘額值賦給@temp
if @@rowcount<>1
begin
print '您的密碼有誤！或者您的銀行卡存在問題，請重新輸入或者聯絡發卡銀行'
return
end
print '您的餘額還有'+convert(varchar,@temp)
--強制轉換為varchar用於連接字串
end

declare @t numeric(15,0)
set @t=(select bcid from bank where bid=1002)
--以上了兩個語句可以不如此執行，但需要查出該使用者的bcid隨機值，
--弟兄們各自建表時，此值應該各不一樣，所以加上以上兩句便於調試，主要是將隨機值先賦值給變數，以下題目相同
exec find @t,333333
--drop proc find

/*修改密碼*/

create proc change @cid numeric(15,0),@pass numeric(6,0),@newpass numeric(6,0)
--參數1卡號參數2原密碼參數3新密碼
as
begin
if @newpass<100000 or @newpass>999999

begin
print '請輸入6位密碼，就是你左手手指頭數再加一個，謝謝'
return
end

update bank set bpassword=@newpass where (bcid=@cid) and (bpassword=@pass)
--修改其密碼
if @@rowcount<>1
--驗證原密碼是否有誤在此處執行有誤則update影響行數應為0行
begin
print '您的原密碼有誤！或者您的銀行卡存在問題，請重新輸入或者聯絡發卡銀行'
return
end
else
begin
print '您的密碼已經被你改啦！'
end
end
declare @t numeric(15,0)
set @t=(select bcid from bank where bid=1004)
--以上了兩個語句可以不如此執行，但需要查出該使用者的bcid隨機值，
--弟兄們各自建表時，此值應該各不一樣，所以加上以上兩句便於調試，主要是將隨機值先賦值給變數，以下題目相同
exec change @t,555555,787878
--drop proc change

/*取款*/
create proc dec @cid numeric(15,0),@pass numeric(6,0),@money money
as
begin
if convert(int,@money)%50<>0 or @money=0
--對50模數，保證取款額為50的整數倍（也會為100的整數倍）
begin
print '本自動取款機只接受面額為50元或100元的人民幣呀！'
return
end
declare @temp money
set @temp=(select bmoney from bank where (bcid=@cid) and (bpassword=@pass))
if (@temp-@money)<0
--如餘額小於取款額，則餘額不足
begin
print '您的餘額不夠啦！！！'
return
end
update bank set bmoney=bmoney-@money where bcid=@cid and bpassword=@pass
--將餘額減去取款額度，更新
if @@rowcount<>1
begin
print '您的密碼有誤！或者您的銀行卡存在問題，請重新輸入或者聯絡發卡銀行'
return
end
else
begin
print '取款成功！您剛剛取出了'+convert(varchar,@money)+'，請趕緊拿卡，要不然我就吃了！'
end
end

declare @t numeric(15,0)
set @t=(select bcid from bank where bid=1006)
--以上了兩個語句可以不如此執行，但需要查出該使用者的bcid隨機值，
--弟兄們各自建表時，此值應該各不一樣，所以加上以上兩句便於調試，主要是將隨機值先賦值給變數，以下題目相同
exec dec @t,444444,50000

/*銷戶*/

create proc pdel @cid numeric(15,0),@pass numeric(6,0)
--@name,@pass,@money
as
begin

if @pass<100000 or @pass>999999

begin
print '請輸入6位密碼，就是你左手手指頭數再加一個，謝謝'
return
end

delete from bank where (bcid=@cid) and (bpassword=@pass)
--刪除該使用者記錄，即銷戶
if @@rowcount<>1

begin
print '出了點點小錯誤，請重新操作，不好意思啦！'
return
end
else

begin
print '您已經和我們銀行沒啥關係了！'
end
end

declare @t numeric(15,0)
set @t=(select bcid from bank where bid=1005)
--以上了兩個語句可以不如此執行，但需要查出該使用者的bcid隨機值，
--弟兄們各自建表時，此值應該各不一樣，所以加上以上兩句便於調試，主要是將隨機值先賦值給變數，以下題目相同
exec pdel @t,666666