1、SQL Server 2005數(shù)據(jù)庫(kù)應(yīng)用技術(shù)（第二版）,第10章 存儲(chǔ)過(guò)程與觸發(fā)器（1）,復(fù)習(xí),視圖簡(jiǎn)介 創(chuàng)建和管理視圖 創(chuàng)建視圖(視圖加密、架構(gòu)綁定的目的) 如何獲得視圖的有關(guān)信息 修改與刪除視圖 使用視圖修改數(shù)據(jù) 使用視圖優(yōu)化性能 索引視圖 分區(qū)視圖,本次課的學(xué)習(xí)內(nèi)容,理解存儲(chǔ)過(guò)程,了解存儲(chǔ)過(guò)程的用途 創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法與示例(了解輸入與輸出參數(shù)的使用) 存儲(chǔ)過(guò)程的更改與刪除 執(zhí)行存儲(chǔ)過(guò)程的方式,10-1 存儲(chǔ)過(guò)程,導(dǎo)入: 為了查詢(xún)XS數(shù)據(jù)庫(kù)中信息系的學(xué)生所修總學(xué)分信息，可以使用下列查詢(xún)： USE XS SELECT 學(xué)號(hào)，姓名，系名，總學(xué)分 FROM XSDA WHERE 系名=信息 O

2、RDER BY 總學(xué)分 DESC 問(wèn)題: 如果網(wǎng)絡(luò)上有5000個(gè)用戶(hù)執(zhí)行同一查詢(xún) ,結(jié)果會(huì)怎樣? 解決方法: 將代碼以一定的形式存放在服務(wù)器中，而不是存儲(chǔ)在客戶(hù)機(jī)上 。,10-1-1 存儲(chǔ)過(guò)程基礎(chǔ) 存儲(chǔ)過(guò)程就是在SQL Server數(shù)據(jù)庫(kù)中存放的查詢(xún)，是存儲(chǔ)在服務(wù)器中的一組預(yù)編譯過(guò)的T-SQL語(yǔ)句，而不是在客戶(hù)機(jī)上的前端代碼中存放的查詢(xún)。 1.存儲(chǔ)過(guò)程的優(yōu)點(diǎn) 存儲(chǔ)過(guò)程除減少網(wǎng)絡(luò)通信流之外，還有如下優(yōu)點(diǎn)： （1）存儲(chǔ)過(guò)程在服務(wù)器端運(yùn)行，執(zhí)行速度快。 （2）存儲(chǔ)過(guò)程是預(yù)編譯過(guò)的，當(dāng)?shù)谝淮握{(diào)用以后，就駐留在內(nèi)存中，以后調(diào)用時(shí)不必再進(jìn)行編譯，因此，它的運(yùn)行速度比獨(dú)立運(yùn)行同樣的程序要快。,10-1 存儲(chǔ)

3、過(guò)程,優(yōu)點(diǎn)： （3）簡(jiǎn)化數(shù)據(jù)庫(kù)管理。例如，如果需要修改現(xiàn)有查詢(xún)，而查詢(xún)存放在用戶(hù)機(jī)器上，則要在所有的用戶(hù)機(jī)器上進(jìn)行修改。而如果在服務(wù)器中集中存放查詢(xún)并作為存儲(chǔ)過(guò)程，則只需要在服務(wù)器上改變一次。 （4）存儲(chǔ)過(guò)程可以接受參數(shù)。允許零個(gè)或多個(gè)輸入?yún)?shù)，也允許零個(gè)或多個(gè)輸出參數(shù)。 （5）可作為安全機(jī)制使用。即使無(wú)對(duì)應(yīng)權(quán)限的用戶(hù)，也可授予權(quán)限執(zhí)行存儲(chǔ)過(guò)程。 注意：并不是每一個(gè)發(fā)往服務(wù)器的查詢(xún)都要使用存儲(chǔ)過(guò)程，只有用戶(hù)經(jīng)常運(yùn)行這個(gè)查詢(xún)，才應(yīng)考慮將該查詢(xún)生成為存儲(chǔ)過(guò)程。,10-1 存儲(chǔ)過(guò)程,2.存儲(chǔ)過(guò)程的分類(lèi) ： 按編寫(xiě)的語(yǔ)言來(lái)分：T-SQL存儲(chǔ)過(guò)程與CLR(公共語(yǔ)言運(yùn)行)存儲(chǔ)過(guò)程。 T-SQL 存儲(chǔ)過(guò)程又

