數(shù)據(jù)庫 存儲(chǔ)過程

數(shù)據(jù)庫存儲(chǔ)過程第一頁，共三十一頁，編輯于2023年，星期三回顧事務(wù)具有ACID四個(gè)屬性，它們是？管理控制事務(wù)的常用T-SQL語句有哪些？索引分為哪幾類？根據(jù)您的理解，什么是視圖？它有什么好處？您在stuInfo表中創(chuàng)建了主鍵列stuNo，SQLServer將自動(dòng)創(chuàng)建哪些索引？2第二頁，共三十一頁，編輯于2023年，星期三本章目標(biāo)了解存儲(chǔ)過程的優(yōu)點(diǎn)掌握常用的系統(tǒng)存儲(chǔ)過程掌握如何創(chuàng)建存儲(chǔ)過程掌握如何調(diào)用存儲(chǔ)過程3第三頁，共三十一頁，編輯于2023年，星期三存儲(chǔ)過程（procedure）類似于C語言中的函數(shù)用來執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則存儲(chǔ)過程可以帶參數(shù)，也可以返回結(jié)果intsum(inta,intb){ints;s=a+b;returns;}存儲(chǔ)過程相當(dāng)于C語言中的函數(shù)什么是存儲(chǔ)過程2-14第四頁，共三十一頁，編輯于2023年，星期三存儲(chǔ)過程------------------------單個(gè)SELECT語句SELECT語句塊SELECT語句與邏輯控制語句可以包含什么是存儲(chǔ)過程2-2存儲(chǔ)過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等5第五頁，共三十一頁，編輯于2023年，星期三執(zhí)行速度更快允許模塊化程序設(shè)計(jì)提高系統(tǒng)安全性減少網(wǎng)絡(luò)流通量存儲(chǔ)過程的優(yōu)點(diǎn)6第六頁，共三十一頁，編輯于2023年，星期三系統(tǒng)存儲(chǔ)過程由系統(tǒng)定義，存放在master數(shù)據(jù)庫中類似C語言中的系統(tǒng)函數(shù)系統(tǒng)存儲(chǔ)過程的名稱都以“sp_”開頭或”xp_”開頭用戶自定義存儲(chǔ)過程由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程類似C語言中的用戶自定義函數(shù)存儲(chǔ)過程的分類7第七頁，共三十一頁，編輯于2023年，星期三常用的系統(tǒng)存儲(chǔ)過程4-1系統(tǒng)存儲(chǔ)過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_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í)際文本。8第八頁，共三十一頁，編輯于2023年，星期三EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系統(tǒng)存儲(chǔ)過程4-2修改數(shù)據(jù)庫的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫當(dāng)前數(shù)據(jù)庫中查詢的對(duì)象的列表返回某個(gè)表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程演示：常用的存儲(chǔ)過程9第九頁，共三十一頁，編輯于2023年，星期三常用的擴(kuò)展存儲(chǔ)過程：xp_cmdshell可以執(zhí)行DOS命令下的一些的操作以文本行方式返回任何輸出調(diào)用語法：EXECxp_cmdshellDOS命令[NO_OUTPUT]常用的系統(tǒng)存儲(chǔ)過程4-310第十頁，共三十一頁，編輯于2023年，星期三常用的系統(tǒng)存儲(chǔ)過程4-4USEmasterGOEXECxp_cmdshell'mkdird:\bank',NO_OUTPUTIFEXISTS(SELECT*FROMsysdatabases

WHEREname='bankDB')

DROPDATABASEbankDBGOCREATEDATABASEbankDB(

…)GOEXECxp_cmdshell'dirD:\bank\'--查看文件創(chuàng)建數(shù)據(jù)庫bankDB，要求保存在D:\bank創(chuàng)建文件夾D:\bank查看文件夾D:\bank11第十一頁，共三十一頁，編輯于2023年，星期三定義存儲(chǔ)過程的語法CREATEPROC[EDURE]存儲(chǔ)過程名

