SQL實(shí)驗(yàn)7：使用存儲(chǔ)過程.doc

GDOU-B-11-112大學(xué)學(xué)生實(shí)驗(yàn)報(bào)告書實(shí)驗(yàn)名稱實(shí)驗(yàn)七：使用存儲(chǔ)過程課程名稱數(shù)據(jù)庫原理與設(shè)計(jì)成績學(xué)院（系）軟件學(xué)院專業(yè)計(jì)算機(jī)軟件工程班級學(xué)生姓名學(xué)號實(shí)驗(yàn)地點(diǎn)實(shí)驗(yàn)日期實(shí)驗(yàn)?zāi)康模?. 掌握存儲(chǔ)過程的使用方法實(shí)驗(yàn)內(nèi)容按要求完成給出的下列題目，要求寫出相應(yīng)數(shù)據(jù)庫的腳本語句。（要求寫出存儲(chǔ)過程的創(chuàng)建和執(zhí)行語句。）1 創(chuàng)建一個(gè)存儲(chǔ)過程，能夠顯示要求禮物包裝（Orderdetail表）的訂單的號碼和附言。執(zhí)行相應(yīng)的存儲(chǔ)過程。CREATE PROCEDURE proc1ASBEGINselect cOrderNo,vMessage from OrderdetailENDexec proc12 創(chuàng)建一個(gè)存儲(chǔ)過程，接收國家的ID和名稱，并將其插入到Country表中。執(zhí)行該過程。CREATE PROCEDURE proc3 (x char(3)=050,y char(25)=aa)ASBEGINinsert into country values (x,y)ENDexec proc33 創(chuàng)建一個(gè)存儲(chǔ)過程，接收國家的ID，在Shopper表，Recipient表和ShippingRate表中查找該國家是否被參照引用，如果該國家值沒有被引用，則刪除相應(yīng)的國家記錄。執(zhí)行該過程。CREATE PROCEDURE proc4 (z char(3)AS begin if (not exists (select * from Shopper where Shopper.cCountryId=z)or not exists (select * from Recipient where Recipient.cCountryId=z)or not exists (select *from ShippingRate where ShippingRate.cCountryId=z)delete from Country where Country.cCountryId=zEndexec proc4 0504 創(chuàng)建一個(gè)存儲(chǔ)過程，該存儲(chǔ)過程接收玩具的ID，顯示相應(yīng)的玩具的名稱和價(jià)格。執(zhí)行過程。CREATE PROCEDURE proc5 (a char(6)ASBEGINselect vToyName,mToyRate from Toys where cToyId =a ENDexec proc5 0000015 創(chuàng)建一個(gè)存儲(chǔ)過程，能夠根據(jù)提供的訂單號碼顯示相應(yīng)的訂單信息，如果沒有提供訂單號碼，則打印消息No Order No。執(zhí)行該過程。CREATE PROC proc6 (b char(6)ASBEGINIF EXISTS(SELECT * FROM Orders WHERE cOrderNo=b)BEGIN SELECT *FROM Orders WHERE cOrderNo=bENDELSEBEGINPRINT No Order NoENDENDexec proc6 000000exec proc6 0000026 創(chuàng)建存儲(chǔ)過程，接收姓和名，在SHOPPER表中查找相應(yīng)的訂購者詳細(xì)信息。CREATE PROC proc7 (c char(20),d char(20) )ASBEGINselect * from dbo.Shopper where vFirstName=c and vLastName=d ENDexec proc7 betty,williams7 創(chuàng)建存儲(chǔ)過程，對于接收的訂單號，如果該訂單訂購的總花費(fèi)在60元以上，則打印消息并返回值0，否則打印相應(yīng)消息并返回值1。create PROC proc8 (e char(6)ASBEGINIF (SELECT mTotalCost FROM Orders WHERE cOrderNo=e )60BEGIN SELECT *FROM Orders WHERE cOrderNo=e RETURN 0ENDELSEBEGINPRINT No more than 60RETURN 1ENDENDexec proc8 000002exec proc8 0000078 創(chuàng)建存儲(chǔ)過程，能夠接收玩具ID，并按下列格式打印出該玩具的名稱，描述和價(jià)格，并列出訂購了該玩具的訂單的詳細(xì)信息，寫出相應(yīng)的所有存儲(chǔ)過程。如下所示： The Name of toy : Robby the Whale The Description of toy: A giant Blue Whale wThe Price of toy : 8.99cOrderNo cToyId siQty cGiftWrap cWrapperId vMessage mToyCost - 000005 000001 4 Y 001 Happy Birthday 35.96 create proc proc10 toyid char(6), toyName varchar(20), TOyDescription varchar(250) output, toyrate money outputasbegin if exists (select * from Toys where cToyId =toyid ) begin select toyName =vToyName,TOyDescription =vToyDescription, toyrate =mToyRate from Toys where cToyId =toyid return 0 end else return 1endcreate proc proc11 toyid char(6)=000001, toyname varchar(20) output, ToyDescription varchar(250) output, ToyRate money outputasbegin declare Name varchar(20) declare Description varchar(250) declare rate money declare ret int exec ret =proc10 toyid ,toyname ,ToyDescription output, Rate output if ret=0 begin printThe Name of toy :+Name printThe Description of toy:+Description printThe Price of toy :+rate select cOrderNo,cToyId,siQty,cGiftW