SQLServer 2005 存儲過程的基本操作

第十講存儲過程復(fù)習(xí)索引是以表列為基礎(chǔ)建立的數(shù)據(jù)庫對象，記錄了索引列在數(shù)據(jù)表中的物理存儲位置，實現(xiàn)了表中數(shù)據(jù)的邏輯排序。

聚集索引（唯一索引）表中數(shù)據(jù)行的物理存儲順序與索引順序完全相同，而非聚集索引表中的數(shù)據(jù)行不按非聚集索引鍵的順序排序和存儲。使用索引的意義：可以提高查詢速度；可以保證數(shù)據(jù)記錄的唯一性；減少查詢中分組和排序的時間；加速表與表之間的連接。建立索引的原則適合建立索引的列：主鍵列自動建立聚集索引。外鍵列應(yīng)建立索引。在經(jīng)常查詢的字段上最好建立索引。orderby子句中使用的列。不適合建立索引的列：對于那些查詢中很少涉及的列、重復(fù)值比較多的列不要建索引。對于定義為text，Image和Bit數(shù)據(jù)類型的列上不要建立索引。存儲過程

例：創(chuàng)建視圖，查看“計算機系”的班級名稱。

經(jīng)管系createviewxb_bjmcasselect班級名稱

from班級

where系部代碼=（select系部代碼

from系部

where系部名稱=‘經(jīng)管系’）計算機系createviewxb_bjmcasselect班級名稱

from班級

where系部代碼=（select系部代碼

from系部

where系部名稱=‘計算機系’）商務(wù)技術(shù)系createviewxb_bjmcasselect班級名稱

from班級

where系部代碼=（select系部代碼

from系部

where系部名稱=‘商務(wù)技術(shù)系’）問題：有沒有一種好的辦法，不用為每個系部都創(chuàng)建一個視圖來查看該系部的信息，而是通過一種通用的方法來實現(xiàn)呢？存儲過程

PROCEDURE存儲過程教學(xué)目標：

1、明確存儲過程的概念及作用

2、學(xué)會存儲過程的基本操作

3、學(xué)會存儲過程的基本操作（創(chuàng)建和執(zhí)行）存儲過程1、存儲過程的概念存儲過程是一種數(shù)據(jù)庫對象，是為了實現(xiàn)某個特定任務(wù)，將一組預(yù)編譯的SQL語句以一個存儲單元的形式存儲在服務(wù)器上，供用戶調(diào)用執(zhí)行。存儲過程在第一次執(zhí)行時進行編譯，然后將編譯好的代碼保存在高速緩存中以便以后調(diào)用，這樣可以提高代碼的執(zhí)行效率。一個過程一旦被創(chuàng)建，就可以通過該過程的名稱或者必要的參數(shù)進行調(diào)用。存儲過程同其它編程語言中的過程相似，有如下特點：接收輸入?yún)?shù)并以輸出參數(shù)的形式將多個值返回至調(diào)用過程或批處理。包含執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其它過程）的編程語句。向調(diào)用過程或批處理返回狀態(tài)值，以表明成功或失敗以及失敗的原因。