@參數(shù)1數(shù)據(jù)類型=默認(rèn)值OUTPUT,……,@參數(shù)n數(shù)據(jù)類型=默認(rèn)值OUTPUTASSQL語句GO和C語言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值如何創(chuàng)建存儲(chǔ)過程12第十二頁，共三十一頁，編輯于2023年，星期三創(chuàng)建不帶參數(shù)的存儲(chǔ)過程2-1問題：請(qǐng)創(chuàng)建存儲(chǔ)過程，查看本次考試平均分以及未通過考試的學(xué)員名單13第十三頁，共三十一頁，編輯于2023年，星期三創(chuàng)建不帶參數(shù)的存儲(chǔ)過程2-2CREATEPROCEDUREproc_stu

ASDECLARE@writtenAvgfloat,@labAvgfloat

SELECT@writtenAvg=AVG(writtenExam),

@labAvg=AVG(labExam)

FROMstuMarksprint'筆試平均分：'+convert(varchar(5),@writtenAvg)print'機(jī)試平均分：'+convert(varchar(5),@labAvg)IF(@writtenAvg>70AND@labAvg>70)print'本班考試成績：優(yōu)秀'ELSEprint'本班考試成績：較差'print'--------------------------------------------------'print'參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExam

FROMstuInfoINNERJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREwrittenExam<60ORlabExam<60GOproc_stu為存儲(chǔ)過程的名稱筆試平均分和機(jī)試平均分變量顯示考試成績的等級(jí)顯示未通過的學(xué)員14第十四頁，共三十一頁，編輯于2023年，星期三EXECUTE（執(zhí)行）語句用來調(diào)用存儲(chǔ)過程調(diào)用的語法

EXEC過程名[參數(shù)]

調(diào)用存儲(chǔ)過程EXECproc_stu15第十五頁，共三十一頁，編輯于2023年，星期三創(chuàng)建帶參數(shù)的存儲(chǔ)過程存儲(chǔ)過程的參數(shù)分兩種：輸入?yún)?shù)輸出參數(shù)

intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）傳入?yún)?shù)值輸入?yún)?shù)：用于向存儲(chǔ)過程傳入值，類似C語言的按值傳遞;輸出參數(shù)：用于在調(diào)用存儲(chǔ)過程后，

返回結(jié)果，類似C語言的按引用傳遞;

返回結(jié)果16第十六頁，共三十一頁，編輯于2023年，星期三帶輸入?yún)?shù)的存儲(chǔ)過程3-1問題：修改上例：由于每次考試的難易程度不一樣，每次筆試和機(jī)試的及格線可能隨時(shí)變化（不再是60分），這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化。分析：在述存儲(chǔ)過程添加2個(gè)輸入?yún)?shù)：@writtenPass筆試及格線@labPass機(jī)試及格線17第十七頁，共三十一頁，編輯于2023年，星期三帶輸入?yún)?shù)的存儲(chǔ)過程3-2CREATEPROCEDUREproc_stu

@writtenPassint,

@labPassint

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO輸入?yún)?shù)：筆試及格線輸入?yún)?shù)：機(jī)試及格線查詢沒有通過考試的學(xué)員18第十八頁，共三十一頁，編輯于2023年，星期三帶輸入?yún)?shù)的存儲(chǔ)過程3-3EXECproc_stu60,55調(diào)用帶參數(shù)的存儲(chǔ)過程

假定本次考試機(jī)試偏難，機(jī)試的及格線定為55分，筆試及格線定為60分--或這樣調(diào)用：EXECproc_stu@labPass=55,@writtenPass=60機(jī)試及格線降分后，李斯文（59分）成為“漏網(wǎng)之魚”了19第十九頁，共三十一頁，編輯于2023年，星期三輸入?yún)?shù)的默認(rèn)值3-1帶參數(shù)的存儲(chǔ)過程確實(shí)比較方便，調(diào)用者可根據(jù)試卷的難易度，隨時(shí)修改每次考試的及格線問題：如果試卷的難易程度合適，則調(diào)用者還是必須如此調(diào)用：EXECproc_stu60,60，比較麻煩這樣調(diào)用就比較合理：EXECproc_stu55EXECproc_stu

筆試及格線55分，機(jī)試及格線默認(rèn)為60分筆試和機(jī)試及格線都默認(rèn)為標(biāo)準(zhǔn)的60分20第二十頁，共三十一頁，編輯于2023年，星期三CREATEPROCEDUREproc_stu

@writtenPassint=60,

@labPassint=60

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學(xué)員：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPass

ORlabExam<@labPass

