第8章存儲(chǔ)過程

1、學(xué)習(xí)目標(biāo)n理解存儲(chǔ)過程的作用，學(xué)會(huì)根據(jù)需要?jiǎng)?chuàng)建、修改、刪除存儲(chǔ)過程（帶輸入、輸出參數(shù)），在實(shí)際應(yīng)用開發(fā)時(shí)能夠靈活運(yùn)用存儲(chǔ)過程以提高開發(fā)效率。要把某完成特定功能的SQL做成類似C語言的函數(shù)，供需要時(shí)調(diào)用，如何做？問題 存儲(chǔ)過程（存儲(chǔ)過程（procedure）類似于）類似于c語言中的函數(shù)語言中的函數(shù) 是一組被編譯在一起的是一組被編譯在一起的T-SQL語句的集合，它們被集合在語句的集合，它們被集合在一起以完成一個(gè)特定的任務(wù)。用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)一起以完成一個(gè)特定的任務(wù)。用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則雜的業(yè)務(wù)規(guī)則 存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果什么是存儲(chǔ)

2、過程什么是存儲(chǔ)過程int sum(int a,int b) int s; s =a+b; return s;存儲(chǔ)過程相存儲(chǔ)過程相當(dāng)于當(dāng)于c語言語言中的函數(shù)中的函數(shù)-單個(gè)單個(gè) SELECT 語句語句SELECT 語句塊語句塊SELECT語句與邏輯語句與邏輯控制語句控制語句可以包含可以包含什么是存儲(chǔ)過程什么是存儲(chǔ)過程 存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等等存儲(chǔ)過程的作用模塊化編程：模塊化編程： 創(chuàng)建一個(gè)存儲(chǔ)過程存放在數(shù)據(jù)庫中后，就可以被其他程序反復(fù)使用?？焖賵?zhí)行快速執(zhí)行 存儲(chǔ)過程第一次被執(zhí)行后，就駐留在內(nèi)存中。以后執(zhí)行就省去了重新分析、

3、優(yōu)化、編譯的過程。減少網(wǎng)絡(luò)通信量減少網(wǎng)絡(luò)通信量 有了存儲(chǔ)過程后，在網(wǎng)絡(luò)上只要一條語句就能執(zhí)行一個(gè)存儲(chǔ)過程。安全機(jī)制安全機(jī)制 通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性，通過授權(quán)可以讓用戶只能執(zhí)行存儲(chǔ)過程而不能直接訪問數(shù)據(jù)庫對(duì)象。存儲(chǔ)過程的分類系統(tǒng)存儲(chǔ)過程 由系統(tǒng)定義，存放在master數(shù)據(jù)庫中 類似Java語言類庫中的方法 系統(tǒng)存儲(chǔ)過程的名稱都以“sp_”開頭或“xp_”開頭用戶自定義存儲(chǔ)過程 由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程 類似c語言中用戶自定義的函數(shù)常用的系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息s

4、p_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns返回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程sp_password添加或修改登錄帳戶的密碼sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本EXEC sp_databasesEXEC sp_renamedb Northwind,Northwind1USE stuDBGOEXEC sp

5、_tablesEXEC sp_columns stuInfo EXEC sp_help stuInfoEXEC sp_helpconstraint stuInfoEXEC sp_helpindex stuMarksEXEC sp_helptext view_stuInfo_stuMarks EXEC sp_stored_procedures 常用的系統(tǒng)存儲(chǔ)過程常用的系統(tǒng)存儲(chǔ)過程修改數(shù)據(jù)庫的名稱修改數(shù)據(jù)庫的名稱(單用戶訪問單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫當(dāng)前數(shù)據(jù)庫中查詢的對(duì)象的列表當(dāng)前數(shù)據(jù)庫中查詢的對(duì)象的列表返回某個(gè)表列的信息返回某個(gè)表列的信息查看表查看表stuInfo的

6、信息的信息查看表查看表stuInfo的約束的約束查看表查看表stuMarks的索引的索引查看視圖的語句文本查看視圖的語句文本查看當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程查看當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程演示：常用的存儲(chǔ)過程演示：常用的存儲(chǔ)過程常用的系統(tǒng)存儲(chǔ)過程n常用的擴(kuò)展存儲(chǔ)過程：xp_cmdshell q可以執(zhí)行DOS命令下的一些的操作 q以文本行方式返回任何輸出 q調(diào)用語法：qEXEC xp_cmdshell DOS命令 NO_OUTPUTnSQL Server2005中啟動(dòng)執(zhí)行 xp_cmdshell 擴(kuò)展存儲(chǔ)過程的方法 EXEC sp_configure show advanced options, 1GORE

7、CONFIGUREGOEXEC sp_configure xp_cmdshell, 1GORECONFIGUREGO 常用的系統(tǒng)存儲(chǔ)過程常用的系統(tǒng)存儲(chǔ)過程USE masterGOEXEC xp_cmdshell mkdir d:bank, NO_OUTPUTIF EXISTS(SELECT * FROM sysdatabases WHERE name=bankDB) DROP DATABASE bankDBGOCREATE DATABASE bankDB ( )GOEXEC xp_cmdshell dir D:bank -查看文件查看文件 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫bankDB，要求保存在，要求保存

8、在D:bank創(chuàng)建文件夾創(chuàng)建文件夾D:bank查看文件夾查看文件夾D:bank如何創(chuàng)建存儲(chǔ)過程定義存儲(chǔ)過程的語法定義存儲(chǔ)過程的語法 CREATE PROCEDURE 存儲(chǔ)過程名 參數(shù)1 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT, , 參數(shù)n 數(shù)據(jù)類型 = 默認(rèn)值 OUTPUT AS SQL語句 GOq和c語言的函數(shù)一樣，參數(shù)可選q參數(shù)分為輸入?yún)?shù)、輸出參數(shù) q輸入?yún)?shù)允許有默認(rèn)值調(diào)用存儲(chǔ)過程nEXECUTE（執(zhí)行）語句用來調(diào)用存儲(chǔ)過程n調(diào)用的語法EXEC 過程名 參數(shù)EXEC proc_stun存儲(chǔ)過程的參數(shù)分兩種存儲(chǔ)過程的參數(shù)分兩種：q輸入?yún)?shù)q輸出參數(shù)n輸入?yún)?shù)輸入?yún)?shù)：q用于向存儲(chǔ)過程傳入值，

