數(shù)據(jù)庫原理與應(yīng)用實驗12

實驗十二 存儲過程與函數(shù) 薁號：計算機(jī)科學(xué)與技術(shù)學(xué)姓名：羈螃蒆衿薂班級：專業(yè)：薃蕿蚄裊莀實驗日同組人：無期：羄蒈羈芄蒁薄莈螅薂袁罿【實驗?zāi)康呐c要求】 袇 ．1 熟練掌握存儲過程的編寫。 2．蒅 3．熟練掌握函數(shù)的編寫與使用。．4莁【實驗內(nèi)容與步驟】莂14.1.基礎(chǔ)知識14.2.芆T-SQL和函數(shù)是一組編譯好存儲在服務(wù)器上的完成特定功能StoredProcedure）存儲過程（芅客戶端應(yīng)用程序可以通過指定存儲過程或函數(shù)的名字并給出參數(shù)代碼，是某數(shù)據(jù)庫的對象。（如果該存儲過程帶有參數(shù)）來執(zhí)行存儲過程。14.3.創(chuàng)建用戶存儲過程14.4.蒃1.使用存儲過程模板創(chuàng)建存儲過程蒀在【對象資源管理器】窗口中，展開“數(shù)據(jù)庫”節(jié)點，再展開所選擇的具體數(shù)據(jù)庫節(jié)點， 蚆再展開選擇“可編程性”節(jié)點，右擊“存儲過程” ，選擇“新建存儲過程”命令，如圖所示：羆在右側(cè)查詢編輯器中出現(xiàn)存儲過程的模板，可以在此基礎(chǔ)上編輯存儲過程，單擊“執(zhí)行 蒄按鈕，即可創(chuàng)建該存儲過程。薈荿實驗14-1：創(chuàng)建一個簡單的存儲過程，實現(xiàn)從 CP表中讀取 Mp3產(chǎn)品相關(guān)信息 螆USECPXS 芁GO羈此為無參存儲過程 -- get_mp3PROCEDURECREATE螈．AS蒆價格,庫存量 SELECT產(chǎn)品編號,產(chǎn)品名稱,莃FROMCP聿'mp3'WHERE產(chǎn)品名稱=羋執(zhí)行存儲過程 實驗14-2：羃執(zhí)行存儲過程可用下列方法之一： 莄）使用存儲過程名字如： get_mp3（1蒂2)使用Exec命令：如：EXECget_mp3蚇執(zhí)行上面創(chuàng)建的存儲過程，并給出執(zhí)行結(jié)果： 蚃膁．薀使用T-SQL語句創(chuàng)建存儲過程肇在查詢分析器里使用 T-SQL可直接創(chuàng)建存儲過程 蒄格式：芃CREATEPROC 過程名蚈@形參名 類型蒆@變參名 類型 OUTPUT 膄AS 莄SQL語句肁實驗14-3：創(chuàng)建一個多表查詢的存儲過程。羅問題：查詢在2009年9月18日有銷售的產(chǎn)品名稱羄）請給出相應(yīng)的代碼（1膁CREATEprocedureG_product腿AS蠆產(chǎn)品名稱 SELECT蚅cpxsbFROMCP,膃.CP產(chǎn)品編號 and'2009-9-180:00:00';銷售日期==.WHERECPXSB產(chǎn)品編號薁肈蒅（2）執(zhí)行存儲過程 ,并給出執(zhí)行結(jié)果： 羀蝕14.5.14.6. 存儲過程的參數(shù)蕆輸入?yún)?shù)（值參）膅實驗14-4：輸入?yún)?shù)為某產(chǎn)品的名字。 肂USECPXS螈GO袇CREATEPROCEDUREP_CPXS2 袆@Product_namechar(30) --形式參數(shù) 肅As膀SELECT 產(chǎn)品編號,產(chǎn)品名稱,價格,庫存量 莆FROMCP蚆WHERE 產(chǎn)品名稱=@Product_name袀GO艿執(zhí)行存儲過程： 螆（1）直接傳值： 莇EXECP_CPXS2 '冰箱' --實參表 羂請給出執(zhí)行結(jié)果： 薁葿袃（2）變量傳值： 螀DECLARE @tempchar(30)袈SET@temp='洗衣機(jī)'薃EXECP_CPXS2@temp --實參表螀請給出執(zhí)行結(jié)果： 袈羋實驗14-5：使用默認(rèn)參數(shù) 莄閱讀以下程序段，理解參數(shù)傳遞過程 袂USECPXS膀GO螇CREATEPROCEDUREP_CPXS3肄@namevarchar(10)=NULL --默認(rèn)參數(shù)羃AS艿IF@nameISNULL膆SELECT產(chǎn)品編號,產(chǎn)品名稱,價格,庫存量襖FROMCP螁ELSE蟻SELECT產(chǎn)品編號,產(chǎn)品名稱,價格,庫存量薆CPFROM薅．WHERE 產(chǎn)品名稱=@name螂GO蝿執(zhí)行存儲過程： 艿蒞不帶參數(shù)時袃EXECP_CPXS3袈請給出執(zhí)行結(jié)果： 蠆肆帶參數(shù)時蟻EXECP_CPXS3‘彩色電視機(jī) '芀請給出執(zhí)行結(jié)果： 膈袆2.輸出參數(shù)（變參） 螞實驗14-6：利用輸出參數(shù)計算階乘。荿USECPXS薇–判斷系統(tǒng)中是否有名為factorial的存儲過程，若有，則刪除之薆IFEXISTS(SELECTnameFROMsysobjects螄WHEREname='factorial'ANDtype='P')螁DROPPROCEDUREfactorial羇GO--前面這段僅是準(zhǔn)備工作，真正的工作在之后芇CREATE--PROCEDUREfactorial創(chuàng)建存儲過程薁@infloat, --輸入形式參數(shù)@outfloatOUTPUT --輸出形式參數(shù)AS螃DECLARE@iint薂DECLARE@sfloat羈SET@i=1裊SET@s=1薃WHILE@i<=@in蚄

