函數,游標與存儲過程的綜合應用

--在TOY數據庫中完成以下操作
use toy
go
--1、編寫一個存儲過程，接收任意一個訂單號，打印訂單的表頭數據。格式如下：
--(其中訂單編號由訂單日期+訂單號的字符串組成)
--訂單編號:20010520000001???訂貨日期:2001-05-20???訂貨人:拉爾森
create procedure proc_order
(@orderid as varchar(6))
as
begin
?declare @ord varchar(6),@name varchar(10),@time datetime
?select @ord=[定單號],@name=[名],@time=[定單日期] from [dbo].[定單],[dbo].[購物者]
??where [dbo].[定單].購物者號=[dbo].[購物者].購物者號 and [定單號]=@orderid
?print '訂單編號：'+Convert(varchar(12),@time,112)+@orderid+'???訂貨日期：'+Convert(varchar(10),@time,120)+'??訂貨人：'+@name
end
go
execute proc_order '000001'
go
--2、編寫一個存儲過程或函數，接收訂單號、玩具號、輸出該訂單中該種玩具的銷售金額。
--其中，玩具的銷售金額＝玩具單價*?訂單中玩具的購買數量*折扣率，折扣率的處理規則如下：
--玩具的銷售金額 ???折扣
------------------------------------------------------------
--[0-30)????????????1.0
--[30-50)?????????????0.9
--[50-100) ?????0.8
--[100以上???????????0.7
create proc proc_dis
(@orderid varchar(6),@wjid varchar(6),@salemoney decimal(8,2) output)
as
begin
?declare @money decimal(8,2);
?select @money=價格*購買數量 from [dbo].[玩具],[dbo].[定單詳情] where [dbo].[玩具].玩具號=[dbo].[定單詳情].玩具號
?and [定單詳情].定單號=@orderid?and [定單詳情].玩具號=@wjid
?select @salemoney=@money*
??case when @money<30 then 1.0
????when @money>=30 and 價格<50 then 0.9
????when @money>=50 and 價格<100 then 0.8
????when @money>100 then 0.7
??end??from [dbo].[玩具],[dbo].[定單詳情] where [dbo].[玩具].玩具號=[dbo].[定單詳情].玩具號
??and [定單詳情].定單號=@orderid?and [定單詳情].玩具號=@wjid
end
go
declare @salemoney decimal(8,2)
execute proc_dis '000001','000007',@salemoney output
select @salemoney
go
--3、編寫一個存儲過程，接收一個訂單號，調用上述存儲過程打印輸出該訂單下的所有玩具的明細信息。例如：輸入訂單號000001，則打印格式如下：

--玩具編號???玩具名稱?????????????????????????購買數量????單價?????金額
--------???------------------------------???--------???------???------
--000007?????tie dye kit？工具箱??????????????2??????????20??????36
--000008?????愛麗絲奇境記?????????????????????1??????????15???????15
create proc proc_dtinfo
(@orderid varchar(6))
as
begin
?print '玩具編號???玩具名稱??????????????????購買數量????單價?????金額 '
?print '--------???-----------------------???--------???------???------'
?declare @wjid varchar(6),@wjname varchar(20),@num int,@price decimal(8,2)
?declare cur cursor
?for select [玩具].玩具號 from [定單詳情],[玩具] where [定單詳情].玩具號=[玩具].玩具號 and [定單詳情].定單號=@orderid
?open cur
??fetch next from cur into @wjid
??while @@fetch_status=0
??begin
???declare @salemoney decimal(8,2)
???execute proc_dis @orderid,@wjid,@salemoney output
???select @wjname=[玩具].玩具名,@num=[定單詳情].購買數量,@price=[玩具].價格,@salemoney=@salemoney from [定單詳情],[玩具] where [定單詳情].玩具號=[玩具].玩具號
???and [定單詳情].定單號=@orderid?and [玩具].玩具號=@wjid
???print @wjid+'????'+@wjname+'??????'+str(@num)+'???'+Convert(varchar(10),@price)+'????'+Convert(varchar(10),@salemoney)
???fetch next from cur into @wjid
??end
?close cur
?deallocate cur
end
go
exec proc_dtinfo '000001'
go
--4、編寫一個存儲過程，接收一個訂單號，計算出該訂單的總金額，并輸出。(總金額=該訂單中所有玩具的銷售金額+訂單的運輸費用)。
create proc p_sum
(@orderid varchar(6))
as
begin
?declare @ysmoney decimal(8,2),@wjid varchar(6),@sum decimal(8,2)
?set @sum=0;
?select @ysmoney=[定單].運輸價格 from [dbo].[定單] where [定單].定單號=@orderid
?declare cur cursor
?for select 玩具號 from [dbo].[定單詳情] where [定單詳情].定單號=@orderid
?open cur
??fetch next from cur into @wjid
??while @@FETCH_STATUS=0
??begin
???declare @salemoney decimal(8,2)
???execute proc_dis @orderid,@wjid,@salemoney output
???set @sum=@sum+@salemoney;
???fetch next from cur into @wjid
??end
??print '總金額：'+ltrim(str(@sum+@ysmoney));
?close cur
?deallocate cur
end
go
exec p_sum '000001'
go
--5、編寫一個存儲過程，接收一個訂單號，打印出該訂單的附加信息(包括：運送方式、接受者姓名、接受者地址、郵政編碼和聯系電話)。
--例如：輸入訂單號000001，則打印格式如下：
--運送方式:標準航運?????????????????運送金額:6
--接收者姓名:約翰遜
--接收者地址:美國加利福尼亞州桑德蘭227 海濱大道.
--郵政編碼:94087-1147
--電話號碼:123-5673??????
create procedure proc_fjinfo
(@orderid varchar(6))
as
begin
?declare @fsm varchar(10),@ysmoney decimal(8,2),@name varchar(20),@gjname varchar(20),@zname varchar(20),@city varchar(20),
?@address varchar(200),@yb varchar(10),@phone varchar(10)
?select @fsm=[運輸方式].方式名,@ysmoney=運輸價格,@name=[接受者].名,@gjname=[國家].國家名,@zname=[接受者].州,@city=[接受者].城市,
?@address=[接受者].地址,@yb=[接受者].郵編,@phone=[接受者].電話 from [dbo].[定單]
??inner join [dbo].[運輸方式] on [dbo].[運輸方式].方式號=[dbo].[定單].運輸號
??inner join [dbo].[接受者] on [dbo].[接受者].定單號=[dbo].[定單].定單號
??inner join [dbo].[國家] on [dbo].[國家].國家號=[接受者].國家號 and 定單.定單號=@orderid
?print '運送方式:'+@fsm+'????'+'運送金額:'+ltrim(str(@ysmoney))
?print '接收者姓名:'+@name
?print '接收者地址:'+rtrim(@gjname)+ltrim(rtrim(@zname))+ltrim(rtrim(@city))+ltrim(@address)
?print '郵政編碼:'+@yb
?print '電話號碼:'+@phone
end
go
execute proc_fjinfo '000001'
go
--6、編寫一個存儲過程，接收一個訂單號，調用上述的所有存儲過程打印出訂單的完整信息。格式如下：
--例如：輸入訂單號000001，則打印格式如下：
--???????????????????????????????訂 貨 單??????????????????????????????
--======================================================================
--訂單編號:20010520000001???訂貨日期:2001-05-20???訂貨人:拉爾森
--======================================================================
--玩具編號???玩具名稱?????????????????????????購買數量????單價?????金額
--------???------------------------------???--------???------???------
--000007?????tie dye kit？工具箱??????????????2??????????20??????36
--000008?????愛麗絲奇境記?????????????????????1??????????15???????15
--======================================================================
--運送方式:標準航運?????????????????運送金額:6
--接收者姓名:約翰遜
--接收者地址:美國加利福尼亞州桑德蘭227 海濱大道.
--郵政編碼:94087-1147
--電話號碼:123-5673??????
--======================================================================
--總金額:57
create proc p_detail
(@orderid varchar(6))
as
begin
?print '???????????????????????????????訂 貨 單???????????????????????????????'??????
?print '======================================================================'
?exec proc_order @orderid
?print '======================================================================'
?exec proc_dtinfo @orderid
?print '======================================================================'
?exec proc_fjinfo @orderid
?print '======================================================================'
?exec p_sum @orderid
end
go
exec p_detail '000001'
go
--7、創建一個函數，根據所給玩具號及數量，計算應支付的金額。
create function fun_pay(@wjid varchar(6),@num int)
returns decimal(8,2)
begin
?return @num*(select [價格] from [dbo].[玩具] where [玩具號]=@wjid)
end
go
select dbo.fun_pay('000007',2);
go
--8、創建一個函數，根據給定的訂單號，求出該訂單的詳細購物情況(訂單號、玩具名、數量)。
create function fun_detinfo(@orderid varchar(6))
returns table
as
?return (select 定單號,玩具名,購買數量??from [dbo].[定單詳情] inner join dbo.玩具 on dbo.玩具.玩具號=[定單詳情].玩具號
?????????where 定單號=@orderid)
go
select * from dbo.fun_detinfo('000001')
go