4、可分為： 用戶(hù)定義存儲(chǔ)過(guò)程 系統(tǒng)存儲(chǔ)過(guò)程。,10-1 存儲(chǔ)過(guò)程,10-1-2 創(chuàng)建存儲(chǔ)過(guò)程 定義一個(gè)存儲(chǔ)過(guò)程時(shí)，首先要確定以下幾點(diǎn)： （1）存儲(chǔ)過(guò)程的名字。 （2）該存儲(chǔ)過(guò)程所要完成的功能，這是一個(gè)存儲(chǔ)過(guò)程的主體。 （3）是否需要輸入?yún)?shù)，是否需要輸出參數(shù)來(lái)輸出結(jié)果。,10-1 存儲(chǔ)過(guò)程,10-1-2 創(chuàng)建存儲(chǔ)過(guò)程 創(chuàng)建存儲(chǔ)過(guò)程的方法有: 1.使用模板創(chuàng)建存儲(chǔ)過(guò)程: SSMS/VIEW/模板資源管理器/存儲(chǔ)器/創(chuàng)建存儲(chǔ)過(guò)程 2.用SSMS管理平臺(tái)創(chuàng)建 對(duì)象資源管理器/數(shù)據(jù)庫(kù)/可編程性/存儲(chǔ)過(guò)程/新建存儲(chǔ)過(guò)程,10-1 存儲(chǔ)過(guò)程,10-1-2 創(chuàng)建存儲(chǔ)過(guò)程 3. 用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程 語(yǔ)

5、法格式： CREATE PROCEDURESchema_name. procedure_name parameter data_type =defaultOUTPUT, WITH AS sql_statement 注: WITH 各個(gè)選項(xiàng)請(qǐng)?jiān)斠?jiàn)P201,10-1 存儲(chǔ)過(guò)程,說(shuō)明： （1）用戶(hù)自定義存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建。 （2）=default用于指定存儲(chǔ)過(guò)程輸入?yún)?shù)的默認(rèn)值。默認(rèn)值必須是常量或NULL，可以包含通配符%、_、和。如果在定義時(shí)帶了“=default”參數(shù)，則執(zhí)行存儲(chǔ)過(guò)程時(shí)根據(jù)情況可不提供實(shí)參。 （3）在定義用戶(hù)存儲(chǔ)過(guò)程時(shí)，不能使用如下對(duì)象創(chuàng)建語(yǔ)句 CREATE VIEW

6、、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER （4）CREATE PROCEDURE必須是批處理中的第一條語(yǔ)句。存儲(chǔ)過(guò)程的定義也不能跨越批處理。 （5）用相應(yīng)架構(gòu)名稱(chēng)限定存儲(chǔ)過(guò)程所引用的對(duì)象名稱(chēng)，從而確保從存儲(chǔ)過(guò)程中訪問(wèn)來(lái)自不同架構(gòu)的表、視圖或其它對(duì)象。,10-1 存儲(chǔ)過(guò)程,創(chuàng)建存儲(chǔ)過(guò)程示例,1、創(chuàng)建簡(jiǎn)單的存儲(chǔ)過(guò)程 例： USE XS GO IF OBJECT_ID ( dbo.Getxsda, P ) IS NOT NULL DROP PROCEDURE dbo.Getxsda GO CREATE PROCEDURE

7、dbo.Getxsda AS SELECT * FROM XSDA GO -DECLAREreturn_value int -EXECreturn_value = dbo.GetXSDA -SELECTReturn Value = return_value,創(chuàng)建存儲(chǔ)過(guò)程示例,1、創(chuàng)建簡(jiǎn)單的存儲(chǔ)過(guò)程 又例：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程jsjzf_p，用于查詢(xún)信息系學(xué)生總學(xué)分的排名情況（從高到低）。 USE XS GO if exists(select * from sys.objects where name=jsjzf_pand type=P) DROP PROC jsjzf_p GO CREATE PR

