第八講存儲(chǔ)過(guò)程

第八講存儲(chǔ)過(guò)程第一頁(yè)，共二十一頁(yè)，2022年，8月28日2本章目標(biāo)了解存儲(chǔ)過(guò)程的優(yōu)點(diǎn)掌握常用的系統(tǒng)存儲(chǔ)過(guò)程掌握如何創(chuàng)建存儲(chǔ)過(guò)程掌握如何調(diào)用存儲(chǔ)過(guò)程第二頁(yè)，共二十一頁(yè)，2022年，8月28日3存儲(chǔ)過(guò)程（procedure）類似于C#語(yǔ)言中的函數(shù)用來(lái)執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則存儲(chǔ)過(guò)程可以帶參數(shù)，也可以返回結(jié)果intsum(inta,intb){ints;s=a+b;returns;}存儲(chǔ)過(guò)程相當(dāng)于C#語(yǔ)言中的函數(shù)什么是存儲(chǔ)過(guò)程第三頁(yè)，共二十一頁(yè)，2022年，8月28日4存儲(chǔ)過(guò)程------------------------單個(gè)SELECT語(yǔ)句SELECT語(yǔ)句塊SELECT語(yǔ)句與邏輯控制語(yǔ)句可以包含什么是存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程可以包含數(shù)據(jù)操縱語(yǔ)句、變量、邏輯控制語(yǔ)句等第四頁(yè)，共二十一頁(yè)，2022年，8月28日5執(zhí)行速度更快允許模塊化程序設(shè)計(jì)提高系統(tǒng)安全性減少網(wǎng)絡(luò)流通量存儲(chǔ)過(guò)程的優(yōu)點(diǎn)第五頁(yè)，共二十一頁(yè)，2022年，8月28日6系統(tǒng)存儲(chǔ)過(guò)程由系統(tǒng)定義，存放在master數(shù)據(jù)庫(kù)中類似C#語(yǔ)言中的系統(tǒng)函數(shù)系統(tǒng)存儲(chǔ)過(guò)程的名稱都以“sp_”開頭或”xp_”開頭用戶自定義存儲(chǔ)過(guò)程由用戶在自己的數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程類似C#語(yǔ)言中的用戶自定義函數(shù)存儲(chǔ)過(guò)程的分類第六頁(yè)，共二十一頁(yè)，2022年，8月28日7常用的系統(tǒng)存儲(chǔ)過(guò)程系統(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_rename更改表名/視圖名/存儲(chǔ)過(guò)程名sp_help查看某個(gè)表的所有信息第七頁(yè)，共二十一頁(yè)，2022年，8月28日8EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系統(tǒng)存儲(chǔ)過(guò)程修改數(shù)據(jù)庫(kù)的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)當(dāng)前數(shù)據(jù)庫(kù)中查詢的對(duì)象的列表返回某個(gè)表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語(yǔ)句文本查看當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程演示：常用的存儲(chǔ)過(guò)程第八頁(yè)，共二十一頁(yè)，2022年，8月28日9定義存儲(chǔ)過(guò)程的語(yǔ)法CREATEPROC[EDURE]存儲(chǔ)過(guò)程名

@參數(shù)1數(shù)據(jù)類型=默認(rèn)值OUTPUT,……,@參數(shù)n數(shù)據(jù)類型=默認(rèn)值OUTPUTASSQL語(yǔ)句GO和C#語(yǔ)言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值創(chuàng)建存儲(chǔ)過(guò)程第九頁(yè)，共二十一頁(yè)，2022年，8月28日創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程問題：請(qǐng)創(chuàng)建存儲(chǔ)過(guò)程，查看本次考試平均分以及未通過(guò)考試的學(xué)員名單第十頁(yè)，共二十一頁(yè)，2022年，8月28日11EXECUTE（執(zhí)行）語(yǔ)句用來(lái)調(diào)用存儲(chǔ)過(guò)程調(diào)用的語(yǔ)法

EXEC[UTE]

存儲(chǔ)過(guò)程名[參數(shù)]

調(diào)用執(zhí)行存儲(chǔ)過(guò)程EXECproc_stu第十一頁(yè)，共二十一頁(yè)，2022年，8月28日12創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的參數(shù)分兩種：輸入?yún)?shù)輸出參數(shù)

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

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

返回結(jié)果第十二頁(yè)，共二十一頁(yè)，2022年，8月28日13帶輸入?yún)?shù)的存儲(chǔ)過(guò)程問題：第十三頁(yè)，共二十一頁(yè)，2022年，8月28日14帶輸入?yún)?shù)的存儲(chǔ)過(guò)程EXECproc_stu60,55調(diào)用帶參數(shù)的存儲(chǔ)過(guò)程

--或這樣調(diào)用：EXECproc_stu@labPass=55,@writtenPass=60第十四頁(yè)，共二十一頁(yè)，2022年，8月28日15輸入?yún)?shù)的默認(rèn)值帶參數(shù)的存儲(chǔ)過(guò)程確實(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分第十五頁(yè)，共二十一頁(yè)，2022年，8月28日16CREATEPROCEDUREproc_stu

@writtenPassint=60,

@labPassint=60

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

ORlabExam<@labPass

GO筆試及格線：默認(rèn)為60分機(jī)試及格線：默認(rèn)為60分查詢沒有通過(guò)考試的學(xué)員輸入?yún)?shù)的默認(rèn)值第十六頁(yè)，共二十一頁(yè)，2022年，8月28日17輸入?yún)?shù)的默認(rèn)值EXECproc_stu--都采用默認(rèn)值EXECproc_stu64--機(jī)試采用默認(rèn)值EXECproc_stu60,55--都不采用默認(rèn)值調(diào)用帶參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程--錯(cuò)誤的調(diào)用方式：希望筆試采用默認(rèn)值，機(jī)試及格線55分EXECproc_stu,55--正確的調(diào)用方式：EXECproc_stu@labPass=55第十七頁(yè)，共二十一頁(yè)，2022年，8月28日18帶輸出參數(shù)的存儲(chǔ)過(guò)程如果希望調(diào)用存儲(chǔ)過(guò)程后，返回一個(gè)或多個(gè)值，這時(shí)就需要使用輸出（OUTPUT）參數(shù)了問題：第十八頁(yè)，共二十一頁(yè)，2022年，8月28日19CREATEPROCEDUREproc_stu

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

FROMstuMarksWHEREwrittenExam<@writtenPassORlabExam<@labPassGO輸出（返回）參數(shù)：表示沒有通過(guò)的人數(shù)推薦將默認(rèn)參數(shù)放后帶輸出參數(shù)的存儲(chǔ)過(guò)程3-2統(tǒng)計(jì)并返回沒有通過(guò)考試的學(xué)員人數(shù)第十九頁(yè)，共二十一頁(yè)，2022年，8月28日20/*---調(diào)用存儲(chǔ)過(guò)程----*/DECLARE@sumintEXECproc_stu@sumOUTPUT,64print'--------------------------------------------------'IF@sum>=3print'未通過(guò)人數(shù)：'+convert(varchar(5),@sum)+'人,超過(guò)60%,及格分?jǐn)?shù)線還應(yīng)下調(diào)'ELSEprint'未通過(guò)人數(shù)：'+convert(varchar(5),@sum)+'人,已控制在60%以下，及格分?jǐn)?shù)線適中'GO調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程帶輸出參數(shù)的存儲(chǔ)過(guò)程3-3調(diào)用時(shí)必須帶OUTPUT關(guān)鍵字，返回結(jié)果將存放在變量@sum中后續(xù)語(yǔ)句