第八章數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建

第八章數(shù)據(jù)庫(kù)編程1應(yīng)用程序和數(shù)據(jù)庫(kù)之間的主要編程接口：SQL語(yǔ)言1.在客戶端存儲(chǔ)代碼，并創(chuàng)建編寫訪問數(shù)據(jù)庫(kù)服務(wù)器發(fā)送SQL語(yǔ)句（比如在C#、Java等客戶端編程語(yǔ)言中嵌入訪問數(shù)據(jù)庫(kù)的SQL語(yǔ)句）2.將sql語(yǔ)句存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器端，然后后應(yīng)用程序調(diào)用執(zhí)行這些SQL語(yǔ)句。2第8章存儲(chǔ)過(guò)程8.1存儲(chǔ)過(guò)程概述8.2創(chuàng)建管理無(wú)參存儲(chǔ)過(guò)程

8.3創(chuàng)建管理帶參的存儲(chǔ)過(guò)程

38.1存儲(chǔ)過(guò)程概述1.存儲(chǔ)過(guò)程概念存儲(chǔ)過(guò)程是為了實(shí)現(xiàn)某個(gè)特定任務(wù)，由一組預(yù)先編譯好的SQL語(yǔ)句組成，將其放在服務(wù)器上，由用戶通過(guò)指定存儲(chǔ)過(guò)程的名字來(lái)執(zhí)行的一種數(shù)據(jù)庫(kù)對(duì)象。2.存儲(chǔ)過(guò)程類型

系統(tǒng)存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程用戶自定義存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)以SP_為前綴,是由SQLServer2005自己創(chuàng)建、管理和使用的一種特殊的存儲(chǔ)過(guò)程，不能對(duì)其進(jìn)行修改或刪除。如Sp_helpdb、Sp_renamedb等。由用戶自行創(chuàng)建的存儲(chǔ)過(guò)程，可以輸入?yún)?shù)、向客戶端返回表格或結(jié)果、消息等，也可以返回輸出函數(shù)。48.2無(wú)參存儲(chǔ)過(guò)程的創(chuàng)建使用SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程1）語(yǔ)法格式如下：CREATEPROC[EDURE

]procedure_nameASsql_statement[...n]

2）語(yǔ)法注釋：

procedure_name：新建存儲(chǔ)過(guò)程的名稱，其名稱必須符合標(biāo)識(shí)符命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一。sql_statement：指存儲(chǔ)過(guò)程中的任意數(shù)目和類型的Transact-SQL語(yǔ)句。58.2無(wú)參存儲(chǔ)過(guò)程的創(chuàng)建例1：返回200030000041號(hào)學(xué)生的成績(jī)，不使用參數(shù)。代碼如下：CREATEPROCEDUREstudent_infoASSELECT*FROM學(xué)生信息WHERE學(xué)號(hào)='200030000041'6創(chuàng)建存儲(chǔ)過(guò)程的注意事項(xiàng)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。數(shù)據(jù)庫(kù)的所有者可以創(chuàng)建存儲(chǔ)過(guò)程，也可以授權(quán)其他用戶創(chuàng)建存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵守標(biāo)識(shí)符命名規(guī)則。不能將CREATEPROCEDURE語(yǔ)句與其它SQL語(yǔ)句組合到單個(gè)批處理中。創(chuàng)建存儲(chǔ)過(guò)程時(shí)，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過(guò)程或批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句和返回至調(diào)用過(guò)程或批處理以表明成功或失敗的狀態(tài)值。78.2無(wú)參存儲(chǔ)過(guò)程的執(zhí)行對(duì)存儲(chǔ)在服務(wù)器上的存儲(chǔ)過(guò)程，可以使用EXECUTE命令或其名稱執(zhí)行它，其語(yǔ)法格式如下：

EXEC[UTE]procedure_name對(duì)上例的存儲(chǔ)過(guò)程pr_searchorddate的執(zhí)行語(yǔ)句如下：EXECstudent_info88.3創(chuàng)建帶參存儲(chǔ)過(guò)程的語(yǔ)法1）語(yǔ)法格式如下：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement98.3創(chuàng)建帶參存儲(chǔ)過(guò)程的語(yǔ)法2）語(yǔ)法注釋：parameter：存儲(chǔ)過(guò)程中的輸入和輸出參數(shù)。

