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 ('lecky',111111,1000000)
　　insert into bank (bname,bpassword,bmoney) values ('drld',222222,1)
　　insert into bank (bname,bpassword,bmoney) values ('junjun',333333,7551)
　　insert into bank (bname,bpassword,bmoney) values ('whuang',444444,80000)
　　insert into bank (bname,bpassword,bmoney) values ('lee',555555,756)
　　insert into bank (bname,bpassword,bmoney) values ('chou',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