創(chuàng)建和管理存儲(chǔ)過程

1、第章創(chuàng)建和管理存儲(chǔ)過程第章創(chuàng)建和管理存儲(chǔ)過程第一頁(yè)，共21頁(yè)。11.1 11.1 什么是存儲(chǔ)過程什么是存儲(chǔ)過程在在SQL Server 2008應(yīng)用操作中，存儲(chǔ)過程扮演相當(dāng)重要的應(yīng)用操作中，存儲(chǔ)過程扮演相當(dāng)重要的角色。它們基于預(yù)編譯并存儲(chǔ)在角色。它們基于預(yù)編譯并存儲(chǔ)在SQL Server數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)中，不僅能提高應(yīng)用效率確保一致性，更能提高系統(tǒng)執(zhí)行的不僅能提高應(yīng)用效率確保一致性，更能提高系統(tǒng)執(zhí)行的速度。速度。第二頁(yè)，共21頁(yè)。11.1.1 11.1.1 存儲(chǔ)過程的基本概念存儲(chǔ)過程的基本概念SQL Server 2008的存儲(chǔ)過程包含一些的存儲(chǔ)過程包含一些Transact-SQL語(yǔ)句并語(yǔ)句并

2、以特定的名稱存儲(chǔ)在數(shù)據(jù)庫(kù)中。存儲(chǔ)過程也是一種數(shù)據(jù)以特定的名稱存儲(chǔ)在數(shù)據(jù)庫(kù)中。存儲(chǔ)過程也是一種數(shù)據(jù)庫(kù)對(duì)象。存儲(chǔ)過程具有實(shí)現(xiàn)聲明變量、條件執(zhí)行以及其庫(kù)對(duì)象。存儲(chǔ)過程具有實(shí)現(xiàn)聲明變量、條件執(zhí)行以及其他各項(xiàng)強(qiáng)大的程序設(shè)計(jì)功能。他各項(xiàng)強(qiáng)大的程序設(shè)計(jì)功能。第三頁(yè)，共21頁(yè)。11.1.2 11.1.2 存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的優(yōu)點(diǎn)當(dāng)利用當(dāng)利用MS SQL Server創(chuàng)建一個(gè)應(yīng)用程序時(shí)，創(chuàng)建一個(gè)應(yīng)用程序時(shí)，Transaction-SQL是一種主要的編程語(yǔ)言。若運(yùn)用是一種主要的編程語(yǔ)言。若運(yùn)用Transaction-SQL來來進(jìn)行編程，有兩種方法。進(jìn)行編程，有兩種方法。在本地存儲(chǔ)在本地存儲(chǔ)Transactio

3、n-SQL程序，并創(chuàng)建應(yīng)用程序向程序，并創(chuàng)建應(yīng)用程序向SQL Server發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。把部分用把部分用Transaction-SQL編寫的程序作為存儲(chǔ)過程存儲(chǔ)在編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQL Server中，并創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)中，并創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。存儲(chǔ)過程能夠通過接收參數(shù)向調(diào)用者據(jù)結(jié)果進(jìn)行處理。存儲(chǔ)過程能夠通過接收參數(shù)向調(diào)用者返回結(jié)果集，結(jié)果集的格式由調(diào)用者確定；返回狀態(tài)值返回結(jié)果集，結(jié)果集的格式由調(diào)用者確定；返回狀態(tài)值給調(diào)用者，指明調(diào)用是成功或是失敗；并可以在一個(gè)存給調(diào)用者，指明調(diào)用是成功或是失??；并

4、可以在一個(gè)存儲(chǔ)過程中調(diào)用另一存儲(chǔ)過程。儲(chǔ)過程中調(diào)用另一存儲(chǔ)過程。第四頁(yè)，共21頁(yè)。11.1.3 11.1.3 存儲(chǔ)過程與視圖的區(qū)別存儲(chǔ)過程與視圖的區(qū)別存儲(chǔ)過程與視圖有著類似的功能，二者之間也具有一定的差存儲(chǔ)過程與視圖有著類似的功能，二者之間也具有一定的差別：別：可以在單個(gè)存儲(chǔ)過程中執(zhí)行一系列可以在單個(gè)存儲(chǔ)過程中執(zhí)行一系列Transact-SQL語(yǔ)句，而語(yǔ)句，而在視圖中只能是在視圖中只能是SELECT語(yǔ)句。語(yǔ)句。視圖不能接受參數(shù)，只能返回結(jié)果集；而存儲(chǔ)過程可以接受視圖不能接受參數(shù)，只能返回結(jié)果集；而存儲(chǔ)過程可以接受參數(shù)，包括輸入、輸出參數(shù)，并能返回單個(gè)或多個(gè)結(jié)果參數(shù)，包括輸入、輸出參數(shù)，并能返回

