數(shù)據(jù)庫存儲(chǔ)過程ppt課件

1、8.2.1 存儲(chǔ)過程概述存儲(chǔ)過程概述v在SQL Server 2000中，可以將某些需求多次調(diào)用的實(shí)現(xiàn)某個(gè)特定義務(wù)的代碼段編寫成一個(gè)過程，將其保管在數(shù)據(jù)庫中，并由SQL Server效力器經(jīng)過過程名來調(diào)用它們，這些過程就叫做存儲(chǔ)過程。8.2.1 存儲(chǔ)過程概述存儲(chǔ)過程概述v 應(yīng)該提倡多運(yùn)用存儲(chǔ)過程，緣由有以下幾點(diǎn)。v 1實(shí)現(xiàn)了模塊化編程。v 2調(diào)用一次以后，相關(guān)信息就保管在內(nèi)存中，下次調(diào)用時(shí)可以直接執(zhí)行。v 3存儲(chǔ)過程可以接受輸入?yún)?shù)并可以前往輸出值。v 4存儲(chǔ)過程具有對(duì)數(shù)據(jù)庫立刻訪問的功能。v 5運(yùn)用存儲(chǔ)過程可以加快程序的運(yùn)轉(zhuǎn)速度。v 6運(yùn)用存儲(chǔ)過程可以減少網(wǎng)絡(luò)流量。v 7運(yùn)用存儲(chǔ)過程可以提高

2、數(shù)據(jù)庫的平安性。8.2.1 存儲(chǔ)過程概述存儲(chǔ)過程概述v在SQL Server 2000中，存儲(chǔ)過程分為系統(tǒng)測(cè)試、用戶存儲(chǔ)過程、暫時(shí)存儲(chǔ)過程、擴(kuò)展存儲(chǔ)過程及遠(yuǎn)程存儲(chǔ)過程。v1.系統(tǒng)存儲(chǔ)過程是由系統(tǒng)自動(dòng)創(chuàng)建的，主要存儲(chǔ)在master數(shù)據(jù)庫中，普通以sp_為前綴。系統(tǒng)存儲(chǔ)過程完成的功能主要是從系統(tǒng)表中獲取信息。8.2.1 存儲(chǔ)過程概述存儲(chǔ)過程概述2. 用戶存儲(chǔ)過程是由用戶根據(jù)各自的運(yùn)用需求進(jìn)展創(chuàng)建，完成某一個(gè)特定功能的存儲(chǔ)過程。稱號(hào)前普通不加sp_。3. 暫時(shí)存儲(chǔ)過程屬于用戶存儲(chǔ)過程。假設(shè)用戶存儲(chǔ)過程名前有#，那么為暫時(shí)存儲(chǔ)過程，只能在一個(gè)用戶會(huì)話中運(yùn)用。如在名字前有#，那么表示為全局存儲(chǔ)過程，可以

3、再一切的用戶會(huì)話中運(yùn)用。8.2.1 存儲(chǔ)過程概述存儲(chǔ)過程概述4. 擴(kuò)展存儲(chǔ)過程是在SQL Server環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(DLL)，前綴為xp_。它們可以被加載到SQL Server系統(tǒng)中，并且按照存儲(chǔ)過程的方式執(zhí)行。5. 遠(yuǎn)程存儲(chǔ)過程是從遠(yuǎn)程效力器上調(diào)用的存儲(chǔ)過程，或是從銜接到另一個(gè)效力器上的客戶機(jī)上調(diào)用的存儲(chǔ)過程，是非本地效力器上的存儲(chǔ)過程。本卷須知本卷須知v 不能將創(chuàng)建存儲(chǔ)過程的語句與其他SQL語句組合到單個(gè)批處置中；v 默許權(quán)限屬于數(shù)據(jù)庫一切者，并可以將權(quán)限授予其他用戶；v 存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象，稱號(hào)必需遵守標(biāo)示符規(guī)那么；v 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程；v 一個(gè)存儲(chǔ)過程的最大

4、尺寸為128M。8.2.2 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程v在SQL Server 2000中，可以運(yùn)用三種方法創(chuàng)建存儲(chǔ)過程。v運(yùn)用創(chuàng)建存儲(chǔ)過程導(dǎo)游創(chuàng)建存儲(chǔ)過程。v運(yùn)用SQL Server 2000企業(yè)管理器創(chuàng)建存儲(chǔ)過程。v運(yùn)用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過程。1運(yùn)用創(chuàng)建存儲(chǔ)過程導(dǎo)游創(chuàng)建存儲(chǔ)運(yùn)用創(chuàng)建存儲(chǔ)過程導(dǎo)游創(chuàng)建存儲(chǔ)過程過程v1在企業(yè)管理器中，選中某個(gè)SQL Server效力器，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，選擇“工具菜單中的“導(dǎo)游菜單項(xiàng)，單擊導(dǎo)游中“數(shù)據(jù)庫選項(xiàng)左邊的加號(hào)，選中“創(chuàng)建存儲(chǔ)過程導(dǎo)游選項(xiàng)。v2單擊“確定按鈕。2運(yùn)用運(yùn)用SQL Server

