數(shù)據(jù)庫(kù)原理與應(yīng)用-09.存儲(chǔ)過(guò)程的創(chuàng)建和使用ppt課件

1、 第九講第九講 存儲(chǔ)過(guò)程的創(chuàng)建和運(yùn)用存儲(chǔ)過(guò)程的創(chuàng)建和運(yùn)用1、創(chuàng)建存儲(chǔ)過(guò)程、創(chuàng)建存儲(chǔ)過(guò)程2、執(zhí)行存儲(chǔ)過(guò)程、執(zhí)行存儲(chǔ)過(guò)程3、查看和修正存儲(chǔ)過(guò)程、查看和修正存儲(chǔ)過(guò)程4、重命名和刪除存儲(chǔ)過(guò)程、重命名和刪除存儲(chǔ)過(guò)程 本講主要內(nèi)容 存儲(chǔ)過(guò)程的概念SQL ServerSQL Server提供了一種方法，它可以將一些固定的提供了一種方法，它可以將一些固定的操作集中起來(lái)由操作集中起來(lái)由SQL ServerSQL Server數(shù)據(jù)庫(kù)效力器來(lái)完成，以實(shí)數(shù)據(jù)庫(kù)效力器來(lái)完成，以實(shí)現(xiàn)某個(gè)義務(wù)，這種方法就是存儲(chǔ)過(guò)程。類似于現(xiàn)某個(gè)義務(wù)，這種方法就是存儲(chǔ)過(guò)程。類似于DOSDOS一下的一下的批處置。存儲(chǔ)過(guò)程是批處置。存儲(chǔ)過(guò)程是S

2、QLSQL語(yǔ)句和可選控制流程語(yǔ)句的預(yù)編語(yǔ)句和可選控制流程語(yǔ)句的預(yù)編譯集合。是一種封裝反復(fù)義務(wù)操作的方法，以一個(gè)稱號(hào)譯集合。是一種封裝反復(fù)義務(wù)操作的方法，以一個(gè)稱號(hào)存儲(chǔ)，作為一個(gè)單元處置。存儲(chǔ)過(guò)程屬于效力器方軟件，存儲(chǔ)，作為一個(gè)單元處置。存儲(chǔ)過(guò)程屬于效力器方軟件，可立刻訪問(wèn)數(shù)據(jù)庫(kù)可立刻訪問(wèn)數(shù)據(jù)庫(kù)在在SQL ServerSQL Server中存儲(chǔ)過(guò)程分為兩類：即系統(tǒng)提供的中存儲(chǔ)過(guò)程分為兩類：即系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。 存儲(chǔ)過(guò)程存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，可由運(yùn)用程序經(jīng)過(guò)一個(gè)存儲(chǔ)過(guò)程存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，可由運(yùn)用程序經(jīng)過(guò)一個(gè)調(diào)用來(lái)執(zhí)行，而且充許用戶聲明變量。同時(shí)，存儲(chǔ)

3、過(guò)程調(diào)用來(lái)執(zhí)行，而且充許用戶聲明變量。同時(shí)，存儲(chǔ)過(guò)程可以接納和輸出參數(shù)、前往執(zhí)行存儲(chǔ)過(guò)程的形狀值，也可以接納和輸出參數(shù)、前往執(zhí)行存儲(chǔ)過(guò)程的形狀值，也可以嵌套調(diào)用?？梢郧短渍{(diào)用。留意留意:存儲(chǔ)過(guò)程與函數(shù)不同，由于存儲(chǔ)過(guò)程并不前往存儲(chǔ)過(guò)程與函數(shù)不同，由于存儲(chǔ)過(guò)程并不前往取代其稱號(hào)的值，也不能直接在表達(dá)式中運(yùn)用。取代其稱號(hào)的值，也不能直接在表達(dá)式中運(yùn)用。 在在SQL Server中存儲(chǔ)過(guò)程分為兩類中存儲(chǔ)過(guò)程分為兩類:系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶存系統(tǒng)提供的存儲(chǔ)過(guò)程和用戶存儲(chǔ)過(guò)程。系統(tǒng)過(guò)程主要存儲(chǔ)在儲(chǔ)過(guò)程。系統(tǒng)過(guò)程主要存儲(chǔ)在Master數(shù)據(jù)庫(kù)中，并以數(shù)據(jù)庫(kù)中，并以SP_為前綴，并且為前綴，并且系統(tǒng)存儲(chǔ)過(guò)程

4、主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理系統(tǒng)存儲(chǔ)過(guò)程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server提供支持。經(jīng)過(guò)系統(tǒng)存儲(chǔ)過(guò)程，提供支持。經(jīng)過(guò)系統(tǒng)存儲(chǔ)過(guò)程，SQLServer中的許多管理性或信息性的活動(dòng)中的許多管理性或信息性的活動(dòng):如了解數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息如了解數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息都可以被順利有效地完成。雖然這些系統(tǒng)存儲(chǔ)過(guò)程被放在都可以被順利有效地完成。雖然這些系統(tǒng)存儲(chǔ)過(guò)程被放在master數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)中，但是仍可以在其它數(shù)據(jù)庫(kù)中對(duì)其進(jìn)展調(diào)用，在調(diào)用時(shí)不用在存儲(chǔ)過(guò)中，但是仍可以在其它數(shù)據(jù)庫(kù)中對(duì)其進(jìn)展調(diào)用，在調(diào)用時(shí)不用在存儲(chǔ)過(guò)程名前加上數(shù)據(jù)庫(kù)名，而且當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)

