存儲(chǔ)過(guò)程與自定義函數(shù).ppt

《存儲(chǔ)過(guò)程與自定義函數(shù).ppt》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。

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

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

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

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

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

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

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