5、單個(gè)或多個(gè)結(jié)果集以及返回值，這樣可大大地提高應(yīng)用的靈活性。集以及返回值，這樣可大大地提高應(yīng)用的靈活性。一般來說，人們將經(jīng)常用到的多個(gè)表的連接查詢定義為視圖一般來說，人們將經(jīng)常用到的多個(gè)表的連接查詢定義為視圖，而存儲(chǔ)過程完成復(fù)雜的一系列的處理，在存儲(chǔ)過程中，而存儲(chǔ)過程完成復(fù)雜的一系列的處理，在存儲(chǔ)過程中也會(huì)經(jīng)常用到視圖。也會(huì)經(jīng)常用到視圖。第五頁(yè)，共21頁(yè)。11.2 11.2 存儲(chǔ)過程分類存儲(chǔ)過程分類在在SQL Server2008中，存儲(chǔ)過程可以分為系統(tǒng)存儲(chǔ)過程和用中，存儲(chǔ)過程可以分為系統(tǒng)存儲(chǔ)過程和用戶存儲(chǔ)過程兩類。下面我們將分別展開學(xué)習(xí)。戶存儲(chǔ)過程兩類。下面我們將分別展開學(xué)習(xí)。第六頁(yè)，共21頁(yè)

6、。11.2.1 11.2.1 系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程（系統(tǒng)存儲(chǔ)過程（System Stored Procedures）主要存儲(chǔ)在）主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中，并以數(shù)據(jù)庫(kù)中，并以sp為前綴。系統(tǒng)存儲(chǔ)過程主要是從為前綴。系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server 2008提供支持。通過系統(tǒng)存儲(chǔ)過程，提供支持。通過系統(tǒng)存儲(chǔ)過程，SQL Server 2008中中的許多管理性或信息性的活動(dòng)的許多管理性或信息性的活動(dòng)(如了解數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)如了解數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息庫(kù)信息)都可以被有效地完成。都可以被有效地完成

7、。第七頁(yè)，共21頁(yè)。11.2.2 11.2.2 本地存儲(chǔ)過程本地存儲(chǔ)過程本地存儲(chǔ)過程本地存儲(chǔ)過程(Local Stored Procedures)也就是用戶自行創(chuàng)也就是用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù)庫(kù)中的存儲(chǔ)過程，。這些存儲(chǔ)過程建并存儲(chǔ)在用戶數(shù)據(jù)庫(kù)中的存儲(chǔ)過程，。這些存儲(chǔ)過程能完成某一特定功能能完成某一特定功能(如查詢用戶所需的數(shù)據(jù)信息如查詢用戶所需的數(shù)據(jù)信息)。事實(shí)。事實(shí)上一般所說的存儲(chǔ)過程指的就是本地存儲(chǔ)過程。上一般所說的存儲(chǔ)過程指的就是本地存儲(chǔ)過程。第八頁(yè)，共21頁(yè)。11.2.3 11.2.3 臨時(shí)存儲(chǔ)過程臨時(shí)存儲(chǔ)過程臨時(shí)存儲(chǔ)過程用以解決臨時(shí)性問題，臨時(shí)存儲(chǔ)過程（臨時(shí)存儲(chǔ)過程用以解決臨時(shí)性

8、問題，臨時(shí)存儲(chǔ)過程（Temporary Stored Procedures）可分為以下兩種：）可分為以下兩種：1.本地臨時(shí)存儲(chǔ)過程本地臨時(shí)存儲(chǔ)過程2.全局臨時(shí)存儲(chǔ)過程全局臨時(shí)存儲(chǔ)過程第九頁(yè)，共21頁(yè)。11.2.4 11.2.4 遠(yuǎn)程存儲(chǔ)過程遠(yuǎn)程存儲(chǔ)過程在在SQL Server 2008中，遠(yuǎn)程存儲(chǔ)過程（中，遠(yuǎn)程存儲(chǔ)過程（Remote Stored Procedures）是位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程，通?？梢允梗┦俏挥谶h(yuǎn)程服務(wù)器上的存儲(chǔ)過程，通?？梢允褂梅植际讲樵兒陀梅植际讲樵兒虴XECUTE命令執(zhí)行一個(gè)遠(yuǎn)程存儲(chǔ)過程。命令執(zhí)行一個(gè)遠(yuǎn)程存儲(chǔ)過程。如果想要執(zhí)行遠(yuǎn)程服務(wù)器的存儲(chǔ)過程，就必須將這兩臺(tái)服務(wù)