5、庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程程名前加上數(shù)據(jù)庫(kù)名，而且當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)建。用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建并能完會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)建。用戶自定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建并能完成某一特定功能成某一特定功能(如查詢用戶所需數(shù)據(jù)信息如查詢用戶所需數(shù)據(jù)信息)的存儲(chǔ)過(guò)程。的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程時(shí)存放在存儲(chǔ)過(guò)程時(shí)存放在SQL Server中的特別快的數(shù)據(jù)庫(kù)對(duì)象，當(dāng)初次運(yùn)中的特別快的數(shù)據(jù)庫(kù)對(duì)象，當(dāng)初次運(yùn)轉(zhuǎn)存儲(chǔ)過(guò)程時(shí)，它按以下方式進(jìn)展轉(zhuǎn)存儲(chǔ)過(guò)程時(shí)，它按以下方式進(jìn)展:1、該過(guò)程被劃分成部件片斷。、該過(guò)程被劃分成部件片斷。2、檢查援用數(shù)據(jù)庫(kù)中其它對(duì)蒙、檢查援用數(shù)據(jù)庫(kù)中其它對(duì)蒙(表、

6、視圖等表、視圖等)的部件，確保援用的對(duì)的部件，確保援用的對(duì)象是存在的，這也被稱為分解。象是存在的，這也被稱為分解。3、一旦分解完成，該過(guò)程的名字將存放倒、一旦分解完成，該過(guò)程的名字將存放倒sysobjects表中，而創(chuàng)表中，而創(chuàng)建存儲(chǔ)過(guò)程的代碼存放在建存儲(chǔ)過(guò)程的代碼存放在syscomments表中表中4、然后編譯，并且，編譯過(guò)程中將創(chuàng)建如何運(yùn)轉(zhuǎn)查詢的藍(lán)本。該、然后編譯，并且，編譯過(guò)程中將創(chuàng)建如何運(yùn)轉(zhuǎn)查詢的藍(lán)本。該藍(lán)本通稱稱為常規(guī)方案或查詢樹，查詢樹存放在藍(lán)本通稱稱為常規(guī)方案或查詢樹，查詢樹存放在sysProcetlures表中。表中。5、存儲(chǔ)過(guò)程初次運(yùn)轉(zhuǎn)時(shí)，讀出查詢方案并完全編譯成過(guò)程方案，、

7、存儲(chǔ)過(guò)程初次運(yùn)轉(zhuǎn)時(shí)，讀出查詢方案并完全編譯成過(guò)程方案，然后運(yùn)轉(zhuǎn)。這樣，節(jié)約了每次運(yùn)轉(zhuǎn)存成過(guò)程的語(yǔ)法檢查、分解和編譯查然后運(yùn)轉(zhuǎn)。這樣，節(jié)約了每次運(yùn)轉(zhuǎn)存成過(guò)程的語(yǔ)法檢查、分解和編譯查詢樹的時(shí)間。詢樹的時(shí)間。當(dāng)利用當(dāng)利用SQL Server創(chuàng)建一個(gè)運(yùn)用程序時(shí)，創(chuàng)建一個(gè)運(yùn)用程序時(shí)，T-SQL是一種主是一種主要的編程言語(yǔ)。假設(shè)運(yùn)用要的編程言語(yǔ)。假設(shè)運(yùn)用T一一SQL來(lái)進(jìn)展編程有兩種方法來(lái)進(jìn)展編程有兩種方法:其一是在本地存儲(chǔ)其一是在本地存儲(chǔ)T-SQL程序并創(chuàng)建運(yùn)用程序，向程序并創(chuàng)建運(yùn)用程序，向SQL-Server發(fā)送命令米對(duì)結(jié)果進(jìn)展發(fā)送命令米對(duì)結(jié)果進(jìn)展T-SQL編寫的程序作為存儲(chǔ)編寫的程序作為存儲(chǔ)過(guò)程其二是