存儲過程存儲過程的優(yōu)點:安全機制：只給用戶訪問存儲過程的權(quán)限，而不授予用戶訪問表和視圖的權(quán)限。改良了執(zhí)行性能：在第一次執(zhí)行時進行編譯，將編譯好的代碼保存在高速緩存中以便以后調(diào)用，以后執(zhí)行無需編譯。這樣可以提高代碼的執(zhí)行效率。減少網(wǎng)絡(luò)流量：存儲過程存在于服務(wù)器上，調(diào)用時，只需傳遞執(zhí)行存儲過程的執(zhí)行命令和返回結(jié)果。模塊化的程序設(shè)計：增強了代碼的可重用性，提高了開發(fā)效率。簡化復(fù)雜語句：可以在單個存儲過程中執(zhí)行一系列SQL語句；可以在一個的存儲過程內(nèi)引用其它的存儲過程，可以簡化一系列復(fù)雜語句。存儲過程2、存儲過程的類型用戶定義的存儲過程：用戶定義的存儲過程是用戶根據(jù)需要，為完成某一特定功能，在自己的普通數(shù)據(jù)庫中創(chuàng)建的存儲過程。系統(tǒng)存儲過程：系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，以sp_為前綴，主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQLServer提供幫助，為用戶查看數(shù)據(jù)庫對象提供方便。擴展存儲過程：擴展存儲過程以xp_為前綴，它是關(guān)系數(shù)據(jù)庫引擎的開放式數(shù)據(jù)服務(wù)層的一部分，可以使用戶在動態(tài)鏈接庫(DLL)文件所包含的函數(shù)中實現(xiàn)邏輯，從而擴展了T-SQL的功能，并且可以象調(diào)用T-SQL過程那樣從T-SQL語句調(diào)用這些函數(shù)。存儲過程的操作簡單存儲過程的操作包括：創(chuàng)建存儲過程執(zhí)行存儲過程修改存儲過程刪除存儲過程存儲過程的基本操作---創(chuàng)建創(chuàng)建存儲過程時，需要注意下列事項：1、只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。2、數(shù)據(jù)庫的所有者可以創(chuàng)建存儲過程，也可以授權(quán)其他用戶創(chuàng)建存儲過程。3、存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標識符命名規(guī)則。4、不能將CREATEPROCEDURE語句與其它SQL語句組合到單個批處理中。5、創(chuàng)建存儲過程時，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句和返回至調(diào)用過程或批處理以表明成功或失敗的狀態(tài)值。創(chuàng)建不帶參數(shù)的存儲過程使用SQL語句創(chuàng)建不帶參數(shù)的存儲過程語法格式如下：

CREATEPROC[EDURE]

存儲過程名[;number]

[WITHrecompile|encryption|recompile,encryption]

ASSQL語句說明：存儲過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。number：該參數(shù)是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起刪除。RECOMPILE：SQL不會緩存該過程的計劃，每次執(zhí)行該過程時都要重新編譯。ENCRYPTION：所創(chuàng)建的存儲過程的內(nèi)容會被加密,可防止將過程作為SQLServer復(fù)制的一部分發(fā)布。

問題1：創(chuàng)建存儲過程proc_jsjxbj，顯示計算機系的班級名稱。

創(chuàng)建不帶參數(shù)的存儲過程SELECT班級名稱FROM班級WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱='計算機系')GO（1）先在查詢編輯器中編寫實現(xiàn)存儲過程功能的T-SQL語句。（3）單擊“分析”按鈕，進行語法檢查；語法無誤后，單擊“執(zhí)行”按鈕，創(chuàng)建該存儲過程。創(chuàng)建不帶參數(shù)的存儲過程CREATEPROCproc_jsjxbjASSELECT班級名稱FROM班級WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱='計算機系')GO（2）調(diào)試上述SQL語句正確后，再在查詢編輯器中輸入其完整的創(chuàng)建存儲過程的代碼，創(chuàng)建存儲過程。執(zhí)行不帶參數(shù)的存儲過程執(zhí)行不帶參數(shù)的存儲過程的語法如下：

[EXEC[UTE]]存儲過程名說明：如果存儲過程是批處理中的第一條語句，EXECUTE命令可以省略，可以使用存儲過程的名字執(zhí)行該存儲過程。例：在查詢分析器中執(zhí)行存儲過程proc_jsjxbj。執(zhí)行方式如下:proc_jsjxbjEXECproc_jsjxbjEXECUTEproc_jsjxbj創(chuàng)建帶輸入?yún)?shù)的存儲過程問題2:能否創(chuàng)建一個存儲過程proc_bjmc，通過接收可變的系部名稱，顯示指定系部的班級名稱呢？使用SQL語句創(chuàng)建帶輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROC[EDURE]

存儲過程名[;number]

@參數(shù)1數(shù)據(jù)類型WITH[recompile|encryption|remcompile,encryption]