9、器如果想要執(zhí)行遠(yuǎn)程服務(wù)器的存儲(chǔ)過程，就必須將這兩臺(tái)服務(wù)器配置為彼此相互識(shí)別的遠(yuǎn)程服務(wù)器。然后，檢查本地和遠(yuǎn)程配置為彼此相互識(shí)別的遠(yuǎn)程服務(wù)器。然后，檢查本地和遠(yuǎn)程服務(wù)器上的【服務(wù)器屬性】對(duì)話框中的配置選項(xiàng)，驗(yàn)證這兩服務(wù)器上的【服務(wù)器屬性】對(duì)話框中的配置選項(xiàng)，驗(yàn)證這兩臺(tái)服務(wù)器的配置選項(xiàng)設(shè)置是否正確，使每個(gè)服務(wù)器都允許遠(yuǎn)臺(tái)服務(wù)器的配置選項(xiàng)設(shè)置是否正確，使每個(gè)服務(wù)器都允許遠(yuǎn)程用戶執(zhí)行過程調(diào)用。程用戶執(zhí)行過程調(diào)用。執(zhí)行遠(yuǎn)程存儲(chǔ)過程主要使用系統(tǒng)存儲(chǔ)過程執(zhí)行遠(yuǎn)程存儲(chǔ)過程主要使用系統(tǒng)存儲(chǔ)過程sp_addlinkedserver、sp_configure和和sp_serveroption。第十頁(yè)，共21頁(yè)。11

10、.2.5 11.2.5 擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程是用戶可以使用外部程序語(yǔ)言編寫的存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程是用戶可以使用外部程序語(yǔ)言編寫的存儲(chǔ)過程。顯而易見，通過擴(kuò)展存儲(chǔ)過程可以彌補(bǔ)。顯而易見，通過擴(kuò)展存儲(chǔ)過程可以彌補(bǔ)SQL Server 2008的不足，并按需要自行擴(kuò)展其功能。擴(kuò)展存儲(chǔ)過程的不足，并按需要自行擴(kuò)展其功能。擴(kuò)展存儲(chǔ)過程在使用和執(zhí)行上與一般的存儲(chǔ)過程完全相同。用戶可以在使用和執(zhí)行上與一般的存儲(chǔ)過程完全相同。用戶可以將參數(shù)傳遞給擴(kuò)展存儲(chǔ)過程，擴(kuò)展存儲(chǔ)過程也能夠返回將參數(shù)傳遞給擴(kuò)展存儲(chǔ)過程，擴(kuò)展存儲(chǔ)過程也能夠返回結(jié)果和狀態(tài)值。擴(kuò)展存儲(chǔ)過程的名稱通常以結(jié)果和狀態(tài)值。擴(kuò)展存儲(chǔ)過程的

11、名稱通常以xp_開頭。擴(kuò)開頭。擴(kuò)展存儲(chǔ)過程是以動(dòng)態(tài)鏈接（展存儲(chǔ)過程是以動(dòng)態(tài)鏈接（DLLS）的形式存在，能讓）的形式存在，能讓SQL Server 2008動(dòng)態(tài)地裝載和執(zhí)行。擴(kuò)展存儲(chǔ)過程一定動(dòng)態(tài)地裝載和執(zhí)行。擴(kuò)展存儲(chǔ)過程一定要存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)要存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)master中。中。第十一頁(yè)，共21頁(yè)。11.3 11.3 使用使用SQL Server 2008SQL Server 2008創(chuàng)建和執(zhí)行存儲(chǔ)過創(chuàng)建和執(zhí)行存儲(chǔ)過程程和數(shù)據(jù)庫(kù)、表、視圖的創(chuàng)建一樣，存儲(chǔ)過程的創(chuàng)建、執(zhí)行也和數(shù)據(jù)庫(kù)、表、視圖的創(chuàng)建一樣，存儲(chǔ)過程的創(chuàng)建、執(zhí)行也可以通過可以通過SQL Server Management Studio和