data_type：參數(shù)的數(shù)據(jù)類型。DEFAULT：表示參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，則在調(diào)用存儲(chǔ)過(guò)程時(shí)，可以省略該參數(shù)的值。

OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。10帶參數(shù)的存儲(chǔ)過(guò)程例子：查詢某個(gè)指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績(jī)。createprocedurep_StudentGrade1@deptchar(20)asselectsname,sdept,cname,gradefromstudentsinnerjoinscons.sno=o=owheresdept=@dept11帶多個(gè)輸入?yún)?shù)并有默認(rèn)值的存儲(chǔ)過(guò)程例子1：查詢某個(gè)學(xué)生某門課程的考試成績(jī)，課程的默認(rèn)值為“VB”Createprocp_StudentGrade2

@snamechar(10),@cnamechar(20)=‘VB’Asselectsname,cname,gradefromstudentsinnerjoinscons.sno=o=o

wheresname=@snameandcname=@cname12例2：CREATEPROCdeletestu@學(xué)號(hào)char(4)ASDELETE學(xué)生信息WHERE學(xué)號(hào)=@學(xué)號(hào)存儲(chǔ)過(guò)程的執(zhí)行如下：Execdeletestu‘200030000041’138.3.2創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過(guò)程執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過(guò)程按位置傳遞參數(shù)值在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中，直接給出參數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，給出的參數(shù)的順序與創(chuàng)建執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中的參數(shù)的順序一致，即參數(shù)傳遞的順序就是參數(shù)定義的順序。例：execp_StudentGrade2‘吳賓’，‘高等數(shù)學(xué)’通過(guò)參數(shù)名傳遞參數(shù)值在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中，使用【參數(shù)名=參數(shù)值】的形式給出參數(shù)值。其優(yōu)點(diǎn)是參數(shù)可以以任意順序給出。例：例：execp_StudentGrade2@cname=‘高等數(shù)學(xué)’，@sname=‘吳賓’

148.3.2創(chuàng)建/執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過(guò)程在輸入?yún)?shù)中使用默認(rèn)值在執(zhí)行存儲(chǔ)過(guò)程時(shí)，如果沒有指定參數(shù)，則系統(tǒng)運(yùn)行就會(huì)出錯(cuò)；此時(shí)如果希望在執(zhí)行時(shí)不給出參數(shù)也能正確運(yùn)行，則在創(chuàng)建存儲(chǔ)過(guò)程時(shí)給輸入?yún)?shù)指定默認(rèn)值。例：execp_StudentGrade2‘吳賓’相當(dāng)于執(zhí)行：execp_StudentGrade2‘吳賓’，‘VB’

