第13講存儲過程

1、2022-6-81回顧回顧用約束來強制數(shù)據(jù)完整性用約束來強制數(shù)據(jù)完整性主鍵約束主鍵約束外鍵約束外鍵約束關(guān)系圖，級聯(lián)更新，級聯(lián)刪除。關(guān)系圖，級聯(lián)更新，級聯(lián)刪除。唯一約束唯一約束檢查約束檢查約束默認約束默認約束非空約束非空約束數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫原理與應(yīng)用第第13講講 存儲過程存儲過程2022-6-83提綱提綱重點重點對存儲過程優(yōu)點的理解對存儲過程優(yōu)點的理解創(chuàng)建和執(zhí)行存儲過程的方法創(chuàng)建和執(zhí)行存儲過程的方法難點難點存儲過程的輸入?yún)?shù)、輸出參數(shù)和狀態(tài)值存儲過程的輸入?yún)?shù)、輸出參數(shù)和狀態(tài)值1. 存儲過程存儲過程關(guān)鍵詞：預(yù)定義的，被存儲的，關(guān)鍵詞：預(yù)定義的，被存儲的，SQL批處理。批處理。2022-6-

2、85(1) 存儲過程的概念存儲過程的概念是一組預(yù)編譯的是一組預(yù)編譯的Transact-SQL語句，用于完成某項任務(wù)，語句，用于完成某項任務(wù)，它可以它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以，并且可以嵌嵌套套調(diào)用。調(diào)用。(2) 存儲過程的優(yōu)點存儲過程的優(yōu)點存儲過程存儲過程 ，經(jīng)編譯后存放在數(shù)據(jù)庫服務(wù)器端，供客戶端調(diào)用，經(jīng)編譯后存放在數(shù)據(jù)庫服務(wù)器端，供客戶端調(diào)用，因此存儲過程可以充分利用服務(wù)器的高性能運算能力，而因此存儲過程可以充分利用服務(wù)器的高性能運算能力，而無須把大量的結(jié)果集傳送到客戶處理，減少網(wǎng)絡(luò)傳輸開銷。無須把大量的結(jié)果集傳送到客戶處理，減少網(wǎng)絡(luò)傳輸開銷。(1

3、)模塊化編程。模塊化編程。(2)快速執(zhí)行。快速執(zhí)行。 (3)減少網(wǎng)絡(luò)通信量。減少網(wǎng)絡(luò)通信量。(4)提供安全機制。提供安全機制。(5)保證操作一致性。保證操作一致性。2022-6-86(3) SQL Server存儲過程的類型：存儲過程的類型：p系統(tǒng)存儲過程系統(tǒng)存儲過程由系統(tǒng)提供的存儲過程，主要存儲在由系統(tǒng)提供的存儲過程，主要存儲在master數(shù)據(jù)庫中并以數(shù)據(jù)庫中并以sp_為前綴為前綴如：如：sp_renamedb NewSales,sales sp_helpdb 數(shù)據(jù)庫名數(shù)據(jù)庫名-查看數(shù)據(jù)庫列表查看數(shù)據(jù)庫列表 p用戶定義存儲過程用戶定義存儲過程由用戶創(chuàng)建并能完成某一特定功能由用戶創(chuàng)建并能完成某

4、一特定功能(例如查詢用戶所需數(shù)據(jù)信例如查詢用戶所需數(shù)據(jù)信息息)的存儲過程。它處于用戶創(chuàng)建的數(shù)據(jù)庫中的存儲過程。它處于用戶創(chuàng)建的數(shù)據(jù)庫中p臨時存儲過程臨時存儲過程在一個在一個SQL SERVER實例中有效，重啟后會消失。實例中有效，重啟后會消失。p擴展存儲過程。擴展存儲過程。擴展存儲過程是擴展存儲過程是SQL Server可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接庫可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接庫(DLL)。當擴展存儲過程加載到當擴展存儲過程加載到SQL Server中，它的使用方法中，它的使用方法與系統(tǒng)存儲過程一樣與系統(tǒng)存儲過程一樣。擴展存儲過程只能添加到。擴展存儲過程只能添加到master數(shù)據(jù)庫數(shù)據(jù)庫中，其

5、前綴是中，其前綴是xp_。2022-6-87(4) 存儲過程的創(chuàng)建、修改、刪除與執(zhí)行存儲過程的創(chuàng)建、修改、刪除與執(zhí)行n創(chuàng)建創(chuàng)建 CREATE PROC 存儲過程名存儲過程名 參數(shù)表參數(shù)表With RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS SQL STATEMENT其中：其中：RECOMPILE-執(zhí)行時重新編譯，執(zhí)行時重新編譯，ENCRPTION-對存儲對存儲過程腳本加密。過程腳本加密。p執(zhí)行執(zhí)行 EXECUTE 存儲過程名存儲過程名 參數(shù)表參數(shù)表p修改存儲過程修改存儲過程 ALTER PROC 存儲過程名存儲過程名p刪除存儲過程刪除存儲過程DRO

