數(shù)據(jù)庫(kù)課件補(bǔ)充存儲(chǔ)過程

1、存儲(chǔ)過程存儲(chǔ)過程的概念創(chuàng)建與執(zhí)行 存儲(chǔ)過程的操作存儲(chǔ)過程的概念一、存儲(chǔ)過程 存儲(chǔ)過程是由一些SQL語(yǔ)句和控制語(yǔ)句組成的被封裝起來(lái)的過程，它駐留在數(shù)據(jù)庫(kù)中。 可以被客戶應(yīng)用程序調(diào)用，也可以從另一個(gè)過程或觸發(fā)器調(diào)用。它的參數(shù)可以被傳遞和返回。與應(yīng)用程序中的函數(shù)過程類似，存儲(chǔ)過程可以通過名字來(lái)調(diào)用，而且它們同樣有輸入?yún)?shù)和輸出參數(shù)。 在SQL Server中存儲(chǔ)過程分為兩類：即系統(tǒng)提供的存儲(chǔ)過程和用戶自定義的存儲(chǔ)過程。 系統(tǒng)存儲(chǔ)過程 由系統(tǒng)提供的存儲(chǔ)過程，可以作為命令執(zhí)行各種操作。 系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫(kù)master中，其前綴是sp_。用戶存儲(chǔ)過程 在用戶數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過程，用來(lái)完成用戶指

2、定的數(shù)據(jù)庫(kù)操作。 創(chuàng)建與執(zhí)行一、創(chuàng)建 可以使用三種方法創(chuàng)建存儲(chǔ)過程 ： 1、使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程。 2、利用SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過程。 3、使用Transact-SQL語(yǔ)句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過程。1、 使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程 在企業(yè)管理器中，選擇工具菜單中的向?qū)нx項(xiàng)，選擇“創(chuàng)建存儲(chǔ)過程向?qū)А?，則出現(xiàn)歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框。根據(jù)以下各圖提示可完成創(chuàng)建存儲(chǔ)過程。圖10-1 新建SQL Server組圖10-2 歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框圖10-3 選擇數(shù)據(jù)庫(kù)對(duì)話框 圖10-4 選擇數(shù)據(jù)庫(kù)對(duì)象對(duì)話框圖10-5 完成

3、創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框圖10-6 編輯存儲(chǔ)過程屬性對(duì)話框圖10-7 編輯存儲(chǔ)過程SQL對(duì)話框2、. 使用企業(yè)管理器創(chuàng)建存儲(chǔ)過程 、在SQL Server企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫(kù)，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇“新建”選項(xiàng)，再選擇下一級(jí)菜單中的“存儲(chǔ)過程”選項(xiàng)，或者用右鍵單擊存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過程”選項(xiàng)，均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程對(duì)話框。 、在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的SQL語(yǔ)句，單擊“檢查語(yǔ)法”，則可以檢查語(yǔ)法是否正確；單擊“確定”按鈕，即可保存該存儲(chǔ)過程。如果要設(shè)置權(quán)限，單擊“權(quán)限”按鈕。圖10-8 選擇新建存儲(chǔ)過程對(duì)話框（1

4、）圖10-9 選擇新建存儲(chǔ)過程對(duì)話框（2）圖10-10 新建存儲(chǔ)過程對(duì)話框圖10-11 設(shè)置權(quán)限對(duì)話框3、 使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過程命令格式：CREATE PROCEDURE 存儲(chǔ)過程名 參數(shù) 數(shù)據(jù)類型 長(zhǎng)度 參數(shù) 數(shù)據(jù)類型 長(zhǎng)度 OUTPUTAS SQL語(yǔ)句 二、執(zhí)行命令格式：EXEC UTE 存儲(chǔ)過程名 參數(shù)名= 參數(shù)值 參數(shù)值1，參數(shù)值2，根據(jù)返回值類型的不同，我們可以將存儲(chǔ)過程分為三類：1）返回記錄集的存儲(chǔ)過程。返回記錄集的存儲(chǔ)過程的執(zhí)行結(jié)果是一個(gè)記錄集，典型的例子是從數(shù)據(jù)庫(kù)中檢索出符合某一個(gè)或幾個(gè)條件的記錄。2) 返回?cái)?shù)值的存儲(chǔ)過程（也可以稱為標(biāo)量存儲(chǔ)過程）。返回?cái)?shù)值的存儲(chǔ)過程

