SQL Server(存儲(chǔ)過(guò)程學(xué)習(xí)筆記)

1、筆記記錄筆記記錄8.2.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述v在SQL Server 2000中，可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫(xiě)成一個(gè)過(guò)程，將其保存在數(shù)據(jù)庫(kù)中，并由SQL Server服務(wù)器通過(guò)過(guò)程名來(lái)調(diào)用它們，這些過(guò)程就叫做存儲(chǔ)過(guò)程。筆記記錄筆記記錄8.2.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述v 應(yīng)該提倡多使用存儲(chǔ)過(guò)程，原因有以下幾點(diǎn)。（1）實(shí)現(xiàn)了模塊化編程。（2）調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時(shí)可以直接執(zhí)行。（3）存儲(chǔ)過(guò)程可以接受輸入?yún)?shù)并可以返回輸出值。（4）存儲(chǔ)過(guò)程具有對(duì)數(shù)據(jù)庫(kù)立即訪問(wèn)的功能。（5）使用存儲(chǔ)過(guò)程可以加快程序的運(yùn)行速度。（6）使用存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)

2、流量。（7）使用存儲(chǔ)過(guò)程可以提高數(shù)據(jù)庫(kù)的安全性。筆記記錄筆記記錄8.2.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述v在SQL Server 2000中，存儲(chǔ)過(guò)程分為系統(tǒng)測(cè)試、用戶(hù)存儲(chǔ)過(guò)程、臨時(shí)存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程及遠(yuǎn)程存儲(chǔ)過(guò)程。1.系統(tǒng)存儲(chǔ)過(guò)程是由系統(tǒng)自動(dòng)創(chuàng)建的，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中，一般以sp_為前綴。系統(tǒng)存儲(chǔ)過(guò)程完成的功能主要是從系統(tǒng)表中獲取信息。筆記記錄筆記記錄8.2.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述2. 用戶(hù)存儲(chǔ)過(guò)程是由用戶(hù)根據(jù)各自的應(yīng)用需求進(jìn)行創(chuàng)建，完成某一個(gè)特定功能的存儲(chǔ)過(guò)程。名稱(chēng)前一般不加sp_。3. 臨時(shí)存儲(chǔ)過(guò)程屬于用戶(hù)存儲(chǔ)過(guò)程。如果用戶(hù)存儲(chǔ)過(guò)程名前有#，則為臨時(shí)存儲(chǔ)過(guò)程，只能在一個(gè)

3、用戶(hù)會(huì)話(huà)中使用。如在名字前有#，則表示為全局存儲(chǔ)過(guò)程，可以再所有的用戶(hù)會(huì)話(huà)中使用。筆記記錄筆記記錄8.2.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述4. 擴(kuò)展存儲(chǔ)過(guò)程是在SQL Server環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)(DLL)，前綴為xp_。它們可以被加載到SQL Server系統(tǒng)中，并且按照存儲(chǔ)過(guò)程的方式執(zhí)行。5. 遠(yuǎn)程存儲(chǔ)過(guò)程是從遠(yuǎn)程服務(wù)器上調(diào)用的存儲(chǔ)過(guò)程，或是從連接到另一個(gè)服務(wù)器上的客戶(hù)機(jī)上調(diào)用的存儲(chǔ)過(guò)程，是非本地服務(wù)器上的存儲(chǔ)過(guò)程。筆記記錄筆記記錄注意事項(xiàng)注意事項(xiàng)v 不能將創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句與其他SQL語(yǔ)句組合到單個(gè)批處理中；v 默認(rèn)權(quán)限屬于數(shù)據(jù)庫(kù)所有者，并可以將權(quán)限授予其他用戶(hù)；v 存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)

4、對(duì)象，名稱(chēng)必須遵守標(biāo)示符規(guī)則；v 只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程；v 一個(gè)存儲(chǔ)過(guò)程的最大尺寸為128M。筆記記錄筆記記錄8.2.2 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程v在SQL Server 2000中，可以使用三種方法創(chuàng)建存儲(chǔ)過(guò)程。v使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建存儲(chǔ)過(guò)程。v使用SQL Server 2000企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。v使用Transact-SQL語(yǔ)句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程。筆記記錄筆記記錄1使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建存儲(chǔ)使用創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建存儲(chǔ)過(guò)程過(guò)程v（1）在企業(yè)管理器中，選中某個(gè)SQL Server服務(wù)器，選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，選擇“工具”菜單中的“

5、向?qū)А辈藛雾?xiàng)，單擊向?qū)е小皵?shù)據(jù)庫(kù)”選項(xiàng)左邊的加號(hào)，選中“創(chuàng)建存儲(chǔ)過(guò)程向?qū)А边x項(xiàng)。v（2）單擊“確定”按鈕。筆記記錄筆記記錄2使用使用SQL Server 2000企業(yè)管理企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程器創(chuàng)建存儲(chǔ)過(guò)程v 使用SQL Server 2000企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程的步驟如下：v 在SQL Server 2000企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫(kù)，右擊要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中依次選擇“新建”“存儲(chǔ)過(guò)程”命令；或者右擊數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程”命令。筆記記錄筆記記錄3使用使用Transact-SQL語(yǔ)句創(chuàng)建存語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程儲(chǔ)過(guò)程v可以使