9、類似c語言帶參函數(shù)n輸出參數(shù)：輸出參數(shù)：q用于在調(diào)用存儲(chǔ)過程后，返回結(jié)果調(diào)用存儲(chǔ)過程調(diào)用存儲(chǔ)過程int sum (int a, int b) int s; s=a+b; return s;c=sum（5, 8）傳入?yún)魅雲(yún)?shù)值數(shù)值返回結(jié)果返回結(jié)果創(chuàng)建不帶參數(shù)的存儲(chǔ)過程 請(qǐng)創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單請(qǐng)創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單創(chuàng)建不帶參數(shù)的存儲(chǔ)過程CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(written

10、Exam), labAvg=AVG(labExam) FROM stuMarks print 筆試平均分：筆試平均分：+convert(varchar(5),writtenAvg) print 機(jī)試平均分：機(jī)試平均分：+convert(varchar(5),labAvg) IF (writtenAvg70 AND labAvg70) print 本班考試成績(jī)：優(yōu)秀本班考試成績(jī)：優(yōu)秀 ELSE print 本班考試成績(jī)：較差本班考試成績(jī)：較差 print - print 參加本次考試沒有通過的學(xué)員：參加本次考試沒有通過的學(xué)員： SELECT stuName,stuInfo.stuNo,writt

11、enExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GOproc_stu為存儲(chǔ)過程的名稱為存儲(chǔ)過程的名稱筆試平均分和機(jī)筆試平均分和機(jī)試平均分變量試平均分變量 顯示考試成績(jī)的等級(jí)顯示考試成績(jī)的等級(jí) 顯示未通過的學(xué)員顯示未通過的學(xué)員 帶輸入?yún)?shù)的存儲(chǔ)過程修改上例：由于每次考試的難易程度不一樣，每次修改上例：由于每次考試的難易程度不一樣，每次筆試和機(jī)試的及格線筆試和機(jī)試的及格線可能隨時(shí)變化（不再是可能隨時(shí)變化（不再是6060分）

12、，這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化上述存儲(chǔ)過程添加上述存儲(chǔ)過程添加2個(gè)輸入?yún)?shù)：個(gè)輸入?yún)?shù)：writtenPass 筆試及格線筆試及格線 labPass 機(jī)試及格線機(jī)試及格線 帶輸入?yún)?shù)的存儲(chǔ)過程帶輸入?yún)?shù)的存儲(chǔ)過程CREATE PROCEDURE proc_stu writtenPass int, labPass int AS print - print 參加本次考試沒有通過的學(xué)員：參加本次考試沒有通過的學(xué)員： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN

13、stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：機(jī)試及格線輸入?yún)?shù)：機(jī)試及格線查詢沒有通過查詢沒有通過考試的學(xué)員考試的學(xué)員帶輸入?yún)?shù)的存儲(chǔ)過程帶輸入?yún)?shù)的存儲(chǔ)過程EXEC proc_stu 60,55 調(diào)用帶參數(shù)的存儲(chǔ)過程調(diào)用帶參數(shù)的存儲(chǔ)過程 假定本次考試機(jī)試偏難，機(jī)試的及格線定為假定本次考試機(jī)試偏難，機(jī)試的及格線定為55分，筆試及格線定為分，筆試及格線定為60分分-或這樣調(diào)用：或這樣調(diào)用：EXEC proc_st

