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

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

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

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

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

]procedure_nameASsql_statement[...n]

2）語法注釋：

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

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

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

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

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

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

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

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