5、2000企業(yè)管理企業(yè)管理器創(chuàng)建存儲(chǔ)過程器創(chuàng)建存儲(chǔ)過程v 運(yùn)用SQL Server 2000企業(yè)管理器創(chuàng)建存儲(chǔ)過程的步驟如下：v 在SQL Server 2000企業(yè)管理器中，選擇指定的效力器和數(shù)據(jù)庫，右擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，在彈出的快捷菜單中依次選擇“新建“存儲(chǔ)過程命令；或者右擊數(shù)據(jù)庫中的存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過程命令。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存語句創(chuàng)建存儲(chǔ)過程儲(chǔ)過程v可以運(yùn)用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過程，只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存

6、儲(chǔ)過程v CREATE PROCEDURE的語法方式如下：v CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n v其中各參數(shù)的含義如下：vprocedure_name：用于指定所要?jiǎng)?chuàng)建存儲(chǔ)過程的稱號(hào)。vnumber：該參數(shù)是可選的整數(shù)，它用來對(duì)同名的存儲(chǔ)過程分組，以便用一條DROP PROCEDURE語句即可將同

7、組的過程一同除去。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程vparameter：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個(gè)或多個(gè)參數(shù)。vdata_type：用于指定參數(shù)的數(shù)據(jù)類型。vVARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。僅適用于游標(biāo)參數(shù)。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程vdefault：用于指定參數(shù)的默許值。vOUTPUT：闡明該參數(shù)是一個(gè)前往參數(shù)。vRECOMPILE：闡明SQL Server 2000不會(huì)保管該存儲(chǔ)過程的執(zhí)行方案，該存儲(chǔ)過程每執(zhí)行一次都要重新編譯。3運(yùn)用運(yùn)用Trans

8、act-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程vENCRYPTION：表示對(duì)存儲(chǔ)過程文本進(jìn)展加密。vFOR REPLICATION：用于指定該存儲(chǔ)過程只能在數(shù)據(jù)復(fù)制時(shí)運(yùn)用。本選項(xiàng)不能和WITH RECOMPILE選項(xiàng)一同運(yùn)用。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程vAS：用于指定該存儲(chǔ)過程要執(zhí)行的操作。vsql_statement：是存儲(chǔ)過程中包含的恣意數(shù)目和類型的Transact-SQL語句。3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程v例9-1 創(chuàng)建一個(gè)GetInfo，用于獲取一切學(xué)生信息。vIf existsv (SELECT name

9、FROM sysobjectsv WHERE name=GetInfo AND type=p )v DROP PROCEDURE GetInfovGOvCREATE PROCEDURE GetInfovAS v SELECT * FROM 學(xué)生表vGO3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程v例9-2 創(chuàng)建一個(gè)StuInfo，用于獲取指定學(xué)生的信息。vIF EXISTSvvGOvCREATE RPOCEDURE StuInfov StuId varchar(8)vASv SELECT * FROM 學(xué)生

10、表v WHERE 學(xué)號(hào)=StuIdvGO3運(yùn)用運(yùn)用Transact-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程v 例9-4 創(chuàng)建GetScore，獲取一切課程的平均成果、最高成果、最低成果。并前往結(jié)果。v IF EXISTS GOv CREATE PROCEDURE GetScorev kcID varchar(6), AVGScore int OUTPUT,v MAXScore int OUTPUT,MINScore int OUTPUTv ASv SELECT AVGScore=AVG(Grade),MAXScore=v MAX(Grade)，MINScore=MIN(Grade)v FROM

11、 SCv WHERE Cno=kcID8.2.3 管理存儲(chǔ)過程管理存儲(chǔ)過程v1查看存儲(chǔ)過程v存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢赃\(yùn)用企業(yè)管理器或系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程。1查看存儲(chǔ)過程查看存儲(chǔ)過程v在企業(yè)管理器中查看用戶創(chuàng)建的存儲(chǔ)過程的方法如下。v1在企業(yè)管理器中，翻開指定的效力器和數(shù)據(jù)庫項(xiàng)，并單擊存儲(chǔ)過程文件夾，此時(shí)在右邊的窗格中就會(huì)顯示出數(shù)據(jù)庫中的一切存儲(chǔ)過程。1查看存儲(chǔ)過程查看存儲(chǔ)過程v2右擊要查看的存儲(chǔ)過程，從彈出的快捷菜單中選擇“屬性命令，會(huì)彈出“存儲(chǔ)過程屬性對(duì)話框。1查看存儲(chǔ)過程查看存儲(chǔ)過

12、程v也可以運(yùn)用系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程?？晒┻\(yùn)用的系統(tǒng)存儲(chǔ)過程及其語法方式如下：v1sp_help：用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型vsp_help objname= namev參數(shù)name為要查看的存儲(chǔ)過程的稱號(hào)。1查看存儲(chǔ)過程查看存儲(chǔ)過程v2sp_helptext：用于顯示存儲(chǔ)過程的源代碼vsp_helptext objname= namev參數(shù)name為要查看的存儲(chǔ)過程的稱號(hào)。1查看存儲(chǔ)過程查看存儲(chǔ)過程v3sp_depends：用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫對(duì)象vsp_depends objname=objectv參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過程的稱號(hào)。1查看存儲(chǔ)

