第8章-第1講-存儲(chǔ)過程課件

第9章存儲(chǔ)過程和觸發(fā)器

——存儲(chǔ)過程的使用主講人：白楊第25講復(fù)習(xí):使用批處理、腳本、注釋標(biāo)識(shí)符、常量、變量T-SQL系統(tǒng)內(nèi)置函數(shù)流程控制語句begin…end語句if…else、ifexists語句while、break和continue語句waitfor語句return語句第9章存儲(chǔ)過程和觸發(fā)器

——存儲(chǔ)過程的使用

學(xué)習(xí)目標(biāo)認(rèn)知目標(biāo)：掌握存儲(chǔ)過程的定義及優(yōu)點(diǎn)了解常用的系統(tǒng)存儲(chǔ)過程能力目標(biāo):掌握存儲(chǔ)過程的創(chuàng)建和執(zhí)行(重點(diǎn))掌握存儲(chǔ)過程中輸入?yún)?shù)和輸出參數(shù)的設(shè)置和使用方法。

(難點(diǎn))一、存儲(chǔ)過程的基本概念

——1.存儲(chǔ)過程的含義及功能存儲(chǔ)過程的含義是一組預(yù)先寫好的能實(shí)現(xiàn)某種功能的T-SQL程序，是存儲(chǔ)在服務(wù)器上的預(yù)編譯集合。存儲(chǔ)過程是一種獨(dú)立的數(shù)據(jù)庫對(duì)象，在服務(wù)器上創(chuàng)建和運(yùn)行。類似于編程語言中的過程或函數(shù)。一、存儲(chǔ)過程的基本概念

——2.存儲(chǔ)過程的優(yōu)點(diǎn)模塊化編程執(zhí)行速度快效率高存儲(chǔ)過程在執(zhí)行1次后，其執(zhí)行計(jì)劃就保存在內(nèi)存中，以后在執(zhí)行時(shí)就不必再進(jìn)行編譯和優(yōu)化。減少網(wǎng)絡(luò)流量由于存儲(chǔ)過程是存在服務(wù)器上的一組T-SQL，在客戶端調(diào)用時(shí)，可以只用一條調(diào)用語句即可執(zhí)行。提供一種安全機(jī)制可以限制用戶訪問存儲(chǔ)過程中涉及到的表的權(quán)限，只授予執(zhí)行存儲(chǔ)過程的權(quán)限，用戶只能通過存儲(chǔ)過程來訪問表，從而保證了數(shù)據(jù)的安全性。一、存儲(chǔ)過程的基本概念

——3.存儲(chǔ)過程的類型系統(tǒng)存儲(chǔ)過程是指由SQLServer提供的存儲(chǔ)過程，可以作為命令執(zhí)行。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是sp_。用戶存儲(chǔ)過程是由用戶在當(dāng)前數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程。最好不要以sp開頭。若用戶定義的存儲(chǔ)過程與系統(tǒng)存儲(chǔ)過程同名，用戶定義的存儲(chǔ)過程永遠(yuǎn)不會(huì)執(zhí)行。二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——1.簡單存儲(chǔ)過程創(chuàng)建格式：createproc[edure]存儲(chǔ)過程名

asSQL語句說明：createprocedure不能與別的T-SQL語句位于同一個(gè)批中。相應(yīng)的執(zhí)行格式：[exec]存儲(chǔ)過程名二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——1.簡單存儲(chǔ)過程實(shí)例1：創(chuàng)建1個(gè)簡單的存儲(chǔ)過程p_cj1，查詢所有學(xué)生的學(xué)號(hào)、姓名、課程編號(hào)和成績信息，并按成績降序排列。usexscjgocreate

procedurep_cj1

as

select學(xué)生基本信息表.學(xué)號(hào),姓名,課程編號(hào),成績from學(xué)生基本信息表,成績表

where學(xué)生基本信息表.學(xué)號(hào)=成績表.學(xué)號(hào)

orderby4descgoexecp_cj1

--執(zhí)行存儲(chǔ)過程二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——2.使用輸入?yún)?shù)創(chuàng)建格式：createproc[edure]存儲(chǔ)過程名@形參數(shù)據(jù)類型[=默認(rèn)值]，…nasSQL語句相應(yīng)的執(zhí)行格式：[execute]存儲(chǔ)過程名[@實(shí)參=]值,…n輸入?yún)?shù)的傳遞方式有兩種：按位置傳遞：直接給出參數(shù)的值，實(shí)參與形參一一對(duì)應(yīng)通過參數(shù)名傳遞：使用“參數(shù)名=參數(shù)值“的形式，參數(shù)可以任意順序給出。輸入形參：接收從外部傳遞給存儲(chǔ)過程的數(shù)據(jù)。輸入實(shí)參：將數(shù)據(jù)傳遞給存儲(chǔ)過程。二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——2.使用輸入?yún)?shù)實(shí)例2：創(chuàng)建1個(gè)帶有輸入?yún)?shù)的存儲(chǔ)過程p_cj2，查詢指定課程編號(hào)(作為輸入?yún)?shù))的學(xué)生成績信息。create

procedurep_cj2@kchchar(3)=‘101’--有默認(rèn)值的輸入形參：接收外部傳遞的數(shù)據(jù)

as