6、P PROC 存儲過程名存儲過程名2. 存儲過程創(chuàng)建與執(zhí)行示例存儲過程創(chuàng)建與執(zhí)行示例關(guān)鍵詞：關(guān)鍵詞：PROC，OUTPUT2022-6-89學(xué)號學(xué)號課程號課程號成績成績學(xué)號學(xué)號姓名姓名性別性別身份證號身份證號生日生日課程號課程號課程名課程名學(xué)分學(xué)分學(xué)生表學(xué)生表課程表課程表成績表成績表(1)示例數(shù)據(jù)庫示例數(shù)據(jù)庫Teach數(shù)據(jù)庫包含三個表，表結(jié)構(gòu)如下：數(shù)據(jù)庫包含三個表，表結(jié)構(gòu)如下：例例2022-6-81010.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用(1) 無參數(shù)的存儲過程。無參數(shù)的存儲過程。(2)帶參數(shù)的存儲過程帶參數(shù)的存儲過程p存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，參數(shù)可分存儲過

7、程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，參數(shù)可分為：輸入?yún)?shù)為：輸入?yún)?shù)(input)和輸出參數(shù)和輸出參數(shù)(output)。一個存儲過。一個存儲過程可以有多個參數(shù)，用程可以有多個參數(shù)，用“,”分隔。分隔。p輸入?yún)?shù)的定義格式：輸入?yún)?shù)的定義格式：參數(shù)名參數(shù)名 類型類型 p輸出參數(shù)的定義格式：輸出參數(shù)的定義格式：參數(shù)名參數(shù)名 類型類型 outputp執(zhí)行執(zhí)行對輸入?yún)?shù)，在對輸入?yún)?shù)，在EXEC語句中用常量傳值語句中用常量傳值對輸出參數(shù)，先要定義一個存儲結(jié)果的參數(shù)，在對輸出參數(shù)，先要定義一個存儲結(jié)果的參數(shù)，在EXEC語句中用語句中用OUTPUT顯式聲明它為輸出項。顯式聲明它為輸出項。2022-6-811課

8、堂練習：課堂練習：寫存儲過程寫存儲過程proc_AddCourse，用于向課程表增加新行，用于向課程表增加新行（用參數(shù)表示數(shù)據(jù)值（用參數(shù)表示數(shù)據(jù)值)寫存儲過程寫存儲過程Proc_ModifyCourse，用于修改一個給定了，用于修改一個給定了課程號的課程的課程名和學(xué)分。課程號的課程的課程名和學(xué)分。課程號課程號課程名課程名學(xué)分學(xué)分課程表課程表3. 存儲過程的執(zhí)行狀態(tài)存儲過程的執(zhí)行狀態(tài)關(guān)鍵詞：狀態(tài)值是一個整數(shù)，關(guān)鍵詞：狀態(tài)值是一個整數(shù)，0表示成功，其他的都表示成功，其他的都用來表示執(zhí)行錯誤。用來表示執(zhí)行錯誤。2022-6-81310.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值（

9、1）存儲過程的狀態(tài)值）存儲過程的狀態(tài)值p狀態(tài)值表示過程是否成功執(zhí)行，或者過程失敗的原因。狀態(tài)值表示過程是否成功執(zhí)行，或者過程失敗的原因。p狀態(tài)值是一個整數(shù)。狀態(tài)值是一個整數(shù)。p當存儲過程成功執(zhí)行時，狀態(tài)值當存儲過程成功執(zhí)行時，狀態(tài)值 0；當執(zhí)行中發(fā)生錯誤，；當執(zhí)行中發(fā)生錯誤，則會返回一個則會返回一個“-99 -1”之間的整數(shù)。這一功能是由系之間的整數(shù)。這一功能是由系統(tǒng)提供的，通常用來捕捉錯誤原因。統(tǒng)提供的，通常用來捕捉錯誤原因。p用戶也可以在存儲過程中自定義返回狀態(tài)值，自定義用戶也可以在存儲過程中自定義返回狀態(tài)值，自定義狀態(tài)值是狀態(tài)值是大于大于0或小于或小于-99的整數(shù)。用的整數(shù)。用RETUR

10、N返回狀返回狀態(tài)值。這種做法通常用來處理可預(yù)見的不正確執(zhí)行方態(tài)值。這種做法通常用來處理可預(yù)見的不正確執(zhí)行方式。式。p在執(zhí)行存儲過程時，可以用變量來存儲狀態(tài)值。格式：在執(zhí)行存儲過程時，可以用變量來存儲狀態(tài)值。格式：EXECUTE status_var=procedure_name2022-6-81410.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值p自定義狀態(tài)值舉例：創(chuàng)建存儲過程，輸入學(xué)號和課程自定義狀態(tài)值舉例：創(chuàng)建存儲過程，輸入學(xué)號和課程號，返回成績。號，返回成績。CREATE PROC proc_GetScoreBySidAndCidsid char(10)=NULL,ci