6、用Transact-SQL語(yǔ)句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程，只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。筆記記錄筆記記錄3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程v CREATE PROCEDURE的語(yǔ)法形式如下：v CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n 筆記記錄筆

7、記記錄v其中各參數(shù)的含義如下：vprocedure_name：用于指定所要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的名稱(chēng)。vnumber：該參數(shù)是可選的整數(shù)，它用來(lái)對(duì)同名的存儲(chǔ)過(guò)程分組，以便用一條DROP PROCEDURE語(yǔ)句即可將同組的過(guò)程一起除去。3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程筆記記錄筆記記錄vparameter：過(guò)程中的參數(shù)。在CREATE PROCEDURE語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。vdata_type：用于指定參數(shù)的數(shù)據(jù)類(lèi)型。vVARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。僅適用于游標(biāo)參數(shù)。3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)

8、程筆記記錄筆記記錄vdefault：用于指定參數(shù)的默認(rèn)值。vOUTPUT：表明該參數(shù)是一個(gè)返回參數(shù)。vRECOMPILE：表明SQL Server 2000不會(huì)保存該存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃，該存儲(chǔ)過(guò)程每執(zhí)行一次都要重新編譯。3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程筆記記錄筆記記錄vENCRYPTION：表示對(duì)存儲(chǔ)過(guò)程文本進(jìn)行加密。vFOR REPLICATION：用于指定該存儲(chǔ)過(guò)程只能在數(shù)據(jù)復(fù)制時(shí)使用。本選項(xiàng)不能和WITH RECOMPILE選項(xiàng)一起使用。3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程筆記記錄筆記記錄vAS：用于指定該存儲(chǔ)過(guò)程要執(zhí)行的操

9、作。vsql_statement：是存儲(chǔ)過(guò)程中包含的任意數(shù)目和類(lèi)型的Transact-SQL語(yǔ)句。3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程筆記記錄筆記記錄v例9-1 創(chuàng)建一個(gè)GetInfo，用于獲取所有學(xué)生信息。If exists (SELECT name FROM sysobjects WHERE name=GetInfo AND type=p ) DROP PROCEDURE GetInfoGOCREATE PROCEDURE GetInfoAS SELECT * FROM 學(xué)生表GO3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程筆記記錄筆記記

10、錄3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程v例9-2 創(chuàng)建一個(gè)StuInfo，用于獲取指定學(xué)生的信息。IF EXISTSGOCREATE RPOCEDURE StuInfo StuId varchar(8)AS SELECT * FROM 學(xué)生表 WHERE 學(xué)號(hào)=StuIdGO筆記記錄筆記記錄3使用使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程v 例9-4 創(chuàng)建GetScore，獲取所有課程的平均成績(jī)、最高成績(jī)、最低成績(jī)。并返回結(jié)果。IF EXISTS GOCREATE PROCEDURE GetScore kcID varchar(6), AVGSc

11、ore int OUTPUT, MAXScore int OUTPUT,MINScore int OUTPUTAS SELECT AVGScore=AVG(Grade),MAXScore= MAX(Grade)，MINScore=MIN(Grade) FROM SC WHERE Cno=kcID筆記記錄筆記記錄8.2.3 管理存儲(chǔ)過(guò)程管理存儲(chǔ)過(guò)程v1查看存儲(chǔ)過(guò)程v存儲(chǔ)過(guò)程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂闷髽I(yè)管理器或系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶(hù)創(chuàng)建的存儲(chǔ)過(guò)程。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v在企業(yè)管理器中查看

12、用戶(hù)創(chuàng)建的存儲(chǔ)過(guò)程的方法如下。v（1）在企業(yè)管理器中，打開(kāi)指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，并單擊存儲(chǔ)過(guò)程文件夾，此時(shí)在右邊的窗格中就會(huì)顯示出數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過(guò)程。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v（2）右擊要查看的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇“屬性”命令，會(huì)彈出“存儲(chǔ)過(guò)程屬性”對(duì)話(huà)框。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v也可以使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶(hù)創(chuàng)建的存儲(chǔ)過(guò)程?？晒┦褂玫南到y(tǒng)存儲(chǔ)過(guò)程及其語(yǔ)法形式如下：v（1）sp_help：用于顯示存儲(chǔ)過(guò)程的參數(shù)及其數(shù)據(jù)類(lèi)型vsp_help objname= namev參數(shù)name為要查看的存儲(chǔ)過(guò)程的名稱(chēng)。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查

13、看存儲(chǔ)過(guò)程v（2）sp_helptext：用于顯示存儲(chǔ)過(guò)程的源代碼vsp_helptext objname= namev參數(shù)name為要查看的存儲(chǔ)過(guò)程的名稱(chēng)。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v（3）sp_depends：用于顯示和存儲(chǔ)過(guò)程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象vsp_depends objname=objectv參數(shù)object為要查看依賴(lài)關(guān)系的存儲(chǔ)過(guò)程的名稱(chēng)。筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v（4）sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程列表vsp_stored_proceduressp_name=namev,sp_owner=ownerv,sp

