实验游标和存储过程Word格式文档下载.docx
《实验游标和存储过程Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《实验游标和存储过程Word格式文档下载.docx(19页珍藏版)》请在冰豆网上搜索。
P20050003120GB硬盘21.00
P200500043.5寸软驱18.00
P20060002网卡16.00
(8)创建存储过程,要求:
输入年度,计算每个业务员的年终奖金。
年终奖金=年销售总额×
提成率。
提成率规则如下:
年销售总额5000元以下部分,提成率为10%,对于5000元及超过5000元部分,则提成率为15%。
(9)创建存储过程,要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出,格式如图7-1所示。
===================订单及其明细数据信息====================
---------------------------------------------------
订单编号200801090001
商品编号数量价格
P200500015403.50
P2005000232100.00
P200500032600.00
合计订单总金额3103.50
图7-1订单及其明细数据信息
(10)请使用游标和循环语句创建存储过程proSearchCustomer,根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息,并按商品分组输出。
输出格式如图7-2所示。
===================客户订单表====================
客户名称:
统一股份有限公司
客户地址:
天津市
总金额:
31121.86
商品编号总数量平均价格
P20050001580.70
P2005000219521.05
P200500035282.00
P200700042320.00
报表制作人陈辉制作日期0682012
图7-2客户订单表
实验脚本:
/*
(1)创建游标,逐行显示Customer表的记录,并用WHILE结构来测试@@Fetch_Status的返回值。
*/
declare@C_nochar(9),@C_namechar(18),@C_phonechar(10),
@C_addchar(8),@C_zipchar(6)
declare@textchar(100)
declarecus_curscrollcursorfor
select*
fromCustomer62
select@text='
================================Customer62表的记录===================='
print@text
-----------'
-------'
======================================================================'
opencus_cur
fetchcus_curinto@C_no,@C_name,@C_phone,@C_add,@C_zip
while(@@fetch_status=0)
begin
select@text=@C_no+'
'
+@C_name+'
+@C_phone+'
+@C_add+'
+@C_zip
print@text
fetchcus_curinto@C_no,@C_name,@C_phone,@C_add,@C_zip
end
closecus_cur
deallocatecus_cur
/*
(2)利用游标修改OrderMaster表中orderSum的值*/
declare@orderNovarchar(20),@totalnumeric(9,2)
declareom_curcursorfor
selectorderNo,sum(quantity*price)
fromOrderDetail62
groupbyorderNo
openom_cur
fetchom_curinto@orderNo,@total
begin
updateOrderMaster62
setorderSum=@total
whereorderNo=@orderNo
fetchom_curinto@orderNo,@total
closeom_cur
deallocateom_cur
/*(3)创建游标,要求:
输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/
declare@emNovarchar(8),@emNachar(8),@emsechar
(1),@emdevarchar(10),
@emhevarchar(8),@emsanumeric(8,2)
declareem_curscrollcursorfor
selectemployeeNo,employeeName,sex,department,headShip,salary
fromEmployee62
wheresex='
M'
select@text='
====================================================='
编号姓名性别所属部门职务薪水'
openem_cur
fetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsa
select@text=@emNo+'
+@emNa+'
+@emse+'
+@emde+'
+@emhe
+'
+convert(char(10),@emsa)
print@text
fetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsa
closeem_cur
deallocateem_cur
/*(4)创建存储过程,要求:
按表定义中的CHECK约束自动产生员工编号*/
createtableRnum(
numberchar(8)null,
enamechar(10)null
)--先创建一张新表用来存储已经产生的员工编号
createprocedureno_tot(@namenvarchar(50))
as
declare@iint,@textchar(100)
set@i=1
while(@i<
1000)
begin
ifexists(selectnumber
fromRnum
wherenumber=('
E'
+convert(char(4),year(getdate()))+right('
00'
+convert(varchar(3),@i),3)))
begin
set@i=@i+1
continue
end
else
insertRnumvalues(('
+convert(varchar(3),@i),3)),@name)
select@text='
员工编号'
'
员工姓名'
print@text
select@text=('
+convert(varchar(3),@i),3))+'
+@name
--这里的两个数字'
3'
就是我们要设置的id长度
break
end
end
/*执行过程*/
execno_tot张三
/*(5)创建存储过程,要求:
查找姓“李”的职员的员工编号、订单编号、订单金额*/
createprocedureemli_tot@emNochar(8)
selecta.employeeNo员工编号,b.orderNo订单编号,b.orderSum订单金额
fromEmployee62a,OrderMaster62b
wherea.employeeNo=b.salerNoanda.employeeNamelike'
@emNo'
execemli_tot'
李%'
/*(6)创建存储过程,要求:
统计每个业务员的总销售业绩,显示业绩最好的前3位业务员的销售信息*/
createproceduresaler_tot
selecttop3salerNo业务员编号,sum(orderSum)总销售业绩
fromOrderMaster62
groupbysalerNo
orderbysum(orderSum)desc
execsaler_tot
/* (7)创建存储过程,要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出:
=======大客户中热销的前种商品的销售信息================
商品编号商品名称总销售数量
P20050003120GB硬盘21.00
P200500043.5寸软驱18.00
P20060002网卡16.00*/
createprocedureproduct_tot
declare@proNochar(10),@proNachar(20),@totalint
declare@textchar(100)
declaresale_curscrollcursorfor
selecttop3a.productNo,a.productName,sum(c.quantity)
fromProduct62a,OrderMaster62b,OrderDetail62c
wherea.productNo=c.productNoandb.orderNo=c.orderNoand
b.customerNoin(selecttop5m.customerNo
fromOrderMaster62m,OrderDetail62n
wherem.orderNo=n.orderNo
groupbym.customerNo
orderbysum(quantity)desc)
groupbya.productNo,a.productName
orderbysum(c.quantity)desc
select@text='
=======大客户中热销的前种商品的销售信息======'
print@text
商品编号商品名称总销售数量'
opensale_cur
fetchsale_curinto@proNo,@proNa,@total
while(@@fetch_status=0)
select@text=@proNo+'
+@proNa+'
+convert(char(10),@total)
fetchsale_curinto@proNo,@proNa,@total
closesale_cur
deallocatesale_cur
execproduct_tot
/*(8)创建存储过程,要求:
年销售总额元以下部分,提成率为%,对于元及超过元部分,
则提成率为%*/
createprocedurepride_tot@dateint
declare@saleNochar(15),@totalnumeric(9,2)
declare@textchar(100),@moneynumeric(8,2)
declarepride_curscrollcursorfor
selectsalerNo,sum(orderSum)
fromOrderMaster62
whereyear(orderDate)=@date
groupbysalerNo
=========业务员的年终奖金========='
业务员编号年终奖金'
openpride_cur
fetchpride_curinto@saleNo,@total
if(@total<
5000)
select@money=@total*0.1
select@money=500+(@total-5000)*0.15
select@text=@saleNo+'
'
+convert(char(10),@money)
fetchpride_curinto@saleNo,@total
closepride_cur
deallocatepride_cur
execpride_tot2012
/*(9)创建存储过程,要求将OrderMaster62表中每一个订单所对应的明细数据信息按规定格式输出,格式如图-1所示。
===================订单及其明细数据信息====================
---------------------------------------------------
订单编号200801090001
商品编号数量价格
P200500015403.50
P2005000232100.00
P200500032600.00
合计订单总金额3103.50
图-1订单及其明细数据信息*/
createprocedureorderm_tot@ordernochar(15)
declare@pronochar(15),@quantityint,@pricenumeric(9,2)
declareorderm_curscrollcursorfor
selectproductNo,sum(quantity),sum(quantity*price)
fromOrderDetail62
whereorderNo=@orderno
groupbyproductNo
=============订单及其明细数据信息================'
-------------------------------------------------'
订单编号'
+@orderno
商品编号数量价格'
openorderm_cur
fetchorderm_curinto@prono,@quantity,@price
select@text=@prono+'
+convert(char(5),@quantity)+'
+convert(char(10),@price)
print@text
fetchorderm_curinto@prono,@quantity,@price
closeorderm_cur
deallocateorderm_cur
declare@sumnumeric(9,2)
declareorm_curscrollcursorfor
selectorderSum
whereorderNo=@orderno
openorm_cur
fetchorm_curinto@sum
合计订单总金额'
+convert(char(12),@sum)
fetchorm_curinto@sum
closeorm_cur
deallocateorm_cur
execorderm_tot200801090001
/*(10)请使用游标和循环语句创建存储过程proSearchCustomer,根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息,并按商品分组输出。
输出格式如图-2所示。
===================客户订单表====================
客户名称:
客户地址:
总金额:
商品编号总数量平均价格
P20050001580.70
P2005000219521.05
P200500035282.00
P200700042320.00
报表制作人陈辉制作日期0682012*/
createprocedureproSearchCustomer(@cusnochar(10))
declare@cusnamechar(40),@addresschar(20),@totalnumeric(9,2)
declaresear_curscrollcursorfor
selecta.customerName,a.address,sum(b.orderSum)
fromCustomer62a,OrderMaster62b
wherea.customerNo=b.customerNoanda.customerNo=@cusno
groupbya.customerName,a.address
===================客户订单表===================='
select@tex