1、第八章 （二）存儲(chǔ)過(guò)程本章目標(biāo)了解存儲(chǔ)過(guò)程的優(yōu)點(diǎn)掌握常用的系統(tǒng)存儲(chǔ)過(guò)程掌握如何創(chuàng)建存儲(chǔ)過(guò)程掌握如何調(diào)用存儲(chǔ)過(guò)程2存儲(chǔ)過(guò)程（procedure）類似于C語(yǔ)言中的函數(shù)用來(lái)執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則存儲(chǔ)過(guò)程可以帶參數(shù)，也可以返回結(jié)果int sum(int a,int b) int s; s =a+b; return s ;存儲(chǔ)過(guò)程相當(dāng)于C語(yǔ)言中的函數(shù)什么是存儲(chǔ)過(guò)程 2-13存儲(chǔ)過(guò)程-單個(gè) SELECT 語(yǔ)句SELECT 語(yǔ)句塊SELECT語(yǔ)句與邏輯控制語(yǔ)句可以包含什么是存儲(chǔ)過(guò)程 2-2存儲(chǔ)過(guò)程可以包含數(shù)據(jù)操縱語(yǔ)句、變量、邏輯 控制語(yǔ)句等4執(zhí)行速度更快允許模塊化程序設(shè)計(jì) 提高系統(tǒng)安全性減少網(wǎng)絡(luò)流

2、通量存儲(chǔ)過(guò)程的優(yōu)點(diǎn)5系統(tǒng)存儲(chǔ)過(guò)程由系統(tǒng)定義，存放在master數(shù)據(jù)庫(kù)中類似C語(yǔ)言中的系統(tǒng)函數(shù)系統(tǒng)存儲(chǔ)過(guò)程的名稱都以“sp_”開(kāi)頭或”xp_”開(kāi)頭用戶自定義存儲(chǔ)過(guò)程由用戶在自己的數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程類似C語(yǔ)言中的用戶自定義函數(shù)存儲(chǔ)過(guò)程的分類 6常用的系統(tǒng)存儲(chǔ)過(guò)程 4-1系統(tǒng)存儲(chǔ)過(guò)程說(shuō)明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束s

3、p_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義的存儲(chǔ)過(guò)程、觸發(fā)器或視圖的實(shí)際文本。7EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE studentGOEXEC sp_tablesEXEC sp_columns student EXEC sp_help studentEXEC sp_helpconstraint studentEXEC sp_helpindex

4、scEXEC sp_helptext stu_js EXEC sp_stored_procedures 常用的系統(tǒng)存儲(chǔ)過(guò)程 4-2修改數(shù)據(jù)庫(kù)的名稱(單用戶訪問(wèn))列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)當(dāng)前數(shù)據(jù)庫(kù)中查詢的對(duì)象的列表返回某個(gè)表列的信息查看表student的信息查看表student的約束查看表sc的索引查看視圖的語(yǔ)句文本查看當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程演示：常用的存儲(chǔ)過(guò)程8常用的擴(kuò)展存儲(chǔ)過(guò)程：xp_cmdshell 可以執(zhí)行DOS命令下的一些的操作 以文本行方式返回任何輸出 調(diào)用語(yǔ)法： EXEC xp_cmdshell DOS命令 NO_OUTPUT常用的系統(tǒng)存儲(chǔ)過(guò)程 4-39常用的系統(tǒng)存儲(chǔ)過(guò)程 4-4US

5、E masterGOEXEC xp_cmdshell mkdir d:bank, NO_OUTPUTIF EXISTS(SELECT * FROM sysdatabases WHERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ( -設(shè)置主數(shù)據(jù)文件和日志文件的路徑為d:bank)GOEXEC xp_cmdshell dir D:bank -查看文件創(chuàng)建數(shù)據(jù)庫(kù)bankDB，要求保存在D:bank創(chuàng)建文件夾D:bank查看文件夾D:bank10定義存儲(chǔ)過(guò)程的語(yǔ)法 CREATE PROCEDURE 存儲(chǔ)過(guò)程名 參數(shù)1 數(shù)據(jù)類型

6、 = 默認(rèn)值 OUTPUT, , 參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT AS SQL語(yǔ)句 GO和C語(yǔ)言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù) 輸入?yún)?shù)允許有默認(rèn)值如何創(chuàng)建存儲(chǔ)過(guò)程11創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程 2-1問(wèn)題：請(qǐng)創(chuàng)建存儲(chǔ)過(guò)程，查看本次考試平均分以及未通過(guò)考試的學(xué)員名單12創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程 2-2CREATE PROCEDURE proc_stu AS DECLARE Avg float SELECT Avg=AVG(grade) FROM sc print 平均分：+convert(varchar(5),Avg) IF (Avg70) print 本班考試成績(jī)：優(yōu)秀

