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

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)建一個(gè)存儲過程，能夠顯示要求禮物包裝（Orderdetail表）的訂單的號碼和附言。執(zhí)行相應(yīng)的存儲過程。1. Create proc prcOrderDetail As Select cOrderNo,vMessage From Orderdetail exec prcOrderDetail2 創(chuàng)建一個(gè)存儲過程，接收國家的ID和名稱，并將其插入到Country表中。執(zhí)行該過程。2. Create proc prcCountry ID char(3),name char(25)asinsert into Country values(ID,name) exec prcCountry 050,Brazil3 創(chuàng)建一個(gè)存儲過程，接收國家的ID，在Shopper表，Recipient表和ShippingRate表中查找該國家是否被參照引用，如果該國家值沒有被引用，則刪除相應(yīng)的國家記錄。執(zhí)行該過程。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 創(chuàng)建一個(gè)存儲過程，該存儲過程接收玩具的ID，顯示相應(yīng)的玩具的名稱和價(jià)格。執(zhí)行過程。Create proc prctoys toyId char(6)as select vToyName,mToyRate from toys where cToyId=toyIdexec prctoys 0000015 創(chuàng)建一個(gè)存儲過程，能夠根據(jù)提供的訂單號碼顯示相應(yīng)的訂單信息，如果沒有提供訂單號碼，則打印消息No Order No。執(zhí)行該過程。4. Create proc prcOrder OrderNo char(6)=nullasif OrderNo is nullprint No Order No elseselect * from Ordersexec prcOrder 0000016 創(chuàng)建存儲過程，接收姓和名，在SHOPPER表中查找相應(yīng)的訂購者詳細(xì)信息。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 創(chuàng)建存儲過程，對于接收的訂單號，如果該訂單訂購的總花費(fèi)在60元以上，則打印消息并返回值0，否則打印相應(yīng)消息并返回值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 創(chuàng)建存儲過程，能夠接收玩具ID，并按下列格式打印出該玩具的名稱，描述和價(jià)格，并列出訂購了該玩具的訂單的詳細(xì)信息，寫出相應(yīng)的所有存儲過程。如下所示： 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 . 指導(dǎo)教師日期注：請用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