实验8：使用存储过程(答案).doc

GDOU-B-11-112广东海洋大学学生实验报告书实验名称实验七：使用存储过程课程名称数据库原理与设计成绩学院（系）软件学院专业计算机软件工程班级学生姓名学号实验地点实验日期实验目的：1. 掌握存储过程的使用方法实验内容按要求完成给出的下列题目，要求写出相应数据库的脚本语句。（要求写出存储过程的创建和执行语句。）1 创建一个存储过程，能够显示要求礼物包装（Orderdetail表）的订单的号码和附言。执行相应的存储过程。1. Create proc prcOrderDetail As Select cOrderNo,vMessage From Orderdetail exec prcOrderDetail2 创建一个存储过程，接收国家的ID和名称，并将其插入到Country表中。执行该过程。2. Create proc prcCountry ID char(3),name char(25)asinsert into Country values(ID,name) exec prcCountry 050,Brazil3 创建一个存储过程，接收国家的ID，在Shopper表，Recipient表和ShippingRate表中查找该国家是否被参照引用，如果该国家值没有被引用，则删除相应的国家记录。执行该过程。3. Create proc prcCountry1 ID char(3)asif not exists(select * from Shopper where cCountryID=ID union select * from Recipient where cCountryID=ID union select * from shippingRate where cCountryID=ID) delete from country where cCountryID=ID exec prcCountry1 0494 创建一个存储过程，该存储过程接收玩具的ID，显示相应的玩具的名称和价格。执行过程。Create proc prctoys toyId char(6)as select vToyName,mToyRate from toys where cToyId=toyIdexec prctoys 0000015 创建一个存储过程，能够根据提供的订单号码显示相应的订单信息，如果没有提供订单号码，则打印消息No Order No。执行该过程。4. Create proc prcOrder OrderNo char(6)=nullasif OrderNo is nullprint No Order No elseselect * from Ordersexec prcOrder 0000016 创建存储过程，接收姓和名，在SHOPPER表中查找相应的订购者详细信息。5. Create proc prcShopper fname varchar(30),lname varchar(30)asselect * from Shopper where vFirstname=fname and vLastName=lname exec prcShopper angels,Angela6. Create proc prcOrder1 No char(6)asif (select mTotalcost from Orders where cOrderNo=no)60beginPrint Total cost is more than 60return 0endelsebeginprint Total cost is less than 60return 1end exec prcOrder1 0000017 创建存储过程，对于接收的订单号，如果该订单订购的总花费在60元以上，则打印消息并返回值0，否则打印相应消息并返回值1。7. Create proc prcOrder1 No char(6)asif (select mTotalcost from Orders where cOrderNo=no)60beginPrint Total cost is more than 60return 0endelsebeginprint Total cost is less than 60return 1end exec prcOrder1 0000018 创建存储过程，能够接收玩具ID，并按下列格式打印出该玩具的名称，描述和价格，并列出订购了该玩具的订单的详细信息，写出相应的所有存储过程。如下所示： The Name of toy : Robby the Whale The Description of toy: A giant Blue WhaleThe Price of toy : 8.99cOrderNo cToyId siQty cGiftWrap cWrapperId vMessage mToyCost - 000005 000001 4 Y 001 Happy Birthday 35.96 . 指导教师日期注：请用A4纸书写，不够另附纸。第页，共页8. Create proc prcOrderDetail As Select cOrderNo,vMessage From Orderdetail exec prcOrderDetail9. Create proc prcCountry ID char(3),name char(25)asinsert into Country values(ID,name) exec prcCountry 050,Brazil10. Create proc prcCountry1 ID char(3)asif not exists(select * from Shopper where cCountryID=ID union select * from Recipient where cCountryID=ID union select * from shippingRate where cCountryID=ID) delete from country where cCountryID=ID exec prcCountry1 04911. Create proc prctoys toyId char(6)as select vToyName,mToyRate from toys where cToyId=toyId exec prctoys 00000112. Create proc prcOrder OrderNo char(6)=nullasif OrderNo is nullprint No Order No elseselect * from Ordersexec prcOrder 00000113. Create proc prcShopper fname varchar(30),lname varchar(30)asselect * from Shopper where vFirstname=fname and vLastName=lname exec prcShopper angels,Angela14. Create proc prcOrder1 No char(6)asif (select mTotalcost from Orders where cOrderNo=no)60beginPrint Total cost is more than 60return 0endelsebeginprint Total cost is less than 60return 1end exec prcOrder1 00000115. create proc prcToys1 Id char(6),name varchar(30) output, des varchar(30) output,rate money outputasif exists(select * from toys where cToyID=ID)beginselect name=vToyName,des=vDescription,rate=mToyRate from Toys where cToyID=IDreturn 0endelsereturn1create proc prcgetToyDetail Id char(6)as declare x varchar(30) declare y varchar(30) declare z money declare returnvalue int exec returnvalue=prcgetToydetail Id,x output,y output,z output if ret