5、執(zhí)行完以后返回一個(gè)值，例如在數(shù)據(jù)庫(kù)中執(zhí)行一個(gè)有返回值的函數(shù)或命令；3) 行為存儲(chǔ)過程。僅僅是用來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)的某個(gè)功能，而沒有返回值，例如在數(shù)據(jù)庫(kù)中的更新和刪除操作。 例1、在XK數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為P_Student的存儲(chǔ)過程，該存儲(chǔ)過程返回學(xué)生表中所有班級(jí)代碼為20000001的記錄。 USE Xk GO CREATE PROCEDURE p_Student AS SELECT * FROM Student WHERE ClassNo=20000001只返回單一記錄集的存儲(chǔ)過程例1的執(zhí)行 USE Xk GO EXEC p_Student例2、在XK數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為P_StudentPar

6、a的存儲(chǔ)過程，該存儲(chǔ)過程根據(jù)指定的班級(jí)返回該班級(jí)代碼對(duì)應(yīng)的Student表中的記錄 CREATE PROCEDURE p_StudentPara ClassNo VARCHAR(8) AS SELECT * FROM Student WHERE ClassNo=ClassNo向存儲(chǔ)過程中傳遞參數(shù)例2的執(zhí)行 EXEC p_StudentPara 20000002 GO EXEC p_StudentPara ClassNo=20000001 GO 例3、創(chuàng)建存儲(chǔ)過程P_ClassNum,要求能根據(jù)用戶給定的班級(jí)代碼，統(tǒng)計(jì)該班的人數(shù)，并將人數(shù)以輸出變量返回給用戶。 CREATE PROCEDURE

7、p_ClassNum ClassNo VARCHAR(8),ClassNum SMALLINT OUTPUT AS SET ClassNum= ( SELECT COUNT(*) FROM Student WHERE ClassNo=ClassNo ) GO 例3的執(zhí)行DECLARE ClassNo VARCHAR(8),ClassNum SMALLINTSET ClassNo=20000001EXEC p_ClassNum ClassNo,ClassNumPRINT ClassNum存儲(chǔ)過程的操作一、查看存儲(chǔ)過程 存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存

8、放在系統(tǒng)表 ments中?？梢允褂檬褂闷髽I(yè)管理器或系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程。2、使用系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程 sp_help objname=存儲(chǔ)過程名 顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型 sp_helptext objname=存儲(chǔ)過程名 顯示存儲(chǔ)過程的源代碼二、 修改存儲(chǔ)過程命令格式： ALTER PROCEDURE 存儲(chǔ)過程名 參數(shù) 數(shù)據(jù)類型 長(zhǎng)度 參數(shù) 數(shù)據(jù)類型 長(zhǎng)度 OUTPUT AS SQL語(yǔ)句1、使用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過程 在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫(kù)，單擊存儲(chǔ)過程文件夾，此時(shí)在右邊的頁(yè)框中顯示該數(shù)據(jù)庫(kù)的所有存儲(chǔ)過程。用右鍵單擊要查看的存儲(chǔ)過程，從彈出的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便可以看到存儲(chǔ)過程的源代碼。 例：修改前邊提到的例2ALTER PROCEDURE p_StudentParaClassName VARCHAR(20) ASSELECT ClassName,StuNo,StuName,Pwd FROM Student,ClassWHERE Student.ClassNo=Class.ClassNo AND ClassName LIKE %+ClassName+%三、重命名存儲(chǔ)過程1、企業(yè)管理器 2、命令方式：sp