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

GDOU-B-11-112大學(xué)學(xué)生實(shí)驗(yàn)報(bào)告書(shū)實(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)容按要求完成給出的下列題目，要求寫(xiě)出相應(yīng)數(shù)據(jù)庫(kù)的腳本語(yǔ)句。（要求寫(xiě)出存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行語(yǔ)句。）1 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，能夠顯示要求禮物包裝（Orderdetail表）的訂單的號(hào)碼和附言。執(zhí)行相應(yīng)的存儲(chǔ)過(guò)程。CREATE PROCEDURE proc1ASBEGINselect cOrderNo,vMessage from OrderdetailENDexec proc12 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，接收國(guó)家的ID和名稱，并將其插入到Country表中。執(zhí)行該過(guò)程。CREATE PROCEDURE proc3 (x char(3)=050,y char(25)=aa)ASBEGINinsert into country values (x,y)ENDexec proc33 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，接收國(guó)家的ID，在Shopper表，Recipient表和ShippingRate表中查找該國(guó)家是否被參照引用，如果該國(guó)家值沒(méi)有被引用，則刪除相應(yīng)的國(guó)家記錄。執(zhí)行該過(guò)程。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ǔ)過(guò)程，該存儲(chǔ)過(guò)程接收玩具的ID，顯示相應(yīng)的玩具的名稱和價(jià)格。執(zhí)行過(guò)程。CREATE PROCEDURE proc5 (a char(6)ASBEGINselect vToyName,mToyRate from Toys where cToyId =a ENDexec proc5 0000015 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，能夠根據(jù)提供的訂單號(hào)碼顯示相應(yīng)的訂單信息，如果沒(méi)有提供訂單號(hào)碼，則打印消息No Order No。執(zhí)行該過(guò)程。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ǔ)過(guò)程，接收姓和名，在SHOPPER表中查找相應(yīng)的訂購(gòu)者詳細(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ǔ)過(guò)程，對(duì)于接收的訂單號(hào)，如果該訂單訂購(gòu)的總花費(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ǔ)過(guò)程，能夠接收玩具ID，并按下列格式打印出該玩具的名稱，描述和價(jià)格，并列出訂購(gòu)了該玩具的訂單的詳細(xì)信息，寫(xiě)出相應(yīng)的所有存儲(chǔ)過(guò)程。如下所示： 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