第12章存儲過程與自定義函數(shù)省名師優(yōu)質(zhì)課賽課獲獎?wù)n件市賽課一等獎?wù)n件

第12章存放過程與自定義函數(shù)存放過程是一組預(yù)先編譯好T-SQL代碼，即在執(zhí)行時就不用再次進行編譯，這么會提升程序運行效率。它類似于其它高級語言里過程，是一個能夠完成特定功效函數(shù)。存放過程能夠作為一個獨立數(shù)據(jù)庫對象，也能夠作為一個單元被用戶應(yīng)用程序調(diào)用。本章內(nèi)容主要包含：存放過程概念；創(chuàng)建和執(zhí)行存放過程；存放過程操作；系統(tǒng)存放過程；自定義函數(shù)。第1頁12.1存放過程存放過程是T-SQL語句和流程控制語句集合，在大型數(shù)據(jù)庫系統(tǒng)開發(fā)和應(yīng)用中，含有很主要作用。第2頁12.1.1存放過程概念運行在客戶端應(yīng)用程序?qū)Ψ?wù)器端數(shù)據(jù)庫進行訪問普通流程以下：（1）T-SQL語句發(fā)送到服務(wù)器。（2）數(shù)據(jù)庫服務(wù)器編譯T-SQL語句。（3）產(chǎn)生查詢執(zhí)行計劃。（4）執(zhí)行查詢計劃。（5）將執(zhí)行結(jié)果返回客戶端。在這個過程中，每一條SQL語句是單獨、一句一句從客戶端向服務(wù)器發(fā)送請求，然后數(shù)據(jù)庫服務(wù)器再將結(jié)果一個一個地返回給應(yīng)用程序。有一些應(yīng)用程序，包括服務(wù)器端數(shù)據(jù)庫處理活動很多，而與用戶交互活動較少，在這種情況下，將相關(guān)數(shù)據(jù)庫處理活動以儲存過程形式放在數(shù)據(jù)庫服務(wù)器上完成，則能夠大大減輕網(wǎng)上傳輸流量，提升應(yīng)用程序性能。第3頁12.1.2存放過程優(yōu)缺點存放過程能夠封裝一些慣用復(fù)雜操作以提升復(fù)用性，而且確保數(shù)據(jù)庫安全性。然而在享受存放過程帶來便利同時，依然需要考慮系統(tǒng)應(yīng)用需求以確定是否選取存放過程。1．存放過程優(yōu)點2．存放過程缺點第4頁12.1.3存放過程類型在SQLServer中，存放過程能夠分為兩類，分別是T-SQL存放過程和CLR存放過程。T-SQL存放過程是指用T-SQL語句編寫存放過程，是一組完成特定功效T-SQL語句集合；CLR存放過程是指導(dǎo)用.NETFRAMEWORK公共語言時方法存放過程。它在.NETFRAMEWORK程序集中是以類公共靜態(tài)方法實現(xiàn)。按照存放過程應(yīng)用目和起源不一樣，能夠?qū)⒋娣胚^程分為系統(tǒng)存放過程、用戶存放過程和擴展存放過程。1．系統(tǒng)存放過程2．用戶存放過程3．?dāng)U展存放過程第5頁12.1.4存放過程與視圖比較存放過程與視圖區(qū)分以下：視圖是從一個或者多個表或視圖中導(dǎo)出來表，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表查詢基礎(chǔ)上，但其并不存在物理上結(jié)構(gòu)；而存放過程類似于程序，一個用SQL語句寫程序，在其內(nèi)能夠定義變量。視圖在每次查詢時，都需要重新生成執(zhí)行計劃；而存放過程第一次編譯完成后，以后就不需要再次編譯，執(zhí)行速度比視圖快。能夠在單個存放過程中執(zhí)行一系列T-SQL語句，存放過程能夠包含程序流，邏輯以及對數(shù)據(jù)庫查詢T-SQL語句，而視圖中只能是SELECT語句。視圖查詢條件是固定，而存放過程能夠經(jīng)過給里面參數(shù)賦值到達查詢條件靈活改變效果。第6頁12.2創(chuàng)建和執(zhí)行存放過程對于存放過程概念和優(yōu)缺點有了充分認識之后，下面將詳細講解存放過程關(guān)鍵內(nèi)容，即怎樣創(chuàng)建和執(zhí)行存放過程。第7頁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)建存放過程第8頁12.2.1創(chuàng)建存放過程第9頁12.2.1創(chuàng)建存放過程第10頁12.2.2執(zhí)行存放過程為了便于演示，此處存放過程執(zhí)行都是放在SSMS查詢編輯器窗口中進行。系統(tǒng)開發(fā)過程中，不一樣編程語言有不一樣執(zhí)行存放過程語法。1．執(zhí)行存放過程語法2．執(zhí)行存放過程第11頁12.2.2執(zhí)行存放過程第12頁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ù)存放過程第13頁12.2.3帶輸入?yún)?shù)存放過程第14頁12.2.4帶輸出參數(shù)存放過程系統(tǒng)開發(fā)過程中，有時需要對查詢結(jié)果進行一定形式操作，并接結(jié)果返回給用戶，需要用到帶輸出參數(shù)存放過程。1．創(chuàng)建帶輸出參數(shù)存放過程2．執(zhí)行帶輸入?yún)?shù)存放過程第15頁12.2.4帶輸出參數(shù)存放過程第16頁12.2.4帶輸出參數(shù)存放過程第17頁12.2.5綜合實例本小節(jié)將展示一個綜合程度較高存放過程實例，該實例以帶輸入?yún)?shù)存放過程為主要結(jié)構(gòu)，結(jié)合慣用T-SQL語法結(jié)構(gòu)，其功效是實現(xiàn)大批量數(shù)據(jù)分頁輸出。1．存放過程代碼2．關(guān)鍵步驟解析第18頁12.2.5綜合實例第19頁12.3存放過程相關(guān)操作除了能夠創(chuàng)建和執(zhí)行存放過程外，還能夠?qū)Υ娣胚^程執(zhí)行修改、查看、重命名和刪除等操作，實現(xiàn)較豐富功效。第20頁12.3.1修改存放過程已經(jīng)有存放過程不完善，或者產(chǎn)生新特定業(yè)務(wù)需求時，需要對原有存放過程進行修改。修改之后存放過程與原存放過程名稱相同。1．修改存放過程基本語法2．使用視圖設(shè)計器對存放過程進行修改第21頁12.3.1修改存放過程第22頁12.3.1修改存放過程第23頁12.3.1修改存放過程第24頁12.3.2查看存放過程使用存放過程時，難以依據(jù)存放過程名稱直接判斷其功效，需要對存放過程進行查看操作才能取得相關(guān)該存放過程詳細信息。查看存放過程方法有兩種，一個是使用SSMS查看，另一個是使用T-SQL查看。1．使用T-SQL語句查看存放過程2．使用SSMS查看存放過程定義第25頁12.3.2查看存放過程第26頁12.3.2查看存放過程第27頁12.3.3重命名存放過程創(chuàng)建存放過程時定義名稱有時不能準確反應(yīng)該存放過程功效，或者很多存放過程需要按照一定規(guī)則統(tǒng)一命名，這就需要對原有存放過程進行重命名。第28頁12.3.3重命名存放過程第29頁12.3.4刪除存放過程不需要存放過程能夠刪除掉，然而刪除時候要詳細分析是否存在關(guān)聯(lián)到該存放過程其它存放過程。假如存在，則調(diào)用該存放過程時會產(chǎn)生錯誤信息。刪除存放過程一樣有兩種方法，一個是使用SSMS，另一個是使用T-SQL語句進行刪除。1．使用T-SQL語句刪除存放過程2．使用SSMS刪除存放過程第30頁12.3.4刪除存放過程第31頁12.4系統(tǒng)存放過程與擴展存放過程前文所包括存放過程均為自定義存放過程，即為實現(xiàn)某種特定業(yè)務(wù)需求，由用戶編寫T-SQL語句集。除此之外，SQLServer還提供了系統(tǒng)存放過程和擴展存放過程。第32頁12.4.1系統(tǒng)存放過程系統(tǒng)存放過程是SQLSerer預(yù)先定義好，其功效主要定位于為用戶提供對象屬性、系統(tǒng)性能等信息查看，防止用戶直接訪問系統(tǒng)表。1．系統(tǒng)存放過程分類2．系統(tǒng)存放過程應(yīng)用第33頁12.4.1系統(tǒng)存放過程第34頁12.4.2擴展存放過程擴展存放過程提供從SQLServer到外部程序接口，方便進行各種維護活動，實質(zhì)上是SQLServer能夠動態(tài)裝載并執(zhí)行動態(tài)鏈接庫。下面列出一些慣用擴展存放過程。xp_availablemedia：xp_dirtree：xp_enumdsn：xp_enumgroups：xp_getfiledetails：xp_makecab：xp_ntsec_enumdomains：xp_servicecontrol：xp_subdirs：第35頁12.5用戶自定義函數(shù)SQLServer不但提供了豐富系統(tǒng)內(nèi)置函數(shù)，同時還允許用戶自定義函數(shù)，滿足個性化業(yè)務(wù)需求。用戶自定義函數(shù)是SQLServer數(shù)據(jù)庫對象，它不能用于執(zhí)行一系列改變數(shù)據(jù)庫狀態(tài)操作，但它能夠像系統(tǒng)函數(shù)一樣在查詢或存放過程等程序段中使用，也能夠像存放過程一樣經(jīng)過EXECUTE命令來執(zhí)行。1．用戶自定義函數(shù)概述2．創(chuàng)建用戶自定義函數(shù)