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

數(shù)據(jù)庫存儲(chǔ)過程第1頁，共49頁，2023年，2月20日，星期六10存儲(chǔ)過程

1.存儲(chǔ)過程的概念2.創(chuàng)建存儲(chǔ)過程3.執(zhí)行存儲(chǔ)過程4.存儲(chǔ)過程的管理與維護(hù)第2頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念

SQLServer應(yīng)用操作中,存儲(chǔ)過程和觸發(fā)器扮演相當(dāng)重要的角色,不僅能提高應(yīng)用效率,確保一致性,更能提高系統(tǒng)執(zhí)行速度。同時(shí)，使用觸發(fā)器來完成業(yè)務(wù)規(guī)則，達(dá)到簡化程序設(shè)計(jì)的目的。第3頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念

存儲(chǔ)過程包括系統(tǒng)存儲(chǔ)過程和用戶存儲(chǔ)過程。系統(tǒng)存儲(chǔ)過程：Executesp_help表名：查看表的結(jié)構(gòu)。Executesp_helpindex表名

：查看表上的索引信息。Executesp_helptext視圖名：查看視圖的定義信息。第4頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念

存儲(chǔ)過程按返回的數(shù)據(jù)類型，可分為兩類：一類類似于SELECT語句，用于查詢數(shù)據(jù)，查詢到的數(shù)據(jù)以結(jié)果集的形式給出；另一類存儲(chǔ)過程是通過輸出參數(shù)返回信息，或不返回信息只執(zhí)行一個(gè)動(dòng)作。存儲(chǔ)過程可以嵌套，即一個(gè)存儲(chǔ)過程的內(nèi)部可以調(diào)用另一個(gè)存儲(chǔ)過程。第5頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（1）基本概念

存儲(chǔ)過程是一組編譯在單個(gè)執(zhí)行計(jì)劃中的Transact-SQL語句，它將一些固定的操作集中起來交給SQLServer數(shù)據(jù)庫服務(wù)器完成，以實(shí)現(xiàn)某個(gè)任務(wù)。

存儲(chǔ)過程就是預(yù)先編譯和優(yōu)化并存儲(chǔ)于數(shù)據(jù)庫中的過程。第6頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（2）存儲(chǔ)過程的優(yōu)點(diǎn)

1）通過本地存儲(chǔ)、代碼預(yù)編譯和緩存技術(shù)實(shí)現(xiàn)高性能的數(shù)據(jù)操作。 2）通過通用編程結(jié)構(gòu)和過程重用實(shí)現(xiàn)編程框架。 3）通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性。第7頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念使用存儲(chǔ)過程的好處 1）允許模塊化的程序設(shè)計(jì)。 2）更快的執(zhí)行速度。 3）有效降低網(wǎng)絡(luò)流量。

4）較好地安全機(jī)制。第8頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（3）存儲(chǔ)過程的類型

1）系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程（SystemStoredProcedures）主要存儲(chǔ)在master數(shù)據(jù)庫中，以sp_開頭，用來進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定，獲取信息，從而為系統(tǒng)管理員管理SQLServer提供支持。第9頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（3）存儲(chǔ)過程的類型

1）系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程我們以前接觸過：Executesp_help表名：查看表的結(jié)構(gòu)。Executesp_helpindex表名：查看表上的索引信息。Executesp_helptext視圖名：查看視圖的定義信息。SQLServer提供了很多系統(tǒng)存儲(chǔ)過程，他們用于系統(tǒng)管理、用戶登錄管理、權(quán)限設(shè)置、數(shù)據(jù)庫對象管理、數(shù)據(jù)復(fù)制等操作。第10頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（3）存儲(chǔ)過程的類型

