管理存儲過程課件

1

管理存儲過程

2存儲過程的概念將一些固定的操作集中起來由SQL服務器來完成，實現(xiàn)某個特定任務，這就是存儲過程。類似于DOS下的批處理。存儲過程是SQL語句和可選控制流程語句的預編譯集合。是一種封裝重復任務操作的方法，以一個名稱存儲，作為一個單元處理。存儲過程屬于服務器方軟件，可立即訪問數(shù)據(jù)庫3存儲過程的概念存儲過程存儲在數(shù)據(jù)庫內，可由應用程序通過一個調用來執(zhí)行，而且充許用戶聲明變量。同時，存儲過程可以接收和輸出參數(shù)、返回執(zhí)行存儲過程的狀態(tài)值，也可以嵌套調用。注意：存儲過程與函數(shù)不同，因為存儲過程并不返回取代其名稱的值，也不能直接在表達式中使用。4存儲過程的分類在SQLServer中存儲過程分為兩類：系統(tǒng)提供的存儲過程和用戶自定義存儲過程。系統(tǒng)過程主要存儲在master

數(shù)據(jù)庫中，并以sp_為前綴，并且系統(tǒng)存儲過程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQLServer

提供支持。通過系統(tǒng)存儲過程，SQLServer

中的許多管理性或信息性的活動：如了解數(shù)據(jù)庫對象、數(shù)據(jù)庫信息都可以被順利有效地完成。盡管這些系統(tǒng)存儲過程被放在master

數(shù)據(jù)庫中，但是仍可以在其它數(shù)據(jù)庫中對其進行調用，在調用時不必在存儲過程名前加上數(shù)據(jù)庫名，而且當創(chuàng)建一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會在新數(shù)據(jù)庫中被自動創(chuàng)建。用戶自定義存儲過程是由用戶創(chuàng)建并能完成某一特定功能（如查詢用戶所需數(shù)據(jù)信息）的存儲過程。5存儲過程的運行過程存儲過程時存放在SQLServer中的特別快的數(shù)據(jù)庫對象，當首次運行存儲過程時，它按以下方式進行：1、該過程被劃分成部件片斷。2、檢查引用數(shù)據(jù)庫中其它對象（表、視圖等）的部件，確保引用的對象是存在的，這也被稱為分解。3、一旦分解完成，該過程的名字將存放倒sysobjects表中，而創(chuàng)建存儲過程的代碼存放在syscomments表中4、然后編譯，并且，編譯過程中將創(chuàng)建如何運行查詢的藍本。該藍本通稱稱為常規(guī)計劃或查詢樹，查詢樹存放在sysProcedures表中。5、存儲過程首次運行時，讀出查詢計劃并完全編譯成過程計劃，然后運行。這樣，節(jié)約了每次運行存成過程的語法檢查、分解和編譯查詢樹的時間。6存儲過程的優(yōu)點當利用SQLServer

創(chuàng)建一個應用程序時，T-SQL是一種主要的編程語言。若運用T-SQL來進行編程有兩種方法：其一是在本地存儲T-SQL程序并創(chuàng)建應用程序，向SQLServer

發(fā)送命令來對結果進行處理。其二是可以把部分用T-SQL

編寫的程序作為存儲過程存儲在SQLServer中，并創(chuàng)建應用程序來調用存儲過程，對數(shù)據(jù)結果進行處理。存儲過程能夠通過接收參數(shù)向調用者返回結果集，結果集的格式由調用者確定；返回狀態(tài)值給調用者，指明調用是成功或是失??；包括針對數(shù)據(jù)庫的操作語句，并且可以在一個存儲過程中調用另一存儲過程。我們通常更偏愛于使用第二種方法，即在SQLServer中使用存儲過程，而不是在客戶計算機上調用T-SQL

編寫的一段程序原因在于存儲過程具有以下優(yōu)點：7存儲過程的優(yōu)點可用存儲過程封裝事務規(guī)則。一旦封裝完成，這些規(guī)則就可用于多個應用，從而有一個一致的數(shù)據(jù)接口，因此，若需改變過程的功能，只需在一個地方對其進行修改，而不必對每個應用都進行修改。8存儲過程的優(yōu)點存儲過程允許標準組件式編程：存儲過程在被創(chuàng)建以后，可以在程序中被多次調用而不必重新編寫該存儲過程的SQL語句；而且數(shù)據(jù)庫專業(yè)人員可隨時對存儲過程進行修改，但對應用程序源代碼毫無影響，因為應用程序源代碼只包含存儲過程的調用語句，從而極大地提高了程序的可移植性。9存儲過程的優(yōu)點存儲過程能夠實現(xiàn)較快的執(zhí)行速度：如果某一操作包含大量的T-SQL

