实验5：数据库编程技术.doc

实验5：数据库编程技术第1个实验游标与存储过程 第7章实验十二请完成以下实验内容：(1) 创建游标，逐行显示表Customer.的记录，并用WHILE结构来测试Fetch_Status的返回值。输出格式如下：客户编号+-+客户名称+-+客户地址+-+客户电话+-+客户邮编+- declare no char(9),na varchar(40),ad char(40),te varchar(20),zi char(6),text char(100)declare cust cursor forselect customerno,customername,address,telephone,zipfrom customerselect text=replicate(=,38)+客户信息+replicate(=,38)print textselect text=space(84)print textselect text=客户编号+-+客户名称+-+客户地址+-+客户电话+-+客户邮编+-print textopen custfetch cust into no,na,ad,te,ziwhile(fetch_status=0)beginselect text=no+space(5)+convert(char(20),na)+space(5)+convert(char(10),ad)+space(3)+convert(char(13),te)+space(7)+ziprint textfetch cust into no,na,ad,te,ziendclose custdeallocate cust(2) 利用游标修改OrderMaster表中Ordersum的值。declare orderno char(12),ordersum numeric(9,2)declare ordermas cursor forselect a.orderno,sum(b.quantity*b.price)from ordermaster a,orderdetail bwhere a.orderno=b.ordernogroup by a.ordernoopen ordermas fetch ordermas into orderno,ordersumwhile(fetch_status=0)begin update ordermasterset ordersum=ordersum where orderno=ordernofetch ordermas into orderno,ordersumend close ordermasdeallocate ordermas结果查看：select *from ordermaster(3) 创建存储过程，要求：按Employee表定义的CHECK约束自动产生员工编号。create procedure chansheng(emp_int int)as declare empno char(8)declare chan cursor forselect employeenofrom employeeorder by employeeno descopen chanfetch chan into empnowhile(fetch_status=0 and emp_int0)begin set empno=E+convert(char(7),convert(int,(substring(empno,2,7)+1)print empnoset emp_int=emp_int-1ENDclose chandeallocate chan执行：按用户需要产生的员工编号个数产生编号exec chansheng 3（产生三个）(4) 创建存储过程，要求：查找姓“李”的“职员”的员工编号、订单编号、销售金额。create procedure empname(emp_name char(2)as declare empno char(8),empname varchar(10),salary numeric(8,2),text varchar(60)declare chan cursor forselect employeeno,employeename,salaryfrom employeewhere convert(char(2),employeename)=emp_nameset text=编号+space(10)+姓名+space(10)+薪水print textopen chanfetch chan into empno,empname,salarywhile(fetch_status=0)begin set text=empno+space(5)+convert(char(8),empname)+space(5)+convert(char(8),salary)print textfetch chan into empno,empname,salaryENDclose chandeallocate chan执行：exec empname 李(5) 创建存储过程，要求：输出所有客户姓名、客户订购金额及其相应业务员的姓名。create procedure p_customeras declare customername varchar(40),ordersum numeric(9,2),salername varchar(10),name varchar(40),text varchar(60)declare chan cursor forselect a.customername,sum(b.ordersum),c.employeenamefrom customer a,ordermaster b,employee cwhere a.customerno=b.customerno and c.employeeno=b.salerno group by a.customername,c.employeenameorder by a.customername,c.employeenameset text=客户名称+space(15)+客户订购金额+space(10)+业务员姓名print textset text=replicate(=,60)print textopen chanfetch chan into customername,ordersum,salernameset name=customernamewhile(fetch_status=0)beginif(name like customername)beginset text=convert(char(20),customername)+space(5)+convert(char(9),ordersum)+space(5)+convert(char(8),salername)print textendelse 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=customernameendfetch chan into customername,ordersum,salernameENDclose chandeallocate chan 执行：exec p_customer(6) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金额。年终奖金年销售总额提成率。提成率规则如下：年销售总额30000元以下部分，提成率为1.0，超过30000元部分，则提成率为1.2。create procedure p_salary year intas declare employeeno char(12),employeename varchar(10),sum numeric(9,2),salary numeric(9,2),text varchar(60)declare chan cursor forselect a.employeeno,a.employeename,sum(b.ordersum)from employee a left join ordermaster b on a.employeeno=b.salerno and year(b.orderdate)=year group by a.employeeno,a.employeenameorder by sum(b.ordersum) descset text=员工编号+space(15)+员工姓名+space(10)+年终奖励print textset text=replicate(=,60)print textopen chanfetch chan into employeeno,employeename,sumwhile(fetch_status=0)beginif(sum30000)beginset salary=(sum-30000)*0.012+30000*0.01set text=employeeno+space(12)+convert(char(10),employeename)+space(8)+convert(char(9),salary)print textendif(sum0) beginset salary=sum*0.01set text=employeeno+space(12)+convert(char(10),employeename)+space(8)+convert(char(9),salary)print textendif(sum is null)beginset salary=0set text=employeeno+space(12)+convert(char(10),employeename)+space(8)+convert(char(9),salary)print textendfetch chan into employeeno,employeename,sumENDclose chandeallocate chan执行：exec p_salary 2008(7) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号，查询该客户的名称、地址、总金额以及所有与该客户有关的详细销售信息，并按商品分组输出，输出格式如下：= 客户订单表 = - 客户名称：统一股份有限公司 客户地址：天津市 总 金 额：65000 - 商品编号 数量 价格 P20050001 5 500.00 P20050002 3 500.00 P20050003 2 300.00 P20050004 2 600.00create procedure proSearchCustomeras declare customerno char(9), customername varchar(40),address varchar(50),ordersum numeric(9,2),orderno char(9),quantity int,price numeric(8,2),no char(9),text varchar(60)declare chan cursor forselect a.customerno,a.customername,a.address,d.sumorder,ductno,sum(c.quantity),c.pricefrom customer a,ordermaster b,orderdetail c,(select customerno,sum(ordersum)sumorder from ordermaster group by customerno)dwhere a.customerno=b.customerno and b.orderno=c.orderno and d.customerno=a.customernogroup by a.customerno,a.customername,a.address,d.sumorder,ductno,c.priceorder by a.customernoset text=replicate(=,9)+ 客户订单表+replicate(=,9)print textset text=replicate(-,30)print textopen chanfetch chan into customerno, customername,address ,ordersum ,orderno ,quantity,price set no=customernoset text=客户名称：+customernameprint textset text=客户地址：+addressprint textset text=总金额：+convert(char(9),ordersum)print textset text=replicate(-,30)print textset text=商品编号+space(7)+数量+space(5)+价格print textwhile(fetch_status=0)beginif(no like(customerno)beginset text=orderno+space(7)+convert(char(2),quantity)+space(5)+convert(char(8),price)print textendelsebeginset no=customernoset text=replicate(-,30)print textset text=客户名称：+customernameprint textset text=客户地址：+addressprint textset text=总金额：+convert(char(9),ordersum)print textset text=replicate(-,30)print textset text=商品编号+space(7)+数量+space(5)+价格print textset text=orderno+space(7)+convert(char(2),quantity)+space(5)+convert(char(8),price)print textendfetch chan into customerno, customername,address ,ordersum ,orderno ,quantity,price ENDclose chandeallocate chan执行：exec proSearchCustomer(8) 创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如下：= 订单及其明细数据信息 =-订单编号：200801090001-商品编号 数量 价格 P20050001 5 500.00P20050002 3 500.00P20050003 2 300.00create procedure proorderas declare orderno char(9),productno char(12),quantity int,price numeric(8,2),no char(9),text varchar(60)declare chan cursor forselect a.orderno,ductno,b.quantity,b.pricefrom ordermaster a,orderdetail bwhere a.orderno=b.ordernoorder by a.orderno set text=replicate(=,4)+ 订单及其明细数据信息+replicate(=,4)print textset text=replicate(-,30)print textopen chanfetch chan into orderno,productno,quantity,price set no=ordernoset text=订单编号：+ordernoprint textset text=replicate(-,30)print textset text=商品编号+space(7)+数量+space(5)+价格print textwhile(fetch_status=0)beginif(no like(orderno)beginset text=productno+space(4)+convert(char(2),quantity)+space(5)+convert(char(8),price)print textendelsebeginset no=ordernoset text=replicate(-,30)print textset text=订单编号：+ordernoprint textset text=replicate(-,30)print textset text=商品编号+space(7)+数量+space(5)+价格print textset text=productno+space(4)+convert(char(2),quantity)+space(5)+convert(char(8),price)print textendfetch chan into orderno,productno,quantity,price ENDclose chandeallocate chan执行：exec proorder 第2个实验触发器 第7章实验十三请完成下面实验内容：(1) 创建触发器，该触发器仅允许“dbo”用户可以删除Employee表内数据。create trigger userlimted on employeefor deleteasif exists(select * from deleted)begin if user!=dborollbackend(2) 创建触发器，保证Employee表中性别为”F”或”M”。 create trigger sexlimted on employeefor update,insertasif exists(select * from inserted where sex not in(F,M)rollback(3) 创建触发器，当更新Customer表中CustomerNo列的值时，同时更新OrderMaster表中的CustomerNo