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