數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建市公開(kāi)課一等獎(jiǎng)省賽課獲獎(jiǎng)?wù)n件

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

8.3創(chuàng)建管理帶參存放過(guò)程

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

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

]procedure_nameASsql_statement[...n]

2）語(yǔ)法注釋：

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

EXEC[UTE]procedure_name對(duì)上例存放過(guò)程pr_searchorddate執(zhí)行語(yǔ)句以下：EXECstudent_info8數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第8頁(yè)8.3創(chuàng)建帶參存放過(guò)程語(yǔ)法1）語(yǔ)法格式以下：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement9數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第9頁(yè)8.3創(chuàng)建帶參存放過(guò)程語(yǔ)法2）語(yǔ)法注釋：parameter：存放過(guò)程中輸入和輸出參數(shù)。

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

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

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

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

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

15數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第15頁(yè)8.3.3存放過(guò)程返回值存放過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值。假如執(zhí)行成功，則返回0；不然返回-1到-99之間隨機(jī)數(shù)，也能夠使用RETURN語(yǔ)句來(lái)指定一個(gè)存放過(guò)程返回值。16數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第16頁(yè)8.3.3存放過(guò)程返回值例：在northwind數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)存放過(guò)程，返回產(chǎn)品表中全部產(chǎn)品庫(kù)存量。createprocpr_lierasbegindeclare@fanhuizhiintselect@fanhuizhi=sum(unitsinstock)fromproductsreturn@fanhuizhiend17數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第17頁(yè)8.3.3存放過(guò)程返回值接收這個(gè)返回值必須要用變量來(lái)接收

declare@jieshouzhiintexec@jieshouzhi=pr_lierprint@jieshouzhi18數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第18頁(yè)8.3.3存放過(guò)程返回值上例中返回值也能夠用output參數(shù)來(lái)返回，能夠?qū)⑸侠娣胚^(guò)程改寫(xiě)為：createprocpr_lier_2@fanhuizhiintoutputasbeginselect@fanhuizhi=sum(unitsinstock)fromproductsend19數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第19頁(yè)8.3.3存放過(guò)程返回值接收output返回值也必須要用變量，如：declare@jieshouzhiintexecpr_lier_2@jieshouzhioutputprint@jieshouzhi注意：1)return返回是整數(shù)，output能夠返回任何數(shù)據(jù)。2)假如讓return返回非整數(shù)值，在創(chuàng)建存放過(guò)程時(shí)不會(huì)犯錯(cuò)，不過(guò)運(yùn)行存放過(guò)程時(shí)將會(huì)犯錯(cuò)。20數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第20頁(yè)例：統(tǒng)計(jì)全體學(xué)生人數(shù)，并將統(tǒng)計(jì)結(jié)果用輸出參數(shù)返回Createprocp_count@totalintoutputAsselect@total=count(*)fromstudent執(zhí)行此存放過(guò)程:Declare@resintExecp_count@resoutputPrint@res其中:declare為變量申明語(yǔ)句語(yǔ)法格式：declare@局部變量名數(shù)據(jù)類型@res:變量名。print：輸出語(yǔ)句，表示將后邊變量值顯示在屏幕上語(yǔ)法格式：print‘ASCII文本字符串’|@局部變量名|字符串表示式

21數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第21頁(yè)8.4查看存放過(guò)程

1.使用對(duì)象資源管理器查看存放過(guò)程2.使用系統(tǒng)存放過(guò)程查看存放過(guò)程信息在SQLServer中，能夠使用sp_helptext、sp_depends、sp_help等系統(tǒng)存放過(guò)程來(lái)查看存放過(guò)程不一樣信息。1）使用sp_helptext查看存放過(guò)程文本信息.其語(yǔ)法格式為：sp_helptext存放過(guò)程名2）使用sp_depends查看存放過(guò)程相關(guān)性.其語(yǔ)法格式為：sp_depends存放過(guò)程名3）使用sp_help查看存放過(guò)程普通信息.其語(yǔ)法格式為：

sp_help存放過(guò)程名22數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第22頁(yè)8.5修改存放過(guò)程

修改存放過(guò)程語(yǔ)法格式為：ALTERPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement23數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第23頁(yè)8.5修改存放過(guò)程

例：修改存放過(guò)程student_info,查詢學(xué)生信息表中某個(gè)指定學(xué)生信息。其程序清單以下：ALTER

PROC

student_info@學(xué)號(hào)varchar(14)AsSelect*From學(xué)生信息Where學(xué)號(hào)=@學(xué)號(hào)GO24數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第24頁(yè)8.6編譯存放過(guò)程在我們使用了一次存放過(guò)程后，可能會(huì)因?yàn)橐恍┰?，必須向表中新增加?shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)。這時(shí)，需要對(duì)存放過(guò)程進(jìn)行重新編譯，SQLServer提供三種重新編譯存放過(guò)程方法：1、在建立存放過(guò)程時(shí)設(shè)定重新編譯語(yǔ)法格式：CREATEPROCEDUREprocedure_name

WITHRECOMPILE

ASsql_statement2、在執(zhí)行存放過(guò)程時(shí)設(shè)定重編譯

語(yǔ)法格式：EXECUTE

procedure_nameWITH

RECOMPILE3、經(jīng)過(guò)使用系統(tǒng)存放過(guò)程設(shè)定重編譯

語(yǔ)法格式為：EXEC

sp_recompileOBJECT（procedure_name）25數(shù)據(jù)庫(kù)編程存儲(chǔ)過(guò)程的創(chuàng)建第25頁(yè)8.7刪除存放過(guò)程

1.使用SSMS對(duì)象資源管理器刪除存放過(guò)程2.使用DROPPROCEDURE語(yǔ)句刪