ASSQL語句創(chuàng)建帶輸入?yún)?shù)的存儲過程（3）單擊“分析”按鈕，進行語法檢查；語法無誤后，單擊“執(zhí)行”按鈕，創(chuàng)建該存儲過程。SELECT班級名稱FROM班級WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱='計算機系')CREATEPROCproc_bjmc

@xbmcvarchar(30)ASSELECT班級名稱FROM班級WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱@xbmc

）GO（1）以一個“具體系部”為基礎(chǔ)編寫出T-SQL語句。（2）調(diào)試該語句正確后，以變量代替“具體系部”，再創(chuàng)建存儲過程。執(zhí)行帶輸入?yún)?shù)的存儲過程執(zhí)行帶參數(shù)的存儲過程，可以采用以下兩種方式：

EXEC存儲過程名值|@參數(shù)名稱=值按位置傳遞：在調(diào)用存儲過程時，直接給出參數(shù)值。如果多于一個參數(shù)，給出的參數(shù)值要與定義參數(shù)的順序一致。如：EXECproc_bjmc‘商務(wù)技術(shù)系’使用參數(shù)名稱傳遞：在調(diào)用存儲過程時，按“參數(shù)名=參數(shù)值”的形式給出參數(shù)值。如果參數(shù)多于一個時，給出參數(shù)的順序可以與定義參數(shù)的順序不一致。如：EXECproc_bjmc@xbmc=‘計算機系’問題：對于帶輸入?yún)?shù)的存儲過程，能否在執(zhí)行時在不指定系部名稱的情況下顯示默認系部的班級名稱，又能接收新的系部名稱，顯示新系部的信息呢？創(chuàng)建輸入?yún)?shù)帶默認值的存儲過程創(chuàng)建輸入?yún)?shù)帶默認值的存儲過程的語法格式如下：CREATEPROC存儲過程名

@參數(shù)1數(shù)據(jù)類型=默認值

ASSQL語句問題3：創(chuàng)建存儲過程proc_bjmc_xb，實現(xiàn)默認顯示計算機系的班級，同時在執(zhí)行時還能接收新的系部名稱，顯示新系部名稱的班級信息。創(chuàng)建輸入?yún)?shù)帶默認值的存儲過程CREATEPROCproc_bjmc_xb@xbmc

varchar(30)='計算機系'

ASSELECT

班級名稱

FROM班級WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱=@xbmc)GO執(zhí)行輸入?yún)?shù)帶默認值的存儲過程執(zhí)行輸入?yún)?shù)帶默認值的存儲過程:如果不為參數(shù)賦值,則參數(shù)使用默認值如果為參數(shù)賦新值,則將新值傳遞給參數(shù)如：EXECproc_bjmc_xbEXECproc_bjmc_xb@xbmc='商務(wù)技術(shù)系'鞏固提高任務(wù)1_1：創(chuàng)建存儲過程proc_stu，顯示student表中所有學(xué)生的信息（思考）。

USEstudentGOCREATEPROCproc_stuASSELECT*FROM學(xué)生GOEXECproc_stu鞏固提高任務(wù)1_2：創(chuàng)建存儲過程proc_stu_xh，查詢用戶指定學(xué)號的學(xué)生信息。（思考）USEstudentGOCREATEPROCproc_stu_xh

@xhchar(12)ASSELECT*FROM學(xué)生WHERE學(xué)號=@xh

GOEXECproc_stu_xh

'050101001001'鞏固提高任務(wù)1_3：創(chuàng)建存儲過程proc_stu_xh，既能顯示默認學(xué)號的學(xué)生信息，又能查詢用戶指定學(xué)號的學(xué)生信息。（思考）USEstudentGOCREATEPROCproc_stu_xh

@xhchar(12)=‘050101001001'

ASSELECT*FROM學(xué)生WHERE學(xué)號=@xh

GOEXECproc_stu_xhEXECproc_stu_xh

'050102001001'問題：存儲過程中可以帶一個輸入?yún)?shù)，能否帶多個輸入?yún)?shù)呢？創(chuàng)建帶2個輸入?yún)?shù)的存儲過程創(chuàng)建帶多個輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROCdbo.存儲過程名