衿蒆BEGIN莀SET@s=@s*@i@i+1 SET@i=

蕿芄END 蒁SET@out=@s--給輸出參數(shù)賦值 蒈調(diào)用存儲過程： 羈羄DECLARE@oufloat–定義變量，用于存儲結(jié)果值薂EXECfactorial5,@ouOUT--實參表袁PRINT@ou莈請給出執(zhí)行結(jié)果： 螅薄14.7.14.8. 創(chuàng)建用戶自定義函數(shù)罿1.使用存儲過程模板創(chuàng)建存儲過程 袇在【對象資源管理器】窗口中，展開“數(shù)據(jù)庫”節(jié)點，再展開所選擇的具體數(shù)據(jù)庫節(jié)點 蒅再展開選擇“可編程性”節(jié)點，右擊“函數(shù)”，選擇“新建”命令，在下一級菜單中選擇適合的選項，如圖所示：莁在右側(cè)查詢編輯器中出現(xiàn)函數(shù)的模板，可以在此基礎(chǔ)上編輯函數(shù)，單擊“執(zhí)行”按鈕 莂即可創(chuàng)建該函數(shù)。芆2.使用T-SQL語句創(chuàng)建函數(shù)芅函數(shù)分為標(biāo)量函數(shù)和表值函數(shù)。前者返回一個標(biāo)量值結(jié)果，在創(chuàng)建函數(shù)時，應(yīng)在Returns蒃語句后指明標(biāo)量類型(如：int)；后者以表的形式返回結(jié)果，在創(chuàng)建函數(shù)時，應(yīng)在Returns語句后用關(guān)鍵詞Table指時其反回類型?？芍苯觿?chuàng)建函數(shù) T-SQL在查詢分析器里可使用 蒀．格式： 蚆CREATEFUNCTION 函數(shù)名(莀@形參名 類型@變參名 類型

袈袂)蚃[RETURNS 類型]肀AS 蚅SQL語句 芅膂使用標(biāo)量函數(shù)螀標(biāo)量函數(shù)接受 0常在一個 select

個或多個輸入?yún)?shù)，并返回一個標(biāo)量值。因為標(biāo)量函數(shù)只返回一個值，語句的列列表中使用它們，也可以在where子句中使用它們。