14、_qualifier = qualifier筆記記錄筆記記錄1查看存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程v其中，sp_name = name 用于指定返回目錄信息的過(guò)程名；sp_owner = owner 用于指定過(guò)程所有者的名稱(chēng)；sp_qualifier = qualifier 用于指定過(guò)程限定符的名稱(chēng)。筆記記錄筆記記錄2修改存儲(chǔ)過(guò)程定義修改存儲(chǔ)過(guò)程定義v在企業(yè)管理器中，單擊目錄樹(shù)中的存儲(chǔ)過(guò)程圖標(biāo)，在窗口的右側(cè)右擊要修改的存儲(chǔ)過(guò)程，從彈出的快捷菜單中選擇“屬性”命令，則會(huì)出現(xiàn)存儲(chǔ)過(guò)程屬性對(duì)話(huà)框。筆記記錄筆記記錄2修改存儲(chǔ)過(guò)程定義修改存儲(chǔ)過(guò)程定義v 其語(yǔ)法形式如下：v ALTER PROCEDURE proce

15、dure_name;numberv parameterdata_typev VARYING=defaultOUTPUT,.nv WITHv RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONv FOR REPLICATIONv ASv sql_statement .n 筆記記錄筆記記錄3重命名存儲(chǔ)過(guò)程重命名存儲(chǔ)過(guò)程v在企業(yè)管理器中，右擊要操作的存儲(chǔ)過(guò)程名稱(chēng)，從彈出的快捷菜單中選擇“重命名”命令，或者雙擊存儲(chǔ)過(guò)程名稱(chēng)，當(dāng)存儲(chǔ)過(guò)程名稱(chēng)變成可輸入狀態(tài)時(shí)，就可以直接修改該存儲(chǔ)過(guò)程的名稱(chēng)了。筆記記錄筆記記錄3重命名存儲(chǔ)過(guò)程重命名存儲(chǔ)過(guò)程v也可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_ren

16、ame修改存儲(chǔ)過(guò)程的名稱(chēng)，其語(yǔ)法形式如下：vsp_rename 原存儲(chǔ)過(guò)程名稱(chēng)，新存儲(chǔ)過(guò)程名稱(chēng)筆記記錄筆記記錄8.2.4 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程v 在SQL Server 2000中可以使用EXECUTE命令來(lái)直接執(zhí)行存儲(chǔ)過(guò)程，語(yǔ)法形式如下：v EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 筆記記錄筆記記錄8.2.4 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程v其中各選項(xiàng)的含義如下：vEXECUTE：執(zhí)行存儲(chǔ)過(guò)程的

17、命令關(guān)鍵字，如果此語(yǔ)句是批處理中的第一條語(yǔ)句，可以省略此關(guān)鍵字。vreturn_status：是一個(gè)可選的整型變量，保存存儲(chǔ)過(guò)程的返回狀態(tài)。這個(gè)變量在使用前，必須在批處理、存儲(chǔ)過(guò)程或函數(shù)中聲明過(guò)。筆記記錄筆記記錄8.2.4 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程vprocedure_name：指定執(zhí)行的存儲(chǔ)過(guò)程的名稱(chēng)。v;number：用來(lái)指定該存儲(chǔ)過(guò)程與其他同名存儲(chǔ)過(guò)程同組時(shí)的標(biāo)識(shí)號(hào)。vprocedure_name_var：是局部定義變量名，代表存儲(chǔ)過(guò)程名稱(chēng)。筆記記錄筆記記錄8.2.4 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程vparameter：是在創(chuàng)建存儲(chǔ)過(guò)程時(shí)定義的過(guò)程參數(shù)。調(diào)用時(shí)向存儲(chǔ)過(guò)程所傳遞的參數(shù)值由valu

18、e參數(shù)或variable變量提供，或者使用DEFAULT關(guān)鍵字指定使用該參數(shù)的默認(rèn)值，OUTPUT參數(shù)說(shuō)明指定參數(shù)為返回參數(shù)。vWITH RECOMPILE：指定在執(zhí)行存儲(chǔ)過(guò)程時(shí)重新編譯執(zhí)行計(jì)劃。筆記記錄筆記記錄一個(gè)實(shí)例一個(gè)實(shí)例v創(chuàng)建一個(gè)存儲(chǔ)過(guò)程sortScore，用于獲取給定學(xué)號(hào)的某門(mén)課程考試成績(jī)是否及格的信息。v創(chuàng)建存儲(chǔ)過(guò)程：Create procedure sortScore kecheng char(2),xuehao char(9)Asdeclare fenshu intselect fenshu = gradefrom sc筆記記錄筆記記錄where cno = kecheng and sno = xuehaoif fenshu 60print 不及格else print 及格v執(zhí)行過(guò)程：Exec sortScore 4, 200515125筆記記錄筆記記錄8.2.4 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程