7、 ELSE print 本班考試成績(jī)：較差 print - print 參加本次考試沒(méi)有通過(guò)的學(xué)生： SELECT sname,student.sno,grade FROM student INNER JOIN sc ON student.sno=sc.sno WHEREgrade60 GOproc_stu為存儲(chǔ)過(guò)程的名稱考試平均分變量 顯示考試成績(jī)的等級(jí) 顯示未通過(guò)的學(xué)員 13EXECUTE（執(zhí)行）語(yǔ)句用來(lái)調(diào)用存儲(chǔ)過(guò)程調(diào)用的語(yǔ)法EXEC 過(guò)程名 參數(shù)調(diào)用存儲(chǔ)過(guò)程EXEC proc_stu14創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的參數(shù)分兩種：輸入?yún)?shù)輸出參數(shù)int sum (int a, int b)

8、 int s; s=a+b; return s;c=sum（5, 8）傳入?yún)?shù)值輸入?yún)?shù)： 用于向存儲(chǔ)過(guò)程傳入值，類似C語(yǔ)言的按值傳遞; 輸出參數(shù)： 用于在調(diào)用存儲(chǔ)過(guò)程后， 返回結(jié)果，類似C語(yǔ)言的 按引用傳遞; 返回結(jié)果15帶輸入?yún)?shù)的存儲(chǔ)過(guò)程3-1問(wèn)題：修改上例：由于每次考試的難易程度不一樣，每次 考試的及格線 可能隨時(shí)變化（不再是60分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化。分析：在述存儲(chǔ)過(guò)程添加1個(gè)輸入?yún)?shù)：grade 考試及格線 16帶輸入?yún)?shù)的存儲(chǔ)過(guò)程3-2CREATE PROCEDURE proc_stu grade int AS print - print 參加本次考試沒(méi)有通過(guò)的學(xué)員

9、： SELECT sname,student.sno,grade FROM student INNER JOIN sc ON student.sno=sc.sno WHERE gradegrade GO輸入?yún)?shù)：考試及格線查詢沒(méi)有通過(guò)考試的學(xué)員17帶輸入?yún)?shù)的存儲(chǔ)過(guò)程3-3EXEC proc_stu 54 調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程 假定本次考試機(jī)試偏難，考試的及格線定為54分-或這樣調(diào)用：EXEC proc_stu grade=54機(jī)試及格線降分后，李斯文（55分）成為“漏網(wǎng)之魚(yú)”了18輸入?yún)?shù)的默認(rèn)值3-1帶參數(shù)的存儲(chǔ)過(guò)程確實(shí)比較方便，調(diào)用者可根據(jù)試卷的難易度，隨時(shí)修改每次考試的及格線問(wèn)題：如果

10、試卷的難易程度合適，則調(diào)用者還是必須如此調(diào)用： EXEC proc_stu 60 比較麻煩這樣調(diào)用就比較合理：EXEC proc_stu 54EXEC proc_stu 考試及格線55分考試及格線默認(rèn)為標(biāo)準(zhǔn)的60分19CREATE PROCEDURE proc_stu grade int =60 AS print - print 參加本次考試沒(méi)有通過(guò)的學(xué)員： SELECT sname,student.sno,grade FROM student INNER JOIN sc ON student.sno=sc.sno WHERE grade grade GO考試及格線：默認(rèn)為60分查詢沒(méi)有通過(guò)考

