1开户 insert
a) 余额充足 不加入欠费表
b) 余额不足 加入欠费表
2充值 update
c) 余额足够 从欠费表中删除
d) 余额不足 欠费表保留
3消费 update
e) 余额足够 无操作
f) 余额不足 加入欠费表
4.销户 delete
g) 未欠费 账户表中删除
h) 已欠费 账户表中删除 欠费表中删除
-------2013.7.18星期四 综合题:手机欠费
----建数据库
use master;
--判断要创建的数据库是否已存在
if exists(select * from sysdatabases where name = 'TELDB')
--如果存在则删除之前的数据库
drop database TELDB ;
create database TELDB
on primary
(
name = 'TELDB_data',
filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TELDB\TELDB_data.mdf' ,
size = 5,
filegrowth = 1,
maxsize = 20
)
log on
(
name = 'TELDB_log',
filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TELDB\TELDB_log.ldf' ,
size = 1,
filegrowth = 10%,
maxsize = 5
)
go
use TELDB;
if exists(select * from sysobjects where name = 'USER')
--如果存在则删除之前的数据库
drop table [USER] ;
create table [USER]
(
Account int identity(1000,1),
TelNo char(11),
balance money,
Name varchar(20)
)
go
if exists(select * from sysobjects where name = 'qianfei')
--如果存在则删除之前的数据库
drop table qianfei ;
create table qianfei
(
Account int,
TelNo char(11),
Name varchar(20),
)
go
Alter table [User]
Add constraint pk_USER_Account primary key(Account),
constraint ck_USER_balance check(balance >= 0),
constraint UQ_USER_TelNo unique(TelNo)
go
Alter table qianfei
Add constraint FK_qianfei_Account_USER_Account foreign key (Account) References [USER](Account)
go
----为开户建立存储过程
----账户自动加 1 ,手机号以132打头后八位
----随机生成,用户只用输入姓名和开户时的金额即可
----调用方式为:Exec proc_open_an_Account '张三' , 100
----产生随机8位数的方式运用Rand()函数
---- 0 < Rand()< 1
----产生10-15 的方式 Rand()*5 + 10
----产生10-20 的方式 Rand()*11 + 10
----print convert(int,Rand()*11 + 10)
----产生0000 0000- 9999 9999 的方式 Rand()*1 0000 0000
----后八位号码可以以 0 打头,在前面加 1
----即 1 0000 0000 - 1 9999 9999
----产生方式Right(共9位,后8位)
----(Rand() * (99999999 + 1)) + 100000000
----print Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
----那么随机手机号就是
--set @phonenum = '132' + Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
--验证一下:
/*
declare @phonenum char(11)
set @phonenum = '132' + Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
print @phonenum
*/
/*
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
*/
if exists(select * from sysobjects where name = 'proc_open_an_Account')
drop proc proc_open_an_Account;
go
Create procedure proc_open_an_Account
@name varchar(20),
@balance money
As
while( 1 = 1 )
begin
--定义手机号码
declare @phonenum char(11)
set @phonenum = '132' +
Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
--判断手机号码是否重复,如果重复,下面的不做,重新生成手机号码
--如果不重复继续执行下列操作
if not exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
begin
--手机没有号码重复,继续做
insert into [USER] values(@phonenum ,@balance ,@name )
--检查余额,如果 balance >= 10 就不加到qianfei表,用户表始终有他
if (@balance < 10)
begin
-- balance < 10 加到qianfei表
insert into qianfei (Account ,TelNo ,Name )
select Account , TelNo , Name
from dbo.[USER]
where balance < 10
end
return;
end
else
begin
--手机号码重复,重新生成手机号码
continue;
end
end
go
/*实验代码
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
*/
----为充值建立存储过程
----调用方式为:Exec proc_recharge phonenum , recharge money
----所以有两个输入参数@phonenum @money
--看一下这个电话号码是否在欠费表上
--先得到这个电话号码的余额
/*
declare @balancebefore money
select @balancebefore from dbo.[USER] where TelNo = @phonenum
if(@balancebefore >= 10)
--这个电话号码的余额大于10,不再欠费表上,不用执行删除操作
begin
update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
end
else
begin
--这个电话号码的余额小于10,在欠费表上,看是否执行删除操作
update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
declare @balanceafter money
select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
--充值了,看余额是否大于等于10,能不能删除
if(@balanceafter >= 10)
begin
delete from qianfei where TelNo = @phonenum
end
end
*/
if exists(select * from sysobjects where name = 'proc_recharge')
drop proc proc_recharge;
go
Create procedure proc_recharge
@phonenum char(11),
@money money
As
--在用户表中找到这个电话号码,再充值
--如果有这个号码,就充值,没这个号码给出提示
if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
begin
declare @balancebefore money
select @balancebefore from dbo.[USER] where TelNo = @phonenum
if(@balancebefore >= 10)
--这个电话号码的余额大于10,不再欠费表上,不用执行删除操作
begin
update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
end
else
begin
--这个电话号码的余额小于10,在欠费表上,看是否执行删除操作
update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
declare @balanceafter money
select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
--充值了,看余额是否大于等于10,能不能删除
if(@balanceafter >= 10)
begin
delete from qianfei where TelNo = @phonenum
end
end
end
else
begin
Raiserror('用户表里面没有这个电话号码,你可能输入错误了',16,1)
end
go
/*实验代码
Exec proc_recharge '13262324748' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
*/
----为消费建立存储过程
----调用方式为:Exec proc_consume phonenum ,consume money
----所以有两个输入参数@phonenum @money
--看一下这个电话号码是否在欠费表上,并得到这个电话号码的余额
--判断余额和comsume money 的大小,如果consume money大于余额,
--就不能消费这么多,并给出提示
--如果consume money 小于余额,消费之后得到余额看加不加入qianfei表
if exists(select * from sysobjects where name = 'proc_consume')
drop proc proc_consume;
go
Create procedure proc_consume
@phonenum char(11),
@money money
As
if not exists(select TelNo from qianfei where TelNo = @phonenum )
begin
update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
declare @balanceafter money
select @balanceafter = balance from dbo.[USER] where TelNo = @phonenum
if(@balanceafter < 10)
begin
insert into qianfei (Account ,TelNo ,Name )
select Account , TelNo , Name
from dbo.[USER]
where TelNo = @phonenum
end
end
else
begin
declare @balancenow money
select @balancenow = balance from dbo.[USER] where TelNo = @phonenum
if(@balancenow >= @money)
begin
update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
end
else
begin
print '你的钱只有:' + convert(varchar(20),@balancenow) +
'块,你还想消费' + convert(varchar(10),@money) + '块,做梦了你!'
end
end
go
/*实验代码
Exec proc_recharge '13262950886' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec proc_consume '13262324748' , 15
Exec proc_consume '13242687354' , 45
Exec proc_consume '13286088603' , 15
Exec proc_consume '13242816704' , 15
*/
----为销户建立存储过程
----调用方式为:Exec proc_consume phonenum
----首先查看余额,如果余额小于10,那么在欠费表上也有他,需要删除2个
----否则删除一个
if exists(select * from sysobjects where name = 'proc_delete_account')
drop proc proc_delete_account;
go
Create procedure proc_delete_account
@phonenum char(11)
As
declare @moneynow money
select @moneynow = balance from dbo.[USER] where TelNo = @phonenum
if(@moneynow >= 10)
begin
----只删除一个表的
delete from dbo.[USER] where TelNo = @phonenum
end
else
begin
delete from qianfei where TelNo = @phonenum
delete from dbo.[USER] where TelNo = @phonenum
end
go
/*实验代码
Exec proc_recharge '13262950886' , 2
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec proc_consume '13217591377' , 15
Exec proc_consume '13217591377' , 2
Exec proc_consume '13286088603' , 15
Exec proc_consume '13242816704' , 15
Exec proc_delete_account '13262324748'
Exec proc_delete_account '13242687354'
*/
----本题用触发器的做法就是如下:
----建数据库
use master;
--判断要创建的数据库是否已存在
if exists(select * from sysdatabases where name = 'TRIGGER_TELDB')
drop database TRIGGER_TELDB ;
create database TRIGGER_TELDB
on primary
(
name = 'TRIGGER_TELDB_data',
filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TRIGGER_TELDB\TRIGGER_TELDB_data.mdf' ,
size = 5,
filegrowth = 1,
maxsize = 20
)
log on
(
name = 'TRIGGER_TELDB_log',
filename = 'C:\Documents and Settings\Administrator\桌面\我的数据库\TRIGGER_TELDB\TRIGGER_TELDB_log.ldf' ,
size = 1,
filegrowth = 10%,
maxsize = 5
)
go
use TRIGGER_TELDB;
if exists(select * from sysobjects where name = 'USER')
--如果存在则删除之前的数据库
drop table [USER] ;
create table [USER]
(
Account int identity(1000,1),
TelNo char(11),
balance money,
Name varchar(20)
)
go
if exists(select * from sysobjects where name = 'qianfei')
--如果存在则删除之前的数据库
drop table qianfei ;
create table qianfei
(
Account int,
TelNo char(11),
Name varchar(20),
)
go
Alter table [User]
Add constraint pk_USER_Account primary key(Account),
constraint ck_USER_balance check(balance >= 0),
constraint UQ_USER_TelNo unique(TelNo)
go
Alter table qianfei
Add constraint FK_qianfei_Account_USER_Account foreign key (Account) References [USER](Account)
go
----建立inserted触发器,当向[USER]表中插入数据时,
----自动判断是否向qianfei表,插入数据
if exists(select * from sysobjects where name = 'trigger_open_An_Account')
drop trigger trigger_open_An_Account;
go
create trigger trigger_open_An_Account
on dbo.[USER] --在dbo.[USER] 表中创建触发器
for insert --为什么事件而触发
As --事件触发后要做什么事情
begin transaction
declare @balance money
select @balance = balance from inserted
--如果余额 < 10 ,插入qianfei表中
if(@balance < 10)
begin
insert into qianfei(Account,TelNo,Name)
select Account,TelNo,Name
from inserted
end
commit tran
go
--创建开户的存储过程
if exists(select * from sysobjects where name = 'proc_open_an_Account')
drop proc proc_open_an_Account;
go
Create procedure proc_open_an_Account
@name varchar(20),
@balance money
As
while( 1 = 1 )
begin
--定义手机号码
declare @phonenum char(11)
set @phonenum = '132' +
Right(convert(char(9),convert(int,((Rand() * (99999999 + 1)) + 100000000))),8)
--判断手机号码是否重复,如果重复,下面的不做,重新生成手机号码
--如果不重复继续执行下列操作
if not exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
begin
--手机没有号码重复,继续做
insert into dbo.[USER] values(@phonenum ,@balance ,@name )
return;
end
else
begin
--手机号码重复,重新生成手机号码
continue;
end
end
go
/*测试代码
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
select * from dbo.[user];
select* from qianfei;
*/
---创建充值update触发器
if exists(select * from sysobjects where name = 'trigger_update_recharge_Account')
drop trigger trigger_update_recharge_Account;
go
create trigger trigger_update_recharge_Account
on dbo.[USER]
for update
As
--更新是先把旧数据放入deleted表,带插入新数据放入inserted表
declare @balancebefore money
--得到之前的余额看是否在qianfei表上
select @balancebefore = balance from deleted
if(@balancebefore < 10)
begin
--这个电话号码的余额小于10,在欠费表上,看是否执行删除操作
declare @balanceafter money
select @balanceafter = balance from inserted
--充值了,看余额是否大于等于10,能不能删除
if(@balanceafter >= 10)
begin
declare @phonenum char(11)
select @phonenum = TelNo from inserted
delete from qianfei where TelNo = @phonenum
end
end
go
----为充值建立存储过程
if exists(select * from sysobjects where name = 'proc_recharge')
drop proc proc_recharge;
go
Create procedure proc_recharge
@phonenum char(11),
@money money
As
--在用户表中找到这个电话号码,再充值
--如果有这个号码,就充值,没这个号码给出提示
if exists (select TelNo from dbo.[USER] where TelNo = @phonenum)
begin
update dbo.[USER] set balance = balance + @money where TelNo = @phonenum
end
else
begin
Raiserror('用户表里面没有这个电话号码,你可能输入错误了',16,1)
end
go
/*实验代码
Exec proc_recharge '13294163543' , 5
--不纯在的号码
Exec proc_recharge '13237151857' , 50
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13275403394' , 20
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
*/
---创建消费update触发器
if exists(select * from sysobjects where name = 'trigger_update_consume_Account')
drop trigger trigger_update_consume_Account;
go
create trigger trigger_update_consume_Account
on dbo.[USER]
for update
As
begin tran
---得到当前余额,看是否在qianfei表上,在qianfei表上的话,不动
declare @balancenow money
select @balancenow = balance from deleted
if(@balancenow >= 10)
begin
--消费之前不在qianfei表上,消费得到之后余额,再比较
declare @balanceafter money
select @balanceafter = balance from inserted
if(@balanceafter < 10)
begin
--消费之后余额小于10,要加入qianfei表了
insert into qianfei
select Account,TelNo,Name
from inserted
end
end
commit tran
go
----为消费建立存储过程
if exists(select * from sysobjects where name = 'proc_consume')
drop proc proc_consume;
go
Create procedure proc_consume
@phonenum char(11),
@money money
As
--查看这个手机号码可用余额
declare @balancenow money
select @balancenow = balance from dbo.[USER] where TelNo = @phonenum
if(@balancenow >= @money)
begin
update dbo.[USER] set balance = balance - @money where TelNo = @phonenum
end
else
begin
print'你的钱只有:' + convert(varchar(20),@balancenow) +
'块,你还想消费' + convert(varchar(10),@money) + '块,做梦了你!'
end
go
/*实验代码
Exec proc_recharge '13294163543' , 5
--不纯在的号码
Exec proc_recharge '13261701311' , 5
Exec proc_recharge '13262950886' , 15
Exec proc_recharge '13262162372' , 20
delete from dbo.[USER]
delete from qianfei;
select * from dbo.[USER];
select * from qianfei;
Exec proc_open_an_Account '李四' , 5
Exec proc_open_an_Account '张三' , 50
Exec proc_open_an_Account '易明' , 15
---钱不够花的
Exec proc_consume '13261701311' , 5
Exec proc_consume '13276372454' , 10
Exec proc_consume '13240102413' , 15
Exec proc_consume '13206197933' , 10
Exec proc_delete_account '13242896467'
Exec proc_delete_account '13217591377'
*/
---创建销户delete触发器
----为[USER]表建立delete触发器
if exists(select * from sysobjects where name = 'trigger_close_Account')
drop trigger trigger_close_Account;
go
create trigger trigger_close_Account
on dbo.[USER]
instead of delete
As
begin tran
---得到当前余额,看是否在qianfei表上,在qianfei表上的话,不动
declare @balancenow money
select @balancenow = balance from deleted
if(@balancenow < 10)
begin
--在销户之前余额小于10,所以在qianfei表上
declare @phonenum char(11)
select @phonenum = TelNo from deleted
delete from qianfei where TelNo = @phonenum
end
delete from dbo.[USER] where TelNo = @phonenum
commit tran
go
----为销户建立存储过程
----调用方式为:Exec proc_consume phonenum
----首先查看余额,如果余额小于10,那么在欠费表上也有他,需要删除2个
----否则删除一个
if exists(select * from sysobjects where name = 'proc_delete_account')
drop proc proc_delete_account;
go
Create procedure proc_delete_account
@phonenum char(11)
As
delete from dbo.[USER] where TelNo = @phonenum
go
/*实验代码
select * from dbo.[USER];
select * from qianfei;
Exec proc_delete_account '13261701311'
Exec proc_delete_account '13294163543'
*/
---非常重要:因为[USER]表与qianfei表建立Account的外键,所以应该先删除子表,再删父表
---但是delete触发器要先完成delete动作在触发,SQL没有before delete 触发器,因而可用
---instead of delete触发器,在父表执行删除时,不会删除,而是触发了此触发器,在触发器里
---可先删除子表再删除父表;