SQLServer 2005 存儲(chǔ)過(guò)程的基本操作

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

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

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

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

from班級(jí)

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

from系部

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

from班級(jí)

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

from系部

where系部名稱(chēng)=‘計(jì)算機(jī)系’）商務(wù)技術(shù)系createviewxb_bjmcasselect班級(jí)名稱(chēng)

from班級(jí)

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

from系部

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

PROCEDURE存儲(chǔ)過(guò)程教學(xué)目標(biāo)：

1、明確存儲(chǔ)過(guò)程的概念及作用

2、學(xué)會(huì)存儲(chǔ)過(guò)程的基本操作

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

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

CREATEPROC[EDURE]

存儲(chǔ)過(guò)程名[;number]

[WITHrecompile|encryption|recompile,encryption]

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

問(wèn)題1：創(chuàng)建存儲(chǔ)過(guò)程proc_jsjxbj，顯示計(jì)算機(jī)系的班級(jí)名稱(chēng)。

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

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

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

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

存儲(chǔ)過(guò)程名[;number]

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

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

WHERE系部名稱(chēng)='計(jì)算機(jī)系')CREATEPROCproc_bjmc

@xbmcvarchar(30)ASSELECT班級(jí)名稱(chēng)FROM班級(jí)WHERE系部代碼=(SELECT系部代碼FROM系部

WHERE系部名稱(chēng)@xbmc

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

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

@參數(shù)1數(shù)據(jù)類(lèi)型=默認(rèn)值

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

varchar(30)='計(jì)算機(jī)系'

ASSELECT

班級(jí)名稱(chēng)

FROM班級(jí)WHERE系部代碼=(SELECT系部代碼FROM系部

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

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

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

GOEXECproc_stu_xh

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

@xhchar(12)=‘050101001001'

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

GOEXECproc_stu_xhEXECproc_stu_xh

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

@參數(shù)1數(shù)據(jù)類(lèi)型[=默認(rèn)值]，@參數(shù)2數(shù)據(jù)類(lèi)型[=默認(rèn)值]ASSQL語(yǔ)句提示:

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

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

@參數(shù)1數(shù)據(jù)類(lèi)型[=默認(rèn)值]ASSQL語(yǔ)句提示:

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

例7：查詢(xún)姓劉的學(xué)生的學(xué)號(hào)，姓名，課程名稱(chēng)和成績(jī)。

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

CREATE存儲(chǔ)過(guò)程名@參數(shù)數(shù)據(jù)類(lèi)型OUTPUT

ASSQL語(yǔ)句問(wèn)題5：創(chuàng)建存儲(chǔ)過(guò)程proc_kcpjf，要求計(jì)算出SQL課程的平均分，并將分?jǐn)?shù)通過(guò)輸出變量返回給用戶(hù)。

USEstudentGOCREATEPROCproc_kcpjf

@pjf

tinyintOUTPUTASSELECT@pjf=AVG(成績(jī))FROM課程注冊(cè)

WHERE課程號(hào)=(SELECT課程號(hào)FROM課程

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

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

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

tinyintEXECUTEproc_kcpjf

@pj

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

@pjftinyintOUTPUTASSELECT@pjf=AVG(成績(jī))FROM課程注冊(cè)WHERE課程號(hào)=(SELECT課程號(hào)FROM課程

WHERE課程名稱(chēng)=@kcm)GO執(zhí)行存儲(chǔ)過(guò)程proc_kbkcpjf:USEstudentGODECLARE@pj

tinyintEXECUTEproc_kbkcpjf

'SQL',@pj

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

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

數(shù)據(jù)類(lèi)型[=默認(rèn)值]ASSQL語(yǔ)句提示:

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

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

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

WHERE系部代碼=@xbdmGOEXECproc_xb_del

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

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

proc_jsjxbj

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

proc_jsjxbj

sp_help查看存儲(chǔ)過(guò)程的一般信息EXECsp_help

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

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

1、使用企業(yè)管理器刪除右擊要?jiǎng)h除的存儲(chǔ)過(guò)程，從彈出的菜單中選擇“刪除”。在新彈出的“移除對(duì)象”對(duì)話(huà)框中，單擊“全部移去”按鈕，刪除該存儲(chǔ)過(guò)程。2、使用查詢(xún)分析器SQL語(yǔ)句修改

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

EXECUTEprocedure_nameWITHRECOMPILE3、通過(guò)使用系統(tǒng)存儲(chǔ)過(guò)程設(shè)定重編譯系統(tǒng)存儲(chǔ)過(guò)程sp_recompile強(qiáng)制在下次運(yùn)行存儲(chǔ)過(guò)程時(shí)進(jìn)行重新編譯。其語(yǔ)法格式為：EXE