1、第12章 存儲過程與自定義函數(shù),存儲過程是一組預先編譯好的T-SQL代碼，即在執(zhí)行時就不用再次進行編譯，這樣會提高程序的運行效率。它類似于其他高級語言里的過程，是一種能夠完成特定功能的函數(shù)。存儲過程可以作為一個獨立的數(shù)據(jù)庫對象，也可以作為一個單元被用戶的應用程序調(diào)用。本章內(nèi)容主要包括： 存儲過程的概念； 創(chuàng)建和執(zhí)行存儲過程； 存儲過程的操作； 系統(tǒng)存儲過程； 自定義函數(shù)。,12.1 存儲過程,存儲過程是T-SQL語句和流程控制語句的集合，在大型數(shù)據(jù)庫系統(tǒng)的開發(fā)和應用中，具有很重要的作用。,12.1.1 存儲過程的概念,運行在客戶端的應用程序?qū)Ψ掌鞫说臄?shù)據(jù)庫進行訪問的一般流程如下： （1）T-

2、SQL語句發(fā)送到服務器。 （2）數(shù)據(jù)庫服務器編譯T-SQL語句。 （3）產(chǎn)生查詢執(zhí)行計劃。 （4）執(zhí)行查詢計劃。 （5）將執(zhí)行結(jié)果返回客戶端。 在這個過程中，每一條SQL語句是單獨的、一句一句的從客戶端向服務器發(fā)送請求，然后數(shù)據(jù)庫服務器再將結(jié)果一個一個地返回給應用程序。有一些應用程序，涉及的服務器端數(shù)據(jù)庫處理活動很多，而與用戶的交互活動較少，在這種情況下，將有關(guān)數(shù)據(jù)庫的處理活動以儲存過程的形式放在數(shù)據(jù)庫服務器上完成，則可以大大減輕網(wǎng)上傳輸流量，提高應用程序性能。,12.1.2 存儲過程的優(yōu)缺點,存儲過程能夠封裝一些常用的復雜操作以提高復用性，并且保證數(shù)據(jù)庫的安全性。然而在享有存儲過程帶來便利的

3、同時，仍然需要考慮系統(tǒng)的應用需求以確定是否選用存儲過程。 1存儲過程的優(yōu)點 2存儲過程的缺點,12.1.3 存儲過程的類型,在SQL Server 2005中，存儲過程可以分為兩類，分別是T-SQL存儲過程和CLR存儲過程。T-SQL存儲過程是指用T-SQL語句編寫的存儲過程，是一組完成特定功能的T-SQL語句集合；CLR存儲過程是指引用.NET FRAMEWORK公共語言時方法的存儲過程。它在.NET FRAMEWORK程序集中是以類的公共靜態(tài)方法實現(xiàn)的。 按照存儲過程的應用目和來源的不同，可以將存儲過程分為系統(tǒng)存儲過程、用戶存儲過程和擴展存儲過程。 1系統(tǒng)存儲過程 2用戶存儲過程 3擴展存

4、儲過程,12.1.4 存儲過程與視圖的比較,存儲過程與視圖的區(qū)別如下： 視圖是從一個或者多個表或視圖中導出來的表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上的，但其并不存在物理上的結(jié)構(gòu)；而存儲過程類似于程序，一個用SQL語句寫的程序，在其內(nèi)可以定義變量。 視圖在每次查詢時，都需要重新生成執(zhí)行計劃；而存儲過程第一次編譯完成后，以后就不需要再次編譯，執(zhí)行速度比視圖快。 可以在單個存儲過程中執(zhí)行一系列T-SQL語句，存儲過程可以包含程序流，邏輯以及對數(shù)據(jù)庫查詢的T-SQL語句，而視圖中只能是SELECT語句。 視圖的查詢條件是固定的，而存儲過程可以通過給里面的參數(shù)賦值達到查詢條件靈活變化的效果。,12.2

5、 創(chuàng)建和執(zhí)行存儲過程,對于存儲過程的概念和優(yōu)缺點有了充分的認識之后，下面將詳細講解存儲過程的核心內(nèi)容，即如何創(chuàng)建和執(zhí)行存儲過程。,12.2.1 創(chuàng)建存儲過程,存儲過程是一組實現(xiàn)特定功能的T-SQL語句集，使用存儲過程不僅能夠極大的提升執(zhí)行效率，而且可以作為一種安全機制，實現(xiàn)用戶不同級別權(quán)限的控制。 1創(chuàng)建存儲過程的語法 2存儲過程的結(jié)構(gòu) 3創(chuàng)建存儲過程的規(guī)則 4使用T-SQL創(chuàng)建存儲過程 5使用SSMS創(chuàng)建存儲過程,12.2.1 創(chuàng)建存儲過程,12.2.1 創(chuàng)建存儲過程,12.2.2 執(zhí)行存儲過程,為了便于演示，此處存儲過程的執(zhí)行都是放在SSMS查詢編輯器窗口中進行的。系統(tǒng)開發(fā)過程中，不同的編

