第8章-存儲過程(精品課件)

1、第8章 存儲過程12021/8/5page2本章知識點w 存儲過程概述w 存儲過程分類 w 存儲過程執(zhí)行過程 w 創(chuàng)建存儲過程 w 修改及刪除存儲過程w 調用存儲過程page3存儲過程概述w 場 景F在學生成績管理系統(tǒng)中，系統(tǒng)用戶經(jīng)常在學生成績管理系統(tǒng)中，系統(tǒng)用戶經(jīng)常查詢學生考試查詢學生考試信息，信息，包括：學生名字、所屬班級、授課老師、考試科目、考試分包括：學生名字、所屬班級、授課老師、考試科目、考試分數(shù)等信息。由于該查詢在程序中很多地方都要用到，而且使數(shù)等信息。由于該查詢在程序中很多地方都要用到，而且使用頻率非常高，因此，開發(fā)人員想用一種可以用頻率非常高，因此，開發(fā)人員想用一種可以重用重用

2、而又而又高性高性能能的方式來實現(xiàn)。的方式來實現(xiàn)。page4存儲過程概述w 問題分析F所查詢的信息分布在所查詢的信息分布在student、score、course、teacher四張表中，需要用連接查詢四張表中，需要用連接查詢,查詢代碼如下：查詢代碼如下：SELECT *FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO=SCORE.SNOINNER JOIN COURSE ON SCORE.CNO=COURSE.CNOINNER JOIN TEACHER ON COURSE.TNO=TEACHER.TNOWHEREGROP BY HAVINGORDER B

3、Ypage5存儲過程概述w 問題分析F存在的問題存在的問題 該查詢功能在程序中的很多地方使用，因此同樣一段代碼要重復寫多遍，如果查詢信息有所改變，則要改變很多地方，給程序員帶來一定的麻煩。 系統(tǒng)使用人員每次提交查詢，數(shù)據(jù)庫服務器會對查詢語句進行編譯、解析和執(zhí)行，而且是反復做同樣的事情，浪費服務器資源。page6存儲過程概述w 解決問題F解決方案解決方案 常規(guī)SQL語句能實現(xiàn)查詢功能，但存在一些問題，如：代碼復用率低、可維護性差和性能低，因此SQL SERVER給出了一種可重用、易維護和高效的解決方案 存儲過程（Stored Procedure ）page7存儲過程概述w 定 義F將多次重復執(zhí)行

4、的實現(xiàn)特定功能的代碼段編寫成一個將多次重復執(zhí)行的實現(xiàn)特定功能的代碼段編寫成一個“過程過程”，將其保存在數(shù)據(jù)庫中，并由將其保存在數(shù)據(jù)庫中，并由SQL Server服務器通過其過程名來進服務器通過其過程名來進行調用，這樣的行調用，這樣的“過程過程”就叫做就叫做存儲過程。存儲過程。w 特 征 存儲過程是一個SQL語句組合。在創(chuàng)建時進行預編譯，首次被調用時進行解析，以后再被調用，則可直接執(zhí)行 存儲過程實現(xiàn)了模塊化編程。被創(chuàng)建后可以被多個用戶共享和重用，有效的減少網(wǎng)絡流量，提高SQL語句的執(zhí)行效率，提高數(shù)據(jù)庫的安全性能page8存儲過程概述w 優(yōu) 點F存儲過程允許標準組件式編程。存儲過程允許標準組件式編