@參數(shù)1數(shù)據(jù)類型[=默認值]，@參數(shù)2數(shù)據(jù)類型[=默認值]ASSQL語句提示:

創(chuàng)建帶多個輸入?yún)?shù)的存儲過程時同樣可以為參數(shù)指定默認值.執(zhí)行帶多個參數(shù)的存儲過程時:如果不為參數(shù)賦值，則參數(shù)使用默認值;如果為參數(shù)賦新值，則將新值傳遞給參數(shù)如果加上參數(shù)名稱，則參數(shù)的順序可以顛倒，否則只能按指定的順序創(chuàng)建帶2個輸入?yún)?shù)的存儲過程問題4：存儲過程proc_xmkc，查詢某個學(xué)生、某門課程的成績。CREATEPROCproc_xmkc@xmchar(8)='李冰',@kcchar(20)='SQL'ASSELECT姓名,成績FROM學(xué)生INNERJOIN課程注冊O(shè)N學(xué)生.學(xué)號=課程注冊.學(xué)號

INNERJOIN課程ON課程注冊.課程號=課程.課程號WHERE姓名=@xmAND課程名稱=@kcEXECproc_xmkcEXECproc_xmkc@kc='英語'EXECproc_xmkc@xm='劉祥',@kc='英語‘EXECproc_xmkc@kc='英語',@xm='劉祥'問題：在查詢時可以在條件中使用通配符進行模糊查詢，在存儲過程中的輸入?yún)?shù)能否使用通配符進行模糊查詢呢？創(chuàng)建輸入?yún)?shù)帶通配符的存儲過程創(chuàng)建帶通配符輸入?yún)?shù)的存儲過程CREATEPROCdbo.存儲過程名

@參數(shù)1數(shù)據(jù)類型[=默認值]ASSQL語句提示:

創(chuàng)建帶多個輸入?yún)?shù)的存儲過程時同樣可以為參數(shù)指定默認值.執(zhí)行帶多個參數(shù)的存儲過程時:如果不為參數(shù)賦值,則參數(shù)使用默認值;如果為參數(shù)賦新值,則將新值傳遞給參數(shù)如果加上參數(shù)名稱,則參數(shù)的順序可以顛倒,否則只能按指定的順序例6：查詢某指定姓氏的學(xué)生的基本信息,默認姓氏為劉。

例7：查詢姓劉的學(xué)生的學(xué)號，姓名，課程名稱和成績。

問題：存儲過程中可以通過輸入?yún)?shù)接收數(shù)據(jù)，能否通過輸出參數(shù)輸出查詢結(jié)果呢？創(chuàng)建帶輸出參數(shù)的存儲過程在創(chuàng)建存儲過程時,可以定義返回參數(shù),返回參數(shù)通過OUTPUT來定義.

CREATE存儲過程名@參數(shù)數(shù)據(jù)類型OUTPUT

ASSQL語句問題5：創(chuàng)建存儲過程proc_kcpjf，要求計算出SQL課程的平均分，并將分數(shù)通過輸出變量返回給用戶。

USEstudentGOCREATEPROCproc_kcpjf

@pjf

tinyintOUTPUTASSELECT@pjf=AVG(成績)FROM課程注冊

WHERE課程號=(SELECT課程號FROM課程

WHERE課程名稱='SQL')GO執(zhí)行帶有輸出參數(shù)的存儲過程執(zhí)行帶有輸出參數(shù)的存儲過程時，返回參數(shù)必須在定義存儲過程和使用存儲過程時都定義，可使用相同的變量名進行定義,也可使用不同的變量名進行定義，但數(shù)據(jù)類型一定要相同.執(zhí)行前需先通過DECLARE語句聲明接收存儲過程返回值的變量及類型；DECLARE@變量數(shù)據(jù)類型如:DECLARE@pj

tinyint執(zhí)行存儲過程使用該變量時，還必須為這個變量加上OUTPUT聲明。EXEC@變量OUTPUT如:EXEC@pj