8、OCEDURE jsjzf_p AS SELECT 學(xué)號(hào),姓名,系名,總學(xué)分 FROM XSDA WHERE 系名=信息 ORDER BY 總學(xué)分 DESC,創(chuàng)建存儲(chǔ)過(guò)程示例,2、創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程 例：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用于根據(jù)系名返回XSDA的數(shù)據(jù)，系名稱(chēng)條件使用輸入?yún)?shù)。 IF OBJECT_ID ( dbo.GetXSDA2, P ) IS NOT NULL DROP PROCEDURE dbo.GetXSDA2 GO CREATE PROCEDURE GetXSDA2 xiname varchar(20) AS SELECT * FROM XSDA WHERE 系名 = xin

9、ame GO -exec GetXSDA2 信息 -exec GetXSDA2 xiname=信息 -GetXSDA2 信息,創(chuàng)建存儲(chǔ)過(guò)程示例,又例：使用輸入?yún)?shù)，創(chuàng)建存儲(chǔ)過(guò)程grkccj_p，查詢(xún)某人指定課程的成績(jī)和學(xué)分。 if object_id(dbo.grkccj_p,P) is not null drop proc dbo.grkccj_p go CREATE PROCEDURE dbo.grkccj_p name char(8),kcname char(20) AS SELECT xsda.學(xué)號(hào),姓名,課程名稱(chēng),成績(jī),學(xué)分FROM XSDA,XSCJ,KCXX WHERE XSDA

10、.學(xué)號(hào)=XSCJ.學(xué)號(hào)AND XSCJ.課程編號(hào)=KCXX.課程編號(hào) AND 姓名=name AND 課程名稱(chēng)=kcname GO,創(chuàng)建存儲(chǔ)過(guò)程示例,3、使用帶默認(rèn)值的輸入?yún)?shù) 【例】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程xscj_p，查詢(xún)指定學(xué)生的學(xué)號(hào)，姓名，所選課程名稱(chēng)及該課程的成績(jī)。默認(rèn)為查詢(xún)劉姓學(xué)生的學(xué)習(xí)情況。 if exists(select * from sys.objects where name=xscj_p and type=P) DROP PROC DBO.xscj_p GO CREATE PROCEDURE DBO.xscj_p name varchar(10) =劉% AS SELECT x

11、sda.學(xué)號(hào),姓名,課程名稱(chēng),成績(jī)FROM XSDA,XSCJ,KCXX WHERE XSDA.學(xué)號(hào)=XSCJ.學(xué)號(hào)AND XSCJ.課程編號(hào)=KCXX.課程編號(hào)AND 姓名LIKE name,創(chuàng)建存儲(chǔ)過(guò)程示例,為輸入?yún)?shù)預(yù)設(shè)默認(rèn)值的好處有： 第一：可以方便用戶(hù)，因?yàn)榭梢詫⒔^大數(shù)用戶(hù)多數(shù)時(shí)候想查詢(xún)的內(nèi)容作為默認(rèn)值。 第二：預(yù)設(shè)了默認(rèn)值，那么在調(diào)用存儲(chǔ)過(guò)程時(shí)，即使用戶(hù)忘記了為參數(shù)賦值，程序也會(huì)執(zhí)行下去。 -EXEC XSCJ_P -EXEC XSCJ_p 李劉王%,創(chuàng)建存儲(chǔ)過(guò)程示例,4、使用輸出參數(shù) 如果需要將存儲(chǔ)過(guò)程的值返回，可使用輸出參數(shù)。 例：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程kcavg_p，查詢(xún)所有學(xué)生所

12、學(xué)指定課程的平均成績(jī)，并將該平均成績(jī)返回。 if object_id(dbo.kcavg_p,P) IS NOT NULL DROP PROC DBO.KCAVG_P GO CREATE PROCEDURE kcavg_p kcname char(20) ,kcavg decimal(3,1) output AS SELECT kcavg=avg(成績(jī)) FROM XSCJ JOIN KCXX ON XSCJ.課程編號(hào)=KCXX.課程編號(hào) WHERE 課程名稱(chēng)=kcname,創(chuàng)建存儲(chǔ)過(guò)程示例,5、返回值 可以使用RETURN 語(yǔ)句從存儲(chǔ)過(guò)程中返回信息。 例： IF OBJECT_ID ( db

13、o.Getxsda, P ) IS NOT NULL DROP PROCEDURE dbo.Getxsda GO CREATE PROCEDURE dbo.Getxsda AS SELECT * FROM XSDA -return 1 GO DECLAREreturn_value int EXECreturn_value = dbo.GetXSDA SELECTReturn Value = return_value,輸出參數(shù)和返回值,又例: CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarcha

14、r(50), DeptID smallint OUTPUT AS IF (Name = ) OR (GroupName = ) RETURN -1 INSERT INTO HumanResources.Department (Name, GroupName) VALUES(Name, GroupName) SET DeptID = SCOPE_IDENTITY() RETURN 0,輸出參數(shù)和返回值,DECLARE dept int, result int EXEC result = AddDepartment Refunds, , dept OUTPUT IF (result = 0) SE

15、LECT dept ELSE SELECT Error during insert,10-1-3 執(zhí)行存儲(chǔ)過(guò)程,與系統(tǒng)存儲(chǔ)過(guò)程一樣，用戶(hù)定義存儲(chǔ)過(guò)程也是通過(guò)EXEC命令來(lái)執(zhí)行。 語(yǔ)法格式： EXECUTE varI=procedure_name value|variable OUTPUT, 說(shuō)明： （1）當(dāng)該語(yǔ)句作為批處理的第一條語(yǔ)句時(shí)，EXEC（或EXECUTE）可以缺省。 （2）procedure_name為需要執(zhí)行的存儲(chǔ)過(guò)程名。 （3）value為輸入?yún)?shù)提供實(shí)值，variable為一個(gè)已定義的變量，OUTPUT緊跟在變量后，說(shuō)明該變量用于保存輸出參數(shù)返回的值。 （4）當(dāng)有多個(gè)參數(shù)時(shí)，彼

16、此用逗號(hào)分隔。 （5）可以將存儲(chǔ)過(guò)程的返回值賦給一個(gè)變量,10-1-3 執(zhí)行存儲(chǔ)過(guò)程,【例】執(zhí)行存儲(chǔ)過(guò)程grkccj_p，查詢(xún)王紅計(jì)算機(jī)文化基礎(chǔ)課的成績(jī)與學(xué)分。 USE XS EXEC GRKCCJ_P 王紅,計(jì)算機(jī)文化基礎(chǔ) GO 【例】執(zhí)行存儲(chǔ)過(guò)程kcavg_p，查詢(xún)計(jì)算機(jī)文化基礎(chǔ)課程的平均分。 USE XS GO DECLARE AVG DECIMAL(3,1) EXEC KCAVG_P 計(jì)算機(jī)文化基礎(chǔ),AVG OUTPUT SELECT AVG AS 計(jì)算機(jī)文件基礎(chǔ)課的平均成績(jī),10-1-4 修改存儲(chǔ)過(guò)程,通過(guò)T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程，語(yǔ)法格式也與定義存儲(chǔ)過(guò)程類(lèi)似，只需將關(guān)鍵字CREA

17、TE 改為ALTER就行了。 語(yǔ)法格式： ALTER PROCEDURESchema_name procedure_name parameter data_type =defaultOUTPUT, WITH AS sql_statement 其中，各參數(shù)的含義與CREATE PROCEDURE相同。,10-1-4 修改存儲(chǔ)過(guò)程,【例】修改存儲(chǔ)過(guò)程XZFj_p，將它修改為查詢(xún)指定系(默認(rèn)為信息)學(xué)生的總學(xué)分。 ALTER PROCEDURE xzf_p xiname char(10) =信息 AS SELECT 學(xué)號(hào),姓名,系名,總學(xué)分FROM XSDA WHERE 系名=xiname ORDE

18、R BY 總學(xué)分DESC -XZF_p 管理 /*查詢(xún)管理系*/ -XZF_p /*查詢(xún)信息系*/,10-1-5 查看存儲(chǔ)過(guò)程,存儲(chǔ)過(guò)程被創(chuàng)建之后，可以進(jìn)行查看瀏覽既可以通過(guò)SQL Server管理平臺(tái)查看用戶(hù)建的存儲(chǔ)過(guò)程，也可以使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶(hù)創(chuàng)建的存儲(chǔ)過(guò)程。 使用系統(tǒng)存儲(chǔ)過(guò)程查看創(chuàng)建的存儲(chǔ)過(guò)程 SP_help objname=name SP_helptext objname=name SP_depends objname= object SP_stored_procedures -返回當(dāng)前環(huán)境中的存儲(chǔ)過(guò)程列表,10-1-6 刪除存儲(chǔ)過(guò)程,刪除存儲(chǔ)過(guò)程 語(yǔ)法格式： DROP PROCEDURE procedure_name