6、程語言有不同的執(zhí)行存儲過程語法。 1執(zhí)行存儲過程的語法 2執(zhí)行存儲過程,12.2.2 執(zhí)行存儲過程,12.2.3 帶輸入?yún)?shù)的存儲過程,系統(tǒng)開發(fā)過程中，有時需要根據(jù)用戶的輸入信息產(chǎn)生不同的查詢結(jié)果，即把用戶的輸入信息作為參數(shù)傳遞給存儲過程，這就要用到帶輸入?yún)?shù)的存儲過程。 1創(chuàng)建帶輸入?yún)?shù)的存儲過程 2執(zhí)行帶輸入?yún)?shù)的存儲過程,12.2.3 帶輸入?yún)?shù)的存儲過程,12.2.4 帶輸出參數(shù)的存儲過程,系統(tǒng)開發(fā)過程中，有時需要對查詢結(jié)果進行一定形式的操作，并接結(jié)果返回給用戶，需要用到帶輸出參數(shù)的存儲過程。 1創(chuàng)建帶輸出參數(shù)的存儲過程 2執(zhí)行帶輸入?yún)?shù)的存儲過程,12.2.4 帶輸出參數(shù)的存儲過程,

7、12.2.4 帶輸出參數(shù)的存儲過程,12.2.5 綜合實例,本小節(jié)將展示一個綜合程度較高的存儲過程實例，該實例以帶輸入?yún)?shù)的存儲過程為主要結(jié)構(gòu)，結(jié)合常用的T-SQL語法結(jié)構(gòu)，其功能是實現(xiàn)大批量數(shù)據(jù)的分頁輸出。 1存儲過程代碼 2關(guān)鍵環(huán)節(jié)解析,12.2.5 綜合實例,12.3 存儲過程的有關(guān)操作,除了可以創(chuàng)建和執(zhí)行存儲過程外，還可以對存儲過程執(zhí)行修改、查看、重命名和刪除等操作，實現(xiàn)較豐富的功能。,12.3.1 修改存儲過程,已有的存儲過程不完善，或者產(chǎn)生新的特定業(yè)務需求時，需要對原有的存儲過程進行修改。修改之后的存儲過程與原存儲過程名稱相同。 1修改存儲過程基本語法 2使用視圖設(shè)計器對存儲過程進

8、行修改,12.3.1 修改存儲過程,12.3.1 修改存儲過程,12.3.1 修改存儲過程,12.3.2 查看存儲過程,使用存儲過程時，難以根據(jù)存儲過程的名稱直接判斷其功能，需要對存儲過程進行查看操作才能獲得有關(guān)該存儲過程的詳細信息。查看存儲過程的方法有兩種，一種是使用SSMS查看，另一種是使用T-SQL查看。 1使用T-SQL語句查看存儲過程 2使用SSMS查看存儲過程的定義,12.3.2 查看存儲過程,12.3.2 查看存儲過程,12.3.3 重命名存儲過程,創(chuàng)建存儲過程時定義的名稱有時不能準確的反映該存儲過程的功能，或者很多存儲過程需要按照一定的規(guī)則統(tǒng)一命名，這就需要對原有存儲過程進行重

9、命名。,12.3.3 重命名存儲過程,12.3.4 刪除存儲過程,不需要的存儲過程可以刪除掉，然而刪除的時候要詳細分析是否存在關(guān)聯(lián)到該存儲過程的其他存儲過程。如果存在，則調(diào)用該存儲過程時會產(chǎn)生錯誤信息。刪除存儲過程同樣有兩種方法，一種是使用SSMS，另一種是使用T-SQL語句進行刪除。 1使用T-SQL語句刪除存儲過程 2使用SSMS刪除存儲過程,12.3.4 刪除存儲過程,12.4 系統(tǒng)存儲過程與擴展存儲過程,前文所涉及的存儲過程均為自定義存儲過程，即為實現(xiàn)某種特定的業(yè)務需求，由用戶編寫的T-SQL語句集。除此之外，SQL Server 2005還提供了系統(tǒng)存儲過程和擴展存儲過程。,12.4

10、.1 系統(tǒng)存儲過程,系統(tǒng)存儲過程是SQL Serer預先定義好的，其功能主要定位于為用戶提供對象屬性、系統(tǒng)性能等信息的查看，避免用戶直接訪問系統(tǒng)表。 1系統(tǒng)存儲過程分類 2系統(tǒng)存儲過程的應用,12.4.1 系統(tǒng)存儲過程,12.4.2 擴展存儲過程,擴展存儲過程提供從SQL Server到外部程序的接口，以便進行各種維護活動，實質(zhì)上是SQL Server可以動態(tài)裝載并執(zhí)行的動態(tài)鏈接庫。下面列出一些常用的擴展存儲過程。 xp_availablemedia： xp_dirtree： xp_enumdsn： xp_enum groups： xp_getfiledetails： xp_makecab： xp_ntsec_enumdomains： xp_servicecontrol： xp_subdirs：,12.5 用戶自定義函數(shù),SQL Server 2005不僅提供了豐富的系統(tǒng)內(nèi)置函數(shù)，同時還允許用戶自定義函數(shù)，滿足個性化的業(yè)務需求。用戶自定義函數(shù)是SQL Server的數(shù)據(jù)庫對象，它不能用于執(zhí)行一系列改變數(shù)據(jù)庫狀態(tài)的操作，但它可以像系統(tǒng)函數(shù)一樣在查詢或存儲過程等程序段中使用，也可以像存儲過程一樣通過EXECUTE 命令來執(zhí)行。 1用戶自定義函數(shù)概述 2創(chuàng)建用戶