2）用戶自定義的存儲(chǔ)過程用戶定義的存儲(chǔ)過程可分為本地存儲(chǔ)過程（LocalStoredProcedures）、遠(yuǎn)程存儲(chǔ)過程(RemoteStoredProcedures)、臨時(shí)存儲(chǔ)過程(TemporaryStoredProcedures)、擴(kuò)展存儲(chǔ)過程(ExtendedStoredProcedures)等。在這里我們只討論本地存儲(chǔ)過程。第11頁，共49頁，2023年，2月20日，星期六1.存儲(chǔ)過程的概念（3）存儲(chǔ)過程的類型

2）用戶自定義的存儲(chǔ)過程

由用戶在當(dāng)前工作的數(shù)據(jù)庫中創(chuàng)建的存儲(chǔ)過程，稱作本地存儲(chǔ)過程。事實(shí)上一般所說的存儲(chǔ)過程指的就是本地存儲(chǔ)過程。第12頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

創(chuàng)建用戶自定義存儲(chǔ)過程有兩種方法：可以使用T-SQL語言的CREATEPROCEDURE語句創(chuàng)建存儲(chǔ)過程，也可以使用對象資源管理器向?qū)?chuàng)建存儲(chǔ)過程。

第13頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程創(chuàng)建和使用存儲(chǔ)過程都必須遵循如下的規(guī)則：1）創(chuàng)建存儲(chǔ)過程的權(quán)限默認(rèn)為屬于數(shù)據(jù)庫所有者，該所有者可以把次權(quán)限授予其他用戶。2）存儲(chǔ)過程是數(shù)據(jù)庫對象，其名稱必須遵守標(biāo)識符規(guī)則。名稱標(biāo)識符的長度最大為128位，且數(shù)據(jù)庫中必須惟一。3）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。4）每個(gè)存儲(chǔ)過程最多可以使用1024個(gè)參數(shù)。5）存儲(chǔ)過程最大支持32層嵌套。第14頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（1）創(chuàng)建不帶參數(shù)的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程的基本語法如下：

CREATEPROCEDURE存儲(chǔ)過程名 [WITHENCRYPTION]—對存儲(chǔ)過程加密 [WITHRECOMPILE]—對存儲(chǔ)過程重新編譯 AS SQL語句第15頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（1）創(chuàng)建不帶參數(shù)的存儲(chǔ)過程

執(zhí)行存儲(chǔ)過程的基本語法如下：EXEC[UTE]存儲(chǔ)過程名如果該存儲(chǔ)過程是批處理中第一條語句，則EXEC可以省略。簡單存儲(chǔ)過程類似于將一組SQL語句起個(gè)名字，然后就可以在需要時(shí)反復(fù)調(diào)用。復(fù)雜一些的則要有輸入和輸出參數(shù)。第16頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

【實(shí)例】在學(xué)生成績管理數(shù)據(jù)庫中，創(chuàng)建一個(gè)名為“grade_pro”的存儲(chǔ)過程，用于查詢不及格學(xué)生的成績信息（包括學(xué)號，姓名，課程名稱，成績）。USE學(xué)生成績管理系統(tǒng)GOcreateproceduregrade_proasselect學(xué)生表.學(xué)號,姓名,課程名,分?jǐn)?shù)from學(xué)生表,課程表,成績表where學(xué)生表.學(xué)號=成績表.學(xué)號and課程表.課程號=成績表.課程號and分?jǐn)?shù)<60go第17頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

【實(shí)例】針對教師表Teacher，創(chuàng)建一個(gè)名為teacher_pro1的存儲(chǔ)過程，用于查詢所有男教師的信息。USE學(xué)生成績管理系統(tǒng)GOcreateprocedureteacher_proasselect*from教師表where性別='男'go第18頁，共49頁，2023年，2月20日，星期六

執(zhí)行存儲(chǔ)過程

在存儲(chǔ)過程建立好后，該存儲(chǔ)過程作為數(shù)據(jù)庫對象已經(jīng)存在，其名稱和文件分別存放在sysobjects和syscomments系統(tǒng)表中?？梢允褂肨-SQL的EXECUTE語句來執(zhí)行存儲(chǔ)過程。如果該存儲(chǔ)過程是批處理中第一條語句，則EXEC關(guān)鍵字可以省略。執(zhí)行存儲(chǔ)過程的基本語法如下：EXEC[UTE]存儲(chǔ)過程名第19頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（2）創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程

