1、第8章 存儲過程,本章知識點,存儲過程概述 存儲過程分類 存儲過程執(zhí)行過程 創(chuàng)建存儲過程 修改及刪除存儲過程 調(diào)用存儲過程,存儲過程概述,場 景 在學(xué)生成績管理系統(tǒng)中，系統(tǒng)用戶經(jīng)常查詢學(xué)生考試信息，包括：學(xué)生名字、所屬班級、授課老師、考試科目、考試分?jǐn)?shù)等信息。由于該查詢在程序中很多地方都要用到，而且使用頻率非常高，因此，開發(fā)人員想用一種可以重用而又高性能的方式來實現(xiàn)。,存儲過程概述,問題分析 所查詢的信息分布在student、score、course、teacher四張表中，需要用連接查詢,查詢代碼如下：,SELECT * FROM STUDENT INNER JOIN SCORE ON ST

2、UDENT.SNO=SCORE.SNO INNER JOIN COURSE ON SCORE.CNO=COURSE.CNO INNER JOIN TEACHER ON COURSE.TNO=TEACHER.TNO WHERE GROP BY HAVING ORDER BY,存儲過程概述,問題分析 存在的問題 該查詢功能在程序中的很多地方使用，因此同樣一段代碼要重復(fù)寫多遍，如果查詢信息有所改變，則要改變很多地方，給程序員帶來一定的麻煩。 系統(tǒng)使用人員每次提交查詢，數(shù)據(jù)庫服務(wù)器會對查詢語句進行編譯、解析和執(zhí)行，而且是反復(fù)做同樣的事情，浪費服務(wù)器資源。,存儲過程概述,解決問題 解決方案 常規(guī)SQL語

3、句能實現(xiàn)查詢功能，但存在一些問題，如：代碼復(fù)用率低、可維護性差和性能低，因此SQL SERVER給出了一種可重用、易維護和高效的解決方案 存儲過程（Stored Procedure ）,存儲過程概述,定 義 將多次重復(fù)執(zhí)行的實現(xiàn)特定功能的代碼段編寫成一個“過程”，將其保存在數(shù)據(jù)庫中，并由SQL Server服務(wù)器通過其過程名來進行調(diào)用，這樣的“過程”就叫做存儲過程。 特 征 存儲過程是一個SQL語句組合。在創(chuàng)建時進行預(yù)編譯，首次被調(diào)用時進行解析，以后再被調(diào)用，則可直接執(zhí)行 存儲過程實現(xiàn)了模塊化編程。被創(chuàng)建后可以被多個用戶共享和重用，有效的減少網(wǎng)絡(luò)流量，提高SQL語句的執(zhí)行效率，提高數(shù)據(jù)庫的安全

4、性能,存儲過程概述,優(yōu) 點 存儲過程允許標(biāo)準(zhǔn)組件式編程。 存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度。 存儲過程能夠減少網(wǎng)絡(luò)流量。 存儲過程可被作為一種安全機制來充分利用。,創(chuàng)建存儲過程,基本語法 CREATE PROC | PROCEDURE procedure_name (parameter data_type VARYING = default OUT | OUTPUT ,. WITH ENCRYPTION AS BEGIN ,. END,創(chuàng)建存儲過程,基本語法：參數(shù)說明 procedure_name ： 過程名稱 過程的參數(shù) data_type ： parameter ： 參數(shù)的數(shù)據(jù)類型 VARY

5、ING ：指定作為輸出參數(shù)支持的結(jié)果集，僅適用于cursor參數(shù) DEFAULT ： 參數(shù)的默認(rèn)值 OUTPUT ： 指示參數(shù)是輸出參數(shù) ENCRYPTION ： 將CREATE PROCEDURE語句的原始文本加密 AS ： 指明該存儲過程將要執(zhí)行的動作 ： 要包含在過程中的一個或多個T-SQL語句,創(chuàng)建存儲過程,執(zhí)行代碼,CREATE PROCEDURE prcListClasses WITH ENCRYPTION AS BEGIN SELECT c.classCourseName, d.depName, c.classTeacherName, c.classStartTime, cr.c

6、rBuildingName, cr.crRoomNo, c.classLimitNum, c.classExistNum, c.classCredit FROM Classes c JOIN Department d ON c.classdepNo=d.depNo JOIN ClassRoom cr ON c.classRoomNo=cr.crNo WHERE classCourseName=計算機網(wǎng)絡(luò) END,執(zhí)行存儲過程,語 法 EXECUTE | EXEC proc_name , Sp_help sp_helptext 注 意 在執(zhí)行存儲過程時，如果語句是一個批處理中的第一個語句，則不一

7、定要指定 EXECUTE 或EXEC關(guān)鍵字。 如果存儲過程帶有參數(shù)，則在執(zhí)行過程時，需帶上相應(yīng)的參數(shù)。,實例分析二：存儲過程用輸出參數(shù)返回值,課堂練習(xí)一： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一種歌曲的平均價格。 課堂練習(xí)二： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一類歌曲中最貴的那一首。 課堂練習(xí)三： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出每一名同學(xué)的平均分?jǐn)?shù)。 課堂練習(xí)四： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出每一位老師所帶的學(xué)生的個數(shù)。,執(zhí)行存儲過程,執(zhí)行存儲過程的步驟 編譯階段 在創(chuàng)建時，系統(tǒng)對其語

