




版權說明:本文檔由用戶提供并上傳,收益歸屬內容提供方,若內容存在侵權,請進行舉報或認領
文檔簡介
1、SQL Server 2005數(shù)據(jù)庫應用技術(第二版),第10章 存儲過程與觸發(fā)器(1),復習,視圖簡介 創(chuàng)建和管理視圖 創(chuàng)建視圖(視圖加密、架構綁定的目的) 如何獲得視圖的有關信息 修改與刪除視圖 使用視圖修改數(shù)據(jù) 使用視圖優(yōu)化性能 索引視圖 分區(qū)視圖,本次課的學習內容,理解存儲過程,了解存儲過程的用途 創(chuàng)建存儲過程的語法與示例(了解輸入與輸出參數(shù)的使用) 存儲過程的更改與刪除 執(zhí)行存儲過程的方式,10-1 存儲過程,導入: 為了查詢XS數(shù)據(jù)庫中信息系的學生所修總學分信息,可以使用下列查詢: USE XS SELECT 學號,姓名,系名,總學分 FROM XSDA WHERE 系名=信息 O
2、RDER BY 總學分 DESC 問題: 如果網絡上有5000個用戶執(zhí)行同一查詢 ,結果會怎樣? 解決方法: 將代碼以一定的形式存放在服務器中,而不是存儲在客戶機上 。,10-1-1 存儲過程基礎 存儲過程就是在SQL Server數(shù)據(jù)庫中存放的查詢,是存儲在服務器中的一組預編譯過的T-SQL語句,而不是在客戶機上的前端代碼中存放的查詢。 1.存儲過程的優(yōu)點 存儲過程除減少網絡通信流之外,還有如下優(yōu)點: (1)存儲過程在服務器端運行,執(zhí)行速度快。 (2)存儲過程是預編譯過的,當?shù)谝淮握{用以后,就駐留在內存中,以后調用時不必再進行編譯,因此,它的運行速度比獨立運行同樣的程序要快。,10-1 存儲
3、過程,優(yōu)點: (3)簡化數(shù)據(jù)庫管理。例如,如果需要修改現(xiàn)有查詢,而查詢存放在用戶機器上,則要在所有的用戶機器上進行修改。而如果在服務器中集中存放查詢并作為存儲過程,則只需要在服務器上改變一次。 (4)存儲過程可以接受參數(shù)。允許零個或多個輸入?yún)?shù),也允許零個或多個輸出參數(shù)。 (5)可作為安全機制使用。即使無對應權限的用戶,也可授予權限執(zhí)行存儲過程。 注意:并不是每一個發(fā)往服務器的查詢都要使用存儲過程,只有用戶經常運行這個查詢,才應考慮將該查詢生成為存儲過程。,10-1 存儲過程,2.存儲過程的分類 : 按編寫的語言來分:T-SQL存儲過程與CLR(公共語言運行)存儲過程。 T-SQL 存儲過程又
4、可分為: 用戶定義存儲過程 系統(tǒng)存儲過程。,10-1 存儲過程,10-1-2 創(chuàng)建存儲過程 定義一個存儲過程時,首先要確定以下幾點: (1)存儲過程的名字。 (2)該存儲過程所要完成的功能,這是一個存儲過程的主體。 (3)是否需要輸入?yún)?shù),是否需要輸出參數(shù)來輸出結果。,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 各個選項請詳見P201,10-1 存儲過程,說明: (1)用戶自定義存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建。 (2)=default用于指定存儲過程輸入?yún)?shù)的默認值。默認值必須是常量或NULL,可以包含通配符%、_、和。如果在定義時帶了“=default”參數(shù),則執(zhí)行存儲過程時根據(jù)情況可不提供實參。 (3)在定義用戶存儲過程時,不能使用如下對象創(chuàng)建語句 CREATE VIEW
6、、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER (4)CREATE PROCEDURE必須是批處理中的第一條語句。存儲過程的定義也不能跨越批處理。 (5)用相應架構名稱限定存儲過程所引用的對象名稱,從而確保從存儲過程中訪問來自不同架構的表、視圖或其它對象。,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)建一個存儲過程jsjzf_p,用于查詢信息系學生總學分的排名情況(從高到低)。 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 學號,姓名,系名,總學分 FROM XSDA WHERE 系名=信息 ORDER BY 總學分 DESC,創(chuàng)建存儲過程示例,2、創(chuàng)建帶輸入?yún)?shù)的存儲過程 例:創(chuàng)建一個存儲過程,用于根據(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,查詢某人指定課程的成績和學分。 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.學號,姓名,課程名稱,成績,學分FROM XSDA,XSCJ,KCXX WHERE XSDA
10、.學號=XSCJ.學號AND XSCJ.課程編號=KCXX.課程編號 AND 姓名=name AND 課程名稱=kcname GO,創(chuàng)建存儲過程示例,3、使用帶默認值的輸入?yún)?shù) 【例】創(chuàng)建一個存儲過程xscj_p,查詢指定學生的學號,姓名,所選課程名稱及該課程的成績。默認為查詢劉姓學生的學習情況。 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.學號,姓名,課程名稱,成績FROM XSDA,XSCJ,KCXX WHERE XSDA.學號=XSCJ.學號AND XSCJ.課程編號=KCXX.課程編號AND 姓名LIKE name,創(chuàng)建存儲過程示例,為輸入?yún)?shù)預設默認值的好處有: 第一:可以方便用戶,因為可以將絕大數(shù)用戶多數(shù)時候想查詢的內容作為默認值。 第二:預設了默認值,那么在調用存儲過程時,即使用戶忘記了為參數(shù)賦值,程序也會執(zhí)行下去。 -EXEC XSCJ_P -EXEC XSCJ_p 李劉王%,創(chuàng)建存儲過程示例,4、使用輸出參數(shù) 如果需要將存儲過程的值返回,可使用輸出參數(shù)。 例:創(chuàng)建一個存儲過程kcavg_p,查詢所有學生所
12、學指定課程的平均成績,并將該平均成績返回。 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)當該語句作為批處理的第一條語句時,EXEC(或EXECUTE)可以缺省。 (2)procedure_name為需要執(zhí)行的存儲過程名。 (3)value為輸入?yún)?shù)提供實值,variable為一個已定義的變量,OUTPUT緊跟在變量后,說明該變量用于保存輸出參數(shù)返回的值。 (4)當有多個參數(shù)時,彼
16、此用逗號分隔。 (5)可以將存儲過程的返回值賦給一個變量,10-1-3 執(zhí)行存儲過程,【例】執(zhí)行存儲過程grkccj_p,查詢王紅計算機文化基礎課的成績與學分。 USE XS EXEC GRKCCJ_P 王紅,計算機文化基礎 GO 【例】執(zhí)行存儲過程kcavg_p,查詢計算機文化基礎課程的平均分。 USE XS GO DECLARE AVG DECIMAL(3,1) EXEC KCAVG_P 計算機文化基礎,AVG OUTPUT SELECT AVG AS 計算機文件基礎課的平均成績,10-1-4 修改存儲過程,通過T-SQL語句修改存儲過程,語法格式也與定義存儲過程類似,只需將關鍵字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,將它修改為查詢指定系(默認為信息)學生的總學分。 ALTER PROCEDURE xzf_p xiname char(10) =信息 AS SELECT 學號,姓名,系名,總學分FROM XSDA WHERE 系名=xiname ORDE
18、R BY 總學分DESC -XZF_p 管理 /*查詢管理系*/ -XZF_p /*查詢信息系*/,10-1-5 查看存儲過程,存儲過程被創(chuàng)建之后,可以進行查看瀏覽既可以通過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 -返回當前環(huán)境中的存儲過程列表,10-1-6 刪除存儲過程,刪除存儲過程 語法格式: DROP PROCEDURE procedure_name
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網頁內容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
- 4. 未經權益所有人同意不得將文件中的內容挪作商業(yè)或盈利用途。
- 5. 人人文庫網僅提供信息存儲空間,僅對用戶上傳內容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內容本身不做任何修改或編輯,并不能對任何下載內容負責。
- 6. 下載文件中如有侵權或不適當內容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025年杭州市稅務系統(tǒng)遴選面試真題帶詳解含答案
- 2025年安徽科技學院005機械工程學院085500機械考研報錄數(shù)據(jù)分析報告初
- 撤場施工安全協(xié)議書范文
- 特色餐廳員工派遣與餐飲服務品質提升合同
- 職業(yè)技能代理招生合作框架協(xié)議
- 垃圾分類現(xiàn)場指導服務協(xié)議
- 國企項目招標廉政監(jiān)督與保障服務協(xié)議
- 醫(yī)院安全生產月活動工作總結
- 安全生產是什么
- 防溺水安全教育一分鐘
- 刑法學(上冊)馬工程課件 第1章 刑法概說
- GB/T 9125.1-2020鋼制管法蘭連接用緊固件第1部分:PN系列
- GB/T 27770-2011病媒生物密度控制水平鼠類
- 2023年廣西賓陽縣昆侖投資集團有限公司招聘筆試題庫及答案解析
- 社區(qū)社群團長招募書經典案例干貨課件
- 12、施工現(xiàn)場“三級配電”配置規(guī)范-附電路圖
- 新人教版七年級上冊初中生物全冊課時練(課后作業(yè)設計)
- 智能制造MES項目實施方案(注塑行業(yè)MES方案建議書)
- 四年級奧數(shù)講義
- 江蘇省南京市2024屆高一數(shù)學下學期期末試題(含解析)
- 多旋翼無人機專業(yè)培訓教材ppt課件
評論
0/150
提交評論