OUTPUT執(zhí)行帶有輸出參數(shù)的存儲過程執(zhí)行proc_kcpjf的代碼如下：USEstudentGODECLARE@pj

tinyintEXECUTEproc_kcpjf

@pj

OUTPUTPRINT'SQL課程學(xué)生的平均分數(shù)為'+STR(@pj)GO問題：存儲過程中能否既通過輸入?yún)?shù)接收數(shù)據(jù)，又通過輸出參數(shù)輸出結(jié)果呢？創(chuàng)建帶輸入輸出參數(shù)的存儲過程問題6：創(chuàng)建存儲過程，要求根據(jù)用戶指定的課程名稱，計算出該課程的平均分，并將分數(shù)通過輸出變量返回給用戶。CREATEPROCproc_kbkcpjf@kcmvarchar(20),

@pjftinyintOUTPUTASSELECT@pjf=AVG(成績)FROM課程注冊WHERE課程號=(SELECT課程號FROM課程

WHERE課程名稱=@kcm)GO執(zhí)行存儲過程proc_kbkcpjf:USEstudentGODECLARE@pj

tinyintEXECUTEproc_kbkcpjf

'SQL',@pj

OUTPUTPRINT'SQL課程學(xué)生的平均分數(shù)為'+STR(@pj)GO鞏固提高任務(wù)2：存儲過程的功能是：輸入某學(xué)生,輸出該學(xué)生各門課程的總成績、平均成績。CREATEPROCproc_xs_zfpjf@xmvarchar(8),@zfintOUTPUT,@pjftinyintOUTPUTASSELECT@zf=SUM(成績),@pjf=AVG(成績)FROM課程注冊WHERE學(xué)號=(SELECT學(xué)號FROM學(xué)生

WHERE姓名=@xm)GODECLARE@zfint,@pjftinyintEXECUTEproc_xs_zfpjf'李冰',@zfOUTPUT,@pjfOUTPUTPRINT'該生的總分為：'+STR(@zf)PRINT''PRINT'該生的平均分為：'+STR(@pjf)GO問題：前面創(chuàng)建的存儲過程均是用于查詢數(shù)據(jù)的存儲過程，能否創(chuàng)建用于添加、更新和刪除數(shù)據(jù)的存儲過程呢？創(chuàng)建添加、修改、刪除記錄的存儲過程創(chuàng)建用于添加、修改、刪除的帶輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROC存儲過程名@參數(shù)

數(shù)據(jù)類型[=默認值]ASSQL語句提示:

創(chuàng)建用于添加、修改、刪除記錄的帶輸入?yún)?shù)的存儲過程時同樣可以為參數(shù)指定默認值執(zhí)行輸入?yún)?shù)帶默認值的存儲過程:如果不為參數(shù)賦值,則參數(shù)使用默認值如果為參數(shù)賦新值,則將新值傳遞給參數(shù)如果加上參數(shù)名稱,參數(shù)的順序可以顛倒,否則只能按指定的順序創(chuàng)建用于添加記錄的存儲過程問題7：創(chuàng)建一個用于向“系部”表插入記錄的存儲過程proc_xb_add，插入的具體數(shù)據(jù)由調(diào)用時給出。CREATEPROCproc_xb_Insert

(@xbdmchar(2),@xbmcvarchar(30),@xzhrvarchar(8))ASINSERTINTO系部(系部代碼,系部名稱,系主任)VALUES(@xbdm,@xbmc,@xzhr)GOEXECproc_xb_Insert

‘05’,‘藝術(shù)設(shè)計系’,‘劉永才‘GO創(chuàng)建用于刪除記錄的存儲過程問題8：創(chuàng)建存儲過程，在“系部”表中刪除用戶指定值的系部信息,如果系部代碼被使用，則不允許刪除。CREATEPROCproc_xb_del@xbdmchar(2)ASIFEXISTS(SELECT*FROM專業(yè)WHERE系部代碼=@xbdm)PRINT'該系部代碼正在使用，不能刪除'ELSEDELETEFROM系部