5、程。F存儲過程能夠實現(xiàn)較快的執(zhí)行速度。存儲過程能夠實現(xiàn)較快的執(zhí)行速度。F存儲過程能夠減少網(wǎng)絡流量。存儲過程能夠減少網(wǎng)絡流量。F存儲過程可被作為一種安全機制來充分利用。存儲過程可被作為一種安全機制來充分利用。page9創(chuàng)建存儲過程w 基本語法CREATE PROC | PROCEDURE procedure_name (parameter data_type VARYING = default OUT | OUTPUT ,. WITH ENCRYPTION ASBEGIN ,. ENDpage10創(chuàng)建存儲過程w基本語法：參數(shù)說明參數(shù)說明 procedure_name ： 過程名稱過程名稱 過程的

6、參數(shù)過程的參數(shù) data_type ： parameter ： 參數(shù)的數(shù)據(jù)類型參數(shù)的數(shù)據(jù)類型 VARYING ：指定作為輸出參數(shù)支持的結果集，僅適用于：指定作為輸出參數(shù)支持的結果集，僅適用于cursor參數(shù)參數(shù) DEFAULT ： 參數(shù)的默認值參數(shù)的默認值 OUTPUT ： 指示參數(shù)是輸出參數(shù)指示參數(shù)是輸出參數(shù) ENCRYPTION ： 將將CREATE PROCEDURE語句的原始文本語句的原始文本加密加密 AS ： 指明該存儲過程將要執(zhí)行的動作指明該存儲過程將要執(zhí)行的動作 ： 要包含在過程中的一個或多個要包含在過程中的一個或多個T-SQL語句語句page11創(chuàng)建存儲過程w 執(zhí)行代碼CREA

7、TE PROCEDURE prcListClassesWITH ENCRYPTIONASBEGINSELECT c.classCourseName, d.depName, c.classTeacherName, c.classStartTime, cr.crBuildingName, 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.c

8、rNo WHERE classCourseName=計算機網(wǎng)絡 ENDpage12執(zhí)行存儲過程w 語 法EXECUTE | EXEC proc_name ,Sp_help sp_helptextF注注 意意 在執(zhí)行存儲過程時，如果語句是一個批處理中的第一個語句，則不一定要指定 EXECUTE 或EXEC關鍵字。 如果存儲過程帶有參數(shù)，則在執(zhí)行過程時，需帶上相應的參數(shù)。page13實例分析二：存儲過程用輸出參數(shù)返回值w 課堂練習一：F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一種歌曲的平均價格。種歌曲的平均價格。w 課堂練習二：

9、F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出每一類歌曲中最貴的那一首。類歌曲中最貴的那一首。w 課堂練習三：F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出每一名同學的平均分數(shù)。夠查詢出每一名同學的平均分數(shù)。w 課堂練習四：F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出每一位老師所帶的學生的個數(shù)。夠查詢出每一位老師所帶的學生的個數(shù)。page14執(zhí)行存儲過程w 執(zhí)行存儲過程的步驟F編

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

11、用代碼命令 利用Manager StudioF利用代碼命令查看存儲過程利用代碼命令查看存儲過程 sp_help proc_name命令查看名為proc_name的存儲過程。 sp_helptext proc_name命令查看名為proc_name的存儲過程的詳細代碼。page17存儲過程概述w刪除存儲過程兩種方式F 在在Management Studio中選中要刪除的存儲過程，右鍵中選中要刪除的存儲過程，右鍵單擊單擊“刪除刪除”命令即可。如下圖。命令即可。如下圖。F 使用使用DROP命令命令DROP PROC PROC_ClassInfopage18修改存儲過程w 使用ALTERF例例 子：子

12、：ALTER PROCEDURE prcListClasses()ASBEGINselect c.classNo, c.classCourseName, c.classStartTime, c.classTeacherName, cr.crBuildingName, cr.crRoomNo from Classes c join ClassRoom cr on c.classRoomNo=cr.crNoENDpage19存儲過程的輸入和輸出w 參數(shù)是存儲過程與外界進行交互的一種途徑F存儲過程通過存儲過程通過輸入?yún)?shù)輸入?yún)?shù)和和輸出參數(shù)輸出參數(shù)與外界進行交互與外界進行交互.w 存儲過程的數(shù)據(jù)傳遞

13、方式：F輸入?yún)?shù)輸入?yún)?shù) 調用者向存儲過程輸入的數(shù)據(jù)值F輸出參數(shù)輸出參數(shù) OUTPUT 存儲過程向調用者返回的數(shù)據(jù)值FRETURN語句語句 向外返回int型數(shù)據(jù)page20實例分析一：存儲過程中使用輸入?yún)?shù)w 提出問題F學生成績管理系統(tǒng)學生成績管理系統(tǒng),用戶想更靈活的查詢用戶想更靈活的查詢指定學生指定學生的相關信的相關信息，利用簡單的存儲過程缺乏靈活性，難以滿足用戶需求，息，利用簡單的存儲過程缺乏靈活性，難以滿足用戶需求，系統(tǒng)應該能夠讓用戶查詢指定課程名稱課程相關信息。系統(tǒng)應該能夠讓用戶查詢指定課程名稱課程相關信息。page21實例分析一w 分析問題F步驟步驟1：確定存儲過程所需輸入變量：確定

