存儲(chǔ)過程(精)ppt課件

.,第11章存儲(chǔ)過程,.,本章教學(xué)概要,主要內(nèi)容11.1存儲(chǔ)過程的定義11.2存儲(chǔ)過程的優(yōu)點(diǎn)11.3存儲(chǔ)過程的創(chuàng)建11.4重新編譯存儲(chǔ)過程11.5自動(dòng)執(zhí)行存儲(chǔ)過程11.6查看、修改和刪除存儲(chǔ)過程11.7擴(kuò)展存儲(chǔ)過程教學(xué)目標(biāo)理解存儲(chǔ)過程的概念和作用掌握創(chuàng)建存儲(chǔ)過程的方法掌握?qǐng)?zhí)行存儲(chǔ)過程的方法掌握查看、修改和刪除索引的方法,.,11.1存儲(chǔ)過程的定義,SQLServer的存儲(chǔ)過程類似于編程語言中的過程。在使用Transact-SQL語言編程的過程中，我們可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)過程，將其保存在數(shù)據(jù)庫中，并由SQLServer服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲(chǔ)過程。在SQLServer中存儲(chǔ)過程分為三類：即系統(tǒng)提供的存儲(chǔ)過程（sp_）、擴(kuò)展存儲(chǔ)過程（xp_）和用戶自定義的存儲(chǔ)過程。,.,11.2存儲(chǔ)過程的優(yōu)點(diǎn),存儲(chǔ)過程是一種把重復(fù)的任務(wù)操作封裝起來的一種方法，支持用戶提供參數(shù)，可以返回、修改值，允許多個(gè)用戶使用相同的代碼，完成相同的數(shù)據(jù)操作。它提供了一種集中且一致的實(shí)現(xiàn)數(shù)據(jù)完整性邏輯的方法。存儲(chǔ)過程用于實(shí)現(xiàn)頻繁使用的查詢、業(yè)務(wù)規(guī)則、被其它過程使用的公共例行程序。存儲(chǔ)過程具有以下優(yōu)點(diǎn)：實(shí)現(xiàn)了模塊化編程。存儲(chǔ)過程具有對(duì)數(shù)據(jù)庫立即訪問的功能。使用存儲(chǔ)過程可以加快程序的運(yùn)行速度。使用存儲(chǔ)過程可以減少網(wǎng)絡(luò)流量。使用存儲(chǔ)過程可以提高數(shù)據(jù)庫的安全性。,.,11.3存儲(chǔ)過程的創(chuàng)建,在SQLServer中，可以使用兩種方法創(chuàng)建存儲(chǔ)過程：使用T-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程。利用SQLServer企業(yè)管理器創(chuàng)建存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲(chǔ)過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。,.,1.使用T-SQL語句創(chuàng)建存儲(chǔ)過程,創(chuàng)建一個(gè)存儲(chǔ)過程的語法如下：CREATEPROCEDUREOWNER.procedure_name(parameterdata_typeVARYING=defaultOUTPUT),.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement.n,.,CREATEPROCEDURE創(chuàng)建存儲(chǔ)過程的參數(shù)的意義如下：,procedure_name：用于指定要?jiǎng)?chuàng)建的存儲(chǔ)過程的名稱。parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個(gè)或多個(gè)參數(shù)。data_type：用于指定參數(shù)的數(shù)據(jù)類型。VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。Default：用于指定參數(shù)的默認(rèn)值。OUTPUT：表明該參數(shù)是一個(gè)返回參數(shù)。RECOMPILE：表明SQLServer不會(huì)保存該存儲(chǔ)過程的執(zhí)行計(jì)劃。ENCRYPTION：表示SQLServer加密了syscomments表，該表的text字段是包含CREATEPROCEDURE語句的存儲(chǔ)過程文本。AS：用于指定該存儲(chǔ)過程要執(zhí)行的操作。sql_statement：是存儲(chǔ)過程中要包含的任意數(shù)目和類型的Transact-SQL語句。,.,創(chuàng)建存儲(chǔ)過程前，應(yīng)該考慮下列幾個(gè)事項(xiàng)：不能將CREATEPROCEDURE語句與其它SQL語句組合到單個(gè)批處理中。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。除了臨時(shí)存儲(chǔ)過程。臨時(shí)存儲(chǔ)過程總是創(chuàng)建在tempdb數(shù)據(jù)庫中一個(gè)存儲(chǔ)過程的最大尺寸為128M?！纠?1-1】創(chuàng)建一存儲(chǔ)過程，要求該存儲(chǔ)過程返回學(xué)生姓名、所學(xué)課程和任課教師。CREATEPROCEDUREspStuCouTea_nameASSELECTa.student_name,b.course_name,c.teacher_nameFROMstudentaINNERJOINstudent_coursedONa.student_id=d.student_idINNERJOINcoursebONd.course_id=b.course_idINNERJOINteacher_course_classeON(e.class_id=a.class_id)and(e.course_id=d.course_id)INNERJOINteachercONc.teacher_id=e.teacher_id執(zhí)行以上腳本，便可創(chuàng)建存儲(chǔ)過程spStuCouTea_name。如要執(zhí)行該存儲(chǔ)過程，可在查詢分析器中執(zhí)行如下語句：EXECspStuCouTea_name,.,2.使用企業(yè)管理器創(chuàng)建存儲(chǔ)過程,（）在SQLServer企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，在彈出的快捷菜單中選擇“新建”選項(xiàng)，再選擇下一級(jí)菜單中的“存儲(chǔ)過程”選項(xiàng)，或者用右鍵單擊存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過程”選項(xiàng)，均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程對(duì)話框。（）在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的T_SQL語句，單擊“檢查語法”，則可以檢查語法是否正確；單擊“確定”按鈕，即可保存該存儲(chǔ)過程。如果要設(shè)置權(quán)限，單擊“權(quán)限”按鈕。,.,3.創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程,輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過程傳遞的參數(shù)。它們?cè)趧?chuàng)建存儲(chǔ)過程語句中被定義，其參數(shù)值在執(zhí)行該存儲(chǔ)過程時(shí)由調(diào)用該存儲(chǔ)過程的語句給出。具體語法如下:parameter_namedataype=default【例11-2】建立一個(gè)存儲(chǔ)過程，選擇某一個(gè)指定學(xué)生的學(xué)生姓名、所學(xué)課程及任課教師姓名。CREATEPROCEDUREspStuCouTea_withParamstudentnamevarchar(8)/*studentnamevarchar(8)=%*/ASSELECTa.student_name,b.course_name,c.teacher_nameFROMstudentaINNERJOINstudent_coursedONa.student_id=d.student_idINNERJOINcoursebONd.course_id=b.course_idINNERJOINteacher_course_classeON(e.class_id=a.class_id)and(e.course_id=d.course_id)INNERJOINteachercONc.teacher_id=e.teacher_idWHEREstudent_name=studentname,.,【例11-3】為JWGL數(shù)據(jù)庫建立一個(gè)存儲(chǔ)過程，通過執(zhí)行存儲(chǔ)過程將學(xué)生信息添加到student表。CREATEPROCEDUREspAddStudentidchar(8)=NULL,namenvarchar(8)=NULL,sexchar(2)=NULL,birthdaysmalldatetime=NULL,classchar(6)=NULL,InDatesmalldatetime=NULL,homenvarchar(40)=NULLASIFidISNULLORnameISNULLORsexISNULLORbirthdayISNULLORclassISNULLORInDateISNULLBEGINPRINT請(qǐng)重新輸入該學(xué)生信息！PRINT你必須提供學(xué)生的學(xué)號(hào)、姓名、性別、出生日期、班級(jí)號(hào)及入學(xué)日期。,.,PRINT（家庭地址可以為空）RETURNENDDECLAREbitSexbitIFsex=男SETbitSex=1ELSESETbitSex=0BEGINTRANSACTIONINSERTstudent(student_id,student_name,sex,birth,class_id,entrance_date,home_addr)VALUES(id,name,bitSex,birthday,class,InDate,home)IFerror0BEGINROLLBACKTRANRETURNENDCOMMITTRANSACTIONPRINT學(xué)生+name+的信息成功添加到表student中。,.,4.創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程,通過在創(chuàng)建存儲(chǔ)過程的語句中定義輸出參數(shù)，可以創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程。執(zhí)行該存儲(chǔ)過程，可以返回一個(gè)或多個(gè)值。具體語法如下：parameter_namedataype=defaultOUTPUT【例11-4】創(chuàng)建一個(gè)實(shí)現(xiàn)加法計(jì)算并將運(yùn)算結(jié)果作為輸出參數(shù)的存儲(chǔ)過程。CREATEPROCEDUREspAddValue1INT,Value2INT,ResultValueINTOUTPUTASSELECTResultValue=Value1+Value2創(chuàng)建了上面的存儲(chǔ)過程之后，下面我們來看看怎樣使用它。為了使用spAdd，接受其輸出參數(shù)的返回值，調(diào)用它的程序中也必須定義一個(gè)變量，并使用OUTPUT關(guān)鍵字指定它為調(diào)用輸出參數(shù)。,.,【例11-5】執(zhí)行spAdd存儲(chǔ)過程，輸入?yún)?shù)由value1和value2提供，輸出參數(shù)valueTotal。DECLAREvalue1INTDECLAREvalue2INTDECLAREvalueTotalINTSETvalue1=125SETvalue2=3SETvalueTotal=34EXECspAddvalue1,value2,valueTotalOUTPUTPRINTCONVERT(CHAR(5),value1)+與+CONVERT(CHAR(5),value2)+的和等于:+CONVERT(CHAR(5),valueTotal),.,11.4重新編譯存儲(chǔ)過程,重新編譯存儲(chǔ)過程有以下三種方法：在創(chuàng)建存儲(chǔ)過程時(shí)，使用CREATEPROCEDURE中的RECOMPILE重編譯選項(xiàng)。具體語法為：CREATEPROCEDURE.WITHRECOMPILE【例11-6】為JWGL數(shù)據(jù)庫創(chuàng)建一個(gè)帶重編譯選項(xiàng)的存儲(chǔ)過程，用于查詢某學(xué)生的成績信息。CREATEPROCspStudentCoursestudentidchar(8)WITHRECOMPILEASSELECT*FROMstudent_coursestudent_id=studentid執(zhí)行存儲(chǔ)過程時(shí)重編譯。在EXECUTE語句中使用WITHRECOMPILE選項(xiàng)，讓SQLServer在執(zhí)行一個(gè)存儲(chǔ)過程時(shí)，重新編譯該存儲(chǔ)過程。其語法如下：EXECUTEprocedure_nameparameterWITHRECOMPILE,.,【例11-7】帶重新編譯選項(xiàng)，執(zhí)行存儲(chǔ)過程spAdd。DECLAREvalueTotalINTEXECspAdd4,9,valueTotalOUTPUTWITHRECOMPILEPRINTCONVERT(CHAR(5),valueTotal)執(zhí)行存儲(chǔ)過程時(shí)重編譯，可以在執(zhí)行存儲(chǔ)過程期間創(chuàng)建新的查詢計(jì)劃，新的執(zhí)行計(jì)劃存放在高速緩存中。使用sp_recompile系統(tǒng)存儲(chǔ)過程，指定表的存儲(chǔ)過程進(jìn)行重編譯。語法如下：sp_recompiletable_name【例11-8】EXECsp_recompilestudent將強(qiáng)制student表的所有存儲(chǔ)過程和觸發(fā)器在下一次運(yùn)行時(shí)被重新編譯。,.,11.5自動(dòng)執(zhí)行存儲(chǔ)過程,存儲(chǔ)過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在SQLServer中可以使用EXECUTE命令來直接執(zhí)行存儲(chǔ)過程。使用系統(tǒng)存儲(chǔ)過程sp_procoption可以將現(xiàn)有存儲(chǔ)過程設(shè)置為自動(dòng)執(zhí)行過程或停止自動(dòng)執(zhí)行，也可以查看SQLServer啟動(dòng)時(shí)執(zhí)行的所有過程的列表。其語法是：sp_procoptionProcName=procedure,OptionName=option,OptionValue=value其中：ProcName=procedure：要為其設(shè)置或查看選項(xiàng)的過程名。無默認(rèn)值。OptionName=option：要設(shè)置的選項(xiàng)的名稱。option的唯一值是startup，該值設(shè)置存儲(chǔ)過程的自動(dòng)執(zhí)行狀態(tài)。OptionValue=value：表示選項(xiàng)是設(shè)置為開（true或on）還是關(guān)（false或off）。無默認(rèn)值。,.,11.6查看、修改和刪除存儲(chǔ)過程,1.查看存儲(chǔ)過程存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用使用企業(yè)管理器或系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程。用T-SQL語句查看存儲(chǔ)過程查看存儲(chǔ)過程的定義系統(tǒng)存儲(chǔ)過程sp_helptext可查看未加密的存儲(chǔ)過程的定義腳本，也可用于查看規(guī)則、默認(rèn)值、用戶定義函數(shù)、觸發(fā)器或視圖的定義腳本。使用其語法如下：sp_helptextobjname=name查看有關(guān)存儲(chǔ)過程的信息使用系統(tǒng)存儲(chǔ)過程sp_help可查看有關(guān)存儲(chǔ)過程的信息。具體語法形式如下：sp_helpproc_name使用企業(yè)管理器查看存儲(chǔ)過程,.,2.修改存儲(chǔ)過程,使用T-SQL語句修改存儲(chǔ)過程Transact-SQL中提供了ALTERPROCEDURE語句來更改已經(jīng)創(chuàng)建的存儲(chǔ)過程，它不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。它的語法如下所示：ALTERPROCEDUREprocedure_nameparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n使用企業(yè)管理器修改存儲(chǔ)過程,.,3.刪除存儲(chǔ)過程使用SQL語句刪除存儲(chǔ)過程從當(dāng)前數(shù)據(jù)庫中刪除一個(gè)或多個(gè)存儲(chǔ)過程的T-SQL語句是DROPPROCEDURE。具體語法如下：DROPPROCEDUREprocedure,n【例11-9】要將spAdd存儲(chǔ)過程刪除，則可以執(zhí)行DROPPROCspAdd語句。使用企業(yè)管理器刪除存儲(chǔ)過程,.,補(bǔ)充內(nèi)容：重命名存儲(chǔ)過程,修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存儲(chǔ)過程sp_rename，其語法形式如下：sp_rename原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱另外，通過企業(yè)管理器也可以修改存儲(chǔ)過程的名稱。,.,11.7擴(kuò)展存儲(chǔ)過程,1.什么是擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程使你得以使用編程語言創(chuàng)建自己的外部例程。對(duì)用戶來說，擴(kuò)展存儲(chǔ)過程與普通存儲(chǔ)過程一樣，執(zhí)行方法也相同?？蓪?shù)傳遞給擴(kuò)展存儲(chǔ)過程，擴(kuò)展存儲(chǔ)過程可返回結(jié)果，也可返回狀態(tài)。擴(kuò)展存儲(chǔ)過程可用于擴(kuò)展MSSQLServer2005的功能。擴(kuò)展存儲(chǔ)過程與通常的存儲(chǔ)過程其實(shí)有很大差別。存儲(chǔ)過程是一系列預(yù)編譯的Transact-SQL語句，而擴(kuò)展