12、和T-SQL兩種兩種方式執(zhí)行，本節(jié)將對(duì)這兩種方式進(jìn)行介紹。方式執(zhí)行，本節(jié)將對(duì)這兩種方式進(jìn)行介紹。第十二頁(yè)，共21頁(yè)。11.3.1 11.3.1 使用使用SSMSSSMS創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 在【對(duì)象資源管理器】面板中右擊【在【對(duì)象資源管理器】面板中右擊【wj】|【可編程性】【可編程性】|【存儲(chǔ)過程】分支項(xiàng)，在彈出的快捷菜單中單擊【新建【存儲(chǔ)過程】分支項(xiàng)，在彈出的快捷菜單中單擊【新建存儲(chǔ)過程】命令，在右側(cè)的空白面板中彈出如圖存儲(chǔ)過程】命令，在右側(cè)的空白面板中彈出如圖11.3所示所示的存儲(chǔ)過程模板。的存儲(chǔ)過程模板。修改代碼，使存儲(chǔ)過程完成對(duì)表修改代碼，使存儲(chǔ)過程完成對(duì)表student中年齡為中

13、年齡為18歲的學(xué)歲的學(xué)生信息的查詢，如圖生信息的查詢，如圖11.4所示。所示。單擊菜單欄上的【執(zhí)行】按鈕，如圖單擊菜單欄上的【執(zhí)行】按鈕，如圖11.5所示，在【存儲(chǔ)過所示，在【存儲(chǔ)過程】分支項(xiàng)中顯示了新建的存儲(chǔ)過程分支項(xiàng)【程】分支項(xiàng)中顯示了新建的存儲(chǔ)過程分支項(xiàng)【dbo.p_student1】分支項(xiàng)，表明完成了存儲(chǔ)過程的創(chuàng)建?！糠种ы?xiàng)，表明完成了存儲(chǔ)過程的創(chuàng)建。第十三頁(yè)，共21頁(yè)。11.3.2 11.3.2 使用使用T-SQLT-SQL創(chuàng)建和執(zhí)行存儲(chǔ)過程創(chuàng)建和執(zhí)行存儲(chǔ)過程除了使用除了使用SQL Server Management Studio外，也可以使用外，也可以使用T-SQL代碼創(chuàng)建存儲(chǔ)過程

14、，相應(yīng)的語(yǔ)法代碼如下所示：代碼創(chuàng)建存儲(chǔ)過程，相應(yīng)的語(yǔ)法代碼如下所示：CREATE PROCEDURE procedure_name WITH ENCRYPTION WITH RECOMPILE AS Sql_statement第十四頁(yè)，共21頁(yè)。11.3.3 11.3.3 查看存儲(chǔ)過程的屬性查看存儲(chǔ)過程的屬性在存儲(chǔ)過程創(chuàng)建成功后，用戶可以在在存儲(chǔ)過程創(chuàng)建成功后，用戶可以在SQL Server Management Studio窗口中下查看存儲(chǔ)過程的屬性。在窗口中下查看存儲(chǔ)過程的屬性。在SQL Server Management Studio窗口中查看存儲(chǔ)過程窗口中查看存儲(chǔ)過程p_student

15、1的屬性。的屬性。在對(duì)象資源管理器面板中右擊【在對(duì)象資源管理器面板中右擊【wj】|【可編程性】【可編程性】|【存儲(chǔ)【存儲(chǔ)過程】過程】|【dbo.p_student2】分支，在彈出的快捷菜單中選】分支，在彈出的快捷菜單中選擇【屬性】命令，彈出如圖擇【屬性】命令，彈出如圖11.10對(duì)話框。對(duì)話框。在該對(duì)話框中，可以對(duì)存儲(chǔ)過程的屬性進(jìn)行修改，同樣，單在該對(duì)話框中，可以對(duì)存儲(chǔ)過程的屬性進(jìn)行修改，同樣，單擊【權(quán)限】標(biāo)簽頁(yè)，可以為存儲(chǔ)過程設(shè)置用戶和角色。擊【權(quán)限】標(biāo)簽頁(yè)，可以為存儲(chǔ)過程設(shè)置用戶和角色。同時(shí)，還可以對(duì)存儲(chǔ)過程的定義進(jìn)行修改，在對(duì)象資源同時(shí)，還可以對(duì)存儲(chǔ)過程的定義進(jìn)行修改，在對(duì)象資源管理器面板