select*from成績表where課程編號(hào)=@課程信息表hgoexecp_cj2--（1）使用默認(rèn)值執(zhí)行存儲(chǔ)過程execp_cj2‘102‘--（2）按位置傳遞參數(shù)execp_cj2@kch=‘103‘--（3）通過參數(shù)名傳遞參數(shù)輸入實(shí)參：將數(shù)據(jù)傳遞給存儲(chǔ)過程。二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——2.使用輸入?yún)?shù)課堂練習(xí)1：創(chuàng)建并執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程p_學(xué)生基本信息表，查詢指定學(xué)號(hào)(作為輸入?yún)?shù))的學(xué)生姓名、課程編號(hào)、成績。

create

procedurep_學(xué)生基本信息表@xhchar(6)

as

select姓名，課程編號(hào)，成績

from學(xué)生基本信息表,

成績表

where學(xué)生基本信息表.學(xué)號(hào)=成績表.學(xué)號(hào)and學(xué)生基本信息表.學(xué)號(hào)=@xhgoexecp_學(xué)生基本信息表‘020102‘--（1）按位置傳遞參數(shù)execp_學(xué)生基本信息表@xh=‘020103‘--（2）通過參數(shù)名傳遞參數(shù)注意：因輸入?yún)?shù)沒有默認(rèn)值，所以不能用“execp_學(xué)生基本信息表”二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——3.使用輸出參數(shù)創(chuàng)建格式：createproc[edure]存儲(chǔ)過程名@形參數(shù)據(jù)類型output，…nasSQL語句相應(yīng)的執(zhí)行格式：[execute]存儲(chǔ)過程名@實(shí)參output,…n說明：輸出實(shí)參和輸出形參的名字可以相同，也可以不同。使用時(shí)，要先聲明輸入和輸出實(shí)參變量。輸出形參：將數(shù)據(jù)返回給調(diào)用它的程序。輸出實(shí)參：從存儲(chǔ)過程將數(shù)據(jù)帶回。二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——3.使用輸出參數(shù)實(shí)例3：創(chuàng)建1個(gè)帶有輸入?yún)?shù)和輸出的存儲(chǔ)過程p_kh，返回指定教師(作為輸入?yún)?shù))所授課程的課程編號(hào)(作為輸出參數(shù))。create

procedurep_kh@teacherchar(8),@kchchar(3)output

as

select@kch=課程編號(hào)from課程信息表where任課教師=@teachergodeclare@teachervarchar(8),@課程信息表hchar(3)set@teacher='趙怡'execp_kh@teacher,@kchoutputprint@teacher+‘教師所授課程的課程號(hào)為:’+@kch輸入實(shí)參：將教師名傳遞給存儲(chǔ)過程。輸出實(shí)參：將帶回課程號(hào)。輸入形參輸出形參：保存查詢到的課程號(hào)輸出形參：將數(shù)據(jù)返回給調(diào)用它的程序

課堂練習(xí)2：創(chuàng)建并執(zhí)行帶輸入和輸出參數(shù)的存儲(chǔ)過程p_cj3，查詢指定學(xué)號(hào)(作為輸入?yún)?shù))學(xué)生所選課程的課程名稱和成績(兩個(gè)作為輸出參數(shù))。

create

procp_cj3@xhchar(6),@kcmchar(3)output,@cjint

output

as

select@kcm=課程信息表.課程名稱,@cj=成績from成績表,課程信息表

where成績表.課程編號(hào)=課程信息表.課程編號(hào)and學(xué)號(hào)=@xhgodeclare@xhchar(6),@kcmchar(3),@cjintset@xh='020101'execp_cj3@xh,@kcmoutput,@cjoutputprint@xh+‘學(xué)號(hào)所選修的課程是《’+@kcm+‘》。其成績是’+cast(@cjasvarchar(5))二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——4.使用返回值語句格式：return整型表達(dá)式作用：用來顯示存儲(chǔ)過程的執(zhí)行情況。相應(yīng)的執(zhí)行格式：[execute]@狀態(tài)值=存儲(chǔ)過程名二、創(chuàng)建和執(zhí)行存儲(chǔ)過程

——4.使用返回值實(shí)例4：創(chuàng)建并執(zhí)行存儲(chǔ)過程p_find，用于查找指定的學(xué)生，如果找到，則返回?cái)?shù)字1，否則返回0。create

procedurep_find@findnamechar(8)

as

ifexists(select*from學(xué)生基本信息表

where姓名=@findname)

return1

else

return0go--執(zhí)行存儲(chǔ)過程declare@resultintexec@result=p_find‘陳偉‘if@result=1

print

‘有這個(gè)人！‘

else

print

‘沒有這個(gè)人！‘三、修改存儲(chǔ)過程語句格式：alterproc[edure]存儲(chǔ)過程名[@形參數(shù)據(jù)類型[=默認(rèn)值][output]，…n]asSQL語句說明：修改存儲(chǔ)過程的定義后，原存儲(chǔ)過程的權(quán)限設(shè)置仍有效如果采用先刪除存儲(chǔ)過程再重建同名存儲(chǔ)過程的方法，那么在原來存儲(chǔ)過程上設(shè)置的權(quán)限將會(huì)全部丟失。四、刪除、查看存儲(chǔ)過程刪除存儲(chǔ)過程：語句格式：dropproc[edure]存儲(chǔ)過程名查看存儲(chǔ)過程：sp_help存儲(chǔ)過程名--顯示存儲(chǔ)過程的基本信息sp_helptext存儲(chǔ)過程名--顯示存儲(chǔ)過程的源代碼本課小結(jié)存儲(chǔ)過程的含義、優(yōu)點(diǎn)、類型創(chuàng)建存儲(chǔ)過程：需要確定存儲(chǔ)過程的三個(gè)組成部分：所有的輸入?yún)?shù)、傳給調(diào)用程序的輸出