14、存儲過程所需輸入變量F步驟步驟2：創(chuàng)建帶參數(shù)的存儲過程：創(chuàng)建帶參數(shù)的存儲過程F步驟步驟3：執(zhí)行存儲過程，驗證其是否能：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù)輸入?yún)?shù)page22實例分析一w 問題求解F步驟步驟1：確定存儲過程所需輸入變量：確定存儲過程所需輸入變量 確定參數(shù)名： classCourseName 確定參數(shù)的數(shù)據(jù)類型： varchar(20)F注注 意意 在確定輸入?yún)?shù)數(shù)據(jù)類型時，最好和數(shù)據(jù)庫定義的相關字段匹配page23實例分析一w 問題求解F步驟步驟2：創(chuàng)建帶參數(shù)的存儲過程：創(chuàng)建帶參數(shù)的存儲過程CREATE PROCEDURE getstuByname( stuName varcha

15、r(20)ASBEGINSELECT *FROM studentWHERE sname= stunameENDpage24實例分析一w 問題求解F步驟步驟3：執(zhí)行存儲過程，驗證其是否能：執(zhí)行存儲過程，驗證其是否能輸入?yún)?shù)輸入?yún)?shù) EXEC stuByName 王芳page25實例分析二：存儲過程用輸出參數(shù)返回值w 課堂練習一：F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出某類音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢出某類歌曲的平均價格。歌曲的平均價格。w 課堂練習二：F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢在某個音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠查詢在

16、某個價格范圍內的歌曲的信息。價格范圍內的歌曲的信息。w 課堂練習三：F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠將指定學生的指定課程分數(shù)提高指定的分數(shù)。夠將指定學生的指定課程分數(shù)提高指定的分數(shù)。注意：在存儲過注意：在存儲過程總傳入?yún)?shù)的時候，只能是變量或者常量。不允許使用函數(shù)動程總傳入?yún)?shù)的時候，只能是變量或者常量。不允許使用函數(shù)動態(tài)計算態(tài)計算F 課堂練習四： 在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠將指在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠將指定的學生信息插入到定的學生信息插入到st

17、udent表中。表中。 page26實例分析二：存儲過程用輸出參數(shù)返回值w 提出問題F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠查詢出程能夠查詢出指定課程指定課程的平均分、最高分和最低分并將這些的平均分、最高分和最低分并將這些查詢出的值返回。查詢出的值返回。page27實例分析二w 分析問題F步驟步驟1：確定存儲過程所需輸入變量：確定存儲過程所需輸入變量F步驟步驟2：創(chuàng)建帶參數(shù)的存儲過程：創(chuàng)建帶參數(shù)的存儲過程F步驟步驟3：執(zhí)行存儲過程，驗證其是否能：執(zhí)行存儲過程，驗證其是否能輸入和輸出參數(shù)輸入和輸出參數(shù)page28page29

18、實例分析二w 問題求解F步驟步驟1：確定存儲過程所需輸入變量：確定存儲過程所需輸入變量 確定參數(shù) - 輸入?yún)?shù)：cname varchar(20) -指定課程名稱 - 輸出參數(shù)：avg int OUTPUT max int OUTPUT min float OUTPUT -返回指定課程的信息 注 意：輸出參數(shù)必須要用OUTPUT標識page30實例分析二w 問題求解F步驟步驟2：創(chuàng)建帶參數(shù)的存儲過程：創(chuàng)建帶參數(shù)的存儲過程CREATE PROCEDURE prcClass_Student (cname varchar(20)=,avg float OUTPUT, max int OUTPUT,m

19、in int OUTPUT)ASBEGINSELECT avg = avg(degree), max = max(degree),min=min(degree) FROM ScoreGroup by cnameHaving cname=cnameENDpage31實例分析二w 問題求解F步驟步驟3：驗證存儲過程是否能輸入和輸出參數(shù)，查詢：驗證存儲過程是否能輸入和輸出參數(shù)，查詢“王芳王芳同學的選課率。同學的選課率。DECLARE avg float, max int, min int EXEC prcClass_Student 操作系統(tǒng)操作系統(tǒng),avg OUTPUT,max OUTPUT,min