158.那3.被3存儲(chǔ)咽過(guò)程群的返蹦回值存儲(chǔ)其過(guò)程辰在執(zhí)杯行后慎都會(huì)張返回牽一個(gè)暮整型仁值。籍如果雞執(zhí)行至成功邊，則元返回0；否抵則返嬸回-1到-9翠9之間列的隨程機(jī)數(shù)奴，也袋可以熊使用RE強(qiáng)TU芒RN語(yǔ)句跡來(lái)指趴定一統(tǒng)個(gè)存濫儲(chǔ)過(guò)吐程的琴返回解值。168.3冶.3存儲(chǔ)捧過(guò)程違的返泄回值例：遷在no唉rt威hw猛in精d數(shù)據(jù)歐庫(kù)創(chuàng)株建一還個(gè)存糧儲(chǔ)過(guò)弄程，盯返回抄產(chǎn)品購(gòu)表中共的所質(zhì)有產(chǎn)咬品的某庫(kù)存弊量。cr鹽ea句te堡p浪ro邀c星pr像_l螞ie央rasbe渾gi場(chǎng)nde坐cl政ar得e成@f天an尿hu文iz疼hi極i暫ntse羊le趴ct紛@水fa招nh彎ui魯zh銳i=逝su徒m(墻un使it笑si糊ns最to京ck波)fr話ompr味od伏uc蛾tsre俘tu坡rn及@篩fa搶nh麻ui侍zh昌ien拆d178.存3.街3存儲(chǔ)帆過(guò)程毒的返房誠(chéng)回值接受民這個(gè)情返回欺值必給須要門用變學(xué)量來(lái)來(lái)接收de酒cl映ar猾e謙@j餅ie搶sh聞ou悅zh繩i輩in抓tex挽ec男@修ji營(yíng)es悶ho悟uz剪hi扶=p完r_波li貍erpr外in臂t奴@j由ie烤sh善ou轎zh鋸i188.矛3.稱3存儲(chǔ)巷過(guò)程怠的返掉回值上例鉗中返前回的迅值也秒可以天用ou曲tp峽ut參數(shù)分來(lái)返諷回，褲可以?shī)Z將上摧例的貨存儲(chǔ)科過(guò)程杜改寫懷為：cr娘ea擇te司p桑ro筒c晴pr抱_l拼ie闊r_舊2@f棵an犁hu昏iz丘hi迎i堡nt粱o次ut輔pu降tasbe獎(jiǎng)gi銷nse你le道ct顏@蠟fa韻nh鉆ui鄭zh朋i=似su辭m(修un合it君si疤ns評(píng)to柄ck菊)fr徑om暖p彼ro貸du杯ct默sen盜d198.賊3.獻(xiàn)3存儲(chǔ)戲過(guò)程分的返也回值接收ou編tp熟ut的返忘回值廉也必鹿須要團(tuán)用變搭量，托如：de忌cl束ar傲e檔@j紡ie時(shí)sh提ou必zh鉆i搖in疏tex簽ec伴p柜r_粉li悼er疤_2吳@繁ji攜es可ho鐵uz蜘hi局o棵ut孩pu麻tpr鑄in伶t猾@j析ie假sh蜜ou掘zh館i注意錦：1)re懇tu截rn返回警的是線整數(shù)腸，ou玻tp舒ut可以車返回成任何泊數(shù)據(jù)奇。2)如果跌讓re巖tu丹rn返回問非整蜂數(shù)值駛，在傳創(chuàng)建共存儲(chǔ)爬過(guò)程就時(shí)不厚會(huì)出駐錯(cuò)，禮但是睬運(yùn)行沙存儲(chǔ)么過(guò)程燥時(shí)將截會(huì)出難錯(cuò)。20例：必統(tǒng)計(jì)湖全體瘋學(xué)生紀(jì)人數(shù)究，并世將統(tǒng)盡計(jì)結(jié)贏果用詞輸出凝參數(shù)得返回Cr柴ea浸te呢p診ro做cp_織co丈un忙t@t帳ot篇alin啊tou仿tp拋utAsse埋le亭ct膨@刪to齒ta曾l=冰co許un蹄t(長(zhǎng)*)fr則om貢s布tu骨de惕nt執(zhí)行搖此存惠儲(chǔ)過(guò)改程:De他cl笑ar賠e蒼@re嶄sin止tEx冊(cè)ecp_不co加un戲t@re掘sou贈(zèng)tp第utPr樹in礙t嫂@re捷s其中:蔽de煌cl書ar掘e為變順量聲叔明語(yǔ)刻句語(yǔ)法吩格式悠：de甚cl怨ar償e籌@局部門變量粘名缸數(shù)據(jù)村類型@re廳s:變量排名。pr晴in筒t：輸蔑出語(yǔ)摸句，符表示睬將后強(qiáng)邊變慚量的導(dǎo)值顯示示在腦屏幕膚上語(yǔ)法念格式蛛：pr昌in界t抖‘A待SC已II文本熄字符屯串’|@局部銅變量都名|字符瞇串表庸達(dá)式218.憲4查看魚存儲(chǔ)再過(guò)程1.使用令對(duì)象止資源昂管理導(dǎo)器查瞧看存逗儲(chǔ)過(guò)呢程2.使用糊系統(tǒng)正存儲(chǔ)予過(guò)程寫查看葬存儲(chǔ)耍過(guò)程水信息在SQ清L厚Se芒rv蠟er中，食可以珍使用sp卻_h職el刺pt謙ex稍t、sp訊_d雅ep喜en鍋ds、sp揭_h視el夠p等系尼統(tǒng)存鹿儲(chǔ)過(guò)呈程來(lái)五查看改存儲(chǔ)裳過(guò)程駝的不榨同信測(cè)息。1）使賽用sp姐_h該el混pt睜ex嗓t查看煙存儲(chǔ)本過(guò)程幻玉的文偶本信淚息.其語(yǔ)抬法格片式為紡：sp史_h特el簡(jiǎn)pt唐ex緞t存儲(chǔ)房誠(chéng)過(guò)程螺名2）使姥用sp旬_d獸ep序en蹦ds查看號(hào)存儲(chǔ)逝過(guò)程咐的相秧關(guān)性.其語(yǔ)期法格全式為扇：sp輝_d輛ep適en菠ds存儲(chǔ)品過(guò)程陰名3）使醫(yī)用sp猶_h焦el載p查看笨存儲(chǔ)梯過(guò)程唉的一個(gè)般信欄息.其語(yǔ)數(shù)法格畝式為盾：sp步_h續(xù)el前p存儲(chǔ)安過(guò)程久名228.別5修改濕存儲(chǔ)六過(guò)程修改野存儲(chǔ)許過(guò)程煩語(yǔ)法徑格式嗽為：AL夢(mèng)TE貢R棕PR桶OC[D驅(qū)UR遍E]pr殘oc瘦ed屠ur儉e_閑na氣me[{座@p澤ar鴿am殘et釣erda輸ta萄_t種yp完e}[=de堪fa惡ul起t]端[O干UT被PU扭T][默,…旱n][W砌IT蠟H筍{R菌EC渾OM貓PI瞞LE勺|桐E性NC濤RY響PT摘IO需N煉|哲RE念CO怪MP兩IL盆E,援EN退CR定YP國(guó)TI聾ON驚}蔑]ASSq乓l_逗st轎at遲em粥en堡t238.仔5修改蘿存儲(chǔ)熄過(guò)程例：割修改賤存儲(chǔ)逗過(guò)程st隆ud箱en澡t_咱in餅fo,查詢計(jì)學(xué)生畫信息囑表中某個(gè)選指定頓學(xué)生氏信息席。其程旁序清懷單如尸下：AL惕TE鉗RPR奧OCst綱ud敞en撇t_踐in脆fo@學(xué)號(hào)va派rc貴ha撇r(竹14倡)AsSe繳le恒ct除*Fr鴨om學(xué)生撿信息Wh字er農(nóng)e學(xué)號(hào)=@學(xué)號(hào)GO248.棋6編譯撤存儲(chǔ)攏過(guò)程在我敢們使?fàn)C用了宿一次寇存儲(chǔ)蝴過(guò)程物后，爭(zhēng)可能謙會(huì)因霞為某哲些原睛因，扮必須評(píng)向表劃中新見增加爬數(shù)據(jù)奔列或燙者為宿表新皮添加幸索引絮，從槍而改宅變了救數(shù)據(jù)旋庫(kù)的侍邏輯反結(jié)構(gòu)秘。這閉時(shí)，季需要認(rèn)對(duì)存走儲(chǔ)過(guò)都程進(jìn)偷行重共新編未譯，SQ妹L塔Se盞rv驕er提供午三種背重新悠編譯同存儲(chǔ)影過(guò)程伏的方恒法：1、在箏建立攻存儲(chǔ)追過(guò)程拖時(shí)設(shè)萌定重閉新編臉譯語(yǔ)法尸格式快：CR葛EA輕TE守PR縱OC四ED腸UR靈Epr榴oc鋤ed鑰ur巨e_圣na價(jià)meWI臨TH幫RE殼CO稿MP封IL免EASsq擇l_榜st勸at雜em烘en國(guó)t2、在織執(zhí)行觸存儲(chǔ)發(fā)過(guò)程炕時(shí)設(shè)籃定重崖編譯語(yǔ)法妥格式俯：EX蜻EC叮UT誦Epr城oc研