蚇所以通實驗14-7：基于CP表編寫函數(shù)getStock()，根據(jù)傳進(jìn)的參數(shù)“產(chǎn)品編號”，查詢并返回莃相應(yīng)產(chǎn)品的“庫存量”。請閱讀以下程序，理解其基本結(jié)構(gòu)和實現(xiàn)思想，給出運行結(jié)果。 薂芇createfunctiongetStock(@productidchar(6))螈intreturns 螆as羈begin肇int;declare@stock薅庫存量select@stock=襖cp.fromdbo莁@productid產(chǎn)品編號=where螈;return@stock薇end;羂調(diào)用函數(shù)： 袀函數(shù)一般在 Select語句或Where子句中被調(diào)用，以下是一函數(shù)調(diào)用實例： 蒈蚈selectdbo.getStock('100001')as庫存量 蒞請給出執(zhí)行結(jié)果： 艿羋蒆使用表值函數(shù)羃表值函數(shù)遵守與標(biāo)量函數(shù)相同的規(guī)則，區(qū)別在于表值函數(shù)返回一個表作為輸出。因此 罿一般在select語句的 from子句中使用它們，并可能與其他表或視圖進(jìn)行聯(lián)接。實驗14-8：創(chuàng)建一個自定義函數(shù)fun_cpInfo()，根據(jù)產(chǎn)品編號返回該產(chǎn)品的名稱、價蕆和庫存量。createfunctionfun_cpInfo(@product_Nochar(6))裊returnstable--表值函數(shù)，返回查詢結(jié)果集(即表)莂as蝿return(select產(chǎn)品名稱,價格,庫存量芄fromCP羄where產(chǎn)品編號=@product_No)螁調(diào)用函數(shù)：葿函數(shù)創(chuàng)建后，可在SQL語句中調(diào)用。調(diào)用函數(shù)fun_cpInfo()，可在查詢分析器中執(zhí)行如莆下Select語句：肂請給出執(zhí)行結(jié)果： 芁芀蕆思考：請比較標(biāo)量函數(shù)和表值函數(shù)，理解其在編寫和使用上的差別。 蝕沒什么差別。羀14.9.14.10. 實驗練習(xí)芄 存儲過程 薃對于CPXS數(shù)據(jù)庫，完成以下存儲過程。 肀無參存儲過程螁編寫一無參存儲過程用于查詢每個客戶購買產(chǎn)品的情況（包括客戶編號、 產(chǎn)品編號、客芆，然后調(diào)用該存儲過程。戶名稱、產(chǎn)品名稱、價格、購買日期、購買數(shù)量） ．請給出程序源碼： 羅createprocedureau_info_all 螃as膇select*莇fromcpleftjoincpxsb肄onCPXSB.產(chǎn)品編號=CP.產(chǎn)品編號 leftjoinxss膂oncpxsb.客戶編號=xss.客戶編 羇膅請給出執(zhí)行測試結(jié)果： 膂螞蚈．帶有參數(shù)的存儲過程肁編寫一加密存儲過程，查詢指定客戶購買產(chǎn)品的情況。并調(diào)用該存儲過程查詢客戶編蒈為“000002”的客戶購買情況。請給出程序源碼： 芇createprocedureaa_info_all@guestnochar(6)withencryption蒀as膈select肅fromcpjoinleft cpxs肅．onCPXSB.產(chǎn)品編號=CP.產(chǎn)品編號leftjoinxss羀oncpxsb.客戶編號=xss.客戶編號罿wherexss.客戶編號=@guestno;肆請給出執(zhí)行膄測試結(jié)果：薃．3.帶有通配符參數(shù)的存儲過程 膇編寫一存儲過程，查詢指定產(chǎn)品的銷售情況。如果沒有提供參數(shù)，則查詢產(chǎn)品名稱中 節(jié)含有“冰箱”的產(chǎn)品銷售情況。請給出程序源碼： 肅createprocedureaaa_info_all(@productnamechar(16))莀as羅select數(shù)量蚄fromcpleftjoincpxsb蒂onCPXSB.產(chǎn)品編號=CP.產(chǎn)品編號 leftjoinxss膀oncpxsb.客戶編號=xss.客戶編號肆where產(chǎn)品名稱=@productname 螃袁請給出執(zhí)行測試結(jié)果： 袀肈肅莁參數(shù)的存儲過程

OUTPUT

帶有

4.