8、可以把部分用在過(guò)程其二是可以把部分用在SQL Server中，并創(chuàng)建運(yùn)用程中，并創(chuàng)建運(yùn)用程序來(lái)調(diào)用存儲(chǔ)過(guò)程，對(duì)數(shù)據(jù)結(jié)果進(jìn)展處置。存儲(chǔ)過(guò)程可以序來(lái)調(diào)用存儲(chǔ)過(guò)程，對(duì)數(shù)據(jù)結(jié)果進(jìn)展處置。存儲(chǔ)過(guò)程可以經(jīng)過(guò)接納參數(shù)向調(diào)用者前往結(jié)果集，結(jié)果集的格式由調(diào)用經(jīng)過(guò)接納參數(shù)向調(diào)用者前往結(jié)果集，結(jié)果集的格式由調(diào)用者確定者確定;前往形狀值給調(diào)用者，指明調(diào)用是勝利或是失敗前往形狀值給調(diào)用者，指明調(diào)用是勝利或是失敗:包括針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，并且可以在一個(gè)存儲(chǔ)過(guò)程中包括針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，并且可以在一個(gè)存儲(chǔ)過(guò)程中調(diào)用另一存儲(chǔ)過(guò)程。調(diào)用另一存儲(chǔ)過(guò)程。我們通常更偏愛(ài)于運(yùn)用第二種方法，即在我們通常更偏愛(ài)于運(yùn)用第二種方法，即在S

9、QL Server中運(yùn)中運(yùn)用存儲(chǔ)過(guò)程，而不是在客戶計(jì)算機(jī)上調(diào)用用存儲(chǔ)過(guò)程，而不是在客戶計(jì)算機(jī)上調(diào)用T一一QL編寫的一編寫的一段程序緣由在于存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)段程序緣由在于存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn):存儲(chǔ)過(guò)程的優(yōu)點(diǎn)可用存儲(chǔ)過(guò)程封裝事務(wù)規(guī)那么。一旦封裝完成，這些可用存儲(chǔ)過(guò)程封裝事務(wù)規(guī)那么。一旦封裝完成，這些規(guī)那么就可用于多個(gè)運(yùn)用，從而有一個(gè)一致的數(shù)據(jù)接規(guī)那么就可用于多個(gè)運(yùn)用，從而有一個(gè)一致的數(shù)據(jù)接口，因此，只需改動(dòng)過(guò)程的功能，只需在一個(gè)地方對(duì)口，因此，只需改動(dòng)過(guò)程的功能，只需在一個(gè)地方對(duì)其進(jìn)展修正，而不用對(duì)每個(gè)運(yùn)用都進(jìn)展修正。其進(jìn)展修正，而不用對(duì)每個(gè)運(yùn)用都進(jìn)展修正。存儲(chǔ)過(guò)程允許規(guī)范組件式編程存儲(chǔ)過(guò)程允

10、許規(guī)范組件式編程:存儲(chǔ)過(guò)程在被創(chuàng)存儲(chǔ)過(guò)程在被創(chuàng)建以后，可以在程序中被多次調(diào)用而不用重新編建以后，可以在程序中被多次調(diào)用而不用重新編寫該存儲(chǔ)過(guò)程的寫該存儲(chǔ)過(guò)程的SQL語(yǔ)句語(yǔ)句;而且數(shù)據(jù)庫(kù)專業(yè)人員而且數(shù)據(jù)庫(kù)專業(yè)人員可隨時(shí)對(duì)存儲(chǔ)過(guò)程進(jìn)展修正，但對(duì)運(yùn)用程序源代可隨時(shí)對(duì)存儲(chǔ)過(guò)程進(jìn)展修正，但對(duì)運(yùn)用程序源代碼毫無(wú)影響，由于運(yùn)用程序源代碼只包含存儲(chǔ)過(guò)碼毫無(wú)影響，由于運(yùn)用程序源代碼只包含存儲(chǔ)過(guò)程的調(diào)用語(yǔ)句，從而極大地提高了程序的可移植程的調(diào)用語(yǔ)句，從而極大地提高了程序的可移植性。性。存儲(chǔ)過(guò)程可以實(shí)現(xiàn)較快的執(zhí)行速度存儲(chǔ)過(guò)程可以實(shí)現(xiàn)較快的執(zhí)行速度:假設(shè)某一操作包含大假設(shè)某一操作包含大量的量的TSQL代碼或分別被多次