8、句進行語法檢查。若有語法錯誤則創(chuàng)建失敗，否則創(chuàng)建成功 解析階段 在首次執(zhí)行時，從系統(tǒng)中讀取存儲過程，并檢查引用的數(shù)據(jù)庫對象是否存在。若存在找不到的數(shù)據(jù)庫對象則產(chǎn)生錯誤，否則進入執(zhí)行階段 執(zhí)行階段 依次執(zhí)行存儲過程中的SQL語句,存儲過程概述,存儲過程分類 用戶定義的存儲過程 T-SQL存儲過程 CLR存儲過程 系統(tǒng)存儲過程 臨時存儲過程 遠(yuǎn)程存儲過程,存儲過程概述,查看存儲過程 查看數(shù)據(jù)庫的的存儲過程也可以通過兩種方法： 利用代碼命令 利用Manager Studio 利用代碼命令查看存儲過程 sp_help proc_name命令查看名為proc_name的存儲過程。 sp_helptext

9、 proc_name命令查看名為proc_name的存儲過程的詳細(xì)代碼。,存儲過程概述,刪除存儲過程兩種方式 在Management Studio中選中要刪除的存儲過程，右鍵單擊“刪除”命令即可。如下圖。 使用DROP命令 DROP PROC PROC_ClassInfo,修改存儲過程,使用ALTER 例 子： ALTER PROCEDURE prcListClasses ( ) AS BEGIN select c.classNo, c.classCourseName, c.classStartTime, c.classTeacherName, cr.crBuildingName, cr.cr

10、RoomNo from Classes c join ClassRoom cr on c.classRoomNo=cr.crNo END,存儲過程的輸入和輸出,參數(shù)是存儲過程與外界進行交互的一種途徑 存儲過程通過輸入?yún)?shù)和輸出參數(shù)與外界進行交互. 存儲過程的數(shù)據(jù)傳遞方式： 輸入?yún)?shù) 調(diào)用者向存儲過程輸入的數(shù)據(jù)值 輸出參數(shù) OUTPUT 存儲過程向調(diào)用者返回的數(shù)據(jù)值 RETURN語句 向外返回int型數(shù)據(jù),實例分析一：存儲過程中使用輸入?yún)?shù),提出問題 學(xué)生成績管理系統(tǒng),用戶想更靈活的查詢指定學(xué)生的相關(guān)信息，利用簡單的存儲過程缺乏靈活性，難以滿足用戶需求，系統(tǒng)應(yīng)該能夠讓用戶查詢指定課程名稱課程相關(guān)

11、信息。,實例分析一,分析問題 步驟1：確定存儲過程所需輸入變量 步驟2：創(chuàng)建帶參數(shù)的存儲過程 步驟3：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù),實例分析一,問題求解 步驟1：確定存儲過程所需輸入變量 確定參數(shù)名： classCourseName 確定參數(shù)的數(shù)據(jù)類型： varchar(20) 注 意 在確定輸入?yún)?shù)數(shù)據(jù)類型時，最好和數(shù)據(jù)庫定義的相關(guān)字段匹配,實例分析一,問題求解 步驟2：創(chuàng)建帶參數(shù)的存儲過程,CREATE PROCEDURE getstuByname ( stuName varchar(20) AS BEGIN SELECT * FROM student WHERE sname= st

12、uname END,實例分析一,問題求解 步驟3：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù) EXEC stuByName 王芳,實例分析二：存儲過程用輸出參數(shù)返回值,課堂練習(xí)一： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出某類歌曲的平均價格。 課堂練習(xí)二： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢在某個價格范圍內(nèi)的歌曲的信息。 課堂練習(xí)三： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠?qū)⒅付▽W(xué)生的指定課程分?jǐn)?shù)提高指定的分?jǐn)?shù)。注意：在存儲過程總傳入?yún)?shù)的時候，只能是變量或者常量。不允許使用函數(shù)動態(tài)計算 課堂練習(xí)四： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過

13、程能夠?qū)⒅付ǖ膶W(xué)生信息插入到student表中。,實例分析二：存儲過程用輸出參數(shù)返回值,提出問題 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出指定課程的平均分、最高分和最低分并將這些查詢出的值返回。,實例分析二,分析問題 步驟1：確定存儲過程所需輸入變量 步驟2：創(chuàng)建帶參數(shù)的存儲過程 步驟3：執(zhí)行存儲過程，驗證其是否能輸入和輸出參數(shù),實例分析二,問題求解 步驟1：確定存儲過程所需輸入變量 確定參數(shù) - 輸入?yún)?shù)：cname varchar(20) -指定課程名稱 - 輸出參數(shù)：avg int OUTPUT max int OUTPUT min float OUTPUT -返