14、u labPass=55,writtenPass=60機(jī)試及格線降分后，機(jī)試及格線降分后，李斯文李斯文（59分）成為分）成為“漏網(wǎng)之魚漏網(wǎng)之魚”了了輸入?yún)?shù)的默認(rèn)值n帶參數(shù)的存儲(chǔ)過程確實(shí)比較方便，調(diào)用者可根據(jù)試卷的難易度，隨時(shí)修改每次考試的及格線如果試卷的難易程度合適，則調(diào)用者還是必須如果試卷的難易程度合適，則調(diào)用者還是必須如此調(diào)用：如此調(diào)用： EXEC proc_stu 60,60，比較麻煩，比較麻煩這樣調(diào)用就比較合理：這樣調(diào)用就比較合理：EXEC proc_stu 55EXEC proc_stu 筆試及格線筆試及格線55分，機(jī)試及格線默認(rèn)為分，機(jī)試及格線默認(rèn)為60分分 筆試和機(jī)試及格線都默

15、認(rèn)為標(biāo)準(zhǔn)的筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的60分分CREATE PROCEDURE proc_stu writtenPass int=60, labPass int=60 AS print - print 參加本次考試沒有通過的學(xué)員：參加本次考試沒有通過的學(xué)員： SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO筆試及格線

16、：默認(rèn)為筆試及格線：默認(rèn)為60分分機(jī)試及格線：默認(rèn)為機(jī)試及格線：默認(rèn)為60分分查詢沒有通過考試的學(xué)員查詢沒有通過考試的學(xué)員輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值輸入?yún)?shù)的默認(rèn)值EXEC proc_stu -都采用默認(rèn)值都采用默認(rèn)值 EXEC proc_stu 64 -機(jī)試采用默認(rèn)值機(jī)試采用默認(rèn)值 EXEC proc_stu 60,55 -都不采用默認(rèn)值都不采用默認(rèn)值 調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過程調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過程-錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分分EXEC proc_stu ,55 -正確的調(diào)用方式：正確的調(diào)用方

17、式：EXEC proc_stu labPass=55 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程n創(chuàng)建格式：CREATE PROCEDURE proc_namepara_name datatype =default, ASSql_statements當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，參數(shù)值可以按任意順序指定，對(duì)于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給出參數(shù)的傳遞值。EXECUTE procedure_nameparameter_name=value,n執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程使用參數(shù)名傳遞參數(shù)值使用參數(shù)名傳遞參數(shù)值按位置傳遞參數(shù)按位置傳遞參數(shù)當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，參數(shù)值的順序必須與存儲(chǔ)過程中定義的輸入?yún)?shù)順

18、序一致。案例 創(chuàng)建一個(gè)名為p_StuByPara的存儲(chǔ)過程，根據(jù)給定的班級(jí)編碼和學(xué)生學(xué)號(hào)，顯示相應(yīng)學(xué)生的信息使用參數(shù)名傳遞參數(shù)值的方法執(zhí)行存儲(chǔ)過程使用參數(shù)名傳遞參數(shù)值的方法執(zhí)行存儲(chǔ)過程p_StudentPara，分別查找班級(jí)編號(hào)為分別查找班級(jí)編號(hào)為“2000001”，“20000002”的所有學(xué)生信息。的所有學(xué)生信息。帶輸出參數(shù)的存儲(chǔ)過程帶輸出參數(shù)的存儲(chǔ)過程 如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，這時(shí)就需如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（要使用輸出（OUTPUT）參數(shù)了）參數(shù)了 修改上例，返回未通過考試的學(xué)員人數(shù)修改上例，返回未通過考試的學(xué)員人數(shù)CREATE P

19、ROCEDURE proc_stu notpassSum int OUTPUT, writtenPass int=60, labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam, labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo) FROM stuMarks WHERE writtenE

20、xamwrittenPass OR labExam=3 print 未通過人數(shù)：未通過人數(shù)：+convert(varchar(5),sum)+ 人人, 超過超過60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)及格分?jǐn)?shù)線還應(yīng)下調(diào)ELSE print 未通過人數(shù)：未通過人數(shù)：+convert(varchar(5),sum)+ 人人, 已控制在已控制在60%以下，及格分?jǐn)?shù)線適中以下，及格分?jǐn)?shù)線適中GO 調(diào)用帶輸出參數(shù)的存儲(chǔ)過程調(diào)用帶輸出參數(shù)的存儲(chǔ)過程帶輸出參數(shù)的存儲(chǔ)過程帶輸出參數(shù)的存儲(chǔ)過程調(diào)用時(shí)必須帶調(diào)用時(shí)必須帶OUTPUT關(guān)鍵字關(guān)鍵字 ，返，返回結(jié)果將存放在變量回結(jié)果將存放在變量sum中中 后續(xù)語句引用返回結(jié)果后續(xù)語句引用返回結(jié)果修改存儲(chǔ)過程n修改格式：ALTER PROCEDURE proc_namepara_name datatype =default, WITH ENCRYPTIONASSql_statements【問題】使用Transact-SQL語句，修改存儲(chǔ)過程p_StudentPara，使其能根據(jù)用戶提供的班級(jí)名稱，而不是班級(jí)編號(hào)進(jìn)行模糊查詢，并要求加密存儲(chǔ)過程。重命名