GO筆試及格線：默認(rèn)為60分機(jī)試及格線：默認(rèn)為60分查詢沒有通過考試的學(xué)員輸入?yún)?shù)的默認(rèn)值3-221第二十一頁，共三十一頁，編輯于2023年，星期三輸入?yún)?shù)的默認(rèn)值3-3EXECproc_stu--都采用默認(rèn)值EXECproc_stu64--機(jī)試采用默認(rèn)值EXECproc_stu60,55--都不采用默認(rèn)值調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過程--錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分EXECproc_stu,55--正確的調(diào)用方式：EXECproc_stu@labPass=5522第二十二頁，共三十一頁，編輯于2023年，星期三帶輸出參數(shù)的存儲(chǔ)過程3-1如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT）參數(shù)了問題：修改上例，返回未通過考試的學(xué)員人數(shù)。23第二十三頁，共三十一頁，編輯于2023年，星期三CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,@writtenPassint=60,@labPassint=60AS……SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam<@writtenPassORlabExam<@labPassSELECT@notpassSum=COUNT(stuNo)

FROMstuMarksWHEREwrittenExam<@writtenPassORlabExam<@labPassGO輸出（返回）參數(shù)：表示沒有通過的人數(shù)推薦將默認(rèn)參數(shù)放后帶輸出參數(shù)的存儲(chǔ)過程3-2統(tǒng)計(jì)并返回沒有通過考試的學(xué)員人數(shù)24第二十四頁，共三十一頁，編輯于2023年，星期三/*---調(diào)用存儲(chǔ)過程----*/DECLARE@sumintEXECproc_stu@sumOUTPUT,64print'--------------------------------------------------'IF@sum>=3print'未通過人數(shù)：'+convert(varchar(5),@sum)+'人,超過60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)'ELSEprint'未通過人數(shù)：'+convert(varchar(5),@sum)+'人,已控制在60%以下，及格分?jǐn)?shù)線適中'GO調(diào)用帶輸出參數(shù)的存儲(chǔ)過程帶輸出參數(shù)的存儲(chǔ)過程3-3調(diào)用時(shí)必須帶OUTPUT關(guān)鍵字，返回結(jié)果將存放在變量@sum中后續(xù)語句引用返回結(jié)果25第二十五頁，共三十一頁，編輯于2023年，星期三可以使用PRINT語句顯示錯(cuò)誤信息，但這些信息是臨時(shí)的，只能顯示給用戶RAISERROR顯示用戶定義的錯(cuò)誤信息時(shí)可指定嚴(yán)重級(jí)別，設(shè)置系統(tǒng)變量@@ERROR記錄所發(fā)生的錯(cuò)誤等處理存儲(chǔ)過程中的錯(cuò)誤26第二十六頁，共三十一頁，編輯于2023年，星期三使用RAISERROR語句4-1RAISERROR(msg_id|msg_str,severity,stateWITHoption[,...n]])RAISERROR語句的用法如下：msg_id：在sysmessages系統(tǒng)表中指定用戶定義錯(cuò)誤信息msg_str：用戶定義的特定信息，最長255個(gè)字符severity：定義嚴(yán)重性級(jí)別。用戶可使用的級(jí)別為0–18級(jí)state：表示錯(cuò)誤的狀態(tài)，1至127之間的值option：指示是否將錯(cuò)誤記錄到服務(wù)器錯(cuò)誤日志中27第二十七頁，共三十一頁，編輯于2023年，星期三問題：完善上例，當(dāng)用戶調(diào)用存儲(chǔ)過程時(shí)，傳入的及格線參數(shù)不在0～100之間時(shí)，將彈出錯(cuò)誤警告，終止存儲(chǔ)過程的執(zhí)行。使用RAISERROR語句4-228第二十八頁，共三十一頁，編輯于2023年，星期三CREATEPROCEDUREproc_stu@notpassSumintOUTPUT,--輸出參數(shù)@writtenPassint=60,--默認(rèn)參數(shù)放后@labPassint=60--默認(rèn)參數(shù)放后ASIF(NOT@writtenPassBETWEEN0AND100)OR(NOT@labPassBETWEEN0AND100)BEGIN

RAISERROR(‘及格線錯(cuò)誤，請(qǐng)指定0－100之間的分?jǐn)?shù)，統(tǒng)計(jì)中斷退出',16,1)RETURN---立即返回，退出存儲(chǔ)過程END…..其他語句同上例，