11、執(zhí)行，那么存儲(chǔ)過(guò)程要比代碼或分別被多次執(zhí)行，那么存儲(chǔ)過(guò)程要比批處置的執(zhí)行速度快很多。由于存儲(chǔ)過(guò)程是預(yù)編譯的，在批處置的執(zhí)行速度快很多。由于存儲(chǔ)過(guò)程是預(yù)編譯的，在初次運(yùn)轉(zhuǎn)一個(gè)存儲(chǔ)過(guò)程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)展分析優(yōu)化，初次運(yùn)轉(zhuǎn)一個(gè)存儲(chǔ)過(guò)程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)展分析優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行方案并給出最終被存在系統(tǒng)表中的執(zhí)行方案;而批處置的而批處置的T-SQL語(yǔ)句在每次運(yùn)轉(zhuǎn)時(shí)都要進(jìn)展編譯和優(yōu)化，因此速度相語(yǔ)句在每次運(yùn)轉(zhuǎn)時(shí)都要進(jìn)展編譯和優(yōu)化，因此速度相對(duì)要慢一些。對(duì)要慢一些。存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)流量存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)流量:對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象的操作對(duì)象的操作(

12、如查詢修正如查詢修正)，假設(shè)這一操作所涉及到的，假設(shè)這一操作所涉及到的T-SQL語(yǔ)句被組織成一存儲(chǔ)過(guò)程，那么當(dāng)在客戶計(jì)算機(jī)上語(yǔ)句被組織成一存儲(chǔ)過(guò)程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過(guò)程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語(yǔ)句調(diào)用該存儲(chǔ)過(guò)程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語(yǔ)句;否那否那么將是多條么將是多條SQL語(yǔ)句，從而大大添加了網(wǎng)絡(luò)流量，降低語(yǔ)句，從而大大添加了網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。網(wǎng)絡(luò)負(fù)載。存儲(chǔ)過(guò)程可被作為一種平安機(jī)制來(lái)充分利用存儲(chǔ)過(guò)程可被作為一種平安機(jī)制來(lái)充分利用:系統(tǒng)管理員系統(tǒng)管理員經(jīng)過(guò)對(duì)執(zhí)行某一存儲(chǔ)過(guò)程的權(quán)限進(jìn)展限制，從而可以實(shí)經(jīng)過(guò)對(duì)執(zhí)行某一存儲(chǔ)過(guò)程的權(quán)限進(jìn)展限制，從而可以實(shí)現(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問(wèn)權(quán)限的限

13、制，防止非授權(quán)用戶對(duì)數(shù)現(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問(wèn)權(quán)限的限制，防止非授權(quán)用戶對(duì)數(shù)據(jù)的訪問(wèn)，保證數(shù)據(jù)的平安。據(jù)的訪問(wèn)，保證數(shù)據(jù)的平安。創(chuàng)建存儲(chǔ)過(guò)程的規(guī)那么幾乎任何可以寫成批處置的幾乎任何可以寫成批處置的T一一SQL代碼都可用于創(chuàng)建代碼都可用于創(chuàng)建存儲(chǔ)過(guò)程，但是在設(shè)計(jì)存儲(chǔ)過(guò)程時(shí)，需求遵照以下規(guī)那存儲(chǔ)過(guò)程，但是在設(shè)計(jì)存儲(chǔ)過(guò)程時(shí)，需求遵照以下規(guī)那么么:名字必需符合名字必需符合SQL Server命名規(guī)那么。命名規(guī)那么。援用的對(duì)象必需在創(chuàng)建存儲(chǔ)過(guò)程前就存在援用的對(duì)象必需在創(chuàng)建存儲(chǔ)過(guò)程前就存在不能在單個(gè)存儲(chǔ)過(guò)程中創(chuàng)建后去掉或再創(chuàng)建同名的對(duì)象。不能在單個(gè)存儲(chǔ)過(guò)程中創(chuàng)建后去掉或再創(chuàng)建同名的對(duì)象。存儲(chǔ)過(guò)程最后能有存儲(chǔ)過(guò)程

14、最后能有255各參數(shù)。各參數(shù)。創(chuàng)建存儲(chǔ)過(guò)程的規(guī)那么再本人的存儲(chǔ)過(guò)程中可以援用暫時(shí)表，部分暫時(shí)表再過(guò)程終了時(shí)將再本人的存儲(chǔ)過(guò)程中可以援用暫時(shí)表，部分暫時(shí)表再過(guò)程終了時(shí)將會(huì)消逝。會(huì)消逝。再存儲(chǔ)過(guò)程中不能有如下的再存儲(chǔ)過(guò)程中不能有如下的SQL創(chuàng)建語(yǔ)句創(chuàng)建語(yǔ)句:Create Default、Create Procedure、Create Rule、Create Trigger、Create View.可在過(guò)程中嵌套過(guò)程?？稍谶^(guò)程中嵌套過(guò)程。創(chuàng)建存儲(chǔ)過(guò)程的文本不能超越創(chuàng)建存儲(chǔ)過(guò)程的文本不能超越64K字節(jié)，以為字節(jié)，以為SQL存放再存放再syscomments表中。表中。假設(shè)在存儲(chǔ)過(guò)程中運(yùn)用了假設(shè)在存儲(chǔ)過(guò)

15、程中運(yùn)用了Select *，而底層表中參與了新的列，新，而底層表中參與了新的列，新的列再過(guò)程運(yùn)轉(zhuǎn)時(shí)無(wú)法顯示。的列再過(guò)程運(yùn)轉(zhuǎn)時(shí)無(wú)法顯示。1 創(chuàng)建存儲(chǔ)過(guò)程 在在SQL ServerSQL Server中，可以運(yùn)用三種方法創(chuàng)建存中，可以運(yùn)用三種方法創(chuàng)建存儲(chǔ)過(guò)程儲(chǔ)過(guò)程 ：運(yùn)用創(chuàng)建存儲(chǔ)過(guò)程導(dǎo)游創(chuàng)建存儲(chǔ)過(guò)程。運(yùn)用創(chuàng)建存儲(chǔ)過(guò)程導(dǎo)游創(chuàng)建存儲(chǔ)過(guò)程。利用利用SQL Server SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。運(yùn)用運(yùn)用Transact-SQLTransact-SQL語(yǔ)句中的語(yǔ)句中的CREATE CREATE PROCEDUREPROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程。命令創(chuàng)建存儲(chǔ)過(guò)

16、程。 需求確定存儲(chǔ)過(guò)程的三個(gè)組成部分：一切的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。一切的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句。它存儲(chǔ)過(guò)程的語(yǔ)句。前往給調(diào)用者的形狀值，以指明調(diào)用是勝利還前往給調(diào)用者的形狀值，以指明調(diào)用是勝利還是失敗。是失敗。1. 運(yùn)用創(chuàng)建存儲(chǔ)過(guò)程導(dǎo)游創(chuàng)建存儲(chǔ)過(guò)程 在企業(yè)管理器中，選擇工具菜單中的導(dǎo)游在企業(yè)管理器中，選擇工具菜單中的導(dǎo)游選項(xiàng)，選擇選項(xiàng)，選擇“創(chuàng)建存儲(chǔ)過(guò)程導(dǎo)游創(chuàng)建存儲(chǔ)過(guò)程導(dǎo)游 根據(jù)提示可根據(jù)提示可完成創(chuàng)建存儲(chǔ)過(guò)程。完成創(chuàng)建存儲(chǔ)過(guò)程。2. 運(yùn)用SQL Server 企業(yè)管理器創(chuàng)建

17、存儲(chǔ)過(guò)程 在在SQL ServerSQL Server企業(yè)管理器中，選擇指定的效企業(yè)管理器中，選擇指定的效力器和數(shù)據(jù)庫(kù)，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，力器和數(shù)據(jù)庫(kù)，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇在彈出的快捷菜單中選擇“新建選項(xiàng)，再選擇下一級(jí)菜新建選項(xiàng)，再選擇下一級(jí)菜單中的單中的“存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程選項(xiàng)選項(xiàng)在文本框中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的在文本框中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的T_SQLT_SQL語(yǔ)句，單擊語(yǔ)句，單擊“檢查語(yǔ)法，那么可以檢查語(yǔ)法能否正確；檢查語(yǔ)法，那么可以檢查語(yǔ)法能否正確；單擊單擊“確定按鈕，即可保管該存儲(chǔ)過(guò)程。假設(shè)要設(shè)置權(quán)確定按鈕，即可保管該存儲(chǔ)過(guò)程。假設(shè)要

18、設(shè)置權(quán)限，單擊限，單擊“權(quán)限權(quán)限按鈕。按鈕。3. 運(yùn)用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程前，應(yīng)該思索以下幾個(gè)事項(xiàng)：創(chuàng)建存儲(chǔ)過(guò)程前，應(yīng)該思索以下幾個(gè)事項(xiàng)：不能將不能將 CREATE PROCEDURE CREATE PROCEDURE 語(yǔ)句與其它語(yǔ)句與其它 SQL SQL 語(yǔ)句組語(yǔ)句組合到單個(gè)批處置中。合到單個(gè)批處置中。創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限默許屬于數(shù)據(jù)庫(kù)一切者，該一切創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限默許屬于數(shù)據(jù)庫(kù)一切者，該一切者可將此權(quán)限授予其他用戶。者可將此權(quán)限授予其他用戶。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，其稱號(hào)必需遵守標(biāo)識(shí)符規(guī)那存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，其稱號(hào)必需遵守標(biāo)識(shí)符規(guī)那么。么。只能在當(dāng)前數(shù)據(jù)庫(kù)中

19、創(chuàng)建存儲(chǔ)過(guò)程。只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。一個(gè)存儲(chǔ)過(guò)程的最大尺寸為一個(gè)存儲(chǔ)過(guò)程的最大尺寸為128M128M。CREATE PROCEDURE的語(yǔ)法方式： CREATEPROCEDUREprocedure_name;number p a r a m e t e r d a t a _ t y p e V A R Y I N G = d e f a u l t O U T P U T ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION F O R R E P L I C A T I O N AS sql_statement .n CREATE

20、PROCEDURE的語(yǔ)法參數(shù)的意義： procedure_name：用于指定要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的稱號(hào)。：用于指定要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的稱號(hào)。 number：該參數(shù)是可選的整數(shù)，它用來(lái)對(duì)同名的存儲(chǔ)：該參數(shù)是可選的整數(shù)，它用來(lái)對(duì)同名的存儲(chǔ)過(guò)程分組，以便用一條過(guò)程分組，以便用一條 DROP PROCEDURE 語(yǔ)句即可語(yǔ)句即可將同組的過(guò)程一同除去。將同組的過(guò)程一同除去。 parameter：過(guò)程中的參數(shù)。在：過(guò)程中的參數(shù)。在 CREATE PROCEDURE 語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。 data_type：用于指定參數(shù)的數(shù)據(jù)類型。：用于指定參數(shù)的數(shù)據(jù)類型。 VARYING

21、：用于指定作為輸出：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)參數(shù)支持的結(jié)果集。果集。 Default：用于指定參數(shù)的默許值。：用于指定參數(shù)的默許值。 OUTPUT：闡明該參數(shù)是一個(gè)前往參數(shù)。：闡明該參數(shù)是一個(gè)前往參數(shù)。 RECOMPILE：闡明：闡明 SQL Server 不會(huì)保管該存儲(chǔ)過(guò)程不會(huì)保管該存儲(chǔ)過(guò)程的執(zhí)行方案的執(zhí)行方案 。ENCRYPTION ：表示：表示 SQL Server 加密了加密了 syscomments 表，該表的表，該表的text字段是包含字段是包含 CREATE PROCEDURE 語(yǔ)句的存儲(chǔ)過(guò)程文本。語(yǔ)句的存儲(chǔ)過(guò)程文本。 FOR REPLICATION：用于指定不能

22、在訂閱效力器上：用于指定不能在訂閱效力器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。 AS：用于指定該存儲(chǔ)過(guò)程要執(zhí)行的操作。：用于指定該存儲(chǔ)過(guò)程要執(zhí)行的操作。sql_statement：是存儲(chǔ)過(guò)程中要包含的恣意數(shù)目和類：是存儲(chǔ)過(guò)程中要包含的恣意數(shù)目和類型的型的 Transact-SQL 語(yǔ)句。語(yǔ)句。2 執(zhí)行存儲(chǔ)過(guò)程 直接執(zhí)行存儲(chǔ)過(guò)程可以運(yùn)用直接執(zhí)行存儲(chǔ)過(guò)程可以運(yùn)用EXECUTE命令來(lái)命令來(lái)執(zhí)行，其語(yǔ)法方式如下：執(zhí)行，其語(yǔ)法方式如下：EXECUTE r e t u r n _ s t a t u s = procedure_name;number|procedure_name_var

23、parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 假設(shè)執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句是批中的第一個(gè)語(yǔ)句，可以省略假設(shè)執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句是批中的第一個(gè)語(yǔ)句，可以省略execute關(guān)鍵字。關(guān)鍵字。return status為整型部分變量，用于保管存儲(chǔ)過(guò)程的前為整型部分變量，用于保管存儲(chǔ)過(guò)程的前往值往值;procedure_name指定執(zhí)行的存儲(chǔ)過(guò)程的稱指定執(zhí)行的存儲(chǔ)過(guò)程的稱號(hào)號(hào);number用來(lái)指定該存儲(chǔ)過(guò)程與其它同名存儲(chǔ)過(guò)程用來(lái)指定該存儲(chǔ)過(guò)程與其它同名存儲(chǔ)過(guò)程同組時(shí)的標(biāo)識(shí)號(hào)。同組時(shí)的標(biāo)識(shí)號(hào)。parameter:在創(chuàng)建過(guò)程時(shí)定義的過(guò)程參數(shù)。調(diào)用

24、者向存在創(chuàng)建過(guò)程時(shí)定義的過(guò)程參數(shù)。調(diào)用者向存儲(chǔ)過(guò)程所傳送的參數(shù)值由儲(chǔ)過(guò)程所傳送的參數(shù)值由value參數(shù)或參數(shù)或variable變量提供，變量提供，或者運(yùn)用或者運(yùn)用default關(guān)鍵字指定運(yùn)用該參數(shù)的默許值。關(guān)鍵字指定運(yùn)用該參數(shù)的默許值。output參數(shù)闡明指定參數(shù)為前往參數(shù)。參數(shù)闡明指定參數(shù)為前往參數(shù)。留意留意:假設(shè)按假設(shè)按paramete=value|variable的方式為存儲(chǔ)過(guò)的方式為存儲(chǔ)過(guò)程提供參數(shù)，可以不思索創(chuàng)建存儲(chǔ)過(guò)程時(shí)的參數(shù)順序，但程提供參數(shù)，可以不思索創(chuàng)建存儲(chǔ)過(guò)程時(shí)的參數(shù)順序，但是必需以這種方式提供該存儲(chǔ)過(guò)程的全部參數(shù)。假設(shè)不以是必需以這種方式提供該存儲(chǔ)過(guò)程的全部參數(shù)。假設(shè)不以

25、這種方式提供參數(shù)，那么必需按照創(chuàng)建存儲(chǔ)過(guò)程時(shí)參數(shù)的這種方式提供參數(shù)，那么必需按照創(chuàng)建存儲(chǔ)過(guò)程時(shí)參數(shù)的順序提供參數(shù)。順序提供參數(shù)。With Recompile指定在實(shí)行存儲(chǔ)過(guò)程時(shí)重新編譯執(zhí)行方指定在實(shí)行存儲(chǔ)過(guò)程時(shí)重新編譯執(zhí)行方案。案。例：Create procedure GetAvgname varchar(10),avg int outputAs DeclareErrorSave intSetErrorSave=0Select avgp=AVG(工程工程)From project as p INNER JOIN pmanager as pmon p.擔(dān)任人擔(dān)任人ID=Pm.擔(dān)任人擔(dān)任人IDWh

26、ere pm.姓名姓名=nameif(Error 0) SetErrorSave=ErrorReturnErrorSavego例： 運(yùn)用運(yùn)用 EXECUTE 命令傳送單個(gè)參數(shù)，它執(zhí)行命令傳送單個(gè)參數(shù)，它執(zhí)行 showind 存存儲(chǔ)過(guò)程，以儲(chǔ)過(guò)程，以 titles 為參數(shù)值。為參數(shù)值。showind 存儲(chǔ)過(guò)程需求參數(shù)存儲(chǔ)過(guò)程需求參數(shù) (tabname)，它是一個(gè)表的稱號(hào)。其程序清單如下：，它是一個(gè)表的稱號(hào)。其程序清單如下：EXEC showind titles當(dāng)然，在執(zhí)行過(guò)程中變量可以顯式命名：當(dāng)然，在執(zhí)行過(guò)程中變量可以顯式命名：EXEC showind tabname = titles假設(shè)這是

27、假設(shè)這是 isql 腳本或批處置中第一個(gè)語(yǔ)句，那么腳本或批處置中第一個(gè)語(yǔ)句，那么 EXEC 語(yǔ)語(yǔ)句可以省略：句可以省略：showind titles或者或者showind tabname = titles3 查看和修正存儲(chǔ)過(guò)程 查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表存儲(chǔ)過(guò)程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表中，它的源代碼存放在系統(tǒng)表syscomments中。可以運(yùn)用運(yùn)用企業(yè)管理器或系統(tǒng)存中。可以運(yùn)用運(yùn)用企業(yè)管理器或系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程。儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程。1運(yùn)用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過(guò)

28、程 在企業(yè)管理器中，翻開指定的效力器和數(shù)在企業(yè)管理器中，翻開指定的效力器和數(shù)據(jù)庫(kù)項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，單擊存據(jù)庫(kù)項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，單擊存儲(chǔ)過(guò)程文件夾，此時(shí)在右邊的頁(yè)框中顯示該數(shù)據(jù)儲(chǔ)過(guò)程文件夾，此時(shí)在右邊的頁(yè)框中顯示該數(shù)據(jù)庫(kù)的一切存儲(chǔ)過(guò)程。用右鍵單擊要查看的存儲(chǔ)過(guò)庫(kù)的一切存儲(chǔ)過(guò)程。用右鍵單擊要查看的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便程，從彈出的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便可以看到存儲(chǔ)過(guò)程的源代碼?？梢钥吹酱鎯?chǔ)過(guò)程的源代碼。 2運(yùn)用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程 可供運(yùn)用的系統(tǒng)存儲(chǔ)過(guò)程及其語(yǔ)法方式如下：可供運(yùn)用的系統(tǒng)存儲(chǔ)過(guò)程及其語(yǔ)法方式如下：sp_he

