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

1、GDOU-B-11-112广东海洋大学学生实验报告书 实验名称实验七：使用存储过程课程名称数据库原理与设计成绩学院（系）软件学院专业计算机软件工程班级学生姓名学号实验地点实验日期实验目的：1. 掌握存储过程的使用方法实验内容按要求完成给出的下列题目，要求写出相应数据库的脚本语句。（要求写出存储过程的创建和执行语句。）1 创建一个存储过程，能够显示要求礼物包装（Orderdetail表）的订单的号码和附言。执行相应的存储过程。1. Create proc prcOrderDetail AsSelect cOrderNo,vMessageFrom Orderdetailexec prcOrderD

2、etail2 创建一个存储过程，接收国家的ID和名称，并将其插入到Country表中。执行该过程。2. Create proc prcCountry ID char(3,name char(25asinsert into Country values(ID,nameexec prcCountry 050,Brazil3 创建一个存储过程，接收国家的ID，在Shopper表，Recipient表和ShippingRate表中查找该国家是否被参照引用，如果该国家值没有被引用，则删除相应的国家记录。执行该过程。3. Create proc prcCountry1 ID char(3asif not

3、exists(select * from Shopper where cCountryID=ID union select * from Recipient where cCountryID=ID union select * from shippingRate where cCountryID=IDdelete from country where cCountryID=IDexec prcCountry1 0494 创建一个存储过程，该存储过程接收玩具的ID，显示相应的玩具的名称和价格。执行过程。Create proc prctoys toyId char(6asselect vToyNa

4、me,mToyRate from toys where cToyId=toyIdexec prctoys 0000015 创建一个存储过程，能够根据提供的订单号码显示相应的订单信息，如果没有提供订单号码，则打印消息No Order No。执行该过程。4. Create proc prcOrder OrderNo char(6=nullasif OrderNo is nullprint No Order Noelseselect * from Ordersexec prcOrder 0000016 创建存储过程，接收姓和名，在SHOPPER表中查找相应的订购者详细信息。5. Create pro

5、c prcShopper fname varchar(30,lname varchar(30asselect * from Shopper where vFirstname=fname and vLastName=lnameexec prcShopper angels,Angela6. Create proc prcOrder1 No char(6asif (select mTotalcost from Orders where cOrderNo=no>60beginPrint Total cost is more than 60return 0endelsebeginprint Tot

6、al cost is less than 60return 1endexec prcOrder1 0000017 创建存储过程，对于接收的订单号，如果该订单订购的总花费在60元以上，则打印消息并返回值0，否则打印相应消息并返回值1。7. Create proc prcOrder1 No char(6asif (select mTotalcost from Orders where cOrderNo=no>60beginPrint Total cost is more than 60return 0endelsebeginprint Total cost is less than 60re

7、turn 1endexec prcOrder1 0000018 创建存储过程，能够接收玩具ID，并按下列格式打印出该玩具的名称，描述和价格，并列出订购了该玩具的订单的详细信息，写出相应的所有存储过程。如下所示：The Name of toy : Robby the Whale The Description of toy: A giant Blue WhalecOrderNo cToyId siQty cGiftWrap cWrapperId vMessage mToyCost - 000005 000001 4 Y 001 Happy Birthday 35.96 . 指导教师日期注：请用A

8、4纸书写，不够另附纸。 第页，共页8. Create proc prcOrderDetail AsSelect cOrderNo,vMessageFrom Orderdetailexec prcOrderDetail9. Create proc prcCountry ID char(3,name char(25asinsert into Country values(ID,nameexec prcCountry 050,Brazil10. Create proc prcCountry1 ID char(3asif not exists(select * from Shopper where c

9、CountryID=ID union select * from Recipient where cCountryID=ID union select * from shippingRate where cCountryID=IDdelete from country where cCountryID=IDexec prcCountry1 04911. Create proc prctoys toyId char(6asselect vToyName,mToyRate from toys where cToyId=toyIdexec prctoys 00000112. Create proc

10、prcOrder OrderNo char(6=nullasif OrderNo is nullprint No Order Noelseselect * from Ordersexec prcOrder 00000113. Create proc prcShopper fname varchar(30,lname varchar(30asselect * from Shopper where vFirstname=fname and vLastName=lnameexec prcShopper angels,Angela14. Create proc prcOrder1 No char(6a

11、sif (select mTotalcost from Orders where cOrderNo=no>60beginPrint Total cost is more than 60return 0endelsebeginprint Total cost is less than 60return 1endexec prcOrder1 00000115. create proc prcToys1 Id char(6,name varchar(30 output, des varchar(30 output,rate money outputasif exists(select * fr

12、om toys where cToyID=IDbeginselect name=vToyName,des=vDescription,rate=mToyRate from Toys where cToyID=IDreturn 0endelsereturn1create proc prcgetToyDetail Id char(6asdeclare x varchar(30declare y varchar(30declare z moneydeclare returnvalue intexec returnvalue=prcgetToydetail Id,x output,y output,z outputif