实验5数据库编程技术.docx
《实验5数据库编程技术.docx》由会员分享,可在线阅读,更多相关《实验5数据库编程技术.docx(19页珍藏版)》请在冰豆网上搜索。
![实验5数据库编程技术.docx](https://file1.bdocx.com/fileroot1/2022-10/29/b1794621-d660-4dcc-8331-4d3515894e14/b1794621-d660-4dcc-8331-4d3515894e141.gif)
实验5数据库编程技术
实验5:
数据库编程技术
第1个实验.游标与存储过程——第7章实验十二
请完成以下实验内容:
(1)创建游标,逐行显示表Customer.的记录,并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下:
'客户编号'+'-----'+'客户名称'+'----'+'客户地址'+'-----'+'客户电话'+'------'+'客户邮编'+'------'
declare@nochar(9),@navarchar(40),@adchar(40),@tevarchar(20),@zichar(6),@textchar(100)
declarecustcursorfor
selectcustomerno,customername,address,telephone,zip
fromcustomer
select@text=replicate('=',38)+'客户信息'+replicate('=',38)
print@text
select@text=space(84)
print@text
select@text='客户编号'+'----------'+'客户名称'+'------------'+'客户地址'+'--------'+'客户电话'+'---------'+'客户邮编'+'-----'
print@text
opencust
fetchcustinto@no,@na,@ad,@te,@zi
while(@@fetch_status=0)
begin
select@text=@no+space(5)+convert(char(20),@na)+space(5)+convert(char(10),@ad)+space(3)+convert(char(13),@te)+space(7)+@zi
print@text
fetchcustinto@no,@na,@ad,@te,@zi
end
closecust
deallocatecust
(2)利用游标修改OrderMaster表中Ordersum的值。
declare@ordernochar(12),@ordersumnumeric(9,2)
declareordermascursorfor
selecta.orderno,sum(b.quantity*b.price)
fromordermastera,orderdetailb
wherea.orderno=b.orderno
groupbya.orderno
openordermas
fetchordermasinto@orderno,@ordersum
while(@@fetch_status=0)
begin
updateordermaster
setordersum=@ordersum
whereorderno=@orderno
fetchordermasinto@orderno,@ordersum
end
closeordermas
deallocateordermas
结果查看:
select*
fromordermaster
(3)创建存储过程,要求:
按Employee表定义的CHECK约束自动产生员工编号。
createprocedurechansheng(@emp_intint)
as
declare@empnochar(8)
declarechancursorfor
selectemployeeno
fromemployee
orderbyemployeenodesc
openchan
fetchchaninto@empno
while(@@fetch_status=0and@emp_int>0)
begin
set@empno='E'+convert(char(7),convert(int,(substring(@empno,2,7))+1))
print@empno
set@emp_int=@emp_int-1
END
closechan
deallocatechan
执行:
按用户需要产生的员工编号个数产生编号
execchansheng3(产生三个)
(4)创建存储过程,要求:
查找姓“李”的“职员”的员工编号、订单编号、销售金额。
createprocedureempname(@emp_namechar
(2))
as
declare@empnochar(8),@empnamevarchar(10),@salarynumeric(8,2),@textvarchar(60)
declarechancursorfor
selectemployeeno,employeename,salary
fromemployee
whereconvert(char
(2),employeename)=@emp_name
set@text='编号'+space(10)+'姓名'+space(10)+'薪水'
print@text
openchan
fetchchaninto@empno,@empname,@salary
while(@@fetch_status=0)
begin
set@text=@empno+space(5)+convert(char(8),@empname)+space(5)+convert(char(8),@salary)
print@text
fetchchaninto@empno,@empname,@salary
END
closechan
deallocatechan
执行:
execempname'李'
(5)创建存储过程,要求:
输出所有客户姓名、客户订购金额及其相应业务员的姓名。
createprocedurep_customer
as
declare@customernamevarchar(40),@ordersumnumeric(9,2),@salernamevarchar(10),@namevarchar(40),@textvarchar(60)
declarechancursorfor
selecta.customername,sum(b.ordersum),c.employeename
fromcustomera,ordermasterb,employeec
wherea.customerno=b.customernoandc.employeeno=b.salerno
groupbya.customername,c.employeename
orderbya.customername,c.employeename
set@text='客户名称'+space(15)+'客户订购金额'+space(10)+'业务员姓名'
print@text
set@text=replicate('=',60)
print@text
openchan
fetchchaninto@customername,@ordersum,@salername
set@name=@customername
while(@@fetch_status=0)
begin
if(@namelike@customername)
begin
set@text=convert(char(20),@customername)+space(5)+convert(char(9),@ordersum)+space(5)+convert(char(8),@salername)
print@text
end
else
begin
set@text=replicate('-',60)
print@text
set@text=convert(char(20),@customername)+space(5)+convert(char(9),@ordersum)+space(5)+convert(char(8),@salername)
print@text
set@name=@customername
end
fetchchaninto@customername,@ordersum,@salername
END
closechan
deallocatechan
执行:
execp_customer
(6)创建存储过程,要求:
输入年度,计算每个业务员的年终奖金额。
年终奖金=年销售总额×提成率。
提成率规则如下:
年销售总额30000元以下部分,提成率为1.0%,超过30000元部分,则提成率为1.2%。
createprocedurep_salary@yearint
as
declare@employeenochar(12),@employeenamevarchar(10),@sumnumeric(9,2),@salarynumeric(9,2),@textvarchar(60)
declarechancursorfor
selecta.employeeno,a.employeename,sum(b.ordersum)
fromemployeealeftjoinordermasterbona.employeeno=b.salernoandyear(b.orderdate)=@year
groupbya.employeeno,a.employeename
orderbysum(b.ordersum)desc
set@text='员工编号'+space(15)+'员工姓名'+space(10)+'年终奖励'
print@text
set@text=replicate('=',60)
print@text
openchan
fetchchaninto@employeeno,@employeename,@sum
while(@@fetch_status=0)
begin
if(@sum>30000)
begin
set@salary=(@sum-30000)*0.012+30000*0.01
set@text=@employeeno+space(12)+convert(char(10),@employeename)+space(8)+convert(char(9),@salary)
print@text
end
if(@sum>0)
begin
set@salary=@sum*0.01
set@text=@employeeno+space(12)+convert(char(10),@employeename)+space(8)+convert(char(9),@salary)
print@text
end
if(@sumisnull)
begin
set@salary=0
set@text=@employeeno+space(12)+convert(char(10),@employeename)+space(8)+conv