一個(gè)存儲(chǔ)過程可以帶一個(gè)或多個(gè)輸入?yún)?shù)，輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過程傳遞的參數(shù)，它們在創(chuàng)建存儲(chǔ)過程語句中被定義，在執(zhí)行存儲(chǔ)過程中給出相應(yīng)的參數(shù)值。第20頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（2）創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程的語法格式如下：CREATEPROCEDURE存儲(chǔ)過程名@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句第21頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程【例10-2】從學(xué)生表中返回指定學(xué)號的學(xué)生的信息。createprocedureu_getstudent@numbervarchar(40)asselect*from學(xué)生表where學(xué)號=@number第22頁，共49頁，2023年，2月20日，星期六

執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程在執(zhí)行存儲(chǔ)過程的語句中，有兩種方式來傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。使用參數(shù)名傳遞參數(shù)值，是通過語句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，參數(shù)值可以按任意順序制定，對于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給出參數(shù)的傳遞值。

第23頁，共49頁，2023年，2月20日，星期六執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程

1）使用參數(shù)名傳遞參數(shù)值

執(zhí)行使用參數(shù)名傳遞參數(shù)值的存儲(chǔ)過程的語法格式如下。

EXECUTE存儲(chǔ)過程名[@參數(shù)名=參數(shù)值][,...n]第24頁，共49頁，2023年，2月20日，星期六執(zhí)行存儲(chǔ)過程

【實(shí)例】執(zhí)行【實(shí)例10-2】創(chuàng)建的存儲(chǔ)過程u_getstudentexecuteu_getstudent@number=

'1002'第25頁，共49頁，2023年，2月20日，星期六

執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程

2）按位置傳送參數(shù)值

在執(zhí)行存儲(chǔ)過程的語句中，不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值。當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，傳遞值的順序必須與存儲(chǔ)過程中定義的輸入?yún)?shù)的順序相一致。按位置傳遞參數(shù)值的存儲(chǔ)過程的語法格式如下。

EXECUTE存儲(chǔ)過程名[參數(shù)值1,參數(shù)值2,...n]第26頁，共49頁，2023年，2月20日，星期六

執(zhí)行存儲(chǔ)過程

【實(shí)例】執(zhí)行【實(shí)例10-2】創(chuàng)建的存儲(chǔ)過程u_getstudentexecuteu_getstudent

'1002'第27頁，共49頁，2023年，2月20日，星期六執(zhí)行存儲(chǔ)過程

可以看到，按參數(shù)位置傳遞參數(shù)值比按參數(shù)名傳遞參數(shù)值簡潔,比較適合參數(shù)值較少的情況;而按參數(shù)名傳遞參數(shù)使程序可讀性增強(qiáng)。特別是參數(shù)數(shù)量較多時(shí)，建議使用按參數(shù)名稱傳遞參數(shù)的方法，這樣的程序可讀性、可維護(hù)性都要好一些。第28頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程【例10-3】創(chuàng)建帶有通配符參數(shù)的存儲(chǔ)過程。下面的存儲(chǔ)過程只從學(xué)生表中返回指定一些學(xué)生(提供名字和姓氏)的信息。該存儲(chǔ)過程對傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則返回所有學(xué)生的信息。createprocedureu_getstudentbyname@namevarchar(40)='%'asselect*from學(xué)生表where姓名like@namego第29頁，共49頁，2023年，2月20日，星期六執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程，可以按多種組合執(zhí)行。executeu_getstudentbynameexecuteu_getstudentbyname'張%'executeu_getstudentbyname'張劉'第30頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（3）創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程如果我們需要從存儲(chǔ)過程中返回一個(gè)或多個(gè)值，可以通過在創(chuàng)建存儲(chǔ)過程的語句中定義輸出參數(shù)來實(shí)現(xiàn)，為了使用輸出參數(shù)，需要在CREATEPROCEDURE語句中指定OUTPUT關(guān)鍵字。第31頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