13、過程查看存儲(chǔ)過程v4sp_stored_procedures：用于前往當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程列表vsp_stored_proceduressp_name=namev,sp_owner=ownerv,sp_qualifier = qualifier1查看存儲(chǔ)過程查看存儲(chǔ)過程v其中，sp_name = name 用于指定前往目錄信息的過程名；sp_owner = owner 用于指定過程一切者的稱號(hào)；sp_qualifier = qualifier 用于指定過程限定符的稱號(hào)。2修正存儲(chǔ)過程定義修正存儲(chǔ)過程定義v在企業(yè)管理器中，單擊目錄樹中的存儲(chǔ)過程圖標(biāo)，在窗口的右側(cè)右擊要修正的存儲(chǔ)過程，從彈出的快

14、捷菜單中選擇“屬性命令，那么會(huì)出現(xiàn)存儲(chǔ)過程屬性對(duì)話框。2修正存儲(chǔ)過程定義修正存儲(chǔ)過程定義v 其語法方式如下：v ALTER PROCEDURE procedure_name;numberv parameterdata_typev VARYING=defaultOUTPUT,.nv WITHv RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONv FOR REPLICATIONv ASv sql_statement .n 3重命名存儲(chǔ)過程重命名存儲(chǔ)過程v在企業(yè)管理器中，右擊要操作的存儲(chǔ)過程稱號(hào)，從彈出的快捷菜單中選擇“重命名命令，或者雙擊存儲(chǔ)過程稱號(hào)，當(dāng)存儲(chǔ)過程稱

15、號(hào)變成可輸入形狀時(shí)，就可以直接修正該存儲(chǔ)過程的稱號(hào)了。3重命名存儲(chǔ)過程重命名存儲(chǔ)過程v也可以運(yùn)用系統(tǒng)存儲(chǔ)過程sp_rename修正存儲(chǔ)過程的稱號(hào)，其語法方式如下：vsp_rename 原存儲(chǔ)過程稱號(hào)，新存儲(chǔ)過程稱號(hào)8.2.4 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程v 在SQL Server 2000中可以運(yùn)用EXECUTE命令來直接執(zhí)行存儲(chǔ)過程，語法方式如下：v EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 8.

16、2.4 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程v其中各選項(xiàng)的含義如下：vEXECUTE：執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字，假設(shè)此語句是批處置中的第一條語句，可以省略此關(guān)鍵字。vreturn_status：是一個(gè)可選的整型變量，保管存儲(chǔ)過程的前往形狀。這個(gè)變量在運(yùn)用前，必需在批處置、存儲(chǔ)過程或函數(shù)中聲明過。8.2.4 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程vprocedure_name：指定執(zhí)行的存儲(chǔ)過程的稱號(hào)。v;number：用來指定該存儲(chǔ)過程與其他同名存儲(chǔ)過程同組時(shí)的標(biāo)識(shí)號(hào)。vprocedure_name_var：是部分定義變量名，代表存儲(chǔ)過程稱號(hào)。8.2.4 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程vparameter：是在創(chuàng)建存儲(chǔ)過程時(shí)

17、定義的過程參數(shù)。調(diào)用時(shí)向存儲(chǔ)過程所傳送的參數(shù)值由value參數(shù)或variable變量提供，或者運(yùn)用DEFAULT關(guān)鍵字指定運(yùn)用該參數(shù)的默許值，OUTPUT參數(shù)闡明指定參數(shù)為前往參數(shù)。vWITH RECOMPILE：指定在執(zhí)行存儲(chǔ)過程時(shí)重新編譯執(zhí)行方案。一個(gè)實(shí)例一個(gè)實(shí)例v創(chuàng)建一個(gè)存儲(chǔ)過程sortScore，用于獲取給定學(xué)號(hào)的某門課程考試成果能否及格的信息。v創(chuàng)建存儲(chǔ)過程：vCreate procedure sortScore kecheng char(2),vxuehao char(9)vAsvdeclare fenshu intvselect fenshu = gradevfrom scwhe

18、re cno = kecheng and sno = xuehaoif fenshu 60print 不及格else print 及格執(zhí)行過程：Exec sortScore 4, 2019151258.2.4 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程v例9-8 執(zhí)行9-4創(chuàng)建的GetScore,獲取課號(hào)為1的相關(guān)內(nèi)容。v程序清單如下：vdeclare AVGScore int,MAXScore int,MINScore intvexec GetScore 1,AVGScore out, MAXScore out, MINScore outvselect AVGScore , MAXScore , MINScorev!思索：執(zhí)行該存儲(chǔ)過程是，聲明的變量名能否要和定義的存儲(chǔ)過程的變量名堅(jiān)持一致。8.2.6 刪除存儲(chǔ)過程刪除存儲(chǔ)過程v1運(yùn)用企業(yè)管理器刪除存儲(chǔ)過程v在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快捷菜單中選擇“刪除命令，