29、lp：用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類型：用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類型sp_help objname= name參數(shù)參數(shù)name為要查看的存儲(chǔ)過(guò)程的稱號(hào)。為要查看的存儲(chǔ)過(guò)程的稱號(hào)。 sp_helptext：用于顯示存儲(chǔ)過(guò)程的源代碼：用于顯示存儲(chǔ)過(guò)程的源代碼 sp_helptext objname= name參數(shù)參數(shù)name為要查看的存儲(chǔ)過(guò)程的稱號(hào)。為要查看的存儲(chǔ)過(guò)程的稱號(hào)。sp_depends：用于顯示和存儲(chǔ)過(guò)程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象：用于顯示和存儲(chǔ)過(guò)程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象sp_depends objname=object參數(shù)參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過(guò)程的稱號(hào)。為要查看依賴關(guān)系的存儲(chǔ)

30、過(guò)程的稱號(hào)。sp_stored_procedures：用于前往當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表：用于前往當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表2. 修正存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程可以根據(jù)用戶的要求或者基表定義的改動(dòng)而改動(dòng)。運(yùn)用存儲(chǔ)過(guò)程可以根據(jù)用戶的要求或者基表定義的改動(dòng)而改動(dòng)。運(yùn)用ALTER PROCEDURE語(yǔ)句可以更改先前經(jīng)過(guò)執(zhí)行語(yǔ)句可以更改先前經(jīng)過(guò)執(zhí)行 CREATE PROCEDURE 語(yǔ)句創(chuàng)建的過(guò)程，但不會(huì)更改權(quán)限，也不影響相關(guān)的語(yǔ)句創(chuàng)建的過(guò)程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。其語(yǔ)法方式如下：存儲(chǔ)過(guò)程或觸發(fā)器。其語(yǔ)法方式如下：ALTERPROCEDUREprocedure_name;number

