實(shí)驗(yàn)8：使用存儲過程(答案)

1、GDOU-B-11-112廣東海洋大學(xué)學(xué)生實(shí)驗(yàn)報(bào)告書 實(shí)驗(yàn)名稱實(shí)驗(yàn)七：使用存儲過程課程名稱數(shù)據(jù)庫原理與設(shè)計(jì)成績學(xué)院（系）軟件學(xué)院專業(yè)計(jì)算機(jī)軟件工程班級學(xué)生姓名學(xué)號實(shí)驗(yàn)地點(diǎn)實(shí)驗(yàn)日期實(shí)驗(yàn)?zāi)康模?. 掌握存儲過程的使用方法實(shí)驗(yàn)內(nèi)容按要求完成給出的下列題目，要求寫出相應(yīng)數(shù)據(jù)庫的腳本語句。（要求寫出存儲過程的創(chuàng)建和執(zhí)行語句。）1 創(chuàng)建一個存儲過程，能夠顯示要求禮物包裝（Orderdetail表）的訂單的號碼和附言。執(zhí)行相應(yīng)的存儲過程。1. Create proc prcOrderDetail AsSelect cOrderNo,vMessageFrom Orderdetailexec prcOrderD

2、etail2 創(chuàng)建一個存儲過程，接收國家的ID和名稱，并將其插入到Country表中。執(zhí)行該過程。2. Create proc prcCountry ID char(3,name char(25asinsert into Country values(ID,nameexec prcCountry 050,Brazil3 創(chuàng)建一個存儲過程，接收國家的ID，在Shopper表，Recipient表和ShippingRate表中查找該國家是否被參照引用，如果該國家值沒有被引用，則刪除相應(yīng)的國家記錄。執(zhí)行該過程。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 創(chuàng)建一個存儲過程，該存儲過程接收玩具的ID，顯示相應(yīng)的玩具的名稱和價(jià)格。執(zhí)行過程。Create proc prctoys toyId char(6asselect vToyNa

4、me,mToyRate from toys where cToyId=toyIdexec prctoys 0000015 創(chuàng)建一個存儲過程，能夠根據(jù)提供的訂單號碼顯示相應(yīng)的訂單信息，如果沒有提供訂單號碼，則打印消息No Order No。執(zhí)行該過程。4. Create proc prcOrder OrderNo char(6=nullasif OrderNo is nullprint No Order Noelseselect * from Ordersexec prcOrder 0000016 創(chuàng)建存儲過程，接收姓和名，在SHOPPER表中查找相應(yīng)的訂購者詳細(xì)信息。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 創(chuàng)建存儲過程，對于接收的訂單號，如果該訂單訂購的總花費(fèi)在60元以上，則打印消息并返回值0，否則打印相應(yīng)消息并返回值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 創(chuàng)建存儲過程，能夠接收玩具ID，并按下列格式打印出該玩具的名稱，描述和價(jià)格，并列出訂購了該玩具的訂單的詳細(xì)信息，寫出相應(yīng)的所有存儲過程。如下所示：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 . 指導(dǎo)教師日期注：請用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