（3）創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程的語法格式如下：CREATEPROCEDURE存儲(chǔ)過程名@參數(shù)名數(shù)據(jù)類型[VARYING][=默認(rèn)值]OUTPUT[,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句第32頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程【例10-4】創(chuàng)建一個(gè)存儲(chǔ)過程U_getstudentaveagebydept，獲得某個(gè)院系的學(xué)生的平均年齡。createprocedureu_getstudentaveagebydept@deptvarchar(40),@aveageintoutasselect@aveage=avg(year(getdate())-year(出生日期))from學(xué)生表where院系名稱=@deptgo第33頁，共49頁，2023年，2月20日，星期六執(zhí)行存儲(chǔ)過程存儲(chǔ)過程的執(zhí)行：declare@aveageintexecuteu_getstudentaveagebydept'計(jì)算機(jī)學(xué)院',@aveageoutprint@aveage第34頁，共49頁，2023年，2月20日，星期六2.創(chuàng)建存儲(chǔ)過程

【實(shí)例】創(chuàng)建一個(gè)存儲(chǔ)過程ssc_pro3，獲得選取某門課程的選課人數(shù)。USE學(xué)生成績管理系統(tǒng)GOcreateprocedures_pro3@snamevarchar(30),@countintoutasselect@count=count(*)from學(xué)生表,課程表,成績表where學(xué)生表.學(xué)號=成績表.學(xué)號

and課程表.課程號=成績表.課程號

and課程名=@snamego第35頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

在存儲(chǔ)過程建立好后，該存儲(chǔ)過程作為數(shù)據(jù)庫對象已經(jīng)存在，其名稱和文件分別存放在sysobjects和syscomments系統(tǒng)表中?？梢允褂肨-SQL的EXECUTE語句來執(zhí)行存儲(chǔ)過程。如果該存儲(chǔ)過程是批處理中第一條語句，則EXEC關(guān)鍵字可以省略。執(zhí)行存儲(chǔ)過程的基本語法如下：EXEC[UTE]存儲(chǔ)過程名第36頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

（1）執(zhí)行不帶參數(shù)的存儲(chǔ)過程【實(shí)例8-37】執(zhí)行【實(shí)例8-32】中創(chuàng)建的名為“grade_pro”的存儲(chǔ)過程，用于查詢不及格學(xué)生的成績信息（包括學(xué)號，姓名，課程名稱，成績）。Execgrade_pro【實(shí)例8-38】執(zhí)行【實(shí)例8-33】創(chuàng)建的名為teacher_pro1的存儲(chǔ)過程，用于查詢所有男教師的信息。Execteacher_pro1

第37頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程在執(zhí)行存儲(chǔ)過程的語句中，有兩種方式來傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。使用參數(shù)名傳遞參數(shù)值，是通過語句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，參數(shù)值可以按任意順序制定，對于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給出參數(shù)的傳遞值。

第38頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程

1）使用參數(shù)名傳遞參數(shù)值

執(zhí)行使用參數(shù)名傳遞參數(shù)值的存儲(chǔ)過程的語法格式如下。

EXECUTE存儲(chǔ)過程名[@參數(shù)名=參數(shù)值][,...n]第39頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

【實(shí)例8-39】執(zhí)行【實(shí)例8-34】創(chuàng)建的存儲(chǔ)過程ssc_pro1，使用輸入?yún)?shù)（課程名稱），用于查詢某門課程的選修情況，包括學(xué)號，姓名，課程名稱和成績。EXECssc_pro1@scname='數(shù)據(jù)庫技術(shù)'【實(shí)例8-40】執(zhí)行【實(shí)例8-36】創(chuàng)建的存儲(chǔ)過程ssc_pro3，獲得選取某門課程的選課人數(shù)。DECLARE@ccountINTEXECssc_pro3@scname='數(shù)據(jù)庫技術(shù)',@ccount=@ccountOUTPUTPRINT'選修數(shù)據(jù)庫技術(shù)課程的人數(shù)為：',@ccount第40頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