31、parameterdata_type V A R Y I N G = d e f a u l t O U T P U T , . . . n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION AS sql_statement .n 4 重命名和刪除存儲(chǔ)過(guò)程1. 重命名存儲(chǔ)過(guò)程重命名存儲(chǔ)過(guò)程修正存儲(chǔ)過(guò)程的稱號(hào)可以運(yùn)用系統(tǒng)存儲(chǔ)過(guò)程修正存儲(chǔ)過(guò)程的稱號(hào)可以運(yùn)用系統(tǒng)存儲(chǔ)過(guò)程sp_rename，其語(yǔ)法方式如下：，其語(yǔ)法方式如下：sp_rename 原存儲(chǔ)過(guò)程稱號(hào)，新存儲(chǔ)過(guò)程原存儲(chǔ)過(guò)程稱號(hào)，新存儲(chǔ)過(guò)程稱號(hào)稱號(hào)另外，經(jīng)過(guò)企業(yè)管理器也可以修

32、正存儲(chǔ)過(guò)程另外，經(jīng)過(guò)企業(yè)管理器也可以修正存儲(chǔ)過(guò)程的稱號(hào)。的稱號(hào)。 2. 刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程可以運(yùn)用刪除存儲(chǔ)過(guò)程可以運(yùn)用DROP命令，命令，DROP命令命令可以將一個(gè)或者多個(gè)存儲(chǔ)過(guò)程或者存儲(chǔ)過(guò)程組從可以將一個(gè)或者多個(gè)存儲(chǔ)過(guò)程或者存儲(chǔ)過(guò)程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法方式如下：當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法方式如下：drop procedure procedure ,n當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存儲(chǔ)當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存儲(chǔ)過(guò)程。過(guò)程。 在存儲(chǔ)過(guò)程中運(yùn)用參數(shù)例例:下面的存儲(chǔ)過(guò)程有下面的存儲(chǔ)過(guò)程有5個(gè)傳入?yún)?shù)，求其平均值，而后將個(gè)傳入?yún)?shù)，求其平均值，而后將平均值賦給平均值

33、賦給output。Create Procedure scoresscore1 smallint,score2 smallint,score3 smallint,score4 smallint,score5 smallint,myAvg smallint OutputAs selectmyAvg=(scarel+scare2+scare3+scare4+scare5)/5要調(diào)用要調(diào)用myAvg的值，一首先要定義一個(gè)變量，然的值，一首先要定義一個(gè)變量，然后才干運(yùn)轉(zhuǎn)該程序。后才干運(yùn)轉(zhuǎn)該程序。Declare AvgScore smallintExec scores 113,9,8,8,113,AvgScnre OutputSelect The Average Score is:，AvgScnrego在將值傳送給存儲(chǔ)過(guò)程時(shí)，可以按位置順序傳入在將值傳送給存儲(chǔ)過(guò)程時(shí)，可以按位置順序傳入(稱為按位置傳稱為按位置傳入入)