WHERE系部代碼=@xbdmGOEXECproc_xb_del

'05'創(chuàng)建用于更新記錄的存儲過程問題9：創(chuàng)建存儲過程，在“系部”表中更新用戶指定值的系部信息。CREATEPROCproc_xb_update(@xbdmchar(2),@xbmcchar(30),@xzhrchar(8))ASUPDATE系部SET系部名稱=@xbmc,系主任=@xzhr

WHERE系部代碼=@xbdmGOEXECproc_xb_update'06','畜牧系','王寶山'查看存儲過程存儲過程創(chuàng)建好后，其名稱保存在系統(tǒng)表sysobjects中，其源代碼保存在syscomments中，兩表通過ID字段進行關(guān)聯(lián)。如果需要查看存儲過程相關(guān)信息，可以直接使用系統(tǒng)表，也可以使用系統(tǒng)存儲過程，還可以使用SQL語句。問題10：使用系統(tǒng)表查看student數(shù)據(jù)庫中名為proc_jsjxbj的存儲過程的定義信息。USEstudentGOSELECTTEXTFROMSYSCOMMENTSWHEREIDIN(SELECTIDFROMSYSOBJECTSWHERENAME=‘proc_jsjxbj'ANDXTYPE='P')GO使用系統(tǒng)存儲過程查看存儲過程信息sp_helptext查看存儲過程的文本信息EXECsp_helptext

proc_jsjxbj

查看存儲過程的相關(guān)性EXECsp_depends

proc_jsjxbj

sp_help查看存儲過程的一般信息EXECsp_help

proc_jsjxbj查看存儲過程信息使用SQLServerManagementStudio查看存儲過程信息啟動SQLServerManagementStudio，在“對象資源管理器”窗口中，依次展開數(shù)據(jù)庫→student→可編程性→存儲過程節(jié)點。在展開的存儲過程節(jié)點中右擊需要查看的存儲過程，在彈出的快捷菜單中右鍵選擇“編寫存儲過程腳本為”→“CREATE到”→“新編輯器窗口”：查看存儲過程代碼查看依賴關(guān)系：查看該存儲過程的基本表修改存儲過程修改存儲過程的語法格式如下:ALTERPROC存儲過程名ASSQL語句例：修改存儲過程proc_jsjxbj

，返回經(jīng)濟管理系的班級名稱。存儲過程與觸發(fā)器刪除存儲過程

1、使用企業(yè)管理器刪除右擊要刪除的存儲過程，從彈出的菜單中選擇“刪除”。在新彈出的“移除對象”對話框中，單擊“全部移去”按鈕，刪除該存儲過程。2、使用查詢分析器SQL語句修改

DROPPROC存儲過程名例：刪除剛剛建立的存儲過程。存儲過程與觸發(fā)器存儲過程的重新編譯存儲過程第一次執(zhí)行后，其被編譯的代碼將駐留在高速緩存中，當(dāng)用戶再次執(zhí)行該存儲過程時，SQLServer將其從緩存中調(diào)出執(zhí)行。有時，在我們使用了一次存儲過程后，可能會因為某些原因，必須向表中新增加數(shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時，如果調(diào)用緩存中的存儲過程，需要對它進行重新編譯，使存儲過程能夠得到優(yōu)化。存儲過程與觸發(fā)器存儲過程的重新編譯SQLServer提供三種重新編譯存儲過程的方法：1、在建立存儲過程時設(shè)定重新編譯在其定義中指定WITHRECOMPILE選項2、在執(zhí)行存儲過程時設(shè)定重編譯在執(zhí)行存儲過程時指定WITHRECOMPILE選項，可強制對存儲過程進行重新編譯。其語法格式如下：

EXECUTEprocedure_nameWITHRECOMPILE3、通過使用系統(tǒng)存儲過程設(shè)定重編譯系統(tǒng)存儲過程sp_recompile強制在下次運行存儲過程時進行重新編譯。其語法格式為：EXE