11、試的學(xué)員輸入?yún)?shù)的默認(rèn)值3-220輸入?yún)?shù)的默認(rèn)值3-3EXEC proc_stu -都采用默認(rèn)值 EXEC proc_stu 54 -不采用默認(rèn)值 調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程-錯(cuò)誤的調(diào)用方式：若有兩個(gè)成績(jī)一個(gè)采取默認(rèn)，一個(gè)為54EXEC proc_stu 空格,54 -正確的調(diào)用方式：EXEC proc_stu grade=54 21帶輸出參數(shù)的存儲(chǔ)過(guò)程 3-1如果希望調(diào)用存儲(chǔ)過(guò)程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT）參數(shù)了 問(wèn)題：修改上例，返回未通過(guò)考試的學(xué)員人數(shù)。22CREATE PROCEDURE proc_stu notpassSum int OUTPUT, gra

12、de int=60 AS SELECT sname,student.sno,grade from student INNER JOIN sc ON student.sno=sc.sno WHERE gradegrade SELECT notpassSum=COUNT(sno) FROM sc WHERE grade=3 print 未通過(guò)人數(shù)：+convert(varchar(5),sum)+ 人, 超過(guò)60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)ELSE print 未通過(guò)人數(shù)：+convert(varchar(5),sum)+ 人, 已控制在60%以下，及格分?jǐn)?shù)線適中GO 調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程帶輸出參數(shù)

13、的存儲(chǔ)過(guò)程 3-3調(diào)用時(shí)必須帶OUTPUT關(guān)鍵字 ，返回結(jié)果將存放在變量sum中 后續(xù)語(yǔ)句引用返回結(jié)果24可以使用PRINT語(yǔ)句顯示錯(cuò)誤信息，但這 些信息是臨時(shí)的，只能顯示給用戶 RAISERROR 顯示用戶定義的錯(cuò)誤信息時(shí)可指定嚴(yán)重級(jí)別，設(shè)置系統(tǒng)變量ERROR記錄所發(fā)生的錯(cuò)誤等處理存儲(chǔ)過(guò)程中的錯(cuò)誤 25使用RAISERROR 語(yǔ)句4-1RAISERROR (msg_id | msg_str,severity, state WITH option,.n) RAISERROR語(yǔ)句的用法如下： msg_id：在sysmessages系統(tǒng)表中指定用戶定義錯(cuò)誤信息msg_str：用戶定義的特定信息，最

14、長(zhǎng)255個(gè)字符severity：定義嚴(yán)重性級(jí)別。用戶可使用的級(jí)別為018級(jí)state：表示錯(cuò)誤的狀態(tài)，1至127之間的值option：指示是否將錯(cuò)誤記錄到服務(wù)器錯(cuò)誤日志中 26問(wèn)題：完善上例，當(dāng)用戶調(diào)用存儲(chǔ)過(guò)程時(shí)，傳入的及格線參數(shù)不在0100之間時(shí)，將彈出錯(cuò)誤警告，終止存儲(chǔ)過(guò)程的執(zhí)行。 使用RAISERROR 語(yǔ)句4-227CREATE PROCEDURE proc_stu notpassSum int OUTPUT, -輸出參數(shù) grade int=60 -默認(rèn)參數(shù)放后 AS IF (NOT grade BETWEEN 0 AND 100) BEGIN RAISERROR (及格線錯(cuò)誤，請(qǐng)指

15、定0100之間的分 數(shù)，統(tǒng)計(jì)中斷退出,16,1) RETURN -立即返回，退出存儲(chǔ)過(guò)程 END .其他語(yǔ)句同上例，略GO 錯(cuò)誤處理引發(fā)系統(tǒng)錯(cuò)誤，指定錯(cuò)誤的嚴(yán)重級(jí)別16，調(diào)用狀態(tài)為1（默認(rèn)）,并影響ERROR系統(tǒng)變量的值 使用RAISERROR 語(yǔ)句4-3注：嚴(yán)重級(jí)別 11 到 16:這些消息表明錯(cuò)誤可由用戶修正。28使用RAISERROR 語(yǔ)句4-4/*-調(diào)用存儲(chǔ)過(guò)程，測(cè)試RAISERROR語(yǔ)句-*/DECLARE sum int, t intEXEC proc_stu sum OUTPUT ,604 SET t=ERROR print 錯(cuò)誤號(hào)：+convert(varchar(5),t )IF t0 RETURN -退出批處理，后續(xù)語(yǔ)句不再執(zhí)行print -IF sum=3 print 未通過(guò)人數(shù)：+convert(varchar(5),sum)+ 人,超過(guò)60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)ELSE print 未通過(guò)人數(shù)：+convert(varchar(5),sum)+ 人,