第13講存儲(chǔ)過程

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

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

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

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

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

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

7、參數(shù)可分存儲(chǔ)過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，參數(shù)可分為：輸入?yún)?shù)為：輸入?yún)?shù)(input)和輸出參數(shù)和輸出參數(shù)(output)。一個(gè)存儲(chǔ)過。一個(gè)存儲(chǔ)過程可以有多個(gè)參數(shù)，用程可以有多個(gè)參數(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ù)，先要定義一個(gè)存儲(chǔ)結(jié)果的參數(shù)，在對輸出參數(shù)，先要定義一個(gè)存儲(chǔ)結(jié)果的參數(shù)，在EXEC語句中用語句中用OUTPUT顯式聲明它為輸出項(xiàng)。顯式聲明它為輸出項(xiàng)。2022

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

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

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

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

12、號(hào)課程號(hào)=cidRETURN 0-值值0表示過程運(yùn)行沒有出錯(cuò)。表示過程運(yùn)行沒有出錯(cuò)。 2022-5-1115p執(zhí)行存儲(chǔ)過程，獲取狀態(tài)值舉例，接上片。執(zhí)行存儲(chǔ)過程，獲取狀態(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 沒有該課程沒有該課程

13、4. 綜合實(shí)例綜合實(shí)例同學(xué)們自己體會(huì)同學(xué)們自己體會(huì)2022-5-11172022-5-1118(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-5-1119（2） 創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表if Exists (Select * from sysobjects where name=系表系表)DROP

14、 TABLE 系表系表goCREATE 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é)期編號(hào)學(xué)期編號(hào) 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é)期

15、第二學(xué)期 ),CHECK (學(xué)年學(xué)年 Like 0-90-90-90-9-0-90-90-90-9%)go2022-5-1120(3) 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程-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 系表系表EXE

16、C proc_AddDepart 網(wǎng)絡(luò)技術(shù)系網(wǎng)絡(luò)技術(shù)系,綜合樓四樓綜合樓四樓EXEC Proc_AddDepart 計(jì)算機(jī)系計(jì)算機(jī)系,綜合樓二樓綜合樓二樓EXEC Proc_AddDepart 管理系管理系,綜合樓三樓綜合樓三樓goEXEC proc_initDeptDatago2022-5-11213) 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程-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 (yea

17、r,term)goCreate 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 2010