11、d char(6)=NULLASIF sid=NULL or cid=NULL RETURN 15 -用值用值15表示用戶沒有提供參數(shù)表示用戶沒有提供參數(shù)IF NOT EXISTS (SELECT * FROM 學(xué)生表學(xué)生表 WHERE 學(xué)號學(xué)號=sid) RETURN -101 -值值-l01表示沒有該學(xué)生表示沒有該學(xué)生IF NOT EXISTS (SELECT * FROM 課程表課程表 WHERE 課程號課程號=cid) RETURN -102 -值值-102表示沒有該課程表示沒有該課程SELECT * FROM 成績表成績表WHERE 學(xué)號學(xué)號=sid AND 課程號課程號=cidRE

12、TURN 0-值值0表示過程運行沒有出錯。表示過程運行沒有出錯。 2022-6-815p執(zhí)行存儲過程，獲取狀態(tài)值舉例，接上片。執(zhí)行存儲過程，獲取狀態(tài)值舉例，接上片。DECLARE return_status intEXEC return_status=proc_GetScoreBySidAndCid 001, NN0001IF return_status=15 SELECT 沒有提供參數(shù)沒有提供參數(shù)ELSE IF return_status=-101 SELECT 沒有該學(xué)生沒有該學(xué)生ELSE IF return_status=-102 SELECT 沒有該課程沒有該課程4. 綜合實例綜合實例

13、同學(xué)們自己體會同學(xué)們自己體會2022-6-8172022-6-818(1)創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫-創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫Create Database DBTEACHON PRIMARY(NAME=Teach_dat, FILENAME=D:YZDBTEACH.mdf)LOG ON(NAME=Teach_log,FILENAME=D:YZDBTEACH.ldf)go-打開數(shù)據(jù)庫打開數(shù)據(jù)庫use DBteachgo2022-6-819（2） 創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表if Exists (Select * from sysobjects where name=系表系表)DROP TABLE 系表系表goC

14、REATE TABLE 系表系表(系名系名 nchar(10) PRIMARY KEY,辦公地址辦公地址 nchar(20) NOT NULL)goif EXISTS (SELECT * FROM SYSOBJECTS WHERE name=學(xué)期表學(xué)期表)DROP TABLE 學(xué)期表學(xué)期表goCREATE TABLE 學(xué)期表學(xué)期表(學(xué)期編號學(xué)期編號 int identity(1,1) PRIMARY KEY,學(xué)年學(xué)年 nchar(12) NOT NULL,學(xué)期學(xué)期 nchar(4) NOT NULL,CHECK (學(xué)期學(xué)期=第一學(xué)期第一學(xué)期 or 學(xué)期學(xué)期=第二學(xué)期第二學(xué)期 ),CHECK (

15、學(xué)年學(xué)年 Like 0-90-90-90-9-0-90-90-90-9%)go2022-6-820(3) 創(chuàng)建存儲過程創(chuàng)建存儲過程-1-proc_AddDepart添加系表數(shù)據(jù)添加系表數(shù)據(jù)CREATE PROCEDURE proc_AddDepartdeptname nchar(10),Address nchar(10)ASINSERT INTO 系表系表(系名系名,辦公地址辦公地址)VALUES(deptname,Address)go-初始化系表數(shù)據(jù)初始化系表數(shù)據(jù)CREATE PROCEDURE proc_initDeptDataASDELETE 系表系表EXEC proc_AddDepar

16、t 網(wǎng)絡(luò)技術(shù)系網(wǎng)絡(luò)技術(shù)系,綜合樓四樓綜合樓四樓EXEC Proc_AddDepart 計算機系計算機系,綜合樓二樓綜合樓二樓EXEC Proc_AddDepart 管理系管理系,綜合樓三樓綜合樓三樓goEXEC proc_initDeptDatago2022-6-8213) 創(chuàng)建存儲過程創(chuàng)建存儲過程-2-Proc_AddTerm:添加學(xué)期添加學(xué)期CREATE PROCEDURE proc_AddTerm -添加學(xué)期添加學(xué)期year nchar(12),term nchar(4)asINSERT INTO 學(xué)期表學(xué)期表(學(xué)年學(xué)年,學(xué)期學(xué)期) VALUES (year,term)goCreate

17、PROCEDURE proc_InitTermData-學(xué)期表初始數(shù)據(jù)學(xué)期表初始數(shù)據(jù)ASDELETE 學(xué)期表學(xué)期表EXEC proc_AddTerm 2007-2008,第一學(xué)期第一學(xué)期EXEC proc_AddTerm 2007-2008,第二學(xué)期第二學(xué)期EXEC proc_AddTerm 2008-2009,第一學(xué)期第一學(xué)期EXEC proc_AddTerm 2008-2009,第二學(xué)期第二學(xué)期EXEC proc_AddTerm 2009-2010,第一學(xué)期第一學(xué)期EXEC proc_AddTerm 2009-2010,第二學(xué)期第二學(xué)期EXEC proc_AddTerm 2010-2011,第一學(xué)期第一學(xué)期EXEC proc_AddTerm 201