數(shù)據(jù)庫編程存儲過程的創(chuàng)建市公開課一等獎省賽課獲獎?wù)n件

第八章數(shù)據(jù)庫編程1數(shù)據(jù)庫編程存儲過程的創(chuàng)建第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數(shù)據(jù)庫編程存儲過程的創(chuàng)建第2頁第8章存放過程8.1存放過程概述8.2創(chuàng)建管理無參存放過程

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

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

系統(tǒng)存放過程擴展存放過程用戶自定義存放過程系統(tǒng)存放過程存放以SP_為前綴,是由SQLServer自己創(chuàng)建、管理和使用一個特殊存放過程，不能對其進行修改或刪除。如Sp_helpdb、Sp_renamedb等。由用戶自行創(chuàng)建存放過程，能夠輸入?yún)?shù)、向客戶端返回表格或結(jié)果、消息等，也能夠返回輸出函數(shù)。4數(shù)據(jù)庫編程存儲過程的創(chuàng)建第4頁8.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語句。5數(shù)據(jù)庫編程存儲過程的創(chuàng)建第5頁8.2無參存放過程創(chuàng)建例1：返回30000041號學(xué)生成績，不使用參數(shù)。代碼以下：CREATEPROCEDUREstudent_infoASSELECT*FROM學(xué)生信息WHERE學(xué)號='30000041'6數(shù)據(jù)庫編程存儲過程的創(chuàng)建第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)值。7數(shù)據(jù)庫編程存儲過程的創(chuàng)建第7頁8.2無參存放過程執(zhí)行對存放在服務(wù)器上存放過程，能夠使用EXECUTE命令或其名稱執(zhí)行它，其語法格式以下：

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

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

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

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

wheresname=@snameandcname=@cname12數(shù)據(jù)庫編程存儲過程的創(chuàng)建第12頁例2：CREATEPROCdeletestu@學(xué)號char(4)ASDELETE學(xué)生信息WHERE學(xué)號=@學(xué)號存放過程執(zhí)行以下：Execdeletestu‘30000041’13數(shù)據(jù)庫編程存儲過程的創(chuàng)建第13頁8.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é)’經(jīng)過參數(shù)名傳遞參數(shù)值在執(zhí)行存放過程語句中，使用【參數(shù)名=參數(shù)值】形式給出參數(shù)值。其優(yōu)點是參數(shù)能夠以任意次序給出。例：例：execp_StudentGrade2@cname=‘高等數(shù)學(xué)’，@sname=‘吳賓’

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

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

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

21數(shù)據(jù)庫編程存儲過程的創(chuàng)建第21頁8.4查看存放過程

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

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

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

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

PROC

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

WITHRECOMPILE

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

語法格式：EXECUTE

procedure_nameWITH

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

語法格式為：EXEC

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

1.使用SSMS對象資源管理器刪除存放過程2.使用DROPPROCEDURE語句刪