蟻．編寫一存儲過程，查詢指定客戶在指定時間段內(nèi)購買指定產(chǎn)品的數(shù)量，存儲過程中使用裊了輸入和輸出參數(shù)。并調(diào)用該存儲過程查詢名稱為“家電市場”的客戶在2004年購買“洗衣機(jī)”的數(shù)量。請給出程序源碼：芃createprocedureau螀@productnumintoutput,肇@guestnamechar(30),羆@datechar(4),莂@productnamechar(30)腿as袇select@productnum=數(shù)量羈fromcp,cpxsb,xss蚄whereCPXSB.產(chǎn)品編號=CP.產(chǎn)品編號andcpxsb.客戶編號=xss.客戶編號袃andxss.客戶名稱=@guestnameandcpxsb.銷售日期like'%'+@date+'%'薈andcp.產(chǎn)品名稱=@productname螅螂請給出執(zhí)行測試結(jié)果： 節(jié)莈．袆帶有OUTPUT游標(biāo)參數(shù)的存儲過程膅編寫一帶有 OUTPUT游標(biāo)參數(shù)的存儲過程，游標(biāo)結(jié)果集為客戶信息，并通過調(diào)用該存 螂儲過程，實現(xiàn)依次讀取游標(biāo) CUR2中各行數(shù)據(jù)。請給出程序源碼： 聿createproceduretitl_cursor@titl_cursorcursorvaryingoutput袈as芃cursor=@titl_cursorset膁．forward_onlystatic for 衿*select蚅xssfrom蚆薀open@titl_cursor 蕿螇declare@mycursorcursor螄output@mycursor=titl_cursorfrom@mycursorfetchnext莀)@@fetch_status=0while(袈begin袂@mycursor fetchnextfromend肀

蚃

@titl_cursorexec羀蚅請給出執(zhí)行測試結(jié)果： 芅膂．螀蚇6.創(chuàng)建一個多表查詢的存儲過程。莃問題：查詢在2009年9月18日有銷售的產(chǎn)品名稱(若無此數(shù)據(jù)，請先添加之，以便薂于測試)。）請給出相應(yīng)的代碼 1（芇．createprocedureau_info_alll 螈as螆產(chǎn)品名稱 selectcp.羈joincpxsbfromcpleft肇xssleft產(chǎn)品編號 join=onCPXSB.產(chǎn)品編號 CP.薅客戶編號 oncpxsb.=xss.客戶編號襖.cpxsb銷售日期'2009-09-18'=where莁螈（2）執(zhí)行存儲過程 ,并給出執(zhí)行結(jié)果： 薇羂袀14.5.2函數(shù)蒈對于CPXS數(shù)據(jù)庫，定義完成如下功能的函數(shù)。蚈1.據(jù)產(chǎn)品名稱，查詢該產(chǎn)品的相關(guān)信息。（函數(shù)名為：FU_CP）蒞請給出程序源碼： 艿createfunctionfu_cp(@productnamechar(12))羋returnstable蒆as蒃return羃(罿select*蕆fromcp裊where產(chǎn)品名稱=@productname莂)蝿芄請給出執(zhí)行測試結(jié)果： 查詢產(chǎn)品名稱為“ mp3”的產(chǎn)品情況 羄select*螁fromfu_cp('mp3')葿莆．2.按某年某季度統(tǒng)計給定產(chǎn)品名稱的銷售數(shù)量及銷售金額。

(函數(shù)名為：

FU1_CPXS)

芁請給出程序源碼：

芀CREATEFUNCTION

FU1_CPXS

(@YEAR

INT,@QUARTER

INT,@產(chǎn)品名稱

char(10))

蕆RETURNSTABLE

蒅AS

蝕RETURN 羀SELECT 產(chǎn)品名稱,SUM(數(shù)量)AS銷售數(shù)量,SUM(銷售額)AS銷售總額 芄FROMCP,CPXSB 薃產(chǎn)品名稱 @=產(chǎn)品名稱 AND產(chǎn)品編號.CP=產(chǎn)品編號.CPXSBWHERE肀．ANDDATEPART(YY,銷售日期)=@YEARAN 螁芆請給出執(zhí)行測試結(jié)果： 查詢2004年第3季度彩色電視機(jī)的銷售數(shù)量和銷售金額select*fromFU1_CPXS('2004','03','彩色電視機(jī) ')3.根據(jù)銷售商名稱， 統(tǒng)計其在某年某季度內(nèi)銷售商品名稱、

數(shù)量及金額。（函數(shù)名為

FU2_CPXS

）請給出程序源碼：CREATEFUNCTION FU3_CPXS(@客戶名稱 char(10),@YEARINT,@QUARTERRETURNSTABLEASRETURNSELEC