代碼或分別被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。因為存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優(yōu)化器對其進行分析優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計劃；而批處理的T-SQL語句在每次運行時都要進行編譯和優(yōu)化，因此速度相對要慢一些。10存儲過程的優(yōu)點存儲過程能夠減少網(wǎng)絡流量：對于同一個針對數(shù)據(jù)數(shù)據(jù)庫對象的操作（如查詢修改），如果這一操作所涉及到的T-SQL

語句被組織成一存儲過程，那么當在客戶計算機上調用該存儲過程時，網(wǎng)絡中傳送的只是該調用語句；否則將是多條SQL語句，從而大大增加了網(wǎng)絡流量，降低網(wǎng)絡負載。11存儲過程的優(yōu)點存儲過程可被作為一種安全機制來充分利用：系統(tǒng)管理員通過對執(zhí)行某一存儲過程的權限進行限制，從而能夠實現(xiàn)對相應的數(shù)據(jù)訪問權限的限制，避免非授權用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。12創(chuàng)建存儲過程的規(guī)則幾乎任何可以寫成批處理的T-SQL代碼都可用于創(chuàng)建存儲過程，但是在設計存儲過程時，需要遵循下列規(guī)則：名字必須符合SQLServer命名規(guī)則。引用的對象必須在創(chuàng)建存儲過程前就存在不能在單個存儲過程中創(chuàng)建后去掉或再創(chuàng)建同名的對象。存儲過程最后能有255各參數(shù)。13創(chuàng)建存儲過程的規(guī)則再自己的存儲過程中可以引用臨時表，局部臨時表再過程結束時將會消失。再存儲過程中不能有如下的SQL創(chuàng)建語句：CreateDefault、CreateProcedure、CreateRule、CreateTrigger、CreateView?？稍谶^程中嵌套過程。創(chuàng)建存儲過程的文本不能超過64K字節(jié)，以為SQL存放再syscomments表中。若再存儲過程中使用了Select*，而底層表中加入了新的列，新的列再過程運行時無法顯示。14使用企業(yè)管理器管理存儲過程創(chuàng)建存儲過程查看和修改存儲過程刪除存儲過程15使用創(chuàng)建存儲過程向導創(chuàng)建使用創(chuàng)建存儲過程向導創(chuàng)建存儲過程的步驟如下：1、打開企業(yè)管理器，在樹狀目錄中展開服務器節(jié)點。2、在工具欄中選擇命令“工具－向導”，打開“選擇向導”對話框。3、在“選擇向導”對話框中選擇節(jié)點“數(shù)據(jù)庫－創(chuàng)建存儲過程向導”，按“確定”按鈕。4、在對話框“歡迎使用創(chuàng)建存儲過程向導”中單擊“下一步”16使用創(chuàng)建存儲過程向導創(chuàng)建5、在對話框“選擇數(shù)據(jù)庫”中選擇存放存儲過程的數(shù)據(jù)庫名，按“下一步”按鈕。6、在對話框“選擇存儲過程”中選擇存儲過程將針對哪些數(shù)據(jù)表做哪些操作。單擊“下一步”按鈕。7、在對話框“正在完成創(chuàng)建存儲過程向導”中，可以看到正在創(chuàng)建的存儲過程的名稱和描述。8、若需要對某個存儲過程進行設置，可以在該對話框內選定該存儲過程，然后按“編輯”按鈕，打開“編輯存儲過程屬性”對話框，在該對話框內可以完成對該存儲過程的設置。9、逐一完成對每個存儲過程的設置以后，返回到對話框“正在完成創(chuàng)建存儲過程向導”，按“完成按鈕”。即可結束。17圖1歡迎使用創(chuàng)建存儲過程向導對話框18圖2選擇數(shù)據(jù)庫對話框19圖3選擇數(shù)據(jù)庫對象對話框20圖4完成創(chuàng)建存儲過程向導對話框21圖5編輯存儲過程屬性對話框22圖6編輯存儲過程SQL對話框23使用企業(yè)管理器創(chuàng)建存儲過程在企業(yè)管理器中創(chuàng)建一個存儲過程步驟如下：1