（2）執(zhí)行帶參數(shù)的存儲(chǔ)過程

2）按位置傳送參數(shù)值

在執(zhí)行存儲(chǔ)過程的語句中，不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值。當(dāng)存儲(chǔ)過程含有多個(gè)輸入?yún)?shù)時(shí)，傳遞值的順序必須與存儲(chǔ)過程中定義的輸入?yún)?shù)的順序相一致。按位置傳遞參數(shù)值的存儲(chǔ)過程的語法格式如下。

EXECUTE存儲(chǔ)過程名[參數(shù)值1,參數(shù)值2,...n]第41頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

【實(shí)例8-41】執(zhí)行【實(shí)例8-34】創(chuàng)建的存儲(chǔ)過程ssc_pro1，使用輸入?yún)?shù)（課程名稱），用于查詢某門課程的選修情況，包括學(xué)號，姓名，課程名稱和成績。EXECssc_pro1'數(shù)據(jù)庫技術(shù)'【實(shí)例8-42】執(zhí)行【實(shí)例8-36】創(chuàng)建的存儲(chǔ)過程ssc_pro3，獲得選取某門課程的選課人數(shù)。DECLARE@ccountINTEXECssc_pro3'數(shù)據(jù)庫技術(shù)',@ccountOUTPUTSELECT'選修數(shù)據(jù)庫技術(shù)課程的人數(shù)為：',@ccount第42頁，共49頁，2023年，2月20日，星期六3.執(zhí)行存儲(chǔ)過程

可以看到，按參數(shù)位置傳遞參數(shù)值比按參數(shù)名傳遞參數(shù)值簡潔,比較適合參數(shù)值較少的情況;而按參數(shù)名傳遞參數(shù)使程序可讀性增強(qiáng)。特別是參數(shù)數(shù)量較多時(shí)，建議使用按參數(shù)名稱傳遞參數(shù)的方法，這樣的程序可讀性、可維護(hù)性都要好一些。第43頁，共49頁，2023年，2月20日，星期六4.存儲(chǔ)過程的管理與維護(hù)

（1）查看存儲(chǔ)過程存儲(chǔ)過程建立好后，該存儲(chǔ)過程作為數(shù)據(jù)庫對象已經(jīng)存在，其名稱和文件分別存放在sysobjects和syscomments系統(tǒng)表中。我們可以通過對象資源管理器查看存儲(chǔ)過程的源代碼，也可以通過SQLServer提供的系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程信息。

可以使用對象資源管理器和系統(tǒng)存儲(chǔ)過程來查看用戶自定義存儲(chǔ)過程。第44頁，共49頁，2023年，2月20日，星期六4.存儲(chǔ)過程的管理與維護(hù)

（1）查看存儲(chǔ)過程

2）使用系統(tǒng)存儲(chǔ)過程在查詢分析器下，可以通過系統(tǒng)存儲(chǔ)過程sp_helptext查看存儲(chǔ)過程的定義；通過sp_help查看存儲(chǔ)過程的參數(shù)；通過sp_depends查看存儲(chǔ)過程的相關(guān)性?！緦?shí)例】在查詢分析器下，使用系統(tǒng)存儲(chǔ)過程，查看【實(shí)例10-2】創(chuàng)建的存儲(chǔ)過程、參數(shù)和相關(guān)性。EXECUTEsp_helptextssc_pro1EXECUTEsp_helpssc_pro1EXECUTEsp_dependsssc_pro1第45頁，共49頁，2023年，2月20日，星期六4.存儲(chǔ)過程的管理與維護(hù)

（2）修改存儲(chǔ)過程

修改存儲(chǔ)過程通常是指編輯它的參數(shù)和T-S