第8章-第1講-存儲過程課件

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

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

——存儲過程的使用

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

(難點)一、存儲過程的基本概念

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

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

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

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

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

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

procedurep_cj1

as

select學生基本信息表.學號,姓名,課程編號,成績from學生基本信息表,成績表

where學生基本信息表.學號=成績表.學號

orderby4descgoexecp_cj1

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

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

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

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

as

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

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

create

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

as

select姓名，課程編號，成績

from學生基本信息表,

成績表

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

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

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

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

as

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

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

create

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

output

as

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

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

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

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

procedurep_find@findnamechar(8)

as

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

where姓名=@findname)

return1

else

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

print

‘有這個人！‘

else

print

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