16、中右擊【管理器面板中右擊【wj】|【可編程性】【可編程性】|【存儲(chǔ)過程】【存儲(chǔ)過程】|【dbo.p_student2】分支選項(xiàng)，在彈出的快捷菜單中單擊【】分支選項(xiàng)，在彈出的快捷菜單中單擊【修改】菜單項(xiàng)，在對(duì)象資源管理器右側(cè)的面板中給出修修改】菜單項(xiàng)，在對(duì)象資源管理器右側(cè)的面板中給出修改存儲(chǔ)過程定義的改存儲(chǔ)過程定義的T-SQL代碼。如圖代碼。如圖11.11所示，存儲(chǔ)過所示，存儲(chǔ)過程定義的修改是通過程定義的修改是通過ALTER PROCEDURE來完成。來完成。第十五頁(yè)，共21頁(yè)。11.3.4 11.3.4 帶參數(shù)的存儲(chǔ)過程帶參數(shù)的存儲(chǔ)過程由于視圖沒有提供參數(shù)，對(duì)于行的篩選只能綁定在視圖定義由于視

17、圖沒有提供參數(shù)，對(duì)于行的篩選只能綁定在視圖定義中，靈活性不大。而存儲(chǔ)過程提供了參數(shù)，大大提高了中，靈活性不大。而存儲(chǔ)過程提供了參數(shù)，大大提高了系統(tǒng)開發(fā)的靈活性。系統(tǒng)開發(fā)的靈活性。向存儲(chǔ)過程設(shè)定輸入、輸出參數(shù)的主要目的是通過參數(shù)向存向存儲(chǔ)過程設(shè)定輸入、輸出參數(shù)的主要目的是通過參數(shù)向存儲(chǔ)過程輸入和輸出信息來擴(kuò)展存儲(chǔ)過程的功能。通過設(shè)儲(chǔ)過程輸入和輸出信息來擴(kuò)展存儲(chǔ)過程的功能。通過設(shè)定參數(shù)，可以多次使用同一存儲(chǔ)過程并按用戶要求查找定參數(shù)，可以多次使用同一存儲(chǔ)過程并按用戶要求查找所需的結(jié)果。所需的結(jié)果。第十六頁(yè)，共21頁(yè)。11.4 11.4 使用使用SQL Server 2008SQL Server 2

18、008管理存儲(chǔ)過程管理存儲(chǔ)過程在完成存儲(chǔ)過程的創(chuàng)建后，可以通過在完成存儲(chǔ)過程的創(chuàng)建后，可以通過SQL Server 2008對(duì)存對(duì)存儲(chǔ)過程進(jìn)行管理，本節(jié)中將對(duì)此進(jìn)行詳細(xì)的學(xué)習(xí)。儲(chǔ)過程進(jìn)行管理，本節(jié)中將對(duì)此進(jìn)行詳細(xì)的學(xué)習(xí)。第十七頁(yè)，共21頁(yè)。11.4.1 11.4.1 使用使用SQL Server 2008SQL Server 2008修改存儲(chǔ)過程修改存儲(chǔ)過程修改存儲(chǔ)過程是由修改存儲(chǔ)過程是由ALTER語(yǔ)句來完成的，其語(yǔ)法如下：語(yǔ)句來完成的，其語(yǔ)法如下：ALTER PROCEDURE procedure_nameWITH ENCRYPTIONWITH RECOMPILEASSql_statement第十八頁(yè)，共21頁(yè)。11.4.2 11.4.2 使用使用SQL Server 2008SQL Server 2008刪除存儲(chǔ)過程刪除存儲(chǔ)過程存儲(chǔ)過程的刪除可以通過兩種方法來完成。存儲(chǔ)過程的刪除可以通過兩種方法來完成。1.使用使用SQL Server Management Studio刪除存儲(chǔ)過程刪除存儲(chǔ)過程2.使用使用T-SQL代碼刪除存儲(chǔ)過程代碼刪除存儲(chǔ)過程第十九頁(yè)，共21頁(yè)。11.4.3 11.4.3 使用