存储过程练习操作

use Comm
go

--查询至少有2个电话的用户名、电话号码的个数
select UserName 用户名,count(*) 电话号码个数 from UserInfo group by UserName having count(*)>=2

--查询通话时间少于1分钟的通话ID
select CommId 通话ID from TelRecord where (datediff(ms,Stime,Etime)<60000)

/*--找出通话记录表中存在的电话号码而在用户信息表中没有的电话号码，
 然后把这些电话号码插入到用户信息表，新插入的数据用户名为“未知”、地址为“未知”--*/
while exists (select * from dbo.TelRecord T
  inner join dbo.UserInfo U on T.TelFrom=U.TelNo
 where T.TelDest not in (select TelNo from UserInfo))
 begin
  declare @new nvarchar(30)
  select @new=TelDest from dbo.TelRecord T
   inner join dbo.UserInfo U on T.TelFrom=U.TelNo
   where T.TelDest not in (select TelNo from UserInfo)
  insert into UserInfo (UserName,TelNo,Address)
   values ('未知',@new,'未知')
 end

--编写存储过程，按输入的月份、主叫号码统计通话记录、通话总时间
use Comm
go

if exists (select * from sysobjects where name='proc_Rec')
 drop proc proc_Rec
go

--通话记录所有的总时间
create proc proc_Rec
 @month int,@TelFrom nvarchar(30),
 @sumTime nvarchar(100) output
as
 declare @ms int,@time nvarchar(11)
 select @ms=sum(datediff(ms,Stime,Etime)) from TelRecord where month(Stime)=@month and TelFrom=@TelFrom
 set @sumTime=convert(nvarchar(11),@ms/84400000)+'天'+convert(nvarchar(11),@ms/3600000)+
  '小时'+convert(nvarchar(11),@ms/60000)+'分'+convert(nvarchar(11),@ms/1000)+'秒'+
   convert(nvarchar(11),@ms%1000)+'毫秒'
go

declare @month int,@TelFrom nvarchar(30),@sumTime nvarchar(100)
set @month=2
set @TelFrom='98290000'
exec proc_Rec @month,@TelFrom,@sumTime output
print @sumTime

if exists (select * from sysobjects where name='proc_sumRec')
 drop proc proc_sumRec
go

--通话记录每条各自的总时间
create proc proc_sumRec
 @month int,@TelFrom nvarchar(30),
 @sumTime nvarchar(100) output
as
 declare @ms int
 select *,(convert(nvarchar(11),datediff(ms,Stime,Etime)/3600000)+'小时'+
   convert(nvarchar(11),datediff(ms,Stime,Etime)%3600000/60000)+'分'+
   convert(nvarchar(11),datediff(ms,Stime,Etime)%60000/1000)+'秒'+
   convert(nvarchar(11),datediff(ms,Stime,Etime)%1000)+'毫秒') 通话总时间
  from TelRecord where month(Stime)=@month and TelFrom=@TelFrom
go

declare @sumTime nvarchar(100)
exec proc_sumRec 2,'98290000',@sumTime output
print @sumTime