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

2、以特定的名稱存儲在數據庫中。存儲過程也是一種數據以特定的名稱存儲在數據庫中。存儲過程也是一種數據庫對象。存儲過程具有實現聲明變量、條件執行以及其庫對象。存儲過程具有實現聲明變量、條件執行以及其他各項強大的程序設計功能。他各項強大的程序設計功能。第三頁，共21頁。11.1.2 11.1.2 存儲過程的優點存儲過程的優點當利用當利用MS SQL Server創建一個應用程序時，創建一個應用程序時，Transaction-SQL是一種主要的編程語言。若運用是一種主要的編程語言。若運用Transaction-SQL來來進行編程，有兩種方法。進行編程，有兩種方法。在本地存儲在本地存儲Transactio

3、n-SQL程序，并創建應用程序向程序，并創建應用程序向SQL Server發送命令來對結果進行處理。發送命令來對結果進行處理。把部分用把部分用Transaction-SQL編寫的程序作為存儲過程存儲在編寫的程序作為存儲過程存儲在SQL Server中，并創建應用程序來調用存儲過程，對數中，并創建應用程序來調用存儲過程，對數據結果進行處理。存儲過程能夠通過接收參數向調用者據結果進行處理。存儲過程能夠通過接收參數向調用者返回結果集，結果集的格式由調用者確定；返回狀態值返回結果集，結果集的格式由調用者確定；返回狀態值給調用者，指明調用是成功或是失敗；并可以在一個存給調用者，指明調用是成功或是失敗；并

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

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

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

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

8、問題，臨時存儲過程（Temporary Stored Procedures）可分為以下兩種：）可分為以下兩種：1.本地臨時存儲過程本地臨時存儲過程2.全局臨時存儲過程全局臨時存儲過程第九頁，共21頁。11.2.4 11.2.4 遠程存儲過程遠程存儲過程在在SQL Server 2008中，遠程存儲過程（中，遠程存儲過程（Remote Stored Procedures）是位于遠程服務器上的存儲過程，通常可以使）是位于遠程服務器上的存儲過程，通常可以使用分布式查詢和用分布式查詢和EXECUTE命令執行一個遠程存儲過程。命令執行一個遠程存儲過程。如果想要執行遠程服務器的存儲過程，就必須將這兩臺服務

9、器如果想要執行遠程服務器的存儲過程，就必須將這兩臺服務器配置為彼此相互識別的遠程服務器。然后，檢查本地和遠程配置為彼此相互識別的遠程服務器。然后，檢查本地和遠程服務器上的【服務器屬性】對話框中的配置選項，驗證這兩服務器上的【服務器屬性】對話框中的配置選項，驗證這兩臺服務器的配置選項設置是否正確，使每個服務器都允許遠臺服務器的配置選項設置是否正確，使每個服務器都允許遠程用戶執行過程調用。程用戶執行過程調用。執行遠程存儲過程主要使用系統存儲過程執行遠程存儲過程主要使用系統存儲過程sp_addlinkedserver、sp_configure和和sp_serveroption。第十頁，共21頁。11

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

11、名稱通常以xp_開頭。擴開頭。擴展存儲過程是以動態鏈接（展存儲過程是以動態鏈接（DLLS）的形式存在，能讓）的形式存在，能讓SQL Server 2008動態地裝載和執行。擴展存儲過程一定動態地裝載和執行。擴展存儲過程一定要存儲在系統數據庫要存儲在系統數據庫master中。中。第十一頁，共21頁。11.3 11.3 使用使用SQL Server 2008SQL Server 2008創建和執行存儲過創建和執行存儲過程程和數據庫、表、視圖的創建一樣，存儲過程的創建、執行也和數據庫、表、視圖的創建一樣，存儲過程的創建、執行也可以通過可以通過SQL Server Management Studio和

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

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

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

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

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

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

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