啟動企業(yè)管理器，登錄到要使用的服務器2

選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，在左窗格中單擊存儲過程文件夾，此時在右窗格中顯示該數(shù)據(jù)庫的所有存儲過程。3右擊存儲過程文件夾，在彈出菜單中選擇“新建存儲過程”命令4在“新建存儲過程屬性”對話框中指定存儲過程的名字。在文本框中輸入創(chuàng)建存儲過程的語句。5

單擊

“檢查語法”按鈕，檢查語法是否正確6

單擊“確定”保存存儲過程，并關閉屬性對話框。24圖7選擇新建存儲過程對話框（1）25圖8選擇新建存儲過程對話框（2）26圖9新建存儲過程對話框27圖10設置權限對話框28查看和修改存儲過程查看存儲過程：存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂檬褂闷髽I(yè)管理器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。29查看和修改存儲過程在企業(yè)管理器中查看和修改存儲過程及其定義文本，步驟如下：1打開企業(yè)管理器，在樹狀目錄中展開存儲過程所在的數(shù)據(jù)庫節(jié)點。2

單擊“存儲過程”節(jié)點，在右窗格中顯示該數(shù)據(jù)庫的所有存儲過程。選擇希望查看或修改的存儲過程，并單擊鼠標右鍵，在彈出菜單中選擇命令“屬性”3在“屬性”對話框中可以查看存儲過程的名稱、所有者、創(chuàng)建日期和存儲過程的定義文本。30查看和修改存儲過程在“屬性”對話框中還可以對該存儲過程做如下的修改：修改存儲過程定義文本。修改存儲過程的權限：單擊“權限”按鈕，對存儲過程的權限進行修改。4、單擊“確定”按鈕，并關閉屬性對話框。31查看存儲過程的相關性如果希望查看存儲過程的相關性，可以按如下步驟：1、打開企業(yè)管理器，在樹狀目錄中展開存儲過程所在的數(shù)據(jù)庫節(jié)點。2、單擊“存儲過程”節(jié)點，在右邊的內容窗口中選擇希望查看相關性的存儲過程，并單擊鼠標右鍵，在彈出菜單中選擇命令“所有任務－顯示相關性”32重命名存儲過程可以按如下步驟：1、打開企業(yè)管理器，在樹狀目錄中展開存儲過程所在的數(shù)據(jù)庫節(jié)點。2、單擊“存儲過程”節(jié)點，在右邊的內容窗口中選擇希望重命名的存儲過程，并單擊鼠標右鍵，在彈出菜單中選擇命令“重命名”3、輸入存儲過程的新名稱，并按回車鍵。4、確認新名稱。33刪除存儲過程對于不再需要的存儲過程，可以使用企業(yè)管理器將其刪除，步驟如下：1、打開企業(yè)管理器，在樹狀目錄中展開存儲過程所在的數(shù)據(jù)庫節(jié)點。2、單擊“存儲過程”節(jié)點，在右邊的內容窗口中選擇希望刪除的存儲過程，并單擊鼠標右鍵，在彈出菜單中選擇命令“刪除”3、單擊“全部除去”按鈕，刪除選定的存儲過程。34使用T-SQL管理存儲過程創(chuàng)建存儲過程執(zhí)行存儲過程查看存儲過程修改存儲過程刪除存儲過程創(chuàng)建一組存儲過程在存儲過程中使用參數(shù)WithRecompile選項35使用T-SQL創(chuàng)建存儲過程創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分：1、所有的輸入?yún)?shù)以及傳給調用者的輸出參數(shù)。2、被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調用其它存儲過程的語句。3、返回給調用者的狀態(tài)值，以指明調用是成功還是失敗。36使用T-SQL創(chuàng)建存儲過程使用CREATEPROCEDURE創(chuàng)建存儲過程語法如下：CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]其中，各參數(shù)的含義如下所示：37使用T-SQL創(chuàng)建存儲過程procedure_name：用于指定要創(chuàng)建的存儲過程的名稱。number：該參數(shù)是可選的整數(shù)，它用來對同名的存儲過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。38使用T-SQL創(chuàng)建存儲過程data_type：用于指定參數(shù)的數(shù)據(jù)類型。VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結果集。Default：用于指定參數(shù)的默認值。OUTPUT：表明該參數(shù)是一個返回參數(shù)39使用T-SQL創(chuàng)建存儲過程RECOMPILE：表明SQLServer不會保存該存儲過程的執(zhí)行計劃。ENCRYPTION：表示SQLServer加密了syscomments表，該表的text字段是包含CREATEPROCEDURE語句的存儲過程文本。FORREPLICATION：用于指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。AS：用于指定該存儲過程要執(zhí)行的操作。sql_statement：是存儲過程中要包含的任意數(shù)目和類型的Transact-SQL語句。40例1：在數(shù)據(jù)庫company中創(chuàng)建一個存儲過程，用于返回項目標的不小于5000的項目情況，并按項目標的的降序進行排列。Usecompany--如果存在同名的存儲過程，先刪除Ifexists(selectnamefromsysobjectswherename='pinfo50000'andtype='P')Dropprocedurepinfo50000GoCreateprocedurepinfo50000Asselect*fromprojectwhere項目標的>=50000Orderby項目標的DESCGoExecpinfo50000go41執(zhí)行存儲過程直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，其語法形式如下：[[EXEC[UTE]]{[@return_status=]procedure_name[;number]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,...n]

[WITHRECOMPILE]其中，各參數(shù)的意義如下：

42如果執(zhí)行存儲過程的語句是批中的第一個語句，可以省略execute關鍵字。@return_status為整型局部變量，用于保存存儲過程的返回值；procedure_name指定執(zhí)行的存儲過程的名稱；[;number]用來指定該存儲過程與其它同名存儲過程同組時的標識號。@parameter：在創(chuàng)建過程時定義的過程參數(shù)。調用者向存儲過程所傳遞的參數(shù)值由value參數(shù)或@variable變量提供，或者使用default關鍵字指定使用該參數(shù)的默認值。output參數(shù)說明指定參數(shù)為返回參數(shù)。43注意：如果按“@parameter=value|variable”的形式為存儲過程提供參數(shù)，可以不考慮創(chuàng)建存儲過程時的參數(shù)順序，但是必須以這種方式提供該存儲過程的全部參數(shù)。如果不以這種方式提供參數(shù)，則必須按照創(chuàng)建存儲過程時參數(shù)的順序提供參數(shù)。WithRecompile指定在實行存儲過程時重新編譯執(zhí)行計劃。44例：在數(shù)據(jù)庫company中創(chuàng)建一個存儲過程，用于查詢某員工所負責項目的平均項目標的。該存儲過程包含一個輸入?yún)?shù)，一個輸出參數(shù)和一個返回值。Usecompany--如果在數(shù)據(jù)庫中已經存在存儲過程GetAvgPbiaodi，先將其刪除Ifexists(selectnamefromsysobjectsWherename='GetAvgPbiaodi'andtype='P')DropprocedureGetAvgPbiaodigo45CreateprocedureGetAvgPbiaodi@namevarchar(10),@avgpbiaodiintoutputAsDeclare@ErrorSaveintSet@ErrorSave=0Select@avgpbiaodi=AVG(項目標的)FromprojectaspINNERJOINpmanageraspmonp.負責人ID=pm.負責人IDWherepm.姓名=@nameIf(@@Error<>0)Set@ErrorSave=@@ErrorReturn@ErrorSave