14、回指定課程的信息 注 意：輸出參數(shù)必須要用OUTPUT標(biāo)識,實例分析二,問題求解 步驟2：創(chuàng)建帶參數(shù)的存儲過程,CREATE PROCEDURE prcClass_Student ( cname varchar(20)=, avg float OUTPUT, max int OUTPUT, min int OUTPUT ) AS BEGIN SELECT avg = avg(degree), max = max(degree),min=min(degree) FROM Score Group by cname Having cname=cname END,實例分析二,問題求解 步驟3：驗證存儲

15、過程是否能輸入和輸出參數(shù)，查詢“王芳同學(xué)的選課率。,DECLARE avg float, max int, min int EXEC prcClass_Student 操作系統(tǒng), avg OUTPUT, max OUTPUT, min OUTPUT,實例分析二,結(jié)果分析 代碼通過調(diào)用存儲過程prcClass_Student，傳入?yún)?shù)“操作系統(tǒng)”，返回該課程的選課信息。 輸出參數(shù)和用來接收返回數(shù)據(jù)的變量的數(shù)據(jù)類型和位置必須匹配，并且用來接收返回值的參數(shù)也必須標(biāo)識OUTPUT。 實例中存儲過程用輸入?yún)?shù)和輸出參數(shù)與外部進行數(shù)據(jù)交互，利用輸入和輸出參數(shù)能給程序帶來更大的靈活性。,實例分析二：存儲過程

16、用輸出參數(shù)返回值,課堂練習(xí)一： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠返回某位歌手所唱的歌曲名字，價格和類別。 課堂練習(xí)二： 音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠所有歌曲中的最貴的，最便宜的和均價。 課堂練習(xí)三： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠返回出指定張旭老師教的科目中最低分的學(xué)生姓名和分?jǐn)?shù)。 課堂練習(xí)四： 在學(xué)生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠返回指定學(xué)生的指定科目的的分?jǐn)?shù)。,存儲過程用默認(rèn)值參數(shù),通過存儲過程的輸出參數(shù)，可以對外部調(diào)用對象返回一個或多個值，同樣在存儲過程中還有另一種方式可以返回值使用RETURN關(guān)鍵字。 使

17、用RETURN只能返回單個值，并且是整型值。它通常返回下列值： 操作過程中受影響的行數(shù) 錯誤編碼 插入到 IDENTITY 列中的值,存儲過程用RETURN返回值,通過存儲過程的輸出參數(shù)，可以對外部調(diào)用對象返回一個或多個值，同樣在存儲過程中還有另一種方式可以返回值使用RETURN關(guān)鍵字。 使用RETURN只能返回單個值，并且是整型值。它通常返回下列值： 操作過程中受影響的行數(shù) 錯誤編碼 插入到 IDENTITY 列中的值,實例分析三,提出問題 創(chuàng)建存儲過程以查看指定老師所授選修課的信息，并向調(diào)用對象返回一個消息，表明查詢結(jié)果。,實例分析三,分析問題 步驟1：確定使用RETURN返回單個值，作為

18、輸出消息 步驟2：創(chuàng)建存儲過程 步驟3：執(zhí)行存儲過程，驗證其功能,實例分析三,問題求解 RETURN語句的語法為： RETURN integer_expression 步驟1：確定使用RETURN返回單個值，作為輸出消息 RETURN 1表示查詢成功，找到所需記錄 RETURN 0表示查詢失敗，沒有發(fā)現(xiàn)有效記錄,實例分析三,問題求解 步驟2：創(chuàng)建存儲過程,CREATE PROCEDURE prcListClassesByTeacherName ( classTeacherName varchar(16) ) AS BEGIN IF exists(SELECT * FROM COURSE WHE

19、RE classTeacherName=classTeacherName) BEGIN RETURN 1 END ELSE BEGIN RETURN 0 END END,實例分析三,問題求解 步驟3：執(zhí)行存儲過程，驗證其功能 DECLARE result int EXEC result = prcListClassesByTeacherName 朱紫英 IF(result=1) PRINT 存在相關(guān)信息 ELSE PRINT 沒有相關(guān)信息 注 意： RETURN 不能返回NULL。若試圖返回NULL，將生成警告信息并返回 0,存儲過程的高級應(yīng)用,存儲過程的高級應(yīng)用包括： 存儲過程間的調(diào)用 在存儲過程中使用游標(biāo),實例分析四：存儲過程間的調(diào)用,提出問題 現(xiàn)在需要建立一個存儲過程，此存儲過程在場景一的基礎(chǔ)上能夠查詢查看多門課程的相關(guān)信息。,實例分析四,分析問題 步驟1：創(chuàng)建調(diào)用現(xiàn)有存儲過程的存儲過程 步驟2：執(zhí)行存儲過程，驗證其功能,實例分析四,問題求解 步驟1：創(chuàng)