第10章存儲過程與觸發(fā)器1.ppt

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

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

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

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

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

6、、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER （4）CREATE PROCEDURE必須是批處理中的第一條語句。存儲過程的定義也不能跨越批處理。 （5）用相應(yīng)架構(gòu)名稱限定存儲過程所引用的對象名稱，從而確保從存儲過程中訪問來自不同架構(gòu)的表、視圖或其它對象。,10-1 存儲過程,創(chuàng)建存儲過程示例,1、創(chuàng)建簡單的存儲過程 例： 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)建存儲過程示例,1、創(chuàng)建簡單的存儲過程 又例：創(chuàng)建一個(gè)存儲過程jsjzf_p，用于查詢信息系學(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é)號,姓名,系名,總學(xué)分 FROM XSDA WHERE 系名=信息 ORDER BY 總學(xué)分 DESC,創(chuàng)建存儲過程示例,2、創(chuàng)建帶輸入?yún)?shù)的存儲過程 例：創(chuàng)建一個(gè)存儲過程，用于根據(jù)系名返回XSDA的數(shù)據(jù)，系名稱條件使用輸入?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)建存儲過程示例,又例：使用輸入?yún)?shù)，創(chuàng)建存儲過程grkccj_p，查詢某人指定課程的成績和學(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é)號,姓名,課程名稱,成績,學(xué)分FROM XSDA,XSCJ,KCXX WHERE XSDA

10、.學(xué)號=XSCJ.學(xué)號AND XSCJ.課程編號=KCXX.課程編號 AND 姓名=name AND 課程名稱=kcname GO,創(chuàng)建存儲過程示例,3、使用帶默認(rèn)值的輸入?yún)?shù) 【例】創(chuàng)建一個(gè)存儲過程xscj_p，查詢指定學(xué)生的學(xué)號，姓名，所選課程名稱及該課程的成績。默認(rè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é)號,姓名,課程名稱,成績FROM XSDA,XSCJ,KCXX WHERE XSDA.學(xué)號=XSCJ.學(xué)號AND XSCJ.課程編號=KCXX.課程編號AND 姓名LIKE name,創(chuàng)建存儲過程示例,為輸入?yún)?shù)預(yù)設(shè)默認(rèn)值的好處有： 第一：可以方便用戶，因?yàn)榭梢詫⒔^大數(shù)用戶多數(shù)時(shí)候想查詢的內(nèi)容作為默認(rèn)值。 第二：預(yù)設(shè)了默認(rèn)值，那么在調(diào)用存儲過程時(shí)，即使用戶忘記了為參數(shù)賦值，程序也會執(zhí)行下去。 -EXEC XSCJ_P -EXEC XSCJ_p 李劉王%,創(chuàng)建存儲過程示例,4、使用輸出參數(shù) 如果需要將存儲過程的值返回，可使用輸出參數(shù)。 例：創(chuàng)建一個(gè)存儲過程kcavg_p，查詢所有學(xué)生所

12、學(xué)指定課程的平均成績，并將該平均成績返回。 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(成績) FROM XSCJ JOIN KCXX ON XSCJ.課程編號=KCXX.課程編號 WHERE 課程名稱=kcname,創(chuàng)建存儲過程示例,5、返回值 可以使用RETURN 語句從存儲過程中返回信息。 例： 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í)行存儲過程,與系統(tǒng)存儲過程一樣，用戶定義存儲過程也是通過EXEC命令來執(zhí)行。 語法格式： EXECUTE varI=procedure_name value|variable OUTPUT, 說明： （1）當(dāng)該語句作為批處理的第一條語句時(shí)，EXEC（或EXECUTE）可以缺省。 （2）procedure_name為需要執(zhí)行的存儲過程名。 （3）value為輸入?yún)?shù)提供實(shí)值，variable為一個(gè)已定義的變量，OUTPUT緊跟在變量后，說明該變量用于保存輸出參數(shù)返回的值。 （4）當(dāng)有多個(gè)參數(shù)時(shí)，彼

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

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

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