go46--聲明變量，用于保存返回值和輸出參數(shù)Declare@returnvalueint,@avgintEXEC@returnvalue=GetAvgPbiaodi'李中新',@avgOUTPUTPrint'執(zhí)行的結果：'--函數(shù)用轉換數(shù)據(jù)類型Print'返回值＝'+cast(@returnvalueaschar(2))Print'李中新復雜項目的平均標的為：'+cast(@avgaschar(10))go47例：本例將例1中的存儲過程返回的結果集存入一個新建的表importProject中。CreatetableImportmantProject

(

項目編號int,項目名稱

char(10),項目標的

int,客戶編號int,負責人編號

[int]NULL,負責人IDint)InsertintoimportmantProjectExecPinfo50000Go48查看存儲過程查看存儲過程的定義查看存儲過程的相關性查看存儲過程的所有者、類型、創(chuàng)建日期和參數(shù)49查看存儲過程的定義：用系統(tǒng)存儲過程sp_helptext可以用于顯示存儲過程的源代碼

，語法為：sp_helptext[[@objname=]name]參數(shù)name為要查看的存儲過程的名稱。例如：execsp_helptextGetAvgPbiaodi注意：如果存儲過程用帶有ENCRYPTION選項的方式創(chuàng)建，則無法用sp_helptext（或企業(yè)管理器）查看用于創(chuàng)建存儲過程的文本。查看存儲過程的定義50查看存儲過程的相關性系統(tǒng)存儲過程sp_depends是用于顯示和存儲過程相關的數(shù)據(jù)庫對象，其語法為：sp_depends[@objname=]’object’其中，參數(shù)object為要查看依賴關系的存儲過程的名稱。例如：execsp_dependsGetAvgPbiaodi51查看存儲過程的所有者等信息系統(tǒng)存儲過程sp_help是用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型sp_help[[@objname=]name]其中，參數(shù)name為要查看的存儲過程的名稱。例：execsp_helpGetAvgPbiaodi52修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下：sp_rename原存儲過程名稱，新存儲過程名稱例：execsp_renameGetAvgPbiaodi,‘項目平均標的過程’重命名存儲過程53存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTERPROCEDURE語句可以更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)建的過程，但不會更改權限，也不影響相關的存儲過程或觸發(fā)器。修改存儲過程54修改存儲過程其語法形式如下：ALTERPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]其中，格式與創(chuàng)建存儲過程的語法完全相同，只是Create變?yōu)锳lter55修改存儲過程注意：只有存儲過程的創(chuàng)建者、db_owner和db_ddladmin的成員才可以修改存儲過程。在CreateProcedure語句中使用的選項也必須在AlterProcedure語句中使用。56例：修改例1中創(chuàng)建的存儲過程Pinfo50000,使這個存儲過程值輸出字段“項目名稱”和“項目標的”。AlterProcedurePinfo50000Asselect項目名稱，項目標的FromProjectWhere項目標的>=50000Orderby項目標的DESCGoExecPinfo50000go57刪除存儲過程刪除存儲過程可以使用DROP命令，DROP命令可以將一個或者多個存儲過程或者存儲過程組從當前數(shù)據(jù)庫中刪除，其語法形式如下：dropprocedure{procedure}[,…n]其中，procedure指定要刪除的存儲過程或存儲過程組的名稱。例如：dropProcedurePinfo50000注意：可以選擇是否指定過程所有者名稱，但不能指定服務器名稱和數(shù)據(jù)庫名稱。不能除去組內的個別過程，必須除去整個過程組；如果該過程組在其它的應用程序或存儲過程中被調用，系統(tǒng)會顯示出錯信息。58創(chuàng)建一組過程在“;number”選項中，通過指定分號和數(shù)字，可創(chuàng)建一組存儲過程。一組存儲過程通常是為同一應用程序使用的，維護工作變得向對簡單，因為一個特定應用程序使用的所有過程引用的是同一個組。59例：CreateProcgroup_sp;1Asselect*fromauthorsGoCreateProcgroup_sp;2Asselectau_lnamefromauthorsGoCreateProcgroup_sp;3AsselectdistinctcityfromauthorsGo這個批處理語句將創(chuàng)建一個稱為group_sp的單一過程，它包括3個不同過程作為它的一部分。要引用單個過程，只要執(zhí)行它們并帶有“;number”作為名字的一部分。如：execgroup_sp;360在刪除過程組時，只要刪除過程的名字，所用組的部件也將被刪除。比如：Dropproceduredbo.group_sp注意：無法刪除組中的某一個過程。61在存儲過程中使用參數(shù)例：下面的存儲過程有5個傳入?yún)?shù)，求其平均值，而后將平均值賦給output。CreateProcedurescores@score1smallint,@score2smallint,@score3smallint,@score4smallint,@score5smallint,@myAvgsmallintOutputAsselect@myAvg=(@score1+@score2+@score3+@score4+@score5)/562要擴展myAvg的值，首先要定義一個變量，然后才能運行該程序。Declare@AvgScoresmallintExecscores10,9,8,8,10,@AvgScoreOutputSelect'TheAverageScoreis:',@AvgScorego63在存儲過程中使用參數(shù)在將值傳遞給存儲過程時，可以按位置順序傳入(稱為按位置傳入)，也可按引用轉參數(shù)，即用參數(shù)名稱＝值的方式傳入?yún)?shù)。當按引用傳入?yún)?shù)時，可按任何次序傳入?yún)?shù)。例：declare@Avgsco