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

GDOU-B-11-112廣東海洋大學(xué)學(xué)生實(shí)驗(yàn)報(bào)告書實(shí)驗(yàn)名稱實(shí)驗(yàn)七：使用存儲(chǔ)過(guò)程課程名稱數(shù)據(jù)庫(kù)原理與設(shè)計(jì)成績(jī)學(xué)院（系）軟件學(xué)院專業(yè)計(jì)算機(jī)軟件工程班級(jí)學(xué)生姓名學(xué)號(hào)實(shí)驗(yàn)地點(diǎn)實(shí)驗(yàn)日期實(shí)驗(yàn)?zāi)康模?. 掌握存儲(chǔ)過(guò)程的使用方法實(shí)驗(yàn)內(nèi)容按要求完成給出的下列題目，要求寫出相應(yīng)數(shù)據(jù)庫(kù)的腳本語(yǔ)句。（要求寫出存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行語(yǔ)句。）1 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，能夠顯示要求禮物包裝（Orderdetail表）的訂單的號(hào)碼和附言。執(zhí)行相應(yīng)的存儲(chǔ)過(guò)程。1. Create proc prcOrderDetail As Select cOrderNo,vMessage From Orderdetail exec prcOrderDetail2 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，接收國(guó)家的ID和名稱，并將其插入到Country表中。執(zhí)行該過(guò)程。2. Create proc prcCountry ID char(3),name char(25)asinsert into Country values(ID,name) exec prcCountry 050,Brazil3 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，接收國(guó)家的ID，在Shopper表，Recipient表和ShippingRate表中查找該國(guó)家是否被參照引用，如果該國(guó)家值沒有被引用，則刪除相應(yīng)的國(guó)家記錄。執(zhí)行該過(guò)程。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è)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程接收玩具的ID，顯示相應(yīng)的玩具的名稱和價(jià)格。執(zhí)行過(guò)程。Create proc prctoys toyId char(6)as select vToyName,mToyRate from toys where cToyId=toyIdexec prctoys 0000015 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，能夠根據(jù)提供的訂單號(hào)碼顯示相應(yīng)的訂單信息，如果沒有提供訂單號(hào)碼，則打印消息No Order No。執(zhí)行該過(guò)程。4. Create proc prcOrder OrderNo char(6)=nullasif OrderNo is nullprint No Order No elseselect * from Ordersexec prcOrder 0000016 創(chuàng)建存儲(chǔ)過(guò)程，接收姓和名，在SHOPPER表中查找相應(yīng)的訂購(gòu)者詳細(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)建存儲(chǔ)過(guò)程，對(duì)于接收的訂單號(hào)，如果該訂單訂購(gòu)的總花費(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)建存儲(chǔ)過(guò)程，能夠接收玩具ID，并按下列格式打印出該玩具的名稱，描述和價(jià)格，并列出訂購(gòu)了該玩具的訂單的詳細(xì)信息，寫出相應(yīng)的所有存儲(chǔ)過(guò)程。如下所示： 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)教師日期注：請(qǐng)用A4紙書寫，不夠另附紙。第頁(yè)，共頁(yè)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