20、 OUTPUTpage32實例分析二w 結果分析F代碼通過調用存儲過程代碼通過調用存儲過程prcClass_Student，傳入?yún)?shù)，傳入?yún)?shù)“操作系操作系統(tǒng)統(tǒng)”，返回該課程的選課信息。，返回該課程的選課信息。F輸出參數(shù)輸出參數(shù)和和用來接收返回數(shù)據(jù)的變量用來接收返回數(shù)據(jù)的變量的數(shù)據(jù)類型和位置必須匹配，的數(shù)據(jù)類型和位置必須匹配，并且用來接收返回值的參數(shù)也必須標識并且用來接收返回值的參數(shù)也必須標識OUTPUT。 F實例中存儲過程用輸入?yún)?shù)和輸出參數(shù)與外部進行數(shù)據(jù)交互，利實例中存儲過程用輸入?yún)?shù)和輸出參數(shù)與外部進行數(shù)據(jù)交互，利用輸入和輸出參數(shù)能給程序帶來更大的靈活性。用輸入和輸出參數(shù)能給程序帶來更大

21、的靈活性。page33實例分析二：存儲過程用輸出參數(shù)返回值w 課堂練習一：F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠返回某位歌音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠返回某位歌手所唱的歌曲名字，價格和類別。手所唱的歌曲名字，價格和類別。w 課堂練習二：F音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠所有歌曲中音樂信息管理系統(tǒng)中創(chuàng)建存儲過程，磁存儲過程能夠所有歌曲中的最貴的，最便宜的和均價。的最貴的，最便宜的和均價。 w 課堂練習三：F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠返回出指定張旭老師教的科目中最低分的

22、學生姓名和分數(shù)。夠返回出指定張旭老師教的科目中最低分的學生姓名和分數(shù)。w 課堂練習四：F在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能在學生成績管理系統(tǒng)中，需要建立一個存儲過程，此存儲過程能夠返回指定學生的指定科目的的分數(shù)。夠返回指定學生的指定科目的的分數(shù)。page34存儲過程用默認值參數(shù) 通過存儲過程的輸出參數(shù)，可以對外部調用對象返回一個或多個通過存儲過程的輸出參數(shù)，可以對外部調用對象返回一個或多個值，同樣在存儲過程中還有另一種方式可以返回值值，同樣在存儲過程中還有另一種方式可以返回值使用使用RETURN關鍵字。關鍵字。w 使用RETURN只能返回單個值，并且是整型值。它通常返回下

23、列值：F操作過程中受影響的行數(shù)操作過程中受影響的行數(shù)F錯誤編碼錯誤編碼F插入到插入到 IDENTITY 列中的值列中的值page35存儲過程用RETURN返回值 通過存儲過程的輸出參數(shù)，可以對外部調用對象返回一個或多個通過存儲過程的輸出參數(shù)，可以對外部調用對象返回一個或多個值，同樣在存儲過程中還有另一種方式可以返回值值，同樣在存儲過程中還有另一種方式可以返回值使用使用RETURN關鍵字。關鍵字。w 使用RETURN只能返回單個值，并且是整型值。它通常返回下列值：F操作過程中受影響的行數(shù)操作過程中受影響的行數(shù)F錯誤編碼錯誤編碼F插入到插入到 IDENTITY 列中的值列中的值page36實例分析

24、三w 提出問題F創(chuàng)建存儲過程以查看創(chuàng)建存儲過程以查看指定老師指定老師所授選修課的信息，并向調用所授選修課的信息，并向調用對象對象返回一個消息返回一個消息，表明查詢結果。，表明查詢結果。page37實例分析三w 分析問題F步驟步驟1：確定使用：確定使用RETURN返回單個值，作為輸出消息返回單個值，作為輸出消息F步驟步驟2：創(chuàng)建存儲過程：創(chuàng)建存儲過程F步驟步驟3：執(zhí)行存儲過程，驗證其功能：執(zhí)行存儲過程，驗證其功能page38實例分析三w 問題求解FRETURN語句的語法為：語句的語法為： RETURN integer_expressionF步驟步驟1：確定使用：確定使用RETURN返回單個值，作

25、為輸出消息返回單個值，作為輸出消息 RETURN 1表示查詢成功，找到所需記錄 RETURN 0表示查詢失敗，沒有發(fā)現(xiàn)有效記錄page39實例分析三w 問題求解F步驟步驟2：創(chuàng)建存儲過程：創(chuàng)建存儲過程CREATE PROCEDURE prcListClassesByTeacherName(classTeacherName varchar(16)ASBEGINIF exists(SELECT * FROM COURSEWHERE classTeacherName=classTeacherName)BEGINRETURN 1 ENDELSEBEGINRETURN 0ENDENDpage40實例分析

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